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 http://www.gaia-gis.it/spatialite/binaries.html
* I needed pysqlite for my python 2.5 installation from http://code.google.com/p/pysqlite/downloads/list
* 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 poi.id = poitag.poi
AND poitag.tag = tag.id
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 http://www.gaia-gis.it/spatialite-2.4.0-4/binaries.html 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
inserted 2207 ways
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
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).
* my previous post First steps towards open source OpenStrretMap Points Of Interest POI data
* my next post OpenStreetMap Points Of Interest - it works!