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


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) Zilina example

asked 24 Oct '19, 08:55

atom-systems's gravatar image

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

FROM planet_osm_polygon city, planet_osm_point suburb
WHERE city.boundary='administrative' AND city.admin_level='8'
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 ♦
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

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

SELECT, st_x(st_centroid(st_transform(city.way,4326))) as city_lon,
  st_y(st_centroid(st_transform(city.way,4326))) as city_lat,,
  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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 24 Oct '19, 08:55

question was seen: 1,295 times

last updated: 24 Oct '19, 10:38

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