[postgis-users] geomunion HOWTO?
Martin Davis
mbdavis at refractions.net
Thu Mar 13 17:40:38 PDT 2008
Yep, that's the kind of number I was asking for. Not surprising that it
takes a long time!
I think you might be pushing the bounds of what can be handled by
PostGIS/GEOS at the moment...
Roger André wrote:
> 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
> <mailto: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 <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>
> [mailto: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
> <mailto: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
> <mailto:postgis-users at postgis.refractions.net>
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> <mailto:postgis-users at postgis.refractions.net>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> <mailto: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
>
--
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022
More information about the postgis-users
mailing list