PHP MySQL How to calculate distance for between row?

Question

I have a table like this:

stepID   | UserID| Date        | Lat        | Lng
1        |1      | 2019-10-11  | -7.2905838 | 112.5655568
2        |1      | 2019-10-11  | -7.2349607 | 112.6106177
3        |1      | 2019-10-11  | -7.2345435 | 112.6112432
4        |1      | 2019-10-12  | -7.2529265 | 112.6542999

I need to calculate distance that user has been visited on the same day (for example 2019-10-11). So waht will be show on PHP page is (the KM amount below is an example) :

From step 1 to 2: 2 KM
From step 2 to 3: 3 KM
From step 3 to 4: 3 KM
TOTAL FOR TODAY: 8 KM

I've googling and also search in this stackoverflow's history but didn't found like what I face today. Need your suggestion how to query this. Thank you before, GBU always.

sorry for text format, I will change the text layout so everyone will not confuse — Rian, Nov 22 '19 at 07:11

score 0 · Accepted Answer · answered Nov 22 '19 at 07:46

0

This is not the best task for MySQL. It would be much better to perform it in any programming language by reading rows from DB 1 by 1. However if you want to use exactly MySQL then something like that (assuming StepIDs are sequential numbers without gaps):

SELECT UserID, SUM(km) total
FROM (
  SELECT t1.UserID, 
    DEGREES(ACOS(LEAST(1.0, COS(RADIANS(t1.Lat))
         * COS(RADIANS(t2.Lat))
         * COS(RADIANS(t1.Lon- t2.Lon))
         + SIN(RADIANS(t1.Lat))
         * SIN(RADIANS(t2.Lat))))) km
  FROM table t1
  JOIN table t2 ON t1.UserID = t2.UserID and t1.StepID = t2.StepID - 1
) t
GROUP BY UserID

I've got the formula from: https://stackoverflow.com/a/24372831/2244262

answered Nov 22 '19 at 07:46

Stalinko

3,319
28
31

Hi @Stalinko, thank you for your suggestion. GBU always – Rian Nov 24 '19 at 04:51
Hi @Stalinko, sorry for late reply. Didn't work because MySQL says "#1248 - Every derived table must have its own alias"...while I've make an alias for each column like this: – Rian Dec 02 '19 at 02:37
SELECT SUM(km) total FROM (SELECT a.user_id, DEGREES(ACOS(LEAST(1.0, COS(RADIANS(a.activity_result_lat_in)) * COS(RADIANS(b.activity_result_lat_in)) * COS(RADIANS(a.activity_result_lng_in- b.activity_result_lng_in)) + SIN(RADIANS(a.activity_result_lat_in)) * SIN(RADIANS(b.activity_result_lat_in))))) km FROM tbl_activity_result a INNER JOIN tbl_activity_result b ON a.user_id = b.user_id and a.activity_result_id = b.activity_result_id - 1) WHERE a.user_id = 170 AND date(a.activity_date) >= '2019-10-28' AND date(a.activity_date) <= '2019-10-28' GROUP BY a.user_id – Rian Dec 02 '19 at 02:40
@rizal, all correct. In your example you don't have an alias for the sub-query. Add something before "where". – Stalinko Dec 02 '19 at 08:40
Finally it worked. Thank you for your correction @Stalinko. – Rian Dec 04 '19 at 03:37
@rizal if my answer helped you please mark it as correct :) – Stalinko Dec 04 '19 at 08:25
hi @Stalinko, sorry I just logged in again today. I've marked.it. Thank you for your help. – Rian Mar 13 '20 at 13:31

PHP MySQL How to calculate distance for between row?

1 Answers1