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

...
2014-02-17T11:06:22.000-05:00,Vehicle3,40.820890,-73.935900
2014-02-17T11:06:23.000-05:00,车辆1,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,车辆1,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,车辆1,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,车辆1,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分钟采样一次。

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

问题是我不知道如何将方法构造为SQLite select语句,并且感谢您提供的任何帮助。

非常感谢你!

最佳答案

我使用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


笔记:


单位为km / h,将代码中的6372公里地球半径更改为3959英里(英里/小时)。
您的date命令可能是date而不是第6行的gnudate
如果要处理一段时间下线的车辆,请将计算tdiff的行上移至if语句上方并测试if tdiff<60,以便仅在距最后一个位置的时间少于一分钟的情况下计算速度(说)。

09-11 19:33
查看更多