[postgis-users] Cascaded Union Aggregate function
Dane Springmeyer
blake at hailmail.net
Sun Oct 5 22:46:28 PDT 2008
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081005/c5f0397d/attachment.html>
More information about the postgis-users
mailing list