Connecting planet_osm_polygon and planet_osm_point

General talk
1

I have somewhat of a newbie question:

How can I relate entries in the polygon table with entries in the points table in a reliable way?

A concrete example: getting all major german cities and their polygons. My naive approach:

This query works great for getting all 83 major cities:

SELECT name FROM planet_osm_point WHERE place = 'city'

When using the result in a polygon query though…


SELECT name
FROM planet_osm_polygon
WHERE
    boundary='administrative' AND
    name in (SELECT name FROM planet_osm_point WHERE place = 'city')
;

… the result contains many duplicates:


Berlin
Potsdam
Magdeburg
Magdeburg
Magdeburg ...

I realize, that there are different admin_level’s which I would have to restrict as well.

I am looking for insights into how I can connect planet_osm_polygon and planet_osm_point more systematically.
Maybe using osm_ids or something similar.

Thank you for reading :slight_smile: