15
Jun
17

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.

linked

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!

 

 

 

Advertisements

0 Responses to “Magic of WKT (Well Known Text)”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Gordon Luckett

Arrow Geomatics Inc's Gordon Luckett

Contact

gordon dot luckett at arrowgeo dot com 1-519-837-9500 (Arrow Geomatics Inc.)

Checkout MapGuide Guy’s Youtube Channel

gordonluckett@twitter


%d bloggers like this: