php - 按顺序分组纬度和经度对

Question

我有一个表，其中每 x 秒写入一个位置。该表由以下列组成:

lat  | lon  | time  | .....

最终目标是使轨道在 map 上可见，并在设备停止显示图像的点上显示。在随着时间的推移有多个条目的点上，应将行添加到一起以在该位置显示具有“timeSpent”的行，这是同一位置不同行的差异。所以:

lat  | lon  | timeFirst | timeSpent

例如这是“原始”表:

x1,y1, 13:00:12  // Point 1
x1,y2, 13:01:34  // Point 2
x3,y3, 13:02:01  // Point 3
x3,y3, 13:03:03  // Point 3
x3,y3, 13:04:34  // Point 3
x3,y3, 13:05:12  // Point 3
x4,y6, 13:06:23  // Point 4

第 3 点有多个条目。应该一起加入到下表中:

    x1,y1, 13:00:12, null
    x1,y2, 13:01:34, null
    x3,y3, 13:02:01, 13:05:12
    x4,y6, 13:06:23, null

我真的很想有一个生成结果表的 native MySQL 查询。但如果有必要，我可以使用 PHP 来解析源表。

谁能帮我弄到想要的 table ？

Answer 1

您的示例中不清楚的问题是，如果您向其中添加另一行值:

x3,y3, 13:59:59  // Point 3

它仍然是点 3，但您会期望它被视为序列中的另一个点。为此，您应该有一个查询来准备这样的数据(注意 x3,y3 值):

+---------+-----+-----+| COUNTER | LAT | LON |+---------+-----+-----+|       1 | x1  | y1  ||       2 | x1  | y2  ||       3 | x3  | y3  ||       3 | x3  | y3  ||       3 | x3  | y3  ||       3 | x3  | y3  ||       4 | x4  | y6  ||       5 | x3  | y3  |+---------+-----+-----+

Once you have this, you can easily group by. The query that would help you do this is the following:

select
  if(@lat = lat and @lon = lon, @counter, @counter := @counter + 1) counter,
  @lat := lat lat, @lon := lon lon
from t, (select @lat := '', @lon := '', @counter := 0) init
order by time

因此，对于该查询，您需要做的就是将其分组并获取最小值和最大值(如果它们相等，则将它们归零)。因此，为了获得您正在寻找的结果，您应该运行以下查询:

select
  if(@lat = lat and @lon = lon, @counter, @counter := @counter + 1) counter,
  @lat := lat lat, @lon := lon lon, min(time) timeFirst,
  nullif(max(time), min(time)) timeSpent
from t, (select @lat := '', @lon := '', @counter := 0) init
group by counter, lat, lon
order by time

注意:这也将返回计数器列。您可以通过将先前的查询包装到另一个选择语句中来摆脱它，例如:

select lat, lon, timeFirst, timeSpent from ([previous query]) as FinalQuery

但这会大大降低性能。因此，我的建议是不要在您的应用程序中使用该列。

删除计数器列的结果:

+-----+-----+-----------+-----------+| LAT | LON | TIMEFIRST | TIMESPENT |+-----+-----+-----------+-----------+| x1  | y1  | 13:00:12  |           || x1  | y2  | 13:01:34  |           || x3  | y3  | 13:02:01  | 13:05:12  || x4  | y6  | 13:06:23  |           || x3  | y3  | 13:59:59  |           |+-----+-----+-----------+-----------+