ESQL calculate distance and speed based on two geo points about elasticsearch HOT 3 OPEN

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

from elasticsearch.

This is related to the scheduled work for ST_DISTANCE, which covers at least the distance calculation part. However calculating speed is a separate concern. At the simplest, this could be simply distance/duration, which does not require a new function, so could be considered complete once the ST_DISTANCE is done. However, there are two further considerations:

• The above assumes we have some way of having the two points and two timestamps in the same row, which could be true of some datasets, but is far likely to not be true when each document contains only the current location and timestamp. So we need a way of getting both the current and previous document into the same row.
• This feature feels like it is suitable for TSDB. During the original TSDB work we did a feature involving optimized geo_line aggregations, and those collected sequences of locations grouped by TSID into LineString geometries, ordered by time, including a feature for line simplification for very large geometries. There was a request to filter out outliers that deviate too much from the line, and the above feature sounds related, where we want speed outliers to be detected and highlighted. If the users of this feature are likely to use TSDB features, since they are working with time-ordered event data, perhaps we should consider a TSDB feature around outlier detection (both spatial, temporal and spatiotemporal/speed)?

from elasticsearch.

I also took a look at the linked enhancement request and the SPL query they use and have a few comments:

• It looks like the main missing feature from our side is eventstats, which I believe we're working on (calling 'inline stats' at the moment).
• The SPL query seems to do a lot of unnecessary inefficient work. In particular it appears to use eventstats to associate every single event with the same user with every other event of that user, and then calculate the distance, duration and speed between every combination. This is extremely inefficient, if we assume we only really need to consider consecutive events in time-order.

It would be far more efficient to use some time-ordering, or event ordering approach and look at windowing functions. @alex-spies pointed out the SQL functions LEAD and LAG as a good approach to this. They also seem generally useful for event data, log data and the security use cases.

from elasticsearch.