Working with SQL in SedonaDB

This page details several nuances of using SQL in SedonaDB.

Creating Arrays of Spatial Types in SQL

When constructing an array of spatial objects (like ST_POINT) in SedonaDB, you must use bracket notation [...] instead of the standard ARRAY() function.

The Incorrect Method: ARRAY()

Attempting to use the ARRAY() function to create an array of spatial types is not supported and will result in a planning error. SedonaDB will not recognize ARRAY as a valid function for this operation.

>>> sd.sql("SELECT ARRAY(ST_POINT(1,2), ST_POINT(3,4))")
...
Error during planning: Invalid function 'array'

The Correct Method: Brackets

To correctly build an array, enclose your comma-separated spatial objects in square brackets []. This syntax successfully creates a list containing the spatial data structures.

>>> sd.sql("SELECT [ST_POINT(1,2), ST_POINT(3,4)]").show()
┌──────────────────────────────────────────────────────────────────────────────────────────┐
│            make_array(st_point(Int64(1),Int64(2)),st_point(Int64(3),Int64(4)))           │
│                                           list                                           │
╞══════════════════════════════════════════════════════════════════════════════════════════╡
│ [0101000000000000000000f03f0000000000000040, 010100000000000000000008400000000000001040] │
└──────────────────────────────────────────────────────────────────────────────────────────┘

This approach correctly instructs SedonaDB to construct an array containing the two ST_POINT objects.

Temporary Views Not Supported in SQL

SedonaDB does not support the CREATE TEMP VIEW or CREATE TEMPORARY VIEW SQL commands. Executing these statements will result in an error.

Attempting to create a temporary view directly with sd.sql() will fail, as shown below.

>>> sd.sql("CREATE TEMP VIEW b AS SELECT * FROM '/path/to/building.parquet'")
Traceback (most recent call last):
  ...
sedonadb._lib.SedonaError: Temporary views not supported

Recommended Alternative

The correct way to create a view is to load your data and use to_view().

This approach provides the same functionality and is the standard practice in Spark-based environments.

# Step 1: Load your data into a DataFrame first
>>> building_df = sd.read_parquet("/path/to/building.parquet")

# Step 2: Register the DataFrame as a temporary view
>>> building_df.to_view("b")

# Step 3: You can now successfully query the view using SQL
>>> sd.sql("SELECT * FROM b LIMIT 5").show()