2

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.

3

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.

  • ST_ClusterDBSCAN is the perfect function for that, and the performance gap is tremendous, thanks for pointing it out. I came out with this query that seems to makes the job: WITH res AS ( SELECT *, ST_ClusterDBSCAN(geom, 0.8, 1) OVER(PARTITION BY <column>) AS clst FROM <table> ) SELECT ST_Union(res.geom) FROM res GROUP BY res.<column>, res.clst However I couldn't use St_UnaryUnion as an error pops out, I don't get why you mentionned it ? – MaxPlank Oct 9 '18 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.