First steps towards open source OpenStreetMap Points Of Interest POI data


I'm working with some European data at the moment, part of which is POI data for "nearest hospital", "nearest educational facility", etc. and the data is quite poor. I suspect that it originally sourced from NavTec and hence "Yellow Books". I also suspect that the data in OpenStreetMap will be orders of magnitude more accurate.

My first port of call was to examine the attributes used in OSM. To do this I download an area of central Dublin into JOSM and look at the raw data. Taking hospitals as an example I can quickly see that these are encoded in three different ways:

I've then taken a look at SpatiaLite which is a really lightweight file-based database server. This would be great for packaging with an app, but is also good for quick experiments. I may need to go up to PostGIS if it isn't up to the job.

I downloaded the spatialite-gui binary for Windows, unzipped it and ran it.

From GeoFabrik I downloaded


In the SpatialLite GUI I created a new database and then went to "Load Shapefile", I picked a .shp file from my downloaded data and set the SRID to 4326 (which is the id in the spatial_ref_sys table for WGS-84) and the charset to UTF-8. It then imported in a matter of seconds. This gave me a sql table that I could query using extra functions such as Distance()

I got a WGS-84 lat/lon for my search point by right-clicking on Google Maps and doing a "Drop LatLng Marker" (I can't remember whether this is an opt-in feature). I have since worked out that SpatialLite expects the coordinated to be the other way round: lon/lat and not lat/lon.

SELECT ROWID, "PK_UID", "osm_id", "name", "type"
, Distance("Geometry" ,GeomFromText('POINT(-6.2626 53.34942)'))*100000 AS dist
FROM "points"
WHERE "type" = "hospital"

gives me a result set starting with: osmid=641802162, name=Dublin Dental Hospital, distance 1289m

When I query the buildings I get: osmid=51776450, name=NULL, distance 261m…which is the first building of the Rotunda Maternity Hospital.

What I don't know is where to get hold of the area data. Is it in the road shapefile?! I think not. So where would it be?

Enrico Zini's post Importing OSM nodes into Spatialite

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