PostGIS : group rows based on geometric functions

Question

I have asked a question on dba.stackexchange about how to list rows in a table based on whatever criterion I have regarding their fields. The answer we found was to use the PostgreSQL function array_agg combined with the GROUP BY clause.

This works when the criterion for grouping rows is a numeric or text value that can be handled by GROUP BY (so, it also may be the result of whatever numeric or string operation you want).

First level problem:

My problem is that I would like to be able to do the same based on geometric fields and PostGIS functions such as ST_Intersect or ST_DWithin (e.g.: grouping together rows that refer to geometries that touch each other or are within a certain distance).

Using a code like

GROUP BY ST_Intersect(table1.geometry, table2.geometry) 

Will most certainly not work as it will return two groups: all the geometries that do not intersect with any other one and all the geometries that intersect with any other one.

Second-level problem:

Since I actually want to detect the group of geometries that intersect with other ones, there is another issue. If geometry A intersects with geometry B and geometry B intersects with geometry C, I want to be able to detect the group [A, B, C] even though A and C do not intersect!

Answer 1

I have searched for it few times ago. My mind process led me to make an array of all geometries intersecting each one. Then for each array I was looking on previous array if the geometry id wasn't already in there, and merge both array in such case. You can still do it that way.

But with the St_Cluster...functions you can do it much more easily. I guess there is many ways but here is one.

Using ST_Clusterintersecting(geom) you will get an array of geometry collection. Each geometry collection will be the pack of the polygons forming a big block. So, unnest this array to get the collections. Then put an id on those collection with row_number. ST_Dump those collections, keeping the id. You have now the native geometries with an group id for each block.

The last step is to found the original id of those geometries. To do so, JOIN your new geometries with your native one with ST_Contains and array_agg GROUP BY group_id.

Worked for me.

At some point I had to ST_SetSRID cause it was lost by the cluster operation.

Edit : If I'm not very clear I can show you a code which will do it. But I guess you will understand more if you do it by yourself. So you could adapt it to the DWithin version.