[postgis-users] Cascaded Union Aggregate function

[Dane Springmeyer]

Regina,

Great, my query now works with your amended upgis function posting.

So, here are my timing outputs for the dataset on watersheds I just  
sent a reference to:

select count(*) from npsa;
--3162 records

select st_cascadeunion(the_geom) from npsa group by wholedrain;
-- 48.39 sec

select upgis_cascadeunion(the_geom) from npsa group by wholedrain;
-- 50.91 sec

select ST_Union(the_geom) from npsa group by wholedrain;
-- 2.49 minutes

select ST_Collect(the_geom) from npsa group by wholedrain;
-- 10.73 seconds

Next chance I get I'll take a look at the actual results.

Thanks!
Dane


On Oct 5, 2008, at 5:53 AM, Paragon Corporation wrote:

> Dane,
> Dane,
> Oops sorry about that .  I just posted a revised version.  Can you  
> try that
> out?  Thanks.
>
> Also can you send me a sample of your data off list.  I think the  
> issue was
> I was experimenting with array_append vs. st_geom_accum and I had  
> gotten the
> error you described below, but then was unable to replicate it again.
>
> It could be the detoasting affect of st_geom_accum that Mark had  
> described
> was the difference between the two.
>
> Thanks,
> Regina
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of  
> Dane
> Springmeyer
> Sent: Sunday, October 05, 2008 1:47 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Cascaded Union Aggregate function
>
> Hi Regina,
>
> Thank you so much for the wiki posting. I've been using your code  
> from your
> august 12th email with great success, if not joy. What an amazing  
> speed
> improvement, especially for such a critical function. This was your  
> exact
> email of code I have working:
> http://postgis.refractions.net/pipermail/postgis-devel/2008-August/003412.ht
> ml
>
> I've just downloaded the new code off the wiki, but I'm getting an  
> error of
> 'Unknown Geometry Type: 0'. I'll paste the whole error below in case  
> you
> have an idea what might be going on. I'm running mac 10.5 with these
> details:
>
>             postgis_version
> ---------------------------------------
>  1.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
> (1 row)
>
> --------------------------------------------------------------
>  PostgreSQL 8.3.3 on i386-apple-darwin9.4.0, compiled by GCC
>
>
> Cheers,
>
> Dane
>
>
> -- full error:
> test=# select upgis_cascadeunion(the_geom) from npsa group by  
> wholedrain;
>
> ERROR:  Unknown geometry type: 0
> CONTEXT:  SQL function "upgis_dump_collect_garray" statement 1
> SQL statement "SELECT
> upgis_dump_collect_garray( array_append(ARRAY(SELECT  $1 [s] As geom  
> FROM
> generate_series(1,  $2 ) As s WHERE NOT (s = ANY( $3 )) ),
> st_unite_garray(ARRAY(SELECT  $1 [s] As geom FROM generate_series(1,
> $2 ) As s WHERE s = ANY( $3 ) ) ) ) )"
> PL/pgSQL function "st_collect_unite_garray" line 29 at assignment SQL
> statement "SELECT  upgis_unitecascade_garray(ARRAY(SELECT
> st_collect_unite_garray( $1 [i:least(i +  $2  - 1, $3 )]) As geom FROM
> generate_series(1,  $3 , $2 ) As i),  $4  + 1,  $5 , false)"
> PL/pgSQL function "upgis_unitecascade_garray" line 48 at assignment
> SQL statement "SELECT
> upgis_dump_collect_garray( array_append(ARRAY(SELECT  $1 [ $2 [s]]  
> As geom
> FROM generate_series(1, array_upper( $2 ,1)) As s ),
> upgis_unitecascade_garray(ARRAY(SELECT  $1 [s] As geom FROM
> generate_series(1,  $3 ) As s WHERE NOT (s = ANY( $2 )) ),  $4  + 1,
> $5 , true) ) )"
> PL/pgSQL function "upgis_unitecascade_garray" line 33 at assignment  
> SQL
> function "upgis_unitecascade_garray_sort" statement 1
>
>
> On Oct 4, 2008, at 4:53 PM, Paragon Corporation wrote:
>
>>
>> Oops sorry about that.
>>
>> Just realized I had ugly spaces in the link.  I've changed it.  Use
>> this one
>>
>> http://postgis.refractions.net/support/wiki/index.php? 
>> upgis_cascadeuni
>> on
>>
>> -----Original Message-----
>> From: postgis-users-bounces at postgis.refractions.net
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
>> Paragon Corporation
>> Sent: Saturday, October 04, 2008 7:44 PM
>> To: 'PostGIS Users Discussion'
>> Subject: [postgis-users] Cascaded Union Aggregate function
>>
>> For those people who have unions to do that are slow, feel free to  
>> try
>> out this aggregate union function.
>>
>> I haven't tested it enough for it to make it into 1.3.4, but have
>> included a link to the source code in the wiki.  It should work just
>> fine on PostGIS versions 1.2.2 and above.
>>
>> http://postgis.refractions.net/support/wiki/index.php?PL%2FPGSQL 
>> %20Pse
>> udo%20 Cascade%20Union%20Aggregate%20Function
>>
>> Let me know if you run into any problems with it.  It has worked well
>> for the samples I have used it on.
>>
>> Thanks,
>> Regina
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081005/9b1281a3/attachment.html>