[postgis-users] Cascaded Union Aggregate function

Mon Oct 6 04:02:06 PDT 2008

Dane,

I just ran the full union set in OpenJump and it took 55 seconds.  

Running your below for me with the OpenJump that contains the Cascade
Union functionality takes 30 seconds.

So don't know what was wrong with the other pc I was on.  Maybe I had
too much going on or there is something flawed with the snapshot I was
using on that box (it was definitely cascade union though because the
non-cascade one has a count down and ran out of memory when trying to do
the whole set).  OpenJump cascade union still wins hands down :).

I tried unioning on this same box with my PostGIS install for good
comparison and my timings for the 

--69 secs
select upgis_cascadeunion(the_geom) from npsa_albers group by
wholedrain;

and
--  61 secs
select ST_NPoints(upgis_cascadeunion(the_geom)) from npsa_albers group
by wholedrain;

I'm going to assume its a bit slower on this box because I haven't
tweaked my postgresql.conf on this machine.

Next I'll test on my linux 1.3.4 SVN install.

Thanks,
Regina

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Paragon Corporation
Sent: Monday, October 06, 2008 5:30 AM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] Cascaded Union Aggregate function

Dan,

Disregard my last email except the OpenJump part.  I was reading out of
order.

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:12 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Cascaded Union Aggregate function

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/00341
2.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/00341
2.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

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20081006/d0d91427/attachment.html>