Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I need to build a PostGIS query that would select every polygon inside an area the river is flowing through.

I'd better describe with an example below: There is a river (blue line) flowing through several violet polygons (selected in yellow). Both of the yellow polygons are crossed by the green one (labeled Žulová, which is a boundary of a county), but the river actually flows through the only one of them (labeled 2-04-04-044) and the other one is only touched by the end point of the river.

The query I have so far looks like this:

SELECT DISTINCT 
        poradi 
        ,ST_Distance(ST_StartPoint(ST_LineMerge(toky.geom))
        ,ST_Centroid(pov.geom)) AS dist
FROM 
        povodi_iv AS pov
        ,cr_osy_toku_snap_2m AS toky
        ,$table
WHERE 
        toky.gid = $gid // river id
    AND 
             $table.uid = $id // county id
    AND 
             ST_Intersects($table.geom, pov.geom)
    AND 
             ST_Intersects(toky.geom, pov.geom)
ORDER BY 
        dist ;

But it gives me the result you see on the picture.

Could anyone help me out to build a query that would return only the violet polygons that are both crossed by county boundary and flown through by a river?

Example of what I want to achieve

share|improve this question
2  
I think the function you're looking for is ST_Crosses –  nickves Jan 24 '13 at 12:34
    
I tried this with no success. SELECT DISTINCT poradi, ST_Distance(ST_StartPoint(ST_LineMerge(toky.geom)),ST_Centroid(pov.geom)) AS dist FROM povodi_iv AS pov, cr_osy_toku_snap_2m AS toky, $table WHERE toky.gid = $gid AND $table.uid = $id AND ST_Intersects($table.geom, pov.geom) AND ST_Crosses(toky.geom, pov.geom) ORDER BY dist Any hints? –  zimmi Jan 24 '13 at 14:34
    
so your problem is that your query returns polygons that are touched by the river and not cross through them? –  nickves Jan 25 '13 at 8:46
    
Exactly. I need only polygons that river goes through to be returned, not the one that are touched, but not crossed. –  zimmi Jan 25 '13 at 11:27
    
How sure you are the features only touches each other? I think that if you look really close you will see that they intersect somehow, even by a tiny fragment, and thus giving you that result. –  nickves Jan 25 '13 at 15:40
show 1 more comment

2 Answers 2

Well check for the "kind of intersection". Translating your predicate below to human language:

WHERE 
        toky.gid = $gid // river id
    AND 
             $table.uid = $id // county id
    AND 
             ST_Intersects($table.geom, pov.geom)
    AND 
             ST_Intersects(toky.geom, pov.geom)

Want data where the river toki has the identifier - toky.gid = $gid - and also there is a county which must have code equals to - $table.uid = $id. The county geometry must have "contact" with the pov and the river must have "contact" with a county - AND ST_Intersects(toky.geom, pov.geom).

You did not requested which type of "contact" you want. You need to say that also the river must have a contact of kind "pass inside" of pov.geom.

Maybe this addition solves:

AND st_geometrytype( st_intersection(toky.geom, pov.geom) ) IN ( 'ST_Polygon', 'ST_MultiPolygon' )
share|improve this answer
add comment

If you want to exclude the polygons, which only touch the river, maybe it helps, if you write:

WHERE
     ...
  AND
     ST_Intersects(toky.geom, pov.geom) AND NOT St_touches(toky.geom, pov.geom)

or use ST_Relate instead of ST_Intersects

share|improve this answer
add comment

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.