[postgis-users] Cascaded Union Aggregate function
Dane Springmeyer
blake at hailmail.net
Sun Oct 5 22:11:36 PDT 2008
Regina,
Okay, I've finished running your fuller test query included on the wiki.
Both of the variations on your cascade union timed the same, and
astonishingly faster that ST_Union:
SELECT ST_NPoints(upgis_cascadeunion(the_geom)) As afterpoints,
Sum(ST_NPoints(the_geom)) as beforepoints
FROM npsa;
-- 4.05 minutes
-- points before = 163612
-- points after = 1069320
SELECT ST_NPoints(ST_Union(the_geom)) As afterpoints,
Sum(ST_NPoints(the_geom)) as beforepoints
FROM npsa;
--161.85 min
-- points before = 163612
-- points after = 1069320
-- http://postgis.refractions.net/pipermail/postgis-devel/2008-August/003412.html
SELECT ST_NPoints(st_cascadeunion(the_geom)) As afterpoints,
Sum(ST_NPoints(the_geom)) as beforepoints
FROM npsa;
-- 4.06 minutes
-- points before = 163612
-- points after = 1069320
Dane
On Oct 5, 2008, at 10:29 AM, Paragon Corporation wrote:
> Dane,
>
> Nice to hear. I ran on your set too, but ran thru the whole thing
>
> SELECT ST_NPoints(upgis_cascadeunion(the_geom)) As afterpoints,
> Sum(ST_NPoints(the_geom)) as beforepoints
> FROM npsa_albers
>
> Took SELECT 259329/1000.0/60 ms => 4.322 minutes
>
> N Points after = 163,612;
> N Points Before = 1,069,320;
>
> I was afraid to try this using the current ST_Union (you remember by
> chance how long your below takes with ST_Union?)
>
> I tried unioning the shape in OpenJump version that has the cascade
> union and it took about 5 minutes.
>
> 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/7a8027ed/attachment.html>
More information about the postgis-users
mailing list