[postgis-users] How to cast geometry to 'varbinary' in PostGIS / PostgreSQL

[Matthew Baker]

Hi all,

Does anyone have any thoughts to the problem below?

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.

I've posted this to GIS.SE too... (hope that's ok...)

http://gis.stackexchange.com/questions/177620/how-to-cast-geometry-to-varbinary-in-postgis-postgresql

Thanks in advance for any thoughts or suggestions!

-Matt Baker
Denver Public Schools
Denver, CO
mattbaker at gmail.com



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20160120/7bd5473e/attachment.html>