GPX download available on openstreetmap.la

I'm pleased to announce minor updates on openstreetmap.la: OpenStreetMap points of interest in Laos are now available for download in the popular GPS Exchange Format (GPX) and the roads Shapefile has been improved.

I considered providing GPX files as an additional download format since the launch of openstreetmap.la, but I didn't implemented it till last week. Contrary to the KMZ format, that always requires (e.g. PHP) scripting if you want customized styles, my goal was to create the GPX file without any scripting.

As often there are several ways how to solve a problem, I'd like to share how I did it, assuming the OpenStreetMap are already in a PostGIS database. As a (probably) daily user of OGR, I knew that OGR supports PostGIS as well as GPX, so it was the obvious way to have a deeper look at OGR's GPX driver.

Since GPX uses a fixed schema the attributes from the database has to be renamed according to the GPX schema. It was obvious how to name the attributes to get the <name>, <cmt> etc. elements. But from the OGR documentation I didn't understand how I have to name the link attribute, since the <link> element is nested and the GPX schema allows as many <link> elements as you want. I took a closer look at the code to see that OGR requires the first link named "link1_href", the second "link2_href" etc. Furthermore I wanted different symbols for the different OpenStreetMap features like restaurant, hotel etc. that required a nested CASE WHEN ... ELSE statement. Since the SQL statement was getting quite impressive, I decided to create a new database view called gpx_poi, instead of using the -sql OGR option or creating a new virtual OGR format.

CREATE OR REPLACE VIEW gpx_poi AS
SELECT "way",
CASE WHEN "name" IS NOT NULL THEN "name"
  ELSE CASE WHEN "amenity" IS NOT NULL THEN INITCAP(REPLACE("amenity",'_',' '))
    ELSE CASE WHEN "tourism" IS NOT NULL THEN INITCAP(REPLACE("tourism",'_',' '))
      ELSE ''
    END
  END
END AS "name",
CASE WHEN "amenity" IS NOT NULL THEN INITCAP(REPLACE("amenity",'_',' '))
  ELSE CASE WHEN "tourism" IS NOT NULL THEN INITCAP(REPLACE("tourism",'_',' '))
    ELSE ''
  END
END AS "cmt",
CASE WHEN "amenity" IS NOT NULL THEN INITCAP(REPLACE("amenity",'_',' '))
  ELSE CASE WHEN "tourism" IS NOT NULL THEN INITCAP(REPLACE("tourism",'_',' '))
    ELSE ''
  END
END AS "desc",
"link1_href",
"link1_text",
'text/html'::TEXT AS "link1_type",
CASE WHEN "tourism" = 'hotel' THEN 'Lodging'
  ELSE CASE WHEN "amenity" = 'post_office' THEN 'Post Office'
    ELSE CASE WHEN "amenity" = 'telephone' THEN 'Telephone'
      ELSE CASE WHEN "amenity" = 'toilets' THEN 'Restrooms'
        ELSE CASE WHEN "amenity" = 'school' THEN 'School'
-- etc. for all other symbols
        END
      END
    END
  END
END AS "sym"
FROM (SELECT "way","osm_id","name","amenity",
"tourism","natural","man_made","place",
'http://www.openstreetmap.org/browse/node/' || "osm_id" AS "link1_href",
'Node: ' || "osm_id" AS "link1_text"
FROM public.osm_en_point
UNION
SELECT ST_Centroid(way) AS "way","osm_id","name","amenity",
"tourism","natural","man_made","place",
'http://www.openstreetmap.org/browse/way/' || "osm_id" AS "link1_href",
'Way: ' || "osm_id" AS "link1_text"
FROM public.osm_en_polygon)
AS union_table

Having this gpx_poi database view, it's an easy ogr2ogr conversion:

ogr2ogr -f GPX -s_srs EPSG:900913 -t_srs EPSG:4326 pointsofinterest.gpx PG:"dbname='openstreet' user='openstreet'" gpx_poi

Just to make sure the GPX output is correct, I did also an XML validation of the output file:

xmlstarlet val --xsd http://www.topografix.com/GPX/1/1/gpx.xsd --err pointsofinterest.gpx

And finally the result, a screenshot from the waypoints on a Garmin device:

The second update on openstreetmap.la concerns the roads Shapefile. Last week when I worked on the routing in GRASS GIS I realized that the road reference number is missing in the roads Shapefile. Meanwhile the "ref" attribute is included.