[postgis-users] GeomUnion to dissolve polygon borders

[strk at refractions.net]

On Tue, May 17, 2005 at 09:08:40AM -0700, Martin Davis wrote:
> One trick you can use in JTS to speed up large unions is to aggregate
> the polygons into a single GeometryCollection, and then take the
> buffer(0) of the collection.  This should work in GEOS, and might work
> in PostGIS as well if the appropriate methods have been exposed.

This would be:
	SELECT buffer(collect(the_geom), 0);

--strk;

> 
> Martin Davis, Senior Technical Architect
> Vivid Solutions Inc.      www.vividsolutions.com
> Suite #1A-2328 Government Street Victoria, B.C. V8T 5G5
> Phone: (250) 385 6040 - Local 308 Fax: (250) 385 6046
> 
> 
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net 
> > [mailto:postgis-users-bounces at postgis.refractions.net] On 
> > Behalf Of Matthew Perry
> > Sent: May 16, 2005 9:48 PM
> > To: postgis-users at postgis.refractions.net
> > Subject: [postgis-users] GeomUnion to dissolve polygon borders
> > 
> > 
> > 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 _______________________________________________
> > postgis-users mailing list postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users