MySQL - Large table calculate vehicle average speeds

I have a large table with the following structure:

dt	veh_id	lat	long
2021-01-14 13:00:00	123456ABC	26.56602763823091	2.937412284992731
2021-01-14 13:00:00	1762GH	26.56602763823091	2.937412284992731
2021-01-14 13:00:00	189725FT	26.56602763823091	2.937412284992731
2021-01-14 13:52:07	123456ABC	27.03312220493411	2.6008171450629343
2021-01-14 14:02:27	123456ABC	27.195391148992073	2.490953866466876

What I would like to calculate (if possible) is the additional rows based on the same vehicle ID travelling.

dt	veh_id	lat	long	Time	Distance (Miles)	Speed (MPH)
2021-01-14 13:00:00	123456ABC	26.56602763823091	2.937412284992731	0	0	0
2021-01-14 13:00:00	1762GH	26.56602763823091	2.937412284992731
2021-01-14 13:00:00	189725FT	26.56602763823091	2.937412284992731
2021-01-14 13:52:07	123456ABC	27.03312220493411	2.6008171450629343	00:52:07	39.5	45.4
2021-01-14 14:02:27	123456ABC	27.195391148992073	2.490953866466876	00:06:20	13.2	76.6

This is over a very large number of rows, at the moment the data is only in day tables with each table containing 1 day. This may however be merged to run this query over multiple days. I am interested in any vehicles exceeding a certain speed so filtering on this column would be great.

Thanks in advance you very clever people. :)

This data is in a MariaDB (10.3)(database but can be exported to anything to help me achieve this. I have no idea where to start with this and have tried to run this in Power BI with measures which had some partial success but ran out of RAM and having spent a few days trying I have now almost given up on this.

I am a bit of a newbie when it comes to MySQL and whilst I can get away with basic commands, I have never attempted anything like this and not even sure if it is possible? Any help or guidance appreciated.

Solution

You would do something like:

select dt, veh_id, lat, lon, sec_to_time(secs) time, round(distance,1) distance, round(if(secs,distance/secs*3600,0),1) speed
from (
    select dt, veh_id, lat, lon,
        timediff(dt,coalesce(lag(dt) over w,dt)) secs,
        coalesce(
            (3958.7613*(2*ASIN(SQRT(POWER(SIN(((lat-lag(lat) over w)*0.017453293)/2),2)+COS(lat*0.017453293)*COS((lag(lat) over w)*0.017453293)*POWER(SIN(((lon-lag(lon) over w)*0.017453293)/2),2)))))
        ,0) distance
    from a_large_table
    window w as (partition by veh_id order by dt)
) add_lag;

I may have gotten the distance formula wrong, I didn't get the same numbers you did.

fiddle