bufkit_data/archive/query/
station_summary.rs

1use crate::{
2    errors::BufkitDataErr,
3    models::Model,
4    site::{StateProv, StationNumber},
5};
6use chrono::FixedOffset;
7use std::{collections::HashMap, str::FromStr};
8use rusqlite::Statement;
9
10/// A summary of the information about a station.
11#[derive(Debug)]
12pub struct StationSummary {
13    /// Station number
14    pub station_num: StationNumber,
15    /// List of ids associated with this site
16    pub ids: Vec<String>,
17    /// All the models in the archive associated with this site
18    pub models: Vec<Model>,
19    /// Station name, common name
20    pub name: Option<String>,
21    /// Notes related to the site
22    pub notes: Option<String>,
23    /// The state-province associated with the site.
24    pub state: Option<StateProv>,
25    /// The time zone offset to local standard time.
26    pub time_zone: Option<FixedOffset>,
27    /// Coordinates
28    pub coords: Vec<(f64, f64)>,
29    /// The number of files in the archive related to this site.
30    pub number_of_files: u32,
31}
32
33struct StationEntry {
34    station_num: StationNumber,
35    id: Option<String>,
36    model: Option<Model>,
37    name: Option<String>,
38    notes: Option<String>,
39    state: Option<StateProv>,
40    time_zone: Option<FixedOffset>,
41    lat: f64,
42    lon: f64,
43    number_of_files: u32,
44}
45
46impl StationSummary {
47    /// Concantenate the ids into a comma separated list.
48    pub fn ids_as_string(&self) -> String {
49        self.ids.join(", ")
50    }
51
52    /// Concatenate the models into a comma separated list.
53    pub fn models_as_string(&self) -> String {
54        self.models
55            .iter()
56            .map(|m| m.as_static_str().to_owned())
57            .collect::<Vec<_>>()
58            .join(", ")
59    }
60
61    /// Concatenate the different coordinates as a string.
62    pub fn coords_as_string(&self) -> String {
63        self.coords
64            .iter()
65            .map(|(lat, lon)| format!("({},{})", lat, lon))
66            .collect::<Vec<_>>()
67            .join(", ")
68    }
69}
70
71impl From<StationEntry> for StationSummary {
72    fn from(entry: StationEntry) -> Self {
73        let StationEntry {
74            station_num,
75            id,
76            model,
77            name,
78            notes,
79            state,
80            time_zone,
81            lat,
82            lon,
83            number_of_files,
84        } = entry;
85
86        let mut models = vec![];
87        if let Some(model) = model {
88            models.push(model);
89        }
90
91        let mut ids = vec![];
92        if let Some(id) = id {
93            ids.push(id);
94        }
95
96        let coords = vec![(lat, lon),];
97
98        StationSummary {
99            station_num,
100            ids,
101            models,
102            name,
103            notes,
104            state,
105            time_zone,
106            coords,
107            number_of_files,
108        }
109    }
110}
111
112impl crate::Archive {
113    /// Get a summary of all the stations in the archive.
114    pub fn station_summaries(&self) -> Result<Vec<StationSummary>, BufkitDataErr> {
115        let mut stmt = self.db_conn.prepare(include_str!("station_summary.sql"))?;
116
117        Self::process_summary_statement(&mut stmt)
118    }
119
120    /// Get a summary of all the stations in the archive near a point..
121    pub fn station_summaries_near(&self, lat: f64, lon: f64) -> Result<Vec<StationSummary>, BufkitDataErr> {
122
123        let max_lat = lat + 0.5;
124        let min_lat = lat - 0.5;
125        let max_lon = lon + 0.5;
126        let min_lon = lon - 0.5;
127
128        let query_str = format!(r#"
129                SELECT 
130                    sites.station_num, 
131                    files.id, 
132                    files.model, 
133                    sites.name, 
134                    sites.state, 
135                    sites.notes, 
136                    sites.tz_offset_sec, 
137                    files.lat,
138                    files.lon,
139                    COUNT(files.station_num)
140                FROM sites LEFT JOIN files ON files.station_num = sites.station_num
141                WHERE files.lat > {} AND files.lat < {} AND files.lon > {} AND files.lon < {}
142                GROUP BY sites.station_num, id, model, lat, lon
143            "#, min_lat, max_lat, min_lon, max_lon);
144
145        let mut stmt = self.db_conn.prepare(&query_str)?;
146
147        let mut summaries = Self::process_summary_statement(&mut stmt)?;
148
149        // Haversine function in kilometers for the selected point
150        let distance = move |coords: &(f64, f64)| -> f64 {
151            let (clat, clon) = coords;
152            
153            let dlat = (lat - clat).to_radians();
154            let dlon = (lon - clon).to_radians();
155
156            let lat = lat.to_radians();
157            let clat = clat.to_radians();
158
159            let a = f64::powi(f64::sin(dlat / 2.0), 2) + f64::powi(f64::sin(dlon / 2.0), 2) * f64::cos(lat) * f64::cos(clat);
160
161            let rad = 6371.0088;
162            let c = 2.0 * f64::asin(f64::sqrt(a));
163            rad * c
164        };
165
166        summaries.sort_unstable_by(|left, right| {
167            let left_min_dist = left.coords.iter()
168                .map(distance)
169                .fold(1_000_000.0, |min, val| { if val < min { val } else { min }});
170
171            let right_min_dist = right.coords.iter()
172                .map(distance)
173                .fold(1_000_000.0, |min, val| { if val < min { val } else { min }});
174
175            left_min_dist.total_cmp(&right_min_dist)
176
177        });
178
179        Ok(summaries)
180    }
181
182    fn process_summary_statement(stmt: &mut Statement) -> Result<Vec<StationSummary>, BufkitDataErr> {
183
184        let mut vals: HashMap<StationNumber, StationSummary> = HashMap::new();
185
186        stmt.query_and_then([], Self::parse_row_to_entry)?
187            .for_each(|stn_entry| {
188                if let Ok(stn_entry) = stn_entry {
189                    if let Some(summary) = vals.get_mut(&stn_entry.station_num) {
190                        if let Some(id) = stn_entry.id {
191                            summary.ids.push(id);
192                        }
193
194                        if let Some(model) = stn_entry.model {
195                            summary.models.push(model);
196                        }
197
198                        summary.number_of_files += stn_entry.number_of_files;
199                    } else {
200                        vals.insert(stn_entry.station_num, StationSummary::from(stn_entry));
201                    }
202                }
203            });
204
205        let mut vals: Vec<StationSummary> = vals.into_iter().map(|(_, v)| v).collect();
206
207        vals.iter_mut().for_each(|summary| {
208            summary.ids.sort_unstable();
209            summary.ids.dedup();
210            summary.models.sort_unstable();
211            summary.models.dedup();
212        });
213
214        Ok(vals)
215    }
216
217    fn parse_row_to_entry(row: &rusqlite::Row) -> Result<StationEntry, rusqlite::Error> {
218        let station_num: StationNumber = row.get::<_, u32>(0).map(StationNumber::from)?;
219        let id: Option<String> = row.get(1)?;
220
221        let model: Option<Model> = row.get::<_, Option<String>>(2).and_then(|string_opt| {
222            string_opt
223                .map(|string| Model::from_str(&string).map_err(|_| rusqlite::Error::InvalidQuery))
224                .transpose()
225        })?;
226
227        let name: Option<String> = row.get(3)?;
228
229        let state: Option<StateProv> = row
230            .get::<_, String>(4)
231            .ok()
232            .and_then(|a_string| StateProv::from_str(&a_string).ok());
233
234        let notes: Option<String> = row.get(5)?;
235
236        let time_zone: Option<chrono::FixedOffset> =
237            row.get::<_, i32>(6).ok().and_then(|offset: i32| {
238                if offset < 0 {
239                    chrono::FixedOffset::west_opt(offset.abs())
240                } else {
241                    chrono::FixedOffset::east_opt(offset)
242                }
243            });
244
245        let lat: f64 = row.get(7)?;
246        let lon: f64 = row.get(8)?;
247        let number_of_files: u32 = row.get(9)?;
248
249        Ok(StationEntry {
250            station_num,
251            id,
252            model,
253            name,
254            state,
255            notes,
256            time_zone,
257            lat,
258            lon,
259            number_of_files,
260        })
261    }
262}
263
264#[cfg(test)]
265mod unit {
266    use crate::archive::unit::*; // test helpers.
267    use crate::{Model, StationNumber};
268
269    #[test]
270    fn test_summaries() {
271        let TestArchive {
272            tmp: _tmp,
273            mut arch,
274        } = create_test_archive().expect("Failed to create test archive.");
275
276        fill_test_archive(&mut arch);
277
278        let sums = arch.station_summaries().unwrap();
279
280        for sum in sums {
281            println!("{:?}", sum);
282
283            assert_eq!(sum.ids.len(), 1);
284            assert_eq!(sum.ids[0], "KMSO");
285
286            assert_eq!(sum.models.len(), 2);
287            assert!(sum.models.contains(&Model::GFS));
288            assert!(sum.models.contains(&Model::NAM));
289
290            assert_eq!(sum.station_num, StationNumber::new(727730));
291            assert_eq!(sum.number_of_files, 6);
292            assert!(sum.name.is_none());
293            assert!(sum.notes.is_none());
294            assert!(sum.time_zone.is_none());
295            assert!(sum.state.is_none());
296        }
297    }
298}
bufkit_data/archive/query/station_summary.rs

bufkit_data/archive/query/
station_summary.rs
