bipin_s bipin_s - 5 months ago 15
SQL Question

mysql - determine stoppages at a particular location

I have a mysql table as follows(5 coloumns):


id |Vehicle_no |Time | latitude |Longitude

1 |Abc123 |2015-09-15 20:15:51 | 26.211718 | 86.877197

2 |Abc123 |2015-09-15 20:16:51 | 26.211718 | 86.877197

3 |Abc123 |2015-09-15 20:17:51 | 26.211718 | 86.877197

4 |Abc123 |2015-09-15 20:18:51 | 26.211718 | 86.877197

5 |Abc123 |2015-09-15 20:19:51 | 26.211718 | 86.877197

6 |Abc123 |2015-09-15 20:20:51 | 26.212718 | 86.878197

7 |Abc123 |2015-09-15 20:21:51 | 26.212728 | 86.878200

8 |Abc123 |2015-09-15 20:22:51 | 26.212738 | 86.877201


The above values I am getting from a GPS receiver every minute. If the latitude and longitude remains same say for e.g more than 5 minutes at a particular day I must indicate that the vehicle is stationary at this location (lat. and long.) on this particular date. In table for id. no 6, 7 there is different lat. and long which means vehicle is moving.
The values of lat. and long may be repeated because I am crossing the same route everyday(same lat. and long.) but the time and date may be different.

Answer
select id, 1 as is_stationary from positions p
where not exists (select 0 from positions pp
    where p.time > pp.time
    and datediff(p.time, pp.time) <= 5/(60*24)
     and p.latitude <> pp.latitude
     and p.longitude <> pp.longitude)

I don't know what you want to do with the result -- write it to a new column or whatever -- so consider stuff up through "from" on the first line as a dummy placeholder.