oxigdal_postgis/
query.rs

1//! Spatial query builder for PostGIS
2//!
3//! This module provides a fluent API for building spatial queries.
4
5use crate::connection::ConnectionPool;
6use crate::error::{QueryError, Result};
7use crate::sql::{ColumnName, TableName, WhereClause, functions};
8use crate::types::FeatureBuilder;
9use oxigdal_core::types::BoundingBox;
10use oxigdal_core::vector::feature::Feature;
11use oxigdal_core::vector::geometry::Geometry;
12
13/// Spatial query builder
14pub struct SpatialQuery {
15    table: TableName,
16    columns: Vec<String>,
17    where_clause: WhereClause,
18    geometry_column: String,
19    id_column: Option<String>,
20    limit: Option<usize>,
21    offset: Option<usize>,
22    order_by: Vec<String>,
23    srid: Option<i32>,
24}
25
26impl SpatialQuery {
27    /// Creates a new spatial query
28    pub fn new(table_name: impl Into<String>) -> Result<Self> {
29        Ok(Self {
30            table: TableName::new(table_name)?,
31            columns: vec!["*".to_string()],
32            where_clause: WhereClause::new(),
33            geometry_column: "geom".to_string(),
34            id_column: Some("id".to_string()),
35            limit: None,
36            offset: None,
37            order_by: Vec::new(),
38            srid: None,
39        })
40    }
41
42    /// Sets the geometry column name
43    pub fn geometry_column(mut self, column: impl Into<String>) -> Self {
44        self.geometry_column = column.into();
45        self
46    }
47
48    /// Sets the ID column name
49    pub fn id_column(mut self, column: impl Into<String>) -> Self {
50        self.id_column = Some(column.into());
51        self
52    }
53
54    /// Sets specific columns to select
55    pub fn select(mut self, columns: &[&str]) -> Self {
56        self.columns = columns.iter().map(|s| s.to_string()).collect();
57        self
58    }
59
60    /// Adds a WHERE condition
61    pub fn where_clause(mut self, condition: impl Into<String>) -> Self {
62        self.where_clause = self.where_clause.and(condition);
63        self
64    }
65
66    /// Adds a bounding box filter
67    pub fn where_bbox(mut self, bbox: &BoundingBox) -> Result<Self> {
68        let srid = self.srid.unwrap_or(4326);
69        self.where_clause = self.where_clause.bbox(&self.geometry_column, bbox, srid)?;
70        Ok(self)
71    }
72
73    /// Filters features that intersect with a geometry
74    pub fn where_intersects(mut self, _geometry: &Geometry) -> Result<Self> {
75        let condition = format!(
76            "ST_Intersects({}, {})",
77            ColumnName::new(&self.geometry_column)?.quoted(),
78            functions::geom_from_wkb(1, self.srid)
79        );
80        self.where_clause = self.where_clause.and(condition);
81        Ok(self)
82    }
83
84    /// Filters features that contain a geometry
85    pub fn where_contains(mut self, _geometry: &Geometry) -> Result<Self> {
86        let condition = format!(
87            "ST_Contains({}, {})",
88            ColumnName::new(&self.geometry_column)?.quoted(),
89            functions::geom_from_wkb(1, self.srid)
90        );
91        self.where_clause = self.where_clause.and(condition);
92        Ok(self)
93    }
94
95    /// Filters features within a geometry
96    pub fn where_within(mut self, _geometry: &Geometry) -> Result<Self> {
97        let condition = format!(
98            "ST_Within({}, {})",
99            ColumnName::new(&self.geometry_column)?.quoted(),
100            functions::geom_from_wkb(1, self.srid)
101        );
102        self.where_clause = self.where_clause.and(condition);
103        Ok(self)
104    }
105
106    /// Filters features within a distance
107    pub fn where_dwithin(mut self, _geometry: &Geometry, distance: f64) -> Result<Self> {
108        let condition = format!(
109            "ST_DWithin({}, {}, {distance})",
110            ColumnName::new(&self.geometry_column)?.quoted(),
111            functions::geom_from_wkb(1, self.srid)
112        );
113        self.where_clause = self.where_clause.and(condition);
114        Ok(self)
115    }
116
117    /// Sets the SRID for spatial operations
118    pub const fn srid(mut self, srid: i32) -> Self {
119        self.srid = Some(srid);
120        self
121    }
122
123    /// Sets the result limit
124    pub const fn limit(mut self, limit: usize) -> Self {
125        self.limit = Some(limit);
126        self
127    }
128
129    /// Sets the result offset
130    pub const fn offset(mut self, offset: usize) -> Self {
131        self.offset = Some(offset);
132        self
133    }
134
135    /// Adds an ORDER BY clause
136    pub fn order_by(mut self, column: impl Into<String>, ascending: bool) -> Self {
137        let direction = if ascending { "ASC" } else { "DESC" };
138        self.order_by.push(format!("{} {direction}", column.into()));
139        self
140    }
141
142    /// Orders by distance from a geometry
143    pub fn order_by_distance(mut self, _geometry: &Geometry) -> Result<Self> {
144        let distance_expr = format!(
145            "ST_Distance({}, {})",
146            ColumnName::new(&self.geometry_column)?.quoted(),
147            functions::geom_from_wkb(1, self.srid)
148        );
149        self.order_by.push(format!("{distance_expr} ASC"));
150        Ok(self)
151    }
152
153    /// Builds the SQL query
154    pub fn build_sql(&self) -> Result<String> {
155        let mut sql = String::from("SELECT ");
156
157        // Add columns
158        sql.push_str(&self.columns.join(", "));
159
160        // FROM clause
161        sql.push_str(&format!(" FROM {}", self.table));
162
163        // WHERE clause
164        if let Some(where_sql) = self.where_clause.build() {
165            sql.push(' ');
166            sql.push_str(&where_sql);
167        }
168
169        // ORDER BY clause
170        if !self.order_by.is_empty() {
171            sql.push_str(" ORDER BY ");
172            sql.push_str(&self.order_by.join(", "));
173        }
174
175        // LIMIT clause
176        if let Some(limit) = self.limit {
177            sql.push_str(&format!(" LIMIT {limit}"));
178        }
179
180        // OFFSET clause
181        if let Some(offset) = self.offset {
182            sql.push_str(&format!(" OFFSET {offset}"));
183        }
184
185        Ok(sql)
186    }
187
188    /// Executes the query and returns features
189    pub async fn execute(self, pool: &ConnectionPool) -> Result<Vec<Feature>> {
190        let sql = self.build_sql()?;
191        let client = pool.get().await?;
192
193        let rows = client
194            .query(&sql, &[])
195            .await
196            .map_err(|e| QueryError::ExecutionFailed {
197                message: e.to_string(),
198            })?;
199
200        let mut features = Vec::with_capacity(rows.len());
201        for row in rows {
202            let feature = FeatureBuilder::new()
203                .geometry_column(&self.geometry_column)
204                .build_from_row(&row)?;
205            features.push(feature);
206        }
207
208        Ok(features)
209    }
210
211    /// Executes the query and returns count
212    pub async fn count(self, pool: &ConnectionPool) -> Result<i64> {
213        let mut sql = String::from("SELECT COUNT(*) FROM ");
214        sql.push_str(&self.table.to_string());
215
216        if let Some(where_sql) = self.where_clause.build() {
217            sql.push(' ');
218            sql.push_str(&where_sql);
219        }
220
221        let client = pool.get().await?;
222        let row = client
223            .query_one(&sql, &[])
224            .await
225            .map_err(|e| QueryError::ExecutionFailed {
226                message: e.to_string(),
227            })?;
228
229        let count: i64 = row.get(0);
230        Ok(count)
231    }
232}
233
234/// Spatial join builder
235pub struct SpatialJoin {
236    left_table: TableName,
237    right_table: TableName,
238    join_type: JoinType,
239    join_condition: String,
240    where_clause: WhereClause,
241    limit: Option<usize>,
242}
243
244/// Join type
245#[derive(Debug, Clone, Copy)]
246pub enum JoinType {
247    /// INNER JOIN
248    Inner,
249    /// LEFT JOIN
250    Left,
251    /// RIGHT JOIN
252    Right,
253}
254
255impl JoinType {
256    const fn as_str(&self) -> &'static str {
257        match self {
258            Self::Inner => "INNER JOIN",
259            Self::Left => "LEFT JOIN",
260            Self::Right => "RIGHT JOIN",
261        }
262    }
263}
264
265impl SpatialJoin {
266    /// Creates a new spatial join
267    pub fn new(left_table: impl Into<String>, right_table: impl Into<String>) -> Result<Self> {
268        Ok(Self {
269            left_table: TableName::new(left_table)?,
270            right_table: TableName::new(right_table)?,
271            join_type: JoinType::Inner,
272            join_condition: String::new(),
273            where_clause: WhereClause::new(),
274            limit: None,
275        })
276    }
277
278    /// Sets the join type
279    pub const fn join_type(mut self, join_type: JoinType) -> Self {
280        self.join_type = join_type;
281        self
282    }
283
284    /// Joins on intersecting geometries
285    pub fn on_intersects(
286        mut self,
287        left_geom: impl Into<String>,
288        right_geom: impl Into<String>,
289    ) -> Result<Self> {
290        let left = ColumnName::new(left_geom)?;
291        let right = ColumnName::new(right_geom)?;
292        self.join_condition = format!("ST_Intersects({}, {})", left.quoted(), right.quoted());
293        Ok(self)
294    }
295
296    /// Adds a WHERE condition
297    pub fn where_clause(mut self, condition: impl Into<String>) -> Self {
298        self.where_clause = self.where_clause.and(condition);
299        self
300    }
301
302    /// Sets the limit
303    pub const fn limit(mut self, limit: usize) -> Self {
304        self.limit = Some(limit);
305        self
306    }
307
308    /// Builds the SQL query
309    pub fn build_sql(&self) -> Result<String> {
310        let mut sql = format!(
311            "SELECT * FROM {} {} {} ON {}",
312            self.left_table,
313            self.join_type.as_str(),
314            self.right_table,
315            self.join_condition
316        );
317
318        if let Some(where_sql) = self.where_clause.build() {
319            sql.push(' ');
320            sql.push_str(&where_sql);
321        }
322
323        if let Some(limit) = self.limit {
324            sql.push_str(&format!(" LIMIT {limit}"));
325        }
326
327        Ok(sql)
328    }
329}
330
331#[cfg(test)]
332mod tests {
333    use super::*;
334
335    #[test]
336    fn test_spatial_query_basic() {
337        let query = SpatialQuery::new("buildings").ok();
338        assert!(query.is_some());
339        let query = query.expect("query creation failed");
340
341        let sql = query.build_sql().ok();
342        assert!(sql.is_some());
343        let sql = sql.expect("SQL build failed");
344        assert!(sql.contains("SELECT"));
345        assert!(sql.contains("FROM"));
346        assert!(sql.contains("buildings"));
347    }
348
349    #[test]
350    fn test_spatial_query_limit() {
351        let query = SpatialQuery::new("buildings").ok();
352        assert!(query.is_some());
353        let query = query.expect("query creation failed").limit(10);
354
355        let sql = query.build_sql().ok();
356        assert!(sql.is_some());
357        let sql = sql.expect("SQL build failed");
358        assert!(sql.contains("LIMIT 10"));
359    }
360
361    #[test]
362    fn test_spatial_query_where() {
363        let query = SpatialQuery::new("buildings").ok();
364        assert!(query.is_some());
365        let query = query
366            .expect("query creation failed")
367            .where_clause("id > 10");
368
369        let sql = query.build_sql().ok();
370        assert!(sql.is_some());
371        let sql = sql.expect("SQL build failed");
372        assert!(sql.contains("WHERE"));
373        assert!(sql.contains("id > 10"));
374    }
375
376    #[test]
377    fn test_spatial_join() {
378        let join = SpatialJoin::new("parcels", "buildings").ok();
379        assert!(join.is_some());
380        let join = join
381            .expect("join creation failed")
382            .on_intersects("parcels.geom", "buildings.geom")
383            .ok();
384        assert!(join.is_some());
385
386        let join = join.expect("join on_intersects failed");
387        let sql = join.build_sql().ok();
388        assert!(sql.is_some());
389        let sql = sql.expect("SQL build failed");
390        assert!(sql.contains("INNER JOIN"));
391        assert!(sql.contains("ST_Intersects"));
392    }
393}
oxigdal_postgis/query.rs

oxigdal_postgis/
query.rs
