Skip to main content

radio_cli/store/
geo.rs

1use super::*;
2use crate::geo::{GeoBounds, GeoFilter, WORLD_WIDTH};
3
4pub(super) const SORT_THRESHOLD: usize = 4096;
5
6pub(super) fn migrate(conn: &Connection) -> Result<()> {
7    conn.execute_batch(
8        "ALTER TABLE stations ADD COLUMN geo_x INTEGER;
9        ALTER TABLE stations ADD COLUMN geo_y INTEGER;
10        CREATE INDEX station_geo ON stations(geo_x,geo_y) WHERE active=1 AND geo_x IS NOT NULL;
11        INSERT INTO meta(key,value) VALUES ('geo_revision','1');",
12    )?;
13    let mut read = conn.prepare("SELECT rowid,json FROM stations WHERE json_extract(json,'$.geo_lat') IS NOT NULL AND json_extract(json,'$.geo_long') IS NOT NULL")?;
14    let mut write = conn.prepare("UPDATE stations SET geo_x=?2,geo_y=?3 WHERE rowid=?1")?;
15    for row in read.query_map([], |r| Ok((r.get::<_, i64>(0)?, r.get::<_, String>(1)?)))? {
16        let (id, json) = row?;
17        let s: Station = serde_json::from_str(&json)?;
18        if let Some((x, y)) = crate::geo::coordinates(s.geo_lat, s.geo_long) {
19            write.execute(params![id, x, y])?;
20        }
21    }
22    Ok(())
23}
24pub(super) fn changed(conn: &Connection) -> Result<()> {
25    conn.execute(
26        "UPDATE meta SET value=CAST(value AS INTEGER)+1 WHERE key='geo_revision'",
27        [],
28    )?;
29    Ok(())
30}
31pub(super) fn predicate(b: GeoBounds, args: &mut Vec<Value>) -> String {
32    args.extend([b.y.into(), (b.y + b.height).into()]);
33    let mut sql = "s.geo_x IS NOT NULL AND s.geo_y>=? AND s.geo_y<?".to_owned();
34    if b.width < WORLD_WIDTH {
35        args.push(b.x.into());
36        args.push(((b.x + b.width) % WORLD_WIDTH).into());
37        sql.push_str(if b.x + b.width < WORLD_WIDTH {
38            " AND (s.geo_x>=? AND s.geo_x<?)"
39        } else {
40            " AND (s.geo_x>=? OR s.geo_x<?)"
41        });
42    }
43    sql
44}
45pub(super) fn plain(f: &Filter) -> bool {
46    f.query.trim().is_empty()
47        && f.country.is_none()
48        && f.language.is_none()
49        && f.tag.is_none()
50        && f.min_bitrate == 0
51        && !f.custom
52        && !f.favorites
53        && !f.history
54}
55impl Store {
56    pub fn geo_revision(&self) -> Result<u64> {
57        Ok(self.conn.query_row(
58            "SELECT CAST(value AS INTEGER) FROM meta WHERE key='geo_revision'",
59            [],
60            |r| r.get(0),
61        )?)
62    }
63    /// Stream compact coordinates into a bounded grid; never retain the atlas.
64    pub fn geo_counts(
65        &self,
66        filter: &Filter,
67        bounds: GeoBounds,
68        columns: usize,
69        rows: usize,
70        mut cancelled: impl FnMut() -> bool,
71    ) -> Result<Vec<u32>> {
72        anyhow::ensure!(
73            (1..=320).contains(&columns) && (1..=80).contains(&rows),
74            "invalid map size"
75        );
76        let mut f = filter.clone();
77        f.geo = Some(GeoFilter::Cell(bounds));
78        let plain = plain(&f);
79        let (conditions, args, source) = if plain {
80            let mut args = vec![];
81            (
82                format!("s.active=1 AND {}", predicate(bounds, &mut args)),
83                args,
84                "stations s INDEXED BY station_geo",
85            )
86        } else {
87            self.conditions(&f)
88        };
89        let mut stmt = self.conn.prepare_cached(&format!(
90            "SELECT s.geo_x,s.geo_y FROM {source} WHERE {conditions}"
91        ))?;
92        let mut points = stmt.query(params_from_iter(args))?;
93        let mut grid = vec![0u32; columns * rows];
94        let mut n = 0usize;
95        while let Some(row) = points.next()? {
96            if n.is_multiple_of(1024) && cancelled() {
97                bail!(DomainError::new(ErrorCode::Cancelled));
98            }
99            n += 1;
100            if let Some((x, y)) = bounds.project(row.get(0)?, row.get(1)?, columns, rows) {
101                grid[y * columns + x] = grid[y * columns + x].saturating_add(1);
102            }
103        }
104        Ok(grid)
105    }
106    pub fn geo_missing_countries(&self, filter: &Filter) -> Result<Vec<crate::model::Facet>> {
107        let mut f = filter.clone();
108        f.geo = Some(GeoFilter::Missing);
109        f.country = None;
110        let (conditions, args, source) = self.conditions(&f);
111        let mut stmt = self.conn.prepare_cached(&format!(
112            "SELECT s.country,count(*) FROM {source} WHERE {conditions} \
113             GROUP BY s.country ORDER BY s.country LIMIT 1000"
114        ))?;
115        Ok(stmt
116            .query_map(params_from_iter(args), |r| {
117                Ok(crate::model::Facet {
118                    value: r.get(0)?,
119                    count: r.get(1)?,
120                })
121            })?
122            .collect::<rusqlite::Result<_>>()?)
123    }
124}