[postgis-devel] Lame attempt at cascade union in sql

[Obe, Regina]

I have to say I'm pretty disappointed with my attempts at simulating
cascaded unions with sql only functions.  Not sure where I went wrong or
perhaps its because my test data set is so unrealistic.  It seems in
general better but only marginally.  Perhaps someone else can see where
I went wrong.

Anyrate below is my attempt at simulating a cascaded union aggregate
with nothing but sql functions:



CREATE OR REPLACE FUNCTION st_unitecascade_garray(geometry[])
  RETURNS geometry AS
$$
	SELECT CASE WHEN array_upper($1,1) < 51 THEN st_unite_garray($1)
ELSE
		ST_unitecascade_garray(st_unite_garray(($1)[1:50]) ||
($1)[51:array_upper($1,1)] )  END;
$$
  LANGUAGE 'sql' IMMUTABLE;


CREATE OR REPLACE FUNCTION st_unitecascade_garray_sort(geometry[])
  RETURNS geometry AS
$$
	SELECT CASE WHEN array_upper($1,1) < 50  THEN
st_unite_garray($1) ELSE
		st_unitecascade_garray(ARRAY(SELECT ($1)[s] FROM
generate_series(1, array_upper($1, 1)) AS s 
			ORDER BY ST_Centroid(($1)[s]) ) ) END;
$$
  LANGUAGE 'sql' IMMUTABLE;
  
DROP AGGREGATE st_cascadeunion(geometry);

CREATE AGGREGATE st_cascadeunion(geometry) (
  SFUNC=st_geom_accum,
  STYPE=geometry[],
  FINALFUNC=st_unitecascade_garray_sort
);


--DROP TABLE test_bubbles;
CREATE TABLE test_bubbles(gid serial PRIMARY KEY, thegroup int, the_geom
geometry);
TRUNCATE TABLE test_bubbles;
CREATE INDEX idx_test_bubbles_the_geom
  ON test_bubbles
  USING gist
  (the_geom)
  WITH (FILLFACTOR=100);

INSERT INTO test_bubbles(the_geom,thegroup)
SELECT ST_Buffer(ST_MakePoint(x,y),r), r*random()::int
FROM generate_series(-100,400,50) x, generate_series(-100, 1000,50) y,
generate_series(5,305,50) r;

VACUUM analyze test_bubbles;
--time ~8313 ms
SELECT ST_Union(the_geom)
from test_bubbles;

--time ~8234 ms
SELECT ST_CascadeUnion(the_geom)
from test_bubbles;

--time ~6015 ms
SELECT ST_Union(the_geom), thegroup, count(the_geom) as num_geoms
from test_bubbles
GROUP BY thegroup;

--time ~6000 ms
SELECT ST_CascadeUnion(the_geom),thegroup, count(the_geom) as num_geoms
from test_bubbles
GROUP BY thegroup;
-----------------------------------------
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.