[postgis-users] Cascaded Union Aggregate function

Dane Springmeyer blake at hailmail.net
Mon Oct 6 12:40:45 PDT 2008 

Hi Regina,


On Oct 6, 2008, at 2:27 AM, Paragon Corporation wrote:

> Dane,
>
> Thanks for testing this out.
>
> That sounds about right.  Yah sorry I missed that bottom part of  
> your ST_Union.  I realized that after the fact.  Have you ever tried  
> running it against the whole dataset (I mean union the whole table  
> into a single geometry) with ST_Union.


Oh my. I don't dare do that except overnight. I would happily do that  
with your new cascadeunion however!

> That was the one I was testing.  My timings for your example look  
> about the same.
>
great.

> That's strange though that OpenJump finishes in 46.3 seconds.  I  
> would have expected it to do better.  Were you using the snapshot  
> build?  The production version doesn't have the Cascade Union  
> functionality in it.
>

Oh, whoops I downloaded the latest stable release I think (Version 1.2  
(Release F) April 5th, 2008). I'll go back and try to get the latest.


Dane


>
> Thanks,
> Regina
>
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net 
> ] On Behalf Of Dane Springmeyer
> Sent: Monday, October 06, 2008 1:46 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Cascaded Union Aggregate function
>
> Regina,
>
>
> On Oct 5, 2008, at 10:29 AM, Paragon Corporation wrote:
>>
>>
>> I was afraid to try this using the current ST_Union (you remember  
>> by chance how long your below takes with ST_Union?)
>>
>
> Oh, I missed addressing this question in my last email. Yes, I  
> indicated how long ST_Union took below (it took 2.49 minutes).
>
>> I tried unioning the shape in OpenJump version that has the cascade  
>> union and it took about 5 minutes.
>>
>
> 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'.
>
> 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.
>
>
> Cheers,
>
> Dane
>
>
>>
>> I'll have to try that again since in all the tests
>> 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.
>>
>> Thanks,
>> Regina
>>
>>
>>
>>
>>
>> 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 12:59 PM
>> To: PostGIS Users Discussion
>> Subject: Re: [postgis-users] Cascaded Union Aggregate function
>>
>> 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
>>
>> _______________________________________________
>> 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/20081006/6224e578/attachment.html>

More information about the postgis-users mailing list