PostGIS SQL query to find a polygon that intersects with a line

Question

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?

Answer 1

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' )

Answer 2

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