4

I'm not being able to understand how is the data type geography in SQL server... For example I have the following data:

0xE6100000010CCEAACFD556484340B2F336363BCA21C0

what I know:

0x is prefix for hexadecimal

last 16 numbers are longitude: B2F336363BCA21C0 (double of decimal format)

16 numbers before the last 16 are latitude: CEAACFD556484340 (double of decimal format)

4 first numbers are SRID: E610 (hexadecimal for WGS84)

what I don't understand:

numbers from 5 to 12 : 0000010C

what is this?

From what I read this seems linked to WKB(Well Known Binary) or EWKB(Extended Well Known Binary) anyway i was not abble to find a definition for EWKB... And for WKB this is supposed to be geometry type (4-byte integer) but the value doesn't match with the Geometry types codes (this example is for one point coordinate)

Can you help to understand this format?

Improve this question

3 Answers 3

Reset to default
1

The spatial types (geometry and geography) in SQL Server are implemented as CLR data types. As with any such data types, you get a binary representation when you query the value directly. Unfortunately, it's not (as far as I know) WKB but rather whatever format Microsoft decided was best for their implementation. For us (the users), we should work with the published interface of methods that have been published by MS (for instance the geography method reference). Which is to say that you should only try to decipher the MS binary representation if you're curious (and not for actually working with it).

That said, if you need/want to work with WKB, you can! For example, you can use the STGeomFromWKB() static method to create a geography instance from WKB that you provide and STAsBinary() can be called on a geography instance to return WKB to you.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

As you said this is more because I'm curious, and want to understand the format, but was not able to find spec at any place.
I feel like that's intentional. That is, they want the freedom to be able to change the format at will. As long as the API (i.e. the documented static and non-static methods) still work, they're good to go. But I applaud your curiosity (I'm upvoting the question as a consequence of that).
0

The Format spec can be found here:

https://msdn.microsoft.com/en-us/library/ee320529(v=sql.105).aspx

As that page shows, it used to change very frequently, but has slowed down significantly over the past 2 years

I am currently needing to dig into the spec to serialize from JVM code into a bcp file so that I can use SQLServerBulkCopy rather than plain JDBC to upload data into tables (it is about 7x faster to write a bcp file than using JDBC), but this is proving to be more complicated than what I originally anticipated.

After testing with bcp, you can upload geographies by specifying an off row format ( varchar(max) ) and store the well known text, SQL server will see this and assume you wanted a geography based on the WKT it sees.

Improve this answer

2 Comments

Hey @cbradsh1 ! I'm very interested in this and if you have managed to BCP the BLOBs directly from different types of data (LINESTRING, POLYGON, etc...) as I have pretty the same use Case in python ;)
I added to my answer, but it isn't showing. You can upload using BCP by formatting the BCP metadata as a varchar(max) and load it with the Well Known Text representation of the geography. SQL server will see this and automatically assume you wanted a geography column
0

In my case converting to nvarchar resolved the issue.

Sample of select and result

Improve this answer

1 Comment

As any other function, but it add nothing regarding the format specification...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.