9

I'm interested in finding additional data for the POIs in OSM database.

Currently I can use the following SQL for getting the name+long+lat for all the POIs

SELECT name, 
         x(transform(way, 4326)) AS lon, 
         y(transform(way, 4326)) AS lat
  FROM planet_osm_point 
  WHERE tourism='museum'
UNION
  SELECT name, 
         x(centroid(transform(way, 4326))) AS lon, 
         y(centroid(transform(way, 4326))) AS lat
  FROM planet_osm_polygon 
  WHERE tourism='museum'

I would also like to have the name of the nearest town/city or the town/city the POI is situated in.

1
  • Do you already have a reverse geocoding (location to name) system set up? – BradHards Dec 31 '12 at 3:43
3

not quite the answer I wanted, but usable in an application: create a query to Nominatim

http://nominatim.openstreetmap.org/search?q=45.8364043,24.8345179&format=xml&addressdetails=1

1
  • If this answers your question, please mark it as the answer (grey checkmark below vote count). – Chad Cooper Jan 5 '13 at 14:34
0

Answer pulled from a related question:

Pretty easy assuming you have the lat/lngs.

  1. Convert lat/lng to the osm data srid
  2. Compute the distance to all POIs that you care about in the database.
  3. Pick the row with the smallest distance.
  4. Additionally, you may want to limit your search to a radius of interest (say 5 KM..)

As an example, let's find 5 closest petrol stations near our POI.

SELECT osm_id,
       name,
       brand,
OPERATOR,
       ST_Distance(barabara_point.way, st_transform( st_setsrid(st_makepoint(36.768676, -1.289927), 4326), 900913)) AS distance
FROM barabara_point
WHERE ST_DWithin(barabara_point.way, st_transform( st_setsrid(st_makepoint(36.768676, -1.289927), 4326), 900913), 5000)
  AND amenity = 'fuel'
ORDER BY distance;

Resultset

-[ RECORD 1 ]--------------
osm_id   | 1334386299
name     |
brand    | Kobil
operator |
distance | 244.544617525396
-[ RECORD 2 ]--------------
osm_id   | 915386940
name     | BP
brand    |
operator |
distance | 1012.76398108684
-[ RECORD 3 ]--------------
osm_id   | 1916188670
name     | Shell
brand    |
operator |
distance | 1160.48349020106
-[ RECORD 4 ]--------------
osm_id   | 558923017
name     | Kenol
brand    |
operator |
distance | 1310.22002620899
-[ RECORD 5 ]--------------
osm_id   | 30092081
name     | Caltex
brand    |
operator |
distance | 1419.61102322244

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.