Grafana geomap with PostgreSQL

Create Grafana time series visualisations of stored IoT data from Pareto Anywhere.

Grafana geomap visualisation

The TL;DR (Too Long; Didn't Read)

Learn how to create geomap visualisations in Grafana with IoT & RTLS data from Pareto Anywhere.

What's a geomap?
Geomap visualisations display geospatial data on a map, can overlay map layers, and can auto-refresh to show movement.
What's Pareto Anywhere?
Pareto Anywhere is open source IoT middleware that makes the data from just about anything usable.
Why PostgreSQL?
PostgreSQL, the world's most advanced open source relational database, facilitates time series and geospatial queries of the IoT and RTLS data from Pareto Anywhere.

Prerequisites

A PostgreSQL database and Grafana installed.

Creating the panel   Step 1 of 2

Create a new geomap visualisation with PostgreSQL query.

What's a panel?
The panel is a visual representation of data composed of a PostgreSQL database query and its visualisation.
Duplicate instead?
If an existing panel is available, it may be easier to duplicate and edit, rather than create from scratch.
PostgreSQL query visualisation

Add new geomap visualisation Part 1

Grafana dashboard add visualization

In Grafana, open an existing dashboard, or create a new dashboard.

From the top menu of the dashboard, click Add and select Visualization.

An Edit panel screen will appear, similar to that shown below.

Select time series visualisation

From the Visualization pull down menu at right, select Geomap.

Enter query Part 2

Grafana offers the possibility to prepare a query via a Builder tool or a Code entry option. In the Queries window, select the Code entry option.

Grafana visualization query

In the code window, enter a PostgreSQL query based on one of the following examples:

All real-time locations over the last minute. 

SELECT
  ST_X(geom) as longitude,
  ST_Y(geom) as latitude,
  devicesignature,
  timestamp
FROM spatem
WHERE timestamp >= NOW() - interval '1 minute'
ORDER BY timestamp DESC;

Location and timestamp of each device's most recent button press. 

SELECT DISTINCT ON (dynamb.devicesignature)
  dynamb.devicesignature,
  translate(dynamb->>'isButtonPressed', '[]', '{}')::boolean[] as isButtonPressed,
  ST_X(spatem.geom) as longitude,
  ST_Y(spatem.geom) as latitude,
  dynamb.timestamp
FROM dynamb JOIN spatem ON dynamb.devicesignature = spatem.devicesignature
WHERE true = ANY(translate(dynamb->>'isButtonPressed', '[]', '{}')::boolean[])
ORDER BY devicesignature, timestamp DESC;

Click the Run query button to validate that the query is successful. Correct any errors and repeat as necessary.

The query should now be visualised correctly. It is now possible to customise the panel, which is covered in Step 2 below.

Customising the panel   Step 2 of 2

Customise the visualisation and the panel.

What are the options?
Grafana offers extensive panel configuration options, including the title, style, units and colour palette.
More details?
Consult Grafana's Geomap configuration options documentation for more details.
Grafana panel customisation

Edit panel options Part 1

Details to come.

Contributor
reelyActive half-barnowl

Tutorial prepared with ♥ by jeffyactive.

You can reelyActive's open source efforts directly by contributing code & docs, collectively by sharing across your network, and commercially through our packages.

Where to next?

Continue exploring our open architecture and all its applications.