6

New to PostGIS, I have a polygon layer of water catchment areas which are formed by the specific monitoring points along the river upstream. For example, on one river, there could be multiple areas, all overlaping with each other. Or there could be a small area not overlaping with any other areas. The task is to find the most upstream catchment areas (red), the ones that does not contain any other areas. I have tried several geometry expressions, but could not get the correct result.

enter image description here

1
  • Of course you tried ST_Contains? A complete different (raster based) approach: To get the most uphill stream segments I remember to make use of stream segment order, as in GRASS delivered by r.watershed and then take the segement with highest order per segment. Commented Mar 16, 2016 at 18:53

2 Answers 2

5

Negation queries are a bit tricky. I've found the best way to achieve them is to use a LEFT JOIN on the opposite condition. So for your example,

SELECT a.* FROM
mytbl a LEFT JOIN mytbl b
ON ST_Contains(a.geom, b.geom)
WHERE b.geom IS NULL;

Basically the LEFT JOIN forces the return set to include every row in the table, even those for which there is no containment relationship. These "extra" rows get NULL values in their portions of the relation, and we filter out just those records to get the answer. It's counter-intuitive, but about as fast as one can get a result.

0

@paulramsey answer was awesome. I am just adding this because I had to remove points (weather observation points) from great lakes. I created great lakes polygon, found the points in the polygon (st_contains) then joined all points to great lakes points and filtered where great lakes points were null.

select allgeom.geom
from grp_gis as allgeom 
left join (
    select geom from 
        (with grp as materialized
                (select geom from great_lakes.lakes where ogc_fid = 7)
        select grp_gis.geom
        from grp_gis, grp where st_contains(grp.geom, grp_gis.geom)) sq 
) as glgeom on allgeom.geom = glgeom.geom
where glgeom.geom is null 

Your Answer

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

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