PostGIS clustering with statistics

Question

I'm discovering PostGIS for myself and right now I'm stuck at the point with clusters. Found some tips about how to make clusters on point layer in PostGIS by using ST_ClusterWithin function. Right now I can display a clean cluster centroid data with no any other parameters.

This is my script:

SELECT 
  ST_NumGeometries(cwn) as num_geoms,
  ST_Centroid(cwn) AS centroid
FROM (
    SELECT unnest(ST_ClusterWithin("GEOM", 0.1)) cwn
    FROM geodata
) f

My another goal is to display some stats like quantity of specific values (0 and 1) in result table.

This is scematic picture of what I need. When cluster and its centroid are created, I'd like to see how many zeros or ones were taken from original points.

So the result should be like this

num_geoms | centroid        | zeros | ones |
----------+-----------------+-------+-------
        4 | geometry_object | 1     | 3    |     
        4 | geometry_object | 3     | 1    |     
        4 | geometry_object | 2     | 2    |   

I'm pretty sure that this can be made, but still haven't got an idea of how to make a correct query.

Answer 1

With a piece of advice by @geozelot I have found a solution. There are two fields that take part in query:

• "GEOM" - basic geometry field with point data
• "SOLVED" - int number field that contain only 0 or 1 values.

My goal was to get clusters with internal geometry number and sums of zeros and ones to make them look like that:

It means that cluster has 8 points within, three of them (green sector) have value 1 in field "SOLVED" and the rest points have 0 (red sector).

Final script looks like:

WITH stat AS (
SELECT
    "SOLVED",
    "GEOM",
    ST_ClusterDBSCAN("GEOM", eps := 0.1, minpoints := 1) over () AS cluster_id  
FROM
    geodata
)

SELECT 
   stat.cluster_id, 
   ST_NumGeometries(unnest(ST_ClusterWithin(stat."GEOM", 0.1))) as num_geoms,
   ST_Centroid(ST_MinimumBoundingCircle(unnest(ST_ClusterWithin(stat."GEOM", 0.1)))) AS centroid,
   SUM(CASE when stat."SOLVED"=0 then 1 else 0 end ) zeros,
   SUM(CASE when stat."SOLVED"> 0 then stat."SOLVED" else 0 end ) ones
   FROM stat
GROUP BY stat.cluster_id

The result will look like:

cluster_id | num_geoms | centroid        | zeros | ones |
-----------+-----------+-----------------+-------+------+
 0         | 8         | geometry_object | 4     | 4    |
 1         | 1         | geometry_object | 0     | 1    |
 2         | 1         | geometry_object | 1     | 0    |
 3         | 1         | geometry_object | 0     | 1    |