<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Lee,<br>
<br>
So here's what I found out. <br>
<br>
It is possible to perform the query you are after is a reasonable
amount of time. The trick here lies in the knowledge that
geomunion works well with a small number of geometries. On a 2.0Ghz
server here, I was able to
merge your sample_poly dataset in about 29 seconds, using about 60MB of
RAM. This approach is basically a start at what Martin was talking
about with the concept of the cascaded union - merge geometries
together based on their spatial proximity.<br>
<br>
Here are my steps:<br>
1. Create a custom ordering of the geometries in your samply_poly table
so that they feed into the geomunion aggregate in the right order.<br>
<tt>CREATE TABLE sample_poly2 (id serial, the_geom geometry);<br>
</tt><tt>-- This is essentially the same as creating an index on the
geometry column and CLUSTERing the table on that index.<br>
</tt><tt>INSERT INTO sample_poly2 (the_geom) <br>
SELECT the_geom <br>
FROM
sample_poly <br>
ORDER BY ST_SnapToGrid(ST_Centroid(the_geom), 0.01);</tt><br>
<br>
2. Union the geometries together in small amounts that are relationally
"close" to each other.<br>
<tt>-- Each subquery aggregates a set of 10 geometries. </tt><br>
<tt>CREATE TABLE union_result AS<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 (<br>
SELECT min(id) AS id, ST_Union(the_geom) AS the_geom<br>
FROM sample_poly2<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>
ORDER BY id;</tt><br>
<br>
The ordering of the geometries plays a critical role. I.E. If I change
the ORDER BY the_geom in step to:<br>
<ul>
<li><tt>ORDER BY random(); -- yields a runtime of about 220 seconds.</tt></li>
<li><tt>ORDER BY the_geom; -- </tt><tt>yields </tt><tt>a runtime of
about 55 seconds<br>
</tt></li>
<li><tt>ORDER BY </tt><tt>ST_SnapToGrid(ST_Centroid(the_geom), 0.01)</tt><tt>;
-- yields a
runtime of about 29 seconds.</tt></li>
</ul>
Replacing ST_Union(the_geom) in the second step with
ST_Buffer(ST_Collect(the_geom), 0) sometimes yielded a faster runtime,
but more often crashed the server. I would recommend using ST_Buffer
only
as
a last resort. It was never intended to operate in this manner - the
union-ing of the geometries is more or less a by product of
buffering by 0. <br>
<br>
Cheers,<br>
Kevin<br>
<tt><br>
</tt><br>
<pre class="moz-signature" cols="72">-------------
Kevin Neufeld
Software Developer
Refractions Research Inc.
300-1207 Douglas St.
Victoria, B.C., V8W 2E7
Phone: (250) 383-3022
Email: <a class="moz-txt-link-abbreviated" href="mailto:kneufeld@refractions.net">kneufeld@refractions.net</a></pre>
<br>
<br>
Lee Keel wrote:
<blockquote
cite="mid:76758090F8686C47A44B6FF52514A1D30929766A@hermes.uai.int"
type="cite">
<pre wrap=""><!---->[Lee Keel]
I am not sure how much the 'cascaded union' will help until it gets ported
to PostGIS.
As for the ArcGIS results, well.... I am as certain as I can be base on my
client. I have not actually performed this operation for them, but
according to my contact there those are accurate numbers for the same
dataset.
-Lee
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>