aboutsummaryrefslogtreecommitdiff
path: root/official_climb
diff options
context:
space:
mode:
Diffstat (limited to 'official_climb')
-rw-r--r--official_climb/aggregate.go147
-rw-r--r--official_climb/aggregate_test.go76
-rw-r--r--official_climb/attempt.go158
-rw-r--r--official_climb/attempt_test.go128
-rw-r--r--official_climb/list.go133
-rw-r--r--official_climb/list_test.go52
6 files changed, 694 insertions, 0 deletions
diff --git a/official_climb/aggregate.go b/official_climb/aggregate.go
new file mode 100644
index 0000000..c2bd75d
--- /dev/null
+++ b/official_climb/aggregate.go
@@ -0,0 +1,147 @@
+package official_climb
+
+import (
+ "sort"
+ "time"
+
+ "github.com/martinlehoux/biking_home/climb"
+)
+
+const profileSteps = 50
+
+// Aggregate is the climb-level summary derived from its recorded attempts.
+type Aggregate struct {
+ Attempts int
+ DistanceM float64
+ ElevationGainM float64
+ SlopePercent float64
+ Cotacol float64
+ Category climb.Category
+ BestDuration time.Duration
+ HasBest bool
+}
+
+// AggregateAttempts summarizes a climb from its attempts: every scalar is the
+// median across attempts so one noisy ride cannot define the climb.
+func AggregateAttempts(attempts []Attempt) Aggregate {
+ if len(attempts) == 0 {
+ return Aggregate{}
+ }
+ distances := make([]float64, len(attempts))
+ gains := make([]float64, len(attempts))
+ slopes := make([]float64, len(attempts))
+ cotacols := make([]float64, len(attempts))
+ aggregate := Aggregate{Attempts: len(attempts)}
+ for i, attempt := range attempts {
+ distances[i] = attempt.DistanceM
+ gains[i] = attempt.ElevationGainM
+ slopes[i] = attempt.SlopePercent
+ cotacols[i] = attempt.Cotacol
+ duration := attempt.EndedAt.Sub(attempt.StartedAt)
+ if duration > 0 && (!aggregate.HasBest || duration < aggregate.BestDuration) {
+ aggregate.BestDuration = duration
+ aggregate.HasBest = true
+ }
+ }
+ aggregate.DistanceM = Median(distances)
+ aggregate.ElevationGainM = Median(gains)
+ aggregate.SlopePercent = Median(slopes)
+ aggregate.Cotacol = Median(cotacols)
+ aggregate.Category = climb.CategoryOf(aggregate.Cotacol)
+ return aggregate
+}
+
+// Median returns the median of values; for an even count it averages the two
+// middle values.
+func Median(values []float64) float64 {
+ if len(values) == 0 {
+ return 0
+ }
+ sorted := make([]float64, len(values))
+ copy(sorted, values)
+ sort.Float64s(sorted)
+ middle := len(sorted) / 2
+ if len(sorted)%2 == 1 {
+ return sorted[middle]
+ }
+ return (sorted[middle-1] + sorted[middle]) / 2
+}
+
+// normalizedSample locates one normalized step inside a segment: the index of
+// the recorded point preceding the target distance and the interpolation
+// fraction toward the next point.
+type normalizedSample struct {
+ index int
+ fraction float64
+}
+
+// normalizedSamples locates profileSteps equally spaced fractions of the
+// segment length, interpolating between recorded points. Distances are
+// relative: segments of different lengths stay comparable.
+func normalizedSamples(distances []float64) []normalizedSample {
+ if len(distances) < 2 {
+ return nil
+ }
+ start, end := distances[0], distances[len(distances)-1]
+ span := end - start
+ if span <= 0 {
+ return nil
+ }
+ samples := make([]normalizedSample, profileSteps)
+ i := 0
+ for step := range profileSteps {
+ target := start + span*float64(step)/float64(profileSteps-1)
+ for i < len(distances)-2 && distances[i+1] < target {
+ i++
+ }
+ segmentSpan := distances[i+1] - distances[i]
+ fraction := 0.0
+ if segmentSpan > 0 {
+ fraction = (target - distances[i]) / segmentSpan
+ }
+ samples[step] = normalizedSample{index: i, fraction: fraction}
+ }
+ return samples
+}
+
+// NormalizeProfile samples a climb segment's elevation at profileSteps
+// equally spaced fractions of the segment length, interpolating between
+// recorded points.
+func NormalizeProfile(segment climb.Climb) []float64 {
+ samples := normalizedSamples(segment.Distances)
+ if samples == nil {
+ return nil
+ }
+ elevations := make([]float64, len(samples))
+ for step, sample := range samples {
+ elevations[step] = sampleElevation(segment.Distances, segment.Elevations, sample)
+ }
+ return elevations
+}
+
+func sampleElevation(distances, elevations []float64, sample normalizedSample) float64 {
+ i := sample.index
+ if i+1 >= len(distances) {
+ return elevations[len(elevations)-1]
+ }
+ return elevations[i] + sample.fraction*(elevations[i+1]-elevations[i])
+}
+
+// MedianProfile merges per-attempt normalized elevation samples into one
+// canonical profile: the median elevation at every step.
+func MedianProfile(samples [][]float64) []float64 {
+ median := make([]float64, 0, profileSteps)
+ for step := range profileSteps {
+ values := make([]float64, 0, len(samples))
+ for _, sample := range samples {
+ if step < len(sample) {
+ values = append(values, sample[step])
+ }
+ }
+ if len(values) == 0 {
+ continue
+ }
+ median = append(median, Median(values))
+ }
+ return median
+}
diff --git a/official_climb/aggregate_test.go b/official_climb/aggregate_test.go
new file mode 100644
index 0000000..2e051ce
--- /dev/null
+++ b/official_climb/aggregate_test.go
@@ -0,0 +1,76 @@
+package official_climb
+
+import (
+ "testing"
+ "time"
+
+ "github.com/martinlehoux/biking_home/climb"
+ "github.com/stretchr/testify/assert"
+ "github.com/stretchr/testify/require"
+)
+
+func TestMedianOddAndEven(t *testing.T) {
+ assert.Equal(t, 3.0, Median([]float64{5, 1, 3}))
+ assert.Equal(t, 2.5, Median([]float64{1, 2, 3, 4}))
+ assert.Equal(t, 0.0, Median(nil))
+}
+
+func TestAggregateAttemptsUsesMedians(t *testing.T) {
+ attempts := []Attempt{
+ {DistanceM: 3000, ElevationGainM: 200, SlopePercent: 6.7, Cotacol: 80, StartedAt: time.Date(2026, 1, 1, 8, 0, 0, 0, time.UTC), EndedAt: time.Date(2026, 1, 1, 8, 20, 0, 0, time.UTC)},
+ {DistanceM: 3200, ElevationGainM: 210, SlopePercent: 6.6, Cotacol: 84, StartedAt: time.Date(2026, 2, 1, 8, 0, 0, 0, time.UTC), EndedAt: time.Date(2026, 2, 1, 8, 15, 0, 0, time.UTC)},
+ {DistanceM: 6000, ElevationGainM: 900, SlopePercent: 15, Cotacol: 400, StartedAt: time.Date(2026, 3, 1, 8, 0, 0, 0, time.UTC), EndedAt: time.Date(2026, 3, 1, 8, 40, 0, 0, time.UTC)},
+ }
+ aggregate := AggregateAttempts(attempts)
+ assert.Equal(t, 3, aggregate.Attempts)
+ assert.Equal(t, 3200.0, aggregate.DistanceM)
+ assert.Equal(t, 210.0, aggregate.ElevationGainM)
+ assert.Equal(t, 6.7, aggregate.SlopePercent)
+ assert.Equal(t, 84.0, aggregate.Cotacol)
+ require.True(t, aggregate.HasBest)
+ assert.Equal(t, 15*time.Minute, aggregate.BestDuration)
+}
+
+func TestAggregateAttemptsIgnoresBrokenDurations(t *testing.T) {
+ attempts := []Attempt{
+ {DistanceM: 3000, StartedAt: time.Date(2026, 1, 1, 8, 0, 0, 0, time.UTC), EndedAt: time.Date(2026, 1, 1, 7, 0, 0, 0, time.UTC)},
+ {DistanceM: 3200, StartedAt: time.Date(2026, 2, 1, 8, 0, 0, 0, time.UTC), EndedAt: time.Date(2026, 2, 1, 8, 15, 0, 0, time.UTC)},
+ }
+ aggregate := AggregateAttempts(attempts)
+ require.True(t, aggregate.HasBest)
+ assert.Equal(t, 15*time.Minute, aggregate.BestDuration)
+}
+
+func TestAggregateAttemptsEmpty(t *testing.T) {
+ aggregate := AggregateAttempts(nil)
+ assert.Equal(t, 0, aggregate.Attempts)
+ assert.False(t, aggregate.HasBest)
+ assert.Equal(t, climb.CategoryNo, aggregate.Category)
+}
+
+func TestNormalizeProfileLinearSegment(t *testing.T) {
+ segment := climb.Climb{Route: climb.Route{
+ Distances: []float64{0, 500, 1000},
+ Elevations: []float64{100, 150, 200},
+ }}
+ samples := NormalizeProfile(segment)
+ require.Len(t, samples, profileSteps)
+ assert.InDelta(t, 100, samples[0], 0.01)
+ assert.InDelta(t, 200, samples[profileSteps-1], 0.01)
+ assert.InDelta(t, 100+100*float64(profileSteps/2)/float64(profileSteps-1), samples[profileSteps/2], 0.01)
+}
+
+func TestMedianProfileRobustToOutlier(t *testing.T) {
+ straight := make([]float64, profileSteps)
+ for i := range straight {
+ straight[i] = float64(i)
+ }
+ spiked := make([]float64, profileSteps)
+ for i := range spiked {
+ spiked[i] = float64(i) + 500
+ }
+ median := MedianProfile([][]float64{straight, spiked, straight})
+ require.Len(t, median, profileSteps)
+ assert.InDelta(t, 10, median[10], 0.01)
+ assert.InDelta(t, 49, median[49], 0.01)
+}
diff --git a/official_climb/attempt.go b/official_climb/attempt.go
new file mode 100644
index 0000000..43778f0
--- /dev/null
+++ b/official_climb/attempt.go
@@ -0,0 +1,158 @@
+package official_climb
+
+import (
+ "database/sql"
+ "fmt"
+ "time"
+
+ "github.com/martinlehoux/biking_home/climb"
+)
+
+// Attempt is one recorded passage of an official climb during one ride,
+// identified by (rideID, climbID, occurrence). Scalar stats are snapshotted at
+// recording time so climb listings never re-parse ride files.
+type Attempt struct {
+ RideID int64
+ ClimbID int64
+ Occurrence int
+ DistanceM float64
+ ElevationGainM float64
+ SlopePercent float64
+ Cotacol float64
+ CotacolAlgoVersion string
+ StartedAt time.Time
+ EndedAt time.Time
+ CreatedAt time.Time
+}
+
+const attemptColumns = "a.ride_id, a.climb_id, a.occurrence, a.distance_m, a.elevation_gain_m, a.slope_percent, a.cotacol, a.cotacol_algo_version, a.started_at, a.ended_at, a.created_at"
+
+// AttemptsForClimb returns every recorded attempt of one official climb,
+// oldest ride first.
+func AttemptsForClimb(db *sql.DB, climbID int64) ([]Attempt, error) {
+ return listAttempts(db, "climb_id = ?", climbID, "r.start_date, a.ride_id, a.occurrence")
+}
+
+// AttemptsForRide returns every recorded attempt within one ride, in route
+// order.
+func AttemptsForRide(db *sql.DB, rideID int64) ([]Attempt, error) {
+ return listAttempts(db, "ride_id = ?", rideID, "a.occurrence")
+}
+
+func listAttempts(db *sql.DB, where string, arg any, order string) ([]Attempt, error) {
+ rows, err := db.Query(`
+ SELECT `+attemptColumns+`
+ FROM ride_official_climb_attempts a
+ JOIN rides r ON r.id = a.ride_id
+ WHERE `+where+`
+ ORDER BY `+order, arg)
+ if err != nil {
+ return nil, fmt.Errorf("list official climb attempts: %w", err)
+ }
+ defer rows.Close()
+ attempts := make([]Attempt, 0)
+ for rows.Next() {
+ attempt, err := scanAttempt(rows)
+ if err != nil {
+ return nil, err
+ }
+ attempts = append(attempts, attempt)
+ }
+ if err := rows.Err(); err != nil {
+ return nil, fmt.Errorf("iterate official climb attempts: %w", err)
+ }
+ return attempts, nil
+}
+
+// ReplaceRideAttempts atomically rewrites every attempt row of one ride,
+// dropping stale matches and inserting the freshly analyzed ones.
+func ReplaceRideAttempts(db *sql.DB, rideID int64, attempts []Attempt) error {
+ tx, err := db.Begin()
+ if err != nil {
+ return fmt.Errorf("replace ride attempts: %w", err)
+ }
+ defer func() { _ = tx.Rollback() }()
+ if _, err := tx.Exec("DELETE FROM ride_official_climb_attempts WHERE ride_id = ?", rideID); err != nil {
+ return fmt.Errorf("delete ride attempts: %w", err)
+ }
+ for _, attempt := range attempts {
+ if attempt.RideID != rideID {
+ return fmt.Errorf("replace ride attempts: attempt references ride %d, expected %d", attempt.RideID, rideID)
+ }
+ if _, err := tx.Exec(`
+ INSERT INTO ride_official_climb_attempts (ride_id, climb_id, occurrence, distance_m, elevation_gain_m, slope_percent, cotacol, cotacol_algo_version, started_at, ended_at)
+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+ `, attempt.RideID, attempt.ClimbID, attempt.Occurrence, attempt.DistanceM, attempt.ElevationGainM, attempt.SlopePercent, attempt.Cotacol, attempt.CotacolAlgoVersion,
+ attempt.StartedAt.UTC().Format(time.RFC3339), attempt.EndedAt.UTC().Format(time.RFC3339)); err != nil {
+ return fmt.Errorf("insert ride attempt: %w", err)
+ }
+ }
+ if err := tx.Commit(); err != nil {
+ return fmt.Errorf("commit ride attempts: %w", err)
+ }
+ return nil
+}
+
+type attemptScanner interface {
+ Scan(dest ...any) error
+}
+
+func scanAttempt(s attemptScanner) (Attempt, error) {
+ var (
+ attempt Attempt
+ startedAt, endedAt string
+ createdAt sql.NullString
+ )
+ if err := s.Scan(&attempt.RideID, &attempt.ClimbID, &attempt.Occurrence, &attempt.DistanceM, &attempt.ElevationGainM, &attempt.SlopePercent, &attempt.Cotacol, &attempt.CotacolAlgoVersion, &startedAt, &endedAt, &createdAt); err != nil {
+ return Attempt{}, err
+ }
+ var err error
+ if attempt.StartedAt, err = time.Parse(time.RFC3339, startedAt); err != nil {
+ return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.started_at: %w", err)
+ }
+ if attempt.EndedAt, err = time.Parse(time.RFC3339, endedAt); err != nil {
+ return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.ended_at: %w", err)
+ }
+ if createdAt.Valid {
+ if attempt.CreatedAt, err = time.Parse(time.RFC3339, createdAt.String); err != nil {
+ return Attempt{}, fmt.Errorf("invalid ride_official_climb_attempts.created_at: %w", err)
+ }
+ }
+ return attempt, nil
+}
+
+// AttemptStats carries the analyzed values snapshotted into an attempt row.
+type AttemptStats struct {
+ OfficialClimbID int64
+ DistanceM float64
+ ElevationGainM float64
+ SlopePercent float64
+ Cotacol float64
+ StartIndex int
+ EndIndex int
+}
+
+// AttemptFromStats builds the persisted snapshot of one analyzed climb
+// occurrence. Timestamps come from the ride's recorded points bounding the
+// climb interval.
+func AttemptFromStats(rideID int64, occurrence int, stats AttemptStats, timestamps []time.Time) Attempt {
+ startedAt, endedAt := time.Time{}, time.Time{}
+ if stats.StartIndex < len(timestamps) {
+ startedAt = timestamps[stats.StartIndex]
+ }
+ if stats.EndIndex < len(timestamps) {
+ endedAt = timestamps[stats.EndIndex]
+ }
+ return Attempt{
+ RideID: rideID,
+ ClimbID: stats.OfficialClimbID,
+ Occurrence: occurrence,
+ DistanceM: stats.DistanceM,
+ ElevationGainM: stats.ElevationGainM,
+ SlopePercent: stats.SlopePercent,
+ Cotacol: stats.Cotacol,
+ CotacolAlgoVersion: climb.CotacolAlgorithmVersion,
+ StartedAt: startedAt,
+ EndedAt: endedAt,
+ }
+}
diff --git a/official_climb/attempt_test.go b/official_climb/attempt_test.go
new file mode 100644
index 0000000..147c80b
--- /dev/null
+++ b/official_climb/attempt_test.go
@@ -0,0 +1,128 @@
+package official_climb_test
+
+import (
+ "database/sql"
+ "testing"
+ "time"
+
+ "github.com/jftuga/geodist"
+ "github.com/martinlehoux/biking_home/internal/dbtest"
+ "github.com/martinlehoux/biking_home/official_climb"
+ "github.com/stretchr/testify/assert"
+ "github.com/stretchr/testify/require"
+)
+
+func coord(lat, lon float64) geodist.Coord {
+ return geodist.Coord{Lat: lat, Lon: lon}
+}
+
+func insertRide(t *testing.T, db *sql.DB, opts ...func(*rideInsert)) int64 {
+ t.Helper()
+ insert := rideInsert{startDate: "2026-09-01T08:00:00Z"}
+ for _, opt := range opts {
+ opt(&insert)
+ }
+ result, err := db.Exec(`
+ INSERT INTO rides (external_id, gpx_path, name, external_sport_type, start_date, distance_m, external_moving_time_s, elapsed_time_s, total_elevation_gain_m, average_speed_mps)
+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+ `, "strava:test-ride-"+insert.startDate, "missing.gpx", "Test Ride", "Ride", insert.startDate, 20_000, 0, 0, 0, 0)
+ require.NoError(t, err)
+ id, err := result.LastInsertId()
+ require.NoError(t, err)
+ return id
+}
+
+type rideInsert struct {
+ startDate string
+}
+
+func withStartDate(startDate string) func(*rideInsert) {
+ return func(r *rideInsert) { r.startDate = startDate }
+}
+
+func attemptFixture(rideID, climbID int64, occurrence int) official_climb.Attempt {
+ return official_climb.Attempt{
+ RideID: rideID,
+ ClimbID: climbID,
+ Occurrence: occurrence,
+ DistanceM: 3200,
+ ElevationGainM: 210,
+ SlopePercent: 6.5,
+ Cotacol: 84.2,
+ CotacolAlgoVersion: "v1",
+ StartedAt: time.Date(2026, 9, 1, 10, 0, 0, 0, time.UTC),
+ EndedAt: time.Date(2026, 9, 1, 10, 18, 30, 0, time.UTC),
+ }
+}
+
+func TestReplaceRideAttemptsRoundTrip(t *testing.T) {
+ db := dbtest.New(t)
+ created, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col de Test", StartCoord: coord(43.6, 5.4), EndCoord: coord(43.62, 5.42)})
+ require.NoError(t, err)
+ rideID := insertRide(t, db)
+
+ require.NoError(t, official_climb.ReplaceRideAttempts(db, rideID, []official_climb.Attempt{
+ attemptFixture(rideID, created.ID, 0),
+ attemptFixture(rideID, created.ID, 1),
+ }))
+
+ attempts, err := official_climb.AttemptsForClimb(db, created.ID)
+ require.NoError(t, err)
+ require.Len(t, attempts, 2)
+ assert.Equal(t, 0, attempts[0].Occurrence)
+ assert.Equal(t, 1, attempts[1].Occurrence)
+ assert.Equal(t, 3200.0, attempts[0].DistanceM)
+ assert.Equal(t, "v1", attempts[0].CotacolAlgoVersion)
+ assert.Equal(t, time.Date(2026, 9, 1, 10, 18, 30, 0, time.UTC), attempts[0].EndedAt)
+
+ rideAttempts, err := official_climb.AttemptsForRide(db, rideID)
+ require.NoError(t, err)
+ require.Len(t, rideAttempts, 2)
+}
+
+func TestReplaceRideAttemptsDropsStaleRows(t *testing.T) {
+ db := dbtest.New(t)
+ first, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col A", StartCoord: coord(43.6, 5.4), EndCoord: coord(43.62, 5.42)})
+ require.NoError(t, err)
+ second, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col B", StartCoord: coord(43.7, 5.5), EndCoord: coord(43.72, 5.52)})
+ require.NoError(t, err)
+ rideID := insertRide(t, db)
+
+ require.NoError(t, official_climb.ReplaceRideAttempts(db, rideID, []official_climb.Attempt{attemptFixture(rideID, first.ID, 0)}))
+ require.NoError(t, official_climb.ReplaceRideAttempts(db, rideID, []official_climb.Attempt{attemptFixture(rideID, second.ID, 0)}))
+
+ attempts, err := official_climb.AttemptsForClimb(db, first.ID)
+ require.NoError(t, err)
+ assert.Empty(t, attempts)
+ attempts, err = official_climb.AttemptsForClimb(db, second.ID)
+ require.NoError(t, err)
+ require.Len(t, attempts, 1)
+}
+
+func TestReplaceRideAttemptsRejectsForeignRide(t *testing.T) {
+ db := dbtest.New(t)
+ created, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col de Test", StartCoord: coord(43.6, 5.4), EndCoord: coord(43.62, 5.42)})
+ require.NoError(t, err)
+ rideID := insertRide(t, db)
+
+ attempt := attemptFixture(rideID+1, created.ID, 0)
+ err = official_climb.ReplaceRideAttempts(db, rideID, []official_climb.Attempt{attempt})
+ assert.Error(t, err)
+}
+
+func TestAttemptsForClimbOrdersByRideDate(t *testing.T) {
+ db := dbtest.New(t)
+ created, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col de Test", StartCoord: coord(43.6, 5.4), EndCoord: coord(43.62, 5.42)})
+ require.NoError(t, err)
+ newer := insertRide(t, db, withStartDate("2026-09-10T08:00:00Z"))
+ older := insertRide(t, db, withStartDate("2026-09-02T08:00:00Z"))
+
+ require.NoError(t, official_climb.ReplaceRideAttempts(db, newer, []official_climb.Attempt{attemptFixture(newer, created.ID, 0)}))
+ require.NoError(t, official_climb.ReplaceRideAttempts(db, older, []official_climb.Attempt{attemptFixture(older, created.ID, 0)}))
+
+ attempts, err := official_climb.AttemptsForClimb(db, created.ID)
+ require.NoError(t, err)
+ require.Len(t, attempts, 2)
+ assert.Equal(t, older, attempts[0].RideID)
+ assert.Equal(t, newer, attempts[1].RideID)
+}
diff --git a/official_climb/list.go b/official_climb/list.go
new file mode 100644
index 0000000..33b12e8
--- /dev/null
+++ b/official_climb/list.go
@@ -0,0 +1,133 @@
+package official_climb
+
+import (
+ "database/sql"
+ "fmt"
+ "time"
+
+ "github.com/martinlehoux/biking_home/climb"
+)
+
+// SortColumn is a climbs-list ordering key.
+type SortColumn string
+
+const (
+ SortName SortColumn = "name"
+ SortAttempts SortColumn = "attempts"
+ SortDistance SortColumn = "distance"
+ SortSlope SortColumn = "slope"
+ SortCotacol SortColumn = "cotacol"
+ SortBestTime SortColumn = "best_time"
+)
+
+// ListedClimb is one climbs-list row: official climb identity plus aggregates
+// over its recorded attempts. Medians are computed in Go from the attempt
+// rows; best time is the minimum attempt duration.
+type ListedClimb struct {
+ ID int64
+ Name string
+ Attempts int
+ DistanceM float64
+ ElevationGainM float64
+ SlopePercent float64
+ Cotacol float64
+ BestDurationS float64
+ HasBest bool
+}
+
+func (c ListedClimb) Category() string {
+ return climb.CategoryOf(c.Cotacol).String()
+}
+
+// ListWithAggregates returns every official climb with aggregates over its
+// attempts. Attempts default to zero for climbs never ridden; climbs without
+// attempts show no distance/slope stats.
+func ListWithAggregates(db *sql.DB) ([]ListedClimb, error) {
+ climbRows, err := db.Query("SELECT id, name FROM official_climbs ORDER BY name ASC")
+ if err != nil {
+ return nil, fmt.Errorf("list climbs: %w", err)
+ }
+ defer climbRows.Close()
+ climbs := make([]ListedClimb, 0)
+ index := make(map[int64]int)
+ for climbRows.Next() {
+ var row ListedClimb
+ if err := climbRows.Scan(&row.ID, &row.Name); err != nil {
+ return nil, err
+ }
+ index[row.ID] = len(climbs)
+ climbs = append(climbs, row)
+ }
+ if err := climbRows.Err(); err != nil {
+ return nil, fmt.Errorf("iterate climbs: %w", err)
+ }
+
+ attemptRows, err := db.Query(`
+ SELECT climb_id, distance_m, elevation_gain_m, slope_percent, cotacol, started_at, ended_at
+ FROM ride_official_climb_attempts`)
+ if err != nil {
+ return nil, fmt.Errorf("list climb attempts: %w", err)
+ }
+ defer attemptRows.Close()
+ samples := make(map[int64]*attemptSamples)
+ for attemptRows.Next() {
+ var (
+ climbID int64
+ distanceM float64
+ gainM float64
+ slope float64
+ cotacol float64
+ startedAt string
+ endedAt string
+ )
+ if err := attemptRows.Scan(&climbID, &distanceM, &gainM, &slope, &cotacol, &startedAt, &endedAt); err != nil {
+ return nil, err
+ }
+ sample, known := samples[climbID]
+ if !known {
+ sample = &attemptSamples{}
+ samples[climbID] = sample
+ }
+ sample.distances = append(sample.distances, distanceM)
+ sample.gains = append(sample.gains, gainM)
+ sample.slopes = append(sample.slopes, slope)
+ sample.cotacols = append(sample.cotacols, cotacol)
+ start, err := time.Parse(time.RFC3339, startedAt)
+ if err != nil {
+ return nil, fmt.Errorf("invalid ride_official_climb_attempts.started_at: %w", err)
+ }
+ end, err := time.Parse(time.RFC3339, endedAt)
+ if err != nil {
+ return nil, fmt.Errorf("invalid ride_official_climb_attempts.ended_at: %w", err)
+ }
+ durationS := end.Sub(start).Seconds()
+ if durationS > 0 && (sample.bestDurationS == 0 || durationS < sample.bestDurationS) {
+ sample.bestDurationS = durationS
+ }
+ }
+ if err := attemptRows.Err(); err != nil {
+ return nil, fmt.Errorf("iterate climb attempts: %w", err)
+ }
+
+ for id, sample := range samples {
+ row := &climbs[index[id]]
+ row.Attempts = len(sample.distances)
+ row.DistanceM = Median(sample.distances)
+ row.ElevationGainM = Median(sample.gains)
+ row.SlopePercent = Median(sample.slopes)
+ row.Cotacol = Median(sample.cotacols)
+ if sample.bestDurationS > 0 {
+ row.BestDurationS = sample.bestDurationS
+ row.HasBest = true
+ }
+ }
+ return climbs, nil
+}
+
+type attemptSamples struct {
+ distances []float64
+ gains []float64
+ slopes []float64
+ cotacols []float64
+ bestDurationS float64
+}
diff --git a/official_climb/list_test.go b/official_climb/list_test.go
new file mode 100644
index 0000000..6a65f80
--- /dev/null
+++ b/official_climb/list_test.go
@@ -0,0 +1,52 @@
+package official_climb_test
+
+import (
+ "testing"
+
+ "github.com/martinlehoux/biking_home/internal/dbtest"
+ "github.com/martinlehoux/biking_home/official_climb"
+ "github.com/stretchr/testify/assert"
+ "github.com/stretchr/testify/require"
+)
+
+func TestListWithAggregatesMediansAndUnridden(t *testing.T) {
+ db := dbtest.New(t)
+ unridden, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col Fantome", StartCoord: coord(43.9, 5.9), EndCoord: coord(43.92, 5.92)})
+ require.NoError(t, err)
+ climbed, err := official_climb.Create(db, official_climb.OfficialClimb{Name: "Col Fréquente", StartCoord: coord(43.6, 5.4), EndCoord: coord(43.62, 5.42)})
+ require.NoError(t, err)
+ rideID := insertRide(t, db)
+
+ attempt := attemptFixture(rideID, climbed.ID, 0)
+ attempt.DistanceM = 3000
+ attempt.Cotacol = 80
+ medianAttempt := attempt
+ medianAttempt.Occurrence = 1
+ medianAttempt.DistanceM = 3200
+ medianAttempt.Cotacol = 84
+ outlier := attempt
+ outlier.Occurrence = 2
+ outlier.DistanceM = 9000
+ outlier.Cotacol = 500
+ require.NoError(t, official_climb.ReplaceRideAttempts(db, rideID, []official_climb.Attempt{attempt, medianAttempt, outlier}))
+
+ climbs, err := official_climb.ListWithAggregates(db)
+ require.NoError(t, err)
+ require.Len(t, climbs, 2)
+
+ byID := make(map[int64]official_climb.ListedClimb)
+ for _, climb := range climbs {
+ byID[climb.ID] = climb
+ }
+ frequente := byID[climbed.ID]
+ assert.Equal(t, 3, frequente.Attempts)
+ assert.Equal(t, 3200.0, frequente.DistanceM, "median ignores the 9 km outlier")
+ assert.Equal(t, 84.0, frequente.Cotacol)
+ assert.True(t, frequente.HasBest)
+ assert.Positive(t, frequente.BestDurationS)
+
+ fantome := byID[unridden.ID]
+ assert.Equal(t, 0, fantome.Attempts)
+ assert.Equal(t, 0.0, fantome.DistanceM)
+ assert.False(t, fantome.HasBest)
+}
generated by cgit v1.3 (git 2.53.0) at 2026-10-18 13:11:00 +0000