Setting threshold for unions in PostGIS?

Question

I have a shapefile of buffers. What I'm trying to do is to merge the buffers that intersect, but not just if any part of the buffers intersect. I would like to set a threshold, for let's say if 50% of more of two buffers intersect then join them together.

[

For example, in the representation above, I would clump the buffer zones in the bottom left, but the two clusters of two buffer zones would be kept independent.

Is there any way in PostGIS I can do this?

Answer 1

Assuming you have Postgis 2.2, you can use ST_ClusterWithin for this purpose. ST_ClusterWithin takes a geometry and a tolerance and returns GeometryCollections of all the geometries that are within a certain distance. You can use the tolerance as a proxy for the percentage overlap, assuming your buffers are all the same size. Once you have returned the GeometryCollections that are within a certain distance of each other (have a certain % overlap), you can then ST_Union the results and use something like ST_Dump or ST_CollectionExtract to return the individual polygons -- assuming you only want polygons as an output.

For example, and using ST_AsText for clarity:

WITH testdata (geom) AS
  (VALUES 
    (ST_Expand(ST_MakePoint(0,0), 2)),
    (ST_Expand(ST_MakePoint(1,1), 2)),
    (ST_Expand(ST_MakePoint(2,2), 2)),
    (ST_Expand(ST_MakePoint(9,9), 2)),
    (ST_Expand(ST_MakePoint(10,10), 2))
 )
,clusters(geom) AS 
   (SELECT 
       UNNEST(ST_ClusterWithin(geom, 2)) 
    FROM testdata)
SELECT 
    ST_AsText((ST_Dump(ST_Union(geom))).geom) 
FROM clusters;

which returns:

POLYGON((-2 -2,-2 2,-1 2,-1 3,0 3,0 4,4 4,4 0,3 0,3 -1,2 -1,2 -2,-2 -2))

POLYGON((7 7,7 11,8 11,8 12,12 12,12 8,11 8,11 7,7 7))

As there are two sets of polygons separated by 2, the tolerance to ST_ClusterWithin.

I am not sure how to calculate the exact tolerance that would yield an overlap of 50% of the area, but a quick experiment with circles of area PI, suggests a value of around .565 apart, when the radius is 1.

SELECT 
    ST_Area(
        ST_Intersection(
           ST_Buffer(ST_MakePoint(0, 0), 1),
           ST_Buffer(ST_MakePoint(.565, .565), 1)
    )
);

which returns 1.57 which is very close to PI/2. If you do not have Postgis 2.2 or greater, than I believe you need to take a recursive approach to ST_Union, which is likely to be more computationally expensive, as you will have to calculate the polygon intersection, their areas, and then union the those polygons that pass the 50% (or other threshold) test.

There is a nice explanation on math stack exchange showing a formula to derive the distance needed for two circles to share a particular percentage overlap.