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 ) ?
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;