# Calculate all latitude and longitude distance in result using MySQL

this is my current query to calculate between two latitude and longitude, but how do i using this or similar formula to calculate all the latitude and longitude in result?

``````SELECT ROUND(6353 * 2 * ASIN(SQRT( POWER(SIN((a.GPSLat -
abs(b.GPSLat)) * pi()/180 / 2),2) + COS(a.GPSLat * pi()/180 ) * COS(
abs(b.GPSLat) *  pi()/180) * POWER(SIN((a.GPSLon - b.GPSLon) *
pi()/180 / 2), 2) )), 2) as TotalKM
from table1 a
inner join table1 b on a.ID = 70 and b.ID = 71;
``````

This example of Lat and Lon in my database

The result count by id70 and id71

Edited : The result that i need is

(id69 + id70 = 0.4km),

(0.4km + id71 = 0.7km),

(0.7km + id72 = 1.0km),

totalkm is 2.1km

``````ID | GPSLAT   | GPSLon     | DistanceInKM
69 | 3.214502 | 101.638419 | 0.0
70 | 3.214478 | 101.638101 | 0.4
71 | 3.214668 | 101.638049 | 0.7
72 | 3.215089 | 101.638022 | 1.0
|TotalKM
2.1
``````

The following pieces of code may help you to find a solution (tested with MySQL 5.7 and MariaDB 10.2). As far as I am aware, MySQL does not have an built-in LAG() function. Thus, I suggest that we use a VIEW, that finds "previous" and "current" values, respectively (for location IDs, longitudes, and latitudes), and that encompasses your rather complicated formula:

``````-- Test table and data:
create table gpsdata(
id int
, gpslat numeric (9,6)
, gpslon numeric (9,6)
);

insert into gpsdata values
(69 , 3.214502 , 101.638419),
(70 , 3.214478 , 101.638101),
(71 , 3.214668 , 101.638049),
(72 , 3.215089 , 101.638022),
(73 , 3.215196 , 101.638626),
(74 , 3.215255 , 101.639087),
(75 , 3.215011 , 101.639448),
(76 , 3.215041 , 101.640151);
``````

VIEW

``````create or replace view distances
as
select
prev.id prev_
, curr.id curr_
, prev.gpslat p_lat_
, curr.gpslat c_lat_
, prev.gpslon p_lon_
, curr.gpslon c_lon_
, 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 (
select
id
, gpslat
, gpslon
from gpsdata
) prev join gpsdata curr
on prev.id = curr.id - 1
where curr.id >= 1
;
``````

We get this from the view:

``````select * from distances;

+-------+-------+----------+----------+------------+------------+-------------+
| prev_ | curr_ | p_lat_   | c_lat_   | p_lon_     | c_lon_     | distance_km |
+-------+-------+----------+----------+------------+------------+-------------+
|    69 |    70 | 3.214502 | 3.214478 | 101.638419 | 101.638101 |        0.04 |
|    70 |    71 | 3.214478 | 3.214668 | 101.638101 | 101.638049 |        0.02 |
|    71 |    72 | 3.214668 | 3.215089 | 101.638049 | 101.638022 |        0.05 |
|    72 |    73 | 3.215089 | 3.215196 | 101.638022 | 101.638626 |        0.07 |
|    73 |    74 | 3.215196 | 3.215255 | 101.638626 | 101.639087 |        0.05 |
|    74 |    75 | 3.215255 | 3.215011 | 101.639087 | 101.639448 |        0.05 |
|    75 |    76 | 3.215011 | 3.215041 | 101.639448 | 101.640151 |        0.08 |
+-------+-------+----------+----------+------------+------------+-------------+
``````

Now, we can use the view to calculate "cumulative km":

``````select
a.prev_ from_
, a.curr_ to_
, a.distance_km distance_between
, sum(b.distance_km) cumulative_km
from
distances a
, distances b
where a.prev_ >= b.prev_
group by a.prev_, a.curr_, a.distance_km
order by a.prev_;

+-------+------+------------------+---------------+
| from_ | to_  | distance_between | cumulative_km |
+-------+------+------------------+---------------+
|    69 |   70 |             0.04 |          0.04 |
|    70 |   71 |             0.02 |          0.06 |
|    71 |   72 |             0.05 |          0.11 |
|    72 |   73 |             0.07 |          0.18 |
|    73 |   74 |             0.05 |          0.23 |
|    74 |   75 |             0.05 |          0.28 |
|    75 |   76 |             0.08 |          0.36 |
+-------+------+------------------+---------------+
``````

The view can also be used to create summaries like ...

``````select
min(prev_) from_
, max(curr_) to_
, sum(distance_km) total_km
from
distances
;

+-------+------+----------+
| from_ | to_  | total_km |
+-------+------+----------+
|    69 |   76 |     0.36 |
+-------+------+----------+
``````

dbfiddle here.

• Minor note: `DECIMAL(7,6)` is not big enough to get very far from the equator. Commented Jul 14, 2017 at 20:32
• @RickJames :-) thanks for pointing this out! Commented Jul 14, 2017 at 20:34
• how to do this in `mysql <5.7` gives error `View's SELECT contains a subquery in the FROM clause` Commented Feb 18, 2019 at 16:53
• @Arpan - Create 2 views: {1} one that represents the subquery "prev", and {2} a view called "distances" that references view {1} instead of the "prev" subquery. Seems to be working with MySQL 5.5 and 5.6. Example code (w/ MySQL 8.0): dbfiddle.uk/… Commented Feb 18, 2019 at 19:31

Add something like this onto the query in order to get the TotalKM

``````GROUP BY id, gpslat, gpslon WITH ROLLUP
``````

Don't attempt to get that exact output with SQL; do that in you application code. The ROLLUP will give you an extra line something like

``````NULL NULL NULL 2.1
``````
• when i add this code in my query `GROUP BY id, gpslat, gpslon WITH ROLLUP` it show me `Column 'ID' in group statement is ambiguous` Commented Jul 13, 2017 at 7:22
• What happens if you leave `ID` out? Commented Jul 13, 2017 at 14:31
• then `id` would become `gpslat` again Commented Jul 14, 2017 at 1:35
• Leave it out of both places. Commented Jul 14, 2017 at 3:26
• @RickJames Not sure how I'd get the cumulative km (see the "Edited" section of the question) by using WITH ROLLUP. Would it be possible for you to shed some light onto this? Commented Jul 14, 2017 at 20:06

All that you need is to add an extra column `odometer` where the total mileage should be written each time you get a new lat-lon point. That approach has a lot of advantages, most valuable of which are:

1. To find a distance between arbitrary two points you have to substract beginning odometer value from the endind. That easy.
2. All the calculation-related load will be distributed over the time.
3. You can verify distances, provided by the trackers. Most of them are buggy and can send garbage data that drive to the complete reports unadequacy.

The other advice is to simplify your calculations as possible. Do not calculate again and again those things you have calculated already, like `pi()/180`.

My final code now is such simple:

``````SET a = 0.01745329252 * lat1;
SET b = 0.01745329252 * lat2;
SET c = 0.01745329252 * (lon1 - lon2);
RETURN 6372795 * acos(sin(a)*sin(b) + cos(a)*cos(b)*cos(c));
``````