Clustering geometry points, return a point's label if the point does not belong to a cluster?

Question

I am clustering geometry points using the k-mean algorithm. The result is a list of clusters. Some clusters contain multiple points. Others only contain a single point. On the client side, we would like to display markers on a map. Clusters containing multiple points will be displayed as a marker containing an integer equivalent to the number of points in that cluster. Clusters containing a single point will be displayed as a marker containing the point's name.

With SQL and postGIS functions, I would like to fetch clusters and the name of any point that is the only point in a cluster. I am nearly there. As shown in the attached image, the following SQL query returns a list of clusters, their kmean index, the number of points in the cluster (called the count) and their centroid point. This is all great, but for all clusters that contain only a single point, I'd also like to include the name of the point.

SELECT kmean, count(*), ST_Centroid(ST_Collect(location_point)) AS centroid, 
       CASE 
           WHEN count(*) > 1 THEN 'cluster'
           ELSE 'voter_name should go here'
       END "member"
FROM (
    SELECT st_clusterkmeans(location_point, 3) OVER () as kmean, 
           voter_name, location_point 
    FROM voter_locations
     ) 
     as ksub    
group by kmean
order by kmean

This is the voter_locations table where the geometry points are stored.

If, in the query, I replace the text 'voter_name should go here' with the voter_name variable, then I get an error

ERROR:  column "ksub.voter_name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:            ELSE voter_name

However, if I do add the voter_name variable to the group by clause, then the response includes one row for every record, like in the following photo... This isn't good because we want one row for every cluster...

How can I tweak my query to achieve the desired results?

Answer 1

You get an entry for each voter because you are grouping on the voter column as well, which results in groups that are all single rows. And you cannot include voter_name in the query unless you either aggregate on the column or build groups that include the column, which means that you have to add one more query level to achieve your objective.

Here are a couple of solutions:

1)Use a join. In this scenario, you run the main query, grouping only on kmean and then join the voter values, then reuse the CASE clause, and finally keep only the distinct rows

WITH ksub AS (
  SELECT 
    st_clusterkmeans(location_point, 3) OVER () as kmean, 
    voter_name, 
    location_point 
  FROM voter_locations
)
SELECT
  DISTINCT
  k1.*,
  CASE WHEN count > 1 THEN 'cluster' ELSE ksub.voter_name END AS member
FROM (
  SELECT 
    kmean, 
    count(*), 
    ST_Centroid(ST_Collect(location_point)) AS cluster
  FROM ksub
  group by kmean
  order by kmean
) k1 left join ksub USING (kmean)

2)Use arrays. In this scenario, you aggregate the voter_names in an array, which you extract in the second query if there is only a single voter_name. As a bonus, it also gives you an array of the voter names. If that is not useful, replace main_query.* with the appropriate columns that you want in the final result.

SELECT
  main_query.*,
  CASE WHEN count > 1 THEN 'cluster' ELSE voters[1] END as member
FROM (
  SELECT 
    kmean, 
    count(*), 
    ST_Centroid(ST_Collect(location_point)) AS centroid,
    array_agg(voter_name) as voters
  FROM (
    SELECT 
      st_clusterkmeans(location_point, 3) OVER () as kmean, 
      voter_name, 
      location_point 
    FROM 
      voter_locations
  ) as ksub
  group by kmean
  order by kmean
) as main_query