apache / apache/sedona-db

ST_KNN: add a geography kernel ("Can't execute ST_KNN() outside a spatial join" for geography args)

Open
#1,086 1 comment 0 reactions 0 assignees View on GitHub
enhancement
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.