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:
select r.route_id,
r.description,
s.series,
sp.station,
sp.measure,
c.x_coord,
c.y_coord,
c.z_coord
from route r, series s, station_point sp, location l, coordinate c
where r.route_id = ???
and s.route_id = r.route_id
and s.current_indicator_lf = 'Y'
and sp.series_id = s.series_id
and l.location_id = sp.location_id
and c.location_id = l.location_id
and c.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.htmlWe 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:
'<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
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
from route r, series s, station_point sp, location l, coordinate c
where r.route_id = ???
and s.route_id = r.route_id
and s.current_indicator_lf = 'Y'
and sp.series_id = s.series_id
and l.location_id = sp.location_id
and c.location_id = l.location_id
and c.current_indicator_lf = 'Y'
order by sp.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.