Thiiink: Ideas, Imagination, and Innovation in GIS

In the data model GIS stream of posts we’ve only addressed the mechanics of the data model, up to now the assumption was that we were converting an empty schema (well, not exactly empty, it has tables and lookup values) from relational into spatial.  Today we’ll get into how to handle the case where there is spatial data stored in a relational database as (these are taken from here):

1. Coordinate pairs stored as numbers with associated projection/datum code
2. Listing of coordinate pairs  as with an associated sort order identifier
3. Coordinate Collections having different spatial references defined

Cases 1, 3, and 3 are very common in the PPDM data model, and Cases 2 and 3 are fairly common in PODS (not many standalone points in PODS that aren’t part of a line or actually an event).

Let’s treat Cases 1 and 2 as virtually the same thing — one or more coordinate pair that needs to be translated into geometry.  In one case we have points and in the other case we typically have lines (seismic lines and well directional surveys are great examples).

In a pure ESRI implementation we’d look towards the Point Object and the Array Object of the geoprocessing framework to supply us what we need for generating geometry.  In a less restrictive world (but still ESRI) like ArcObjects the we can use a Point or Polyline and make them Z and M aware as required.  In a more open world we gravitate towards Well Known Text (WKT) and Well Known Binary (WKT) or maybe even the geometry formats of osgeo.ogr.  We’ll stick with WKT for now since it is easier on the eyes and is handled by all the major RDMBSs in their spatial add-ins.

Since we are primarily concerned with points and lines the WKT forms we need to be familiar with are POINT(X Y) and LINESTRING(X Y Z M, X Y Z M…X Y Z M, X Y Z M).  It should be obvious how to proceed from this point but just in case it isn’t here’s how we proceed (in Python code and using PPDM):

curs.execute(“SELECT UWI, LONGITUDE, LATITUDE FROM WELL”)
wktResults = [(r[0], ‘POINT(%3.9f %3.9f)’%r[1:]) for r in curs.fetchall()]

And that simple piece of code handles the points for us, how you proceed from there depends on your desired approach, maybe write the results to a file for bulk load, or maybe use a Python compliant DB API to load the results straight into a spatially enabled table.  The approach for lines is much the same, all you need to do is query the coordinates and then serialize the coordinates into a string and wrap that in the LINESTRING keyword.

That’s really the easy part, but we haven’t done anything to handle spatial references on the coordinate pairs.  The easy case is where all the coordinates in a database are in the same coordinate reference system, of course this is rarely the case for PPDM instances (international exploration is one of the main reasons why) but predominantly the case in PODS instances.  So what to do?  Let’s at least assume that each coordinate pair is assigned (wrongly or rightly) an ESPG CRS value.  That’s a great start but we also need to know the target spatial reference and, equally as important, the transformation method.  Once we have all that information we’re in a position to begin thinking about how to handle.

That’s enough for one post, more on this later…