0

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

This is my database Lat and Lon example

The result count by id70 and id71

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
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. – Rick James Jul 14 '17 at 20:32
  • @RickJames :-) thanks for pointing this out! – stefan Jul 14 '17 at 20:34
  • how to do this in mysql <5.7 gives error View's SELECT contains a subquery in the FROM clause – Arpan Feb 18 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/… – stefan Feb 18 at 19:31
2

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 – Amir Khan Jul 13 '17 at 7:22
  • What happens if you leave ID out? – Rick James Jul 13 '17 at 14:31
  • then id would become gpslat again – Amir Khan Jul 14 '17 at 1:35
  • Leave it out of both places. – Rick James Jul 14 '17 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? – stefan Jul 14 '17 at 20:06
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.