Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
Do you already have a reverse geocoding (location to name) system set up? –  BradHards Dec 31 '12 at 3:43

2 Answers 2

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

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

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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