[postgis-users] GeomUnion to dissolve polygon borders

[Matthew Perry]

Hi folks,

 I'm trying to use postgis to remove polygon borders between adjacent
polygons sharing a common attribute value (aka "dissolve" in the ESRI
world). Here was my first attempt:

CREATE TABLE landcover AS
  SELECT GeomUnion(the_geom), covertype 
  FROM cveg
  GROUP BY covertype

But it is SLOW... The query takes over 700 minutes. Just for
reference, ArcMap's dissolve tool takes about ~20 minutes for the
original shapefile on a comparable machine.

I have an index on each of the important fields:

 CREATE INDEX cveg_geom_index ON cveg
   USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
 CREATE INDEX cveg_covertype_index ON cveg (covertype);
 VACUUM ANALYZE cveg;

Here's the EXPLAIN:

gisdata=> EXPLAIN SELECT GeomUnion(the_geom), covertype FROM cveg
GROUP BY covertype;
                            QUERY PLAN                             

HashAggregate  (cost=6154.77..6154.79 rows=9 width=1212)
  ->  Seq Scan on cveg  (cost=0.00..5917.18 rows=47518 width=1212)

(2 rows)

The shapefile in question only has 47518 polygon features (~50 MB for
the shp). Oh, and I'm running a 750 Mhz AMD with 128 MB RAM Linux
setup with Postgis 1.0/Postgres 8.0.2.

Is there anyway to speed this process up? Perhaps someone's worked out
a more efficient query to dissolve polygon borders?
-- 
Matt Perry
Humboldt State University
perrygeo at gmail.com