# Calculate Distance travelled from GPS measurement

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

@thedude

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
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

1 Like

And thank you very much @grant1. I consider this a high honor .

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

1 Like

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 .