Sunpeng,<div><br></div><div>There are some things you can do using the power of Procedural Languages in Postgres and Custom Aggregates. In particular you might want to take a look at PL/R. I use PL/R along with PostGIS for creating K-Means classification on attributes for "natural breaks" styling with MapServer. It should be possible to use this for spatial aggregation as well, not to say it is a trivial thing to do, but you can get the type of results you are after even if you don't have the nice syntax that you are talking about that would be a nightmare as Kevin points out.</div>
<div><br></div><div>David<br><br><div class="gmail_quote">On Wed, Feb 10, 2010 at 3:44 PM, Kevin Neufeld <span dir="ltr"><<a href="mailto:kneufeld@refractions.net">kneufeld@refractions.net</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
You want to extend the SQL language by adding the CLUSTERING keyword and then extend PostGIS to somehow implement the clustering?<br>
<br>
Sorry, but I think this would be nothing less than an enormous/colossal/massive undertaking.<br>
<br>
I'm totally guessing here, but at a very high level I think the steps might be<br>
<br>
1. Add the keyword CLUSTERING to PostgreSQL's Parser<br>
<a href="http://www.postgresql.org/docs/8.3/static/parser-stage.html" target="_blank">http://www.postgresql.org/docs/8.3/static/parser-stage.html</a><br>
(the Postgresql development lists should be able to help you out here)<br>
<br>
2. Somehow add an operator (or set of operators) that define CLUSTERING. As I understand it, ORDER BY using the typical ordering operators (<, >, =, <=, etc) to perform it's operations. Similiarly, GROUP BY uses the = operator. PostGIS implements these operators as function calls that operate on the bounding box of geometries. This ultimately allows you to issue a "ORDER BY" or "GROUP BY" types of queries against geometries. I would think you would need to do something similar for your CLUSTERING idea. Create some operator[s] that only the CLUSTERING keyword understands. Again, try the Postgresql development lists for help here.<br>
<br>
3. Dive into PostGIS source and implement the CLUSTERING operator[s] you defined in step 2, which I assume your going to implement something like k-means clustering (which by itself is not trivial).<br>
<br>
4. Polygonize your clustered points using PostGIS. This is the easiest as it's a single sql query using the ST_ConvexHull function.<br>
<br>
<br>
Sorry to burst your enthusiasm, but I think you'd be far better off to try to implement k-means (or the clustering algorithm of your choice) external to the database using your favorite language (Java, C, Python, ...). Extract data from the database, cluster, save the results back in the database, and polygonize.<br>
<br>
-- Kevin<div><div></div><div class="h5"><br>
<br>
On 2/10/2010 12:36 PM, sunpeng wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div></div><div class="h5">
<br>
hi,Kevin,thanks for your help.<br>
<br>
Now, I'll explain my initial motivation.<br>
<br>
Suppose we have a table with:<br>
create table houses (<br>
NAME VARCHAR(128) not null,<br>
);<br>
SELECT AddGeometryColumn('houses', 'location', 4214, 'POINT', 2);<br>
<br>
if we want to do clustering(in data mining environment) the houses<br>
on location, that is, clustering those house near as a cluster(or a<br>
group), and then calculate each cluster's shape, we can not use the<br>
following sql:<br>
<br>
select ST_Boundary(*)<br>
from houses<br>
group by location<br>
<br>
I would like to extend the postgresql or postgis to support the<br>
following sql:<br>
<br>
select ST_Boundary(*)<br>
from houses<br>
clustering by location<br>
<br>
in which the following steps are considered:<br>
1. cluster the houses into several goups<br>
2. calculate each goup's shape<br>
<br>
I know the second step is usually related to aggregation functions<br>
like sum,count,and so on which i don't care too much now. All what I<br>
want to do is to add this sql semantics into postgresql or postgis and<br>
then add a clustering algorithm like dbscan in the executor in<br>
postgresql(or postgis?).<br>
How could I do? Any detailed steps?Like I should modify kwlist.h to<br>
support "CLUSTERING" keyword, and the following steps?<br>
<br>
Thanks!<br>
peng<br>
<br>
---------------------------------------------------------------------------<br>
<br>
we all know<br>
<br>
I'm not sure I follow. Can you explain what exactly you want to do?<br>
<br>
The following query will collect points into clusters (multipoints),<br>
clustered on a 100x100 grid.<br>
<br>
-- generate a sample random point dataset<br>
CREATE TABLE points AS<br>
SELECT ST_MakePoint(random()*1000, random()*1000) AS geom<br>
FROM generate_series(1, 100000);<br>
<br>
-- create point clusters<br>
SELECT st_collect(geom)<br>
FROM points<br>
GROUP BY<br>
round(st_x(geom)/100)*100,<br>
round(st_y(geom)/100)*100;<br>
<br>
<br>
Kevin<br>
<br>
<br>
On 2/4/2010 11:26 PM, sunpeng wrote:<br>
> I want to write "cluster by" instead of "group by" on geospatial<br>
point<br>
> data,should I write the code at postgresql or postgis ?<br>
> thanks<br>
><br>
> peng<br>
<br>
<br>
<br>
<br></div></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">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>
</blockquote>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">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>
</blockquote></div><br><br clear="all"><br>-- <br>************************************<br>David William Bitner<br>
</div>