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.

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
6
  • 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, 2012 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, 2012 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, 2012 at 12:36
  • Sounds like a bug then. I'll update the answer with another solution.
    – lynxlynxlynx
    Oct 4, 2012 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, 2012 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ý'
Improve this answer
1
  • I just noticed that the question was asked almost four years ago...
    – thibautg
    Jun 9, 2016 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
Improve this answer

Your Answer

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

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