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

RETURNS ? AS $$ ? $$ LANGUAGE plr;

SELECT r_cluster(?) OVER (?) FROM points;

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)
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)


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,
               FROM points
         ) AS sub

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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