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

11k Views Asked by At

I have the following polygons in PostGIS

enter image description here

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

2

There are 2 best solutions below

0
On BEST ANSWER

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;
1
On

You can use ST_Dump and ST_Union, but you will have problem on bigger data, if you will UNION milions of polygons, your geometry will be very very complex and PostGIS isn`t designed to work with big, complex geometries. You can use topology, or somethink like this

CREATE TABLE block_buildings AS                                                 
SELECT                                                                          
block_id                                                                        
, ST_MemUnion(geometry)                                                         

FROM houses building                                                            
, LATERAL (                                                                     
   with recursive building_block AS (                                           
      SELECT building.id                                                        
      UNION                                                                     
      SELECT building2.id FROM building_block                                   
      JOIN houses build_geom USING(id)                                          
      JOIN houses building2                                                     
      ON st_dwithin(build_geom.geometry, building2.geometry, 0.5)               
   )                                                                            
   SELECT md5(string_agg(id::text, ',' order by id)) block_id FROM building_block JOIN houses USING(id)
) block                                                                         
GROUP BY block_id                                                               
; 

LATERAL works like for loop, subquery is evaluated for every row. WITH recursive is common table expression, it works recursive, like snowball. ST_DWithin is used because of optimalization, you can use dump on outgoing geometries, if you want merge only polygons with shared boundary, or overlaps. It is slow, but not so much memory consuming (because of lateral), it can be optimalized (for example with plpgsql), because every group is computed for all its polygons. But you can use in aggregate query some aggregates for atrs. If you will create only geometry, you can agregate attrs into using ST_With and ST_PointOnSurface, it is pretty fast, if is well indexed.

-------- edit In actual PostGIS are functions for clustering

this or this or this

This functions