时间戳

首页 > 解决方案 > 寻找一种方法来组织从几天到会话的 GPS 数据（连续数据延伸到第二天的情况）

python - 寻找一种方法来组织从几天到会话的 GPS 数据（连续数据延伸到第二天的情况）

问题描述

我有数千个按日期和时间存储的记录 GPS 点，分成它们的源视频，每个视频都是每分钟创建的。

会话是 GPS 记录器开始到结束的时间，在少数情况下，从晚上 9 点到第二天凌晨 4 点。它们在时间上至少分开 6 小时，但通常更像 12 小时。

不幸的是，当我收集原始数据时，无法知道我是否将一个会话拆分到下一个会话，并且从一个日期开始并延伸到下一个日期的记录块至少有十几次被分解。现在我正试图弄清楚如何重新组织它们。

所有数据都存储在 sqlite 数据库中，其架构如下：

session
    date
    videos # relationship(Video)

Video:
    datetime # includes the minute of the day it was recorded
    coordinates # relationship(Coordinates)

Coordinate:
    latitude = string # (not float to avoid loss of precision)
    longitude = string 
    datetime = # includes the date, minute, and second

我尝试了这样的蛮力方法

def find_gaps():
   """
       Assume the bulk of a session's videos are in the correct
       location but the head or tail may not be

       12567
       12,567
       89012567
       89012, 567

   """

   for session in db.Session.query.order_by(db.Session.date.asc()):
       videos = list(session.videos)
       blocks = list()
       block = [videos.pop(0)]
       for video in videos:
           delta = video.datetime - block[-1].datetime
           minutes = delta.seconds / 60
           print(delta.days, minutes)
           if minutes > 15:
               print(video.name, minutes)
               blocks.append(block)
               block = [video]
           else:
               block.append(video)

       if block:
           blocks.append(block)

   if len(blocks) > 1:
       print(len(blocks))

但从未实现排序逻辑，因为我意识到这仍然会使两个日期重叠的会话保持分开。

我的另一个想法是在重叠的三天（前一天，当天，第二天）中遍历所有视频记录（日期时间 - 小时和分钟），但我想不出找到实际会话的方法.

无关紧要

我记录了我为 Lyft 开车的时间，以及我从丹佛到安克雷奇、阿拉斯加和回来的公路旅行（我喜欢开车）。公路旅行数据对我来说更重要，但也是最复杂的，尤其是去年的数据集。我从一块流浪的岩石上砸碎了我的天窗，然后撕碎了一个轮胎，所以我最终在 12 小时的跑步中一瘸一拐地行驶了约 3200 英里，并在 12 小时的跑步中使用了管道胶带，并以 70 公里/小时（约 45 英里/小时）的速度休息了 6 小时。

标签： pythonalgorithmsorting

解决方案

如果您有最新版本的 SQLite，它将支持窗口函数。如果您没有最新版本，则可以相当轻松地升级。我强烈建议使用它。

窗口函数在 SQL 中提供了一种有效地执行将数据集排序、然后比较邻居、执行运行选项卡等操作的方法。语法有点繁琐，解决它们自然会导致嵌套查询。这需要一点时间来适应。

首先，让我们编写一个查询，为我们Video提供上一个视频的日期时间（警告，所有查询都未经测试，可能会被破坏）

SELECT datetime
  , coordinates
  , lag(datetime) OVER (ORDER BY strftime('%s',datetime)) as last_datetime
FROM Video

（请注意，当前行之前的默认帧是无界的，这意味着我们可以访问以前的视频，但不能访问未来的视频。我将再次使用这个事实，但不会注意它。）

现在让我们进行一个相同的查询，除了我们知道哪些条目启动了一个会话，我将定义为前一小时没有任何条目。

SELECT datetime
  , coordinates
  , CASE
       WHEN strftime('%s',datetime) - strftime('%s',last_datetime) < 3600
       THEN 0
       ELSE 1
    END as is_session_start
FROM (
    SELECT datetime
      , coordinates
      , lag(datetime) OVER (ORDER BY strftime('%s',datetime)) as last_datetime
    FROM Video
  ) AS video_and_prev_datetime

现在我们将用会话开始的日期时间标记每个视频。

SELECT datetime
  , coordinates
  , MAX (
      CASE
        WHEN is_session_start = 1
        THEN strftime('%s',datetime)
        ELSE 0
      END
    ) OVER (
      ORDER BY strftime('%s',datetime)
    ) AS session_start
FROM (
    SELECT datetime
      , coordinates
      , CASE
           WHEN strftime('%s',datetime) - strftime('%s',last_datetime) < 3600
           THEN 0
           ELSE 1
        END as is_session_start
    FROM (
        SELECT datetime
          , coordinates
          , lag(datetime) OVER (ORDER BY strftime('%s',datetime)) as last_datetime
        FROM Video
      ) AS video_and_prev_datetime
  ) as video_and_session_start

现在您的视频已按会话开始时间分类。

如果您更喜欢在代码中执行此操作，我建议您只进行排序Video，strftime('%s',datetime)然后在代码中执行相同类型的“与上一个比较，跟踪会话开始并添加它们”。但鉴于随着时间的推移，越来越多的数据库中出现了窗口函数，您不妨像我刚才演示的那样了解如何使用它们。

推荐阅读