George MacKerron: code blog

PostGIS has an st_askml function. This turns geometries into fragments of KML, and thus takes you most of the way to easy visualisation of spatial queries using Google Earth. But not all the way: these fragments have then to be assembled into a complete document.

I’ve written some wrapper and aggregate functions to automate this. They’re probably deeply inefficient — I wouldn’t advocate building your next web service on them — but for one-off eyeballing of query results I find them really useful.

The key functions are called as_kmldoc; you could see them as the missing aggregate versions of st_askml.

How’s it used?

as_kmldoc has three flavours, differing only in the number of arguments (arity). There are versions for only a geometry, a geometry plus a textual field (which becomes an associated <name> KML element), or a geometry plus two textual fields (which become the <name> and <description> in that order).

Example 1

I have a table of polygons corresponding to areas marked as parks in the OpenStreetMap data. I want to see some of these in Google Earth to check them against the satellite imagery. So I run:

select as_kmldoc(osgb36_polygon) from osm_green_spaces;

Then I paste the resulting KML string into Google Earth, and fiddle a little with the colour and transparency:

Example 2

I have a table of weather station locations, and associated names and codes, as used by Weather Underground. I can select all this data into a KML document by running:

select as_kmldoc(osgb_location, name, code) from wunderground_stations;

Which gives me:

Example 3

This one’s just showing off. Here I visualise the calculation of a kernel-weighted average — which I plan to post more about in future — using the Z dimension (height) to illustrate the relative weightings at different locations.

The complete query is a bit long and involved to reproduce here. But it makes use of one more new function, join_kmldocs, which enables you to combine any number of KML documents into one, by (slightly hackily) making each input document into a <Folder> element of the output document.

How do I get it?

To add these functions to your PostGIS database, run the following query:

create or replace function xml_escape(text)
returns text as $$
  select replace(replace(replace($1, '&', '&amp;'), '<', '&lt;'), '>', '&gt;');
$$ language sql immutable strict;
 
create or replace function kml_wrap(text)
returns text as $$
  select
    '<kml xmlns="http://www.opengis.net/kml/2.2"><Document>'
    || $1 || '</Document></kml>';
$$ language sql immutable strict;
 
create or replace function kml_concat(text, geometry)
returns text as $$
  select $1 || '<Placemark>' || st_askml($2) || '</Placemark>';
$$ language sql immutable strict;
 
create aggregate as_kmldoc(geometry) (
    sfunc = kml_concat,
    finalfunc = kml_wrap,
    stype = text,
    initcond = ''
);
 
create or replace function kml_concat(text, geometry, anyelement)
returns text as $$
  select $1 || '<Placemark><name>' || xml_escape(cast($3 as text)) || '</name>'
  || st_askml($2) || '</Placemark>';
$$ language sql immutable strict;
 
create aggregate as_kmldoc(geometry, anyelement) (
    sfunc = kml_concat,
    finalfunc = kml_wrap,
    stype = text,
    initcond = ''
);
 
create or replace function kml_concat(text, geometry, anyelement, anyelement)
returns text as $$
  select $1 || '<Placemark><name>' || xml_escape(cast($3 as text))
  || '</name><description>' || xml_escape(cast($4 as text))
  || '</description>' || st_askml($2) || '</Placemark>';
$$ language sql immutable strict;
 
create aggregate as_kmldoc(geometry, anyelement, anyelement) (
    sfunc = kml_concat,
    finalfunc = kml_wrap,
    stype = text,
    initcond = ''
);
 
create or replace function join_kmldocs(variadic text[])
returns text as $$
  select kml_wrap(
    replace(
      replace(
        array_to_string($1, ''),
        '<kml xmlns="http://www.opengis.net/kml/2.2"><Document>',
        '<Folder>'
       ),
       '</Document></kml>',
       '</Folder>'
    )
  );
$$ language sql immutable strict;

pgAdmin hint

I work with PostGIS mostly via pgAdmin. If you’re using as_kmldoc via pgAdmin, you might find it useful to change pgAdmin’s ‘Result copy quoting’ to ‘None’ in Preferences > Query Tool. That way, you can copy and paste from pgAdmin straight into Google Earth.