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