Creating visualizations

This page explains how you create a new visualization.

1. Open your dashboard and select add -> new visualization

2. 
3. In the datasource opption select your postgreSQL database

4. 
5. Now you can query the data you want from your database.

Queries

When creating a query for your data you can either use the visual tools (builder) or enter a SQL query manually. This guide will explain how you use the query builder to get data from the database.

Manual queries can be entered here

The visual SQL query builder

Sensors with one value

1. Select the builder on the right hand side of the query box

2. 
3. Select your table and what column you want to get data from. This will automatically create a SQL query in the preview window for you.

4. 
5. To get data over time you also need to get the time the data was collected from the node. To get this you need to add another column by pressing the + button.

6. 
7. To filter the data so you only get data from one box or one sensor you can use the filter option. Select the filter button on the top of the query box and press the + button under filter by column value

8. 
9. To only select values from one sensor add column sensor_type = sensorNumber To only get the values from one node you should also add column sensorbox_id = sensorboxID

10. 
11. To add another box in the same visualization you can copy the query and change the sensor boxID

12. 
13. 
14. You will notice that both your values have the same name in the visualization. To change this you need to add an SQL alias to the query for the value. This can be done by changing to the code section and adding AS "Value box1" after the SELECT value code

Sensors with multiple values

1. For sensors with multiple values its recommended to create one visualization per sensor and add all the different columns belonging to the sensor in one query

Query for the spectrometer with 10 values

Two visualizations one for each box

All queries required to get data from the nodes

• BH1750:

  SELECT value AS "LUX box #", created_at FROM "TABLE NAME" WHERE (sensor_type = 23 AND sensorbox_id = 'sensorboxID') LIMIT 50 

• AS7341:

  SELECT nm405_425, nm435_455, nm470_490, nm505_525, nm545_565, nm580_600, nm620_640, nm670_690, clear, nir, created_at FROM "TABLE_NAME" WHERE (sensor_type = 39 AND sensorbox_id = 'sensorboxID') LIMIT 50 

• SCD30 sensor:

  • Temperature

    SELECT temperature AS "Temperature air box #", created_at FROM "TABLE NAME" WHERE (sensor_type = 61 AND sensorbox_id = 'sensorboxID') LIMIT 50 

  • Humidity

    SELECT humidity AS "Humidity box #", created_at FROM "TABLE NAME" WHERE (sensor_type = 61 AND sensorbox_id = 'sensorboxID') LIMIT 50 

  • CO2

    SELECT co2 AS "CO2 box #", created_at FROM "TABLE NAME" WHERE (sensor_type = 61 AND sensorbox_id = 'sensorboxID') LIMIT 50 

• SEN0193 sensor:

  SELECT value AS "Earth humidity box #", created_at FROM "TABLE NAME" WHERE (sensor_type = 48 AND sensorbox_id = 'sensorboxID') LIMIT 50 

• Thermocouple / MCP9600:

  SELECT value AS "Earth temperature box #", created_at FROM "TABLE NAME" WHERE (sensor_type = 60 AND sensorbox_id = 'sensorboxID') LIMIT 50 

• Battery voltage:

  SELECT battery FROM "TABLE NAME" WHERE (sensorbox_id = 'sensorboxID' AND sensorbox_connector = 9) LIMIT 50 

Customization

There are multiple options for customizing your data you should check out grafanas own documentation for a more in-depth explanation of this.

Picture of mockup dashboard

A finished dashboard with some test data can look something like this.