[postgis-users] geomunion HOWTO?

[Martin Davis]

Just out of curiosity, how many geometries are in each class?  Geomunion 
will take a long time when you start getting into the 100's of geometries.

You might also want to check out this thread - it deals with a similar issue

http://postgis.refractions.net/pipermail/postgis-users/2007-November/017696.html

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

-- 
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022