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

[Matthew Baker]

So the path I took was, as suggested by Paul (and I assumed I would have 
to anyway) was to:

- Select the geometry as ST_AsText from the PG source table
- create a geom_temp column of varchar(max) type in the MSSQL 
destination table
- dump the geometry text into geom_temp column in MSSQL
- When the ETL is done, use STGeomFromText to update the MSSQL geometry 
column
- delete the geom_temp field
- done!

Again all in python using psycopg2 (postgres) and pypyodbc (mssql server)

Oh and any spatial function in PostGIS can be used to transform the data 
(ie. for us, re-project to State Plane for our MSSQL clients) on the fly...

So this is the best spatial ETL solution I've seen so far - no more 
meddling with Talend or GeoKettle... and no more FME either! (Although 
my needs are small, the MSSQL portion was a thorn in my side...)

Thanks again!!!

-m



On 1/20/2016 1:52 PM, Paul Ramsey wrote:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users