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. --- db/migrations/20260827000000_mountain_pass_category.sql | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) create mode 100644 db/migrations/20260827000000_mountain_pass_category.sql (limited to 'db/migrations') 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; -- cgit v1.3