0

I have the following GPS data. I'm trying to get trips made by the user based on the engine status, and ordered by date where engine=1 is a engine start and engine=2 is a engine off event, so every time I find an engine=2 I know that is the end of a trip. I need to present these trips in a JSON format (I'm doing that with php) but I'm stuck on getting the actual trips individually.

Any help will be appreciated. 

mysql> select imei, lat, lon, date, engine from dataGps where imei = '864251020174383';

+-----------------+-----------+------------+---------------------+--------+
| imei            | lat       | lon        | date                | engine |
+-----------------+-----------+------------+---------------------+--------+
| 864251020174383 | 12.137000 | -86.254501 | 2016-10-04 14:15:21 |      1 |
| 864251020174383 | 12.135400 | -86.253342 | 2016-10-04 14:16:24 |      1 |
| 864251020174383 | 12.134140 | -86.251671 | 2016-10-04 14:19:12 |      1 |
| 864251020174383 | 12.134770 | -86.250549 | 2016-10-04 14:19:57 |      1 |
| 864251020174383 | 12.135820 | -86.249687 | 2016-10-04 14:19:57 |      1 |
| 864251020174383 | 12.136580 | -86.248581 | 2016-10-04 14:20:02 |      1 |
| 864251020174383 | 12.137000 | -86.247551 | 2016-10-04 14:20:02 |      1 |
| 864251020174383 | 12.137160 | -86.246262 | 2016-10-04 14:20:03 |      1 |
| 864251020174383 | 12.137080 | -86.245621 | 2016-10-04 14:22:33 |      1 |
| 864251020174383 | 12.136490 | -86.243942 | 2016-10-04 14:23:28 |      1 |
| 864251020174383 | 12.135990 | -86.243080 | 2016-10-04 14:43:05 |      1 |
| 864251020174383 | 12.135820 | -86.241798 | 2016-10-04 14:43:57 |      1 |
| 864251020174383 | 12.135820 | -86.240211 | 2016-10-04 14:47:04 |      1 |
| 864251020174383 | 12.132720 | -86.237892 | 2016-10-04 14:49:02 |      1 |
| 864251020174383 | 12.133971 | -86.238281 | 2016-10-04 15:11:27 |      2 |
| 864251020174383 | 12.104250 | -86.253792 | 2016-10-11 20:01:36 |      1 |
| 864251020174383 | 12.105340 | -86.251129 | 2016-10-11 20:01:45 |      1 |
| 864251020174383 | 12.106010 | -86.249069 | 2016-10-11 20:02:02 |      1 |
| 864251020174383 | 12.102820 | -86.245644 | 2016-10-11 20:02:20 |      1 |
| 864251020174383 | 12.087050 | -86.231468 | 2016-10-11 20:02:32 |      1 |
| 864251020174383 | 12.065980 | -86.212334 | 2016-10-11 20:02:48 |      1 |
| 864251020174383 | 12.065560 | -86.208298 | 2016-10-11 20:02:56 |      1 |
| 864251020174383 | 12.064720 | -86.205040 | 2016-10-11 20:03:12 |      1 |
| 864251020174383 | 12.064050 | -86.202888 | 2016-10-11 20:03:20 |      2 |
+-----------------+-----------+------------+---------------------+--------+

Trip 1:

| 864251020174383 | 12.137000 | -86.254501 | 2016-10-04 14:15:21 |      1 |
| 864251020174383 | 12.135400 | -86.253342 | 2016-10-04 14:16:24 |      1 |
| 864251020174383 | 12.134140 | -86.251671 | 2016-10-04 14:19:12 |      1 |
| 864251020174383 | 12.134770 | -86.250549 | 2016-10-04 14:19:57 |      1 |
| 864251020174383 | 12.135820 | -86.249687 | 2016-10-04 14:19:57 |      1 |
| 864251020174383 | 12.136580 | -86.248581 | 2016-10-04 14:20:02 |      1 |
| 864251020174383 | 12.137000 | -86.247551 | 2016-10-04 14:20:02 |      1 |
| 864251020174383 | 12.137160 | -86.246262 | 2016-10-04 14:20:03 |      1 |
| 864251020174383 | 12.137080 | -86.245621 | 2016-10-04 14:22:33 |      1 |
| 864251020174383 | 12.136490 | -86.243942 | 2016-10-04 14:23:28 |      1 |
| 864251020174383 | 12.135990 | -86.243080 | 2016-10-04 14:43:05 |      1 |
| 864251020174383 | 12.135820 | -86.241798 | 2016-10-04 14:43:57 |      1 |
| 864251020174383 | 12.135820 | -86.240211 | 2016-10-04 14:47:04 |      1 |
| 864251020174383 | 12.132720 | -86.237892 | 2016-10-04 14:49:02 |      1 |
| 864251020174383 | 12.133971 | -86.238281 | 2016-10-04 15:11:27 |      2 |

Trip 2:

| 864251020174383 | 12.104250 | -86.253792 | 2016-10-11 20:01:36 |      1 |
| 864251020174383 | 12.105340 | -86.251129 | 2016-10-11 20:01:45 |      1 |
| 864251020174383 | 12.106010 | -86.249069 | 2016-10-11 20:02:02 |      1 |
| 864251020174383 | 12.102820 | -86.245644 | 2016-10-11 20:02:20 |      1 |
| 864251020174383 | 12.087050 | -86.231468 | 2016-10-11 20:02:32 |      1 |
| 864251020174383 | 12.065980 | -86.212334 | 2016-10-11 20:02:48 |      1 |
| 864251020174383 | 12.065560 | -86.208298 | 2016-10-11 20:02:56 |      1 |
| 864251020174383 | 12.064720 | -86.205040 | 2016-10-11 20:03:12 |      1 |
| 864251020174383 | 12.064050 | -86.202888 | 2016-10-11 20:03:20 |      2 |
Improve this question
1
  • Can you post a sample JSON format?
    – McNets
    Mar 13, 2017 at 23:53

1 Answer 1

Reset to default
0

Use a variable to set a group (trip), and increase it value every time engine = 2

select imei, lat, lon, date, engine, 
       if (@last_engine = 2, @grp := @grp + 1, @grp := @grp) as trip,
       @last_engine := engine
from   (select @grp := 1) x,
       (select imei, lat, lon, date, @last_engine := engine as engine from locs order by date) y
;


| imei            | lat       | lon        | date                | engine | trip|
|-----------------|-----------|------------|---------------------|--------|-----|
| 864251020174383 | 12,137000 | -86,254501 | 04.10.2016 14:15:21 | 1      | 1   |
| 864251020174383 | 12,135400 | -86,253342 | 04.10.2016 14:16:24 | 1      | 1   |
| 864251020174383 | 12,134140 | -86,251671 | 04.10.2016 14:19:12 | 1      | 1   |
| 864251020174383 | 12,134770 | -86,250549 | 04.10.2016 14:19:57 | 1      | 1   |
| 864251020174383 | 12,135820 | -86,249687 | 04.10.2016 14:19:57 | 1      | 1   |
| 864251020174383 | 12,136580 | -86,248581 | 04.10.2016 14:20:02 | 1      | 1   |
| 864251020174383 | 12,137000 | -86,247551 | 04.10.2016 14:20:02 | 1      | 1   |
| 864251020174383 | 12,137160 | -86,246262 | 04.10.2016 14:20:03 | 1      | 1   |
| 864251020174383 | 12,137080 | -86,245621 | 04.10.2016 14:22:33 | 1      | 1   |
| 864251020174383 | 12,136490 | -86,243942 | 04.10.2016 14:23:28 | 1      | 1   |
| 864251020174383 | 12,135990 | -86,243080 | 04.10.2016 14:43:05 | 1      | 1   |
| 864251020174383 | 12,135820 | -86,241798 | 04.10.2016 14:43:57 | 1      | 1   |
| 864251020174383 | 12,135820 | -86,240211 | 04.10.2016 14:47:04 | 1      | 1   |
| 864251020174383 | 12,132720 | -86,237892 | 04.10.2016 14:49:02 | 1      | 1   |
| 864251020174383 | 12,133971 | -86,238281 | 04.10.2016 15:11:27 | 2      | 1   |
| 864251020174383 | 12,104250 | -86,253792 | 11.10.2016 20:01:36 | 1      | 2   |
| 864251020174383 | 12,105340 | -86,251129 | 11.10.2016 20:01:45 | 1      | 2   |
| 864251020174383 | 12,106010 | -86,249069 | 11.10.2016 20:02:02 | 1      | 2   |
| 864251020174383 | 12,102820 | -86,245644 | 11.10.2016 20:02:20 | 1      | 2   |
| 864251020174383 | 12,087050 | -86,231468 | 11.10.2016 20:02:32 | 1      | 2   |
| 864251020174383 | 12,065980 | -86,212334 | 11.10.2016 20:02:48 | 1      | 2   |
| 864251020174383 | 12,065560 | -86,208298 | 11.10.2016 20:02:56 | 1      | 2   |
| 864251020174383 | 12,064720 | -86,205040 | 11.10.2016 20:03:12 | 1      | 2   |
| 864251020174383 | 12,064050 | -86,202888 | 11.10.2016 20:03:20 | 2      | 2   |

Then select using trip number:

select imei, lat, lon, date, engine
from (select imei, lat, lon, date, engine, 
             if (@last_engine = 2, @grp := @grp + 1, @grp := @grp) as grp,
             @last_engine := engine
      from   (select @grp := 0) x,
             (select imei, lat, lon, date, @last_engine := engine as engine from locs order by date) y) z
where trip = 1
;


| imei            | lat       | lon        | date                | engine |
|-----------------|-----------|------------|---------------------|--------|
| 864251020174383 | 12,137000 | -86,254501 | 04.10.2016 14:15:21 | 1      |
| 864251020174383 | 12,135400 | -86,253342 | 04.10.2016 14:16:24 | 1      |
| 864251020174383 | 12,134140 | -86,251671 | 04.10.2016 14:19:12 | 1      |
| 864251020174383 | 12,134770 | -86,250549 | 04.10.2016 14:19:57 | 1      |
| 864251020174383 | 12,135820 | -86,249687 | 04.10.2016 14:19:57 | 1      |
| 864251020174383 | 12,136580 | -86,248581 | 04.10.2016 14:20:02 | 1      |
| 864251020174383 | 12,137000 | -86,247551 | 04.10.2016 14:20:02 | 1      |
| 864251020174383 | 12,137160 | -86,246262 | 04.10.2016 14:20:03 | 1      |
| 864251020174383 | 12,137080 | -86,245621 | 04.10.2016 14:22:33 | 1      |
| 864251020174383 | 12,136490 | -86,243942 | 04.10.2016 14:23:28 | 1      |
| 864251020174383 | 12,135990 | -86,243080 | 04.10.2016 14:43:05 | 1      |
| 864251020174383 | 12,135820 | -86,241798 | 04.10.2016 14:43:57 | 1      |
| 864251020174383 | 12,135820 | -86,240211 | 04.10.2016 14:47:04 | 1      |
| 864251020174383 | 12,132720 | -86,237892 | 04.10.2016 14:49:02 | 1      |
| 864251020174383 | 12,133971 | -86,238281 | 04.10.2016 15:11:27 | 2      |

As far I don't really know final JSON format, I've used json_array function.

select json_array(imei, lat, lon, date, engine)
from (select imei, lat, lon, date, engine, 
             if (@last_engine = 2, @grp := @grp + 1, @grp := @grp) as trip,
             @last_engine := engine
      from   (select @grp := 0) x,
             (select imei, lat, lon, date, @last_engine := engine as engine from locs order by date) y) z
where trip = 1
;

| json_array(imei, lat, lon, date, engine)                                    |
|-----------------------------------------------------------------------------|
| ["864251020174383", 12.137000, -86.254501, "2016-10-04 14:15:21.000000", 1] |
| ["864251020174383", 12.135400, -86.253342, "2016-10-04 14:16:24.000000", 1] |
| ["864251020174383", 12.134140, -86.251671, "2016-10-04 14:19:12.000000", 1] |
| ["864251020174383", 12.134770, -86.250549, "2016-10-04 14:19:57.000000", 1] |
| ["864251020174383", 12.135820, -86.249687, "2016-10-04 14:19:57.000000", 1] |
| ["864251020174383", 12.136580, -86.248581, "2016-10-04 14:20:02.000000", 1] |
| ["864251020174383", 12.137000, -86.247551, "2016-10-04 14:20:02.000000", 1] |
| ["864251020174383", 12.137160, -86.246262, "2016-10-04 14:20:03.000000", 1] |
| ["864251020174383", 12.137080, -86.245621, "2016-10-04 14:22:33.000000", 1] |
| ["864251020174383", 12.136490, -86.243942, "2016-10-04 14:23:28.000000", 1] |
| ["864251020174383", 12.135990, -86.243080, "2016-10-04 14:43:05.000000", 1] |
| ["864251020174383", 12.135820, -86.241798, "2016-10-04 14:43:57.000000", 1] |
| ["864251020174383", 12.135820, -86.240211, "2016-10-04 14:47:04.000000", 1] |
| ["864251020174383", 12.132720, -86.237892, "2016-10-04 14:49:02.000000", 1] |
| ["864251020174383", 12.133971, -86.238281, "2016-10-04 15:11:27.000000", 2] |

Or a json_object function:

select json_object('imei',imei,'lat',lat,'lon',lon,'date',date,'engine',engine)
from (select imei, lat, lon, date, engine, 
             if (@last_engine = 2, @grp := @grp + 1, @grp := @grp) as trip,
             @last_engine := engine
      from   (select @grp := 0) x,
             (select imei, lat, lon, date, @last_engine := engine as engine from locs order by date) y) z
where trip = 1
;


| json_object('imei',imei,'lat',lat,'lon',lon,'date',date,'engine',engine)                                            |
|---------------------------------------------------------------------------------------------------------------------|
| {"lat": 12.137000, "lon": -86.254501, "date": "2016-10-04 14:15:21.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.135400, "lon": -86.253342, "date": "2016-10-04 14:16:24.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.134140, "lon": -86.251671, "date": "2016-10-04 14:19:12.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.134770, "lon": -86.250549, "date": "2016-10-04 14:19:57.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.135820, "lon": -86.249687, "date": "2016-10-04 14:19:57.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.136580, "lon": -86.248581, "date": "2016-10-04 14:20:02.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.137000, "lon": -86.247551, "date": "2016-10-04 14:20:02.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.137160, "lon": -86.246262, "date": "2016-10-04 14:20:03.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.137080, "lon": -86.245621, "date": "2016-10-04 14:22:33.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.136490, "lon": -86.243942, "date": "2016-10-04 14:23:28.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.135990, "lon": -86.243080, "date": "2016-10-04 14:43:05.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.135820, "lon": -86.241798, "date": "2016-10-04 14:43:57.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.135820, "lon": -86.240211, "date": "2016-10-04 14:47:04.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.132720, "lon": -86.237892, "date": "2016-10-04 14:49:02.000000", "imei": "864251020174383", "engine": 1} |
| {"lat": 12.133971, "lon": -86.238281, "date": "2016-10-04 15:11:27.000000", "imei": "864251020174383", "engine": 2} |

Check it here: http://rextester.com/TGLM4008

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.