Calculating speed from position (latitude & longitude) and time stored in SQLite database

Question

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!

Answer 1

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

Answer 2

SQLite does not have the mathematical functions needed to convert lat/lon coordinates into metric coordinates (and Android does not allow you to add them).