PostGIS Dissolve Function Alternative to ST_Union

dissolvepostgis

I'm looking for a function to dissolve shared boundaries between polygon features in a table. ST_UNION() almost does what I'm looking for, but it creates a multipolygon from all polygons in the layer regardless of whether they share a common boundary or not. I'd rather only dissolve boundaries between polygons that touch each other. I figured, there should be some way using ST_TOUCHES() but then the need for a dissolve function seems so common that I would be surprised if there is no built-in function to achieve this.

The use case looks like this: I downloaded Corine Landcover data for a big European country and I want to dissolve boundaries between different forest types (approx. 75,000 polygons in one table). I tried ST_UNION, but it fails me with an "out of memory" error (30,000 polygons did work though):

create table corine00 as 
  select st_union(the_geom) as the_geom, 
         sum(area_ha) as area_ha,
         substr(code_00,1,2) as code_00
  from clc00_c31_forests
  group by substr(code_00,1,2)

Note: All forest codes start with '31' and I'm using PostGIS 1.4, GEOS version: 3.2.0-CAPI-1.6.0

Best Answer

ST_MemUnion() will run a naive and slow memory friendly process. You can try that, if your problem is small enough, it might finish in a reasonable amount of time. You can also just break your problem into halves, then run the halves together. Since the resultants will have a lot fewer points than the inputs you might be able to fit the whole problem into memory that way. Or use the fast memory hungry routine on the halves and the slower routine on the final merge.

Related Question