如何从数据库中选择汽车停止的位置

Question

我有一个MySQL表，里面有来自汽车GPS跟踪器的数据(纬度、lng、速度、时间(。我想选择汽车停止(速度=0(超过10分钟、30分钟、1小时等的位置(纬度、经度和时间(。

我的桌子是这样的：

id纬度经度速度时间-----------------------------------------------------------------------304 52.3888933333336 17.025338333333334 33.67 2014-03-26 08:00:04305 52.39029 17.0237766666667 34.65 2014-03-26 08:00:14306 52.391035 17.021631666668 32.91 2014-03-26 08:00:24307 52.39103166666666 17.01917 30.03 2014-03-26 08:00:34308 52.3908983333333 17.01698 29.33 2014-03-26 08:00:44309 52.390593333335 17.01532 9.54 2014-03-26 08:00:54310 52.39071333333333 17.015056666666666 0 2014-03-26 08:01:04311 52.39105333333 17.01499 10.3 2014-03-26 08:01:14312 52.391485 17.01488 7.82 2014-03-26 08:01:24313 52.391705 17.014815 0 2014-03-26 08:01:34314 52.391705 17.014815 0 2014-03-26 08:01:44315 52.391705 17.014815 0 2014-03-26 08:01:54316 52.391705 17.014815 0 2014-03-26 08:02:04317 52.391705 17.014815 0 2014-03-26 08:02:14318 52.391705 17.014815 0 2014-03-26 08:02:24319 52.39232333333 17.014648333333334 7.12 2014-03-26 08:02:34320 52.392345 17.014635 0 2014-03-26 08:02:44321 52.392345 17.014635 0 2014-03-26 08:02:54322 52.392345 17.014635 0 2014-03-26 08:03:04323 52.392345 17.014635 0 2014-03-26 08:03:15324 52.392345 17.014635 0 2014-03-26 08:03:25325 52.392345 17.014635 0 2014-03-26 08:03:35326 52.392558333333334 17.014471666665 14.11 2014-03-26 08:03:45327 52.392316666666 17.012883333333335 27.47 2014-03-26 08:03:55328 52.39194333333 17.010871666666667 28.93 2014-03-26 08:04:05329 52.39152333333333 17.00893 22.28 2014-03-26 08:04:15330 52.391575 17.007181666666668 27.01 2014-03-26 08:04:25331 52.39164 17.00501 26.48 2014-03-26 08:04:35332 52.39159333333333 17.002895 28.34 2014-03-26 08:04:45333 52.391641666666665 17.000795 26.39 2014-03-26 08:04:55334 52.392156666666665 16.99917833333333 16.56 2014-03-26 08:05:05335 52.39223666666667 16.99879666666667 0 2014-03-26 08:05:15336 52.39234 16.99819 15.38 2014-03-26 08:05:25337 52.39261166666667 16.996865 17.1 2014-03-26 08:05:35338 52.392896666666665 16.995643333334 20.91 2014-03-26 08:05:45339 52.39313666666666 16.99468 7.5 2014-03-26 08:05:55340 52.39331833333333 16.993918333333333 9.1 2014-03-26 08:06:05341 52.3936 16.992806666667 16.86 2014-03-26 08:06:15342 52.393746666665 16.992065 4.72 2014-03-26 08:06:25

我有一个MySQL查询应该这样做，但它会杀死我的数据库。

选择速度、纬度、经度、最小(时间(作为StartTime，最大(时间(为EndTime从(选择t.*，(选择计数(*(从位置t2其中t2.time 0)作为grp从位置t)t其中时间>='$from'AND时间=分钟(时间(+间隔1分钟

我正在寻找其他想法，如何找到速度为0和空闲时间的位置。

例如：

Speed: 0
StartTime: 2014-03-26 08:01:34
EndTime: 2014-03-26 08:02:24

寻求帮助，有什么想法吗？

Answer 1

选择空闲时间超过10分钟的仓位。内部查询选择所有位置和空闲时间，外部查询按最小空闲时间进行过滤。对于内部查询，一辆空闲的汽车的速度为0，并且在同一位置至少有2排。怠速时间是通过取相同位置的最大和最小时间之间的差值来计算的。

SELECT * FROM 
    (SELECT
        latitude,
        longitude,
        TIME_TO_SEC(TIMEDIFF(MAX(time),MIN(time))) idle_time
    FROM positions
    WHERE speed = 0
    GROUP BY latitude, longitude
    HAVING COUNT(*) > 1) t
WHERE idle_time > 600

正如评论中所指出的，上述查询假设车辆永远不会返回到同一位置。如果车辆有可能返回到同一位置，我们需要确保分组位置是连续的。这是在下面的查询中通过验证汽车在每个分组使用的最大和最小时间之间没有处于另一个位置来完成的。

未测试

SELECT * FROM 
    (SELECT
        latitude,
        longitude,
        TIME_TO_SEC(TIMEDIFF(max_time,min_time)) idle_time
    FROM (SELECT
        latitude,
        longitude,
        (SELECT MIN(time) 
            FROM positions p2 
            WHERE p2.latitude = p1.latitude
            AND p1.longitude = p2.longitude
            AND NOT EXISTS (SELECT 1 FROM positions p3 
            WHERE p3.time < p1.time AND p3.time > p2.time
            AND (p3.longitude != p2.longitude OR p3.latitude != p2.latitude))
        ) min_time,
        (SELECT MAX(time) 
            FROM positions p2 
            WHERE p2.latitude = p1.latitude
            AND p2.longitude = p1.longitude
            AND NOT EXISTS (SELECT 1 FROM positions p3 
            WHERE p3.time > p1.time AND p3.time < p2.time
            AND (p3.longitude != p2.longitude OR p3.latitude != p2.latitude))
        ) max_time
     FROM positions p1
     WHERE speed = 0) 
    GROUP BY latitude, longitude, min_time, max_time
    HAVING COUNT(*) > 1) t
WHERE idle_time > 600