OpenStreetMap Points Of Interest - it works!


So, taking features from OSM for healthcare: "pharmacy","hospital","dentist" and "doctors" (we'll leave "baby_hatch" for now as it's slightly scary…or maybe i'm just not enlightened enough!) I can do two sql statements, one for the ways and one for the nodes then UNION ALL them together to get a result set of ways and nodes.

We are competing with: nearest hospital = Children's University Hospital 862m nearest pharmacy = Gordons Chemist, Newry 811km (!!!!)


Results are:

79498864 928009126 amenity hospital Rotunda Maternity Hospital 256.708557
51776450 660713035 building hospital NULL 261.315159
582756469   amenity pharmacy Parnell Pharmacy 304.355369
51776463 660713097 building hospital NULL 322.308431
631808707   amenity pharmacy Health Express 333.650710
471861761   amenity pharmacy Hickey's Pharmacy 338.425801
279912239   amenity pharmacy Hamilton Long Pharmacy 397.367718
768516996   amenity doctors Jervis Medical Centre 469.036586
249430783   amenity pharmacy Traceys 574.297052
567713114   amenity doctors Dame Street Medical Centre 597.042663
567713109   amenity pharmacy City Pharmacy 606.547296
559854612   amenity pharmacy Medipharm 653.331539
249430226   amenity pharmacy NULL 694.285493
41779231 515098838 amenity hospital Children's University Hospital Temple Street 706.971984
33005187 371973866 amenity hospital Mater Private Hospital 829.883832

The second object detected is a hospital building inside the grounds of Rotunda Maternity Hospital. Buildings often won't be contained within grounds, so I can't distinguish. Perhaps when there is a stanalone building then it has a name?

The sql is:

SELECT wt.way_id as way, osm_way_node_refs.node_id as node, wt.k as key, wt.v as value, name_tag.v AS site_name, min(Distance("Geometry" ,GeomFromText('POINT(-6.2626 53.34942)'))*100000) AS dist
FROM osm_way_tags wt, osm_way_node_refs, osm_nodes
LEFT OUTER JOIN osm_way_tags name_tag
ON wt.way_id = name_tag.way_id AND name_tag.k="name"
WHERE wt.way_id = osm_way_node_refs.way_id
AND osm_way_node_refs.node_id = osm_nodes.node_id
AND wt.k in ("amenity","building") AND wt.v in ("pharmacy","hospital","dentist","doctors")
GROUP by way


SELECT "" as way, n.node_id as node, wt.k as key, wt.v as value, name_tag.v AS site_name, Distance("Geometry" ,GeomFromText('POINT(-6.2626 53.34942)'))*100000 AS dist
FROM osm_node_tags wt, osm_nodes n
LEFT OUTER JOIN osm_node_tags name_tag
ON wt.node_id = name_tag.node_id AND name_tag.k="name"
WHERE wt.node_id = n.node_id
AND wt.k in ("amenity","building") AND wt.v in ("pharmacy","hospital","dentist","doctors")

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