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?
Best Answer
Well check for the "kind of intersection". Translating your predicate below to human language:
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: