[GIS] How to cast geometry to varbinary in PostGIS / PostgreSQL

postgispostgresqlsql-server-spatial

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.

Best Answer

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)
Related Question