从位置（纬度和经度）计算速度和存储在SQLite数据库中的时间 [英] Calculating speed from position (latitude & longitude) and time stored in SQLite database
问题描述
我有一个具有以下数据格式的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.0 00-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
...
第一列是日/时间，
秒是车辆编号，
第三和第四是纬度和经度。
车辆数量不是常数，那天。
日/时间取决于每个车辆的实际记录时间。
数据库由超过一百万条记录组成，每3分钟采样一次。
我的基本思路是提取车辆的运行顺序（按车辆分组），排序日/时间，计算距离与时间间隔之间的位置（纬度和纬度）的距离和时间间隔之间的时间间隔和增量。 p>
问题是我不知道如何将方法结构化为SQLite select语句，我感谢任何帮助。
非常感谢！
我已经在这个使用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
注意：
-
单位是km / h，将代码中的6372公里地球半径改为3959英里，单位为mph。
-
您的
/ li>日期命令可能是第6行的日期而不是gnudate
-
如果要处理一段时间下线的车辆，请将计算
tdiff的线移动到如果语句和测试如果tdiff <60，那么只有在自上一个位置以后的时间少于一分钟时才计算速度说）。
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:
Units are km/h, change the 6372 km Earth radius in the code to 3959 miles for units of mph.
Your
datecommand may bedaterather thangnudateon line 6.If you want to handle the vehicles going offline for a while, move the line that calculates
tdiffup above theifstatement and testif tdiff<60so you only calculate speeds if the time since the last position is less than a minute (say).
这篇关于从位置（纬度和经度）计算速度和存储在SQLite数据库中的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
