<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Regina,<div><br></div><div><br><div><div>On Oct 5, 2008, at 10:29 AM, Paragon Corporation wrote:</div><blockquote type="cite"><div style="WORD-WRAP: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space"><div dir="ltr" align="left"><font class="Apple-style-span" color="#0000FF" face="Arial" size="2"><span class="Apple-style-span" style="font-size: 10px;"><br></span></font></div> <div dir="ltr" align="left"><span class="848251317-05102008"><font face="Arial" color="#0000ff" size="2"></font></span> </div> <div dir="ltr" align="left"><span class="848251317-05102008"><font face="Arial" color="#0000ff" size="2">I was afraid to try this using the current ST_Union (you remember by chance how long your below takes with ST_Union?)</font></span></div> <div dir="ltr" align="left"><span class="848251317-05102008"><font face="Arial" color="#0000ff" size="2"></font></span> </div></div></blockquote><div><br></div>Oh, I missed addressing this question in my last email. Yes, I indicated how long ST_Union took below (it took 2.49 minutes).</div><div><br><blockquote type="cite"><div style="WORD-WRAP: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space"> <div dir="ltr" align="left"><span class="848251317-05102008"><font face="Arial" color="#0000ff" size="2">I tried unioning the shape in OpenJump version that has the cascade union and it took about 5 minutes. </font></span></div> <div dir="ltr" align="left"><span class="848251317-05102008"><font face="Arial" color="#0000ff" size="2"></font></span><br></div></div></blockquote><div><br></div>I've never used OpenJump before but just loaded it up and went to TOOLS> Analysis > Union by attribute value. Then I ran that tool with all the options checked using the same attribute field of 'wholedrain'.</div><div><br></div><div>I figure this is the equivalent of this query: 'select upgis_cascadeunion(the_geom) from npsa group by wholedrain' which ran in 50.91 seconds. The OpenJump query took 46.3 seconds.</div><div><br></div><div><br></div><div>Cheers,</div><div><br></div><div>Dane</div><div><br></div><div><br><blockquote type="cite"><div style="WORD-WRAP: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space"><div dir="ltr" align="left"> </div> <div dir="ltr" align="left"><span class="848251317-05102008"><font face="Arial" color="#0000ff" size="2">I'll have to try that again since in all the tests</font></span></div> <div dir="ltr" align="left"><span class="848251317-05102008"><font face="Arial" color="#0000ff" size="2">I have run OpenJump has always been faster (unfortunately where I am at the moment, my PostgreSQL is running on server and OpenJump with shape locally, so that may not have been a fair test). But it seems to end up with the same number of points of 163,612.</font></span></div> <div dir="ltr" align="left"><span class="848251317-05102008"><font face="Arial" color="#0000ff" size="2"></font></span> </div> <div dir="ltr" align="left"><span class="848251317-05102008"><font face="Arial" color="#0000ff" size="2">Thanks,</font></span></div> <div dir="ltr" align="left"><span class="848251317-05102008"><font face="Arial" color="#0000ff" size="2">Regina</font></span></div> <div dir="ltr" align="left"><span class="848251317-05102008"><font face="Arial" color="#0000ff" size="2"></font></span> </div> <div dir="ltr" align="left"><span class="848251317-05102008"><font face="Arial" color="#0000ff" size="2"></font></span> </div> <div dir="ltr" align="left"><span class="848251317-05102008"><font face="Arial" color="#0000ff" size="2"></font></span> </div> <div dir="ltr" align="left"><span class="848251317-05102008"><font face="Arial" color="#0000ff" size="2"></font></span> </div><br> <div class="OutlookMessageHeader" lang="en-us" dir="ltr" align="left"> <hr tabindex="-1"> <font face="Tahoma" size="2"><b>From:</b> postgis-users-bounces@postgis.refractions.net [<a href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>] <b>On Behalf Of </b>Dane Springmeyer<br><b>Sent:</b> Sunday, October 05, 2008 12:59 PM<br><b>To:</b> PostGIS Users Discussion<br><b>Subject:</b> Re: [postgis-users] Cascaded Union Aggregate function<br></font><br></div> <div></div>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: 0px; FONT: 12px '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: 0px; FONT: 12px 'Lucida Grande'; COLOR: rgb(85,85,85)">--3162 records</div> <div style="MIN-HEIGHT: 15px; MARGIN: 0px; FONT: 12px 'Lucida Grande'"><br></div> <div style="MARGIN: 0px; FONT: 12px '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: 0px; FONT: 12px 'Lucida Grande'; COLOR: rgb(85,85,85)">-- 48.39 sec</div> <div style="MIN-HEIGHT: 15px; MARGIN: 0px; FONT: 12px 'Lucida Grande'"><br></div> <div style="MARGIN: 0px; FONT: 12px '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: 0px; FONT: 12px 'Lucida Grande'; COLOR: rgb(85,85,85)">-- 50.91 sec</div> <div style="MIN-HEIGHT: 15px; MARGIN: 0px; FONT: 12px 'Lucida Grande'"><br></div> <div style="MARGIN: 0px; FONT: 12px '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: 0px; FONT: 12px 'Lucida Grande'; COLOR: rgb(85,85,85)">-- 2.49 minutes</div> <div style="MIN-HEIGHT: 15px; MARGIN: 0px; FONT: 12px 'Lucida Grande'"><br></div> <div style="MARGIN: 0px; FONT: 12px '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: 0px; FONT: 12px 'Lucida Grande'; COLOR: rgb(85,85,85)">-- 10.73 seconds</div> <div><font class="Apple-style-span" face="'Lucida Grande'" color="#555555"><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"><a href="http://postgis.refractions.net/support/wiki/index.php?upgis_cascadeuni">http://postgis.refractions.net/support/wiki/index.php?upgis_cascadeuni</a><br></blockquote> <blockquote type="cite">on<br></blockquote> <blockquote type="cite"><br></blockquote> <blockquote type="cite">-----Original Message-----<br></blockquote> <blockquote type="cite">From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br></blockquote> <blockquote type="cite">[<a href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</a>] 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"><a href="http://postgis.refractions.net/support/wiki/index.php?PL%2FPGSQL%20Pse">http://postgis.refractions.net/support/wiki/index.php?PL%2FPGSQL%20Pse</a><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"><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br></blockquote> <blockquote type="cite"><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><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"><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br></blockquote> <blockquote type="cite"><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></blockquote><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><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></div> _______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>http://postgis.refractions.net/mailman/listinfo/postgis-users<br></blockquote></div><br></div></body></html>