0

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?

2
  • Why don't you simply compute the postal_code polygon centroids, and report all centroids inside of the city boundary polygon?
    – AndreJ
    Jan 19, 2015 at 12:11
  • Haven't tried it yet, but I guess it won't work in all cases, since I want to run this all over Germany. Centroids might be outside the city boundaries. Will try and keep u posted.
    – Patrick
    Feb 3, 2015 at 18:14

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.