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.
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!