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?
Best Answer
I had a similar problem an I solved it with the following statement:
Description:
ST_INTERSECTS
also includes touches see : https://postgis.net/docs/ST_Intersects.html so you have to exclude these surrounding postcodes withST_TOUCHES
. This function returnTRUE
if an item touches the other and there are no intersection with the inner area. See https://postgis.net/docs/ST_Touches.html . SoST_TOUCHES(city.geometry, city.geometry)
would returnFALSE
andST_TOUCHES(city.geometry, cityNeighbour.geometry)
would returnTRUE
whileST_INTERSECTS
always returnsTRUE
.