Sedona parameterized queries
- Dominant language
- Java
- Stars
- 2.4k
- Forks
- 784
- Avg merge
- 1d 12h
- Merged PRs (30d)
- 58
Description
Parameterized queries [were recently added to Spark](https://www.databricks.com/blog/parameterized-queries-pyspark) and allow for some really clean syntax when shifting from the Python API to the SQL API. We should consider adding this interface to Sedona.
## Status quo
Create a geometry object for Minnesota:
```python
minnesota = 'POLYGON((-96.4517 43.5008,-91.2195 43.5017,-91.3101 43.8226,…))’
```
Create a DataFrame:
```python
df = sedona.read.format("shapefile").option("charset", "UTF-8").load(user_uri + extract_dir)
```
Create a temporary view:
```python
df.createOrReplaceTempView('gauges')
```
Run a query with a f-string:
```python
query = sedona.sql(f'''
select geometry, Status, Waterbody, Observed
from gauges
where st_intersects(geometry, ST_GeomFromWKT('{minnesota}'))
''')
```
## How to make this more concise with a parameterized query
This could be nicer with a parameterized query:
```python
sedona.sql("""
select geometry, Status, Waterbody, Observed
from {df}
where st_intersects(geometry, ST_GeomFromWKT('{minnesota}'))
""", df=df, minnesota=minnesota)
```
The parameterized query saves you from creating the temporary view and using f-strings.
It would be great if the parameters were also sanitized to prevent SQL injection vulnerabilities.
## Another parameterized query example
This can be even cooler. Assume Minnesota is a geometry object as follows:
```python
minnesota_wkt = 'POLYGON((-96.4517 43.5008,-91.2195 43.5017,-91.3101 43.8226,…))’
minnesota = ST_GeomFromWKT(minnesota_wkt)
```
Then, we can pass the geometry object directly in the parameterized query:
```python
sedona.sql("""
select geometry, Status, Waterbody, Observed
from {df}
where st_intersects(geometry, minnesota)
""", df=df, minnesota=minnesota)
```
Contributor guide
Research direction
Start with the sedona.sql interface and compare it with Spark's parameterized-query behavior described in the issue. Define how DataFrame, geometry, and string parameters should work, then verify that the examples run without a temporary view or f-string and that parameter values are sanitized against SQL injection.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, spark, sql
- Domain
- api, data, databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 30/100