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


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.

1 Answer 1


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);
(1 row)


postgres=> SET bytea_output to 'hex';
postgres=> SELECT ST_AsBinary('POINT (24.3 41.7)'::geometry);
(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!!! Jan 20, 2016 at 20:45
  • A feature in postgres binary gives: "\x01010000000bdb8621a8415ac049c02725b1d64340" while the same feature in MSSQL gives: "0x3D0B0000010C6019954006E8474180E2C554F4823941" ... hmmm... Jan 20, 2016 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, 2016 at 21:43
  • well I had to live with another solution, but thanks for posting this info! Jan 20, 2016 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. Jan 20, 2016 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.