我已经在这个使用awk，我相信在Android上可用 - 它可以很容易地转换为Perl或C代码。

它使用Haversine公式来计算距离。

假设您的sqlite转储在一个名为位置。

 ＃！/ bin / bash 
 awk -F，'
 {
＃转换日期到epoch秒增加理智
 tstr = $ 1; 
 cmd =gnudate --date =tstr+％s
 cmd | getline epoch 
 close（cmd）

＃DEBUG打印纪元，$ 2，$ 3，$ 4 

＃从当前记录中取出所有字段
 vehicle = $ 2; LAT = $ 3; LON = $ 4; 

＃如果我们有这辆车的以前的记录，我们在业务
如果（lats [车辆]）{
 tdiff = epoch-epochs [车辆] 
d = haversine（lat，lon，lats [vehicle]，lons [vehicle]）
 speed = 3600 * d / tdiff 
 if（speed == 0）speed =0（stationary）
打印$ 1，车辆，速度
} 

＃更新上一次看到的lat，lons，这辆车的下一次迭代的历元
 epochs [vehicle] = epoch 
 lats [车辆] = lat 
 lons [车辆] = lon 
} 

函数haversine（lat1，lon1，lat2，lon2，a，c，dlat，dlon）{
 dlat =弧度（lat2-lat1）
 dlon =弧度（lon2-lon1）
 lat1 =弧度（lat1）
 lat2 =弧度（lat2）
a =（sin dl（2））^ 2 + cos（lat1）* cos（lat2）*（sin（dlon / 2））^ 2 
c = 2 * atan2（sqrt（a），sqrt（1-a））
＃6372 =以km为单位的地球半径，以km为单位
返回6372 * c 
} 

函数弧度（degr ee）{＃度到弧度
回程度*（3.1415926 / 180.）

}'位置
 

输出：

  2014-02-17T11：10 ：10.000-05：00车3 0（固定）
 2014-02-17T11：10：07.000-05：00车1 2.23614 
 2014-02-17T11：09：59.000-05：00车2 13.8954 
 2014-02-17T11：13：27.000-05：00车辆3 0（固定）
 2014-02-17T11：13：17.000-05：00车辆1 19.1132 
 2014-02-17T11： 13：09.000-05：00车辆2 12.4564 
 2014-02-17T11：15：37.000-05：00车辆3 0（固定）
 2014-02-17T11：15：26.000-05：00车辆16.6565 
 2014-02-17T11：15：49.000-05：00车辆2 7.72184 
 2014-02-17T11：18：49.000-05：00车辆3 0（固定）
 2014-02-17T11 ：19：08.000-05：00车1 15.5387 
 2014-02-17T11：19：00.000-05：00车辆8.46043 
 2014-02-17T11：22：37.000-05：00车3 0 ）
 2014-02-17T11：22：20.000-05：00 Vehicle1 9.53438 
 2014-02-17T11：22：44.0 00-05：00 Vehicle2 10.349 
 2014-02-17T11：25：50.000-05：00车3 0（固定）
 2014-02-17T11：26：03.000-05：00车1 7.97182 
 2014-02-17T11：28：33.000-05：00车辆3 0（固定）
 2014-02-17T11：28：09.000-05：00车辆12.6412 
 

注意：

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




2. 您的日期命令可能是第6行的日期而不是 gnudate

   / li>
   

3. 如果要处理一段时间下线的车辆，请将计算 tdiff 的线移动到如果语句和测试如果tdiff ，那么只有在自上一个位置以后的时间少于一分钟时才计算速度说）。

I have a SQLite database with the following data format

...
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
...

The first column is day/time, second is vehicle id, third and fourth are latitude and longitude.

The number of vehicle is not a constant, and changing throughout the day. The day/time is depending on the actual recording time which varies by each vehicle. The database consists of more than a million records, with a sampling rate at every 3 minutes.

My basic thought is to extract the running sequence of a vehicle (group by vehicle), sort the day/time, calculate the time interval and delta of the position (latitude and longitude) between the time interval as the distance, with distance and the time interval I am able to calculate the speed.

Problem is I don't know how to structure the approach into SQLite select statement, and I appreciate any help given.

Thank you very much!

I have had a go at this using awk, which I believe is available on Android - it can be readily converted to Perl, or C code.

It uses the Haversine formula to calculate the distance.

It assumes your sqlite dump is in a file called 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

Output:

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

Notes:

1. Units are km/h, change the 6372 km Earth radius in the code to 3959 miles for units of mph.

2. Your date command may be date rather than gnudate on line 6.

3. If you want to handle the vehicles going offline for a while, move the line that calculates tdiff up above the if statement and test if tdiff<60 so you only calculate speeds if the time since the last position is less than a minute (say).