[postgis-users] GeomUnion to dissolve polygon borders

[strk at refractions.net]

On Tue, May 17, 2005 at 04:47:45AM +0000, Matthew Perry wrote:
> 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.

GEOS is known to be SLOW, very slow.
The query will make an array of all geometries in the table
(DETOASTING ALL!) and after that for each element of the array
will: convert to GEOS, union with result. At the end the
result is converted back to postgis.

Index won't give you any speed as you are requesting
a full scan.

The slowest part in this process should be GEOS internal
processing, you can check it defining PROFILE in the profile.h
header file. If you want to help speeding up check out the
geos-devel mailing list:
http://geos.refractions.net/mailman/listinfo/geos-devel

Current CVS of geos contains some performance improvements
but the process is not finished yet (need postgis source
ready to rebuild at any change if you want to keep tuned
with GEOS CVS).

--strk;

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