Calculate travel-distance from GPS-track with fluxlang

The workbench
1

Calculate travel-distance from GPS-track with fluxlang

See the demonstration within a Grafana dashboard and this forum-post for discussion. The Haversine formula is used.

This query is quite slow. I see paths for improvement:

  • Mathematical: The calculation is performed for every consecutive set of lat/lon-coordinates again and again. It might be more efficient when we aggregate the non-negative differences for all consecutive latitudes and longitudes repectivly and perform the calculation on them. My understanding of sperical geometry is too limited for this approach.
  • Informatical: In order to get the lat/lon-coordinates of the preceding record inline with the current record; a) the same data is queried again (doesn’t work when re-using first table) and b) two expensive join()-statement are used (after timeShift()-ing the 2nd one). A way to access a value of the preceding row would be helpful but is as of flux v0.5 not known to me.

Ideas are welcome!

import "math"
import "experimental"

planetRadiusKm = 6371.0
//planetRadiusKm = 6364.539 // at lat 53°N, sea-level

// 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: "ratrack_tonke")
  |> range($range)
  |> filter(fn: (r) =>
      r._measurement == "solarbox_gps_sensors" 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: "ratrack_tonke")
  |> range($range)
  |> filter(fn: (r) =>
      r._measurement == "solarbox_gps_sensors" 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_diff: r._value_diff,
    lat_last: r._value_raw + r._value_diff
     }))
     
// a new experimental join() was recommended, I need to upgrade first
// not tested scribble:
//LAT = experimental.join(left: LATDIFF, right: LATRAW, fn: (left, right) => ({ left with lat_curr: left._value, lat_diff: right._value, lat_last: right._value + left._value }))


// let's do this stuff again for the longitude values
LONRAW = from(bucket: "ratrack_tonke")
  |> range($range)
  |> filter(fn: (r) =>
      r._measurement == "solarbox_gps_sensors" and
      r._field == "longitude"
     )
  |> degreesToRadians()
  |> aggregateWindow(every: $__interval, fn: mean)
  |> fill(column: "_value", usePrevious: true)

LONDIFF = from(bucket: "ratrack_tonke")
  |> range($range)
  |> filter(fn: (r) =>
      r._measurement == "solarbox_gps_sensors" 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_diff: r._value_diff,
    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",
// used for development/debugging/table-view
//    lon_curr: r.lon_curr,
//    lat_curr: r.lat_curr,
//    lon_last: r.lon_last,
//    lat_last: r.lat_last,
    _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"])
2 Likes