Magic of WKT (Well Known Text)

It’s been a long, long while since I posted anything, but I have been anything but idle.

I have been porting a lot of GIS Applications from Oracle Spatial to SQL Server Spatial. This is true for the Oracle OSE and the Topobase customers lately.

Typically Oracle 11g to Microsoft SQL Server 2014 is the migration path I have been following.

My first reaction is always to create a Safe Software FME batch file to do the translation but sometimes I like to do it the hard way.

I wanted a live view in SQL Server to look at the Oracle tables.  Normally very easy with a “Linked Server” in SQL Server.

But, try doing this with Oracle’s SDO_GEOM datatype (NOT!).

Until I found the magic of Well Known Text (WKT).  This is a common Geometry standard that is shared between both SQL and Oracle.  It looks something like this:

POLYGON ((632995.706198033 6925343.8592000678, 632951.17949780729 6925366.6051015854, 632922.04199836869 6925309.5665011816, 632966.56860080548 6925286.8205018751, 632995.706198033 6925343.8592000678))

Now during the query to Oracle, I can ask for the SDO_GEOM column like this:

select SDO_UTIL.TO_WKBGEOMETRY(geom)  as wkt_geom from Parcels;

If I want to see that data in SQL Server, can query the linked server:

select geometry::STGeomFromText(wkt_geom, 26911) from [linked_oracle].gis_prod.Parcels;

NOTE: 26911 is UTM coordinate system.

So this little magic lets me go dynamically from Oracle to SQL Server without static translation.

Worth the wait I hope!