[postgis-devel] More Cascade Union Adventures

[Obe, Regina]

I just realized I had some excess baggage from my previous attempts.
Check these out - now I just have to test with more test cases (and completely verify the resulting geometries are indeed the same - cursory glance suggests they are)
--   223,563 ms - SELECT 223563/1000.00/60 = 3.72 minutes
SELECT ST_CascadeUnion(the_geom)
FROM (SELECT the_geom FROM sample_poly) As foo;

--39,406 ms = 39.41 secs = wow! SELECT 39406/1000.00
SELECT st_unitecascade_garray_sort(ARRAY(SELECT the_geom FROM sample_poly));

-- 67,797 ms = SELECT 67797/1000.00/60 = 1.13 minutes
SELECT ST_Union(the_geom) AS the_geom
FROM (
   SELECT min(id) AS id, ST_Union(the_geom) AS the_geom
   FROM (
     SELECT min(id) AS id, ST_Union(the_geom) AS the_geom
     FROM (
       SELECT min(id) AS id, ST_Union(the_geom) AS the_geom
       FROM (
         SELECT min(id) AS id, ST_Union(the_geom) AS the_geom
         FROM (SELECT the_geom, id FROM sample_poly) As foo
         GROUP BY round(id/10)
         ORDER BY id) AS tmp1
       GROUP BY round(id/100)
       ORDER BY id) AS tmp2
     GROUP BY round(id/1000)
     ORDER BY id) AS tmp3
   GROUP BY round(id/10000)
   ORDER BY id) AS tmp4
GROUP BY round(id/100000);

--Aggregate function code

CREATE OR REPLACE FUNCTION st_unitecascade_garray(geometry[], iter int,
startcount int)
  RETURNS geometry AS
$$
SELECT CASE WHEN array_upper($1,1) < 5 OR  array_upper($1,1) BETWEEN 0
AND ln($3 + 1)/ln($2 + 1) THEN st_unite_garray($1) ELSE
	st_unitecascade_garray(ARRAY(SELECT st_unite_garray($1[i:least(i
+ CAST(ln($3 + 1)/ln($2 + 1) As int) - 1,array_upper($1,1))]) As geom
FROM generate_series(1, array_upper($1,1), CAST(ln($3 + 1)/ln($2 + 1) As
int)) As i), $2 + 1, $3) END

$$
  LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION st_unitecascade_garray_sort(geometry[])
  RETURNS geometry AS
$$
        SELECT CASE WHEN array_upper($1,1) < 10  THEN
st_unite_garray($1) ELSE
                st_unitecascade_garray($1, 2, array_upper($1,1)) 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
);


-----------------------------------------
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-devel/attachments/20080813/36382aef/attachment.html>