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

Question

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

我的表格是这样的:

id  latitude            longitude           speed   time-----------------------------------------------------------------------304 52.388983333333336  17.025338333333334  33.67   2014-03-26 08:00:04305 52.39029            17.023776666666667  34.65   2014-03-26 08:00:14306 52.391035           17.021631666666668  32.91   2014-03-26 08:00:24307 52.39103166666666   17.01917            30.03   2014-03-26 08:00:34308 52.39089833333333   17.01698            29.33   2014-03-26 08:00:44309 52.390593333333335  17.01532            9.54    2014-03-26 08:00:54310 52.39071333333333   17.015056666666666  0       2014-03-26 08:01:04311 52.39105333333333   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.39232333333333   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.014471666666665  14.11   2014-03-26 08:03:45327 52.392316666666666  17.012883333333335  27.47   2014-03-26 08:03:55328 52.39194333333333   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.999178333333333  16.56   2014-03-26 08:05:05335 52.39223666666667   16.998796666666667  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.995643333333334  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.992806666666667  16.86   2014-03-26 08:06:15342 52.393746666666665  16.992065           4.72    2014-03-26 08:06:25

I have a MySQL query that should do this, but it kills my database.

select speed, latitude, longitude, min(time) as StartTime, max(time) as EndTimefrom (select t.*,    (select count(*)    from positions t2    where t2.time  0    ) as grp    from positions t    ) t    where time >= '$from' AND time = min(time) + interval 1 minute

I'm looking for other ideas how to find this positions with speed 0 and idle time.

For example:

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