Data for World Map Panel Plugin in Grafana:
Approach 1:
Using the InfluxDB data:
- Here, the data is pulled from the database(InfluxDB) from a single measurement(table).
Schema of the hdb7 table is:
Time |
DS_ID |
from |
graphStatement |
latitude |
longitude |
to |
totalPower |
value |
![Screenshot from 2021-01-07 13-34-28](https://media2.dev.to/cdn-cgi/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2Fgr9jj8p1i1u2rrpkw8gt.png)
![a1.png](https://res.cloudinary.com/practicaldev/image/fetch/s--BP_-UYWg--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_800/https://res.cloudinary.com/dskzmamid/image/upload/v1610006956/a1.png)
![a2.png](https://res.cloudinary.com/practicaldev/image/fetch/s--CQROmHub--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_800/https://res.cloudinary.com/dskzmamid/image/upload/v1610006954/a2.png)
The above is the data that is pushed to the measurement(hdb7) of the InfluxDB database. Here, the hdb7 table’s graphStatement field values will be shown on the world map.
Query:
SELECT * from "hdb7" GROUP BY * ORDER BY DESC LIMIT 4
col1 |
col2 |
Location Data |
table |
Aggregation |
current |
Map Data Options
Field Mapping
col1 |
col2 |
Table Query Format |
coordinates |
Location Name Field |
graphStatement |
Metric Field |
value |
Latitude Field |
latitude |
Longitude Field |
longitude |
![Screenshot from 2021-01-05 16-43-23p1d1](https://media2.dev.to/cdn-cgi/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2Fbyqhoevj4c9j6m0xn3tj.png)
![Screenshot from 2021-01-05 16-44-00p1d2](https://media2.dev.to/cdn-cgi/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2Fraq6rumu907qwas7fqav.png)
Field Mapping
![Screenshot from 2021-01-05 16-44-09p1d3](https://media2.dev.to/cdn-cgi/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2Fbvrwz3ali445j0zzny12.png)
DrawBack:
1.
The requirement specifically mentions having 2 tables like shown below.
JOIN statements are not supported in InfluxDB.
Table1
DS_ID, Lat, Long
Table2
DS_ID, Sum(pump),sum(light),..total value
Table1:
DS_ID |
latitude |
longitude |
value |
time |
Table2:
DS_ID |
graphStatement |
totalPower |
value |
time |
The graphStatement covers the fields sum(boosterPump), sum(lighting), sum(lift) which are to be displayed on the map.
“JOIN is no longer a concept in 0.9. Series sharing a measurement can be queried together simply by omitting the differentiating tags from the WHERE clause.”
Link:
https://github.com/influxdata/influxdb/issues/624
However, there is one alternative called transformations in Grafana that can be used to join 2 tables of same datasource/mixed datasource as shown below.
Here, the data for Table1 (DS_ID, Lat, Long) will be queried from MySQL database. The data for Table2 (DS_ID, Sum(pump),sum(light),..total value) will be queried from InfluxDB database.
Implementation:
-
We can implement the above by choosing the datasource as Mixed.
The query 1 will be:
SELECT * from hdb10
The query 2 will be:
SELECT * from hdb9
The transform we are going to apply is :
Outer join: DS_ID
All the pictures are shown below:
![Screenshot from 2021-01-08 15-07-09p2d1](https://media2.dev.to/cdn-cgi/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2F8b5h21f0fsb7t2a0lozt.png)
![Screenshot from 2021-01-08 15-07-28p2d2](https://media2.dev.to/cdn-cgi/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2F4loxdrpw5tezt2ulklv6.png)
When we change the metric field to Total(field from hdb10) we get the value on the map as shown below:
![Screenshot from 2021-01-08 15-15-02p2d3](https://media2.dev.to/cdn-cgi/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2Fgznqj96ntgiy2h6qm95t.png)
Approach 2:
Using the MYSQL data:
- Here, the data is pulled from the database(MySQL) from a 2 tables.
// hidden setup JavaScript code goes in this preamble area
const hiddenVar = 42
k@k-Lenovo-G50-70:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| database_name |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use database_name
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------------+
| Tables_in_database_name |
+-------------------------+
| a_one |
| total_power |
| worldmap_latlng |
| worldmap_latlng_a |
+-------------------------+
4 rows in set (0.00 sec)
Table creation:
mysql> INSERT INTO worldmap_latlng_a
-> (lat,
-> lng,
-> DS_ID,
-> value,
-> timestamp)
-> VALUES
-> (1.3521,
-> 103.8198,
-> '0',
-> 1.0,
-> now());
Query OK, 1 row affected (0.13 sec)
mysql> CREATE TABLE worldmap_latlng_a (
-> id int(11) NOT NULL AUTO_INCREMENT,
-> lat FLOAT NOT NULL,
-> lng FLOAT NOT NULL,
-> DS_ID VARCHAR(20) NOT NULL,
-> value FLOAT NOT NULL,
-> timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (id)
-> ) AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.65 sec)
mysql> describe total_power;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| Lift | float | NO | | NULL | |
| Lighting | float | NO | | NULL | |
| Total | float | NO | | NULL | |
| BoosterPump | float | NO | | NULL | |
| DS_ID | varchar(20) | NO | | NULL | |
| value | float | NO | | NULL | |
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
mysql> describe worldmap_latlng_a;
+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| lat | float | NO | | NULL | |
| lng | float | NO | | NULL | |
| DS_ID | varchar(20) | NO | | NULL | |
| value | float | NO | | NULL | |
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------+-------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)
Table1: worldmap_latlng_a
DS_ID
lat
lng
value
timestamp
Table2: total_power
DS_ID
Lift
Lighting
BoosterPump
Total
value
timestamp
INSERTING Records:
mysql> INSERT INTO worldmap_latlng_a
-> (lat,
-> lng,
-> DS_ID,
-> value,
-> timestamp)
-> VALUES
-> (1.3521,
-> 103.8198,
-> '0',
-> 1.0,
-> now());
Query OK, 1 row affected (0.13 sec)
mysql> INSERT INTO total_power
-> (Lift,
-> Lighting,
-> Total,
-> BoosterPump,
-> DS_ID,
-> value,
-> timestamp)
-> VALUES
-> ( 10474.1997022,
-> 8.97861111111,
-> 14987.6236142,
-> 4504.44530083,
-> '0',
-> 1.0,
-> now());
Query OK, 1 row affected (0.11 sec)
DB Connection in Grafana(Add Data sources MySQL):
Host localhost:3306
Database database_name
![Screenshot from 2021-01-05 17-35-47](https://media2.dev.to/cdn-cgi/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2Fy3v3zwnfrd5mp693xx5c.png)
SELECT CONCAT( "-") AS Conca, worldmap_latlng_a.lat, worldmap_latlng_a.lng, worldmap_latlng_a.DS_ID, total_power.Total
FROM total_power
INNER JOIN worldmap_latlng_a
ON worldmap_latlng_a.DS_ID = total_power.DS_I
![Screenshot from 2021-01-05 17-09-46p1d4](https://media2.dev.to/cdn-cgi/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2Fgwufg47ntytsla1ty47k.png)
![Screenshot from 2021-01-05 17-11-27p1d5](https://media2.dev.to/cdn-cgi/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2F99jqlbnldnmy3g1f4o06.png)
The data we get is:
Conca lat lng DS_ID Total
Here, we are getting both the data of the table1(worldmap_latlng_a) and the table2(total_power) from the join query executed above.
![Screenshot from 2021-01-05 17-11-54p1d6](https://media2.dev.to/cdn-cgi/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2Fm3svffq75puzc2wh2g9d.png)
Top comments (0)