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

 

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 diffe