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

Considering that the answer for the question you say is not duplicate does work, you really should explain what you have tried and what error message or unexpected behaviour you experienced. — Shadow, Oct 13 '19 at 23:09
I ran the query and it does not return the expected result, it works for non returning position — yenk, Oct 13 '19 at 23:13
@Shadow i get: Empty set (0.00 sec) for first answer, I get 0 for second answer — yenk, Oct 13 '19 at 23:17
Since I have no clue what query you are running, I cannot comment on what's gone wrong with your attempt. This is why you should include these attempts in your question with the exact code. — Shadow, Oct 13 '19 at 23:19
Possible duplicate of [Find distance between two points using latitude and longitude in mysql](https://stackoverflow.com/questions/24370975/find-distance-between-two-points-using-latitude-and-longitude-in-mysql) — Shadow, Oct 13 '19 at 23:26

GMB · Accepted Answer · 2019-10-13T23:15:03.570

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.

```MySQL server version for the right syntax to use near '(ORDER BY time``` — yenk, Oct 13 '19 at 22:53
Which version of mysql are you using? I mentionned this uses window function, available since 8.0 only — GMB, Oct 13 '19 at 22:55
It ensures that there is no record in between the current record and the one being joined. In other words, it ensures that we are joining with the immediatly preceeding record. — GMB, Oct 13 '19 at 23:27

mysql calculate distance for returnning location

1 Answers1