Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;

Since 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;
share|improve this question

1 Answer 1

up vote 1 down vote accepted

After some experimentation, I got this:

-- x and y are not actually used, they just give the function its signature
CREATE FUNCTION R_cluster_dbscan(x float8, y float8, eps float8)
RETURNS int
WINDOW
STRICT
VOLATILE
LANGUAGE plr
AS $$
    if (pg.state.firstpass == TRUE) {
        pg.state.firstpass <<- FALSE
        c <- fpc::dbscan(cbind(farg1, farg2), eps, MinPts = 2, method = "hybrid", seeds = FALSE)
        assign("cluster", c$cluster, env = .GlobalEnv)
    }

    return(cluster[prownum])
$$

This window function is called once for each row and returns the cluster id of the record, but it calculates the cluster only once using the DBSCAN algorithm and stores it in a global variable. It is used like:

  SELECT c                             AS cluster_id,
         COUNT(*)                      AS place_count,
         ST_Centroid(ST_Collect(geom)) AS center
    FROM (
             SELECT R_cluster_dbscan(ST_X(geom), ST_Y(geom), 1) OVER () AS c,
                    geom
               FROM points
         ) AS sub
GROUP BY c

Et voilà, a DBSCAN clustering implementation using PL/R. Not sure if this is the optimal implementation, but it's an implementation and hopefully some useful sample code.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.