Geo heatmap (SQL)

A
Geo heatmap
panel plots coordinates as a density map — the more events near a spot, the hotter it glows. Use it for anything location-bound: where sales happen, where complaints come from, where support visits cluster.

Query convention

SELECT JSONExtractFloat(metadata, 'lat') AS lat, JSONExtractFloat(metadata, 'lon') AS lonFROM chomper.eventsWHERE JSONHas(metadata, 'lat') AND JSONHas(metadata, 'lon')  AND timestamp >= parseDateTimeBestEffort({from:String}) AND timestamp <= parseDateTimeBestEffort({to:String})

Two required columns,
lat
/
lon
, plus an optional third
weight
(defaults to 1 per row) to bias the heatmap toward certain rows instead of treating every point equally.
JSONHas
guards against rows that never set that key — without it, a missing coordinate would extract as
0.0
/
0.0
and show up at the equator.

Intensity, Contrast, and Size

Three independent display sliders shape how the heat reads, auto-scaled to your own data rather than an absolute number you'd have to guess:
Intensity
(how much accumulated density counts as fully hot),
Contrast
(how visible a single, entirely isolated point is on its own), and
Size
(a point's own screen radius, and so whether nearby points touch and reinforce each other at all).

Layers: splitting the map by category

Add a third column aliased
layer
, and the map splits into up to three independently-colored, independently-toggleable layers — the geo panel's equivalent of a time series' multi-series
series
column:

SELECT  JSONExtractFloat(metadata, 'lat') AS lat, JSONExtractFloat(metadata, 'lon') AS lon,  JSONExtractString(metadata, 'customer_type') AS layerFROM chomper.eventsWHERE event_class = 'sale' AND JSONHas(metadata, 'lat') AND JSONHas(metadata, 'lon')  AND timestamp >= parseDateTimeBestEffort({from:String}) AND timestamp <= parseDateTimeBestEffort({to:String})

A legend appears on the map (top-left) the moment more than one layer is present, letting you show just one layer or toggle a few together — independent of the
Color by layer
setting below.

Color by layer OFF (default) — both layers blend into one shared green→amber→red heat.

Color by layer ON — Individual (green) and B2B (amber) read as two distinct colors, brightest where they overlap.

Color by layer off
(the default) merges every visible layer's points into one combined heat under the usual green→amber→red gradient — the right choice when the question is simply “where do sales happen,” not “which segment dominates where.”
Color by layer on
gives each layer its own fixed hue instead, so two layers overlapping in the same spot reads as visibly brighter and blended — the right choice once the comparison itself (B2B vs. Individual density) is the point of the map.

Clusters and manual radius select

A heatmap blob has no natural single-pixel click target the way a pie slice does, so a click drills into a
cluster
instead. Two ways to trigger one:

• Cluster points (a “Show cluster points” toggle) — small markers placed automatically on the busiest spots; hover one to preview its exact search radius, click to drill down.
• Manual radius select — click and drag anywhere on the map to draw your own circle and drill down into exactly that area, for “I want to ask about this specific spot,” not just wherever a marker happened to land.

Example: Sales locations (layered)

“Sales locations” — layered by customer type, manual radius select on, no cluster markers.

Turn on manual radius select and leave cluster markers off when the goal is letting people circle a specific area and ask “what sold here,” rather than pre-placing dots everywhere.

Example: Complaint locations (clustered)

SELECT JSONExtractFloat(metadata, 'lat') AS lat, JSONExtractFloat(metadata, 'lon') AS lonFROM chomper.eventsWHERE is_negative = 1 AND JSONHas(metadata, 'lat') AND JSONHas(metadata, 'lon')  AND timestamp >= parseDateTimeBestEffort({from:String}) AND timestamp <= parseDateTimeBestEffort({to:String})

“Complaint locations” — no layer column, cluster markers on.

Drilling down into a cluster

Clicking a cluster (or releasing a manual selection) runs the drill-down query with
{clusterLat:Float64}
/
{clusterLon:Float64}
/
{clusterRadiusKm:Float64}
bound to that spot's center and radius — ClickHouse's own
greatCircleDistance(...)
is the natural way to filter rows within it:

SELECT * FROM chomper.eventsWHERE greatCircleDistance(    JSONExtractFloat(metadata, 'lon'), JSONExtractFloat(metadata, 'lat'),    {clusterLon:Float64}, {clusterLat:Float64}  ) <= {clusterRadiusKm:Float64} * 1000  AND timestamp >= parseDateTimeBestEffort({from:String}) AND timestamp <= parseDateTimeBestEffort({to:String})ORDER BY timestamp DESC LIMIT 100

When exactly one layer is currently visible, the click also binds that layer's name, so a drill-down query can add a plain
WHERE layer = {layer:String}
. With two or three layers visible together, no layer binding is added — a cluster combining several layers' points can't be attributed to just one.