3

In MSSQL Server, casting geometry to varbinary(max) produces a string that looks like this:

0x3D0B0000010CA0F3BE3B5CE9474100EC414E3AF93941

When you insert that string into a geometry column, a geometry object is built.

However, I can't seem to find out what the equivalent data format is in PostGIS / PostgreSQL in order to bring that same varbinary format out of a PostGIS geometry column (for the purpose of loading it into an MSSQL Server Geometry object).

I've read that the 'bytea' format does the same as varbinary, but the bytea version of a PostGIS geometry object looks like this:

\001\001\000\000 \346\020\000\000\263\234\021o\224?Z\300-\265\373^\322\334C@

Is there a way to get the same string that the MSSQL varbinary format produces from PostGIS / PostgreSQL?

Some background into this: I'm developing my own ETL tools using Python.

psycopg2 works well with PostgreSQL (PostGIS) database conversion - and PostgreSQL casts geometry to varchar in a SQL statement in my python script, which inserts nicely back into a PostGIS geometry column - PostGIS ETL done!

pypyodbc is working great for the receiving end of a psycopg2 query, but as the problem above states, I can't find the correct format to cast the PostGIS geometry to in the SQL in the python script... otherwise, I can read other columns from PostgreSQL and write them to MSSQL just fine.

3

They're two different representations of the same binary data. SET bytea_output = 'hex' will switch to the hex representation that it sounds like you want.

Here's a couple of examples:

postgres=> SET bytea_output='escape';
postgres=> SELECT ST_AsBinary('POINT (24.3 41.7)'::geometry);
                              st_asbinary                              
-----------------------------------------------------------------------
 \001\001\000\000\000\315\314\314\314\314L8@\232\231\231\231\231\331D@
(1 row)

and

postgres=> SET bytea_output to 'hex';
postgres=> SELECT ST_AsBinary('POINT (24.3 41.7)'::geometry);
                 st_asbinary                  
----------------------------------------------
 \x0101000000cdcccccccc4c38409a99999999d94440
(1 row)
  • Almost the same, isn't it...? Seems the \ in the postgres version replaces the 0 in the MSSQL Version? Going to test this out -and figure out how to replace the \ with 0 - thank you!!! – DPSSpatial Jan 20 '16 at 20:45
  • A feature in postgres binary gives: "\x01010000000bdb8621a8415ac049c02725b1d64340" while the same feature in MSSQL gives: "0x3D0B0000010C6019954006E8474180E2C554F4823941" ... hmmm... – DPSSpatial Jan 20 '16 at 20:47
  • Sorry, I didn't mean that MSSQL and PostGIS had the same internal representation. I meant that the 'escape' style and 'hex' style are two different ways of writing the same binary data. – dbaston Jan 20 '16 at 21:43
  • well I had to live with another solution, but thanks for posting this info! – DPSSpatial Jan 20 '16 at 22:18
  • It would be interesting to see what the binary representation of some known geometries is in SQL Server. Maybe it's like PostGIS, a bastardized WKB or something. – Paul Ramsey Jan 20 '16 at 23:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.