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):
- Coordinate pairs stored as numbers with associated projection/datum code
- Listing of coordinate pairs as with an associated sort order identifier
- 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…
Dexterity of the hands and mind, and words per minute, are these part of the skills that were one time considered laughable by the elders but now are manifesting themselves as vital skills in the ability to compete in today’s knowledge market?
Once again, I’m at the airport, wired into the hotspot, setting up my Linux machine, writing emails, browsing a website, and biting the sides of my cheeks to keep from laughing. The guy next to me is definitely old school, probably even had a secretary at one point in his life. The markings on his bag, laptop, and login splash screen show that he is in the game of engineering consulting (with a rather large public engineering company). Engineering consulting is that wonderful world where a client gets dinged for the inexperience and inefficiencies of the staff, where timelines are guidelines, and delivery rhymes with “change orders”, time is rounded to the 5 minute interval, and charge codes are needed before anything can get done (including make a photocopy).
So why am I laughing, well given this person’s age I would expect him to be a person with seniority and experience (which means tres cher). Yet watching him type with just one finger on just one hand — come on — what era are we living in? Yes, knowledge and experience are important in the consulting world, but isn’t the ability to communicate (aka write) equally important, especially in the realm where things must be documented and explained? Wouldn’t you expect…one second, he just sped up, now at 15 characters per minute, nope, I’m wrong, that was just some pounding on the backspace key…that you (as a client especially) wouldn’t be slammed with invoices that simply cover off the inability for someone to type?
Gotta shutter down, boarding time for my flight, I’ll get back to more technical things in the coming weeks.
