<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16705" name=GENERATOR></HEAD>
<BODY
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>Dane,</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>Nice to hear. I ran on your set too, but ran thru the
whole thing</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>SELECT ST_NPoints(upgis_cascadeunion(the_geom)) As
afterpoints, Sum(ST_NPoints(the_geom)) as beforepoints<BR>FROM
npsa_albers</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>Took SELECT 259329/1000.0/60 ms => 4.322
minutes</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>N Points after = </FONT></SPAN><SPAN
class=848251317-05102008><FONT face=Arial color=#0000ff
size=2>163,612;</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=848251317-05102008><FONT face=Arial
color=#0000ff size=2>N Points Before = 1,069,320;</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>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 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> </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
[mailto:postgis-users-bounces@postgis.refractions.net] <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">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>