No, you are probably just exercising the geometric operators a lot. It is possible a cascaded union would do better, but we don't have that programmed right now. You could try and make it mildly faster by forcing the union to happen in a minimally more efficient order, by sorting when you create your first table, see below...
No guarantees this makes anything better, just a random guess at a hack. On 3/13/08, Roger André <[EMAIL PROTECTED]> 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 ORDER BY X(Extent(the_geom)) + Y(Extent(the_geom)) > end; More ideally, we would bit-interleave the X and Y values, to force the ordering of the inputs to be very well localized, and even more ideally do an actual cascaded union. The goal is to cause each individual geometry + geometry union to *reduce* the amount of aggregate linework. When the g+g ops have no locality, each addition *adds* to the amount of linework, making successive ops slower and slower and slower. > 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
