ESQL calculate distance and speed based on two geo points

[tylerperk]

Description

For security use cases it it common to calculate the distance between two points (based on source IP addresses, typically) and the speed required to travel from one to the other. If the movement is "impossible" then that is a factor used to raise suspicion of malicious activity such as IP spoofing. This can be calculated in other query languages using multiple complex statements involving several math functions and magic numbers. At minimum we should make that possible but ideally we should encapsulate the math into a function that calculates this for you.

[elasticsearchmachine]

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

[craigtaverner]

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)?

[craigtaverner]

To get this to work in ES|QL we would need to support inline stats. But it would be even 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.