Spatial ES|QL Grid Aggregations #123903
Description
Description
In the Query DSL there is support for three types of grid:
All three allow for both search and aggregations:
- Search for data intersecting a grid tile
- Aggregate data by grid tile
We wish to implement this in ES|QL, and the simplest initial implementation would require support for scalar functions:
ST_GEOHASH(geom, precision)ST_GEOTILE(geom, precision)ST_GEOHEX(geom, precision)
These could be used in WHER predicates as well as in aggregations. For the WHERE predicates we would wish to push this down to the Lucene query where possible, perhaps by re-writing to ST_INTERSECTS as we do for ST_DISTANCE.
For the aggregations, we could use the scalar function directly, for example:
FROM index
| WHERE ST_INTERSECTS(location, TO_GEOSHAPE("POLYGON(...)"))
| STATS
count = COUNT_DISTINCT(location),
centroid = ST_CENTROID_AGG(location)
BY ST_GEOHASH(location, 4)
| KEEP count, centroid
Which provides a count of geometries intersecting each tile as well as the centroid of all geometries in that tile.
If we wish to return the tile shape to the client, for example to draw it on a map, we could provide additional functions to convert the tile id into a geo_shape:
FROM index
| WHERE ST_INTERSECTS(location, TO_GEOSHAPE("POLYGON(...)"))
| EVAL geohash = ST_GEOHASH(location, 4)
| STATS
count = COUNT_DISTINCT(location),
centroid = ST_CENTROID_AGG(location)
BY ST_GEOHASH(location, 4)
| EVAL tile = ST_GEOHASH_BOUNDARY(geohash)
| KEEP count, centroid, tile