From 558f755f5746a4ee819de87579951b7533688e5d Mon Sep 17 00:00:00 2001 From: Martin Kagamino Lehoux Date: Thu, 27 Aug 2026 18:05:59 +0200 Subject: Categorise OSM passes by highway and colour them on the ride detail map Passes are extracted from OSM PBF nodes (mountain_pass=yes) and linked to containing ways; highway road values map to road, track to track, path/footway to path, plus hiking for rock/climbing. All passes are kept, coloured distinctly (road/track/path/hiking/unknown) for road-bike validation on ride 397. Single squashed migration adds category column, cheap to re-extract. --- README.md | 11 +- .../20260827000000_mountain_pass_category.sql | 16 ++ db/schema.sql | 19 +-- mountain_pass/detection.go | 11 +- mountain_pass/extract.go | 172 +++++++++++++++++---- mountain_pass/extract_test.go | 43 ++++++ web/frontend/ride-detail-map.ts | 27 +++- web/frontend/types.d.ts | 1 + web/static/style.css | 10 ++ web/views.go | 2 + 10 files changed, 263 insertions(+), 49 deletions(-) create mode 100644 db/migrations/20260827000000_mountain_pass_category.sql diff --git a/README.md b/README.md index deaa9fb..8744938 100644 --- a/README.md +++ b/README.md @@ -49,6 +49,12 @@ The Strava v3 API (OAuth2) is the platform's public access point; scopes gate ea Standard apps are rate-limited to 100 calls per 15 minutes and 1,000 per day. +## OSM + +Passes are French OSM PBF nodes with `mountain_pass=yes` (or legacy `natural=mountain_pass`), stored with name, elevation and coordinates. + +An OSM way is an ordered list of nodes; its `highway` tag describes the road type. For each pass we collect the ways containing its node ID and map `highway` road values (`primary`/`secondary`/…/`service`) → `road`, `track` → `track`, `path`/`footway` → `path` (plus `natural=rock`/`sport=climbing` → `hiking`, no way → `unknown`), coloured distinctly on the ride detail map. + ## Architecture - Go 1.27; SQLite via `mattn/go-sqlite3` @@ -159,6 +165,9 @@ go tool pprof -http=:8081 http://localhost:8080/debug/pprof/heap # allocs ## TODO +- Garmin GPX: elevation, time, temp, HR, cadence +- Garmin TCX: stats (cal, HR, time, cadence) + pos, alt, distance, HR, cadence, speed +- Garmin FIT: time, sport, lap/split, gps, sensor, events - screaming archi + hexagon - Compute estimated power, speed - For a ride, scatter, one point per 100m segment. Slope vs speed @@ -174,8 +183,6 @@ go tool pprof -http=:8081 http://localhost:8080/debug/pprof/heap # allocs - Profile - Adaptative to show ramps - Record ride times on official climbs - Destination via Start -- Mountain pass - - issue: many passes are pedestrian (cf Marseille Calanques, http://localhost:8080/rides/397) - Ride planning - Road quality (Arbois = 2/5, Roquefavour = 4/5) - Blog with pictures and markdown diff --git a/db/migrations/20260827000000_mountain_pass_category.sql b/db/migrations/20260827000000_mountain_pass_category.sql new file mode 100644 index 0000000..0bb3181 --- /dev/null +++ b/db/migrations/20260827000000_mountain_pass_category.sql @@ -0,0 +1,16 @@ +-- migrate:up +ALTER TABLE mountain_passes ADD COLUMN category TEXT NOT NULL DEFAULT 'unknown' CHECK (category IN ('road', 'track', 'path', 'hiking', 'unknown')); + +-- migrate:down +CREATE TABLE mountain_passes_backup ( + id integer primary key, + osm_id integer unique not null, + name text, + elevation integer, + latitude real not null, + longitude real not null +); +INSERT INTO mountain_passes_backup (id, osm_id, name, elevation, latitude, longitude) +SELECT id, osm_id, name, elevation, latitude, longitude FROM mountain_passes; +DROP TABLE mountain_passes; +ALTER TABLE mountain_passes_backup RENAME TO mountain_passes; diff --git a/db/schema.sql b/db/schema.sql index 9713add..8ae55f7 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -1,12 +1,4 @@ CREATE TABLE IF NOT EXISTS "schema_migrations" (version varchar(255) primary key); -CREATE TABLE IF NOT EXISTS "mountain_passes" ( - id integer primary key, - osm_id integer unique not null, - name text, - elevation integer, - latitude real not null, - longitude real not null -); CREATE TABLE rides ( id integer primary key, external_id text unique not null, @@ -32,6 +24,14 @@ CREATE TABLE official_climbs ( 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')) ); +CREATE TABLE IF NOT EXISTS "mountain_passes" ( + id integer primary key, + osm_id integer unique not null, + name text, + elevation integer, + latitude real not null, + longitude real not null +, category TEXT NOT NULL DEFAULT 'unknown' CHECK (category IN ('road', 'track', 'path', 'hiking', 'unknown'))); -- Dbmate schema migrations INSERT INTO "schema_migrations" (version) VALUES ('20250802140659'), @@ -41,4 +41,5 @@ INSERT INTO "schema_migrations" (version) VALUES ('20260813000000'), ('20260820184307'), ('20260821000000'), - ('20260823000000'); + ('20260823000000'), + ('20260827000000'); diff --git a/mountain_pass/detection.go b/mountain_pass/detection.go index 79e55e4..a79377f 100644 --- a/mountain_pass/detection.go +++ b/mountain_pass/detection.go @@ -11,11 +11,13 @@ import ( // 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. +// elevation, as filtered by the loaders. Category classifies bike suitability +// (road, track, path, hiking, unknown) derived from containing way highways. type MountainPass struct { Name string Elevation int Coord *geodist.Coord + Category string } type Crossing struct { @@ -64,7 +66,7 @@ func rideBounds(coords []geodist.Coord, marginM float64) *coordinateBounds { func loadMountainPasses(db *sql.DB, bounds *coordinateBounds) ([]MountainPass, error) { query := ` - SELECT name, elevation, latitude, longitude + SELECT name, elevation, latitude, longitude, COALESCE(category,'unknown') FROM mountain_passes WHERE elevation IS NOT NULL AND name IS NOT NULL AND name != ''` @@ -87,10 +89,13 @@ func loadMountainPasses(db *sql.DB, bounds *coordinateBounds) ([]MountainPass, e for rows.Next() { var mountainPass MountainPass var latitude, longitude float64 - if err := rows.Scan(&mountainPass.Name, &mountainPass.Elevation, &latitude, &longitude); err != nil { + if err := rows.Scan(&mountainPass.Name, &mountainPass.Elevation, &latitude, &longitude, &mountainPass.Category); err != nil { return nil, err } mountainPass.Coord = &geodist.Coord{Lat: latitude, Lon: longitude} + if mountainPass.Category == "" { + mountainPass.Category = CategoryUnknown + } mountainPasses = append(mountainPasses, mountainPass) } return mountainPasses, rows.Err() diff --git a/mountain_pass/extract.go b/mountain_pass/extract.go index 9643603..ccf8d06 100644 --- a/mountain_pass/extract.go +++ b/mountain_pass/extract.go @@ -15,27 +15,34 @@ import ( ) const insertStatement = ` - INSERT INTO mountain_passes (osm_id, name, elevation, latitude, longitude) - VALUES (?, ?, ?, ?, ?) + INSERT INTO mountain_passes (osm_id, name, elevation, latitude, longitude, category) + VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(osm_id) DO UPDATE SET name = excluded.name, elevation = excluded.elevation, latitude = excluded.latitude, - longitude = excluded.longitude + longitude = excluded.longitude, + category = excluded.category ` +// PassCategory classifies a pass for bike suitability: road = paved road, +// track = unpaved vehicle track, path = hiking trail, hiking = rock/climbing, +// unknown = not on a highway. +const ( + CategoryRoad = "road" + CategoryTrack = "track" + CategoryPath = "path" + CategoryHiking = "hiking" + CategoryUnknown = "unknown" +) + // ExtractMountainPasses scans pbfPath for mountain pass nodes and upserts them // into the database. An optional reporter is invoked as scanning progresses // with bytes scanned and the total file size, so callers (e.g. the CLI) can -// render progress. +// render progress. Category is derived from the highways of ways that contain +// the pass node (road > track > path) plus node tags (rock/climbing => hiking). func ExtractMountainPasses(ctx context.Context, pbfPath string, db *sql.DB, reporters ...func(current, total int64)) (int, error) { - file, err := os.Open(pbfPath) - if err != nil { - return 0, fmt.Errorf("failed to open pbf file: %w", err) - } - defer file.Close() - - info, err := file.Stat() + info, err := os.Stat(pbfPath) if err != nil { return 0, fmt.Errorf("failed to stat pbf file: %w", err) } @@ -44,12 +51,91 @@ func ExtractMountainPasses(ctx context.Context, pbfPath string, db *sql.DB, repo if len(reporters) > 0 { report = reporters[0] } + + // Phase 1: collect pass nodes. + type cachedPass struct { + id osm.NodeID + name string + elevation any + lat, lon float64 + tags osm.Tags + } + file, err := os.Open(pbfPath) + if err != nil { + return 0, fmt.Errorf("failed to open pbf file: %w", err) + } reader := &countingReader{r: file} scanner := osmpbf.New(ctx, reader, 4) scanner.SkipWays = true scanner.SkipRelations = true scanner.FilterNode = isMountainPass + cached := make([]cachedPass, 0, 4096) + passSet := make(map[osm.NodeID]int, 4096) // node ID -> index in cached + for scanner.Scan() { + report(reader.n, total) + node, ok := scanner.Object().(*osm.Node) + if !ok { + continue + } + elevation, hasElevation := parseElevation(node.Tags.Find("ele")) + var elevationValue any + if hasElevation { + elevationValue = elevation + } + idx := len(cached) + cached = append(cached, cachedPass{ + id: node.ID, + name: node.Tags.Find("name"), + elevation: elevationValue, + lat: float64(node.Lat), + lon: float64(node.Lon), + tags: node.Tags, + }) + passSet[node.ID] = idx + } + if err := scanner.Err(); err != nil && err != io.EOF { + file.Close() + return 0, fmt.Errorf("failed to scan pbf nodes: %w", err) + } + file.Close() + + // Phase 2: scan ways to collect highways per pass. + highwaysByPass := make(map[osm.NodeID][]string, len(cached)) + file2, err := os.Open(pbfPath) + if err != nil { + return 0, fmt.Errorf("failed to reopen pbf for ways: %w", err) + } + defer file2.Close() + reader2 := &countingReader{r: file2} + scanner2 := osmpbf.New(ctx, reader2, 4) + scanner2.SkipNodes = true + scanner2.SkipRelations = true + for scanner2.Scan() { + // Report second phase as second half of total for progress continuity. + // We offset by total to avoid going backwards; callers use ratio anyway. + report(reader2.n, total) + way, ok := scanner2.Object().(*osm.Way) + if !ok { + continue + } + highway := way.Tags.Find("highway") + // Keep even empty highway to detect non-highway ways? Only track highways. + // We store only ways that actually represent a highway for categorisation; + // non-highway ways are ignored (they keep pass as unknown). + if highway == "" { + continue + } + for _, nid := range way.Nodes { + if _, ok := passSet[nid.ID]; ok { + highwaysByPass[nid.ID] = append(highwaysByPass[nid.ID], highway) + } + } + } + if err := scanner2.Err(); err != nil && err != io.EOF { + return 0, fmt.Errorf("failed to scan pbf ways: %w", err) + } + tx, err := db.BeginTx(ctx, nil) if err != nil { return 0, fmt.Errorf("failed to begin transaction: %w", err) @@ -63,25 +149,14 @@ func ExtractMountainPasses(ctx context.Context, pbfPath string, db *sql.DB, repo defer stmt.Close() count := 0 - for scanner.Scan() { - report(reader.n, total) - 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) + for _, p := range cached { + highways := highwaysByPass[p.id] + category := categorizePass(p.tags, highways) + if _, err := stmt.ExecContext(ctx, p.id, p.name, p.elevation, p.lat, p.lon, category); err != nil { + return count, fmt.Errorf("failed to insert osm pass %d: %w", p.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) } @@ -89,6 +164,49 @@ func ExtractMountainPasses(ctx context.Context, pbfPath string, db *sql.DB, repo return count, nil } +func categorizePass(tags osm.Tags, highways []string) string { + natural := tags.Find("natural") + sport := tags.Find("sport") + isHikingNode := natural == "rock" || sport == "climbing" + for _, h := range highways { + if isRoadHighway(h) { + return CategoryRoad + } + } + if isHikingNode { + return CategoryHiking + } + for _, h := range highways { + if h == "track" { + return CategoryTrack + } + } + for _, h := range highways { + if isPathHighway(h) { + return CategoryPath + } + } + return CategoryUnknown +} + +func isRoadHighway(highway string) bool { + switch highway { + case "motorway", "motorway_link", "trunk", "trunk_link", "primary", "primary_link", "secondary", "secondary_link", "tertiary", "tertiary_link", "unclassified", "residential", "service", "living_street", "road", "pedestrian": + return true + default: + return false + } +} + +func isPathHighway(highway string) bool { + switch highway { + case "path", "footway", "cycleway", "bridleway", "steps", "via_ferrata": + return true + default: + return false + } +} + // countingReader tracks the number of bytes read from an underlying reader, // used to report scan progress against the PBF file size. type countingReader struct { diff --git a/mountain_pass/extract_test.go b/mountain_pass/extract_test.go index 7eae33b..952ba08 100644 --- a/mountain_pass/extract_test.go +++ b/mountain_pass/extract_test.go @@ -68,3 +68,46 @@ func TestCountingReaderTracksBytes(t *testing.T) { } assert.Equal(t, int64(len(content)), reader.n) } + +func TestCategorizePass(t *testing.T) { + cases := []struct { + name string + tags map[string]string + highways []string + wantCategory string + }{ + {"road secondary", map[string]string{"natural": "saddle"}, []string{"secondary"}, CategoryRoad}, + {"road wins over path", map[string]string{"natural": "saddle"}, []string{"path", "secondary", "track"}, CategoryRoad}, + {"track", map[string]string{"natural": "saddle"}, []string{"track"}, CategoryTrack}, + {"path", map[string]string{"natural": "saddle"}, []string{"path"}, CategoryPath}, + {"hiking rock path", map[string]string{"natural": "rock", "sport": "climbing"}, []string{"path"}, CategoryHiking}, + {"hiking rock no way", map[string]string{"natural": "rock"}, nil, CategoryHiking}, + {"unknown no way", map[string]string{"natural": "saddle"}, nil, CategoryUnknown}, + {"via_ferrata is path", map[string]string{"natural": "saddle"}, []string{"via_ferrata"}, CategoryPath}, + {"unknown other highway", map[string]string{"natural": "saddle"}, []string{"construction"}, CategoryUnknown}, + {"hiking rock but road present is road", map[string]string{"natural": "rock", "sport": "climbing"}, []string{"secondary"}, CategoryRoad}, + } + for _, c := range cases { + tags := osm.Tags{} + for k, v := range c.tags { + tags = append(tags, osm.Tag{Key: k, Value: v}) + } + cat := categorizePass(tags, c.highways) + assert.Equal(t, c.wantCategory, cat, c.name) + } +} + +func TestIsRoadHighway(t *testing.T) { + assert.True(t, isRoadHighway("secondary")) + assert.True(t, isRoadHighway("primary")) + assert.True(t, isRoadHighway("tertiary")) + assert.True(t, isRoadHighway("residential")) + assert.False(t, isRoadHighway("track")) + assert.False(t, isRoadHighway("path")) +} + +func TestIsPathHighway(t *testing.T) { + assert.True(t, isPathHighway("path")) + assert.True(t, isPathHighway("footway")) + assert.False(t, isPathHighway("secondary")) +} diff --git a/web/frontend/ride-detail-map.ts b/web/frontend/ride-detail-map.ts index a9b9c06..508829c 100644 --- a/web/frontend/ride-detail-map.ts +++ b/web/frontend/ride-detail-map.ts @@ -41,16 +41,27 @@ export class RideDetailMap { .addTo(this.map); const bounds = routeLayer.getBounds(); if (bounds.isValid()) this.map.fitBounds(bounds, { padding: [24, 24], maxZoom: 15 }); - const passIcon = leaflet.divIcon({ - className: "ride-map-pass-icon", - html: '', - iconSize: [28, 28], - iconAnchor: [14, 14], - }); + const passIconCache = new Map(); + const getPassIcon = (category: string): DivIcon => { + const cat = (category || "unknown").toLowerCase(); + const valid = ["road", "track", "path", "hiking", "unknown"].includes(cat) ? cat : "unknown"; + const cached = passIconCache.get(valid); + if (cached) return cached; + const icon = leaflet.divIcon({ + className: `ride-map-pass-icon ride-map-pass-icon-${valid}`, + html: '', + iconSize: [28, 28], + iconAnchor: [14, 14], + }); + passIconCache.set(valid, icon); + return icon; + }; for (const pass of passes) { - const marker = leaflet.marker([pass.latitude, pass.longitude], { icon: passIcon, alt: pass.name || "Mountain pass" }).addTo(this.map); + const icon = getPassIcon(pass.category); + const marker = leaflet.marker([pass.latitude, pass.longitude], { icon, alt: pass.name || "Mountain pass" }).addTo(this.map); const tooltip = document.createElement("span"); - tooltip.textContent = `${pass.name || "Mountain pass"} | ${Math.round(pass.elevationM)} m`; + const catLabel = pass.category ? ` · ${pass.category}` : ""; + tooltip.textContent = `${pass.name || "Mountain pass"} | ${Math.round(pass.elevationM)} m${catLabel}`; marker.bindTooltip(tooltip); } this.climbLayers = climbs.map((climb) => this.createClimbLayer(climb)); diff --git a/web/frontend/types.d.ts b/web/frontend/types.d.ts index d4c4bdf..790abe6 100644 --- a/web/frontend/types.d.ts +++ b/web/frontend/types.d.ts @@ -62,6 +62,7 @@ export interface RideMapPass { elevationM: number; latitude: number; longitude: number; + category: string; } export interface ClimbBounds { diff --git a/web/static/style.css b/web/static/style.css index 755a5cb..61e482a 100644 --- a/web/static/style.css +++ b/web/static/style.css @@ -47,6 +47,11 @@ --color-profile-3-6: oklch(58% 0.17 255deg); --color-profile-6-9: oklch(58% 0.2 25deg); --color-profile-9-plus: oklch(0% 0 0deg); + --color-pass-road: oklch(51% 0.14 154deg); + --color-pass-track: oklch(58% 0.11 85deg); + --color-pass-path: oklch(65% 0.17 48deg); + --color-pass-hiking: oklch(52% 0.07 265deg); + --color-pass-unknown: oklch(70% 0.04 155deg); --color-category-1: oklch(65% 0.16 15deg); --color-category-2: oklch(65% 0.16 60deg); --color-category-3: oklch(65% 0.16 105deg); @@ -129,6 +134,11 @@ select { border: 1px solid var(--color-input-border); border-radius: .55rem; pad .ride-map { min-height: 24rem; height: min(65vh, 40rem); } .ride-map-pass-icon { display: grid; place-items: center; width: 1.75rem !important; height: 1.75rem !important; border: 2px solid var(--color-surface); border-radius: 50%; background: var(--color-leaf); color: var(--color-on-dark); box-shadow: 0 .15rem .4rem var(--color-panel-shadow); } .ride-map-pass-icon svg { width: 1.15rem; height: 1.15rem; } +.ride-map-pass-icon-road { background: var(--color-pass-road); } +.ride-map-pass-icon-track { background: var(--color-pass-track); } +.ride-map-pass-icon-path { background: var(--color-pass-path); } +.ride-map-pass-icon-hiking { background: var(--color-pass-hiking); } +.ride-map-pass-icon-unknown { background: var(--color-pass-unknown); } .ride-map-climb-start-icon, .ride-map-climb-end-icon { display: grid; place-items: center; width: 1.625rem !important; height: 1.625rem !important; border: 2px solid var(--color-surface); box-shadow: 0 .15rem .4rem var(--color-panel-shadow); } .ride-map-climb-start-icon { border-radius: 50%; background: var(--color-forest); color: var(--color-on-dark); } .ride-map-climb-end-icon { border-radius: .3rem; background: var(--color-accent); color: var(--color-on-dark); } diff --git a/web/views.go b/web/views.go index 5c8d452..9a79906 100644 --- a/web/views.go +++ b/web/views.go @@ -82,6 +82,7 @@ type RideMapPass struct { ElevationM int `json:"elevationM"` Latitude float64 `json:"latitude"` Longitude float64 `json:"longitude"` + Category string `json:"category"` } type RideProfile struct { @@ -322,6 +323,7 @@ func buildRideMapPasses(passes []mountain_pass.MountainPass) []RideMapPass { ElevationM: mountainPass.Elevation, Latitude: mountainPass.Coord.Lat, Longitude: mountainPass.Coord.Lon, + Category: mountainPass.Category, }) } return mapPasses -- cgit v1.3