BY G2 INTEGRATED SOLUTIONS

One of the best things about PODS is that the standard does not depend on any particular GIS technology. The software engineer is left to decide exactly how PODS should be spatialized. As it turns out, this can be a rather complicated process.  One way to quickly visualize a PODS centerline is to extract the data from the coordinate table, and create a KML file.  This KML file can then be easily viewed in Google Earth or many other free and non-free GIS applications. 

This post is a little long, if you want to just see how it works, skip to the end and watch the video (the video is a bit long as well, feel free to skip around it)!Please note that I did all of this in Oracle 10g, the SQL is mostly compatible with SQL Server, but will require a few changes in order to work correctly.

To begin, we first make a query connecting the route table, through series, station_point, and location to the coordinate table.  Some may say that the join through the location table is not required, and technically it is not.  However, some versions of PODS have extra meta-data stored in the location table that you may want to access, so for now we will leave it in.  Now I add the column I am interested in to the select clause, and an order by measure:

selectr.route_id,
       r.description,
       s.series,
       sp.station,
       sp.measure,
       c.x_coord,
       c.y_coord,
       c.z_coord
   fromroute r, series s, station_point sp, location l, coordinate c
  wherer.route_id = ???
    ands.route_id = r.route_id
    ands.current_indicator_lf = ‘Y’
    andsp.series_id = s.series_id
    andl.location_id = sp.location_id
    andc.location_id = l.location_id
    andc.current_indicator_lf = ‘Y’
  order by sp.measure

Don’t forget your current_indicator_lf flags in the query, and to replace the ??? with the route id!

Some of you may be ready to point out some issues about projections and coordinate systems at this point.  The main assumption that I am making here is the the coordinates (latitudes and longitudes) are stored in WGS 1984, the official projection for a KML file.  If your data is in a different projection you will need to do some preparation work before drawing the KML file.

The next part involves adjusting the query to build the XML that a KML is made up of.  I will not cover the details of the KML specification in this post, but you can get a good description here:http://code.google.com/apis/kml/documentation/kml_tut.html

We first need to construct the point XML tag, which will represent the location.  This takes the form:

<Point><coodinates>LONG,LAT,ELEV</coordinates></Point>

So we can add a column to our select clause that creates this string:

‘<Point><coordinates>’||c.x_coord||’,’||c.y_coord||’,’||c.z_coord||’</coordinates></Point>’ as KML_POINT

This will generate the location data needed for the KML file, now we need to create the rest of “Placemark” tag, which represents the location of each point, like this:

<Placemark><name>A Name</name><description>a Description</description><Point …/></Placemark>

Where the <Point …/> is the same as we defined above.  We can generate this tag by adding this to the Select clause:

‘&lt;Placemark&gt;&lt;name&gt;’||s.series||’,’||sp.station||’&lt;/name&gt;&lt;description&gt;’||sp.measure||’&lt;/description&gt;’||’&lt;Point&gt;&lt;coordinates&gt;’||c.x_coord||’,’||c.y_coord||’,’||c.z_coord||’&lt;/coordinates&gt;&lt;/Point&gt;’||’&lt;/Placemark&gt;’ as KML_PLACEMARK

One more step to make a KML file, we need to opening and the closing parts of the file.  To do this, we will simply add the following lines at the beginning:

<?xml version=”1.0″ encoding=”UTF-8″?>
 <kml xmlns=”http://www.opengis.net/kml/2.2″>
 <Document><name>Route KML</name>
 <Folder><name>A route</name>

The results from this query in the middle:


select‘<Placemark><name>’||s.series||’,’||sp.station||'</name><description>’||sp.measure||'</description>’||'<Point><coordinates>’||c.x_coord||’,’||c.y_coord||’,’||c.z_coord||'</coordinates></Point>’||'</Placemark>’ as KML_PLACEMARK
   fromroute r, series s, station_point sp, location l, coordinate c
  wherer.route_id = ???
    ands.route_id = r.route_id
    ands.current_indicator_lf = ‘Y’
    andsp.series_id = s.series_id
    andl.location_id = sp.location_id
    andc.location_id = l.location_id
    andc.current_indicator_lf = ‘Y’
  order bysp.measure

and the following lines at the end:

</Folder>
 </Document>
 </kml>

You can of course get very fancy with the KML and the formatting.  You could also create a polyline or linestring instead of a series of points, but I will leave that for another time!  When building the KML file the first time, it is easy to get a tag messed up, you can use this website (http://kmlvalidator.com/) to upload and validate your KML as you are creating it.  This site does a really good job, but it is very strict so take the feedback with a grain of salt.  Another way to validate things is to simply validate the XML structure.  There are many sites out there to do that, the one I normally use is: http://validator.w3.org/.

Now simply save the file as a KML and open in Google Earth!

Example KML File for Download.