<table cellspacing="0" cellpadding="0" border="0" ><tr><td valign="top" style="font: inherit;">Hi Kevin,<br><br>I'm increasingly impressed with the analytical capability of Postgis, and with how fast it is increasing. I can do (& have done) pretty substantial analytical processing totally within the db, and have tools like QGIS to visualise the results & GMT for publication quality cartography. Postgis is much more than a data management & query tool.<br><br>And the developments are coming from both the Postgis & Postgres camps, as you demo here with the RECURSIVE capability.<br><br>It would be interesting to throw a recursive wrapper around your function to iterate through the 1m points until they are all assigned to a cluster, to see how long it takes... but once done, the clusters are there to be used, assuming it is a one off assignment :-) <br><br>Cheers,<br><br> Brent<br><br>--- On <b>Wed, 12/8/10, Kevin Neufeld
<i><kneufeld.ca@gmail.com></i></b> wrote:<br><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br>From: Kevin Neufeld <kneufeld.ca@gmail.com><br>Subject: Re: [postgis-users] Determining clusters of points<br>To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net><br>Date: Wednesday, December 8, 2010, 6:59 PM<br><br><div class="plainMail">Here's the RECURSIVE script run over a dataset of 20000 random points. I overlayed the points with a polygonal layer of the points buffered and grouped by the group id for visual purposes. I must say, I like this RECURSIVE business (though it's not as performant as I hoped) ... this would have been real handy years ago when I was doing network analysis on a connected linear dataset.<br><br>-- Kevin<br><br><br>On 12/7/2010 9:20 PM, Kevin Neufeld wrote:<br>> On 12/7/2010 5:15 PM, <a ymailto="mailto:pcreso@pcreso.com"
href="/mc/compose?to=pcreso@pcreso.com">pcreso@pcreso.com</a> wrote:<br>>> You might look at the newish (v8.4+ ?) RECURSIVE/WITH capabilities of Postgres.<br>>> <br>>> Given Postgis allows you to create arbitrary buffer zones around your points, the RECURSIVE query capability allows you to write an SQL that will recursively track points which can interact with each other<br>> <br>> So, just playing around a bit, I came up with this RECURSIVE query statement that will find all points in a particular "cluster". Every time I run the UPDATE statement, another cluster of points is identified and updated to have the same group id. I have no idea how this would perform over a table of a million points ...<br>> <br>> <br>> CREATE TABLE pts (id serial, grp int, geom geometry);<br>> CREATE SEQUENCE pts_grp_seq; -- to label cluster groups<br>> <br>> -- Sample data similiar to Sébastien's original post<br>>
INSERT INTO pts (geom)<br>> SELECT (ST_Dump(<br>> 'MULTIPOINT (<br>> 6 129,<br>> 21 200,<br>> 30 66,<br>> 33 109,<br>> 41 137,<br>> 48 191,<br>> 76 119,<br>> 90 197,<br>> 175 212,<br>> 178 230,<br>> 182 53,<br>> 196 209,<br>> 259 124,<br>> 259 148<br>> )')).geom;<br>> <br>> -- Simple wrapper around an UPDATE statement that identifies a<br>> -- point cluster everytime its run<br>> CREATE OR REPLACE FUNCTION cluster_pts() RETURNS text AS<br>> $body$<br>> DECLARE<br>> results int;<br>> BEGIN<br>> LOOP<br>>
-- Assign all records in the same cluster, the same group id<br>> UPDATE pts<br>> SET grp = f.grp<br>> FROM (<br>> WITH RECURSIVE t(id, geom, grp, path, cycle) AS (<br>> -- Grab the first record that doesn't have a group id<br>> (SELECT id, geom, nextval('pts_grp_seq'), ARRAY[id], false<br>> FROM pts<br>> WHERE grp IS NULL<br>> LIMIT 1<br>> )<br>> UNION ALL<br>> -- Find all the other records that are
iteratively within some<br>> -- distance to the starting point.<br>> -- This includes A to B to C paths, but not A to B to A cyclic paths<br>> SELECT a.id, a.geom, b.grp, path || ARRAY[a.id], a.id = ANY(path)<br>> FROM pts a, t b<br>> WHERE a.id != b.id<br>> AND ST_DWithin(a.geom, b.geom, 80)<br>> AND NOT cycle<br>> )<br>> SELECT DISTINCT id, grp FROM t<br>> ) AS f<br>> WHERE pts.id = f.id;<br>> <br>> -- repeat until no more
updates are performed.<br>> GET DIAGNOSTICS results = ROW_COUNT;<br>> EXIT WHEN results = 0;<br>> END LOOP;<br>> <br>> RETURN 'done.';<br>> END;<br>> $body$ LANGUAGE plpgsql;<br>> <br>> SELECT cluster_pts();<br>> <br>> <br></div><br>-----Inline Attachment Follows-----<br><br><div class="plainMail">_______________________________________________<br>postgis-users mailing list<br><a ymailto="mailto:postgis-users@postgis.refractions.net" href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></blockquote></td></tr></table>