I have a Postgres/PostGIS database of
geography points which I'm trying to cluster; meaning I want to group and count them by proximity. I have had first success with the kmeans Postgresql extension, but the algorithm is not optimal for what I require. I want to experiment with more algorithms, and R seems to be the best choice for this. I have set up PL/R and can call R functions from Postgres...
But what now? A simple R function which returns a scalar is no problem, but I'm not sure how to approach even the same kmeans solution in PL/R, yet alone implementing a whole clustering algorithm solution. The aforelinked kmeans extension is very approachable to me, with a query like:
SELECT kmeans, count(*) FROM ( SELECT kmeans(ARRAY[ST_X(geom), ST_Y(geom)], 5) OVER (), geom FROM points ) AS ksub GROUP BY kmeans;
kmeans() only returns a single
int this is very comprehensible to me. But I'm not sure where to start with some of the more complex R clustering solutions. Would it be better to post-process the entire result set in R, like
SELECT r_cluster((SELECT ...))? Can I use a
WINDOW function, in which case what would I return from R and how?
I'd like to see one actual code sample of a PL/R function declaration and invocation to get started on this.
-- please fill in the blanks CREATE OR REPLACE FUNCTION r_cluster() RETURNS ? AS $$ ? $$ LANGUAGE plr; SELECT r_cluster(?) OVER (?) FROM points;