Hybrid “table+json(p)” Location Data
About
The “Location Data” options “table+json” and “table+jsonp” unlock the ability to use a metric source formatted as table as the primary source for location data information while looking up the corresponding label from a JSON document, extending what the plain “table” option already does by combining existing processing/transformation paths in a convenient manner.
TLDR;
If you prefer to read code, the corresponding software test for this feature might be good place to start.
/*
* Hybrid "table+json(p)" Location Data
*
* We are testing the "table+json" and "table+jsonp" location data
* sources here. They are used when basic location information is
* coming from table data but humanized labels are resolved from a
* JSON/JSONP data source by appropriate field name mapping.
*/
Details
This section gives a detailed overview over appropriate configuration of 1. the data source metric, 2. the JSON document for resolving corresponding humanized labels and 3. the respective Grafana Worldmap Panel settings to successfully map all fields and resources.
Configuration of data source metric, JSON document and Grafana Worldmap settings
Primary data source: Metric as Table
The primary location data source is determined through the metric we are querying from. Specifically, this is an InfluxDB data source which is formatted as “Table”. The response yields the fields/tags “station_id”, “geohash” and “metric”.
Request
SELECT last("P1") AS "metric"
FROM "ldi_readings"
WHERE ("station_id" =~ /^$ldi_station_id$/) AND $timeFilter
GROUP BY "geohash", "station_id"
Response
Data source response
{
"results": [
{
"series": [
{
"columns": [
"time",
"metric"
],
"name": "ldi_readings",
"tags": {
"geohash": "u0wt1tdd0x1d",
"station_id": "19"
},
"values": [
[
1555738566000,
5999.12
]
]
},
{
"columns": [
"time",
"metric"
],
"name": "ldi_readings",
"tags": {
"geohash": "u0wt6pv2qqhz",
"station_id": "28"
},
"values": [
[
1555738438000,
9.13
]
]
}
],
"statement_id": 0
}
]
}
Lookup data source: JSON document
The effective label is resolved by looking it up from a corresponding JSON document acquired and processed exactly like the “json(p) endpoint” options
which looks like
[
{
"key": "19",
"name": "Alte Dorfstra\u00dfe, Birkach, Stuttgart, Baden-W\u00fcrttemberg, DE"
},
{
"key": "28",
"name": "Ulmer Stra\u00dfe, Wangen, Stuttgart, Baden-W\u00fcrttemberg, DE"
},
]
The value from the table field designated by “Location Name Field” (here “station_id”, see response above)
will be used to run a lookup against the “key” field of the JSON document as depicted above. If there’s a match, the value associated with the corresponding name will be returned.
Example: LDI map Germany
We are using this to display synthesized address information on a map populated with data coming from luftdatenpumpe through InfluxDB and PostGIS data sources, see also LDI data plane v2.
– Source: luftdaten.info Karte Deutschland
Read more...
Introduction
In one of our data planes, we are feeding data from the luftdaten.info project into an InfluxDB database using luftdatenpumpe.
Details
As the data from InfluxDB is designated by "FORMAT AS Table" into the Worldmap Panel, we couldn’t find a way to acquire the Location Name from a JSON/JSONP data source as it is possible with the "FORMAT AS Time series" variant already.
This PR accounts for that. When choosing to load location data using the new mapping options "table+json" or "table+jsonp", it is possible to load location information from JSON. The value of the data field designated by the "Location Name Field" will be used as a key for looking up the appropriate record in the JSON dataset. If the lookup fails, the specific code path is backward compatible and should produce the same results as before by using the value of the "Location Name Field" itself.
Outcome
By applying that to our installation, we have been able to display a long location name acquired from ldi-stations.json on the map overlay of LDI stations map without having to store the name inside the InfluxDB measurement table itself. We don’t even want to keep a short name there, the lookup is solely done by using a single location identifier, the numeric station_id:
