use geo::{Geometry, Point};
use rusqlite::Connection;
use std::collections::HashMap;
use super::entity::house_number_link;
use super::policy::house_number_policy;
use super::value::house_number;
use crate::admin_level::geometry::admin_geometry;
use crate::database::table;
const SQL_CREATE: &str = "
CREATE TABLE IF NOT EXISTS house_numbers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
node_id INTEGER NOT NULL UNIQUE,
admin_level_id INTEGER NOT NULL REFERENCES admin_levels(id) ON DELETE CASCADE,
number VARCHAR(12) NOT NULL,
wkb BLOB,
strategy INTEGER NOT NULL
);
";
const SQL_CREATE_INDEXES: &str = "
CREATE INDEX IF NOT EXISTS house_numbers_search_by_admin_level_id ON house_numbers(admin_level_id);
";
const SQL_DROP: &str = "DROP TABLE IF EXISTS house_numbers;";
const SQL_DROP_INDEXES: &str = "
DROP INDEX IF EXISTS house_numbers_search_by_admin_level_id;
";
pub struct house_numbers;
impl table for house_numbers {
const CREATE: &str = SQL_CREATE;
const INDEXES: &str = SQL_CREATE_INDEXES;
}
pub(crate) fn drop_table(conn: &Connection) {
conn
.execute_batch(SQL_DROP)
.expect("failed to drop house_numbers");
}
pub fn drop_indexes(conn: &Connection) {
conn
.execute_batch(SQL_DROP_INDEXES)
.expect("failed to drop house_numbers indexes");
}
pub struct candidate_row {
pub id: u64,
pub number: house_number,
pub addr_street: Option<String>,
pub lon: f64,
pub lat: f64,
}
pub fn load_all_candidates(conn: &Connection, policy: &house_number_policy) -> Vec<candidate_row> {
const SQL_LOAD_ALL_CANDIDATES: &str = "
SELECT
id,
CAST({number_select} AS TEXT) AS number,
{street_select} AS addr_street,
CAST(payload->>'lon' AS REAL) AS lon,
CAST(payload->>'lat' AS REAL) AS lat
FROM osm_data.osm_nodes
WHERE {number_select} IS NOT NULL
";
debug_assert!(
!policy.number_tags.is_empty(),
"number_tags must not be empty"
);
let number_select = crate::osm_tag::select::coalesce_of("payload", policy.number_tags);
let street_select = crate::osm_tag::select::coalesce_of("payload", policy.street_tags);
let sql = SQL_LOAD_ALL_CANDIDATES
.replace("{number_select}", &number_select)
.replace("{street_select}", &street_select);
let mut stmt = conn
.prepare(&sql)
.expect("failed to prepare load all candidates");
stmt
.query_map([], |row| {
Ok((
row.get::<_, u64>(0)?,
row.get::<_, String>(1)?,
row.get::<_, Option<String>>(2)?,
row.get::<_, f64>(3)?,
row.get::<_, f64>(4)?,
))
})
.expect("failed to query candidates")
.map(|r| r.expect("failed to read candidate row"))
.filter_map(|(id, raw, addr_street, lon, lat)| {
house_number::normalize(&raw, policy).map(|number| candidate_row {
id,
number,
addr_street,
lon,
lat,
})
})
.collect()
}
struct hn_for_street {
admin_level_id: i64,
number: house_number,
wkb: Option<admin_geometry>,
}
fn by_admin_level_ids(conn: &Connection, ids: &[i64]) -> Vec<hn_for_street> {
const SQL_BY_ADMIN_LEVEL_IDS: &str = "
SELECT admin_level_id, number, wkb
FROM house_numbers
WHERE admin_level_id IN
";
if ids.is_empty() {
return vec![];
}
let sql = format!(
"{} ({})\n ORDER BY node_id",
SQL_BY_ADMIN_LEVEL_IDS.trim(),
crate::database::placeholders_for(ids.len())
);
crate::database::query_by_ids(conn, &sql, ids.iter().copied(), |row| {
Ok(hn_for_street {
admin_level_id: row.get(0)?,
number: house_number::from_stored(&row.get::<_, String>(1)?),
wkb: row.get(2)?,
})
})
}
pub fn numbers_by_street(
conn: &Connection,
admin_level_ids: &[i64],
) -> HashMap<i64, Vec<(house_number, Point<f64>)>> {
let mut by_street: HashMap<i64, Vec<(house_number, Point<f64>)>> = HashMap::new();
for row in by_admin_level_ids(conn, admin_level_ids) {
if let Some(Geometry::Point(point)) = row.wkb.as_ref().map(admin_geometry::geometry) {
by_street
.entry(row.admin_level_id)
.or_default()
.push((row.number, *point));
}
}
by_street
}
pub fn batch_insert_links(conn: &Connection, links: &[house_number_link]) -> i64 {
const SQL_INSERT: &str = "
INSERT OR IGNORE INTO house_numbers (
node_id,
admin_level_id,
number,
wkb,
strategy
) VALUES (
?1,
?2,
?3,
?4,
?5
);
";
if links.is_empty() {
return 0;
}
let tx = conn
.unchecked_transaction()
.expect("failed to begin transaction");
let mut total: i64 = 0;
{
let mut stmt = tx
.prepare(SQL_INSERT)
.expect("failed to prepare house_numbers insert");
for link in links {
let wkb: admin_geometry = Geometry::Point(link.point).into();
let changes = stmt
.execute(rusqlite::params![
link.node_id,
link.street_id.raw() as i64,
link.number.stored_form(),
wkb,
link.strategy.code(),
])
.expect("failed to insert house_number");
total += changes as i64;
}
}
tx.commit().expect("failed to commit");
total
}
pub fn repoint_streets(conn: &Connection, moves: &[(i64, i64)]) -> usize {
const SQL_REPOINT: &str = "
UPDATE house_numbers
SET admin_level_id = ?2
WHERE admin_level_id = ?1
";
if moves.is_empty() {
return 0;
}
let tx = conn
.unchecked_transaction()
.expect("failed to begin transaction");
let mut total: usize = 0;
{
let mut stmt = tx
.prepare(SQL_REPOINT)
.expect("failed to prepare house_numbers repoint");
for &(from, to) in moves {
total += stmt
.execute(rusqlite::params![from, to])
.expect("failed to repoint house_numbers");
}
}
tx.commit().expect("failed to commit");
total
}
pub fn count(conn: &Connection) -> i64 {
const SQL_COUNT: &str = "
SELECT COUNT(*)
FROM house_numbers
";
conn
.query_row(SQL_COUNT, [], |row| row.get(0))
.expect("failed to count house_numbers")
}
pub fn any(conn: &Connection) -> bool {
const SQL_ANY: &str = "
SELECT EXISTS (
SELECT 1
FROM house_numbers
)
";
conn
.query_row(SQL_ANY, [], |row| row.get::<_, bool>(0))
.expect("failed to query house_numbers for a row")
}