geolite 0.0.14

open source geocode solution
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

use rusqlite::Connection;

const SQL_ATTACH_SOURCE: &str = "ATTACH DATABASE ?1 AS merge_src";
const SQL_DETACH_SOURCE: &str = "DETACH DATABASE merge_src";

const SQL_MERGE_ADMIN_LEVELS: &str = "
  INSERT INTO admin_levels (
    id,
    relation_id,
    way_id,
    admin_level,
    wkb,
    name,
    country_iso_code,
    post_code,
    merged_way_ids
  )
  SELECT
    id,
    relation_id,
    way_id,
    admin_level,
    wkb,
    name,
    country_iso_code,
    post_code,
    {source_merged_way_ids}
  FROM merge_src.admin_levels
  WHERE TRUE
  ON CONFLICT (id) DO UPDATE SET
    name             = excluded.name,
    country_iso_code = excluded.country_iso_code,
    post_code        = excluded.post_code,
    wkb              = excluded.wkb,
    merged_way_ids   = excluded.merged_way_ids
";

const SQL_MERGE_HOUSE_NUMBERS: &str = "
  INSERT OR IGNORE INTO house_numbers (
    node_id,
    admin_level_id,
    number,
    wkb,
    strategy
  )
  SELECT
    node_id,
    admin_level_id,
    number,
    wkb,
    strategy
  FROM merge_src.house_numbers
";

// attaches a source database read-only, merges its admin_levels (upsert by id) and house_numbers
// (insert-or-ignore by node_id) into the open base connection, then detaches. returns the number
// of (admin_levels, house_numbers) rows the merge touched.
pub fn merge_source(conn: &Connection, source_path: &str) -> (usize, usize) {
  let canonical = std::fs::canonicalize(source_path)
    .map(|p| p.to_string_lossy().into_owned())
    .unwrap_or_else(|_| source_path.to_string());
  let uri = format!("file:{canonical}?mode=ro");
  conn
    .execute(SQL_ATTACH_SOURCE, [&uri])
    .expect("failed to attach merge source");
  // a source attached read-only cannot gain the column, so one built before it answers NULL
  let tracking = if super::has_column(conn, "merge_src", "admin_levels", "merged_way_ids") {
    "merged_way_ids"
  } else {
    "NULL"
  };
  // admin_levels first: house_numbers.admin_level_id references admin_levels(id).
  let admins = conn
    .execute(
      &SQL_MERGE_ADMIN_LEVELS.replace("{source_merged_way_ids}", tracking),
      [],
    )
    .expect("failed to merge admin_levels");
  let houses = conn
    .execute(SQL_MERGE_HOUSE_NUMBERS, [])
    .expect("failed to merge house_numbers");
  conn
    .execute(SQL_DETACH_SOURCE, [])
    .expect("failed to detach merge source");
  (admins, houses)
}