Combining points into polygons with range of values using ST_ClusterDBSCAN and ST_ConvexHull

Question

I tried to create polygons with similar depths based on values from point data.

I got some help to start with in this question Combining points into polygons with range of values using PostGIS

Since the data points is in wgs84 (4326) I set the max_distance to 0.003 in ST_ClusterDBSCAN(geom, 0.003 , 3). The problem is that I get many polygons that overlap each other, first I thought that this was a problem that could be solved by iterating over them and delete the overlap with ST_Difference. So I wrote a Python script and did this, but then I realized that it was both polygons with high and low depth values that overlapped.

Any idea how I can improve the generation of polygons from the data points or obtain a better result with the polygons that I got?

Answer 1

Those Polygons result from ST_ClusterDBSCAN assigning (and ST_ConcaveHull polygonizing) NULL values for rows where it couldn't find a suitable cluster; in that linked answer I suggested to use min_points >= 3 to feed 3-Point-cluster collections to ST_ConcaveHull - but I failed to explain the consequences.

With min_points >= 3 you will effectively dismiss any possible cluster with less than 3 Points from your result, while keeping the initial Points with NULL assigned (resulting in those large overlapping Polygons); while you cannot create Polygons from less than 3 Points with ST_ConcaveHull (it will return Point or LineString geometries instead), it may yet be interesting to see their spatial correlation.

You have two options:

• since any row without suitable cluster affiliation will get NULL assigned, just don't feed those to ST_Collect:

  SELECT range_bin*<range> AS range_min,                  -- minimum bounds of  range
         ST_ConvexHull(ST_Collect(geom)) AS geom
  FROM   (
    SELECT FLOOR(height::FLOAT/<range>::FLOAT) AS range_bin,
           ST_ClusterDBSCAN(point, 0.003, 3) OVER(PARTITION BY FLOOR(height::FLOAT/<range>::FLOAT)) AS clst
           point AS geom
    FROM   <your_table>
  ) q
  WHERE  clst IS NOT NULL
  GROUP BY
         range_bin, clst
  ;
  

  This will only polygonize (and add to the result set) clusters with 3 or more affiliated Points; those without cluster assignment will get lost.
• let ST_ClsuterDBSCAN find clusters with min_point = 1 (which will assign clst ids also to single Point clusters), and filter the resulting geometries by type (since most client software will not be able to work with multi-type sets):

  SELECT range_bin*<range> AS range_min,                  -- minimum bounds of  range
         ST_ConvexHull(ST_Collect(geom)) AS geom
  FROM   (
    SELECT FLOOR(height::FLOAT/<range>::FLOAT) AS range_bin,
           ST_ClusterDBSCAN(point, 0.003, 1) OVER(PARTITION BY FLOOR(height::FLOAT/<range>::FLOAT)) AS clst
           point AS geom
    FROM   <your_table>
  ) q
  GROUP BY
         range_bin, clst
  HAVING COUNT(geom) > 2 -- for Polygons; > 1 to include LineStrings; remove this (or > 0) to include Points
  ;
  

  or, to handle the edge cases where 3 Points result in a LineString or some Points have equal locations:

  SELECT *
  FROM   (
    SELECT range_bin*<range> AS range_min,                  -- minimum bounds of  range
           ST_ConvexHull(ST_Collect(geom)) AS geom
    FROM   (
      SELECT FLOOR(height::FLOAT/<range>::FLOAT) AS range_bin,
             ST_ClusterDBSCAN(point, 0.003, 1) OVER(PARTITION BY FLOOR(height::FLOAT/<range>::FLOAT)) AS clst
             point AS geom
      FROM   <your_table>
    ) q
    GROUP BY
           range_bin, clst
  ) q
  WHERE GeometryType(geom) = 'POLYGON'  -- 'POINT'/'LINESTRING'
  ;