# How to find the nearest city for a POI in a OpenStreetMap/PostGIS database?

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.

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

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

-
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
``````
-