Running Data ETL Pipeline

Running Data ETL Pipeline

Overview

A personal fitness data pipeline that ingests running session data from two wearable sources, merges them by date, enriches with elevation data, and loads into MariaDB for analytics. A Zeppelin notebook on cyrion provides interactive visualizations and performance metrics.

Source code: /home/wes/projects/running_data_ETL/ Database: fitness_tracking on stygies (MariaDB) Notebook: Apache Zeppelin on cyrion (port 8790) — “Running Analytics” notebook

Architecture

┌─────────────────────────────────────┐
│         Data Sources                │
│                                     │
│  Polar Beat (GPS watch)             │
│  → TCX files (XML)                  │
│  → GPS trackpoints, distance, pace  │
│                                     │
│  Heart Buddy (phone app)            │
│  → CSV files                        │
│  → Second-by-second HR readings     │
└──────────────┬──────────────────────┘
               │
               ▼
┌─────────────────────────────────────┐
│         ETL (etl.py)                │
│                                     │
│  1. Auto-scan ~/Downloads           │
│  2. Match TCX + CSV pairs by date   │
│  3. Parse TCX → session + tracks    │
│  4. Parse CSV → HR readings         │
│  5. SRTM elevation lookup per GPS   │
│  6. Load into MariaDB               │
│  7. Move files to processed/        │
└──────────────┬──────────────────────┘
               │
               ▼
┌─────────────────────────────────────┐
│    MariaDB (stygies.c0smere.net)    │
│    Database: fitness_tracking       │
│                                     │
│  Tables:                            │
│  ├─ sessions                        │
│  ├─ trackpoints                     │
│  └─ heart_rate_readings             │
│                                     │
│  Views:                             │
│  ├─ session_summary                 │
│  └─ session_timeline                │
└──────────────┬──────────────────────┘
               │
               ▼
┌─────────────────────────────────────┐
│    Apache Zeppelin (cyrion:8790)    │
│    "Running Analytics" notebook     │
│                                     │
│  12 visualizations + SQL analytics  │
│  Read-only user: zeppelin           │
└─────────────────────────────────────┘

Data Sources

Polar Beat (TCX Files)

Training Center XML format from a Polar fitness watch. Each file contains:

• Session metadata: sport type, start time, duration, distance, calories
• GPS trackpoints: timestamp, latitude, longitude, cumulative distance (per second)

Filename pattern: Wesley_Ray_2026-02-10_06-24-04.TCX

Heart Buddy (CSV Files)

Separate heart rate sensor app running on a phone. Outputs second-by-second BPM readings.

Filename pattern: Session-Tuesday, 10 Feb 2026.csv

The pipeline matches these two sources by extracting the date from each filename — a TCX and CSV file from the same day are treated as a pair for the same running session.

ETL Script (etl.py)

Language: Python 3 Dependencies: mariadb, srtm, python-dotenv Config: .env file with DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME

Modes

# Auto-scan ~/Downloads, match pairs by date, load, move to processed/
python3 etl.py

# Explicit file paths
python3 etl.py --tcx route.TCX --csv heartrate.csv

# Backfill elevation for existing trackpoints missing it
python3 etl.py --backfill

# Don't move files after loading
python3 etl.py --no-move

Pipeline Steps

1. Scan — Walks ~/Downloads (or --dir) for .TCX and Session-*.csv files
2. Match — Extracts dates from filenames and pairs files that share the same date
3. Parse TCX — Extracts session metadata and GPS trackpoints, converts UTC → Eastern time
4. Parse CSV — Extracts heart rate readings with timestamps
5. Elevation enrichment — For each GPS trackpoint, looks up elevation via SRTM (~30m resolution, tiles cached locally at ~/.cache/srtm/)
6. Load session — Inserts into sessions table; skips on UNIQUE constraint violation (idempotent)
7. Load trackpoints — Bulk inserts GPS data with elevation into trackpoints
8. Load HR readings — Bulk inserts BPM data into heart_rate_readings
9. Archive — Moves source files to processed/ directory

Key Design Decisions

• Date-based file matching rather than manual pairing — just dump both files in Downloads and run the script
• SRTM elevation because Polar Beat TCX files don’t include altitude data; the srtm library downloads .hgt tiles on first use and caches them for offline lookups
• Duplicate detection via UNIQUE constraint on session_date — re-running the script on already-loaded data is safe
• UTC → Eastern conversion at parse time so all stored timestamps are in local time

Database Schema

Tables

sessions — One row per running session

trackpoints — GPS breadcrumbs, ~1 per second

heart_rate_readings — BPM data, ~1 per second

Views

session_summary — Pre-computed per-session metrics including:

• Distance (miles), duration (mm:ss), avg/max speed (mph), avg/max pace (min/mi)
• HR stats: min, max, avg, stddev, reading count
• HR zone distribution (zone1–5 percentages)
• GPS bounding box (min/max lat/lon, start/end coordinates)
• Trackpoint count

session_timeline — Joined trackpoints + heart rate readings by session and timestamp. The primary query target for the Zeppelin notebook — gives a unified view of position, distance, and heart rate at each second.

Zeppelin Notebook

URL: http://cyrion.c0smere.net:8790 Notebook: “Running Analytics” (2MKPER448) DB connection: Read-only MariaDB user zeppelin on stygies

The notebook is organized into three sections:

Data Visualizations

All plots use matplotlib + seaborn, with data loaded via SQLAlchemy from the session_summary, session_timeline, and heart_rate_readings tables/views.

Analytics / Performance Metrics

Three metrics for tracking aerobic fitness over time:

Pace at Fixed Heart Rate (~140 BPM) — Filters GPS data to samples where HR is between 138–142 BPM and computes average pace. As aerobic fitness improves, pace at the same heart rate drops. This is the headline metric — no modeling or assumptions, just a straightforward query. Sessions with <30 qualifying samples are excluded.

Cardiac Efficiency Index — Average speed divided by average heart rate, scaled by 1000. Higher = more forward motion per heartbeat. Simple to compute but sensitive to run/walk intervals (walk breaks drag down speed while HR stays elevated). Becomes more meaningful as continuous running becomes the norm.

Aerobic Decoupling — Splits each session into two halves by time, computes pace-to-HR ratio for each half, reports the percentage difference. Below 5% is excellent (stable cardiac output). Currently disabled because run/walk intervals mask the progressive cardiac drift the metric is designed to detect.

Estimated VO2max (ACSM Formula) — Approximation using the ACSM metabolic equation: VO2 = (speed_m_per_min × 0.2) + 3.5, extrapolated via heart rate reserve method. Acknowledges the estimate is inflated at higher body weight (218 lbs) since the formula assumes average weight. Useful for trending, not absolute values.

Elevation Data

Trackpoints enriched via SRTM provide:

• Elevation profile with HR overlay — Filled elevation plot (distance on X axis) with heart rate on a secondary Y axis
• Route colored by elevation — GPS plot colored by terrain height
• HR vs hill grade — Scatter plot examining whether HR spikes on inclines, with trend line overlay
• Cumulative elevation gain — Line chart of running elevation gain (ft) over elapsed time

References

The notebook includes a references section citing ACSM metabolic equations, Karvonen heart rate methods, Joe Friel’s work on aerobic decoupling and efficiency factor, and cardiovascular drift literature.

Other Scripts

plot_all_runs.py — Standalone script (not in Zeppelin) that generates a static PNG heatmap of all runs. Bins GPS trackpoints at 5m grid resolution, reprojects to Web Mercator, and overlays on OpenStreetMap tiles via contextily. Output: all_runs_heatmap.png.

load_feb8.py / load_polar_hr_sessions.py — One-time loader scripts for sessions where the Polar watch captured embedded HR data (no separate Heart Buddy CSV). These handle the alternate data format for Feb 8, 16, and 18 sessions.

Infrastructure

Zeppelin Docker Config

zeppelin:
  image: apache/zeppelin:${ZEPPELIN_TAG}
  ports:
    - "8790:8080"    # Web UI
    - "8791:8443"    # WebSocket
  volumes:
    - ./zeppelin/notebook:/opt/zeppelin/notebook
    - ./zeppelin/logs:/opt/zeppelin/logs
    - ./zeppelin/conf:/opt/zeppelin/conf
  environment:
    - ZEPPELIN_MEM=-Xms1024m -Xmx2048m
    - ZEPPELIN_INTP_MEM=-Xms512m -Xmx1024m

The notebook connects to MariaDB via a JDBC interpreter configured in interpreter.json. The zeppelin database user has read-only access to fitness_tracking.