Spell the grouped convex hull as ST_Collect_Agg and align the documented queries (#142)
* Spell the grouped convex hull as ST_Collect_Agg and align the documented queries
Q5 computes a convex hull over each customer-month's dropoff locations. The base class -- documented as the Sedona/Spark SQL dialect -- spelled it as a scalar over a materialized array, ST_Collect(ARRAY_AGG(...)), while SedonaDBSpatialBenchBenchmark overrode Q5 for the sole purpose of spelling it as the native aggregate ST_Collect_Agg. Sedona Spark ships that aggregate too, registered in spark/common/src/main/scala/org/apache/spark/sql/sedona_sql/expressions/st_aggregates.scala as call_udf("ST_Collect_Agg", geometry), so the base dialect can say it directly and SedonaDB needs no override. ARRAY_AGG also builds a full array of geometries per group before ST_Collect runs, where a native aggregate accumulates into one collection incrementally.
DuckDB inherits the base Q5 and DuckDB spatial has no ST_Collect_Agg -- its ST_Collect is a scalar over a GEOMETRY[], and its geometry aggregates are ST_Union_Agg and ST_Envelope_Agg -- so the ARRAY_AGG spelling moves into a DuckDB override rather than disappearing. Each override now sits where the dialect genuinely differs: DuckDB has no ST_Collect_Agg, Databricks has neither and uses ST_Union_Agg, and SedonaDB runs the base suite unmodified. The Databricks comment is reworded to name the function the base now uses; its SQL is untouched.
With the override gone, SedonaDBSpatialBenchBenchmark had no queries left and is deleted. Its docstring claimed it existed to name the dialect, but dialect() is never called anywhere in the repo -- every mention is a definition, a docstring, or an unrelated local variable, and the harness gets its engine name from a string literal at run_benchmark.py:393 and its display label from separate maps. The class is therefore repointed to the base in the two places that referenced it, print_queries.py's CLI map (keeping the "SedonaDB" key, so `print_queries.py SedonaDB` still works) and run_benchmark.py's dialect map. This widens the diff into run_benchmark.py, which is otherwise untouched; the class is only deletable because the base now carries the aggregate spelling, so it is a direct consequence of this change rather than an unrelated cleanup. DuckDB and Databricks keep their classes because they still override real queries -- DuckDB q5 and q12, Databricks q5, q7 and q12 -- so only the empty one goes. Verified mechanically: the query dict get_sql_queries("sedonadb") returns is byte-identical before and after, same sha256 over all twelve queries.
dialect() is dead code on the remaining classes too, but that predates this change and is left alone rather than widening scope further.
This requires Sedona >= 1.8.1 for the base dialect. Measured, one Spark 3.5.3 session per version: 1.7.2 and 1.8.0 reject ST_Collect_Agg with UNRESOLVED_ROUTINE, 1.8.1 and 1.9.1 accept it. Nothing in the repo pins a Sedona version, docs/requirements.txt is an unpinned apache-sedona[db] that resolves to 1.9.1, and there is no Sedona Spark runner in the benchmark harness, so no engine the harness runs is affected. DuckDB in particular is unaffected because it now carries its own override.
The documented queries had drifted from the executable ones in two ways, both fixed here. Q5 could not run at all: the Queries page and notebooks/queries.ipynb spelled it ST_Collect(ST_GeomFromWKB(...)) inside an sd.sql(...) call, and SedonaDB has no ST_Collect, so it failed at planning time with "Invalid function 'st_collect'". Q7 ran but multiplied by 111111 where every executable query divides by 0.000009; since 1/0.000009 is 111111.111..., the documented Q7 was off by exactly 1e-6 relative -- not inside the CI tolerance of rtol=1e-6 but exactly on it, its detour_ratio reaching 1.000001e-06 and passing only because numpy's isclose adds an atol term.
The divide form is the suite-wide convention and 111111 appeared only in those three doc lines. It is used by the executable SQL (print_queries.py:165,328), by every other engine implementation (geopandas_queries.py:306, spatial_polars.py:331-333, pycanopy_queries.py:260), and by wherobots/benchmark-dashboard. The repo documents it as the convention in docs/geography-queries.md:37 and its table row "| Unit conversion in SQL | / 0.000009 | none needed |", and it is structural: the radii 0.45 (50 km), 0.045 (5 km) and 0.0045 (500 m) are all exact multiples of 0.000009.
Verified. The executable SQL each engine runs is unchanged except the base dialect's Q5: comparing every query before and after with comments and whitespace normalized away, SedonaDB, DuckDB and Databricks are all unchanged, and DuckDB's Q5 is byte-identical to what it ran before. On real SF1 data all three spellings -- the new base ST_Collect_Agg, the old ARRAY_AGG, and the Databricks ST_Union_Agg form run through Sedona Spark, which also has that function -- return bitwise identical results and match benchmark/answers/sf1/q5.csv, as do SedonaDB 0.4.1 and DuckDB 1.5.5 running their own dialects. All twelve documented queries were then executed against SedonaDB 0.4.1 at SF1 over all six tables, through both the documented `import sedona.db` and the standalone sedonadb package; all twelve run and all twelve match the committed answers under rtol=1e-6 with no atol slack, which Q7 previously did not. Q7 is now bit-for-bit identical to answers/sf1/q7.csv.
Stored notebook outputs were handled per cell. Q5's is left exactly as it was, because re-executing the corrected cell reproduces it byte for byte -- the committed output was already the right data attached to code that could not produce it. Q7's genuinely changed, since the constant moves the values in the sixth significant figure, so it is regenerated from a real execution of the corrected cell; the new values match answers/sf1/q7.csv exactly where the old ones did not.
Only the aggregate spelling and the metre constant change. SELECT lists, GROUP BY, HAVING, ORDER BY and LIMIT are untouched, the Chinese page gets the identical SQL edits with its translated comments left alone except the one stating the conversion, and benchmark/answers/ is not modified.
Signed-off-by: James Willis <james@wherobots.com>
* Refresh the Q7 sample output for the / 0.000009 conversion
The SQL was aligned to divide by 0.000009 but the rendered tables still
carried values computed under * 111111 (off by the 1.000001 ratio between
the two constants). Copied verbatim from the re-executed notebook cell.
---------
Signed-off-by: James Willis <james@wherobots.com>SpatialBench is a benchmark for assessing geospatial SQL analytics query performance across database systems. It provides a reproducible and scalable way to evaluate the performance of spatial data engines using realistic synthetic workloads.
Goals:
SpatialBench includes a set of 12 SQL queries. Because spatial sql syntaxes vary widely across systems, we provide a cli to print all 12 queries in the dialect of your choice. Currently supported dialects are:
We tried to vary the queries only as much as necessary to accommodate dialect differences.
Geopandas is obviously a distinct case, as it is not SQL-based, however, due to its popularity, we felt it was important to include it. Pandas/Geopandas users often hand optimize their queries in ways that SQL engines handle automatically. We felt hand-tuning the queries was unfair for this exercise, and tried to do as little of that as possible while still writing “idiomatic” pandas code. We would be interested in hearing feedback on this approach as well as seeing a “fully hand-optimized” version of the queries.
Spatial Polars, like Geopandas, is not SQL-based. It uses shapely to extend polars, enabling it to work with geospatial data similar to how Geopandas extends pandas. It is much newer and nowhere near as popular/tested as Geopandas, but is capable of computing all of the spatial bench queries, and has been included.
PyCanopy is a Rust-backed spatial query engine for Polars. It provides a dataframe-native API, lazy spatial query planning, and dynamic spatial indexing, and supports the complete SpatialBench query suite.
We welcome contributions and civil discussions on how to improve the queries and their implementations.
You can print the queries in your dialect of choice using the following command:
./spatialbench-queries/print_queries.py <dialect>
SpatialBench includes an automated benchmark framework that runs on GitHub Actions to verify that all queries are fully runnable across supported engines.
Note: The GitHub Actions benchmark is designed to validate correctness and runnability, not for serious performance comparisons. For meaningful performance benchmarks, please run SpatialBench on dedicated hardware with appropriate scale factors. See the Single Node Benchmarks page for detailed performance results.
The automated tests cover:
SpatialBench ships reference answers for every query under benchmark/answers/ (scale factors 1 and 10). They are the output of the canonical SedonaDB dialect, and the correctness job independently cross-checks them against every other engine wherever it can compute the query, so you can confirm that each engine returns the same result for the same query — not just that it runs. See the answers README for the format, provenance, and comparison rules.
You can view the latest results on the GitHub Actions page. Click on any successful workflow run to see the summary with:
You can trigger a benchmark run manually from the Actions tab with configurable options:
The benchmark data is automatically downloaded from Hugging Face and cached for subsequent runs.
SpatialBench defines a spatial star schema with the following tables:
| Table | Type | Abbr. | Description | Spatial Attributes | Cardinality per SF |
|---|---|---|---|---|---|
| Trip | Fact Table | t_ | Individual trip records | pickup & dropoff points | 6M × SF |
| Customer | Dimension | c_ | Trip customer info | None | 30K × SF |
| Driver | Dimension | s_ | Trip driver info | None | 500 × SF |
| Vehicle | Dimension | v_ | Trip vehicle info | None | 100 × SF |
| Zone | Dimension | z_ | Administrative zones (SF-aware scaling) | Polygon | Tiered by SF range (see below) |
| Building | Dimension | b_ | Building footprints | Polygon | 20K × (1 + log₂(SF)) |
The Zone table uses scale factor–aware generation so that zone granularity scales with dataset size and keeps query cost realistic. At small scales, this feels like querying ZIP-level units; at large scales, it uses coarser administrative units.
| Scale Factor (SF) | Zone Subtypes Included | Zone Cardinality |
|---|---|---|
| [0, 10) | microhood, macrohood, county | 156,095 |
| [10, 100) | + neighborhood | 455,711 |
| [100, 1000) | + localadmin, locality, region, dependency | 1,035,371 |
| [1000+) | + country | 1,035,749 |
This tiered scaling reflects geometry complexity and area distributions observed in the Overture division_area dataset which represents administrative boundaries, release version 2025-08-20.1.
Spatial Bench's data generator uses continent-bounded affines. Each continent is defined by a bounding polygon, ensuring generation mostly covers land areas and introducing the natural skew of real geographies.
Bounding polygons:
Africa: POLYGON ((-20.062752 -40.044425, 64.131567 -40.044425, 64.131567 37.579421, -20.062752 37.579421, -20.062752 -40.044425)) Europe: POLYGON ((-11.964479 37.926872, 64.144374 37.926872, 64.144374 71.82884, -11.964479 71.82884, -11.964479 37.926872)) South Asia: POLYGON ((64.58354 -9.709049, 145.526096 -9.709049, 145.526096 51.672557, 64.58354 51.672557, 64.58354 -9.709049)) North Asia: POLYGON ((64.495655 51.944267, 178.834704 51.944267, 178.834704 77.897255, 64.495655 77.897255, 64.495655 51.944267)) Oceania: POLYGON ((112.481901 -48.980212, 180.768942 -48.980212, 180.768942 -10.228433, 112.481901 -10.228433, 112.481901 -48.980212)) South America: POLYGON ((-83.833822 -56.170016, -33.904338 -56.170016, -33.904338 12.211188, -83.833822 12.211188, -83.833822 -56.170016)) South North America: POLYGON ((-124.890724 12.382931, -69.511192 12.382931, -69.511192 42.55308, -124.890724 42.55308, -124.890724 12.382931)) North North America: POLYGON ((-166.478008 42.681087, -52.053245 42.681087, -52.053245 72.659041, -166.478008 72.659041, -166.478008 42.681087))
SpatialBench inherits its speed and efficiency from the tpchgen-rs project, which is one of the fastest open-source data generators available.
Key performance benefits:
SpatialBench is a Rust-based fork of the tpchgen-rs project. It preserves the original’s high-performance, multi-threaded, streaming architecture, while extending it with a spatial star schema and geometry generation logic.
You can install the SpatialBench data generator using Cargo:
cargo install --path ./spatialbench-cli
For contribution or debugging, refer to the ARCHITECTURE.md guide.
Pre-generated datasets are published on Hugging Face at apache-sedona/spatialbench, organized as v<version>/sf<scale>/ (scale factors sf0.1, sf1, sf10, and sf100). If you'd rather not build the generator, download the data directly — for example, scale factor 1:
pip install huggingface-hub hf download apache-sedona/spatialbench --repo-type dataset \ --include "v0.1.0/sf1/**" --local-dir spatialbench-data
The tables land under spatialbench-data/v0.1.0/sf1/. This is the same data the automated benchmark uses. Or generate it yourself with the CLI:
spatialbench-cli -s 1 --format=parquet
spatialbench-cli -s 1 --format=parquet --tables trip,building --output-dir sf1-parquet
spatialbench-cli -s 10 --tables trip,building --parts 4
The generator cli itself supports generating multiple files via the --parts and --part flags. However, if you want to generate multiple files per table of roughly a specific size, you can use the --mb-per-file flag.
This is how data was generated for the benchmark results cited in the SedonaDB launch blog post.
spatialbench-cli --scale-factor 1 --mb-per-file 256 --output-dir sf1-parquet spatialbench-cli --scale-factor 10 --mb-per-file 256 --output-dir sf10-parquet
You can generate data directly to Amazon S3 or S3-compatible storage by providing an S3 URI as the output directory:
# Set AWS credentials export AWS_ACCESS_KEY_ID="your-access-key" export AWS_SECRET_ACCESS_KEY="your-secret-key" export AWS_REGION="us-west-2" # Must match your bucket's region # Generate to S3 spatialbench-cli --scale-factor 10 --mb-per-file 256 --output-dir s3://my-bucket/spatialbench/sf10 # For S3-compatible services (MinIO, etc.) export AWS_ENDPOINT="http://localhost:9000" spatialbench-cli --scale-factor 1 --output-dir s3://my-bucket/data
The S3 writer uses streaming multipart upload, buffering data in 32MB chunks before uploading parts. This ensures memory-efficient generation even for large datasets. All output formats (Parquet, CSV, TBL) are supported, and the generated files are byte-for-byte identical to local generation.
You can override these defaults at runtime by passing a YAML file via the --config flag:
spatialbench-cli -s 1 --format=parquet --tables trip,building --config spatialbench-config.yml
If --config is not provided, SpatialBench checks for ./spatialbench-config.yml. If absent, it falls back to built-in defaults.
For reference, see the provided spatialbench-config.yml.
See CONFIGURATION.md for more details about spatial data generation and the full YAML schema and examples.