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

Excisable

I have updated my original wrong answer. I posted without checking, as not in front of a computer, sorry about that. – Rhinestone

Thank you for your time to answer this , I'm pretty new to postgres and the syntax is so weird (came from mssql) , I'll review it later. Thx !!!! – Excisable

:-) I also came from MySQL, so I understand where you are coming from with the syntax is weird comment. For spatial (and quite a few other things), Postgres/Postgis blows MySQL out of the water, once you get used to it. For what it is worth, things like CTEs (WITH queries) are supported in Oracle and SQL Server too, so it is really MySQL which is the odd one out. – Rhinestone

I'm straggling writing functions (like stored procedures in MSSQL) and still learning all the spatial features... Off Topic - Which program you use for PostGIS queries ? pgAdmin ? Navicat ? ... How do you visualize the data ? :) – Excisable

I write virtually all my queries in psql at the linux command line (old school, I know). To visualize, I either use OpenLayers with WKT or GeoJSON or if the geometries are really complex, I use the pgsql2shp tool to create shp files and open them in the amazingly good and free (as in beer and speech) QGIS. If you are getting into GIS type work, QGIS is a truly amazing product. So, yeah, pretty heavy on the open source stuff -- and my intro to IT was writing Excel vba macros on windows. Funny old world. – Rhinestone

I misread your earlier comment, you said Mssql not mysql. Actually, SQL server has really good spatial support now, but it is kind of backwards in the way functions work, you do geom.STBuffer(1) rather than ST_Buffer(geom, 1), because they are actually CLR functions. – Rhinestone

I have QGIS 2.4 but the OSM plugin stopped working there... and I need OGM/BING/Google raster for the polygons background. Might try newer version or older version of QGIS – Excisable

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;

Rhinestone

Answer 2

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

Seaway

This query is brilliant. Tried the accepted answer and gave up after one day of running, this query took 7 minutes for the same task. – Rustication