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.
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;
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.