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

[Obe, Regina]

This time I think I made some progress.  I guess timings could be better
but definitely an improvement.  My last cascade attempt had major flaws.

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_unite_garray(ARRAY[st_unite_garray(($1)[1:50]),
st_unitecascade_garray(($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) < 51  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 ~7937 ms, 7859 ms
SELECT ST_Union(the_geom)
from test_bubbles;

--time ~5765 ms, 5844 ms
SELECT ST_CascadeUnion(the_geom)
from test_bubbles;

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

--time ~3985 ms, 4484 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.