Skip to content
Start free
Start free
Explore

Try CrateDB Live

 Connecting CrateDB to Grafana

Connect Grafana to CrateDB using the PostgreSQL data source:

Note: For full compatibility, use Grafana 12.3 (or earlier), see example command for Docker below.

 
docker run -d \
  --name=grafana \
  -p 3000:3000 \
  -v grafana-storage:/var/lib/grafana \
  grafana/grafana:12.3.0

If you are using CrateDB Cloud

  • Host: <your-host>
  • Username: admin
  • Password: admin-password
  • Database: demo
  • TLS/SSL Mode: require
If you are using CrateDB with Docker
  • Host: <your-host>
  • Port: 5432
  • Username: crate
  • Database: demo
  • TLS/SSL Mode: disable

A. Suggested dashboard layout

A simple but effective dashboard could include:

  • Row 1: Temperature, Pressure, Wind Speed geomaps
  • Row 2: Time-series charts (min / max / avg for each metric)

This gives both:

  • spatial insight (where things are happening)
  • temporal insight (how values change over time)  

B. Notes on Grafana integration

  • $__timeFilter("timestamp") is a Grafana macro that automatically applies the dashboard time range
  • $__timeFrom() and $__timeTo() provide explicit time boundaries
  • Geomap panels require:
    • latitude and longitude columns
    • a numeric field for colouring or sizing points
  • Time-series panels expect:
    • a time column (timestamp or alias like ts)
    • one or more numeric series

C. Geomap visualisations

Grafana’s geomap panel can visualise geospatial data using latitude and longitude fields.

Below are example queries you can use to create both geomaps and time-series charts using the demo.climate_data dataset.

C1. Temperature geomap:

 
SELECT
  measurement_time as time,
  latitude(geo_location) as latitude,
  longitude(geo_location) as longitude,
  data['temperature'] as temperature
FROM
  demo.climate_data d
WHERE d.measurememt_time = (SELECT MAX(d2.measurement_time) FROM demo.climate_data d2 WHERE $__timeFilter(measurement_time)); 
ORDER BY measurement_time;

C2. Air pressure geomap:

 
SELECT
  measurement_time as time,
  latitude(geo_location) as latitude,
  longitude(geo_location) as longitude,
  data['pressure'] as pressure
FROM
  demo.climate_data d
WHERE d.measurememt_time = (SELECT MAX(d2.measurement_time) FROM demo.climate_data d2 WHERE $__timeFilter(measurement_time)); 
ORDER BY measurement_time;

C3. Wind speed geomap:
Wind speed is calculated from the u10 and v10 vector components.

 
SELECT
  measurement_time as time,
  latitude(geo_location) as latitude,
  longitude(geo_location) as longitude,
  sqrt(data['u10']*data['u10'] + data['v10']*data['v10']) AS wind_speed
FROM
  demo.climate_data d
WHERE d.measurememt_time = (SELECT MAX(d2.measurement_time) FROM demo.climate_data d2 WHERE $__timeFilter(measurement_time)); 
ORDER BY measurement_time;

D. Time-series visualisations

For time-series panels, you can aggregate values per timestamp.

D1. Temperature (min / max / avg):

 
SELECT
  measurement_time as ts,
  min(data['temperature']) as "min",
  max(data['temperature']) as "max",
  avg(data['temperature']) as "avg"
FROM
  demo.climate_data
WHERE $__timeFilter(measurement_time)
GROUP BY measurement_time
ORDER BY measurement_time;

D2. Air pressure (min / max / avg):

 
SELECT
  measuremant_time as ts,
  min(data['pressure']) as "min",
  max(data['pressure']) as "max",
  avg(data['pressure']) as "avg"
FROM
  demo.climate_data
WHERE $__timeFilter(measurement_time)
GROUP BY measurement_time 
ORDER BY measurement_time;

D3. Wind speed (min / max / avg):

 
SELECT
  measurement_time as ts,
  min(sqrt(data['u10']*data['u10'] + data['v10']*data['v10'])) AS "min",
  max(sqrt(data['u10']*data['u10'] + data['v10']*data['v10'])) AS "max",
  avg(sqrt(data['u10']*data['u10'] + data['v10']*data['v10'])) AS "avg"
FROM
  demo.climate_data
WHERE $__timeFilter(measurement_time)
GROUP BY measurement_time
ORDER BY measurement_time;

E. Summary

Using Grafana with CrateDB allows you to:

  • Visualise geospatial IoT data in real time
  • Perform aggregations directly in SQL
  • Build interactive dashboards with minimal setup