alexandrainst / alexandra-trackmap-panel

Grafana map plugin to visualise coordinates as markers, hexbin, ant path, or heatmap.
MIT License
78 stars 26 forks source link

Marker size based on number of records in cluster #41

Open rudibroekhuizen opened 3 years ago

rudibroekhuizen commented 3 years ago

I am using the trackmap panel in a dashboard to explore a GBIF dataset. Using the following query I can zoom in to streetlevel, and show the number of records in the tooltip for each cluster.

WITH a AS (
SELECT
eventdate AS time,
decimallatitude,
decimallongitude,
geom,
COUNT(*) OVER() AS cnt
FROM explore.gbif
WHERE $__timeFilter(eventdate)
AND decimallatitude >= $minLat
AND decimallatitude <= $maxLat
AND decimallongitude >= $minLon
AND decimallongitude <= $maxLon
), b AS (
SELECT
time,
decimallatitude,
decimallongitude,
ST_ClusterKMeans(geom, LEAST(cnt::integer, 200)) OVER() AS clusters
FROM a
)
SELECT
clusters,
COUNT(clusters) AS tooltip,
AVG(decimallatitude) AS latitude,
AVG(decimallongitude) AS longitude
FROM b
GROUP BY 1
ORDER BY 1;

Is it possible to use the number of records in a cluster to set the size of the marker? Or an option to use circles with sizes depending on the number of records in a cluster, like available in the worldmap panel?

clusters

Maximum zoom using LEAST and COUNT OVER(): maxzoom