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 <mattba...@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
mattba...@gmail.com
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users
