1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
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)
}
|