OSM file with city null lines (downloaded from geofabrik.de)

Question

Hello, I downloaded an OSM file from my area (Italy islands) to process it with Java and present an application to my organization. The purpose is, given a point on the map where a criminal activity is in progress, to find in the vicinity (e.g. 500 meters or 1 Km) all the POIs that represent sensitive targets (banks, schools, jewelers, etc.) that can become secondary targets for crimes. For now, I need this information in data format and not as a map. The problem is that in the OSM file I have the city field is often null and from the geographic coordinates of the city limits I cannot make an initial list of all the POIs present. At this point I thought about changing cities hoping that there is one that has a more complete OSM file.

Accepted Answer

You cannot count on the addr:city field being set. You have to work, as you say, with the geographic coordinates of the city boundary which will often be present in OSM. If you load the OSM data into a PostgreSQL database with osm2pgsql, creating the list of POIs in a city is trivial. First, find the relation ID of the city,

select osm_id,name 
from planet_osm_polygon 
where boundary='administrative' and admin_level=8;

(Add an "and name like '%something%' to reduce the list to certain names only. In some countries cities might have a different admin_level; sometimes this also depends on the city size. Check the boundary=administrative wiki page for details.)

Once you have the ID of the boundary (let's say it was -41485 which is the city of Rome), you can do something like

select * from planet_osm_point p,     
   planet_osm_polygon q
where q.id=-41485 and st_contains(q.way, p.way);

You could also add something like "and amenity in ('bank','post_office', ...)" to that query to limit the results.

OSM file with city null lines (downloaded from geofabrik.de)

Follow this question

Related questions