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.

1 files changed, 145 insertions, 27 deletions

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 {