Here is a simple representation of my table of polygons:

enter image description here

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

1 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:

Example of links

  • 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:

Example of links with groups

  • You only need to union by group id after

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.