Grouping polygons by similarity keeping records of ids

Question

I have a layer made with overlapping polygons, some areas has the same feature overlap 100 times. What I want is to aggregate all the ids for those features which I have determined they have identical shape and show only one geometry so instead of have 1000 rows have just 1 row with the aggregation of the 1000 ids and just one geometry.

To achieve that I have used ST_HausdorffDistance, also i have added the subtraction of the area as some polygons are not perfectly snapped.

The code at the moment looks like:

with cte1 as(SELECT a.id as idA, b.id as idB, 
     , 100*(ST_Area(ST_Intersection(a.geom, b.geom))/LEAST(ST_Area(a.geom), ST_Area(b.geom)))::numeric(5,2)
       as pct_overlap
     , st_difference(b.geom,a.geom) as st_dif
     , st_area(st_difference(b.geom,a.geom)) as diff_area
     , (st_area(a.geom) - st_area(b.geom)) as area_subs
     , ST_HausdorffDistance(b.geom, a.geom) as hausdorffdistance
     , a.geom as geomA
  FROM data1 a
  JOIN data1  b ON a.geom && b.geom AND ST_Intersects(a.geom, b.geom)
 WHERE 95 <= 100*(ST_Area(ST_Intersection(a.geom, b.geom))
                 /LEAST(ST_Area(a.geom), ST_Area(b.geom)))::numeric(5,2) 
 and ST_HausdorffDistance(b.geom, a.geom) <= 0.75 
 and (st_area(a.geom) - st_area(b.geom)) <=3 
 and (-1 * (st_area(a.geom) - st_area(b.geom))) <=3 
 limit 100000), cte_agg as (select json_AGG(distinct(cte1.idA, cte1.idB) order by(cte1.idA, cte1.idB)) as ids_agg, cte1.new_geom from cte1 cte1)
 select * from cte_agg

The issue is when i group I have the same geometry several times too because it the aggreagation group the intersection more than once so intersection a with b a with c and then in the next row b with a b with c and then c with a and c with b what should be the same

Answer 1

Depending on the actual similarity (vertex distribution in particular), you could probably get away with groups over gridded centroids, e.g.

SELECT
  ARRAY_AGG(id) AS ids,
  ST_Union(geom) AS geom
FROM
  data1
GROUP BY
  ST_SnapToGrid(ST_Centroid(geom), <grid_size>)
;

where you just need to find a suitable <grid_size> - start small (say, 0.0001 for degree based CRS), and increase slowly.