Hi All! Got a bit of a tricky one today.
I use the readsb docker container to collect data about aircraft flying nearby. The container has an instance of telegraf running inside it, which sends data to influxdb with the following schema (full schema can be seen here):
_measurement: aircraft Tag: Icao Tag: Call ... Tag: _field Field Key: Altitude Field Key: Latitude Field Key: Longitude Field Key: Airspeed ....
I then run this query:
from(bucket: "hassincontainer") |> range(start: -24h, stop:now()) |> filter(fn: (r) => r._measurement == "aircraft" and r.Icao != "") |> keep(columns: ["_stop","Icao"]) |> group(columns: ["_time"], mode: "by")
which gives me tens of thousands of rows in the result, showing the ICAO codes of aircraft seen in the past 24 hours. Note that a unique ICAO code appears hundreds of times in the results, as each aircraft sends hundreds of packets at a time with its position etc.
You get the idea.
Now to my question.
I want to filter out the rows where the same ICAO code appears several times within seconds, keeping only one row.
BUT if an ICAO code re-appears after an hour, I want to keep the row, not filter it out. (This is why the
unique() command won’t work for me). This is a scenario where an aircraft with the same ICAO code is now flying over head again, possibly on another flight.
I did think about using the callsign instead of / in combination with the ICAO code. The issue with this is that the same callsign can be used by the aircraft on the next day, for example. So can’t use the
unique() function there either.
Any ideas? Thanks!