Solution using the most popular time series platform
import "math"
import "experimental"
planetRadiusKm = 6371.0
// helper function to convert degrees to radians
degreesToRadians = (tables=
tables
|> map(fn: (r) => ({ r with _value: r._value * math.pi / 180.0 }))
// let's call all latitude values
LATRAW = from(bucket: "my_bucket")
|> range($range)
|> filter(fn: (r) =>
r._measurement == "gps" and
r._field == "latitude"
)
|> degreesToRadians()
|> aggregateWindow(every: $__interval, fn: mean)
|> fill(column: "_value", usePrevious: true)
// let's create the differences of all latitude values and shift them by one
LATDIFF = from(bucket: "my_bucket")
|> range($range)
|> filter(fn: (r) =>
r._measurement == "gps" and
r._field == "latitude"
)
|> degreesToRadians()
|> aggregateWindow(every: $__interval, fn: mean)
|> difference(nonNegative: false, columns: ["_value"])
|> timeShift(duration: -$__interval, columns: ["_start", "_stop", "_time"])
|> fill(value: 0.0)
// let's join both lat tables together,
// so we have current and previous latitudes in one row
LAT = join(tables: {raw: LATRAW, diff: LATDIFF}, on: ["_time"])
|> sort()
|> map(fn: (r) => ({
_time: r._time,
lat_curr: r._value_raw,
lat_last: r._value_raw + r._value_diff
}))
// let's do this stuff again for the longitude values
LONRAW = from(bucket: "my_bucket")
|> range($range)
|> filter(fn: (r) =>
r._measurement == "gps" and
r._field == "longitude"
)
|> degreesToRadians()
|> aggregateWindow(every: $__interval, fn: mean)
|> fill(column: "_value", usePrevious: true)
LONDIFF = from(bucket: "my_bucket")
|> range($range)
|> filter(fn: (r) =>
r._measurement == "gps" and
r._field == "longitude"
)
|> degreesToRadians()
|> aggregateWindow(every: $__interval, fn: mean)
|> difference(nonNegative: false, columns: ["_value"])
|> timeShift(duration: -$__interval, columns: ["_start", "_stop", "_time"])
|> fill(value: 0.0)
LON = join(tables: {raw: LONRAW, diff: LONDIFF}, on: ["_time"])
|> sort()
|> map(fn: (r) => ({
_time: r._time,
lon_curr: r._value_raw,
lon_last: r._value_raw + r._value_diff
}))
// let's join lats and lons together, filter out NaNs (ugly),
// apply haversine formula and accumulate the sums to get travel-distance
join(tables: {lat:LAT, lon:LON}, on: ["_time"])
|> filter(fn: (r) =>
r.lat_curr >= -90.0 and
r.lon_curr >= -90.0 and
r.lat_last >= -90.0 and
r.lon_last >= -90.0
)
|> map(fn: (r) => ({
_time: r._time,
_field: "travel-distance",
_value: ( 2.0 * math.atan2(
x: math.sqrt(x: (math.sin(x: (r.lat_curr - r.lat_last)/2.0) *
math.sin(x: (r.lat_curr - r.lat_last)/2.0)) + (math.sin(x: (r.lon_curr -
r.lon_last)/2.0) * math.sin(x: (r.lon_curr - r.lon_last)/2.0)) *
math.cos(x: r.lat_curr) * math.cos(x: r.lat_last)),
y: math.sqrt(x: 1.0 - (math.sin(x: (r.lat_curr - r.lat_last)/2.0) *
math.sin(x: (r.lat_curr - r.lat_last)/2.0)) + (math.sin(x: (r.lon_curr -
r.lon_last)/2.0) * math.sin(x: (r.lon_curr - r.lon_last)/2.0)) *
math.cos(x: r.lat_curr) * math.cos(x: r.lat_last)))
) * planetRadiusKm * 1000.0
}))
|> cumulativeSum(columns: ["_value"])