Support spatial distance search in ES|QL (ST_DISTANCE)

[craigtaverner]

Description

The Elasticsearch _search API supports distance filtering and sorting:

• Sorting using the _geo_distance sort field
• Filtering using geo_distance

In PostGIS there are two related functions:

• ST_DWITHIN for filtering by distance
• ST_DISTANCE for calculating a distance, also usable for sorting

These features can be implemented in ES|QL, but we believe we do not need to implement both functions, as we can perform Lucene-pushdown optimization identically in both cases, so we'll implement the more generic function only: double ST_DISTANCE(geomA, geomB).

This can be used for calculating a distance, filter and sorting based on the context in the query:

Distance calculations

FROM airports
| EVAL distance = ST_DISTANCE(location, city_location)

Filtering results

FROM airports
| WHERE ST_DISTANCE(location, TO_GEOPOINT("POINT(12 55)")) < 500000

Sorting results

FROM airports
| EVAL distance = ST_DISTANCE(location, TO_GEOPOINT("POINT(12 55)"))
| SORT distance ASC

Tasks

Beta Give feedback

1. Review existing Elasticsearch support for distance filtering and sorting
2. Review PostGIS and OGC specifications for these functions
3. Implement raw ST_DISTANCE function
4. Support distance filtering
5. Support distance calculations
6. Support distance sorting
7. Support Lucene push-down for distance filtering with ST_DISTANCE #109972
   :Analytics/ES|QL :Analytics/Geo >enhancement Team:Analytics +4
   
8. Documentation

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[craigtaverner]

We should also keep in mind use cases involving speed, as discussed at #108332 (comment)

[craigtaverner]

Since the lucene pushdown for spatial sort has it's own issue, now moved to 8.16, we'll mark this main issue as DONE for 8.15.