jiayuasu opened a new issue, #1086:
URL: https://github.com/apache/sedona-db/issues/1086
## 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']`
--
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]
