Query to calculate sum of distance (longitude, latitude) in consecutive rows in Mysql -

i new sql , stuck. trying calculate (yearly) sum of distance each user has traveled. have table (lets call dist_table) following structure:

rowid     user_name   date             lat        long 1         maria       2005-01-01       51.555     5.014 2         maria       2005-01-01       51.437     5.474 3         peter       2005-02-03       51.437     5.474 4         john        2005-02-03       51.858     5.864 5         maria       2005-02-04       51.858     5.864 6         john        2005-02-03       51.437     5.474 7         john        2006-02-04       0          0 8         john        2006-02-04       51.858     5.864 9         john        2006-02-04       51.858     5.864 10        john        2006-02-04       51.437     5.474 

this intermediate step in calculation (just clarify mean):

rowid     user_name   date             lat        long      distance 1         maria       2005-01-01       51.555     5.014     0 2         maria       2005-01-01       51.437     5.474     34.452 3         peter       2005-02-03       51.437     5.474     0 4         john        2005-02-03       51.858     5.864     0 5         maria       2005-02-04       51.858     5.864     54.012 6         john        2005-03-03       51.437     5.474     54.012 7         john        2006-02-04       0          0          8         john        2006-02-04       51.858     5.864     54.012 9         john        2006-02-04       51.858     5.864     0      10        john        2006-02-04       51.437     5.474     54.012 

and final result need:

user_name   date       sum(distance) maria       2005       88.464 peter       2005       0 john        2005       54.012 john        2006       108.024 

i thinking of using formula (haversine) calculate distance between consecutive rows , summing up:

select user_name,date,dist_table.lat,dist_table.long, 6373 * 2 * asin(sqrt(power(sin((orig_latitude - abs(next_latitude)) * pi()/180 / 2),2) + cos(orig_latitude * pi()/180) * cos(abs(next_latitude) * pi()/180) * power(sin((orig_longitude - next_longitude) * pi()/180 / 2),2)  )) distance dist_table dist_table.lat !=0 , dist_table.long !=0; 

however, unable figure out how call consecutive row. far, got when trying figure out how connect rows:

select user_name, date, lat, ifnull( (     select max( lat )      dist_table     user_name = t1.user_name     , ( date < t1.date ) ) ,0) next_latitude dist_table t1 order user_name, date;  

the problem each user, there can multiple rows satisfying condition , chooses maximum value instead of previous one. furthermore, there 0 in longitude and/or latitude , need ignore these rows.

i thinking solved, if first created column row order based on user_name , date , putting date+1 = t1.date in condition. unfortunately, have limited permissions on server using have handled user defined variables, not know how that.

i using mysql 5.6.19-log.

could me out?

so here's solution first part of problem...

drop table if exists my_table;  create table my_table (id     int not null auto_increment primary key ,user_name   varchar(12) not null ,date             date not null ,lat        decimal(5,3) not null ,lon decimal (5,2) not null );  insert my_table values ( 1,'maria','2005-01-01',51.555 ,5.014), ( 2,'maria','2005-01-01',51.437 ,5.474), ( 3,'peter','2005-02-03',51.437 ,5.474), ( 4,'john' ,'2005-02-03',51.858 ,5.864), ( 5,'maria','2005-02-04',51.858 ,5.864), ( 6,'john' ,'2005-02-03',51.437 ,5.474), ( 7,'john' ,'2006-02-04',0      ,0), ( 8,'john' ,'2006-02-04',51.858 ,5.864), ( 9,'john' ,'2006-02-04',51.858 ,5.864), (10,'john' ,'2006-02-04',51.437 ,5.474);   select x.user_name      , x.id from_id      , min(y.id) to_id   my_table x   join my_table y     on y.user_name = x.user_name    , y.id > x.id  (y.lat <> 0 , y.lon <> 0)    , (x.lat <> 0 , x.lon <> 0)  group      x.id;  +-----------+---------+-------+ | user_name | from_id | to_id | +-----------+---------+-------+ | maria     |       1 |     2 | | maria     |       2 |     5 | | john      |       4 |     6 | | john      |       6 |     8 | | john      |       8 |     9 | | john      |       9 |    10 | +-----------+---------+-------+ 

for rest of problem, following should work.

i have function in database called geo_distance_km. looks this, , saves typing out haversine formula each time:

delimiter // create definer = current_user function geo_distance_km (lat1 double, lon1 double, lat2 double, lon2 double) returns double  begin    declare r int default 6372.8;    declare phi1 double;    declare phi2 double;    declare d_phi double;    declare d_lambda double;    declare double;    declare c double;    declare d double;    set phi1 = radians(lat1);    set phi2 = radians(lat2);    set d_phi = radians(lat2-lat1);    set d_lambda = radians(lon2-lon1);    set = sin(d_phi/2) * sin(d_phi/2) +          cos(phi1) * cos(phi2) *          sin(d_lambda/2) * sin(d_lambda/2);    set c = 2 * atan2(sqrt(a), sqrt(1-a));    set d = r * c;    return d;    end; // delimiter ; 

we can combine have already...

select user_name      , year(date) year      , coalesce(sum(distance),0) total         ( select a.*             , b.lat to_lat             , b.lon to_lon             , round(geo_distance_km(from_lat,from_lon,b.lat,b.lon),3) distance                      ( select x.user_name                    , x.date                    , x.id from_id                    , x.lat from_lat                    , x.lon from_lon                    , min(y.id) to_id                 my_table x                 left                 join my_table y                   on y.user_name = x.user_name                  , y.id > x.id                  , (y.lat <> 0 or y.lon <> 0)                  (x.lat <> 0 , x.lon <> 0)                group                   x.id             )          left          join my_table b            on b.id = a.to_id      ) n  group     user_name      , year;  +-----------+------+---------+ | user_name | year | total   | +-----------+------+---------+ | john      | 2005 | 108.024 | | john      | 2006 |  54.012 | | maria     | 2005 |  88.464 | | peter     | 2005 |   0.000 | +-----------+------+---------+ 

i don't quite understand how handle distances overlap years, should close you're after.