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. That was the one I was testing. My timings for your example look about the same. 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. Thanks, Regina _____
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
