how to count how many buffers intersects in PostGIS

Question

How can I count how many buffers intersects and select only those which have between 2 AND 6 intersections with other buffers? I can create a buffer with following query, but I don't have an idea on how to count intersections

SELECT ST_Buffer(geom::geography, 400)
FROM mytable;

I appreciate any help. Thanks.

Answer 1

It is wrong to use a buffer in such case, as the buffer is only an approximation. Instead, use the index compatible st_dwithin() function.

The idea is to select all points (polygons or else) that are within twice the distance, to group the result and keep the ones having at least 6 nearby features.

The example below use 2 tables, but you can use the same table twice.

SELECT myTable.ID, count(*), array_agg(myOtherTable.ID) as nearby_ids  
FROM mytable 
 JOIN myOtherTable ON st_Dwithin(mytable.geom::geography, myOtherTable.geom::geography, 800)
GROUP BY myTable.ID
HAVING count(*) >= 6;

To use the same table twice, you can alias them:

SELECT a.ID, count(*), array_agg(b.ID) as nearby_ids  
FROM mytable a
 JOIN mytable b ON st_Dwithin(a.geom::geography, b.geom::geography, 800)
GROUP BY a.ID
HAVING count(*) >= 6;