3

I am using ST_ClusterDBSCAN to help cluster some building polygons. I am using the following SQL:

CREATE TABLE clusters AS SELECT ogc_fid, cid, wkb_geometry AS geom FROM ( 
    SELECT ogc_fid, ST_ClusterDBSCAN(wkb_geometry, eps := 5, minpoints := 1) over () AS cid, wkb_geometry 
    FROM building) sq; 

When I check the results every feature has a cid even though there are no other features with the same cid, i.e a building is not within 5 metres of another.

Reading the docs, ST_ClusterDBSCAN should give a feature not within a cluster the value of null.

So I am not sure why this is happening.

1 Answer 1

7

The docs for ST_ClusterDBSCAN are incorrect. The docs state that

A "core" geometry is within eps distance of at least minpoints other input geometries

The word "other" is incorrect here; each geometry is considered to be within eps distance of itself.

A correct definition is found on Wikipedia:

A point p is a core point if at least minPts points are within distance ε(ε is the maximum radius of the neighborhood from p) of it (including p).

If you want to use minpoints := 1 and remove clusters that have only a single geometry, you can add another layer to your query as follows:

CREATE TABLE clusters AS 
SELECT
  ogc_fid, 
  cid, 
  wkb_geometry AS geom
 FROM 
    SELECT
       ogc_fid,
       cid,
       count(1) OVER (PARTITION BY cid) AS num_geoms,
       wkb_geometry 
    FROM ( 
      SELECT
        ogc_fid, 
        ST_ClusterDBSCAN(wkb_geometry, eps := 5, minpoints := 1) over () AS cid, 
        wkb_geometry 
      FROM building
    ) sq
) ssq
WHERE num_geoms > 1;
3
  • Have you informed to update the docs? Nice answer Apr 4, 2017 at 16:57
  • @dbaston thanks. that works brilliantly. It is significantly quicker using those sub queries. Also been using your blog post, so thanks for that too
    – tjmgis
    Apr 4, 2017 at 17:54
  • @JohnBarça, I committed a fix to the docs, but it takes a while to show up on the site.
    – dbaston
    Apr 4, 2017 at 18:21

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.