4

I have a relation representing all the counties in my country and the other one containing all the districts - e. g. each and every county is composed of several districts. How do I select them with spatial query? I tried with

select nazorp from kraje as k, orp_wgs as o WHERE ST_Within(o.geom,k.geom) AND
k.nazev = 'Liberecký'

but had wrong results returned (well, they are probably not wrong, they are just not what I expected them to be). ST_Within only returns districts that don't share a boundary with the county, but I need to get all the districts within the county. Is that possible? I haven't found any built-in function suitable for my needs yet.

2

Use ST_Contains, which will not reject the border if you are working with polygons (a linestring on the boundary of the original would fail, but that's a special case).

edit:

select nazorp from kraje as k, orp_wgs as o WHERE ST_Intersects(o.geom,k.geom) 
AND k.nazev = 'Liberecký'
AND ST_Area(ST_Intersection(o.geom,k.geom)) != ST_Area(o.geom)

This was just a check that the data is sane. Nothing should be returned, but if it is, treat those districts with care. To get what you initially wanted, just omit the last condition (faster) or change the comparison to an equality check.

  • That gives me the same result as a previous example. Only polygons not touching the boundaries of containing polygon are selected as you can see here – Michal Zimmermann Oct 4 '12 at 10:19
  • Are you sure the boundaries align properly? Check with ST_Covers(o.geom, k.geom) on the other districts in the same county — you should get only true results for ST_Contains to work. Any false result means that the boundaries are not overlapping perfectly and the district polygon actually extends outside of the county. You can also try with checking just ST_Intersects, but I'm afraid you will hit the same problems. – lynxlynxlynx Oct 4 '12 at 12:21
  • I get the same results even with very simple geometries I created for test purposes - if I create a feature sharing a boundary with a containing polygon (with snapping enabled) and call St_Contains/St_Within function, it always returns false. – Michal Zimmermann Oct 4 '12 at 12:36
  • Sounds like a bug then. I'll update the answer with another solution. – lynxlynxlynx Oct 4 '12 at 12:41
  • With this query I get all the districts within the county, BUT also all the districts outside which are touching its boundary. – Michal Zimmermann Oct 10 '12 at 6:49
2

It's tricky to use ST_Within if the boundaries are not exactly shared. A workaround is to take a point inside your districts with ST_PointOnSurface and see if this point is inside a county.

Your query should look like:

select nazorp from kraje as k, orp_wgs as o 
WHERE ST_Within(ST_PointOnSurface(o.geom),k.geom) AND
k.nazev = 'Liberecký'
  • I just noticed that the question was asked almost four years ago... – thibautg Jun 9 '16 at 9:22
0

It sounds like you need a solution that is robust to alignment issues. Try

SELECT nazorp from kraje as k, orp_wgs as o 
WHERE ST_Intersects(o.geom,k.geom) AND NOT ST_Touches(o.geom,k.geom)
AND k.nazev = 'Liberecký'

NOT ST_Touches() will exclude districts that only share a boundary, and don't have any overlap.

If this still returns some unwanted districts because of alignment issues, you can add a further condition:

AND ST_Area(ST_Intersection(o.geom,k.geom))>some_tolerance

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.