Mysql route length calculatation based on lat, lng coordinates and engine state

Question

I have a MySQL database table that has latitude, longtitude, date,time, engine_state columns.

I want to calculate the distance the vehicle had travelled between two dates. I found a thread were it is explaind how to make the query and calculate it but my case is a little bit more complicated because i have to do the calculation based on the state of the engine.

The easy way is to query all the data and do it with java but it takes around 20 seconds to get all the data which is very slowly for my needs.

The hard way is to make a query to the database and tell it to calculate it for me and give me the result only.

So here is an example of the data i have:

          Latitude    Longtitude   Date      Time    Engine 
   row 1: 23.3232     42.43344     130510    120030  1
   row 2: 23.3233     42.43354     130510    120031  1
   row 3: 23.3243     42.43321     130510    120032  1
   row 4: 23.3243     42.43321     130510    120033  0
   row 5: 23.3243     42.43321     130510    120034  0
   row 6: 23.3243     42.43321     130510    120035  1
   row 7: 23.3254     42.43332     130510    120036  1

   and so on....

'Engine' column may be 1 - engine is working or 0 - engine is not working

So i want to calculate the distance between two dates excluding the coordinates where the engine state = 0. So in this case i need the distance between row 1 and row 4 summed with the distance between row 5 and row 7.

Also have in mind that the query first needs to tell the database to order by date, time because sometimes the table data is not sorted.

Anyone knows how to make such a query using MySQL?

P.S. The formula for calculating the distance i want to use is Haversine (NOT Vincety)

Answer 1

I'll just leave it here in case someone needs it. This is an interpretation @Kickstart for a Laravel.

$sub = DB::query()->selectRaw(implode(', ', [
    'Loc1.latitude as latitude_from',
    'Loc1.longitude as longitude_from',
    'Loc2.latitude as latitude_to',
    'Loc2.longitude as longitude_to',
    'ST_Distance_Sphere(point(Loc1.longitude, Loc1.latitude),point(Loc2.longitude, Loc2.latitude)) as dist',
]))->fromSub(function ($query) {

    $query->selectRaw(implode(', ', [
        'latitude',
        'longitude',
        '@Cnt1 := @Cnt1+1 AS SeqCnt',
    ]))->fromSub(function ($query) {

        $query->select([
            'latitude',
            'longitude',
        ])->from('tracker_coords')
            ->where('tracker_id', 155)
            ->whereBetween('created_at', [
                '2020-10-02T13:00:00.000000Z',
                '2020-10-03T07:45:00.000000Z'
            ])
            ->orderBY('created_at');

    }, 'Sub0')->crossJoin(
        DB::raw('(SELECT @Cnt1:=0) as Sub1')
    );
}, 'Loc1')->joinSub(function($query) {

    $query->selectRaw(implode(', ', [
        'latitude',
        'longitude',
        '@Cnt2 := @Cnt2+1 AS SeqCnt',
    ]))->fromSub(function ($query) {

        $query->select([
            'latitude',
            'longitude',
        ])->from('tracker_coords')
            ->where('tracker_id', 155)
            ->whereBetween('created_at', [
                '2020-10-02T13:00:00.000000Z',
                '2020-10-03T07:45:00.000000Z'
            ])
            ->orderBY('created_at');

    }, 'Sub0')->crossJoin(
        DB::raw('(SELECT @Cnt2:=1) Sub1')
    );

}, 'Loc2', 'Loc1.SeqCnt', '=', 'Loc2.SeqCnt')->get();

Simple usage:

$coords = $this->getConcatQueryString([
    't1.latitude as latitude_from',
    't1.longitude as longitude_from',
    't2.latitude as latitude_to',
    't2.longitude as longitude_to',
   'ST_Distance_Sphere(point(t1.longitude,t1.latitude),point(t2.longitude, t2.latitude)) as dist',
], fn($query) => $query
    ->from('tracker_coords')
    ->select([
        'latitude',
        'longitude',
        'created_at',
    ])
    ->where('tracker_id', $tracker->id)
    ->whereBetween('created_at', [
        $item->event_at,
        $item->event_to,
    ])
    ->orderBy('created_at')
)->get()->sum('dist');


private function getConcatQuery(array $select, $f)
{
    return DB::query()->selectRaw(implode(', ', $select))
        ->fromSub(function ($query) use ($f) {
            $query->selectRaw(implode(', ', [
                '*',
                '@Cnt1 := @Cnt1+1 AS SeqCnt',
            ]))->fromSub($f, 'Sub0')->crossJoin(
                DB::raw('(SELECT @Cnt1:=0) as Sub1')
            );
        }, 't1')->joinSub(function($query) use ($f) {
            $query->selectRaw(implode(', ', [
                '*',
                '@Cnt2 := @Cnt2+1 AS SeqCnt',
            ]))->fromSub($f, 'Sub0')->crossJoin(
                DB::raw('(SELECT @Cnt2:=1) Sub1')
            );
        }, 't2', 't1.SeqCnt', '=', 't2.SeqCnt');
}

Answer 2

Assuming you have a function to calculate the Haversine distance you could do something like this:-

SELECT SUM(func_Haversine(Loc1.Latitude, Loc1.Longtitude, Loc2.Latitude, Loc2.Longtitude))
FROM
(
    SELECT Latitude, Longtitude, @Cnt1:=@Cnt1+1 AS SeqCnt
    FROM 
    (
        SELECT Latitude, Longtitude
        FROM SomeTable
        WHERE `Date` BETWEEN '13-05-10' AND '13-05-10'
        AND Engine = 1
        ORDER BY `Date`, `Time`
    ) Sub0
    CROSS JOIN (SELECT @Cnt1:=0) Sub1
) Loc1
INNER JOIN
(
    SELECT Latitude, Longtitude, @Cnt2:=@Cnt2+1 AS SeqCnt
    FROM 
    (
        SELECT Latitude, Longtitude
        FROM SomeTable
        WHERE `Date` BETWEEN '13-05-10' AND '13-05-10'
        AND Engine = 1
        ORDER BY `Date`, `Time`
    ) Sub0
    CROSS JOIN (SELECT @Cnt2:=1) Sub1
) Loc2
ON Loc1.SeqCnt = Loc2.SeqCnt

This is getting all the relevant records, adding a sequence number and getting each record and its subsequent one in a line. Then get the distance between each point, and then sum the distances

You could use the Haversine formula directly in the query but it would be fairly messy to read and unncessary when just giving an idea of basic SQL

You have included a date column and a time column but with your description I have assumed it is only date ranges you are interested in and the time is just used to order the records

I have knocked up and SQLfiddle for the basics of the query (bringing the rows back but without calculating the haversine distance).

http://sqlfiddle.com/#!2/1f014a/10