# Compare row to all others in PostGIS

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?

• First check all geoms that a.geom intersects, overlaps and touches, maybe use ST_Relate() here. Then make Union on those and then check if a.geom is inside of it. Or maybe postgis.net/docs/ST_Disjoint.html to get list which do not touch and then union all others. My logic here is that it does not matter if you use all geoms or only those which it has relation. this should make ST_Union faster – simplexio Sep 30 '16 at 6:40

A geometry can only be fully contained by things it intersects with, so reduce the unioning problem to just that subset, then test each feature on its potential container.

``````WITH containers AS (
SELECT ST_Union(a.geom) AS GEOM, b.gid
FROM tbl a JOIN tbl b
ON ST_Intersects(a.geom, b.geom)
WHERE a.gid != b.gid
)
SELECT tbl.gid
FROM tbl JOIN containers
ON containers.gid = tbl.gid
WHERE ST_Covers(containers.geom, tbl.com)
``````

I use covers instead of contains to avoid edge effects in the case of instances where there's two duplicate geometries or geometries with duplicate edges and no neighbours.

• Thanks for a great answer! Compared to the approach I was using in the original question, this approach took the query time from ~15.5 seconds down to ~2.7 seconds. – jczaplew Sep 30 '16 at 13:09

Non Tested code, idea behind this is that it should be faster to create union from geoms that are disjoint than create union from all geoms in table.Other query uses ST_RELATE to find geoms that have relation, (TT*TT****) might be faster logic , there were some mentions about indexes and disjoint operation)

``````WITH tested as (SELECT t.geom, row_number OVER () AS row_no
FROM test t
)
SELECT tested.row_no , ST_Contains(tested.geom,ST_UNION(t.geom)) FROM test t
WHERE
t.row_no != tested.row_no
AND ST_Relate(tested.geom,t.geom,'FF*FF****') IS NOT TRUE -- not sure how this works in postgresql anymore, too much MSSQL
``````

OR

``````  SELECT tested.row_no , ST_Contains(tested.geom,ST_UNION(t.geom)) FROM test t
WHERE
t.row_no != tested.row_no
AND ST_Relate(tested.geom,t.geom,'TT*TT****') IS TRUE -- not sure how this works in postgresql anymore, too much MSSQL
``````
• I think your comment earlier was closest to the mark. You want to test for containment of feature A within the union of all things that intersect feature A. – Paul Ramsey Sep 30 '16 at 12:35