GPX to CSV converter for MyFlightBook

gpx-to-flightbook-csv.sh

#!/bin/bash

if [[ $# -ne 2 ]]; then
  echo "GPX to CSV converter for MyFlightBook"
  echo
  echo "Converts a GPX file (e.g. from a handheld GPS unit) into a CSV file that"
  echo "can be imported into MyFlightBook. Performs basic data filtering, event"
  echo "detection, etc."
  echo
  echo "Usage: $0 <start> <stop>"
  echo "  start  Start time of range of data to extract (UTC)"
  echo "  stop   End time of range of data to extract (UTC)"
  echo
  echo " Times should be in the format YYYYMMDD[HH[MM[SS]]]"
  exit 1
fi

START=$1
STOP=$2

TAXI_SPEED=5           # (kt): Taxiing when >= this speed
TAKEOFF_SPEED=55       # (kt): Airborne when >= this speed
TOUCHDOWN_SPEED=45     # (kt): On ground when <= this speed
TOUCHDOWN_VSPEED=-50   # (fpm): On ground when >= this vertical speed

AWK_PROGRAM=$(cat << 'EOFEOFEOF'
function median(values) {
  delete sorted;
  for (i in values) { sorted[i] = values[i]; }
  n = asort(sorted);
  midpoint = n / 2;

  if (midpoint != int(midpoint)) {
    center = sorted[int(midpoint)+1];
    return center;
  }
  else {
    left = sorted[midpoint];
    right = sorted[midpoint+1];
    return (left+right)/2;
  }
}

function array_push(values, value, limit) {
  for (i = length(values); i >= 1; i--) {
    if (i < limit) {
      values[i+1] = values[i];
    }
    else {
      delete values[i];
    }
  }
  values[1] = value;
}

BEGIN {
  STATE="STOPPED";
  FILTER_SIZE=3
  delete TS_FILTER[0];
  delete ALTITUDE_FILTER[0];
  delete VSPEED_FILTER[0];
  delete SPEED_FILTER[0];
}

{
  if (NR > 1) {
    ID = $1;
    LAT = $2;
    LON = $3;
    ALTITUDE = $4;
    SPEED = $5;
    COURSE = $6;
    DATE = $7;
    TIME = $8;

    # Remember recent timestamps
    split(TIME, HMS, ":");
    TS = HMS[1]*3600+HMS[2]*60+HMS[3];
    array_push(TS_FILTER, TS, FILTER_SIZE);

    # Convert altitude in meters to feet and then filter it
    ALTITUDE = ALTITUDE * 100 / 2.54 / 12;
    array_push(ALTITUDE_FILTER, ALTITUDE, FILTER_SIZE);
    ALTITUDE = int(median(ALTITUDE_FILTER));

    # Calculate a vertical speed
    if (TS_FILTER[FILTER_SIZE] > 0) {
      D_ALT = ALTITUDE_FILTER[1] - ALTITUDE_FILTER[FILTER_SIZE];
      D_TS = TS_FILTER[1] - TS_FILTER[FILTER_SIZE];
      if (D_TS > 0) {
        VSPEED = D_ALT / D_TS * 60;
        array_push(VSPEED_FILTER, VSPEED, FILTER_SIZE);
        VSPEED = int(median(VSPEED_FILTER));
      }
    }

    # Convert speed from meters/second to knots and then filter it
    SPEED = SPEED / 1852 * 3600;
    array_push(SPEED_FILTER, SPEED, FILTER_SIZE);
    SPEED = int(median(SPEED_FILTER));

    # Only print a course when we are moving fast enough
    COURSE = sprintf("%.1f", COURSE) + 0;
    if (SPEED < V_TX) { COURSE = ""; }

    # Use YYYY-MM-DD for date
    gsub("/", "-", DATE);

    # Try to detect what the plane is doing
    COMMENT = "";
    if (STATE == "STOPPED") {
      if (SPEED >= V_TX) { STATE = "TAXI";     COMMENT = "Taxi detected"; }
      if (SPEED >= V_TO) { STATE = "AIRBORNE"; COMMENT = "Take-off detected"; }
    }
    else if (STATE == "TAXI") {
      if (SPEED == 0)    { STATE = "STOPPED";  COMMENT = "Stop detected"; }
      if (SPEED >= V_TO) { STATE = "AIRBORNE"; COMMENT = "Take-off detected"; }
    }
    else if (STATE == "AIRBORNE") {
      if (SPEED <= V_TD && VSPEED >= V_TDV) { STATE = "TOUCHDOWN"; COMMENT = "Touchdown detected"; }
      if (SPEED <= V_TD && VSPEED < V_TDV)  { STATE = "AIRBORNE";  COMMENT = "Stall detected"; }
    }
    else if (STATE == "TOUCHDOWN") {
      if (SPEED <= V_TX) { STATE = "TAXI";     COMMENT = "Full-stop landing detected"; }
      if (SPEED >= V_TO) { STATE = "AIRBORNE"; COMMENT = "Touch-and-go detected"; }
    }

    print LAT, LON, ALTITUDE, VSPEED, SPEED, COURSE, DATE, TIME, COMMENT;
  }
  else {
    print "Latitude","Longitude","GPS Altitude","VSPD","GND Speed","Course","UTC Date","UTC Time","Comment";
  }
}
EOFEOFEOF
)

gpsbabel -t -i gpx -f - \
  -x track,merge,speed,course,start=$START,stop=$STOP \
  -o unicsv,utc=0 \
  -F - |
awk -vRS='\r\n' -vFS=, -vOFS='\t' \
  -vV_TO=$TAKEOFF_SPEED \
  -vV_TD=$TOUCHDOWN_SPEED \
  -vV_TDV=$TOUCHDOWN_VSPEED \
  -vV_TX=$TAXI_SPEED \
  "$AWK_PROGRAM"