[postgis-users] GeomUnion to dissolve polygon borders

[Bill Binko]

On Tue, 17 May 2005, 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.
> 
> 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;
> 

You MIGHT try this:

alter table cveg cluster on cveg_covertype_index;

That will physically group the rows by covertype, which will save some 
disk thrashing time.

Someone more expert on PostGIS might know whether or not the spatialnindex
will be used by GeomUnion.  However, you realize that you're not just 
unioning _touching_ features, right?  There is no intersection in your 
query, so you'll end up with a single polygon for each covertype.

I can't tell whether that's what you want or not.

Finally, the one thing I tell everyone with PostGIS issues is to look at 
the parameters in postgresql.conf.  In particular (as mentioned in the 
postgis manual), the random_page_cost should be changed -- I've had good 
luck with:

random_page_cost = 2
shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each
sort_mem = 4096 # min 64, size in KB

on a machine with a 2.5GHz AMD and 1GB of ram.

Hope this helps you

Bill