From 44f7333d9deef81d3468e20b378c63692a9406ec Mon Sep 17 00:00:00 2001 From: Martin Kagamino Lehoux Date: Fri, 21 Aug 2026 10:30:27 +0200 Subject: Use OSM as the single source of mountain passes Replace the centcols CSV download/enrichment pipeline with OSM PBF extraction as the primary (only) source: merge osmpass into mountain_pass, drop the centcols table and model fields, filter to named passes with elevation, and remove the centcols CLI flags. --- README.md | 48 +---- cli/cli.go | 26 +-- .../20260821000000_osm_only_mountain_passes.sql | 14 ++ db/schema.sql | 13 +- mountain_pass/detection.go | 24 ++- mountain_pass/detection_test.go | 36 +++- mountain_pass/downloader.go | 222 ------------------- mountain_pass/downloader_test.go | 75 ------- mountain_pass/extract.go | 98 +++++++++ mountain_pass/extract_test.go | 49 +++++ mountain_pass/fetch.go | 98 +++++++++ osmpass/enrich.go | 240 --------------------- osmpass/enrich_test.go | 149 ------------- osmpass/extract.go | 98 --------- osmpass/extract_test.go | 49 ----- osmpass/fetch.go | 98 --------- web/server_test.go | 8 +- 17 files changed, 326 insertions(+), 1019 deletions(-) create mode 100644 db/migrations/20260821000000_osm_only_mountain_passes.sql delete mode 100644 mountain_pass/downloader.go delete mode 100644 mountain_pass/downloader_test.go create mode 100644 mountain_pass/extract.go create mode 100644 mountain_pass/extract_test.go create mode 100644 mountain_pass/fetch.go delete mode 100644 osmpass/enrich.go delete mode 100644 osmpass/enrich_test.go delete mode 100644 osmpass/extract.go delete mode 100644 osmpass/extract_test.go delete mode 100644 osmpass/fetch.go diff --git a/README.md b/README.md index d4153a7..3790f62 100644 --- a/README.md +++ b/README.md @@ -1,6 +1,6 @@ # biking_home — cycling ride analysis -A Go toolkit for analyzing cycling rides from GPX exports: parse rides, detect climbs, score their difficulty, match the same climb across rides, and download French mountain passes into SQLite. +A Go toolkit for analyzing cycling rides from GPX exports: parse rides, detect climbs, score their difficulty, match the same climb across rides, and load French mountain passes from OpenStreetMap into SQLite. ## Features @@ -8,8 +8,8 @@ A Go toolkit for analyzing cycling rides from GPX exports: parse rides, detect c - **Climb detection** — automatically finds climbs within a ride, each scored and categorized (Cat 4 … HC) - **Difficulty score (Cotacol)** — scores any ride or climb by splitting it into 100 m segments and summing `0.1 km × slope²` per segment. This fixed step is a deliberate compromise: smaller steps increase sensitivity to elevation noise, while unconstrained variable steps maximize noise rather than meaningful difficulty. - **Similar-climb matching** — finds the same climb across rides by matching start/end coordinates, so times can be compared -- **Mountain pass download** — imports French mountain passes from centcols.org into a SQLite database -- **Pass crossing detection** — enriches passes with OSM coordinates, flags which passes a ride crosses, and names each climb after the pass it tops (e.g. "Col de Castellaras") +- **Mountain pass extraction** — extracts French mountain passes from an OpenStreetMap PBF export (nodes tagged `mountain_pass=yes` / `natural=mountain_pass`) into a SQLite database +- **Pass crossing detection** — flags which passes a ride crosses and names each climb after the pass it tops (e.g. "Col de Castellaras") - **Interactive ride profiles** — explores elevation, climbs, and mountain-pass crossings in the web ride detail view - **Official climb cards** — separates matched climbs from pending matches and expands each matched climb with its own elevation profile - **Web ride library** — starts a local web server by default, imports Strava rides over a date range, stores metadata in SQLite, and keeps their GPX files on disk @@ -33,40 +33,16 @@ mise run build ```bash # Start the web server on http://localhost:8080 mise run dev-watch - -# Recompute and persist all ride computed values -./biking_home -backfill - -# Run data benchmarks comparing computed values against stored references -./biking_home -backtest - -# Download French mountain passes into biking_home.db (SQLite) -./biking_home -download - -# Resume an interrupted download, reusing departments already cached on disk -./biking_home -download -resume - -# Import cached department CSVs (debug_department_06.csv, 13.csv) into the DB -./biking_home -import-cached - -# Extract mountain passes from a France OSM PBF into the DB -./biking_home -extract-osm /path/to/france-latest.osm.pbf - -# Backfill OSM coordinates onto passes (needs -extract-osm run first) -./biking_home -enrich - ``` ## Options / Configuration | Flag | Description | Default | | --- | --- | --- | -| `-download` | Download mountain passes into the database | `false` | -| `-resume` | Skip departments already cached on disk | `false` | -| `-import-cached` | Import cached department CSVs into the database | `false` | +| `-fetch-osm` | Download the France OSM PBF (resumable) | `false` | | `-extract-osm` | Extract mountain passes from a France OSM PBF | `""` | -| `-enrich` | Backfill OSM coordinates onto passes | `false` | | `-backfill` | Recompute and persist all ride computed values | `false` | +| `-backtest` | Benchmark computed vs stored ride values | `false` | Configuration is stored in `config.yaml`. Start from `config.example.yaml` and set the Strava credentials before launching the server. @@ -86,14 +62,12 @@ Standard apps are rate-limited to 100 calls per 15 minutes and 1,000 per day. - Go 1.23; SQLite via `mattn/go-sqlite3` - `ride` — GPX parsing, climb detection, difficulty scores (KOM + Cotacol), similarity index -- `mountain_pass` — centcols.org department CSV download into SQLite, with disk caching and retries -- `osmpass` — OSM PBF extraction (`mountain_pass=yes` nodes) and pass coordinate enrichment +- `mountain_pass` — OSM PBF extraction (`mountain_pass=yes` / `natural=mountain_pass` nodes), pass storage and querying, and crossings/climb matching - `strava` — OAuth2 client returning activity metadata and GPX data - `rides` — SQLite persistence for imported ride metadata and versioned computed values - `config` — typed YAML configuration and atomic persistence - `web` — HTTP server, OAuth callback, sync orchestration, and templ pages -- `cli` — legacy mountain-pass and OSM command handlers -- **Notable choices** — the difficulty score follows the Cotacol method: the ride is split into fixed 100 m segments and each scores `distance_km × slope²`, so steep sections weigh exponentially more than long flat ones +- `cli` — command handlers for data import, backfill, and benchmarking ```mermaid flowchart TB @@ -102,18 +76,16 @@ flowchart TB main["main (bootstrap: main.go)"] ride["ride (GPX parsing, climbs, Cotacol)"] - mpass["mountain_pass (centcols, crossings)"] - osmpass["osmpass (OSM PBF, enrichment)"] + mpass["mountain_pass (OSM PBF, crossings)"] strava["strava (OAuth, List, Get)"] rides["rides (SQLite persistence)"] config["config (YAML)"] web["web (HTTP + templ)"] - cli["cli (legacy commands)"] + cli["cli (import, backfill, benchmark)"] main --> cli cli --> ride cli --> mpass - cli --> osmpass cli --> web mpass --> ride web --> strava @@ -151,14 +123,12 @@ TypeScript checks can also be run directly with `npm run check`, covering format - Record ride times on official climbs - Destination via Start - Planning rides -- Mountain pass from OSM - performance/profile tracking - progress bar cli ## Resources - [Climbfinder: difficulty points](https://climbfinder.com/en/difficulty-points) — the Cotacol formula behind the difficulty score -- [centcols.org](https://www.centcols.org) — French mountain pass reference used for the SQLite data - [Pogacar 21 July 2022](https://www.strava.com/activities/7505784085) — example ride used for climb detection - [gpxgo](https://github.com/tkrajina/gpxgo) — GPX parsing library - [geodist](https://github.com/jftuga/geodist) — haversine distance used for climb matching diff --git a/cli/cli.go b/cli/cli.go index 3a04b8b..3f291bb 100644 --- a/cli/cli.go +++ b/cli/cli.go @@ -15,7 +15,6 @@ import ( "github.com/martinlehoux/biking_home/config" "github.com/martinlehoux/biking_home/mountain_pass" - "github.com/martinlehoux/biking_home/osmpass" "github.com/martinlehoux/biking_home/ride" "github.com/martinlehoux/biking_home/rides" "github.com/martinlehoux/biking_home/web" @@ -23,14 +22,10 @@ import ( ) var ( - download = flag.Bool("download", false, "download mountain passes into the database") - resume = flag.Bool("resume", false, "skip departments already cached on disk") - importCache = flag.Bool("import-cached", false, "import cached department CSVs into the database") - fetchOSM = flag.Bool("fetch-osm", false, "download the France OSM PBF (resumable) into france-latest.osm.pbf") - extractOSM = flag.String("extract-osm", "", "extract mountain passes from an OSM PBF file into the database") - enrich = flag.Bool("enrich", false, "backfill mountain pass coordinates from OSM data") - backfill = flag.Bool("backfill", false, "recompute all stored ride values") - backtest = flag.Bool("backtest", false, "run data benchmarks comparing computed values against stored reference values") + fetchOSM = flag.Bool("fetch-osm", false, "download the France OSM PBF (resumable) into france-latest.osm.pbf") + extractOSM = flag.String("extract-osm", "", "extract mountain passes from an OSM PBF file into the database") + backfill = flag.Bool("backfill", false, "recompute all stored ride values") + backtest = flag.Bool("backtest", false, "run data benchmarks comparing computed values against stored reference values") ) const ( @@ -40,21 +35,12 @@ const ( func Run(db *sql.DB, configPath string, appConfig config.Config) { switch { - case *download: - err := mountain_pass.DownloadMountainPasses(db, 5*time.Second, *resume) - kcore.Expect(err, "failed to download mountain passes") - case *importCache: - _, err := mountain_pass.ImportCachedDepartments(db, []string{"06", "13"}) - kcore.Expect(err, "failed to import cached departments") case *fetchOSM: - err := osmpass.FetchFrancePBF("france-latest.osm.pbf") + err := mountain_pass.FetchFrancePBF("france-latest.osm.pbf") kcore.Expect(err, "failed to download France OSM PBF") case *extractOSM != "": - _, err := osmpass.ExtractMountainPasses(context.Background(), *extractOSM, db) + _, err := mountain_pass.ExtractMountainPasses(context.Background(), *extractOSM, db) kcore.Expect(err, "failed to extract mountain passes from OSM") - case *enrich: - _, err := osmpass.EnrichMountainPasses(db) - kcore.Expect(err, "failed to enrich mountain passes") case *backfill: count, err := rides.Backfill(db) kcore.Expect(err, "failed to backfill ride values") diff --git a/db/migrations/20260821000000_osm_only_mountain_passes.sql b/db/migrations/20260821000000_osm_only_mountain_passes.sql new file mode 100644 index 0000000..ebeed55 --- /dev/null +++ b/db/migrations/20260821000000_osm_only_mountain_passes.sql @@ -0,0 +1,14 @@ +-- migrate:up +drop table mountain_passes; +alter table osm_passes rename to mountain_passes; + +-- migrate:down +alter table mountain_passes rename to osm_passes; +create table mountain_passes ( + id integer primary key, + external_id text unique not null, + name text not null, + country_code text not null, + department_code text not null, + elevation integer not null +); diff --git a/db/schema.sql b/db/schema.sql index 4c03183..60505bb 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -1,13 +1,5 @@ CREATE TABLE IF NOT EXISTS "schema_migrations" (version varchar(255) primary key); -CREATE TABLE mountain_passes ( - id integer primary key, - external_id text unique not null, - name text not null, - country_code text not null, - department_code text not null, - elevation integer not null -, latitude real, longitude real); -CREATE TABLE osm_passes ( +CREATE TABLE IF NOT EXISTS "mountain_passes" ( id integer primary key, osm_id integer unique not null, name text, @@ -47,4 +39,5 @@ INSERT INTO "schema_migrations" (version) VALUES ('20260804000000'), ('20260807000000'), ('20260813000000'), - ('20260820184307'); + ('20260820184307'), + ('20260821000000'); diff --git a/mountain_pass/detection.go b/mountain_pass/detection.go index 4c54493..b71837b 100644 --- a/mountain_pass/detection.go +++ b/mountain_pass/detection.go @@ -9,6 +9,15 @@ import ( "github.com/martinlehoux/biking_home/ride" ) +// MountainPass is a pass extracted from OpenStreetMap: a node tagged +// mountain_pass=yes (or legacy natural=mountain_pass) with a name and an +// elevation, as filtered by the loaders. +type MountainPass struct { + Name string + Elevation int + Coord *geodist.Coord +} + type Crossing struct { Pass MountainPass DistanceToM float64 @@ -55,12 +64,13 @@ func rideBounds(route ride.Ride, marginM float64) *coordinateBounds { func loadMountainPasses(db *sql.DB, bounds *coordinateBounds) ([]MountainPass, error) { query := ` - SELECT external_id, name, country_code, department_code, elevation, latitude, longitude - FROM mountain_passes` + SELECT name, elevation, latitude, longitude + FROM mountain_passes + WHERE elevation IS NOT NULL + AND name IS NOT NULL AND name != ''` args := make([]any, 0, 4) if bounds != nil { query += ` - WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND latitude BETWEEN ? AND ? AND longitude BETWEEN ? AND ?` args = append(args, bounds.minLatitude, bounds.maxLatitude, bounds.minLongitude, bounds.maxLongitude) @@ -76,13 +86,11 @@ func loadMountainPasses(db *sql.DB, bounds *coordinateBounds) ([]MountainPass, e mountainPasses := make([]MountainPass, 0) for rows.Next() { var mountainPass MountainPass - var latitude, longitude sql.NullFloat64 - if err := rows.Scan(&mountainPass.ExternalID, &mountainPass.Name, &mountainPass.CountryCode, &mountainPass.DepartmentCode, &mountainPass.Elevation, &latitude, &longitude); err != nil { + var latitude, longitude float64 + if err := rows.Scan(&mountainPass.Name, &mountainPass.Elevation, &latitude, &longitude); err != nil { return nil, err } - if latitude.Valid && longitude.Valid { - mountainPass.Coord = &geodist.Coord{Lat: latitude.Float64, Lon: longitude.Float64} - } + mountainPass.Coord = &geodist.Coord{Lat: latitude, Lon: longitude} mountainPasses = append(mountainPasses, mountainPass) } return mountainPasses, rows.Err() diff --git a/mountain_pass/detection_test.go b/mountain_pass/detection_test.go index d86e67b..f55532b 100644 --- a/mountain_pass/detection_test.go +++ b/mountain_pass/detection_test.go @@ -28,14 +28,15 @@ func TestDetectCrossingsFindsPass(t *testing.T) { func TestLoadMountainPassesAroundRideUsesExpandedBounds(t *testing.T) { db := dbtest.New(t) _, err := db.Exec(` - INSERT INTO mountain_passes (external_id, name, country_code, department_code, elevation, latitude, longitude) - VALUES (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?) + INSERT INTO mountain_passes (osm_id, name, elevation, latitude, longitude) + VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?) `, - "550e8400-e29b-41d4-a716-446655440000", "On route", "FR", "13", 400, 43.05, 5.05, - "6ba7b810-9dad-41d1-80b4-00c04fd430c8", "Within margin", "FR", "13", 500, 43.18, 5.20, - "6ba7b811-9dad-41d1-80b4-00c04fd430c8", "Outside margin", "FR", "13", 600, 43.21, 5.20, - "6ba7b812-9dad-41d1-80b4-00c04fd430c8", "Missing coordinates", "FR", "13", 700, nil, nil, - "6ba7b813-9dad-41d1-80b4-00c04fd430c8", "On route edge", "FR", "13", 300, 43.10, 5.10, + 1, "On route", 400, 43.05, 5.05, + 2, "Within margin", 500, 43.18, 5.20, + 3, "Outside margin", 600, 43.21, 5.20, + 4, "No elevation", nil, 43.19, 5.20, + 5, "", 700, 43.17, 5.20, + 6, "On route edge", 300, 43.10, 5.10, ) require.NoError(t, err) @@ -53,6 +54,27 @@ func TestLoadMountainPassesAroundRideUsesExpandedBounds(t *testing.T) { assert.Contains(t, []string{passes[1].Name, passes[2].Name}, "Within margin") } +func TestLoadMountainPassesFiltersElevationAndName(t *testing.T) { + db := dbtest.New(t) + _, err := db.Exec(` + INSERT INTO mountain_passes (osm_id, name, elevation, latitude, longitude) + VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?) + `, + 1, "Named pass", 400, 43.05, 5.05, + 2, "No elevation", nil, 43.06, 5.06, + 3, "", 300, 43.07, 5.07, + 4, "No name or elevation", nil, 43.08, 5.08, + ) + require.NoError(t, err) + + passes, err := mountain_pass.LoadMountainPasses(db) + require.NoError(t, err) + require.Len(t, passes, 1) + assert.Equal(t, "Named pass", passes[0].Name) + assert.Equal(t, 400, passes[0].Elevation) + assert.NotNil(t, passes[0].Coord) +} + func TestDetectCrossingsIgnoresDistantPass(t *testing.T) { r := rideFromPoint(t, 43.62, 5.43, 447) far := mountain_pass.MountainPass{ diff --git a/mountain_pass/downloader.go b/mountain_pass/downloader.go deleted file mode 100644 index 00ff586..0000000 --- a/mountain_pass/downloader.go +++ /dev/null @@ -1,222 +0,0 @@ -package mountain_pass - -import ( - "bytes" - "database/sql" - "encoding/csv" - "errors" - "fmt" - "io" - "log/slog" - "net/http" - "os" - "strconv" - "strings" - "time" - - "github.com/jftuga/geodist" - "github.com/martinlehoux/kagamigo/kcore" - "github.com/schollz/progressbar/v3" -) - -type MountainPass struct { - Name string - ExternalID string - CountryCode string - DepartmentCode string - Elevation int - Coord *geodist.Coord -} - -func parseMountainPasses(reader io.Reader) ([]MountainPass, error) { - mountainPasses := make([]MountainPass, 0) - csvReader := csv.NewReader(reader) - csvReader.Comma = '\t' - csvReader.LazyQuotes = true - rows, err := csvReader.ReadAll() - if err != nil { - return nil, kcore.Wrap(err, "Failed to read mountain pass CSV data") - } - rows = rows[1:] - for _, row := range rows { - if len(row) < 4 { - return nil, fmt.Errorf("unexpected row with %d fields: %v", len(row), row) - } - countryCode, departmentCode, err := splitCountryDepartment(row[0]) - if err != nil { - return nil, err - } - codes := strings.Split(row[1], " | ") - names := strings.Split(row[2], " | ") - altitudes := strings.Split(row[3], " | ") - if len(codes) != len(names) || len(codes) != len(altitudes) { - return nil, fmt.Errorf("inconsistent multi-pass row: %v", row) - } - for i := range codes { - elevation, err := strconv.Atoi(altitudes[i]) - if err != nil { - return nil, fmt.Errorf("failed to parse elevation %q: %w", altitudes[i], err) - } - mountainPasses = append(mountainPasses, MountainPass{ - ExternalID: "centcols/" + codes[i], - CountryCode: countryCode, - Name: names[i], - DepartmentCode: departmentCode, - Elevation: elevation, - }) - } - } - - return mountainPasses, nil -} - -func splitCountryDepartment(brevet string) (string, string, error) { - parts := strings.Split(brevet, "-") - if len(parts) != 2 || parts[0] == "" || parts[1] == "" { - return "", "", fmt.Errorf("unexpected brevet code: %q", brevet) - } - return parts[0], parts[1], nil -} - -func getCSVData(departmentCode string) ([]byte, error) { - url := "https://www.centcols.org/membres/cols/csvbrevet.php?id=FR" + departmentCode + "&enc=UTF-8" - res, err := http.Get(url) - if err != nil { - return nil, err - } - defer res.Body.Close() - if res.StatusCode != http.StatusOK { - return nil, statusError{statusCode: res.StatusCode} - } - return io.ReadAll(res.Body) -} - -type statusError struct { - statusCode int -} - -func (e statusError) Error() string { - return fmt.Sprintf("unexpected status code: %d", e.statusCode) -} - -const downloadRetries = 3 - -func departmentCacheFile(departmentCode string) string { - return fmt.Sprintf("debug_department_%s.csv", departmentCode) -} - -func loadCachedDepartment(filename string) ([]MountainPass, bool, error) { - data, err := os.ReadFile(filename) - if err != nil { - if errors.Is(err, os.ErrNotExist) { - return nil, false, nil - } - return nil, false, err - } - mountainPasses, err := parseMountainPasses(bytes.NewReader(data)) - if err != nil { - return nil, false, nil - } - return mountainPasses, true, nil -} - -func downloadDepartment(departmentCode string, resume bool) ([]MountainPass, error) { - filename := departmentCacheFile(departmentCode) - if resume { - mountainPasses, found, err := loadCachedDepartment(filename) - if err != nil { - return nil, err - } - if found { - return mountainPasses, nil - } - } - var lastErr error - backoff := time.Second - for attempt := 1; attempt <= downloadRetries; attempt++ { - data, err := getCSVData(departmentCode) - if err != nil { - lastErr = err - var statusErr statusError - if errors.As(err, &statusErr) && statusErr.statusCode == http.StatusTooManyRequests { - backoff = time.Minute - } - slog.Warn("Retrying department download", "department", departmentCode, "attempt", attempt, "error", err) - time.Sleep(backoff) - backoff *= 2 - continue - } - mountainPasses, err := parseMountainPasses(bytes.NewReader(data)) - if err != nil { - if writeErr := os.WriteFile(filename, data, 0o644); writeErr != nil { - return nil, kcore.Wrap(writeErr, "Failed to write dump file") - } - return nil, err - } - if writeErr := os.WriteFile(filename, data, 0o644); writeErr != nil { - return nil, kcore.Wrap(writeErr, "Failed to write dump file") - } - return mountainPasses, nil - } - return nil, lastErr -} - -func upsertMountainPasses(db *sql.DB, mountainPasses []MountainPass) error { - tx, err := db.Begin() - kcore.Expect(err, "Failed to begin transaction") - defer tx.Rollback() - stmt, err := tx.Prepare(` - INSERT INTO mountain_passes (external_id, name, country_code, department_code, elevation) - VALUES (?, ?, ?, ?, ?) - ON CONFLICT(external_id) DO UPDATE SET - name = excluded.name, - country_code = excluded.country_code, - department_code = excluded.department_code, - elevation = excluded.elevation - `) - kcore.Expect(err, "Failed to prepare statement") - defer stmt.Close() - - for _, mountainPass := range mountainPasses { - _, err := stmt.Exec(mountainPass.ExternalID, mountainPass.Name, mountainPass.CountryCode, mountainPass.DepartmentCode, mountainPass.Elevation) - kcore.Expect(err, "Failed to execute statement") - } - - return tx.Commit() -} - -func DownloadMountainPasses(db *sql.DB, delay time.Duration, resume bool) error { - mountainPasses := make([]MountainPass, 0) - bar := progressbar.Default(90) - ticker := time.NewTicker(delay) - for i := 1; i <= 90; i++ { - <-ticker.C - bar.Add(1) - departmentCode := fmt.Sprintf("%02d", i) - bar.Describe(fmt.Sprintf("Downloading mountain passes for department_code=%s", departmentCode)) - departmentMountainPasses, err := downloadDepartment(departmentCode, resume) - if err != nil { - return kcore.Wrap(err, "Failed to download mountain passes for department "+departmentCode) - } - mountainPasses = append(mountainPasses, departmentMountainPasses...) - } - - return upsertMountainPasses(db, mountainPasses) -} - -func ImportCachedDepartments(db *sql.DB, departmentCodes []string) (int, error) { - mountainPasses := make([]MountainPass, 0) - for _, departmentCode := range departmentCodes { - departmentMountainPasses, found, err := loadCachedDepartment(departmentCacheFile(departmentCode)) - if err != nil { - return 0, err - } - if !found { - slog.Warn("No cached department file", "department", departmentCode) - continue - } - slog.Info("Loaded cached department", "department", departmentCode, "passes", len(departmentMountainPasses)) - mountainPasses = append(mountainPasses, departmentMountainPasses...) - } - return len(mountainPasses), upsertMountainPasses(db, mountainPasses) -} diff --git a/mountain_pass/downloader_test.go b/mountain_pass/downloader_test.go deleted file mode 100644 index f7f5dda..0000000 --- a/mountain_pass/downloader_test.go +++ /dev/null @@ -1,75 +0,0 @@ -package mountain_pass - -import ( - "os" - "path/filepath" - "strings" - "testing" - - "github.com/stretchr/testify/assert" - "github.com/stretchr/testify/require" -) - -func TestParseMountainPasses(t *testing.T) { - csv := "Brevet\tcode\tnom\taltitude\n" + - "FR-01\tFR-01-1500\tCol du Colombier\t1498\n" + - "FR-06\tFR-06-0730c | FR-06-0706\tCol de Castillon | Col de Castillon (Tunnel)\t730 | 706\n" + - "FR-13\tFR-13-0135\tCol du Télégraphe \"Chappe\"\t149\n" + - "FR-90\tFR-88-1171\tCol du Ballon d'Alsace\t1171\n" - mountainPasses, err := parseMountainPasses(strings.NewReader(csv)) - require.NoError(t, err) - - expected := []MountainPass{ - {ExternalID: "centcols/FR-01-1500", CountryCode: "FR", DepartmentCode: "01", Name: "Col du Colombier", Elevation: 1498}, - {ExternalID: "centcols/FR-06-0730c", CountryCode: "FR", DepartmentCode: "06", Name: "Col de Castillon", Elevation: 730}, - {ExternalID: "centcols/FR-06-0706", CountryCode: "FR", DepartmentCode: "06", Name: "Col de Castillon (Tunnel)", Elevation: 706}, - {ExternalID: "centcols/FR-13-0135", CountryCode: "FR", DepartmentCode: "13", Name: "Col du Télégraphe \"Chappe\"", Elevation: 149}, - {ExternalID: "centcols/FR-88-1171", CountryCode: "FR", DepartmentCode: "90", Name: "Col du Ballon d'Alsace", Elevation: 1171}, - } - assert.Equal(t, expected, mountainPasses) -} - -func TestParseMountainPassesHeaderOnly(t *testing.T) { - mountainPasses, err := parseMountainPasses(strings.NewReader("Brevet\tcode\tnom\taltitude\n")) - require.NoError(t, err) - assert.Empty(t, mountainPasses) -} - -func TestSplitCountryDepartment(t *testing.T) { - country, department, err := splitCountryDepartment("FR-01") - require.NoError(t, err) - assert.Equal(t, "FR", country) - assert.Equal(t, "01", department) - - _, _, err = splitCountryDepartment("F-01-2") - assert.Error(t, err) -} - -func TestLoadCachedDepartment(t *testing.T) { - cacheFile := filepath.Join(t.TempDir(), "debug_department_01.csv") - err := os.WriteFile(cacheFile, []byte("Brevet\tcode\tnom\taltitude\nFR-01\tFR-01-1500\tCol du Colombier\t1498\n"), 0o644) - require.NoError(t, err) - - mountainPasses, found, err := loadCachedDepartment(cacheFile) - require.NoError(t, err) - assert.True(t, found) - assert.Equal(t, []MountainPass{{ExternalID: "centcols/FR-01-1500", CountryCode: "FR", DepartmentCode: "01", Name: "Col du Colombier", Elevation: 1498}}, mountainPasses) -} - -func TestLoadCachedDepartmentMissing(t *testing.T) { - mountainPasses, found, err := loadCachedDepartment(filepath.Join(t.TempDir(), "does-not-exist.csv")) - require.NoError(t, err) - assert.False(t, found) - assert.Nil(t, mountainPasses) -} - -func TestLoadCachedDepartmentCorrupt(t *testing.T) { - cacheFile := filepath.Join(t.TempDir(), "debug_department_01.csv") - err := os.WriteFile(cacheFile, []byte("Brevet\tcode\tnom\taltitude\nFR-01\n"), 0o644) - require.NoError(t, err) - - mountainPasses, found, err := loadCachedDepartment(cacheFile) - require.NoError(t, err) - assert.False(t, found) - assert.Nil(t, mountainPasses) -} diff --git a/mountain_pass/extract.go b/mountain_pass/extract.go new file mode 100644 index 0000000..81bed68 --- /dev/null +++ b/mountain_pass/extract.go @@ -0,0 +1,98 @@ +package mountain_pass + +import ( + "context" + "database/sql" + "fmt" + "io" + "log/slog" + "os" + "strconv" + "strings" + + "github.com/paulmach/osm" + "github.com/paulmach/osm/osmpbf" +) + +const insertStatement = ` + INSERT INTO mountain_passes (osm_id, name, elevation, latitude, longitude) + VALUES (?, ?, ?, ?, ?) + ON CONFLICT(osm_id) DO UPDATE SET + name = excluded.name, + elevation = excluded.elevation, + latitude = excluded.latitude, + longitude = excluded.longitude +` + +func ExtractMountainPasses(ctx context.Context, pbfPath string, db *sql.DB) (int, error) { + file, err := os.Open(pbfPath) + if err != nil { + return 0, fmt.Errorf("failed to open pbf file: %w", err) + } + defer file.Close() + + scanner := osmpbf.New(ctx, file, 4) + scanner.SkipWays = true + scanner.SkipRelations = true + scanner.FilterNode = isMountainPass + + tx, err := db.BeginTx(ctx, nil) + if err != nil { + return 0, fmt.Errorf("failed to begin transaction: %w", err) + } + defer tx.Rollback() + + stmt, err := tx.PrepareContext(ctx, insertStatement) + if err != nil { + return 0, fmt.Errorf("failed to prepare insert: %w", err) + } + defer stmt.Close() + + count := 0 + for scanner.Scan() { + node, ok := scanner.Object().(*osm.Node) + if !ok { + continue + } + elevation, hasElevation := parseElevation(node.Tags.Find("ele")) + var elevationValue any + if hasElevation { + elevationValue = elevation + } + if _, err := stmt.ExecContext(ctx, node.ID, node.Tags.Find("name"), elevationValue, node.Lat, node.Lon); err != nil { + return count, fmt.Errorf("failed to insert osm pass %d: %w", node.ID, err) + } + count++ + } + if err := scanner.Err(); err != nil && err != io.EOF { + return count, fmt.Errorf("failed to scan pbf: %w", err) + } + if err := tx.Commit(); err != nil { + return count, fmt.Errorf("failed to commit transaction: %w", err) + } + slog.Info("Extracted mountain passes", "count", count, "source", pbfPath) + return count, nil +} + +func isMountainPass(node *osm.Node) bool { + if node.Tags.Find("mountain_pass") == "yes" { + return true + } + return node.Tags.Find("natural") == "mountain_pass" +} + +func parseElevation(ele string) (int, bool) { + ele = strings.TrimSpace(ele) + if ele == "" { + return 0, false + } + for _, candidate := range strings.Fields(ele) { + if value, err := strconv.Atoi(candidate); err == nil { + return value, true + } + if value, err := strconv.ParseFloat(candidate, 64); err == nil { + return int(value), true + } + } + return 0, false +} diff --git a/mountain_pass/extract_test.go b/mountain_pass/extract_test.go new file mode 100644 index 0000000..636372c --- /dev/null +++ b/mountain_pass/extract_test.go @@ -0,0 +1,49 @@ +package mountain_pass + +import ( + "testing" + + "github.com/paulmach/osm" + "github.com/stretchr/testify/assert" +) + +func TestIsMountainPass(t *testing.T) { + cases := []struct { + name string + tags map[string]string + want bool + }{ + {"tagged", map[string]string{"mountain_pass": "yes", "name": "Col de la Gineste"}, true}, + {"legacy natural", map[string]string{"natural": "mountain_pass"}, true}, + {"not a pass", map[string]string{"natural": "saddle", "name": "Col de la Gineste"}, false}, + {"no tags", map[string]string{}, false}, + } + for _, c := range cases { + t.Run(c.name, func(t *testing.T) { + node := &osm.Node{Tags: osm.Tags{}} + for key, value := range c.tags { + node.Tags = append(node.Tags, osm.Tag{Key: key, Value: value}) + } + assert.Equal(t, c.want, isMountainPass(node)) + }) + } +} + +func TestParseElevation(t *testing.T) { + cases := []struct { + input string + want int + ok bool + }{ + {"1320", 1320, true}, + {"1320 m", 1320, true}, + {" 447.5", 447, true}, + {"", 0, false}, + {"unknown", 0, false}, + } + for _, c := range cases { + value, ok := parseElevation(c.input) + assert.Equal(t, c.want, value) + assert.Equal(t, c.ok, ok) + } +} diff --git a/mountain_pass/fetch.go b/mountain_pass/fetch.go new file mode 100644 index 0000000..4be2d87 --- /dev/null +++ b/mountain_pass/fetch.go @@ -0,0 +1,98 @@ +package mountain_pass + +import ( + "errors" + "fmt" + "io" + "log/slog" + "net/http" + "os" + + "github.com/schollz/progressbar/v3" +) + +const francePBFRUL = "https://download.geofabrik.de/europe/france-latest.osm.pbf" + +// FetchFrancePBF downloads the full France OSM PBF to destPath, resuming a +// partial download when the file already exists and skipping the download +// entirely when it is already complete. +func FetchFrancePBF(destPath string) error { + client := &http.Client{} + + head, err := client.Head(francePBFRUL) + if err != nil { + return fmt.Errorf("failed to check remote size: %w", err) + } + head.Body.Close() + total := head.ContentLength + if total <= 0 { + return fmt.Errorf("unexpected remote content length: %d", total) + } + + info, statErr := os.Stat(destPath) + var offset int64 + if statErr == nil { + offset = info.Size() + if offset >= total { + slog.Info("Already downloaded", "file", destPath, "bytes", offset) + return nil + } + if offset > 0 { + slog.Info("Resuming download", "file", destPath, "bytes", offset, "remaining", total-offset) + } + } else if !errors.Is(statErr, os.ErrNotExist) { + return fmt.Errorf("failed to stat %s: %w", destPath, statErr) + } + + req, err := http.NewRequest(http.MethodGet, francePBFRUL, nil) + if err != nil { + return err + } + req.Header.Set("User-Agent", "biking_home") + if offset > 0 { + req.Header.Set("Range", fmt.Sprintf("bytes=%d-", offset)) + } + + res, err := client.Do(req) + if err != nil { + return err + } + defer res.Body.Close() + + resuming := offset > 0 && res.StatusCode == http.StatusPartialContent + if !resuming { + if res.StatusCode != http.StatusOK { + return fmt.Errorf("unexpected status code: %d", res.StatusCode) + } + if offset > 0 { + slog.Warn("Server ignored Range header, restarting download", "file", destPath) + } + offset = 0 + } + + mode := os.O_CREATE | os.O_WRONLY | os.O_TRUNC + if resuming { + mode = os.O_CREATE | os.O_WRONLY + } + file, err := os.OpenFile(destPath, mode, 0o644) + if err != nil { + return err + } + defer file.Close() + + bar := progressbar.DefaultBytes(res.ContentLength, "Downloading france-latest.osm.pbf") + if resuming { + // Re-append to the partial file when the server honored the range request. + if _, err := file.Seek(offset, io.SeekStart); err != nil { + return err + } + } + _, err = io.Copy(io.MultiWriter(file, bar), res.Body) + if err != nil { + return fmt.Errorf("failed to download %s: %w", francePBFRUL, err) + } + if size, err := file.Stat(); err == nil && size.Size() != total { + return fmt.Errorf("download incomplete: got %d bytes, want %d", size.Size(), total) + } + return nil +} diff --git a/osmpass/enrich.go b/osmpass/enrich.go deleted file mode 100644 index 3af0067..0000000 --- a/osmpass/enrich.go +++ /dev/null @@ -1,240 +0,0 @@ -package osmpass - -import ( - "database/sql" - "fmt" - "log/slog" - "strings" - "unicode" - - "golang.org/x/text/unicode/norm" -) - -const elevationToleranceM = 25 - -type osmPass struct { - Name string - Elevation *int - Latitude float64 - Longitude float64 -} - -func EnrichMountainPasses(db *sql.DB) (int, error) { - osmPasses, err := loadOSMPasses(db) - if err != nil { - return 0, err - } - rows, err := db.Query(` - SELECT external_id, name, department_code, elevation - FROM mountain_passes - WHERE latitude IS NULL - `) - if err != nil { - return 0, err - } - - unmatched := make([]struct { - ExternalID string - Name string - DepartmentCode string - Elevation int - }, 0) - for rows.Next() { - var candidate struct { - ExternalID string - Name string - DepartmentCode string - Elevation int - } - if err := rows.Scan(&candidate.ExternalID, &candidate.Name, &candidate.DepartmentCode, &candidate.Elevation); err != nil { - rows.Close() - return 0, err - } - unmatched = append(unmatched, candidate) - } - if err := rows.Err(); err != nil { - rows.Close() - return 0, err - } - rows.Close() - - statement, err := db.Prepare(` - UPDATE mountain_passes SET latitude = ?, longitude = ? WHERE external_id = ? - `) - if err != nil { - return 0, err - } - defer statement.Close() - - count := 0 - for _, candidate := range unmatched { - matched := matchToOSM(candidate.Name, candidate.DepartmentCode, candidate.Elevation, osmPasses) - if matched == nil { - continue - } - if _, err := statement.Exec(matched.Latitude, matched.Longitude, candidate.ExternalID); err != nil { - return count, err - } - count++ - } - slog.Info("Enriched mountain passes with coordinates", "count", count) - return count, nil -} - -func loadOSMPasses(db *sql.DB) ([]osmPass, error) { - rows, err := db.Query(` - SELECT name, elevation, latitude, longitude - FROM osm_passes - WHERE elevation IS NOT NULL - `) - if err != nil { - return nil, err - } - defer rows.Close() - - osmPasses := make([]osmPass, 0) - for rows.Next() { - var mountainPass osmPass - var name sql.NullString - var elevation sql.NullInt64 - if err := rows.Scan(&name, &elevation, &mountainPass.Latitude, &mountainPass.Longitude); err != nil { - return nil, err - } - mountainPass.Name = name.String - if elevation.Valid { - elevationValue := int(elevation.Int64) - mountainPass.Elevation = &elevationValue - } - osmPasses = append(osmPasses, mountainPass) - } - return osmPasses, rows.Err() -} - -func matchToOSM(name, departmentCode string, elevation int, osmPasses []osmPass) *osmPass { - bbox := departmentBBox(departmentCode) - var best *osmPass - bestNameMatch := false - bestElevationDiff := 0.0 - for i := range osmPasses { - candidate := &osmPasses[i] - if candidate.Elevation == nil { - continue - } - if !bbox.Contains(candidate.Latitude, candidate.Longitude) { - continue - } - elevationDiff := absFloat64(float64(elevation) - float64(*candidate.Elevation)) - if elevationDiff > elevationToleranceM { - continue - } - nameMatch := nameMatches(name, candidate.Name) - if best == nil || - (nameMatch && !bestNameMatch) || - (nameMatch == bestNameMatch && elevationDiff < bestElevationDiff) { - best = candidate - bestNameMatch = nameMatch - bestElevationDiff = elevationDiff - } - } - if best == nil { - return nil - } - if bestNameMatch { - return best - } - for i := range osmPasses { - candidate := &osmPasses[i] - if candidate == best || candidate.Elevation == nil { - continue - } - if !bbox.Contains(candidate.Latitude, candidate.Longitude) { - continue - } - if absFloat64(float64(elevation)-float64(*candidate.Elevation)) <= bestElevationDiff+30 { - return nil - } - } - return best -} - -type bbox struct { - minLat, minLon, maxLat, maxLon float64 -} - -func (b bbox) Contains(latitude, longitude float64) bool { - return latitude >= b.minLat && latitude <= b.maxLat && - longitude >= b.minLon && longitude <= b.maxLon -} - -func departmentBBox(departmentCode string) bbox { - switch departmentCode { - case "01": - return bbox{minLat: 45.5, minLon: 4.7, maxLat: 46.6, maxLon: 6.3} - case "06": - return bbox{minLat: 43.5, minLon: 6.4, maxLat: 44.5, maxLon: 7.8} - case "13": - return bbox{minLat: 43.1, minLon: 4.4, maxLat: 44.0, maxLon: 6.0} - default: - return bbox{minLat: 41.0, minLon: -6.0, maxLat: 52.0, maxLon: 10.0} - } -} - -func nameMatches(centName, osmName string) bool { - cent := normalizeName(centName) - osm := normalizeName(osmName) - if cent == osm { - return true - } - centTokens := tokenize(cent) - if len(centTokens) < 3 { - return false - } - osmTokens := make(map[string]bool) - for _, token := range tokenize(osm) { - osmTokens[token] = true - } - for _, token := range centTokens { - if !osmTokens[token] { - return false - } - } - return true -} - -func tokenize(name string) []string { - return strings.Fields(name) -} - -func normalizeName(name string) string { - name = strings.ToLower(name) - name = norm.NFD.String(name) - normalized := strings.Builder{} - previousSpace := false - for _, r := range name { - if unicode.Is(unicode.Mn, r) { - continue - } - if unicode.IsLetter(r) || unicode.IsDigit(r) { - normalized.WriteRune(r) - previousSpace = false - } else if !previousSpace { - normalized.WriteRune(' ') - previousSpace = true - } - } - return strings.TrimSpace(normalized.String()) -} - -func absFloat64(value float64) float64 { - if value < 0 { - return -value - } - return value -} - -func (p osmPass) String() string { - if p.Elevation == nil { - return fmt.Sprintf("%s @ unknown elevation", p.Name) - } - return fmt.Sprintf("%s @ %dm", p.Name, *p.Elevation) -} diff --git a/osmpass/enrich_test.go b/osmpass/enrich_test.go deleted file mode 100644 index 88c6a3f..0000000 --- a/osmpass/enrich_test.go +++ /dev/null @@ -1,149 +0,0 @@ -package osmpass - -import ( - "database/sql" - "testing" - - _ "github.com/mattn/go-sqlite3" - "github.com/stretchr/testify/assert" - "github.com/stretchr/testify/require" -) - -func TestNormalizeName(t *testing.T) { - assert.Equal(t, "col du telegraphe", normalizeName("Col du Télégraphe")) - assert.Equal(t, "col de la gineste", normalizeName("Col de la Gineste")) - assert.Equal(t, normalizeName("Col de la Gineste"), normalizeName("col de la gineste")) - assert.True(t, nameMatches("Col de la Lombarde", "Col de la Lombarde / Colle della Lombarda")) - assert.False(t, nameMatches("Col de la Gatasse", "Col de la Gatasso")) -} - -func TestEnrichMountainPasses(t *testing.T) { - db := testDB(t, ` - CREATE TABLE osm_passes ( - osm_id integer unique not null, - name text, - elevation integer, - latitude real not null, - longitude real not null - ); - CREATE TABLE mountain_passes ( - external_id text unique not null, - name text not null, - department_code text not null, - elevation integer not null, - latitude real, - longitude real - ); - `) - - insert(t, db, "INSERT INTO osm_passes VALUES (1, 'Col de la Gineste', 327, 43.2, 5.4)") - insert(t, db, "INSERT INTO osm_passes VALUES (2, 'Col de la Couillole', 1678, 44.1, 7.0)") - insert(t, db, "INSERT INTO osm_passes VALUES (3, 'Col de la Gatasso', 122, 43.3, 5.5)") - insert(t, db, "INSERT INTO osm_passes VALUES (4, 'Col des Portes', NULL, 43.6, 5.8)") - - insert(t, db, "INSERT INTO mountain_passes VALUES ('c/1', 'Col de la Gineste', '13', 326, NULL, NULL)") - insert(t, db, "INSERT INTO mountain_passes VALUES ('c/2', 'Col de la Gatasse', '13', 120, NULL, NULL)") - insert(t, db, "INSERT INTO mountain_passes VALUES ('c/3', 'Col inconnu', '13', 999, NULL, NULL)") - - count, err := EnrichMountainPasses(db) - require.NoError(t, err) - assert.Equal(t, 2, count) - - var latitude, longitude float64 - err = db.QueryRow("SELECT latitude, longitude FROM mountain_passes WHERE external_id = 'c/1'").Scan(&latitude, &longitude) - require.NoError(t, err) - assert.InDelta(t, 43.2, latitude, 1e-9) - assert.InDelta(t, 5.4, longitude, 1e-9) - - err = db.QueryRow("SELECT latitude, longitude FROM mountain_passes WHERE external_id = 'c/2'").Scan(&latitude, &longitude) - require.NoError(t, err) - assert.InDelta(t, 43.3, latitude, 1e-9) - assert.InDelta(t, 5.5, longitude, 1e-9) - - var isNull bool - err = db.QueryRow("SELECT latitude IS NULL FROM mountain_passes WHERE external_id = 'c/3'").Scan(&isNull) - require.NoError(t, err) - assert.True(t, isNull) -} - -func TestEnrichMountainPassesAmbiguousElevationOnly(t *testing.T) { - db := testDB(t, ` - CREATE TABLE osm_passes ( - osm_id integer unique not null, - name text, - elevation integer, - latitude real not null, - longitude real not null - ); - CREATE TABLE mountain_passes ( - external_id text unique not null, - name text not null, - department_code text not null, - elevation integer not null, - latitude real, - longitude real - ); - `) - - insert(t, db, "INSERT INTO osm_passes VALUES (1, 'Autre Col A', 122, 43.3, 5.5)") - insert(t, db, "INSERT INTO osm_passes VALUES (2, 'Autre Col B', 130, 43.5, 5.7)") - - insert(t, db, "INSERT INTO mountain_passes VALUES ('c/1', 'Col de la Gatasse', '13', 120, NULL, NULL)") - - count, err := EnrichMountainPasses(db) - require.NoError(t, err) - assert.Zero(t, count) - - var isNull bool - err = db.QueryRow("SELECT latitude IS NULL FROM mountain_passes WHERE external_id = 'c/1'").Scan(&isNull) - require.NoError(t, err) - assert.True(t, isNull) -} - -func TestEnrichMountainPassesRejectsCrossDepartmentHomonym(t *testing.T) { - db := testDB(t, ` - CREATE TABLE osm_passes ( - osm_id integer unique not null, - name text, - elevation integer, - latitude real not null, - longitude real not null - ); - CREATE TABLE mountain_passes ( - external_id text unique not null, - name text not null, - department_code text not null, - elevation integer not null, - latitude real, - longitude real - ); - `) - - insert(t, db, "INSERT INTO osm_passes VALUES (1, 'Collet de la Selle', 1178, 43.77, 6.81)") - - insert(t, db, "INSERT INTO mountain_passes VALUES ('c/1', 'La Selle', '01', 1175, NULL, NULL)") - - count, err := EnrichMountainPasses(db) - require.NoError(t, err) - assert.Zero(t, count) - - var isNull bool - err = db.QueryRow("SELECT latitude IS NULL FROM mountain_passes WHERE external_id = 'c/1'").Scan(&isNull) - require.NoError(t, err) - assert.True(t, isNull) -} - -func testDB(t *testing.T, schema string) *sql.DB { - db, err := sql.Open("sqlite3", ":memory:") - require.NoError(t, err) - db.SetMaxOpenConns(1) - t.Cleanup(func() { db.Close() }) - _, err = db.Exec(schema) - require.NoError(t, err) - return db -} - -func insert(t *testing.T, db *sql.DB, query string) { - _, err := db.Exec(query) - require.NoError(t, err) -} diff --git a/osmpass/extract.go b/osmpass/extract.go deleted file mode 100644 index cda6890..0000000 --- a/osmpass/extract.go +++ /dev/null @@ -1,98 +0,0 @@ -package osmpass - -import ( - "context" - "database/sql" - "fmt" - "io" - "log/slog" - "os" - "strconv" - "strings" - - "github.com/paulmach/osm" - "github.com/paulmach/osm/osmpbf" -) - -const insertStatement = ` - INSERT INTO osm_passes (osm_id, name, elevation, latitude, longitude) - VALUES (?, ?, ?, ?, ?) - ON CONFLICT(osm_id) DO UPDATE SET - name = excluded.name, - elevation = excluded.elevation, - latitude = excluded.latitude, - longitude = excluded.longitude -` - -func ExtractMountainPasses(ctx context.Context, pbfPath string, db *sql.DB) (int, error) { - file, err := os.Open(pbfPath) - if err != nil { - return 0, fmt.Errorf("failed to open pbf file: %w", err) - } - defer file.Close() - - scanner := osmpbf.New(ctx, file, 4) - scanner.SkipWays = true - scanner.SkipRelations = true - scanner.FilterNode = isMountainPass - - tx, err := db.BeginTx(ctx, nil) - if err != nil { - return 0, fmt.Errorf("failed to begin transaction: %w", err) - } - defer tx.Rollback() - - stmt, err := tx.PrepareContext(ctx, insertStatement) - if err != nil { - return 0, fmt.Errorf("failed to prepare insert: %w", err) - } - defer stmt.Close() - - count := 0 - for scanner.Scan() { - node, ok := scanner.Object().(*osm.Node) - if !ok { - continue - } - elevation, hasElevation := parseElevation(node.Tags.Find("ele")) - var elevationValue any - if hasElevation { - elevationValue = elevation - } - if _, err := stmt.ExecContext(ctx, node.ID, node.Tags.Find("name"), elevationValue, node.Lat, node.Lon); err != nil { - return count, fmt.Errorf("failed to insert osm pass %d: %w", node.ID, err) - } - count++ - } - if err := scanner.Err(); err != nil && err != io.EOF { - return count, fmt.Errorf("failed to scan pbf: %w", err) - } - if err := tx.Commit(); err != nil { - return count, fmt.Errorf("failed to commit transaction: %w", err) - } - slog.Info("Extracted mountain passes", "count", count, "source", pbfPath) - return count, nil -} - -func isMountainPass(node *osm.Node) bool { - if node.Tags.Find("mountain_pass") == "yes" { - return true - } - return node.Tags.Find("natural") == "mountain_pass" -} - -func parseElevation(ele string) (int, bool) { - ele = strings.TrimSpace(ele) - if ele == "" { - return 0, false - } - for _, candidate := range strings.Fields(ele) { - if value, err := strconv.Atoi(candidate); err == nil { - return value, true - } - if value, err := strconv.ParseFloat(candidate, 64); err == nil { - return int(value), true - } - } - return 0, false -} diff --git a/osmpass/extract_test.go b/osmpass/extract_test.go deleted file mode 100644 index bd31ebb..0000000 --- a/osmpass/extract_test.go +++ /dev/null @@ -1,49 +0,0 @@ -package osmpass - -import ( - "testing" - - "github.com/paulmach/osm" - "github.com/stretchr/testify/assert" -) - -func TestIsMountainPass(t *testing.T) { - cases := []struct { - name string - tags map[string]string - want bool - }{ - {"tagged", map[string]string{"mountain_pass": "yes", "name": "Col de la Gineste"}, true}, - {"legacy natural", map[string]string{"natural": "mountain_pass"}, true}, - {"not a pass", map[string]string{"natural": "saddle", "name": "Col de la Gineste"}, false}, - {"no tags", map[string]string{}, false}, - } - for _, c := range cases { - t.Run(c.name, func(t *testing.T) { - node := &osm.Node{Tags: osm.Tags{}} - for key, value := range c.tags { - node.Tags = append(node.Tags, osm.Tag{Key: key, Value: value}) - } - assert.Equal(t, c.want, isMountainPass(node)) - }) - } -} - -func TestParseElevation(t *testing.T) { - cases := []struct { - input string - want int - ok bool - }{ - {"1320", 1320, true}, - {"1320 m", 1320, true}, - {" 447.5", 447, true}, - {"", 0, false}, - {"unknown", 0, false}, - } - for _, c := range cases { - value, ok := parseElevation(c.input) - assert.Equal(t, c.want, value) - assert.Equal(t, c.ok, ok) - } -} diff --git a/osmpass/fetch.go b/osmpass/fetch.go deleted file mode 100644 index 6abd136..0000000 --- a/osmpass/fetch.go +++ /dev/null @@ -1,98 +0,0 @@ -package osmpass - -import ( - "errors" - "fmt" - "io" - "log/slog" - "net/http" - "os" - - "github.com/schollz/progressbar/v3" -) - -const francePBFRUL = "https://download.geofabrik.de/europe/france-latest.osm.pbf" - -// FetchFrancePBF downloads the full France OSM PBF to destPath, resuming a -// partial download when the file already exists and skipping the download -// entirely when it is already complete. -func FetchFrancePBF(destPath string) error { - client := &http.Client{} - - head, err := client.Head(francePBFRUL) - if err != nil { - return fmt.Errorf("failed to check remote size: %w", err) - } - head.Body.Close() - total := head.ContentLength - if total <= 0 { - return fmt.Errorf("unexpected remote content length: %d", total) - } - - info, statErr := os.Stat(destPath) - var offset int64 - if statErr == nil { - offset = info.Size() - if offset >= total { - slog.Info("Already downloaded", "file", destPath, "bytes", offset) - return nil - } - if offset > 0 { - slog.Info("Resuming download", "file", destPath, "bytes", offset, "remaining", total-offset) - } - } else if !errors.Is(statErr, os.ErrNotExist) { - return fmt.Errorf("failed to stat %s: %w", destPath, statErr) - } - - req, err := http.NewRequest(http.MethodGet, francePBFRUL, nil) - if err != nil { - return err - } - req.Header.Set("User-Agent", "biking_home") - if offset > 0 { - req.Header.Set("Range", fmt.Sprintf("bytes=%d-", offset)) - } - - res, err := client.Do(req) - if err != nil { - return err - } - defer res.Body.Close() - - resuming := offset > 0 && res.StatusCode == http.StatusPartialContent - if !resuming { - if res.StatusCode != http.StatusOK { - return fmt.Errorf("unexpected status code: %d", res.StatusCode) - } - if offset > 0 { - slog.Warn("Server ignored Range header, restarting download", "file", destPath) - } - offset = 0 - } - - mode := os.O_CREATE | os.O_WRONLY | os.O_TRUNC - if resuming { - mode = os.O_CREATE | os.O_WRONLY - } - file, err := os.OpenFile(destPath, mode, 0o644) - if err != nil { - return err - } - defer file.Close() - - bar := progressbar.DefaultBytes(res.ContentLength, "Downloading france-latest.osm.pbf") - if resuming { - // Re-append to the partial file when the server honored the range request. - if _, err := file.Seek(offset, io.SeekStart); err != nil { - return err - } - } - _, err = io.Copy(io.MultiWriter(file, bar), res.Body) - if err != nil { - return fmt.Errorf("failed to download %s: %w", francePBFRUL, err) - } - if size, err := file.Stat(); err == nil && size.Size() != total { - return fmt.Errorf("download incomplete: got %d bytes, want %d", size.Size(), total) - } - return nil -} diff --git a/web/server_test.go b/web/server_test.go index 09864e4..299575e 100644 --- a/web/server_test.go +++ b/web/server_test.go @@ -115,11 +115,11 @@ func TestHandlerRendersRideDetailWithEmbeddedRoute(t *testing.T) { DistanceM: 20_000, })) _, err := db.Exec(` - INSERT INTO mountain_passes (external_id, name, country_code, department_code, elevation, latitude, longitude) - VALUES (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?) + INSERT INTO mountain_passes (osm_id, name, elevation, latitude, longitude) + VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?) `, - "550e8400-e29b-41d4-a716-446655440000", "Nearby pass", "FR", "13", 400, 43.05, 5.05, - "6ba7b810-9dad-41d1-80b4-00c04fd430c8", "Distant pass", "FR", "13", 500, 43.2, 5.2, + 1, "Nearby pass", 400, 43.05, 5.05, + 2, "Distant pass", 500, 43.2, 5.2, ) require.NoError(t, err) item, found, err := rides.GetByExternalID(db, "strava:550e8400-e29b-41d4-a716-446655440000") -- cgit v1.3