Skip to content

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 results in a planning error. SedonaDB does not recognize ARRAY as a valid function for this operation.

Example (Fails)
>>> 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 that contains the spatial data structures.

Example (Works)
>>> 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 results in an error.

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

Unsupported Example
>>> 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

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.

Working Example
# 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()

Reading Files Directly in SQL

You can query a file directly in a FROM clause by quoting its path or URL, with no separate read_* call. This works for Parquet out of the box:

>>> sd.sql("SELECT * FROM '/path/to/buildings.parquet' LIMIT 5").show()

The common single-file OGR formats (.fgb, .gpkg, .shp, .geojson) are auto-registered on each connection, so their file URLs resolve the same way. Reading them requires pyogrio to be installed — if it isn't, you get a clear error at read time. This behavior is experimental and may change:

>>> sd.sql("SELECT * FROM 'file:///path/to/roads.fgb'").show()

The geometry column name is set by the underlying OGR driver, not by SedonaDB: FlatGeobuf, GeoJSON, and Shapefile expose an unnamed geometry field, which reads back as wkb_geometry, whereas GeoPackage carries its stored geometry-column name (for example geom).