ST_KNN: add a geography kernel ("Can't execute ST_KNN() outside a spatial join" for geography args)
- Dominant language
- Rust
- Stars
- 503
- Forks
- 61
- Avg merge
- 2d 4h
- Merged PRs (30d)
- 90
Description
## Description
`ST_KNN` has no geography kernel. Calling it with geography arguments inside a join fails with:
```
Can't execute ST_KNN() outside a spatial join
```
even though the join *is* a spatial join — the geography arguments simply are not recognised by the KNN join planner, so the predicate falls through to a non-spatial join and then rejects itself.
## Reproduction
```python
import json, sedonadb
sd = sedonadb.connect()
opts = json.dumps({"geom_type": "Point", "bounds": [0, 0, 10, 10], "seed": 11})
sd.sql(f"SELECT id, ST_GeogFromWKB(ST_AsBinary(geometry)) AS geog, "
f"ST_GeomFromWKB(ST_AsBinary(geometry)) AS geom "
f"FROM sd_random_geometry('{opts}') LIMIT 50").to_view("t", overwrite=True)
sd.sql("SELECT id, geog, geom FROM t").to_view("u", overwrite=True)
# geometry: works
sd.sql("SELECT COUNT(*) FROM t a JOIN u b ON ST_KNN(a.geom, b.geom, 5, true)").to_pandas()
# -> 250
# geography: fails
sd.sql("SELECT COUNT(*) FROM t a JOIN u b ON ST_KNN(a.geog, b.geog, 5, false)").to_pandas()
# -> SedonaError: Can't execute ST_KNN() outside a spatial join
```
The two queries are identical apart from the column type.
## Requested behaviour
`ST_KNN(geogA, geogB, k)` ranking neighbours by geodesic distance, consistent with `ST_Distance(geography, geography)` returning metres. The `use_spheroid` flag is meaningful only for geometry — for geography, spherical ranking is the definition, so a 3-argument geography kernel would be the natural signature.
## Also missing: ST_Union_Agg
For completeness, `ST_Union_Agg` has no geography kernel either:
```sql
SELECT ST_Union_Agg(geog) FROM t;
-- st_union_agg(geography): No kernel matching arguments
```
Lower priority — the aggregate that SpatialBench needs is `ST_Collect_Agg`, which already works on geography.
## Motivation
This is the last function blocking a complete geography version of the SpatialBench query suite. Q12 (rank trip pickups by average distance to their 5 nearest buildings) is a KNN join, and it is the one query of the twelve that has no geography formulation today — the other eleven are expressible, see
https://github.com/apache/sedona-spatialbench/blob/main/spatialbench-queries/print_geography_queries.py
The workaround is `ST_KNN(geom, geom, 5, TRUE)` plus `ST_Distance(geog, geog)` for the reported measure, which gives spherically-ranked neighbours over geometry columns — but it does not exercise the geography path, so it is not a fair like-for-like benchmark of geography KNN.
## Environment
- `sedonadb` 0.4.0 (PyPI wheel), Python 3.13, macOS arm64
- `sedonadb.__features__ == ['s2geography']`
Contributor guide
Research direction
Start by tracing the ST_KNN join planner and geography kernel dispatch, then compare them with the existing ST_Distance(geography, geography) path. Reproduce the two joins from the issue and consult spatialbench-queries/print_geography_queries.py for Q12. Done means the three-argument geography ST_KNN ranks spatial-join neighbours by geodesic distance without regressing the geometry form.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, rust, sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 50/100