[postgis-users] Any improvements in GeomUnion speed ?

[Dylan Beaudette]

Greetings,

I have just started using PostGIS and am very impressed thus far. Today I 
attempted some basic GIS geometry operations, namely the GeomUnion function.

Some background:

Software:
postgresql-8.1.2
geos-2.2.1
postgis-1.1.1

Geometry in question is about 4,000 rather detailed polygons:
 Table "public.ca_soil_order_poly"
    Column    |          Type          | Modifiers 
--------------+------------------------+-----------
 wkb_geometry | geometry               | not null
 soil_order   | character varying(255) | 
Indexes:
    "ca_soil_order_poly_spatial_idx" gist (wkb_geometry) CLUSTER
    "soil_order_ca_soil_order" btree (soil_order)


An example of the query used was:

create table ca_soil_order_poly_dissolved as select  GeomUnion(wkb_geometry) 
as wkb_geometry,  ca_soil_order_poly.soil_order 
from ca_soil_order_poly 
group by ca_soil_order_poly.soil_order ;


...this query ran for over an hour, without returning results. I have noticed 
quite a few past messages on the list about the performance penalties imposed 
by moving features to and from GEOS. 

Just to make sure that I was doing everything that I could possibly do to 
speed up this operation I did:

--make some indexes
CREATE INDEX soil_order_ca_soil_order on ca_soil_order_poly (soil_order) ;
CREATE INDEX  ca_soil_order_poly_spatial_idx on ca_soil_order_poly using gist 
(wkb_geometry gist_geometry_ops);

-- hint for speeding up Union operations
ALTER TABLE ca_soil_order_poly ALTER COLUMN wkb_geometry SET not null;
CLUSTER ca_soil_order_poly_spatial_idx on ca_soil_order_poly;
VACUUM ANALYSE ca_soil_order_poly ;

however, the resulting query plan didn't look to hopeful...

                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 HashAggregate  (cost=372.67..372.78 rows=9 width=4704)
   ->  Seq Scan on ca_soil_order_poly  (cost=0.00..352.11 rows=4111 
width=4704)


After reading the mailing list archives, I was unable to glean any further 
ideas on how to make this type of operation go faster. I suppose that if all 
else fails, I can do this operation in GRASS. 


Thanks in advance !


-- 
Dylan Beaudette
Soils and Biogeochemistry Graduate Group
University of California at Davis
530.754.7341