Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

up vote 14 down vote accepted

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.

share|improve this answer
1  
Fantastic to have you here Paul, thanks for bringing you incomparable expertise. –  fmark Aug 26 '10 at 23:15
    
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 '10 at 20:23

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.

share|improve this answer
    
postgis_geos_version() returns: 3.2.0-CAPI-1.6.0 ... I guess that's fine. Will try ST_Collect, thanks. –  underdark Aug 23 '10 at 7:49
    
Well, ST_Collect doesn't seem to dissolve any boundaries and it also creates one giant Multipolygon. –  underdark Aug 23 '10 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 '10 at 20:38

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

share|improve this answer
1  
Note: this will only be helpful if you get the memory issues of the ST_Union handled! :) –  yhw42 Aug 27 '10 at 16:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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