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?