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

  • 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. – Jochen Schwarze Mar 16 '16 at 18:53

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,

mytbl a LEFT JOIN mytbl b
ON ST_Contains(a.geom, b.geom)

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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