mysql calculate distance for returnning location

Question

This is not a duplicate of: Find distance between two points using latitude and longitude in mysql

I tried the query and it's not working, I get empty data set and 0 as the answer.

SEE answer, THAT is the solution, I don't know why this question was marked as duplicate from Shadow

i have a table with the following structure:

table: distance
id int(10) primary key
lat float,
lon float,
time timestamp

i have a android application that ping my server and add a record each time it change position.

for example

id:1, lat:40.753979, lon:-111.881721, time = 1571004620
id:2, lat:40.753979, lon:-111.883721, time = 1571004630
id:3, lat:40.753979, lon:-111.885721, time = 1571004640

I can calculate the total distance if I go one direction:

/* in my python script*/
startLat,startLon = select lat,lon from distance where time >= 1571004620 order by time limit 1;
endLat,endLon = select lat,lon from distance where time <= 1571004640 order by time desc limit limit 1;

then i can substract both coordinates, which end up giving me a longitute distance of 0.004000

problem:

if i add:

id:4, lat:40.753979, lon:-111.881721, time = 1571004650

then I should get: 0.008000

but i get 0 since position 1 is the same as position 4

Answer 1

You can take advantage of MySQL spatial support, available since 5.7 to perfom the whole computation within the database. Function ST_Distance_Sphere() can be used to compute distances.

You actually need a cumulative sum of the distance (this requires window functions, available since MySQL 8.0).

Consider:

SELECT
    id,
    time,
    SUM(
        CASE WHEN lag_lat IS NULL 
            THEN 0
            ELSE ST_Distance_Sphere(point(lag_lon, lag_lat), point(lon, lat))
        END
    ) OVER (ORDER BY time) cumulative_distance
FROM (
    SELECT
        d.*,
        LAG(lat) OVER(ORDER BY time) lag_lat,
        LAG(lon) OVER(ORDER BY time) lag_lon
    FROM distance d
)  x

Demo on DB Fiddle:

| id  | time       | cumulative_distance |
| --- | ---------- | ------------------- |
| 1   | 1571004620 | 0                   |
| 2   | 1571004630 | 168.37177423236415  |
| 3   | 1571004640 | 336.7435484657999   |
| 4   | 1571004650 | 673.4870969097663   |

---

In earlier versions of MySQL, you need to emulate window functions:

• LAG() can be replaced with a self-LEFT JOIN with a NOT EXISTS condition and a correlated subquery in the ON condition

• variables can emulate the cumulative SUM

Query:

SELECT
    id,
    time,
    @running_distance := @running_distance + CASE
        WHEN lag_lat IS NULL THEN 0
        ELSE ST_Distance_Sphere(point(lag_lon, lag_lat), point(lon, lat))
    END running_distance
FROM (
    SELECT
        d.id,
        d.time,
        d.lat,
        d.lon,
        d_lag.lat lag_lat,
        d_lag.lon lag_lon   
    FROM distance d
    LEFT JOIN distance d_lag
        ON  d_lag.time < d.time
        AND NOT EXISTS (
            SELECT 1 
            FROM distance d1
            WHERE d1.time < d.time AND d1.time > d_lag.time
        )
    ORDER BY d.time
) x
CROSS JOIN (SELECT @running_distance := 0) y

Demo on DB Fiddle: same results as above.