<span class="HcCDpe">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 <a href="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/</a>. 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.<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" (<br>"alloc_id" char(8) PRIMARY KEY,<br>
"crop3" numeric,<br>"class" char(8)<br>);<br>select AddGeometryColumn('','test_suit_h_crop3_class','the_geom','-1','MULTIPOLYGON',2);<br>insert into "test_suit_h_crop3_class" ("alloc_id", "crop3", "class",<br>
"the_geom")<br>select vw_suit_area_h.alloc_id, vw_suit_area_h.crop3,<br>case<br>when crop3 < 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>end;<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 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" (<br>
gid serial PRIMARY KEY,<br>"class" char(8)<br>);<br>select AddGeometryColumn('','test_suit_area_h_crop3_diss','the_geom','-1','MULTIPOLYGON',2);<br>INSERT INTO "test_suit_area_h_crop3_diss" (the_geom,class)<br>
SELECT astext(multi(geomunion(the_geom))) AS the_geom, class FROM<br>"test_suit_h_crop3_class" GROUP BY class;<br>end;<br><br>Thanks,<br><br>Roger<br></span>