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 |
  • Can you post a sample JSON format? – McNets Mar 13 '17 at 23:53
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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