Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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.

share|improve this answer
    
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

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
share|improve this answer

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ý'
share|improve this answer
    
I just noticed that the question was asked almost four years ago... – thibautg Jun 9 at 9:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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