[postgis-users] geomunion HOWTO?

[Roger André]

I'm trying to find a way to generate "dissolved" geometries without
exporting shapefiles from PostGIS and performing the operating in ArcGIS.  I
found some instructions online at
http://www.paolocorti.net/public/wordpress/index.php/2007/03/30/union-of-two-geometries-in-postgis/.
These work fine on their example, but the opeartion when applied to my data
set never completes.  I realize my data set is pretty large (), but the same
dissolve operation when done via ArcGIS on a shapefile exported by pgsql2shp
takes around 5 minutes to complete.  This leads me to believe I'm doing
something completely wrong, and I would love to get some feedback from those
of you with experience doing this.  Below are the steps I've done.

Step 1 - create a "crop_3" table that contains only crop3 values, and
a class.  This completes within 30 secs:

begin;
create table "test_suit_h_crop3_class" (
"alloc_id" char(8) PRIMARY KEY,
"crop3" numeric,
"class" char(8)
);
select
AddGeometryColumn('','test_suit_h_crop3_class','the_geom','-1','MULTIPOLYGON',2);
insert into "test_suit_h_crop3_class" ("alloc_id", "crop3", "class",
"the_geom")
select vw_suit_area_h.alloc_id, vw_suit_area_h.crop3,
case
when crop3 < 1 then 'class_0'
when crop3 >= 1 and crop3 < 860 then 'class_1'
when crop3 >= 860 and crop3 < 1720 then 'class_2'
when crop3 >= 1720 and crop3 < 3440 then 'class_3'
when crop3 >= 3440 and crop3 < 5160 then 'class_4'
when crop3 >= 5160 and crop3 < 6880 then 'class_5'
when crop3 >= 6880 and crop3 < 7740 then 'class_6'
when crop3 >= 7740 then 'class_7'
ELSE 'other'
end AS class,
vw_suit_area_h.the_geom
FROM vw_suit_area_h;
end;

Step 2 - create a temp "dissolve" table to store the results of a
geometric union run of the above table, grouped by "class".  I run out of
patience before this ever completes (I've let it run for hours.)

begin;
CREATE TABLE "test_suit_area_h_crop3_diss" (
gid serial PRIMARY KEY,
"class" char(8)
);
select
AddGeometryColumn('','test_suit_area_h_crop3_diss','the_geom','-1','MULTIPOLYGON',2);
INSERT INTO "test_suit_area_h_crop3_diss" (the_geom,class)
SELECT astext(multi(geomunion(the_geom))) AS the_geom, class FROM
"test_suit_h_crop3_class" GROUP BY class;
end;

Thanks,

Roger
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080313/8521d40a/attachment.html>