[postgis-users] geomunion HOWTO?

[Roger André]

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 <lr at pcorp.us> 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:lr at pcorp.us]
> 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: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] 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é <randre at gmail.com> 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
> >  postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080313/9e61d245/attachment.html>