jiayuasu opened a new pull request, #124: URL: https://github.com/apache/sedona-spatialbench/pull/124
## Summary Closes #122. Several SpatialBench queries return result sets that grow with the scale factor, so a harness that materializes the result ends up measuring driver↔engine bandwidth instead of spatial query performance: - **q7** — one row per trip (~6M at SF1) - **q10** — one row per zone (~156K at SF1) - **q12** — five rows per trip (~30M at SF1) - **q1, q5, q8, q9** — also scale unboundedly ## Approach Bound every unbounded query to **at most 100 rows**, TPC-H / TPC-DS style, by adding `LIMIT 100` after the existing `ORDER BY`. This preserves all spatial computation — the scan, per-row `ST_*` evaluation, spatial joins, and aggregations all sit *upstream* of the sort, so only the driver-side materialization is capped, not the work being benchmarked. | Query | Change | Top-100 ranked by | | --- | --- | --- | | q1 | `LIMIT` | closest trips to city center | | q5 | `LIMIT` + `pickup_month` tiebreak; ordering aligned to `dropoff_count` | busiest repeat customer-months | | q7 | `LIMIT` | highest detour ratio | | q8 | `LIMIT` | busiest buildings | | q9 | `LIMIT` | most-overlapping building pairs | | q10 | `LIMIT` | zones by avg trip duration | | q12 | redesigned (see below) | most-isolated pickups | q2, q3, q4, q6, and q11 were already bounded and are unchanged. ### q12 redesign q12 is inherently "5 per trip", so a plain output `LIMIT` would just return the 100 globally-closest pickup–building pairs (all ≈0 distance — degenerate). Instead it now runs the KNN join, then **averages each pickup's 5 nearest-building distances** and ranks pickups by that average descending. The `LIMIT` therefore sits above a `GROUP BY` aggregation — the full KNN join is still performed, and the result is a meaningful "most isolated pickups" ranking (a k-NN local-density measure). ### Determinism Added total-order tiebreakers (`pickup_month` for q5, `t_tripkey` for q12) so the top-100 is reproducible across engines. A nice side effect: with a total order plus a fixed `LIMIT`, every engine should now return the identical 100 rows, which makes cross-engine answer verification feasible. ## Files changed - `spatialbench-queries/print_queries.py` — base + Databricks / DuckDB / SedonaDB overrides - `spatialbench-queries/geopandas_queries.py`, `spatial_polars.py`, `pycanopy_queries.py` - `notebooks/queries.ipynb`, `docs/queries.md`, `docs/queries.zh.md`, `docs/index.md`, `docs/index.zh.md` ## Notes The q5 (re-ordered) and q12 (redesigned) sample outputs in the docs/notebook genuinely change; those two rendered tables were cleared with a "regenerate by running the notebook" note rather than fabricating values. The other five queries' `show(3)` samples are unchanged (same top-3) and were kept. -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
