Automating Garmin Data Integration into Datasette: A Case Study with Yujin

In the bustling life of avid cyclists, manual data entry from daily rides can become a tedious task. Yujin, a passionate cyclist from Austin, Texas, has been exploring ways to automate the integration of his cycling data into a personal ledger and local leaderboards after his routine rides along the scenic Walnut Creek Trail. This case study details the process of automating data extraction from Garmin devices, converting it into a readable format, and uploading it to Datasette—a tool that allows Yujin to share his cycling achievements and health stats with the Austin cycling community.

Step 1: Setting the Scene

Yujin returns home from his daily cycling expedition, his Garmin watch still pulsing with fresh data from the ride. As he plugs in his devices to charge, a script kicks off in the background on his home computer. This script is the heart of Yujin’s automation process, designed to handle everything from data extraction to public display on the leaderboards.

Step 2: Extracting the Data

The first task is extracting the data from Yujin’s Garmin. This is achieved through Garmin's API, which facilitates the download of GPX or FIT files directly from his device once it connects to the network. This step ensures that all the statistical data from his ride—distance, pace, heart rate, and route—are captured accurately.

Step 3: Parsing and Converting Data

Once the data is downloaded, Yujin’s Python script using gpxpy parses the GPX files to convert them into a more manageable format. The script extracts key metrics and converts them into a CSV format, which includes columns for latitude, longitude, elevation, time, and heart rate:

import gpxpy
import pandas as pd

def parse_gpx(gpx_file_path):
    with open(gpx_file_path, 'r') as file:
        gpx = gpxpy.parse(file)
        data = []
        for track in gpx.tracks:
            for segment in track.segments:
                for point in segment.points:
                    data.append([point.latitude, point.longitude, point.elevation, point.time, point.extensions['heart_rate']])
        return pd.DataFrame(data, columns=['Latitude', 'Longitude', 'Elevation', 'Time', 'Heart Rate'])

df = parse_gpx('yujin_ride.gpx')
df.to_csv('yujin_ride.csv', index=False)

Step 4: Loading Data into SQLite

Using pandas and sqlalchemy, the script loads the CSV data into an SQLite database. This step is crucial as it prepares the data for analysis and display:

from sqlalchemy import create_engine

engine = create_engine('sqlite:///YujinCycling.db')
df = pd.read_csv('yujin_ride.csv')
df.to_sql('cycling_data', con=engine, if_exists='append', index=False)  # Append to include every new ride

Step 5: Launching Datasette

With the data securely stored in SQLite, the final script uses Datasette to serve this database on a web interface, allowing Yujin and his fellow cyclists to view and compare their cycling stats:

import subprocess

def launch_datasette():
    subprocess.run(['datasette', 'YujinCycling.db', '--load-extension=spatialite'])

launch_datasette()

Step 6: Automating the Entire Process

To ensure the automation is seamless, Yujin schedules the script to run every time the Garmin device is plugged in, utilizing Task Scheduler on Windows or Cron on Linux/Mac. This automation means that Yujin’s rides are logged without any manual intervention, allowing him more time to rest and plan his next adventure.

Conclusion

This automation setup not only simplifies Yujin’s data management but also enhances the community experience by maintaining up-to-date leaderboards. Yujin’s method serves as a testament to how cyclists and athletes can leverage technology to minimize manual tasks, ensuring their focus remains on the joy of cycling and improving performance. Through this project, Yujin contributes to a growing culture of data-driven sports enthusiasm in Austin.