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