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/schema.sql | 19 ++++++++++--------- 1 file changed, 10 insertions(+), 9 deletions(-) (limited to 'db/schema.sql') 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'); -- cgit v1.3