5

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

  • What is bugging me is the cost of ST_Union for all polygons just to break it in parts again. Would maybe a ST_Union to a subset be faster and more efficient, let's say only to features inside a certain buffer/nearest neighbour or similar? – bennos Mar 17 '16 at 21:23
  • @bennos Actually the query take ~1.5 seconds for 2500 polygons. So it's not that bad. And the idea of adding a buffer is a little bit dangerous ! – obchardon Mar 18 '16 at 8:06
  • ST_ClusterWithin does exactly that: merges features based on a distance threshold. However, I don't see anything particularly wrong with what you already have. – John Powell Mar 18 '16 at 9:35
  • You should post your solution as an answer. – LR1234567 Mar 21 '16 at 2:02
  • @obchardon I am working with a large datasets where ST_Union takes hours, thus your question is of specific interest for me. Breaking the dataset into smaller chunks is one way I was doing my research on. – bennos Mar 21 '16 at 14:02
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.