Merging polygons based on percent overlap in PostGIS

Question

Here is a simple representation of my table of polygons:

I want to merge the small polygons to the big ones (if they intersect) and I can't do a simple union because of cases like polygon 1 would merge polygon A and B together and I don't want that.

First I create 'a' which takes all the polygons less than my area cutoff and unions them together so that polygons like 1 and 2 will be merged and theres less little dinky polygons to deal with, then I want to take all the polygons from the original table that are above that cutoff (ones like A and B) and join the two tables 'a' and 'b' together such that the touching ones are merged.

So polygon 1 will be merged with polygon B (because it has a greater overlap than with polygon A). polygon 3 & 4 will be merged with A. Polygon 5 will be merged with B.

Here is the start of my query:

WITH test AS (
  WITH a AS (
    WITH polygons AS (
      SELECT target_geometry_id, target_geometry_geometry::geometry
      FROM target_geometry
      WHERE target_geometry_set_id = 31
      AND st_area(target_geometry_geometry) < 500
    )
    SELECT (st_dump((ST_Union(p1.target_geometry_geometry)))).geom
    FROM polygons p1
  ),
  b AS (
  SELECT target_geometry_id, target_geometry_geometry
  FROM target_geometry
  WHERE target_geometry_set_id = 31
  AND ST_Area(target_geometry_geometry) >= 500
  )
  SELECT * from a join b -- not sure how to join them

Answer 1

Maybe one way of doing what you want is to do something like this:

• first you compute for all the polygon which one is the other polygon that intersect with it the most (you do a left join of your table with itself while intersecting, then order by the size of the intersection desc, group by polygon id and limit 1 to have for each polygon the main other polygon)
  • You will have a result like this:

• Once you have this what you want is to group all the polygon that belong to the same "group" (that are linked together in some way, for example 2 will be associated to 1, and 1 to B, 5 to B and B to 5, so they are in the same group)

• You should be able to compute a group id by using a recursive CTE, like for example this

  • You want to have a result like this:

• You only need to union by group id after