28

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

3 Answers 3

25

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.

2
  • 4
    Fantastic to have you here Paul, thanks for bringing you incomparable expertise.
    – fmark
    Aug 26, 2010 at 23:15
  • 1
    Thanks, looks like my problem is not small enough. ST_MemUnion() has now been running for 24 hours. I'll try to split the problem.
    – underdark
    Aug 30, 2010 at 20:23
7

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.

1
  • 2
    Note: this will only be helpful if you get the memory issues of the ST_Union handled! :)
    – yhw42
    Aug 27, 2010 at 16:08
5

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.

3
  • postgis_geos_version() returns: 3.2.0-CAPI-1.6.0 ... I guess that's fine. Will try ST_Collect, thanks.
    – underdark
    Aug 23, 2010 at 7:49
  • Well, ST_Collect doesn't seem to dissolve any boundaries and it also creates one giant Multipolygon.
    – underdark
    Aug 23, 2010 at 8:16
  • yeah, I misread the page for ST_Collect. I've updated my answer to provide more specific advice for tuning the memory usage of Postgres.
    – scw
    Aug 23, 2010 at 20:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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