android - 根据存储在 SQLite 数据库中的位置(纬度和经度)和时间计算速度

Question

我有一个具有以下数据格式的 SQLite 数据库

...
2014-02-17T11:06:22.000-05:00 , Vehicle3, 40.820890, -73.935900
2014-02-17T11:06:23.000-05:00 , Vehicle1, 40.803433, -73.945087
2014-02-17T11:06:17.000-05:00 , Vehicle2, 40.798135, -73.946201
2014-02-17T11:10:10.000-05:00 , Vehicle3, 40.820890, -73.935900
2014-02-17T11:10:07.000-05:00 , Vehicle1, 40.802197, -73.945343
2014-02-17T11:09:59.000-05:00 , Vehicle2, 40.804895, -73.941317
2014-02-17T11:13:27.000-05:00 , Vehicle3, 40.820890, -73.935900
2014-02-17T11:13:17.000-05:00 , Vehicle1, 40.794255, -73.951131
2014-02-17T11:13:09.000-05:00 , Vehicle2, 40.810051, -73.937497
2014-02-17T11:15:37.000-05:00 , Vehicle3, 40.820890, -73.935900
2014-02-17T11:15:26.000-05:00 , Vehicle1, 40.789557, -73.954558
2014-02-17T11:15:49.000-05:00 , Vehicle2, 40.813135, -73.937353
2014-02-17T11:18:49.000-05:00 , Vehicle3, 40.820890, -73.935900
2014-02-17T11:19:08.000-05:00 , Vehicle1, 40.782017, -73.960065
2014-02-17T11:19:00.000-05:00 , Vehicle2, 40.817062, -73.938585
2014-02-17T11:22:37.000-05:00 , Vehicle3, 40.820890, -73.935900
2014-02-17T11:22:20.000-05:00 , Vehicle1, 40.778014, -73.962983
2014-02-17T11:22:44.000-05:00 , Vehicle2, 40.822828, -73.937887
2014-02-17T11:25:50.000-05:00 , Vehicle3, 40.820890, -73.935900
2014-02-17T11:26:03.000-05:00 , Vehicle1, 40.774126, -73.965815
2014-02-17T11:28:33.000-05:00 , Vehicle3, 40.820890, -73.935900
2014-02-17T11:28:09.000-05:00 , Vehicle1, 40.770644, -73.968356
...

第一列是日期/时间，第二个是车辆ID，第三个和第四个是经纬度。

车辆的数量不是恒定的，并且全天都在变化。日期/时间取决于每个车辆的实际记录时间。该数据库包含超过一百万条记录，每 3 分钟采样一次。

我的基本思路是提取一辆车的运行顺序(按车辆分组)，对日期/时间进行排序，计算时间间隔和时间间隔之间位置(经纬度)的delta作为距离，用我能够计算速度的距离和时间间隔。

问题是我不知道如何将方法构建到 SQLite select 语句中，感谢您提供的任何帮助。

非常感谢!

Answer 1

我已经尝试使用 awk 进行此操作，我相信它可以在 Android 上使用 - 它可以很容易地转换为 Perl 或 C 代码。

它使用 Haversine 公式计算距离。

它假定您的 sqlite 转储位于一个名为 locations 的文件中。

#!/bin/bash
awk -F, '
   {
      # Convert date to epoch seconds for added sanity
      tstr=$1;
      cmd="gnudate --date=" tstr " +%s"
      cmd | getline epoch
      close(cmd)

      # DEBUG print epoch,$2,$3,$4

      # Pick up all fields from current record
      vehicle=$2;lat=$3;lon=$4;

      # If we have a previous record for this vehicle we are in business
      if(lats[vehicle]){
         tdiff=epoch-epochs[vehicle]
         d=haversine(lat,lon,lats[vehicle],lons[vehicle])
         speed=3600*d/tdiff
         if(speed==0)speed="0 (stationary)"
         print $1,vehicle,speed
      }

      # Update last seen lats, lons, epoch for this vehicle for next iteration
      epochs[vehicle]=epoch
      lats[vehicle]=lat
      lons[vehicle]=lon
   }

   function haversine(lat1,lon1,lat2,lon2,  a,c,dlat,dlon) {
      dlat = radians(lat2-lat1)
      dlon = radians(lon2-lon1)
      lat1 = radians(lat1)
      lat2 = radians(lat2)
      a = (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2
      c = 2 * atan2(sqrt(a),sqrt(1-a))
      # 6372 = Earth radius in km, for distance in km
      return 6372 * c
   }

   function radians(degree) { # degrees to radians
      return degree * (3.1415926 / 180.)

   }' locations

输出:

2014-02-17T11:10:10.000-05:00   Vehicle3 0 (stationary)
2014-02-17T11:10:07.000-05:00   Vehicle1 2.23614
2014-02-17T11:09:59.000-05:00   Vehicle2 13.8954
2014-02-17T11:13:27.000-05:00   Vehicle3 0 (stationary)
2014-02-17T11:13:17.000-05:00   Vehicle1 19.1132
2014-02-17T11:13:09.000-05:00   Vehicle2 12.4564
2014-02-17T11:15:37.000-05:00   Vehicle3 0 (stationary)
2014-02-17T11:15:26.000-05:00   Vehicle1 16.6565
2014-02-17T11:15:49.000-05:00   Vehicle2 7.72184
2014-02-17T11:18:49.000-05:00   Vehicle3 0 (stationary)
2014-02-17T11:19:08.000-05:00   Vehicle1 15.5387
2014-02-17T11:19:00.000-05:00   Vehicle2 8.46043
2014-02-17T11:22:37.000-05:00   Vehicle3 0 (stationary)
2014-02-17T11:22:20.000-05:00   Vehicle1 9.53438
2014-02-17T11:22:44.000-05:00   Vehicle2 10.349
2014-02-17T11:25:50.000-05:00   Vehicle3 0 (stationary)
2014-02-17T11:26:03.000-05:00   Vehicle1 7.97182
2014-02-17T11:28:33.000-05:00   Vehicle3 0 (stationary)
2014-02-17T11:28:09.000-05:00   Vehicle1 12.6412

注意事项:

1. 单位是km/h，将代码中的6372公里地球半径改为3959英里，单位是mph。

2. 您的 date 命令可能是 date 而不是第 6 行的 gnudate。

3. 如果您想处理暂时离线的车辆，请将计算 tdiff 的行移到 if 语句上方并测试 if tdiff<60，以便您仅在距离上一个位置不到一分钟的时间时计算速度(说)。