SailGPS · TrainingEventAnalysis · geospatial walkthrough

From GPS points
to coaching

How a club-racing data project grew into a PostGIS pipeline, a live web map and a YOLO camera eye.

20-minute tour of the development stages — with diagrams, live links and a live analysis at the end.

PostgreSQL / PostGISEPSG:2193 (NZTM) GeoPackage + GPX/FITDocker · Cloudflare Tunnel MapLibre / deck.glYOLO tracking
SailGPS · C:\Users\anton\Sail_GPS\TrainingEventAnalysis01 / 14 · 0:30
Why this project exists

Position + speed is not the whole story

Consumer apps (Strava etc.) stop at where you went and how fast — nothing about skill.
Sailing skill lives in maneuvers: there is one direction you cannot sail — straight into the wind (no-go zone, ~45° off the wind).
And the race officer puts the first mark directly upwind of the start line. Every race starts with tacks.
So the real questions: am I improving? what should I train? — answered from GPS + sensors, benchmarked against fellow sailors.
Story: daughter started sailingInspired by video analysis in rugby (Rassie Erasmus)→ GPS tracking → AI analytics → live clubhouse screen
SailGPS · the problem02 / 14 · 1:30
Development stages

Seven steps down the slippery slope

V0.0 · Jun Free-tier QGIS Cloud + chat GPS tracker compare 2 sailors Hosted app Old PC → web app raw dump + Python ingest share with the club V1 · PostGIS 4-step ETL race / lap / leg mark + line tables EPSG:2193 Docker Ubuntu host Cloudflare Tunnel dev.sailsmart.nz Sensor fusion Old phones as IMU loggers heel · accel 3D 3D speed ribbon YOLO detect + track boats on video auto scoring Presenation/Yolo Now Individual analysis per sailor + coaching agent

Every stage kept on free tiers — swap tools when tokens/credit ran out. The geo core (PostGIS + course context tables) survived every pivot.

SailGPS · development stages03 / 14 · 1:30
Stage 1 · capture

Raw GPS in, nothing lost

Sail_GPS/
├── data/raw, rawstage, rawstage3/   raw GPX / FIT dumps — kept immutable, never edited
├── data/Preload/                     GPX + FIT + shapefiles staged for QGIS
├── data/context/race_context.gpkg    course & mark context (GeoPackage)
├── etl/  scripts + sql                     the 4-step pipeline (next slide)
└── TrainingEventAnalysis/
    ├── Data/
    └── IndividualAnalysis/           per-sailor sessions (slides 10–12)
Raw GPS track, single sailor
Raw 10 s GPS track — the noise is the point; filtering is a separate, rerunnable stage.
Filtered GPS track
After race-window filter + downsample (R2). Raw layer stays untouched.

Sources: handheld GPS trackers (GPX) and old phones running Sensor Logger (FIT/CSV at 1 Hz) — reference scripts: github.com/tszheichoi/awesome-sensor-logger.

SailGPS · stage 1 · capture04 / 14 · 1:30
Stage 2 · the geo core

PostGIS + a 4-step ETL (R1 → R2 → R3 → sailor_track)

RAW DATA FIT / GPX files 1 Hz – 10 s GPS immutable store STEP 1 · R1_* step1_ingest.py parse → raw GPS points table STEP 2 · R2_* filter + segment race window, downsample build LineString STEP 3 · R3_* enrich + mark detection spatial join vs race_marks leg=0..3, metrics, rounded STEP 4 · sailor_track union of all R3_* production-ready view feeds dashboard + reports Layer 0 · master data — hand-entered in QGIS race_regatta · boat_sailor · race_marks (mark points, start/finish line) the manual part: where the marks are, course order, start/finish times PostgreSQL + PostGIS · geometry in EPSG:2193 (NZTM) GeoPackage exports for QGIS · rounded values at R3
SailGPS · stage 2 · PostGIS ETL05 / 14 · 2:00
Stage 3 · host & share

Docker on an Ubuntu box, published through a tunnel

Old PC → Docker Compose (separate dev/prod projects, networks, DB names) → migrated to the Ubuntu machine.
Cloudflare Tunnel publishes it — no open ports; DNS, tunnel ingress, container ports and HTTP response each verified independently.
Presentation layer: deck.gl / kepler.gl sailor dashboard (index.html) + Mapbox/MapLibre map views.
Map tiles note: Mapbox tokens expired (401) → MapLibre GL (no token) + Esri World Imagery; OSM tiles 403 from file:// pages.
SailGPS · stage 3 · hosting06 / 14 · 1:30
From the earlier decks

Key facts worth remembering

2 Jun 2026
Project start
First commit: structure + docs (CIO retrospective).
85 / 270
Commits
On current branch / across all refs.
40
September commits
Peak month — maps & dashboard work (of 85).
5 chapters
Jun → Oct
Ingest → ETL metrics → hosted → maps → dashboard live.
Keep raw data immutable; make every pipeline stage rerunnable; validate calculations against the map.
Separate dev/prod (compose projects, DB names, networks); make deploys observable — DNS, tunnel, ports, routes, HTTP.
Numbered migrations + dynamic table discovery over hard-coded lists — learned from schema incidents (Jun 23, Aug 18 reports).
Early Superset/QGIS-cloud idea was a proposed direction, not the long-running production service — PostGIS + Python won.
Tacking analysis summary from previous deck
From the previous "Lessons Learned" deck: tack events as an explainable debrief — where, speed dip, recovery, personal baseline, audit trail back to the GPS track.
Sailing angle and speed layers
Sailing-angle + speed layers over the course — the diagnostic views that sit between raw GPS and the presentation layer.
SailGPS · key facts from CIO retrospective + lessons learned07 / 14 · 1:30
Stage 4 · beyond 2D

Phone IMUs turn the track into a 3D story

Old phones run Sensor Logger: accelerometer + gyroscope at ~100 Hz, Location at 1 Hz → CSVs per session.
Gyro integration → heel angle over time; sign flip of heel separates tack vs gybe; accel magnitude shows the turn.
Fused with GPS: 3D speed ribbon — geometry computed in EPSG:2193 (true metres), z = speed × 30 m, top face tilted by heel, exported WGS84 for MapLibre.
GeoPackage written as POLYGON Z / LINESTRING Z — loads in QGIS as true 3D XYZ.
Heel angle comparison 11 Nov
Heel compare, 11 Nov 2026 session (Rick "Goose" / Werner "Drank" logged together).
3D acceleration trace, Jake 4 Nov
accel3d trace — Jake, 4 Nov ("Drank" session); the spikes are the maneuvers.
SailGPS · stage 4 · sensor fusion08 / 14 · 1:30
Stage 5 · computer vision

YOLO — you only look once

YoloYacht.mp4 — boat detection + tracking on race video (assets in Presenation/Yolo/).
YOLO detection frame 1
Detection frame — crossing line, boats boxed + re-identified across frames.
YOLO detection frame 2
Tracking keeps IDs across occlusion — basis for automatic mark roundings and scoring.
Why: better race results/scoring from video, and more data collection — mark roundings counted automatically instead of by hand.
Detection (YOLO) finds boats per frame; tracking re-associates them — the two pieces used together.
SailGPS · stage 5 · YOLO09 / 14 · 1:30
Individual analysis · where it lives

One folder per sailor, one folder per session

TrainingEventAnalysis/IndividualAnalysis/
├── Rick/
│   ├── 31 october 26/     5 race GPX · tacks_summary.csv · rick_races.gpkg · rick_races_tacks.gpkg · tack_analysis.png · process_rick.py
│   ├── Goose 2026-11-04/  Sensor Logger CSVs (accel/gyro/location) · accel3d_20261104.html/png · heel pngs
│   └── Goose_11-11-2026/  + heel_20261111.csv · rick_ribbon_20261111.geojson · rick_werner_tack_report_20261111.pdf · tack_profiles/
├── Jake/
│   ├── 31 october 26/     same shape (process_jake.py · jake_races_tacks.gpkg · tack_analysis.png)
│   └── Drank 2026-11-04/  + jake_ribbon_20261104.geojson/.gpkg · jake_ribbon_map_20261104.html (MapLibre 3D ribbon) · heel_report PDF
├── SensorLogsTemp/Werner/Drank_11-11-2026/   heel CSV · tack_profiles T1–T22 · rick_werner report
└── jake_rick_report_20261031/  tack report CSV + PDF (21 pp) + GPKG + 15 tack_profiles/ PNGs

Every session folder is self-contained: raw logs in, calculated tables + GeoPackages + figures + PDF report out. Same schema per sailor → comparable measures across sailors and dates.

SailGPS · IndividualAnalysis folder structure10 / 14 · 1:00
Deep dive 1 · 31 Oct 2026

Jake vs Rick — 5 races, tack by tack

Measure (from tack report CSV)JakeRick
Tacks detected96
Avg speed drop in tack31.2%18.8%
Avg recovery to speed10.1 s10.0 s
Avg distance lost per tack6.5 m (max 20.5)3.9 m (max 11.3)
Grades good / average / focus3 / 3 / 33 / 0 / 3
Jake tack analysis overview
Jake's tack_analysis.png — per-race summary charts.
Jake Race 1 tack 1 profile
Focus: Race 1 T1 — 100% speed drop, 20 s recovery, 15.1 m lost. "Turn quicker through the wind."
Jake Race 3 tack 1 profile
Good: Race 3 T1 — speed actually rose through the turn (−6% drop), 10 s recovery. Same sailor, same day.
SailGPS · 31 Oct · jake_rick_report_2026103111 / 14 · 2:00
Deep dive 2 · 11 Nov 2026 (1 Hz GPS + IMU)

Rick vs Werner — same course, same wind (294–295°)

Measure (rick_werner_tack_summary_20261111.csv)RickWerner
Tacks detected1922
Avg speed drop in tack33.4% (max 54.4%)37.1% (max 55.9%)
Avg recovery to speed7.9 s5.6 s
Avg distance lost per tack3.8 m3.9 m
Grades good / average / focus3 / 7 / 93 / 7 / 12
Worst tack (lat/lon in CSV)Rick T6 18:17:42 @ 175.27544,−37.79914 — 44% drop, 12.7 m lost · Werner T19 18:40:26 — 56% drop
Rick tack 14 profile
Rick T14 — 54.4% drop, 9.8 m lost: "turn quicker through the wind."
Werner tack 16 profile
Werner T16 — 53.7% drop, 7.1 m lost. More focus tacks (12) but faster recovery (5.6 s).
Heel comparison 11 Nov
Heel angle both sailors — the IMU layer behind the tack/gybe split.

Open the 3D ribbon track live (Ubuntu tunnel) — rick_ribbon_20261111.geojson / jake_ribbon_map_20261104.html: speed ribbon, heel-tilted, EPSG:2193 → WGS84.

SailGPS · 11 Nov · Rick vs Werner · ribbon12 / 14 · 1:30
Live demo · ~3 minutes

Ask Mistral to analyze the GPS tracks — right now

Audience picks the question. If nobody shouts one out, use the prepared prompt:

Using the GPS data under C:\Users\anton\Sail_GPS\TrainingEventAnalysis\IndividualAnalysis, compare the sailors on the water: # 31 Oct 2026 — Jake vs Rick: # jake_rick_report_20261031/jake_rick_tack_report_20261031.csv # 11 Nov 2026 — Rick vs Werner: # Rick/Goose_11-11-2026-2026-11-11_04-56-53/rick_werner_tack_summary_20261111.csv Answer: who had the cleanest tacks (speed drop %, recovery s, metres lost per tack)? Where (lat/lon) did the worst tacks happen? What should each sailor train next?

Same prompt saved as Presenation/demo_prompt.txt — paste it into the chat verbatim.

SailGPS · live demo13 / 14 · 3:00
Where this goes next

From calculated events to a coaching agent

Turn tack/gybe events into an explainable debrief: where it happened, speed dip, recovery, vs personal baseline — with a visual audit trail back to the GPS track.
Roll the combined tack report into the QGIS atlas template for map context (make_qgis_report.py).
Heel-vs-speed polar from ribbon data (is the boat faster when flatter?) · per-session wind estimation · 31 Oct vs 4/11 Nov conditions.
YOLO on race video → automatic mark roundings + scoring, joined back to the PostGIS race tables.

Questions?

dev.sailsmart.nz /mapbox/dashboard 3D ribbon (Ubuntu tunnel)

SailGPS · Q&A14 / 14 · buffer