Dissolving polygons on distance condition using PostGIS?

Question

I'm tying to group some polygons in same layer, on geometry & attribute conditions: within 0.8m from each other and with same value in a column.

With answers I've found here I've got something like that:

SELECT ST_Dump(ST_Union(a.geom)).geom as newgeom
FROM foo a, foo b
WHERE ST_Intersects(ST_Buffer(a.geom,0.4),ST_Buffer(b.geom,0.4))
AND a.column = b.column
AND a.id != b.id
GROUP BY a.column

This is the closest I made to the result. However, the difference with all others answers here is that using ST_Dump in order to get singlepart geometries makes my distance condition totally useless, as all geometries will be split even if within 0.8m.

I'm not very experienced in SQL.

Answer 1

The buffer-intersection construct in general is better be avoided in favor of the (for some reason not always index driven, but still) more efficient ST_DWithin. However, you will still need to implement a table self-join, and that will imply unnecessary overhead in this case.

I suggest to look into ST_ClusterDBSCAN instead; note that this will only work if your data is in a (metric) projection, as e.g. threshold values passed in will be assumed to be in the data's CRS units (and geography data type is not accepted).

Running

SELECT *,
       ST_ClusterDBSCAN(geom, 0.8, 1) OVER(PARTITION BY <column>) AS clst
FROM <table>;

will select your table and assign an integer in column clst to each row, clustered by max distance of 0.8 units (eps parameter) and partitioned by <column>.

But note: the clst will be in the range of [0 - n] for each distinct value in <column> (i.e. <value_a> -> [0 - na], <value_b> -> [0 - nb]), so grouping will need to be based on both! You can, however, easily add more attributes to cluster by to the PARTITION BY list or ORDER BY whatever, if you need to, just as with any other window function.

The minpoints parameter will exclude (set to NULL) all geometries that have less cluster partners than the given value; set accordingly (e.g. if you want every geometry to be assigned a clst number, leave at 1; if you only want to consider clusters of more or equal to five fitting geometries, set to 5).

This might effectively be only the first but more efficient step towards your goal and can be used in a subquery or CTE; from here, a ST_UnaryUnion based on equal <column> + clst might or might not be what you want; if you don't know how to proceed, just write a comment and I'll try to expand the answer accordingly.