NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

0
1

Can you advice how can I filter city part nodes displayed on tiles (standard layer) with parent relation from OSM database?

I need to export hierarchy: city(city, town, village, hamlet in OSM) > cityPart(suburb, cityDistrict, administrative in OSM)

https://nominatim.openstreetmap.org/details.php?place_id=198526448 Zilina example

asked 24 Oct '19, 08:55

atom-systems's gravatar image

atom-systems
21226
accept rate: 0%

edited 24 Oct '19, 09:32


Nodes are not suitable for the upper levels of the hierarchy because you cannot find out which other nodes are "below" them. Nodes are only suitable for the lowest level of the hierarchy.

One possible way is to install your own copy of Nominatim and extract the pre-computed hierarchies from Nominatim either through the API or by querying the database directly.

What you can also do is use osm2pgsql to import OSM data for the region you are interested in into a local PostGIS database; you can modify the "style" file to only load "boundary" and "place" objects to speed up the process.

Then, you can build SQL queries that give you the hierarchy you want, for example to compute a list of cities and their suburbs

SELECT city.name, suburb.name 
FROM planet_osm_polygon city, planet_osm_point suburb
WHERE city.boundary='administrative' AND city.admin_level='8'
AND suburb.place='suburb'
AND st_contains(city.way, suburb.way);

And so on. (Note that this assumes cities are on admin level 8 which some larger cities might not be, and that suburbs are just points - they could also be polygons so you'd want to repeat the query above for polygonal suburbs, etc.)

permanent link

answered 24 Oct '19, 09:42

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k927201273
accept rate: 23%

It's working, thank you! Is there quick way how to add latitude and longitude to the select?

(24 Oct '19, 10:20) atom-systems
1

Quick for the database, just not quick for you typing it:

SELECT city.name, st_x(st_centroid(st_transform(city.way,4326))) as city_lon,
  st_y(st_centroid(st_transform(city.way,4326))) as city_lat,
  suburb.name,
  st_x(st_transform(suburb.way,4326)) as sub_lon,
  st_y(st_transform(suburb.way,4326)) as sub_lat

and then continue as above. The complexity arises from (1) having to convert the database coordinates to lat/lon with st_transform and (2) having to compute the centre point of the city polygon with st_centroid.

(24 Oct '19, 10:37) Frederik Ramm ♦

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×287
×205
×118

question asked: 24 Oct '19, 08:55

question was seen: 1,273 times

last updated: 24 Oct '19, 10:38

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum