<!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] Lame attempt at cascade union in sql</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<BR>
<P><FONT SIZE=2>Actually I realized the solution may be much simpler and not require recursion and would be much faster than what I sent you. I'll try that probably and let you know how it goes.<BR>
<BR>
-----Original Message-----<BR>
From: postgis-devel-bounces@postgis.refractions.net on behalf of Kevin Neufeld<BR>
Sent: Thu 8/7/2008 3:46 PM<BR>
To: PostGIS Development Discussion<BR>
Subject: Re: [postgis-devel] Lame attempt at cascade union in sql<BR>
<BR>
Hi Regina,<BR>
<BR>
So, I ran a few tests on a table with ~30000 overlapping polygons.<BR>
<BR>
Test1:<BR>
SELECT ST_Union(the_geom) FROM sample_poly;<BR>
-- Total runtime: + 2 hours before I cancelled it.<BR>
<BR>
<BR>
Test2:<BR>
SELECT ST_CascadeUnion(the_geom) FROM sample_poly;<BR>
-- Total runtime: 19 minutes.<BR>
<BR>
<BR>
Test3:<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 sample_poly<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>
-- Total runtime: 1.5 minutes<BR>
<BR>
<BR>
Test4:<BR>
Reordered the tuples in sample_poly by "ORDER BY<BR>
ST_SnapToGrid(ST_Centroid(the_geom), 0.01);"<BR>
Reran query in Test3:<BR>
-- Total runtime: 50 secs<BR>
<BR>
<BR>
So, your function is a definite improvement over ST_Union, but the<BR>
timings could still be better.<BR>
<BR>
Cheers,<BR>
Kevin<BR>
<BR>
Obe, Regina wrote:<BR>
> Kevin,<BR>
><BR>
> Can you try the cascade approach I sent in my last email with your large<BR>
> dataset.<BR>
><BR>
> I tried it with my real data and here are the results<BR>
><BR>
> --time 3328 ms, 3203 ms, 3188 ms<BR>
> SELECT ST_CascadeUnion(the_geom)<BR>
> FROM (SELECT * FROM landparcels<BR>
> WHERE pid lIKE '01%' and the_geom is not null<BR>
> LIMIT 1000) p<BR>
><BR>
> --time 23375 ms, 23594 ms, 24109 ms<BR>
> SELECT ST_Union(the_geom)<BR>
> FROM (SELECT * FROM landparcels<BR>
> WHERE pid lIKE '01%' and the_geom is not null<BR>
> LIMIT 1000) p<BR>
> -----------------------------------------<BR>
> The substance of this message, including any attachments, may be<BR>
> confidential, legally privileged and/or exempt from disclosure<BR>
> pursuant to Massachusetts law. It is intended<BR>
> solely for the addressee. If you received this in error, please<BR>
> contact the sender and delete the material from any computer.<BR>
><BR>
> _______________________________________________<BR>
> postgis-devel mailing list<BR>
> postgis-devel@postgis.refractions.net<BR>
> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-devel">http://postgis.refractions.net/mailman/listinfo/postgis-devel</A><BR>
_______________________________________________<BR>
postgis-devel mailing list<BR>
postgis-devel@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-devel">http://postgis.refractions.net/mailman/listinfo/postgis-devel</A><BR>
<BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>