mysql计算返回位置的距离

Question

这不是重复的： Find distance between two points using latitude and longitude in mysql

我尝试了查询，但它不起作用，我得到空数据集和 0 作为答案。

查看答案，这就是解决方案，我不知道为什么这个问题被标记为来自 Shadow 的重复

我有一个具有以下结构的表：

table: distance

id int(10) primary key

lat float,

lon float,

time timestamp

我有一个 android 应用程序，可以 ping 我的服务器并在每次更改位置时添加一条记录。

例如

id:1, lat:40.753979, lon:-111.881721, time = 1571004620

id:2, lat:40.753979, lon:-111.883721, time = 1571004630

id: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&nbsp; &nbsp; id,&nbsp; &nbsp; time,&nbsp; &nbsp; SUM(&nbsp; &nbsp; &nbsp; &nbsp; CASE WHEN lag_lat IS NULL&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; THEN 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE ST_Distance_Sphere(point(lag_lon, lag_lat), point(lon, lat))&nbsp; &nbsp; &nbsp; &nbsp; END&nbsp; &nbsp; ) OVER (ORDER BY time) cumulative_distanceFROM (&nbsp; &nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; d.*,&nbsp; &nbsp; &nbsp; &nbsp; LAG(lat) OVER(ORDER BY time) lag_lat,&nbsp; &nbsp; &nbsp; &nbsp; LAG(lon) OVER(ORDER BY time) lag_lon&nbsp; &nbsp; FROM distance d)&nbsp; xDB Fiddle 上的演示：| id&nbsp; | time&nbsp; &nbsp; &nbsp; &nbsp;| cumulative_distance || --- | ---------- | ------------------- || 1&nbsp; &nbsp;| 1571004620 | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| 2&nbsp; &nbsp;| 1571004630 | 168.37177423236415&nbsp; || 3&nbsp; &nbsp;| 1571004640 | 336.7435484657999&nbsp; &nbsp;|| 4&nbsp; &nbsp;| 1571004650 | 673.4870969097663&nbsp; &nbsp;|在早期版本的 MySQL 中，您需要模拟窗口函数：LAG()可以替换为LEFT JOIN带有条件的self-和条件NOT EXISTS中的相关子查询ON变量可以模拟累积SUM询问：SELECT&nbsp; &nbsp; id,&nbsp; &nbsp; time,&nbsp; &nbsp; @running_distance := @running_distance + CASE&nbsp; &nbsp; &nbsp; &nbsp; WHEN lag_lat IS NULL THEN 0&nbsp; &nbsp; &nbsp; &nbsp; ELSE ST_Distance_Sphere(point(lag_lon, lag_lat), point(lon, lat))&nbsp; &nbsp; END running_distanceFROM (&nbsp; &nbsp; SELECT&nbsp; &nbsp; &nbsp; &nbsp; d.id,&nbsp; &nbsp; &nbsp; &nbsp; d.time,&nbsp; &nbsp; &nbsp; &nbsp; d.lat,&nbsp; &nbsp; &nbsp; &nbsp; d.lon,&nbsp; &nbsp; &nbsp; &nbsp; d_lag.lat lag_lat,&nbsp; &nbsp; &nbsp; &nbsp; d_lag.lon lag_lon&nbsp; &nbsp;&nbsp; &nbsp; FROM distance d&nbsp; &nbsp; LEFT JOIN distance d_lag&nbsp; &nbsp; &nbsp; &nbsp; ON&nbsp; d_lag.time < d.time&nbsp; &nbsp; &nbsp; &nbsp; AND NOT EXISTS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT 1&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM distance d1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE d1.time < d.time AND d1.time > d_lag.time&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ORDER BY d.time) xCROSS JOIN (SELECT @running_distance := 0) y