[postgis-users] geomunion HOWTO?

[Paragon Corporation]

 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/.
>  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