summaryrefslogtreecommitdiff
path: root/osmpass
diff options
context:
space:
mode:
Diffstat (limited to 'osmpass')
-rw-r--r--osmpass/enrich.go240
-rw-r--r--osmpass/enrich_test.go149
-rw-r--r--osmpass/extract.go98
-rw-r--r--osmpass/extract_test.go49
-rw-r--r--osmpass/fetch.go98
5 files changed, 634 insertions, 0 deletions
diff --git a/osmpass/enrich.go b/osmpass/enrich.go
new file mode 100644
index 0000000..3af0067
--- /dev/null
+++ b/osmpass/enrich.go
@@ -0,0 +1,240 @@
+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
new file mode 100644
index 0000000..88c6a3f
--- /dev/null
+++ b/osmpass/enrich_test.go
@@ -0,0 +1,149 @@
+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
new file mode 100644
index 0000000..cda6890
--- /dev/null
+++ b/osmpass/extract.go
@@ -0,0 +1,98 @@
+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
new file mode 100644
index 0000000..bd31ebb
--- /dev/null
+++ b/osmpass/extract_test.go
@@ -0,0 +1,49 @@
+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
new file mode 100644
index 0000000..6abd136
--- /dev/null
+++ b/osmpass/fetch.go
@@ -0,0 +1,98 @@
+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
+}
generated by cgit v1.2.3 (git 2.39.1) at 2026-08-19 09:00:02 +0000