Is there a dissolve function in PostGIS other than st_union?

Question

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

Answer 1

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.

Answer 2

I believe ST_Dump is what you want:

ST_Dump:

Returns a set of geometry_dump (geom,path) rows, that make up a geometry g1.... For example it can be use to expand MULTIPOLYGONS into POLYGONS....

So for your case:

 SELECT (ST_Dump( ST_Union( the_geom ) )).geom
 FROM clc00_c31_forests
 GROUP BY substr(code_00,1,2)

I'm not sure how it will interact with the table creation you are trying to do, but it should give you the geometries as separate entries. You would then be able to do a spatial join (using && and ST_Contains ) between the two tables to collect the data onto the geometries.

Answer 3

Is your PostGIS compiled against GEOS 3.1.0+? For that version, a much faster cascaded union was implemented, but if not found will use the older code which is orders of magnitude slower.

Update: it looks as if your PostGIS is using the cascaded union approach, but the memory starvation is real. I'd try increasing the available memory to your Postgres instance, here's some advice from Paul Ramsey's 2007 FOSS4G PostGIS talk:

• Disk access is slow, so higher performance can be gained by using more memory to cache data!
  • Increase shared_buffers
  • Physical RAM - OS needs * 75%
• Sorting is faster in memory
  • Increase work_mem
• Disk clean-up is faster with more memory
  • Increase maintenance_work_mem
• Allocated per connection
• Also
  • Increase wal_buffers
  • Increase checkpoint_segments
  • Decrease random_page_cost

In your case, I'd try increasing shared_buffers, the general recommendation is 25% of your available memory for a database server, but try increasing it to 3-4x its current value and seeing if it completes.