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?

  • Why don't you simply compute the postal_code polygon centroids, and report all centroids inside of the city boundary polygon? – AndreJ Jan 19 '15 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 Bozic Feb 3 '15 at 18:14
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 : http://postgis.org/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 http://postgis.org/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, privacy policy and cookie policy

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