Strava with Alopex DB & Alopex Skulk

What the service does

Strava records and shares running and cycling activities. Fixed stretches of road are registered as segments, so people who covered the same stretch can compare times. The data is activities, heart rate, speed and position during the workout, and segments.

Where the data lives

Skulk time series: heart rate, speed, cadence and elevation are appended to Skulk, tagged by activity. Per-stretch averages and maximums come from time-bucketed aggregation.
Alopex DB tables: activities, gear, segments and segment efforts live in tables. Segment rankings come from SQL over the efforts table.

Writing the readings from devices and detecting when a segment was covered are app code.

Deployment

Skulk and Alopex DB are embedded in the app on the device. Readings collect on the device during the workout and keep going on a mountain trail with no signal. Shared segment rankings are served by an Alopex DB server.

Why Alopex DB and Alopex Skulk fit

Workout data is both fine-grained time series and a list of activities. Skulk ingests and aggregates the time series, Alopex DB holds activities and segments, and the device and the server use the same SQL.

See it in code

Activities and segments live in Alopex DB (the in-ride time series is aggregated by Skulk). Just the core of producing the segment ranking.

import alopex

# アクティビティとセグメントは Alopex DB（走行中の時系列は Skulk が受け持つ）
db = alopex.Database.open("strava.alopex")

db.execute_sql("CREATE TABLE IF NOT EXISTS segments (id INTEGER PRIMARY KEY, name TEXT)")
db.execute_sql("""
    CREATE TABLE IF NOT EXISTS efforts (
        segment_id INTEGER,
        athlete TEXT,
        elapsed INTEGER
    )
""")

db.execute_sql("INSERT INTO segments (id, name) VALUES (?, ?)", [1, "多摩川土手 5km"])
for athlete, elapsed in [("Aya", 1180), ("Ken", 1095), ("Mio", 1240)]:
    db.execute_sql("INSERT INTO efforts (segment_id, athlete, elapsed) VALUES (?, ?, ?)", [1, athlete, elapsed])

# セグメントの順位（記録の速い順）を SQL で出す
rows = db.execute_sql("""
    SELECT athlete, elapsed
    FROM efforts
    WHERE segment_id = ?
    ORDER BY elapsed ASC
""", [1])

for i, row in enumerate(rows, 1):
    print(i, row["athlete"], f'{row["elapsed"]}秒')

Ordering efforts by elapsed time gives the leaderboard directly. The in-ride heart-rate, speed and position time series is ingested and aggregated by Skulk, tagged by activity.

Alopex Skulk for the time series during a run, Alopex DB for activities and segments. Give that pair a try for a workout app.