Next steps in open source OpenStreetMap Points Of Interest POI data


My journey for OSM Points Of Interest has taken me firstly to Enrico Zini's post "Importing OSM nodes into Spatialite"

* as I am using Windows (not by choice, they made me do it!) I needed to download and install libspatialite, proj, geos and libiconv dlls from

* I needed pysqlite for my python 2.5 installation from

* I also needed simplejson which I got via easy_install

* The script needed to point to the .dll and not an .so -> self.db.execute("SELECT load_extension('libspatialite-1.dll')")

* dict() doesn't like it when a key doesn't exist, so if there is an OSM node with no tags["name"] the program will error. Instead, I used defaultdict() with: from collections import defaultdict and self.tags = defaultdict(str)

SELECT poi."id", tag."name" , Distance("geom" ,GeomFromText('POINT(-6.2626 53.34942)'))*100000 AS dist , poi."name", "data" FROM "poi", "poitag", "tag" WHERE = poitag.poi   AND poitag.tag =   AND tag."value" = "cafe" ORDER BY dist

Which gives me Dublin Dental Hospital as my first hit. Again, I am faced with the same difficulty in that amenities could either be an OSM node or a circular OSM way. It gets more complicated to process a Way as it references the Nodes that make it up, and Enrivo's script is about parsing the xml input stream.

In Spatialite 2.4 rc4 there is a new tool called spatialite_osm_raw. This was really easy to use to import OSM XML data direct into a Spatialite database.


C:\Users\jez.nicholson.ARGYLLENVIRO\Desktop>spatialite_osm_raw -o Dublin.osm -d dub_osm.sqlite SQLite version: 3.7.3 SpatiaLite version: 2.4.0   inserted 8239 nodes 4992 tags inserted 2207 ways         7068 tags         10879 node-refs inserted184 relations         688 tags         903 node-refs         6573 way-refs         0 relation-refs

A quick bit of SQL:

SELECT wt.way_id, osm_way_node_refs.node_id, wt.k, wt.v, name.v , Distance("Geometry" ,GeomFromText('POINT(-6.2626 53.34942)'))*100000 AS dist FROM osm_way_tags wt, osm_way_tags name, osm_way_node_refs, osm_nodes WHERE wt.way_id = osm_way_node_refs.way_id AND osm_way_node_refs.node_id = osm_nodes.node_id AND wt.k="amenity" AND wt.v="hospital" AND wt.way_id = name.way_id AND name.k="name" ORDER BY dist  

...and I can list hospitals which are defined as ways: Rotunda Maternity Hospital at 256m away at it's nearest point and Children's University Hospital Temple Street 706m, which is better than the result of "Children's University Hospital at 862m" which I got from Navtec.

Next step is to combine Node and Way POIs. I feel that I will need to create a new sql table that mixes point and polygon geometries (if it doesn't do that already).

<h2>see also</h2>

* my previous post First steps towards open source OpenStrretMap Points Of Interest POI data

* my next post OpenStreetMap Points Of Interest - it works!

openstreetmap hacking
It's All In The Game blog (c) 2005-16 by Jez Nicholson