我有一个具有以下数据格式的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
,以便仅在距最后一个位置的时间少于一分钟的情况下计算速度(说)。