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.<br>--<br><br><div class="gmail_quote">On Thu, Mar 13, 2008 at 3:56 PM, Paragon Corporation <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Slight correction<br>
<br>
SELECT st_multi(st_union(the_geom)) AS the_geom, class FROM<br>
<div class="Ih2E3d">"test_suit_h_crop3_class" GROUP BY class;<br>
<br>
</div>or<br>
<br>
SELECT st_multi(st_collect(the_geom)) AS the_geom, class FROM<br>
<div class="Ih2E3d">"test_suit_h_crop3_class" GROUP BY class;<br>
<br>
<br>
</div><div class="Ih2E3d">-----Original Message-----<br>
From: Paragon Corporation [mailto:<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>]<br>
Sent: Thursday, March 13, 2008 6:54 PM<br>
To: 'PostGIS Users Discussion'<br>
</div><div class="Ih2E3d">Subject: RE: [postgis-users] geomunion HOWTO?<br>
<br>
Also scrap the AsText call you have. I'm guessing its slowing things down<br>
a bit, although probably not much, but its totally unnecessary at anyrate.<br>
Should just be<br>
<br>
</div> SELECT st_multi(st_geomunion(the_geom)) AS the_geom, class FROM<br>
<div class="Ih2E3d">"test_suit_h_crop3_class" GROUP BY class;<br>
<br>
</div><div class="Ih2E3d">You may also want to consider using ST_Collect instead of ST_GeomUnion,<br>
although for large files may not help much.<br>
<br>
<br>
Hope that helps,<br>
Regina<br>
<br>
<br>
-----Original Message-----<br>
From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>
[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of Paul<br>
Ramsey<br>
Sent: Thursday, March 13, 2008 6:39 PM<br>
To: PostGIS Users Discussion<br>
Subject: Re: [postgis-users] geomunion HOWTO?<br>
<br>
</div><div><div></div><div class="Wj3C7c">No, you are probably just exercising the geometric operators a lot. It is<br>
possible a cascaded union would do better, but we don't have that programmed<br>
right now. You could try and make it mildly faster by forcing the union to<br>
happen in a minimally more efficient order, by sorting when you create your<br>
first table, see below...<br>
<br>
No guarantees this makes anything better, just a random guess at a hack.<br>
<br>
On 3/13/08, Roger André <<a href="mailto:randre@gmail.com">randre@gmail.com</a>> wrote:<br>
> I'm trying to find a way to generate "dissolved" geometries without<br>
> exporting shapefiles from PostGIS and performing the operating in<br>
> ArcGIS. I found some instructions online at<br>
><br>
<a href="http://www.paolocorti.net/public/wordpress/index.php/2007/03/30/union-of-two-geometries-in-postgis/" target="_blank">http://www.paolocorti.net/public/wordpress/index.php/2007/03/30/union-of-two<br>
-geometries-in-postgis/</a>.<br>
> These work fine on their example, but the opeartion when applied to<br>
> my data set never completes. I realize my data set is pretty large<br>
> (), but the same dissolve operation when done via ArcGIS on a<br>
> shapefile exported by pgsql2shp takes around 5 minutes to complete.<br>
> This leads me to believe I'm doing something completely wrong, and I<br>
> would love to get some feedback from those of you with experience doing<br>
this. Below are the steps I've done.<br>
><br>
> Step 1 - create a "crop_3" table that contains only crop3 values, and<br>
> a class. This completes within 30 secs:<br>
><br>
> begin;<br>
> create table "test_suit_h_crop3_class" ( "alloc_id" char(8) PRIMARY<br>
> KEY, "crop3" numeric, "class" char(8) ); select<br>
> AddGeometryColumn('','test_suit_h_crop3_class','the_geom','-1','MULTIP<br>
> OLYGON',2); insert into "test_suit_h_crop3_class" ("alloc_id",<br>
> "crop3", "class",<br>
> "the_geom")<br>
> select vw_suit_area_h.alloc_id, vw_suit_area_h.crop3, case when crop3<br>
> < 1 then 'class_0'<br>
> when crop3 >= 1 and crop3 < 860 then 'class_1'<br>
> when crop3 >= 860 and crop3 < 1720 then 'class_2'<br>
> when crop3 >= 1720 and crop3 < 3440 then 'class_3'<br>
> when crop3 >= 3440 and crop3 < 5160 then 'class_4'<br>
> when crop3 >= 5160 and crop3 < 6880 then 'class_5'<br>
> when crop3 >= 6880 and crop3 < 7740 then 'class_6'<br>
> when crop3 >= 7740 then 'class_7'<br>
> ELSE 'other'<br>
> end AS class,<br>
> vw_suit_area_h.the_geom<br>
> FROM vw_suit_area_h<br>
<br>
ORDER BY X(Extent(the_geom)) + Y(Extent(the_geom))<br>
<br>
> end;<br>
<br>
More ideally, we would bit-interleave the X and Y values, to force the<br>
ordering of the inputs to be very well localized, and even more ideally do<br>
an actual cascaded union.<br>
<br>
The goal is to cause each individual geometry + geometry union to<br>
*reduce* the amount of aggregate linework. When the g+g ops have no<br>
locality, each addition *adds* to the amount of linework, making successive<br>
ops slower and slower and slower.<br>
<br>
> Step 2 - create a temp "dissolve" table to store the results of a<br>
> geometric union run of the above table, grouped by "class". I run out<br>
> of patience before this ever completes (I've let it run for hours.)<br>
><br>
> begin;<br>
> CREATE TABLE "test_suit_area_h_crop3_diss" ( gid serial PRIMARY KEY,<br>
> "class" char(8) ); select<br>
> AddGeometryColumn('','test_suit_area_h_crop3_diss','the_geom','-1','MU<br>
> LTIPOLYGON',2); INSERT INTO "test_suit_area_h_crop3_diss"<br>
> (the_geom,class) SELECT astext(multi(geomunion(the_geom))) AS<br>
> the_geom, class FROM "test_suit_h_crop3_class" GROUP BY class; end;<br>
><br>
> Thanks,<br>
><br>
> Roger<br>
><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
><br>
><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>
<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br>