[postgis-users] Any improvements in GeomUnion speed ?

[Robert Burgholzer]

Dylan,
You may try "MemGeomUnion". I work with extremely complicated shapes
such as soil maps myself, and they can be a bear. What I think I have
determined, is that these multi-polygons have many, many sub-polygons
and that is where GEOS starts to experience its difficulties. You may
wish to try as an experiment, geomUnion'ing (or memGeomUnion'ing) a
sub-set of your shapes, perhaps trying it for a single soil order first,
and see if that speeds up. Alternatively, you may do a simplify, but as
you may know, loss in shape edge resolution generally results from
simplify and this may be intolerable for your application. Also, if you
are trying to get a table with a multipolygon in there, you need to
encase your geomUnion within the "Multi()" function, as so:

update seggroups set the_geom = multi(memgeomunion(a.the_geom)) from
lrsegs_dd as a

Good luck, and post any succsess you may have to the list, as for my own
purposes I am still a bit clueless.

Robert

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Dylan Beaudette
Sent: Wednesday, July 19, 2006 8:27 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Any improvements in GeomUnion speed ?

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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users