From eac1145e29e1a2a2ff5d9654ca010071a5840843 Mon Sep 17 00:00:00 2001 From: Martin Kagamino Lehoux Date: Fri, 14 Aug 2026 15:23:48 +0200 Subject: feat: add official climb workflow --- official_climb/official_climb.go | 184 +++++++++++++++++++++++++++++++++ official_climb/official_climb_test.go | 187 ++++++++++++++++++++++++++++++++++ 2 files changed, 371 insertions(+) create mode 100644 official_climb/official_climb.go create mode 100644 official_climb/official_climb_test.go (limited to 'official_climb') diff --git a/official_climb/official_climb.go b/official_climb/official_climb.go new file mode 100644 index 0000000..ab98633 --- /dev/null +++ b/official_climb/official_climb.go @@ -0,0 +1,184 @@ +package official_climb + +import ( + "database/sql" + "fmt" + "math" + "strings" + "time" + + "github.com/jftuga/geodist" + "github.com/martinlehoux/biking_home/ride" +) + +const DefaultMatchRadiusM = 100.0 + +type OfficialClimb struct { + ID int64 + Name string + StartCoord geodist.Coord + EndCoord geodist.Coord + CreatedAt time.Time + UpdatedAt time.Time +} + +type MatchPolicy struct { + EndpointRadiusM float64 +} + +func DefaultMatchPolicy() MatchPolicy { + return MatchPolicy{EndpointRadiusM: DefaultMatchRadiusM} +} + +func (policy MatchPolicy) Validate() error { + if math.IsNaN(policy.EndpointRadiusM) || math.IsInf(policy.EndpointRadiusM, 0) || policy.EndpointRadiusM <= 0 { + return fmt.Errorf("official climb endpoint radius must be greater than zero") + } + return nil +} + +func (climb OfficialClimb) validate() error { + if strings.TrimSpace(climb.Name) == "" { + return fmt.Errorf("official climb name is required") + } + if !validCoord(climb.StartCoord) || !validCoord(climb.EndCoord) { + return fmt.Errorf("official climb coordinates are invalid") + } + return nil +} + +func validCoord(coord geodist.Coord) bool { + return coord.Lat >= -90 && coord.Lat <= 90 && coord.Lon >= -180 && coord.Lon <= 180 +} + +const columns = "id, name, start_latitude, start_longitude, end_latitude, end_longitude, created_at, updated_at" + +func Create(db *sql.DB, climb OfficialClimb) (OfficialClimb, error) { + if err := climb.validate(); err != nil { + return OfficialClimb{}, err + } + result, err := db.Exec(` + INSERT INTO official_climbs (name, start_latitude, start_longitude, end_latitude, end_longitude) + VALUES (?, ?, ?, ?, ?) + `, climb.Name, climb.StartCoord.Lat, climb.StartCoord.Lon, climb.EndCoord.Lat, climb.EndCoord.Lon) + if err != nil { + return OfficialClimb{}, fmt.Errorf("create official climb: %w", err) + } + id, err := result.LastInsertId() + if err != nil { + return OfficialClimb{}, fmt.Errorf("read official climb id: %w", err) + } + created, found, err := GetByID(db, id) + if err != nil { + return OfficialClimb{}, err + } + if !found { + return OfficialClimb{}, fmt.Errorf("official climb %d was not found after creation", id) + } + return created, nil +} + +func List(db *sql.DB) ([]OfficialClimb, error) { + rows, err := db.Query("SELECT " + columns + " FROM official_climbs ORDER BY id") + if err != nil { + return nil, fmt.Errorf("list official climbs: %w", err) + } + defer rows.Close() + + climbs := make([]OfficialClimb, 0) + for rows.Next() { + climb, err := scan(rows) + if err != nil { + return nil, err + } + climbs = append(climbs, climb) + } + if err := rows.Err(); err != nil { + return nil, fmt.Errorf("iterate official climbs: %w", err) + } + return climbs, nil +} + +func GetByID(db *sql.DB, id int64) (OfficialClimb, bool, error) { + row := db.QueryRow("SELECT "+columns+" FROM official_climbs WHERE id = ?", id) + climb, err := scan(row) + if err == sql.ErrNoRows { + return OfficialClimb{}, false, nil + } + if err != nil { + return OfficialClimb{}, false, fmt.Errorf("get official climb %d: %w", id, err) + } + return climb, true, nil +} + +type scanner interface { + Scan(dest ...any) error +} + +func scan(s scanner) (OfficialClimb, error) { + var ( + climb OfficialClimb + startLatitude, startLongitude float64 + endLatitude, endLongitude float64 + createdAt, updatedAt string + ) + if err := s.Scan(&climb.ID, &climb.Name, &startLatitude, &startLongitude, &endLatitude, &endLongitude, &createdAt, &updatedAt); err != nil { + return OfficialClimb{}, err + } + climb.StartCoord = geodist.Coord{Lat: startLatitude, Lon: startLongitude} + climb.EndCoord = geodist.Coord{Lat: endLatitude, Lon: endLongitude} + var err error + climb.CreatedAt, err = time.Parse(time.RFC3339, createdAt) + if err != nil { + return OfficialClimb{}, fmt.Errorf("invalid official_climbs.created_at: %w", err) + } + climb.UpdatedAt, err = time.Parse(time.RFC3339, updatedAt) + if err != nil { + return OfficialClimb{}, fmt.Errorf("invalid official_climbs.updated_at: %w", err) + } + return climb, nil +} + +func MatchClimb(climb ride.Climb, officialClimbs []OfficialClimb, policy MatchPolicy) (OfficialClimb, bool) { + if policy.Validate() != nil { + return OfficialClimb{}, false + } + bestDistance := math.Inf(1) + var best OfficialClimb + found := false + for _, official := range officialClimbs { + startIndex, startDistance := nearestClimbPoint(climb, official.StartCoord) + endIndex, endDistance := nearestClimbPoint(climb, official.EndCoord) + if startDistance > policy.EndpointRadiusM || endDistance > policy.EndpointRadiusM { + continue + } + if startIndex >= endIndex { + continue + } + totalDistance := startDistance + endDistance + if totalDistance < bestDistance || (totalDistance == bestDistance && (!found || official.ID < best.ID)) { + best = official + bestDistance = totalDistance + found = true + } + } + return best, found +} + +func nearestClimbPoint(climb ride.Climb, target geodist.Coord) (int, float64) { + bestIndex := climb.StartIndex() + bestDistance := math.Inf(1) + for index := climb.StartIndex(); index <= climb.EndIndex(); index++ { + distance := distanceM(climb.PointCoord(index), target) + if distance < bestDistance { + bestIndex = index + bestDistance = distance + } + } + return bestIndex, bestDistance +} + +func distanceM(a, b geodist.Coord) float64 { + _, distanceKm := geodist.HaversineDistance(a, b) + return distanceKm * 1000 +} diff --git a/official_climb/official_climb_test.go b/official_climb/official_climb_test.go new file mode 100644 index 0000000..553c482 --- /dev/null +++ b/official_climb/official_climb_test.go @@ -0,0 +1,187 @@ +package official_climb_test + +import ( + "database/sql" + "testing" + "time" + + "github.com/jftuga/geodist" + "github.com/martinlehoux/biking_home/official_climb" + "github.com/martinlehoux/biking_home/ride" + _ "github.com/mattn/go-sqlite3" + "github.com/stretchr/testify/assert" + "github.com/stretchr/testify/require" +) + +func newTestDB(t *testing.T) *sql.DB { + t.Helper() + db, err := sql.Open("sqlite3", ":memory:") + require.NoError(t, err) + t.Cleanup(func() { require.NoError(t, db.Close()) }) + _, err = db.Exec(` + CREATE TABLE official_climbs ( + id INTEGER PRIMARY KEY, + name TEXT NOT NULL, + start_latitude REAL NOT NULL, + start_longitude REAL NOT NULL, + end_latitude REAL NOT NULL, + end_longitude REAL NOT NULL, + created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')), + updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')) + ) + `) + require.NoError(t, err) + return db +} + +func TestCreateListAndGetOfficialClimb(t *testing.T) { + db := newTestDB(t) + created, err := official_climb.Create(db, official_climb.OfficialClimb{ + Name: "Col de Test", + StartCoord: geodist.Coord{Lat: 43.1, Lon: 5.1}, + EndCoord: geodist.Coord{Lat: 43.2, Lon: 5.2}, + }) + require.NoError(t, err) + assert.Positive(t, created.ID) + assert.Equal(t, "Col de Test", created.Name) + assert.Equal(t, geodist.Coord{Lat: 43.1, Lon: 5.1}, created.StartCoord) + + listed, err := official_climb.List(db) + require.NoError(t, err) + require.Len(t, listed, 1) + assert.Equal(t, created.ID, listed[0].ID) + + got, found, err := official_climb.GetByID(db, created.ID) + require.NoError(t, err) + require.True(t, found) + assert.Equal(t, created.EndCoord, got.EndCoord) + assert.False(t, got.CreatedAt.IsZero()) + assert.False(t, got.UpdatedAt.IsZero()) +} + +func TestCreateRejectsInvalidOfficialClimb(t *testing.T) { + db := newTestDB(t) + _, err := official_climb.Create(db, official_climb.OfficialClimb{ + Name: " ", + StartCoord: geodist.Coord{Lat: 43.1, Lon: 5.1}, + EndCoord: geodist.Coord{Lat: 43.2, Lon: 5.2}, + }) + assert.EqualError(t, err, "official climb name is required") +} + +func TestMatchClimbUsesOrderedCoordinates(t *testing.T) { + climb := testClimb( + geodist.Coord{Lat: 43.1002, Lon: 5.1002}, + geodist.Coord{Lat: 43.2002, Lon: 5.2002}, + ) + match, found := official_climb.MatchClimb(climb, []official_climb.OfficialClimb{ + {Name: "Same direction", StartCoord: geodist.Coord{Lat: 43.1, Lon: 5.1}, EndCoord: geodist.Coord{Lat: 43.2, Lon: 5.2}}, + {Name: "Reverse direction", StartCoord: geodist.Coord{Lat: 43.2, Lon: 5.2}, EndCoord: geodist.Coord{Lat: 43.1, Lon: 5.1}}, + }, official_climb.DefaultMatchPolicy()) + + require.True(t, found) + assert.Equal(t, "Same direction", match.Name) +} + +func TestMatchClimbRejectsEndpointOutsideRadius(t *testing.T) { + climb := testClimb( + geodist.Coord{Lat: 43.1, Lon: 5.1}, + geodist.Coord{Lat: 43.3, Lon: 5.3}, + ) + _, found := official_climb.MatchClimb(climb, []official_climb.OfficialClimb{{ + Name: "Partial match", + StartCoord: geodist.Coord{Lat: 43.1, Lon: 5.1}, + EndCoord: geodist.Coord{Lat: 43.2, Lon: 5.2}, + }}, official_climb.DefaultMatchPolicy()) + assert.False(t, found) +} + +func TestMatchClimbFindsOfficialEndpointsInsideDetectedClimb(t *testing.T) { + parsed := ride.FromColumns( + []float64{0, 1000, 2000, 3000}, + []float64{100, 300, 500, 100}, + []geodist.Coord{ + {Lat: 43.1, Lon: 5.1}, + {Lat: 43.101, Lon: 5.101}, + {Lat: 43.102, Lon: 5.102}, + {Lat: 43.103, Lon: 5.103}, + }, + []time.Time{time.Unix(0, 0), time.Unix(60, 0), time.Unix(120, 0), time.Unix(180, 0)}, + ) + climb := parsed.ClimbFromIndexes(0, 3) + matched, found := official_climb.MatchClimb(climb, []official_climb.OfficialClimb{{ + Name: "Corrected boundaries", + StartCoord: parsed.Coord(1), + EndCoord: parsed.Coord(2), + }}, official_climb.DefaultMatchPolicy()) + + require.True(t, found) + assert.Equal(t, "Corrected boundaries", matched.Name) +} + +func TestMatchClimbMatchesSameOfficialClimbAcrossRides(t *testing.T) { + official := official_climb.OfficialClimb{ + Name: "Shared official climb", + StartCoord: geodist.Coord{Lat: 43.100, Lon: 5.100}, + EndCoord: geodist.Coord{Lat: 43.200, Lon: 5.200}, + } + firstRide := ride.FromColumns( + []float64{0, 1000, 2000, 3000}, + []float64{100, 300, 500, 100}, + []geodist.Coord{ + {Lat: 43.090, Lon: 5.090}, + official.StartCoord, + official.EndCoord, + {Lat: 43.210, Lon: 5.210}, + }, + []time.Time{time.Unix(0, 0), time.Unix(60, 0), time.Unix(120, 0), time.Unix(180, 0)}, + ) + secondRide := ride.FromColumns( + []float64{0, 750, 1500, 2250, 3000}, + []float64{100, 180, 300, 500, 100}, + []geodist.Coord{ + {Lat: 43.080, Lon: 5.080}, + {Lat: 43.095, Lon: 5.095}, + official.StartCoord, + official.EndCoord, + {Lat: 43.205, Lon: 5.205}, + }, + []time.Time{time.Unix(0, 0), time.Unix(45, 0), time.Unix(90, 0), time.Unix(135, 0), time.Unix(180, 0)}, + ) + + firstMatch, firstFound := official_climb.MatchClimb(firstRide.ClimbFromIndexes(0, 3), []official_climb.OfficialClimb{official}, official_climb.DefaultMatchPolicy()) + secondMatch, secondFound := official_climb.MatchClimb(secondRide.ClimbFromIndexes(1, 4), []official_climb.OfficialClimb{official}, official_climb.DefaultMatchPolicy()) + + require.True(t, firstFound) + require.True(t, secondFound) + assert.Equal(t, official.Name, firstMatch.Name) + assert.Equal(t, official.Name, secondMatch.Name) +} + +func TestMatchClimbChoosesNearestCandidate(t *testing.T) { + climb := testClimb( + geodist.Coord{Lat: 43.1, Lon: 5.1}, + geodist.Coord{Lat: 43.2, Lon: 5.2}, + ) + match, found := official_climb.MatchClimb(climb, []official_climb.OfficialClimb{ + {ID: 2, Name: "Farther", StartCoord: geodist.Coord{Lat: 43.1005, Lon: 5.1005}, EndCoord: geodist.Coord{Lat: 43.2005, Lon: 5.2005}}, + {ID: 1, Name: "Nearest", StartCoord: geodist.Coord{Lat: 43.1001, Lon: 5.1001}, EndCoord: geodist.Coord{Lat: 43.2001, Lon: 5.2001}}, + }, official_climb.DefaultMatchPolicy()) + + require.True(t, found) + assert.Equal(t, "Nearest", match.Name) +} + +func TestMatchPolicyRejectsInvalidRadius(t *testing.T) { + assert.EqualError(t, (official_climb.MatchPolicy{}).Validate(), "official climb endpoint radius must be greater than zero") +} + +func testClimb(start, end geodist.Coord) ride.Climb { + parsed := ride.FromColumns( + []float64{0, 1000, 2000}, + []float64{100, 150, 200}, + []geodist.Coord{{}, start, end}, + []time.Time{time.Unix(0, 0), time.Unix(60, 0), time.Unix(120, 0)}, + ) + return parsed.ClimbFromDist(1000, 2000) +} -- cgit v1.2.3