[GIS] How to find the nearest city for a POI in a OpenStreetMap/PostGIS database

openstreetmappoint-of-interestpostgis

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.

Best Answer

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

Related Solutions

[GIS] Finding nearest point from POI in PostGIS

Bring your data into PostgreSQL with something like mysql_fdw, or other methods.

Once you have this table, add a geography column, and populate the new column:

ALTER TABLE places ADD COLUMN geog geography(Point,4326);
UPDATE places SET geog = ST_MakePoint(longitude, latitude);

Now select the nearest 10 places that are within 100 kms:

SELECT places.*, ST_Distance(geog, poi)/1000 AS distance_km
FROM places,
  (select ST_MakePoint(-90,47)::geography as poi) as poi
WHERE ST_DWithin(geog, poi, 100000)
ORDER BY ST_Distance(geog, poi)
LIMIT 10;

PostGIS PostgreSQL Queries – Effective Methods for OSM Data Handling

The ids of the elements that compose the relation should be in the "parts" column of the planet_osm_rels as an array. The ids can be nodes of planet_osm_nodes, ways of planet_osm_ways, or polygon of planet_osm_polygon, and you will have to gather them by selecting them with the id (or osm_id) column of those tables. For example for polygons, that would be something like that:

WITH wanted_rel AS (
    SELECT 
            id, parts,tags 
        FROM planet_osm_rels 
        WHERE 'IBGE:GEOCODIGO' = ANY(tags) AND '4106902' = ANY(tags)
)
SELECT 
        id, geom
    FROM planet_osm_polygon pop
    INNER JOIN wanted_rel wr
    ON pop.osm_id IN wr.parts;

But manipulating imports of osm2pgsql can be a little difficult because it's not really made for analysis, just display. I suggest you to use for example imposm to import the wanted data in postgis, so you will have directly nodes, linestring and polygon as geom in a coherent table, and you can select fields that will have a given columns (instead of always having to use hstore). Plus some fields like speed, oneway, or boolean and numeric fields in general have a specific treatment on the import to clean the field and correct error when possible.

If relation are what you want, you should use imposm3.

Also, for path between points this is something completly different, and you will need to use specialized tools otherwise the computation time will quickly explode if you try to do it yourself. You should at tools like pgrouting for something in postgis, or an external tool like osrm or graphhopper that works with OSM data. You also have other paying services of course, if you don't want to do it yourself.

Best Answer

Related Solutions

[GIS] Finding nearest point from POI in PostGIS

PostGIS PostgreSQL Queries – Effective Methods for OSM Data Handling

Related Question