mysql - 在 MySQL 中选择下一个相关行

Question

我在 MySQL 5.7 中有一个空间数据集，其中包含以下列:id、deviceid、latlng_point、时间。 latlng_point 是一个地理空间点。

我想要实现的是计算点之间的距离。我不确定如何解决这个问题。

SELECT
    ST_DISTANCE_SPHERE(latlng_point, i want the next latlng_point here) AS distance
FROM points
WHERE deviceid = 1
ORDER BY time DESC;

在 PHP 中我会做这样的事情:

<?php
    $conn = new mysqli($host,$user,$pass,$db);

    $query = "SELECT latlng_point FROM points WHERE deviceid = 1...";

    $latlng_array = array();        
    if ($result = $conn->query($query)) {
        while ($row = $result->fetch_assoc()) {
            $latlng_array[] = $row;
        }
    }

    $distance = 0;
    for ($i = 0; $i < count($latlng_array) - 1; $i++) {
        $pt1 = $latlng_array[$i]['latlng_point'];
        $pt2 = $latlng_array[$i+1]['latlng_point'];

        $distance += haversine_function($pt1,$pt2);
    }
    echo "Distance: {$distance}";
?>

我正在尝试纯粹在 MySQL 中实现类似的目标。

Answer 1

试试这个:

SELECT SUM(ST_DISTANCE_SPHERE(p1.latlng_point, p2.latlng_point)) AS total_distance
FROM points p1
JOIN points p2 ON p2.id = (
    SELECT p.id
    FROM points p
    WHERE p.deviceid = p1.deviceid
      AND p.time > p1.time
    ORDER BY p.time ASC
    LIMIT 1
)
WHERE p1.deviceid = 1

(相关的)子查询应返回下一个点的id(按时间排序)。

我无法告诉您它是否真的有效，或者是否完全有效(无法测试)。

但是，您应该在 (deviceid, time) 上有一个索引 - 假设 id 是主键。