<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-devel] More Cascade Union Adventures</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>I just realized I had some excess baggage from my previous attempts.<BR>
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)<BR>
-- 223,563 ms - SELECT 223563/1000.00/60 = 3.72 minutes<BR>
SELECT ST_CascadeUnion(the_geom)<BR>
FROM (SELECT the_geom FROM sample_poly) As foo;<BR>
<BR>
--39,406 ms = 39.41 secs = wow! SELECT 39406/1000.00<BR>
SELECT st_unitecascade_garray_sort(ARRAY(SELECT the_geom FROM sample_poly));<BR>
<BR>
-- 67,797 ms = SELECT 67797/1000.00/60 = 1.13 minutes<BR>
SELECT ST_Union(the_geom) AS the_geom<BR>
FROM (<BR>
SELECT min(id) AS id, ST_Union(the_geom) AS the_geom<BR>
FROM (<BR>
SELECT min(id) AS id, ST_Union(the_geom) AS the_geom<BR>
FROM (<BR>
SELECT min(id) AS id, ST_Union(the_geom) AS the_geom<BR>
FROM (<BR>
SELECT min(id) AS id, ST_Union(the_geom) AS the_geom<BR>
FROM (SELECT the_geom, id FROM sample_poly) As foo<BR>
GROUP BY round(id/10)<BR>
ORDER BY id) AS tmp1<BR>
GROUP BY round(id/100)<BR>
ORDER BY id) AS tmp2<BR>
GROUP BY round(id/1000)<BR>
ORDER BY id) AS tmp3<BR>
GROUP BY round(id/10000)<BR>
ORDER BY id) AS tmp4<BR>
GROUP BY round(id/100000);<BR>
<BR>
--Aggregate function code<BR>
<BR>
CREATE OR REPLACE FUNCTION st_unitecascade_garray(geometry[], iter int,<BR>
startcount int)<BR>
RETURNS geometry AS<BR>
$$<BR>
SELECT CASE WHEN array_upper($1,1) < 5 OR array_upper($1,1) BETWEEN 0<BR>
AND ln($3 + 1)/ln($2 + 1) THEN st_unite_garray($1) ELSE<BR>
st_unitecascade_garray(ARRAY(SELECT st_unite_garray($1[i:least(i<BR>
+ CAST(ln($3 + 1)/ln($2 + 1) As int) - 1,array_upper($1,1))]) As geom<BR>
FROM generate_series(1, array_upper($1,1), CAST(ln($3 + 1)/ln($2 + 1) As<BR>
int)) As i), $2 + 1, $3) END<BR>
<BR>
$$<BR>
LANGUAGE 'sql' IMMUTABLE;<BR>
<BR>
CREATE OR REPLACE FUNCTION st_unitecascade_garray_sort(geometry[])<BR>
RETURNS geometry AS<BR>
$$<BR>
SELECT CASE WHEN array_upper($1,1) < 10 THEN<BR>
st_unite_garray($1) ELSE<BR>
st_unitecascade_garray($1, 2, array_upper($1,1)) END;<BR>
$$<BR>
LANGUAGE 'sql' IMMUTABLE;<BR>
--DROP AGGREGATE st_cascadeunion(geometry);<BR>
<BR>
CREATE AGGREGATE st_cascadeunion(geometry) (<BR>
SFUNC=st_geom_accum,<BR>
STYPE=geometry[],<BR>
FINALFUNC=st_unitecascade_garray_sort<BR>
);<BR>
</FONT>
</P>
</BODY>
</HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
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.
</STRONG></P></BODY></HTML>
<P><hr size=1></P>
<P><STRONG><font size="2" color="339900"> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </p> <p> </font></STRONG></P>