ADS-B Grafana: Make lists of types, calsigns and more
The other day I ran into a limitation of Influxdb with Grafana. I have an Influxdb that I feed with data from my ADS-B receiver. This feed contains data like
Reg: PH-AOA
Type: A332
What I wanted to do is count the number of UNIQUE Airbus A330-203’s.
The end goal
TEN hits on PH-AOE A332 must be counted as ONE A320-203 and NOT ten!
To do that you need to create a query with “GROUP BY”. Easy enough. Now here comes the problem: You cannot do a “GROUP BY” in Influxdb that connect two values. You can only do that with a TAG.
Influxdb query – group by tag: actype
The query looks like this:
SELECT count(distinct(“Reg”)) FROM “mesg” WHERE $timeFilter GROUP BY time(1d), “actype”
We need a TAG to group these entries
This tag “actype” does not exist in the datafeed so it must be created. I already use node red to collect and organise the feed so it makes sense to to it there.
Node-Red to the rescue
The ADS-B receiver on the Pi produces the above JSON-feed that after a bit of tinkering looks like this:
How to add a TAG-value for Influxdb with a Node-Red function node
msg.payload=[msg.payload,{tag:value}];
Example
To add two tags:
- Add this tag “icao24” from value “msg.payload.Icao”
- Add this tag “actype” from value “msg.payload.Mdl”
Do this:
msg.payload=[msg.payload,{icao24:msg.payload.Icao},{actype:msg.payload.Mdl}];
return msg
You can now do this query in Grafana:
SELECT count(distinct(“Reg”)) FROM “mesg” WHERE $timeFilter GROUP BY time(1d), “actype”
The result is an aggregated list of aircraft types from unique registrations over a period of 1 day.