Get postal codes of a city in OSM via postgis/postgresql

Question

So, I'm trying to get the postal codes of a city (polygon). Unfortunately, I never get consistent results with my code:

SELECT
a.name,
b.tags->'postal_code'

FROM planet_osm_polygon a
JOIN planet_osm_polygon b ON ST_Intersects(a.way, b.way)

WHERE a.name = 'cityname'
AND   b.boundary = 'postal_code';

With ST_Intersects I get all valid postal codes plus a few non-valid surrounding ones. I also tried a few other functions, like ST_Crosses, ST_Contains, ST_Within, but never got a set of valid postal codes. Is there a best practice out there to solve this?

Answer 1

I had a similar problem an I solved it with the following statement:

SELECT
  city.name,
  postcode.tags->'postal_code'

FROM planet_osm_polygon postcode
JOIN planet_osm_polygon city ON ST_INTERSECTS(city.geometry, postcode.geometry)  
  AND NOT ST_Touches(city.geometry, postcode.geometry)

WHERE a.name = 'cityname'
  AND   b.boundary = 'postal_code';

Description: ST_INTERSECTS also includes touches see : https://postgis.net/docs/ST_Intersects.html so you have to exclude these surrounding postcodes with ST_TOUCHES. This function return TRUE if an item touches the other and there are no intersection with the inner area. See https://postgis.net/docs/ST_Touches.html . So ST_TOUCHES(city.geometry, city.geometry) would return FALSE and ST_TOUCHES(city.geometry, cityNeighbour.geometry) would return TRUE while ST_INTERSECTS always returns TRUE.