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.