KML in SQL

Introduction

We’ve been working with Google Earth KML files for a while, and while I’m sure there are plenty of articles detailing working with XML documents in SQL, I haven’t seen one that discusses KML and KML documents. Since KML is just structured XML, constructing Google Earth documents is just a matter of aggregating xmlelements into the KML schema.

In this post, we’re discussing using SQL XMLTypes to generate KML documents. The scripts created perform examples based on Oracle (10.2g) and PostgreSQL (9.3). For sample XML data we’ll be converting a GPX file to a KML document.

Prerequisites

First, a couple of tricks when requesting the our GPX data from a URI.

For Oracle we’re selecting from a straight HTTPURITYPE request, so this will be easiest to test with.

In PostgreSQL we’ll need to create a foreign data wrapper with an XML callback (www_fdw). The script to load from our foreign table www_fdw_server_gpx can be found here, and the www_fdw extension can be found here (Windows build).

Oracle Example

WITH DATA AS
 (SELECT EXTRACTVALUE (VALUE (tp), '*/@lon') x1,
         EXTRACTVALUE (VALUE (tp), '*/@lat') y1,
         EXTRACTVALUE (VALUE (tp), '*/ele', 'xmlns="http://www.topografix.com/GPX/1/1"') z,
         TO_DATE (EXTRACTVALUE (VALUE (tp), '*/time', 'xmlns="http://www.topografix.com/GPX/1/1"'),'YYYY-MM-DD"T"HH24:MI:SS"Z"') d,
         EXTRACTVALUE (VALUE (tp), '*/speed', 'xmlns="http://www.topografix.com/GPX/1/1"') speed
FROM TABLE (
    XMLSEQUENCE (
      EXTRACT (
        HTTPURITYPE ('http://thinkspatial.com.au/media/2012-05-02.gpx').getxml (),'//trkpt',
  'xmlns="http://www.topografix.com/GPX/1/1"'
      )
     )
) tp)
SELECT XMLELEMENT ("kml",
   XMLATTRIBUTES ('http://www.opengis.net/kml/2.2' AS "xmlns",
  'http://www.google.com/kml/ext/2.2' AS "xmlns:gx",
  'http://www.opengis.net/kml/2.2' AS "xmlns:kml",
  'http://www.w3.org/2005/Atom' AS "xmlns:atom"
 ),
   XMLELEMENT ("Document",
     XMLELEMENT ("name", 'GPX Conversion'),
     XMLELEMENT ("open", 1),
     XMLELEMENT ("Folder",
       XMLELEMENT ("name", 'Waypoints'),
       XMLAGG (
         XMLELEMENT ("Placemark",
           XMLELEMENT ("Point",
             XMLELEMENT ("coordinates", x1 || ',' || y1)
           )
         )
       ) -- end xmlagg
     )  -- end folder
   )
 ).getclobval() doc
  FROM DATA

PostgreSQL Example

COPY (
WITH DATA AS (
    select lat,lng,ele,time,speed from www_fdw_server_gpx
)
SELECT  xmlelement(name "kml",
        xmlattributes ('http://www.opengis.net/kml/2.2' AS "xmlns",
              'http://www.google.com/kml/ext/2.2' AS "xmlns:gx",
              'http://www.opengis.net/kml/2.2'AS "xmlns:kml",
              'http://www.w3.org/2005/Atom' AS "xmlns:atom"),
        xmlelement(name "Document",
            xmlelement(name "name",'GPX Conversion'),
            xmlelement(name "open",1),
            xmlelement(name "Folder",
                xmlelement(name "name", 'Waypoints'),
                xmlagg(
                    xmlelement(name "Placemark",
                        xmlelement(name "Point",
                            xmlelement(name "coordinates", lng||', '||lat)
                        )
                    )
                )
            ) -- end of folder
        )
    )
FROM DATA
) To 'C:\Temp\gpx.kml'; -- or an appropriate location

So to generate ordinary KML documents, the SQL is pretty straight forward.

Of course, with a little more effort you can get some dynamic and useful KML documents. Check out this KML document that was created with this SQL code (Oracle version only). Click on the “Double Click me!” video tour in the left-hand panel. Enjoy the drive!

How to start Google Earth Tour