4

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.

enter image description here

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.

2
  • 2
    You want to use ST_ClusterDBSCAN - see e.g. my answer here.
    – geozelot
    Aug 16 at 7:23
  • @geozelot thanks for this fucntion, just made a script that creates a desired output. You may take a look at my answer and tell if that can be optimized. Aug 16 at 15:33
3

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:

enter image description here

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    |

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.