<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Regina,<div><br></div><div>Great, my query now works with your amended upgis function posting.</div><div><br></div><div>So, here are my timing outputs for the dataset on watersheds I just sent a reference to:</div><div><br></div><div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; color: rgb(0, 0, 204); ">select<span style="color: #000000"> </span>count<span style="color: #000000">(*) </span>from<span style="color: #000000"> npsa;</span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; color: rgb(85, 85, 85); ">--3162 records</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; min-height: 15px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; "><span style="color: #0000cc">select</span> st_cascadeunion(the_geom) <span style="color: #0000cc">from</span> npsa <span style="color: #0000cc">group</span> <span style="color: #0000cc">by</span> wholedrain;</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; color: rgb(85, 85, 85); ">-- 48.39 sec</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; min-height: 15px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; "><span style="color: #0000cc">select</span> upgis_cascadeunion(the_geom) <span style="color: #0000cc">from</span> npsa <span style="color: #0000cc">group</span> <span style="color: #0000cc">by</span> wholedrain;</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; color: rgb(85, 85, 85); ">-- 50.91 sec</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; min-height: 15px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; "><span style="color: #0000cc">select</span> ST_Union(the_geom) <span style="color: #0000cc">from</span> npsa <span style="color: #0000cc">group</span> <span style="color: #0000cc">by</span> wholedrain;</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; color: rgb(85, 85, 85); ">-- 2.49 minutes</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; min-height: 15px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; "><span style="color: #0000cc">select</span> ST_Collect(the_geom) <span style="color: #0000cc">from</span> npsa <span style="color: #0000cc">group</span> <span style="color: #0000cc">by</span> wholedrain;</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal 'Lucida Grande'; color: rgb(85, 85, 85); ">-- 10.73 seconds</div><div><font class="Apple-style-span" color="#555555" face="'Lucida Grande'"><br></font></div><div>Next chance I get I'll take a look at the actual results.</div><div><br></div><div>Thanks!</div><div>Dane</div><div><br></div><div><br></div></div><div><div><div>On Oct 5, 2008, at 5:53 AM, Paragon Corporation wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div>Dane,<br>Dane,<br>Oops sorry about that . I just posted a revised version. Can you try that<br>out? Thanks.<br><br>Also can you send me a sample of your data off list. I think the issue was<br>I was experimenting with array_append vs. st_geom_accum and I had gotten the<br>error you described below, but then was unable to replicate it again.<br><br>It could be the detoasting affect of st_geom_accum that Mark had described<br>was the difference between the two. <br><br>Thanks,<br>Regina<br><br>-----Original Message-----<br>From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br>[<a href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>] On Behalf Of Dane<br>Springmeyer<br>Sent: Sunday, October 05, 2008 1:47 AM<br>To: PostGIS Users Discussion<br>Subject: Re: [postgis-users] Cascaded Union Aggregate function<br><br>Hi Regina,<br><br>Thank you so much for the wiki posting. I've been using your code from your<br>august 12th email with great success, if not joy. What an amazing speed<br>improvement, especially for such a critical function. This was your exact<br>email of code I have working:<br><a href="http://postgis.refractions.net/pipermail/postgis-devel/2008-August/003412.ht">http://postgis.refractions.net/pipermail/postgis-devel/2008-August/003412.ht</a><br>ml<br><br>I've just downloaded the new code off the wiki, but I'm getting an error of<br>'Unknown Geometry Type: 0'. I'll paste the whole error below in case you<br>have an idea what might be going on. I'm running mac 10.5 with these<br>details:<br><br> postgis_version<br>---------------------------------------<br> 1.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1<br>(1 row)<br><br>--------------------------------------------------------------<br> PostgreSQL 8.3.3 on i386-apple-darwin9.4.0, compiled by GCC<br><br><br>Cheers,<br><br>Dane<br><br><br>-- full error:<br>test=# select upgis_cascadeunion(the_geom) from npsa group by wholedrain;<br><br>ERROR: Unknown geometry type: 0<br>CONTEXT: SQL function "upgis_dump_collect_garray" statement 1<br>SQL statement "SELECT <br>upgis_dump_collect_garray( array_append(ARRAY(SELECT $1 [s] As geom FROM<br>generate_series(1, $2 ) As s WHERE NOT (s = ANY( $3 )) ), <br>st_unite_garray(ARRAY(SELECT $1 [s] As geom FROM generate_series(1, <br>$2 ) As s WHERE s = ANY( $3 ) ) ) ) )"<br>PL/pgSQL function "st_collect_unite_garray" line 29 at assignment SQL<br>statement "SELECT upgis_unitecascade_garray(ARRAY(SELECT<br>st_collect_unite_garray( $1 [i:least(i + $2 - 1, $3 )]) As geom FROM<br>generate_series(1, $3 , $2 ) As i), $4 + 1, $5 , false)"<br>PL/pgSQL function "upgis_unitecascade_garray" line 48 at assignment<br>SQL statement "SELECT <br>upgis_dump_collect_garray( array_append(ARRAY(SELECT $1 [ $2 [s]] As geom<br>FROM generate_series(1, array_upper( $2 ,1)) As s ),<br>upgis_unitecascade_garray(ARRAY(SELECT $1 [s] As geom FROM <br>generate_series(1, $3 ) As s WHERE NOT (s = ANY( $2 )) ), $4 + 1, <br>$5 , true) ) )"<br>PL/pgSQL function "upgis_unitecascade_garray" line 33 at assignment SQL<br>function "upgis_unitecascade_garray_sort" statement 1<br><br><br>On Oct 4, 2008, at 4:53 PM, Paragon Corporation wrote:<br><br><blockquote type="cite"><br></blockquote><blockquote type="cite">Oops sorry about that.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">Just realized I had ugly spaces in the link. I've changed it. Use <br></blockquote><blockquote type="cite">this one<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">http://postgis.refractions.net/support/wiki/index.php?upgis_cascadeuni<br></blockquote><blockquote type="cite">on<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">-----Original Message-----<br></blockquote><blockquote type="cite">From: postgis-users-bounces@postgis.refractions.net<br></blockquote><blockquote type="cite">[mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of <br></blockquote><blockquote type="cite">Paragon Corporation<br></blockquote><blockquote type="cite">Sent: Saturday, October 04, 2008 7:44 PM<br></blockquote><blockquote type="cite">To: 'PostGIS Users Discussion'<br></blockquote><blockquote type="cite">Subject: [postgis-users] Cascaded Union Aggregate function<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">For those people who have unions to do that are slow, feel free to try <br></blockquote><blockquote type="cite">out this aggregate union function.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">I haven't tested it enough for it to make it into 1.3.4, but have <br></blockquote><blockquote type="cite">included a link to the source code in the wiki. It should work just <br></blockquote><blockquote type="cite">fine on PostGIS versions 1.2.2 and above.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">http://postgis.refractions.net/support/wiki/index.php?PL%2FPGSQL%20Pse<br></blockquote><blockquote type="cite">udo%20 Cascade%20Union%20Aggregate%20Function<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">Let me know if you run into any problems with it. It has worked well <br></blockquote><blockquote type="cite">for the samples I have used it on.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">Thanks,<br></blockquote><blockquote type="cite">Regina<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">_______________________________________________<br></blockquote><blockquote type="cite">postgis-users mailing list<br></blockquote><blockquote type="cite">postgis-users@postgis.refractions.net<br></blockquote><blockquote type="cite">http://postgis.refractions.net/mailman/listinfo/postgis-users<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">_______________________________________________<br></blockquote><blockquote type="cite">postgis-users mailing list<br></blockquote><blockquote type="cite">postgis-users@postgis.refractions.net<br></blockquote><blockquote type="cite">http://postgis.refractions.net/mailman/listinfo/postgis-users<br></blockquote><br>_______________________________________________<br>postgis-users mailing list<br>postgis-users@postgis.refractions.net<br>http://postgis.refractions.net/mailman/listinfo/postgis-users<br><br><br><br>_______________________________________________<br>postgis-users mailing list<br>postgis-users@postgis.refractions.net<br>http://postgis.refractions.net/mailman/listinfo/postgis-users<br></div></blockquote></div><br></div></body></html>