73

73

关注
发信
关注(28)粉丝(399)

从位置（纬度和经度）计算速度和存储在SQLite数据库中的时间

本文介绍了从位置（纬度和经度）计算速度和存储在SQLite数据库中的时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个具有以下数据格式的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

注意：


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

这篇关于从位置（纬度和经度）计算速度和存储在SQLite数据库中的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

10-23 21:56
Powered by LMLPHP ©2024 73 0.004263