Hi, and thanks for the feedback. I have 8 classes, spread across 191,317 total records. Not sure is that was what you meant by number of geometries. --
On Thu, Mar 13, 2008 at 3:56 PM, Paragon Corporation <[EMAIL PROTECTED]> wrote: > Slight correction > > SELECT st_multi(st_union(the_geom)) AS the_geom, class FROM > "test_suit_h_crop3_class" GROUP BY class; > > or > > SELECT st_multi(st_collect(the_geom)) AS the_geom, class FROM > "test_suit_h_crop3_class" GROUP BY class; > > > -----Original Message----- > From: Paragon Corporation [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 13, 2008 6:54 PM > To: 'PostGIS Users Discussion' > Subject: RE: [postgis-users] geomunion HOWTO? > > Also scrap the AsText call you have. I'm guessing its slowing things > down > a bit, although probably not much, but its totally unnecessary at anyrate. > Should just be > > SELECT st_multi(st_geomunion(the_geom)) AS the_geom, class FROM > "test_suit_h_crop3_class" GROUP BY class; > > You may also want to consider using ST_Collect instead of ST_GeomUnion, > although for large files may not help much. > > > Hope that helps, > Regina > > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Paul > Ramsey > Sent: Thursday, March 13, 2008 6:39 PM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] geomunion HOWTO? > > 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/<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','MULTIP > > OLYGON',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','MU > > LTIPOLYGON',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 > > > > _______________________________________________ > 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
