从一系列lat，lon点计算蜂窝中的累积距离

Question

给定这样的表：

latitude longitude

我们想在这个表中添加第三列，它将存储运行距离

latitude longitude odometer

其中第i行的里程表计算如下：

odometer_i = odometer_{i-1} + distance(lat_i, lon_i, lat_{i-1}, lon_{i-1})
distance(x1, y1, x2, y2) = sqrt((x1 - x2) * (x1 - x2) + (y1 - y2) * (y1 - y2))
odometer_0 = 0 (first row)

如何做到这一点？

Answer 1

解决这个问题的关键是使用hive-lag和sum的窗口函数。

步骤1：第一步是枚举以前的lat，long除了当前的lat之外，long。这是使用滞后函数完成的，是解决这个问题的关键：

SELECT *,
lag(latitude, 1) over (order by timestamp) as previous_latitude,
lag(longitude, 1) over (order by timestamp) as previous_longitude
from table

其中，我们假设存在按时间顺序排列为lat，longs的timestamp列。

步骤2：下一步是将度数转换为弧度

select *,
radians(previous_latitude) as lat1,
radians(previous_longitude) as lon1,
radians(latitude) as lat2,
radians(longitude) as lon2
from step1

步骤3：接下来，我们使用haversine公式的一阶近似值计算(lat1, lon1)和(lat2, lon2)之间的距离：

select *, 
6378137.0 * sqrt(pow(lat1 - lat2, 2) + cos(lat1) * cos(lat2) * pow(lon1 - lon2, 2)) as distance
from step2

步骤4：最后我们计算distance列的运行和：

select *, sum(distance) over (order by timestamp) as odometer from step3