Hi
I didn’t come up with any solution how to calculate the distance between two consecutive GPS coordinates saved in a measurement and display the sum of these distances in a Grafana Dashboard.
Is this possible with Grafana + Influx?
If yes, are tasks the way to go?
Thanks, Thomas
Yes, it’s possible, but you will have to do some complex math in Flux. If you know the latitude and longitude for each GPS coordinate (so lat1, lat2, lon1, lon2), you’d have to do the following:
Haversine formula: a = sin²(Δφ/2) + cos φ1 ⋅ cos φ2 ⋅ sin²(Δλ/2)
c = 2 ⋅ atan2( √a, √(1−a) )
d = R ⋅ c
where φ is latitude, λ is longitude, R is earth’s radius (mean radius = 6,371km);
note that angles need to be in radians to pass to trig functions
In JavaScript (which admittedly does not help here, but I find it easy to follow):
const R = 6371e3; // metres
const φ1 = lat1 * Math.PI/180; // φ, λ in radians
const φ2 = lat2 * Math.PI/180;
const Δφ = (lat2-lat1) * Math.PI/180;
const Δλ = (lon2-lon1) * Math.PI/180;
const a = Math.sin(Δφ/2) * Math.sin(Δφ/2) +
Math.cos(φ1) * Math.cos(φ2) *
Math.sin(Δλ/2) * Math.sin(Δλ/2);
const c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
const d = R * c; // in metres
Maybe check out this helpful video: Scott Anderson [InfluxData] | Map & Reduce – The Powerhouses of Custom Flux Functions - YouTube by the forum’s own personal Obi-Wan Kenobi (@scott )
and this:
// helper function to convert degrees to radians
degreesToRadians = (tables=<-) =>
tables
|> map(fn: (r) => ({ r with _value: r._value * math.pi / 180.0 }))
And of course, after I compile all of the above, I find somebody that already did it. More here: Grafana
Actually, there’s a much simpler way to do this. You can use the geo package and geo.ST_Distance() to calculate the geographic distance between two points. If you combine that with reduce() to build a custom aggregate that returns a total sum of these distances per input table.
For example, let’s say you have to following geotemporal data with latitude and longitude stored as fields:
| _time | id | _field | _value |
|---|---|---|---|
| 2022-01-01T00:00:00Z | ABC1 | lat | 112.1 |
| 2022-01-01T01:00:00Z | ABC1 | lat | 96.3 |
| 2022-01-01T02:00:00Z | ABC1 | lat | 63.1 |
| 2022-01-01T03:00:00Z | ABC1 | lat | 50.6 |
| _time | id | _field | _value |
|---|---|---|---|
| 2022-01-01T00:00:00Z | ABC1 | lon | 42.2 |
| 2022-01-01T01:00:00Z | ABC1 | lon | 50.8 |
| 2022-01-01T02:00:00Z | ABC1 | lon | 62.3 |
| 2022-01-01T03:00:00Z | ABC1 | lon | 74.9 |
| _time | id | _field | _value |
|---|---|---|---|
| 2022-01-01T00:00:00Z | DEF2 | lat | -10.8 |
| 2022-01-01T01:00:00Z | DEF2 | lat | -16.3 |
| 2022-01-01T02:00:00Z | DEF2 | lat | -23.2 |
| 2022-01-01T03:00:00Z | DEF2 | lat | -30.4 |
| _time | id | _field | _value |
|---|---|---|---|
| 2022-01-01T00:00:00Z | DEF2 | lon | -12.2 |
| 2022-01-01T01:00:00Z | DEF2 | lon | -0.8 |
| 2022-01-01T02:00:00Z | DEF2 | lon | 12.3 |
| 2022-01-01T03:00:00Z | DEF2 | lon | 24.9 |
You can use geo.shapeData() to reshape your data to meet the requirements of working with the geo package and also assign an S2 cell ID to each point (also required by the geo package). In this particular case, I’d group the data by id so that it removes the grouping by s2_cell_id returned from geo.shapeData().
Using the sample data above and an S2 cell level:
import "experimental/geo"
data
|> geo.shapeData(latField: "lat", lonField: "lon", level: 10)
|> group(columns: ["id"])
This would output the following:
| _time | id | lat | lon | s2_cell_id |
|---|---|---|---|---|
| 2022-01-01T03:00:00Z | ABC1 | 50.6 | 74.9 | 425be3 |
| 2022-01-01T02:00:00Z | ABC1 | 63.1 | 62.3 | 4385e3 |
| 2022-01-01T01:00:00Z | ABC1 | 96.3 | 50.8 | 5015ed |
| 2022-01-01T00:00:00Z | ABC1 | 112.1 | 42.2 | 513e11 |
| _time | id | lat | lon | s2_cell_id |
|---|---|---|---|---|
| 2022-01-01T00:00:00Z | DEF2 | -10.8 | -12.2 | 045367 |
| 2022-01-01T01:00:00Z | DEF2 | -16.3 | -0.8 | 04ca6f |
| 2022-01-01T02:00:00Z | DEF2 | -23.2 | 12.3 | 1c7939 |
| 2022-01-01T03:00:00Z | DEF2 | -30.4 | 24.9 | 1e8433 |
Ok, this is where you would want to define your own custom function that uses reduce() to return an aggregate sum of the geographic distances of each path. I don’t know that I’ll explain on the logic in here, but I’ve tested this and know that it works:
totalDistance = (tables=<-) =>
tables
|> reduce(
identity: {
index: 0,
lat: 0.0,
lon: 0.0,
totalDistance: 0.0,
},
fn: (r, accumulator) => {
lastPoint =
if accumulator.index == 0 then
{lat: r.lat, lon: r.lon}
else
{lat: accumulator.lat, lon: accumulator.lon}
currentPoint = {lat: r.lat, lon: r.lon}
return {
index: accumulator.index + 1,
lat: r.lat,
lon: r.lon,
totalDistance:
accumulator.totalDistance + geo.ST_Distance(region: lastPoint, geometry: currentPoint),
}
},
)
|> drop(columns: ["index", "lat", "lon"])
So include that in your query:
import "array"
import "experimental/geo"
data =
from(bucket: "example-bucket")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "example-measurement")
|> filter(fn: (r) => r._field == "lat" or r._field == "lon")
totalDistance = (tables=<-) =>
tables
|> reduce(
identity: {
index: 0,
lat: 0.0,
lon: 0.0,
totalDistance: 0.0,
},
fn: (r, accumulator) => {
lastPoint =
if accumulator.index == 0 then
{lat: r.lat, lon: r.lon}
else
{lat: accumulator.lat, lon: accumulator.lon}
currentPoint = {lat: r.lat, lon: r.lon}
return {
index: accumulator.index + 1,
lat: r.lat,
lon: r.lon,
totalDistance:
accumulator.totalDistance + geo.ST_Distance(region: lastPoint, geometry: currentPoint),
}
},
)
|> drop(columns: ["index", "lat", "lon"])
data
|> geo.shapeData(latField: "lat", lonField: "lon", level: 10)
|> group(columns: ["id"])
|> totalDistance()
With the sample data above, this query would return the following:
| id | totalDistance |
|---|---|
| ABC1 | 7028.44474458754 |
| DEF2 | 4428.129653320098 |
Note: By default, the geo package uses km for the unit of distance. To use miles instead, you can set the geo.unit option to mile. This would go at the top of your query, just after your import statements:
option geo.units = {distance: "mile"}
The downside of this approach is that it doesn’t tell you the distance of each leg, just the total distance of the entire sequence of coordinates. Flux doesn’t currently provide a way to do that, but there is a proposal that would make it possible: EPIC: scan function · Issue #4671 · influxdata/flux · GitHub
Hi Scott
perfect solution for me!
I tweaked the formula to my needs (added a filter for GPS errors - had one in my sample data) and it worked within a few minutes!
Big Thank You!
Thomas
Happy to help @thedude. This custom totalDistance() may be worth adding to the geo package. If I can carve out some time, I’ll likely contribute it. With it in the standard library, you could simplify your query a bit 
.
.