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

[Paul Ramsey]

Hi Matt,

varbinary and bytes are the "same" insofar as they hold an array of
bytes. there's no guarantee though, that geometry->varbinary in
sqlserver going to produce the same thing as geometry->bytes in
postgresql.

As a for-example, the sqlserver varbinary example you show about
doesn't start with 00 or 01. That means it's not valid WKB (which
start with a zero or one byte to flag the endianness). It's "something
else". This could crimp your style.

0x3D0B 0000010C A0F3BE3B5CE9474100EC414E3AF93941

It looks like it might end with two doubles, so perhaps it's a point?
That should be WKB type 01, but I don't see a 00000001 anywhere, which
is what the WKB type number would look like (int32 type)

Anyways, your best bet is going to be going via WKB, so maybe work on
wrapping your PostGIS calls in ST_AsBinary or ST_AsEWKB (for 3d/4d
objects). To get hex encoded text versions, just wrap the bytes
outputs in an 'encode' function, like

select encode(st_asbinary(geom), 'hex') from my_table

ATB,

P



On Wed, Jan 20, 2016 at 12:17 PM, Matthew Baker <mattbaker at gmail.com> wrote:
> 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
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users