mysql计算返回位置的距离

Question

这不是重复的： Find distance between two points using latitude and longitude in mysql我尝试了查询，但它不起作用，我得到空数据集和 0 作为答案。查看答案，这就是解决方案，我不知道为什么这个问题被标记为来自 Shadow 的重复我有一个具有以下结构的表：table: distanceid int(10) primary keylat float,lon float,time timestamp我有一个 android 应用程序，可以 ping 我的服务器并在每次更改位置时添加一条记录。例如id:1, lat:40.753979, lon:-111.881721, time = 1571004620id:2, lat:40.753979, lon:-111.883721, time = 1571004630id:3, lat:40.753979, lon:-111.885721, time = 1571004640如果我走一个方向，我可以计算总距离：/* in my python script*/startLat,startLon = select lat,lon from distance where time >= 1571004620 order by time limit 1;endLat,endLon = select lat,lon from distance where time <= 1571004640 order by time desc limit limit 1;然后我可以减去两个坐标，最终得到0.004000的经度距离问题：如果我添加：id:4, lat:40.753979, lon:-111.881721, time = 1571004650那么我应该得到：0.008000但我得到 0 因为位置 1 与位置 4 相同

Answer 1

您可以利用自 5.7 起提供的 MySQL 空间支持在数据库中执行整个计算。函数ST_Distance_Sphere()可用于计算距离。

您实际上需要距离的累积总和（这需要窗口函数，自 MySQL 8.0 起可用）。

考虑：

SELECT

    id,

    time,

    SUM(

        CASE WHEN lag_lat IS NULL 

            THEN 0

            ELSE ST_Distance_Sphere(point(lag_lon, lag_lat), point(lon, lat))

        END

    ) OVER (ORDER BY time) cumulative_distance

FROM (

    SELECT

        d.*,

        LAG(lat) OVER(ORDER BY time) lag_lat,

        LAG(lon) OVER(ORDER BY time) lag_lon

    FROM distance d

)  x

DB Fiddle 上的演示：

| id  | time       | cumulative_distance |

| --- | ---------- | ------------------- |

| 1   | 1571004620 | 0                   |

| 2   | 1571004630 | 168.37177423236415  |

| 3   | 1571004640 | 336.7435484657999   |

| 4   | 1571004650 | 673.4870969097663   |

在早期版本的 MySQL 中，您需要模拟窗口函数：

LAG()可以替换为LEFT JOIN带有条件的self-和条件NOT EXISTS中的相关子查询ON

变量可以模拟累积SUM

询问：

SELECT

    id,

    time,

    @running_distance := @running_distance + CASE

        WHEN lag_lat IS NULL THEN 0

        ELSE ST_Distance_Sphere(point(lag_lon, lag_lat), point(lon, lat))

    END running_distance

FROM (

    SELECT

        d.id,

        d.time,

        d.lat,

        d.lon,

        d_lag.lat lag_lat,

        d_lag.lon lag_lon   

    FROM distance d

    LEFT JOIN distance d_lag

        ON  d_lag.time < d.time

        AND NOT EXISTS (

            SELECT 1 

            FROM distance d1

            WHERE d1.time < d.time AND d1.time > d_lag.time

        )

    ORDER BY d.time

) x

CROSS JOIN (SELECT @running_distance := 0) y