[I] Sedona parameterized queries [sedona]

[via GitHub]

MrPowers opened a new issue, #1781:
URL: https://github.com/apache/sedona/issues/1781

   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)
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscr...@sedona.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org