[postgis-users] GeomUnion to dissolve polygon borders

[Martin Davis]

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.

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
>