How to merge adjactent polygons to 1 polygon and keep min/max data?

Question

I have the following polygons in PostGIS

Each polygon has field with "Data" value. I would like auto merge the polygons which touch each other : 1-2 and 3-4-5-6-7

Also , If possible I would like to have the Min/Max values from the columns of each polygon kept to the new polygon

Id  Data    Geom
1   8.45098 MULTIPOLYGON(((178253.411393551 665205.232423685,178248.411393552 665205.232423685,178248.411393552 665210.232423684,178253.411393551 665210.232423684,178253.411393551 665205.232423685)))
2   10.7918 MULTIPOLYGON(((178258.411393551 665205.232423685,178253.411393551 665205.232423685,178253.411393551 665210.232423684,178258.411393551 665210.232423684,178258.411393551 665205.232423685)))
3   10.7918 MULTIPOLYGON(((178263.411393552 665185.232423682,178258.411393551 665185.232423682,178258.411393551 665190.232423685,178263.411393552 665190.232423685,178263.411393552 665185.232423682)))
4   10.4139 MULTIPOLYGON(((178268.411393553 665185.232423682,178263.411393552 665185.232423682,178263.411393552 665190.232423685,178268.411393553 665190.232423685,178268.411393553 665185.232423682)))
5   7.448   MULTIPOLYGON(((178263.411393552 665180.232423684,178258.411393551 665180.232423684,178258.411393551 665185.232423682,178263.411393552 665185.232423682,178263.411393552 665180.232423684)))
6   10.2318 MULTIPOLYGON(((178268.411393553 665180.232423684,178263.411393552 665180.232423684,178263.411393552 665185.232423682,178268.411393553 665185.232423682,178268.411393553 665180.232423684)))
7   10.998  MULTIPOLYGON(((178263.411393552 665175.232423685,178253.411393551 665175.232423685,178253.411393551 665180.232423684,178258.411393551 665180.232423684,178263.411393552 665180.232423684,178263.411393552 665175.232423685)))
8   10.7548 MULTIPOLYGON(((178263.411393552 665175.232423685,178253.411393551 665175.232423685,178253.411393551 665180.232423684,178258.411393551 665180.232423684,178263.411393552 665180.232423684,178263.411393552 665175.232423685)))

What will be the easiest way to do it (I have little knowledge in QGIS/ArcMap and better knowledge with PostGIS ) ?

Answer 1

The only way I could figure out how to do this, was to create a table of unioned geometries in a CTE, use ST_Dump to produce individual polygons (ie, 1-2 and 3-4-5-6 in your question) and then select the max and min values of the data attributes from the original table (which I have called polygons, as you didn't specify a name), that intersect with the new unioned geometries, and grouping by the same new unioned geometries.

WITH geoms (geom) as 
   (SELECT (ST_Dump(ST_Union(geom))).geom from polygons) 
SELECT max(data), min(data), g.geom
   FROM polygons p, geoms g 
   WHERE St_Intersects(s.geom, g.geom)
   GROUP BY g.geom;

If you want to save this to a new table, then add CREATE TABLE new_table AS in front of the WITH. There may be a more efficient way, but this works. In your question, your input polygons are MutliPolygons, so if you want this in the output also, add ST_Multi in front of the new unioned geometry. Putting that all together, you get something like:

CREATE TABLE Unioned_geometries AS
  WITH geoms (geom) as 
    (SELECT (ST_Dump(ST_Union(geom))).geom from polygons) 
  SELECT max(data), min(data), ST_Multi(g.geom)
    FROM polygons p, geoms g 
    WHERE St_Intersects(s.geom, g.geom)
    GROUP BY g.geom;