[GIS] the best way to merge lots of small adjacents polygons ? (POSTGIS)

mergepostgispostgresql

I am looking for a way to merge lots of small adjacents polygons.

I have this kind of features:

polygon_to_merge

So if my polygons are adjacents AND have the same color attribute then I want to merge those polygons. Typically I want to merge the selected features of the picture. I try to add a "ST_TOUCHES" condition but all the polygons are not necessarily connected to each other.

Another question on this website is similar to this one but don't take into account that polygons need to be connected.

So the first step should be something like:

 SELECT ST_Union(ST_SnapToGrid(the_geom,0.0001)) 
 FROM my_poly
 GROUP BY color;

But how to add the "need to be connected" condition ?

Any ideas ?

EDIT

I found a solution:

Step 1) Merge all the polygons that have the same color.

Step 2) Split multiparts polygons to singleparts polygons.

SELECT (ST_DUMP(ST_UNION(ST_SNAPTOGRID(the_geom,0.0001)))).geom, color
FROM my_poly
GROUP BY color

But there is perhaps a better solution, because i'm loosing some informations during the step 1).

Best Answer

I finally found a solution:

Step 1) Merge all the polygons that have the same attribute 'color'.

Step 2) Split multiparts polygons to singleparts polygons with ST_DUMP.

SELECT (ST_DUMP(ST_UNION(ST_SNAPTOGRID(the_geom,0.0001)))).geom, color
FROM my_poly
GROUP BY color

But there is perhaps a better solution, because i'm loosing some informations during the step 1).

ST_Dump returns a geometry (geom) and an array of integers (path). So to select only the geometry you have to write (ST_Dump(my_geometry)).geom

Related Question