Given a PostGIS table of polygons, what is the best way to check if each geometry is inside the union (
ST_Collect) of all other geometries from the same table?
I have tried the following:
WITH first AS ( SELECT geom, row_number OVER () AS row_no FROM test ) SELECT geom, row_no, ( SELECT ST_Contains(ST_Collect(b.geom), a.geom) FROM first b WHERE b.row_no != a.row_no ) isinside FROM first a
While this works, the subquery can be very slow with a large number of geometries. Is there a more efficient way to achieve the same result?