gpsdata, MySQL - rextester

Language: Layout:

drop table if exists gpsdata;
drop view if exists distances;

create table if not exists gpsdata(ID int, GPSLat decimal(10,6), GPSLon decimal(10,6), GPSTime timestamp);
insert into gpsdata values
(1 , 3.214502 , 101.638419, '2017-06-23 12:21:51'),
(2 , 3.214478 , 101.638101, '2017-06-23 12:20:42'),
(3 , 3.214668 , 101.638049, '2017-06-23 13:25:53'),
(4 , 3.215089 , 101.638022, '2017-06-23 13:19:12'),
(5 , 3.215196 , 101.638626, '2017-06-23 14:10:56'),
(6 , 3.215255 , 101.639087, '2017-06-24 15:10:48'),
(7 , 3.215011 , 101.639448, '2017-06-24 15:10:36'),
(8 , 3.215041 , 101.640151, '2017-06-24 16:10:26'),
(9 , 3.218021 , 101.620416, '2017-06-24 17:31:59'),
(10, 3.123456 , 101.555555, '2017-06-25 18:45:41'),
(11, 3.345214 , 101.632157, '2017-06-25 18:02:51');

create view distances as
select
  prev.id prev_
, curr.id curr_
, prev.gpslat p_lat_
, prev.gpslon p_lon_
, curr.gpslat c_lat_
, curr.gpslon c_lon_
, prev.gpstime p_time_
, curr.gpstime c_time_
, ROUND( 6353 * 2 * 
    ASIN(SQRT( POWER(SIN((curr.GPSLat - abs(prev.GPSLat)) * pi()/180 / 2),2) 
  + COS(curr.GPSLat * pi()/180 ) * COS( abs(prev.GPSLat) *  pi()/180) 
  * POWER(SIN((curr.GPSLon - prev.GPSLon) * pi()/180 / 2), 2) )), 2) as distance_km
from gpsdata prev
  join gpsdata curr 
    on prev.id = curr.id - 1
where curr.id >= 1;

select   prev_, curr_, p_time_, c_time_, distance_km
from     distances
where    p_time_ >= '2017-06-23 12:00:00' 
and      c_time_ <= '2017-06-23 14:00:00'
order by prev_
;

select sum(distance_km) as total_km
from   distances
where  p_time_ >= '2017-06-23 12:00:00' 
and    c_time_ <= '2017-06-23 14:00:00'
;

drop table if exists gpsdata;
drop view if exists distances;


absolute service time: 0,35 sec