Use PostGIS binary function on all possible pairs

Question

I have a table with ids and geometries. I am trying to return all ids which are intersect with another id.

My best query that I managed to arrive to:

SELECT a.id, b.id
FROM t1
WHERE st_equals(a.geom, b.geom) IS FALSE AND ST_intersects(a.geom, b.geom);

For example:

ID	geometry
1	0103000020E6100000010000000500
2	1103000020E6100000010000000500
3	2103000020E6100000010000000500
4	3103000020E6100000010000000500
5	4103000020E6100000010000000500
6	5103000020E6100000010000000500
7	6103000020E6100000010000000500
8	7103000020E6100000010000000500

The desired result: {1,2,3,4}, {5,6,7,8}

• Vince: You were almost there, but didn't define what "a" and "b" were (plus ST_Equals is a really expensive way to test identity (and technically wouldn't work, since it could return True on records other than a.id == b.id)). Next time, please include the error generated by your query.

Answer 1

Join the table to itself on intersects, where id's are not the same:

select a.id a_id, b.id b_id
from public.opbuff a
join
public.opbuff b
on st_intersects(a.geom, b.geom)
where a.id<b.id

enter image description here