jiayuasu opened a new pull request, #131: URL: https://github.com/apache/sedona-spatialbench/pull/131
Closes #125. Every SpatialBench query today is **geometry**: WKB columns are decoded with `ST_GeomFromWKB`, predicates use planar edges, and every distance threshold is an angle in degrees with a hand-rolled conversion — `0.45` "= 50 km", `0.045` "= 5 km", `0.0045` "≈ 500 m", `/ 0.000009` "1 m = 0.000009 degree". Areas in Q5 and Q9 come out in square degrees. Those factors only hold near the equator along longitude. `0.45°` is ~50 km tall but ~41 km wide at Sedona (34.87°N) and ~25 km wide at 60°N, and the generator scatters data across eight continent boxes spanning latitudes −56° to 78° — so a degree-based radius is a different real-world shape in every part of the dataset. Both Sedona engines now have a `GEOGRAPHY` type with geodesic edges and metric units, so this adds the geography counterpart of the suite. ## What's here - **`docs/geography-queries.md`** (+ `.zh.md` mirror, nav entries in `mkdocs.yml`) — defines the geography version of **all 12 queries**, with an engine support matrix, a per-query status, and a Known Issues section. - **`spatialbench-queries/print_geography_queries.py`** — implements the **11** whose SQL is valid on a geography-capable engine. Only Q12 is held back: it needs a geography `ST_KNN`, which no engine has, and a query no engine can run would only surface as a runtime error. Nothing else changes — `print_queries.py`, the benchmark harness, the CI matrix and the committed answers are all untouched. The two suites need **separate** answer sets: a 0.45° planar radius and a 50 000 m geodesic radius select different rows by construction, so their results are not row-for-row comparable. Wiring the suite into `run_benchmark.py` and generating geography ground truth is deliberately left for a follow-up. The geography base class deliberately does **not** subclass `SpatialBenchBenchmark` — that would silently reintroduce the geometry Q12 into this suite — so it borrows only the `queries()` reflection helper. As in the geometry suite, Q5 is the one query needing a SedonaDB-specific spelling (`ST_Collect_Agg` vs `ST_Collect(ARRAY_AGG(...))`). ## Validation Executed against SedonaDB 0.4.0 (`s2geography`) at SF0.1 and SF1 — not just generated and eyeballed. Ten of the twelve produce results: | Query | Result at SF1 | Against the committed geometry answer | |---|---|---| | Q2 | 541 | **exact match** | | Q3 | 22 rows | row count matches | | Q4 | 259 rows | **258 identical** (same zones, counts, order) + 1 spurious row | | Q1, Q5, Q7, Q8 | 100 rows each | differ as expected — metres/m² vs degrees | | Q6, Q10, Q11 | run | inflated by the polygon bug below | | Q9 | error | blocked by the join bug below | | Q12 | — | not implemented, needs geography `ST_KNN` | `ST_Length` and `ST_Area` now return metres and square metres, and Q7 drops the `/ 0.000009` fudge entirely. ## Upstream issues found while validating Four filed against `apache/sedona-db`, each with a self-contained repro (no SpatialBench data needed): - **[#1084](https://github.com/apache/sedona-db/issues/1084)** (bug) — a geography scalar function over the output of a geography spatial join fails with `Array input must not be empty`. The join is correct: Q9's self-join finds exactly the same 37 building pairs as the geometry suite. An equi-join with no matching rows behaves fine, so it's specific to the spatial join's empty output batches. **This is what blocks Q9.** - **[#1085](https://github.com/apache/sedona-db/issues/1085)** (bug) — one zone polygon of 156 095 (`z_zonekey` 150480, Shendi, Sudan) returns a **negative** geography `ST_Area` and matches points anywhere on Earth; `ST_Within` says a mid-Pacific point is inside a Sudanese county. It inflates Q4, Q6, Q10 and Q11 — Q11 counts 6 290 682 cross-zone trips against the geometry suite's 176 391. Simplifying the same ring restores a positive area of near-identical magnitude, so interior determination is being confused by fine ring detail rather than winding order. Dropping its row from Q4 leaves a result identical to the planar answer, which pins it as the sole cause. - **[#1086](https://github.com/apache/sedona-db/issues/1086)** (enhancement) — `ST_KNN` has no geography kernel; the last blocker for a complete suite. - **[#1087](https://github.com/apache/sedona-db/issues/1087)** (docs) — `st_collect_agg` and `st_makeline` are missing their geography kernels in the SQL reference. They *do* accept geography in 0.4.0, which is why Q5 and Q7 are implemented here rather than deferred. Spark-side gaps (`ST_Intersection`, `ST_ConvexHull`, `ST_X`/`ST_Y` on geography) are already covered by the open umbrella apache/sedona#2830, which the docs cite rather than duplicating. ## How to check it ```bash python3 spatialbench-queries/print_geography_queries.py SedonaDB # 11 queries python3 spatialbench-queries/print_geography_queries.py SedonaSpark mkdocs build --strict ``` Requires a SedonaDB wheel built with `s2geography` (`"s2geography" in sedonadb.__features__`) to run the SQL, and Apache Sedona ≥ 1.9.1 for the Spark dialect — the geography constructors landed in 1.8.0, the operators in 1.9.1. -- 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]
