Compute the altitude gain and loss of a GPX trackSQL

-- This requires the spatial extension
LOAD spatial;

-- Pick for example any Garmin Connect or Strava export as GPX file
-- https://en.wikipedia.org/wiki/GPS_Exchange_Format

WITH elevation_gains AS (
  SELECT ele - lag(ele) OVER (ORDER BY track_seg_point_id ASC) AS value
  FROM st_read('activity_11501782487.gpx', layer='track_points')
)
SELECT 
  sum(e.value) FILTER (WHERE e.value >= 0) AS ascent,
  sum(e.value) FILTER (WHERE e.value < 0) AS descent
FROM elevation_gains e;
Copy code