rusqlite_gpkg/
sql_functions.rs

1use crate::error::Result;
2use crate::gpkg::gpkg_geometry_to_wkb;
3use geo_traits::{
4    CoordTrait, GeometryCollectionTrait, GeometryTrait, LineStringTrait, MultiLineStringTrait,
5    MultiPointTrait, MultiPolygonTrait, PointTrait, PolygonTrait,
6};
7use rusqlite::functions::{Context, FunctionFlags};
8use rusqlite::types::{Type, ValueRef};
9use rusqlite::{Connection, Error};
10use wkb::reader::Wkb;
11
12#[derive(Clone, Copy)]
13struct Bounds {
14    minx: f64,
15    maxx: f64,
16    miny: f64,
17    maxy: f64,
18}
19
20/// Register all spatial SQL helper functions in the provided connection.
21///
22/// Example:
23/// ```no_run
24/// use rusqlite::Connection;
25/// use rusqlite_gpkg::register_spatial_functions;
26///
27/// let conn = Connection::open_in_memory()?;
28/// register_spatial_functions(&conn)?;
29/// # Ok::<(), rusqlite_gpkg::GpkgError>(())
30/// ```
31pub fn register_spatial_functions(conn: &Connection) -> Result<()> {
32    register_st_minx(conn)?;
33    register_st_miny(conn)?;
34    register_st_maxx(conn)?;
35    register_st_maxy(conn)?;
36    register_st_isempty(conn)?;
37    Ok(())
38}
39
40pub(crate) fn register_st_minx(conn: &Connection) -> Result<()> {
41    register_bounds_component(conn, "ST_MinX", |b| b.minx)
42}
43
44pub(crate) fn register_st_miny(conn: &Connection) -> Result<()> {
45    register_bounds_component(conn, "ST_MinY", |b| b.miny)
46}
47
48pub(crate) fn register_st_maxx(conn: &Connection) -> Result<()> {
49    register_bounds_component(conn, "ST_MaxX", |b| b.maxx)
50}
51
52pub(crate) fn register_st_maxy(conn: &Connection) -> Result<()> {
53    register_bounds_component(conn, "ST_MaxY", |b| b.maxy)
54}
55
56pub(crate) fn register_st_isempty(conn: &Connection) -> Result<()> {
57    conn.create_scalar_function(
58        "ST_IsEmpty",
59        1,
60        FunctionFlags::SQLITE_DETERMINISTIC,
61        |ctx| {
62            let wkb = match wkb_from_ctx(ctx)? {
63                Some(wkb) => wkb,
64                None => return Ok(None),
65            };
66            let is_empty = bounds_from_geometry(&wkb).is_none();
67            Ok(Some(i64::from(is_empty)))
68        },
69    )?;
70    Ok(())
71}
72
73fn register_bounds_component<F>(conn: &Connection, name: &str, f: F) -> Result<()>
74where
75    F: Fn(Bounds) -> f64 + Copy + Send + Sync + 'static,
76{
77    conn.create_scalar_function(name, 1, FunctionFlags::SQLITE_DETERMINISTIC, move |ctx| {
78        let wkb = match wkb_from_ctx(ctx)? {
79            Some(wkb) => wkb,
80            None => return Ok(None),
81        };
82        Ok(bounds_from_geometry(&wkb).map(f))
83    })?;
84    Ok(())
85}
86
87fn wkb_from_ctx<'a>(ctx: &'a Context<'a>) -> std::result::Result<Option<Wkb<'a>>, Error> {
88    let value = ctx.get_raw(0);
89    match value {
90        ValueRef::Null => Ok(None),
91        ValueRef::Blob(blob) => {
92            let wkb = gpkg_geometry_to_wkb(blob)
93                .map_err(|err| Error::UserFunctionError(Box::new(err)))?;
94            Ok(Some(wkb))
95        }
96        _ => Err(Error::InvalidFunctionParameterType(0, Type::Blob)),
97    }
98}
99
100fn bounds_from_geometry<G: GeometryTrait<T = f64>>(geom: &G) -> Option<Bounds> {
101    use geo_traits::GeometryType as GeoType;
102
103    let mut bounds: Option<Bounds> = None;
104    match geom.as_type() {
105        GeoType::Point(point) => {
106            if let Some(coord) = point.coord() {
107                add_coord(&mut bounds, &coord);
108            }
109        }
110        GeoType::LineString(line) => {
111            for coord in line.coords() {
112                add_coord(&mut bounds, &coord);
113            }
114        }
115        GeoType::Polygon(poly) => {
116            if let Some(ring) = poly.exterior() {
117                add_line_string(&mut bounds, &ring);
118            }
119            for ring in poly.interiors() {
120                add_line_string(&mut bounds, &ring);
121            }
122        }
123        GeoType::MultiPoint(multi) => {
124            for point in multi.points() {
125                if let Some(coord) = point.coord() {
126                    add_coord(&mut bounds, &coord);
127                }
128            }
129        }
130        GeoType::MultiLineString(multi) => {
131            for line in multi.line_strings() {
132                add_line_string(&mut bounds, &line);
133            }
134        }
135        GeoType::MultiPolygon(multi) => {
136            for poly in multi.polygons() {
137                if let Some(ring) = poly.exterior() {
138                    add_line_string(&mut bounds, &ring);
139                }
140                for ring in poly.interiors() {
141                    add_line_string(&mut bounds, &ring);
142                }
143            }
144        }
145        GeoType::GeometryCollection(collection) => {
146            for sub_geom in collection.geometries() {
147                if let Some(sub_bounds) = bounds_from_geometry(&sub_geom) {
148                    merge_bounds(&mut bounds, sub_bounds);
149                }
150            }
151        }
152        GeoType::Rect(_) | GeoType::Triangle(_) | GeoType::Line(_) => {
153            // No GeoPackage geometry types should reach here.
154            unreachable!()
155        }
156    }
157
158    bounds
159}
160
161fn add_line_string<L: LineStringTrait<T = f64>>(bounds: &mut Option<Bounds>, line: &L) {
162    for coord in line.coords() {
163        add_coord(bounds, &coord);
164    }
165}
166
167fn add_coord<C: CoordTrait<T = f64>>(bounds: &mut Option<Bounds>, coord: &C) {
168    let (x, y) = coord.x_y();
169    match bounds {
170        Some(existing) => {
171            existing.minx = existing.minx.min(x);
172            existing.maxx = existing.maxx.max(x);
173            existing.miny = existing.miny.min(y);
174            existing.maxy = existing.maxy.max(y);
175        }
176        None => {
177            *bounds = Some(Bounds {
178                minx: x,
179                maxx: x,
180                miny: y,
181                maxy: y,
182            });
183        }
184    }
185}
186
187fn merge_bounds(bounds: &mut Option<Bounds>, other: Bounds) {
188    match bounds {
189        Some(existing) => {
190            existing.minx = existing.minx.min(other.minx);
191            existing.maxx = existing.maxx.max(other.maxx);
192            existing.miny = existing.miny.min(other.miny);
193            existing.maxy = existing.maxy.max(other.maxy);
194        }
195        None => *bounds = Some(other),
196    }
197}
198
199#[cfg(test)]
200mod tests {
201    use super::register_spatial_functions;
202    use crate::gpkg::wkb_to_gpkg_geometry;
203    use geo_types::{Geometry, GeometryCollection, MultiLineString, MultiPoint};
204    use geo_types::{LineString, Point};
205    use rusqlite::{Connection, params};
206    use wkb::reader::Wkb;
207
208    fn gpkg_blob_from_geometry<G: geo_traits::GeometryTrait<T = f64>>(
209        geometry: G,
210    ) -> crate::Result<Vec<u8>> {
211        let mut wkb = Vec::new();
212        wkb::writer::write_geometry(&mut wkb, &geometry, &Default::default())?;
213        let wkb = Wkb::try_new(&wkb)?;
214        wkb_to_gpkg_geometry(wkb, 4326)
215    }
216
217    #[test]
218    fn st_bounds_for_point() -> crate::Result<()> {
219        let conn = Connection::open_in_memory()?;
220        register_spatial_functions(&conn)?;
221
222        let point = Point::new(1.5, -2.0);
223        let blob = gpkg_blob_from_geometry(point)?;
224
225        let (minx, maxx, miny, maxy, empty): (f64, f64, f64, f64, i64) = conn.query_row(
226            "SELECT ST_MinX(?1), ST_MaxX(?1), ST_MinY(?1), ST_MaxY(?1), ST_IsEmpty(?1)",
227            params![blob],
228            |row| {
229                Ok((
230                    row.get(0)?,
231                    row.get(1)?,
232                    row.get(2)?,
233                    row.get(3)?,
234                    row.get(4)?,
235                ))
236            },
237        )?;
238
239        assert_eq!(minx, 1.5);
240        assert_eq!(maxx, 1.5);
241        assert_eq!(miny, -2.0);
242        assert_eq!(maxy, -2.0);
243        assert_eq!(empty, 0);
244        Ok(())
245    }
246
247    #[test]
248    fn st_is_empty_for_empty_linestring() -> crate::Result<()> {
249        let conn = Connection::open_in_memory()?;
250        register_spatial_functions(&conn)?;
251
252        let line: LineString<f64> = LineString::new(Vec::new());
253        let blob = gpkg_blob_from_geometry(line)?;
254
255        let (minx, empty): (Option<f64>, i64) =
256            conn.query_row("SELECT ST_MinX(?1), ST_IsEmpty(?1)", params![blob], |row| {
257                Ok((row.get(0)?, row.get(1)?))
258            })?;
259
260        assert!(minx.is_none());
261        assert_eq!(empty, 1);
262        Ok(())
263    }
264
265    #[test]
266    fn st_bounds_for_multipoint() -> crate::Result<()> {
267        let conn = Connection::open_in_memory()?;
268        register_spatial_functions(&conn)?;
269
270        let mp = MultiPoint::from(vec![Point::new(1.0, 5.0), Point::new(-2.0, 3.0)]);
271        let blob = gpkg_blob_from_geometry(mp)?;
272
273        let (minx, maxx, miny, maxy): (f64, f64, f64, f64) = conn.query_row(
274            "SELECT ST_MinX(?1), ST_MaxX(?1), ST_MinY(?1), ST_MaxY(?1)",
275            params![blob],
276            |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?)),
277        )?;
278
279        assert_eq!(minx, -2.0);
280        assert_eq!(maxx, 1.0);
281        assert_eq!(miny, 3.0);
282        assert_eq!(maxy, 5.0);
283        Ok(())
284    }
285
286    #[test]
287    fn st_bounds_for_multilinestring() -> crate::Result<()> {
288        let conn = Connection::open_in_memory()?;
289        register_spatial_functions(&conn)?;
290
291        let line_a = LineString::from(vec![(0.0, 0.0), (2.0, 1.0)]);
292        let line_b = LineString::from(vec![(-3.0, 4.0), (-1.0, 2.0)]);
293        let mls = MultiLineString(vec![line_a, line_b]);
294        let blob = gpkg_blob_from_geometry(mls)?;
295
296        let (minx, maxx, miny, maxy): (f64, f64, f64, f64) = conn.query_row(
297            "SELECT ST_MinX(?1), ST_MaxX(?1), ST_MinY(?1), ST_MaxY(?1)",
298            params![blob],
299            |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?)),
300        )?;
301
302        assert_eq!(minx, -3.0);
303        assert_eq!(maxx, 2.0);
304        assert_eq!(miny, 0.0);
305        assert_eq!(maxy, 4.0);
306        Ok(())
307    }
308
309    #[test]
310    fn st_bounds_for_geometry_collection() -> crate::Result<()> {
311        let conn = Connection::open_in_memory()?;
312        register_spatial_functions(&conn)?;
313
314        let point = Geometry::Point(Point::new(5.0, -1.0));
315        let line = Geometry::LineString(LineString::from(vec![(-2.0, 2.0), (1.0, 3.0)]));
316        let collection = GeometryCollection::from(vec![point, line]);
317        let blob = gpkg_blob_from_geometry(collection)?;
318
319        let (minx, maxx, miny, maxy): (f64, f64, f64, f64) = conn.query_row(
320            "SELECT ST_MinX(?1), ST_MaxX(?1), ST_MinY(?1), ST_MaxY(?1)",
321            params![blob],
322            |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?)),
323        )?;
324
325        assert_eq!(minx, -2.0);
326        assert_eq!(maxx, 5.0);
327        assert_eq!(miny, -1.0);
328        assert_eq!(maxy, 3.0);
329        Ok(())
330    }
331}
rusqlite_gpkg/sql_functions.rs

rusqlite_gpkg/
sql_functions.rs
