<!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.7653.38">
<TITLE>RE: [postgis-devel] CascadedUnion Early Results</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<BR>
<P><FONT SIZE=2>Ah okay that looks good. I think the numbers look like about what I got on JTS.<BR>
<BR>
I have to dig up that set Kevin had given me way back when. Kevin you don't still happen to have that do you? I think it was named sample_poly.zip<BR>
<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-devel-bounces@postgis.refractions.net on behalf of Paul Ramsey<BR>
Sent: Tue 1/20/2009 5:08 PM<BR>
To: PostGIS Development Discussion<BR>
Subject: Re: [postgis-devel] CascadedUnion Early Results<BR>
<BR>
Here's your test, slightly modified. Shows about 30-times improvement<BR>
over traditional union.<BR>
<BR>
SELECT state,<BR>
SUM(ST_NPoints(the_geom)) As numpointsbefore,<BR>
ST_NPoints(ST_Union(the_geom)) As numpointsafter<BR>
FROM usstatebounds<BR>
GROUP BY state<BR>
ORDER BY state;<BR>
<BR>
Time: 579121.791 ms<BR>
<BR>
SELECT state,<BR>
SUM(ST_NPoints(the_geom)) As numpointsbefore,<BR>
ST_NPoints(ST_Union_Fast(the_geom)) As numpointsafter<BR>
FROM usstatebounds<BR>
GROUP BY state<BR>
ORDER BY state;<BR>
<BR>
Time: 21145.717 ms<BR>
<BR>
<BR>
On Tue, Jan 20, 2009 at 1:42 PM, Obe, Regina <robe.dnd@cityofboston.gov> wrote:<BR>
> Paul,<BR>
><BR>
> I think we can do better.<BR>
><BR>
> As I recall with my cascade union tests I was getting comparable speed<BR>
> and JTS was about 2-10 times faster.<BR>
><BR>
><BR>
> Can you test the attached data set out. Then it will be a closer<BR>
> comparison. I think for this set JTS was about 20 seconds.<BR>
><BR>
> Below is the link to my results on this way back.<BR>
><BR>
> <A HREF="http://postgis.refractions.net/support/wiki/index.php?PL%2FPGSQL%20Pseud">http://postgis.refractions.net/support/wiki/index.php?PL%2FPGSQL%20Pseud</A><BR>
> o%20Cascade%20Union%20Aggregate%20Function<BR>
><BR>
><BR>
> (though I don't have the JTS one listed there)<BR>
><BR>
> Damn I hope this is the right dataset. I'll retest on my other box but<BR>
> I have so many of these lying around.<BR>
><BR>
> Would also be interesting to repeat Kevin's original test that started<BR>
> this all out. I can send you that dataset off list.<BR>
><BR>
> Thanks,<BR>
> Regina<BR>
><BR>
><BR>
><BR>
><BR>
> -----Original Message-----<BR>
> From: postgis-devel-bounces@postgis.refractions.net<BR>
> [<A HREF="mailto:postgis-devel-bounces@postgis.refractions.net">mailto:postgis-devel-bounces@postgis.refractions.net</A>] On Behalf Of Mark<BR>
> Cave-Ayland<BR>
> Sent: Tuesday, January 20, 2009 4:34 PM<BR>
> To: PostGIS Development Discussion<BR>
> Subject: Re: [postgis-devel] CascadedUnion Early Results<BR>
><BR>
> Paul Ramsey wrote:<BR>
><BR>
>> For completeness, the whole table, about 4x faster:<BR>
>><BR>
>> uniontest=# select count(*) from counties;<BR>
>> count<BR>
>> -------<BR>
>> 3140<BR>
>> (1 row)<BR>
>><BR>
>> uniontest=# select st_area(st_union(the_geom)) from counties;<BR>
> st_area<BR>
>> ------------------<BR>
>> 1095.88034519544<BR>
>> (1 row)<BR>
>><BR>
>> Time: 42352.399 ms<BR>
>><BR>
>> uniontest=# select st_area(st_union_fast(the_geom)) from counties;<BR>
>> st_area<BR>
>> ------------------<BR>
>> 1095.88034519544<BR>
>> (1 row)<BR>
>><BR>
>> Time: 11493.832 ms<BR>
><BR>
> Fascinating. What would be really interesting would be to compare the<BR>
> times with the same dataset in JTS so we can get a feel as to whether<BR>
> there is any more overhead in the array_accum/GEOS conversion code.<BR>
><BR>
><BR>
> ATB,<BR>
><BR>
> Mark.<BR>
><BR>
> --<BR>
> Mark Cave-Ayland<BR>
> Sirius Corporation - The Open Source Experts<BR>
> <A HREF="http://www.siriusit.co.uk">http://www.siriusit.co.uk</A><BR>
> T: +44 870 608 0063<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>
> -----------------------------------------<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>
> 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>
_______________________________________________<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>
</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>