5

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.

Improve this question
2
  • 2
    You want to use ST_ClusterDBSCAN - see e.g. my answer here. Commented Aug 16, 2021 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. Commented Aug 16, 2021 at 15:33

1 Answer 1

Reset to default
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 |
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.