Calculating Journey Summary made by a vehicle and Cache them to database

Question

This is more of a point me in the right direction kind of question, I have 2 solutions but both of them are invalid because the data calculated is invalid in some scenarios.

I am working on a system in which a vehicle sends location readings to a TCP Server.

All this data gets stored in a Location Log table with the following data -

Latitude, Longitude, Speed, DateTime, Event, Address (and other relevant data)

An Event can be Periodic, Ignition On, Ignition Off, Overspeeding, Harsh Braking, Harsh Acceleration, Harsh Turning etc.

Now all my reports are generated based on the entire Journey i.e. A journey would be all the location logs between an Ignition On to Ignition Off.

So a simple report would be

Vehicle ID  
Journey Start Date Time   
Journey End Date Time  
Start Address 
End Address 
Distance Travelled (KM) 
Journey Duration  
No. Overspeedings 
No. Harsh Turnings  
Average Speed  
Max Speed 

Now each time I generate a report it goes to the LocationLog table and calculates all the Journeys during a Date Range.

Which is an expensive process when there are 300 vehicles making 50 Journeys a day over 3 months, when there is ONE location Log per minute.

My Question is that,

What is the best way to Calculate and Cache these Journeys to a separate table? Because Once a Journey has completed the properties won't change.

I have implemented both of the following solutions - But both of them don't work.

---

1 - Whenever there is an Ignition Off event - I fire an event to calculate the Journey details and cache to the database.

THIS DOESN'T WORK - Because in some cases the Ignition On/Off Events are received before sending Periodic Readings. [Periodic readings have a lower priority than Events]

This is causing the invalid calculations of Distance, No. of Overspeedings etc. because they will be received after the device has sent the Ignition readings.

---

2 - I've developed a windows service which runs a daily task - Which calculates the Journeys for the entire day and Cache them to the Database.

THIS DOESN'T WORK - Because in some cases the Vehicle is Travelling overnight, which causes that particular journey to not be Calculated and Cached.

By the technologies on this project are - C#, .NET, Entity Framework, SQL Server 2016.

Answer 1

I agree with everything that @candied_orange says.  In addition:

You didn't mention whether the message sends re-establish a new TCP connection or attempt to continue to use one connection (I rather assume the former).

You have to put some kind of box around late arriving events, and even if you can, perhaps even still, be prepared to update cached calculation as such late arriving events come in (if calculation has happened).

This is not unlike a connection-oriented network protocol like TCP, which detects out of order packets, dropped packets, etc.. — in short, the problem may not be fully solvable without making changes to the event message protocol.  (The difference being perhaps that you might want such protocol features on top and in between TCP connections, i.e. at the application level.)

If you can solve these, then perhaps you can at least know whether you've received all of the events, and be able to label a level of confidence in the generated report.

If you cannot solve these concerns by shoring up the messaging protocol (assuming it doesn't already have the necessary qualities), then you will have to educate consumers of reports of the potential for inaccuracy due to missing or late arriving events messages.

One way to mitigate would be to offer notification to the consumers that a report they had viewed was necessarily updated, in the case of late arriving event messages.  (I don't know what could be done about dropped event messages, if there is no message number or something else like TCP has.)

---

You need to know more details about what happens if

• a periodic event could not be sent (i.e. bad reception, no internet) while moving, and then the car is parked for a while where there is no reception (large parking garage).  After a few days, the car returns to an area of good internet reception.  Will it send very late packets, e.g. days old?

• an event message is dropped (never received)

A protocol detecting missing and out of order messages should be uniformly applied to messages of all priorities, so that, for example, the ignition off message would indicate whether or not there are (how many) outstanding event messages there are.

Answer 2

My assumptions are:

1) Events are stored in some sort of RDBMS (like mySQL) or noSQL database like MongoDB.

2) Events travel from vehicle to TCP server via G4 network or Similar and do not pass through an intermediate servers - apart from your telecom provider's message buffers.

3) The vehicle "box" enforces the message priority - by sending non-periodic events first.

4) The vehicle "box" adds a timestamp to all messages.

5) The vehicle does not add journey-sequence numbers to each message.

6) There is also the possibility of individual messages being lost entirely for a variety of reasons.

So you end up with a message log where messages can be 1) out of order or 2) incomplete at any given moment. Worse is that you may not know (in the absence of message sequence numbers ) that messages are missing. These are the reasons your solutions will not have 100% hit rate.

In such a system there is no way to complete a 100% correct journey report ever, because of the possibility that one or more Periodic events are missing altogether or are still in transit.

The best way to handle this is to add a "confidence" and "comment" columns to your report. This is the professional way to recognise that its impossible to be 100% correct, while enabling you to state your confidence level + the reason.

You can be acceptably close to 100% confident of any report if you run tests to make sure there are no unexplained delays between GPS readings (the driver could have been sleeping - or took an unapproved route etc).

If a journey looks inconsistent, put it in a separate list and reprocess it in the next run.

You can increase your certainty about any report by:

a) scanning the message queue on the TCP/IP server for any message in transit (if you have access to the queue).

b) checking the message sequence numbers (if the vehicle box transmits them)

c) performing consistency checks on all journey reports to make sure they make sense and you can be reasonably sure nothing is missing.

If you do all of this then you will have a better system, but will never be 100% correct for the reasons given.

EDIT:

The consistency checks really depend on your business rules (you did not mention what the fleet does). However, here are some pointers:

1) Verify journeys against dispatch records. ie did the executed trip match what was dispatched to the driver?

2) If there is a long gap between period records, calculate the distance travelled (haversine formula) and time taken, between successive period messages that have been received. Allowing for "as the crow flies" effects you can assess if average speed makes sense or not, or if large chunks of journey are missing.

3) Are journeys split - ie did the drive switch off the engine mid-journey causing a new journey to initiate.

4) If you have good end-to-end protocols, they would emit alerts if messages were missed or delayed. Check error logs for server messages. Depending on how your protocols are implemented, and your ability to communicate with the vehicle boxes and server you may be able to request reports of undelivered/received messages.

Good luck!

Answer 3

• Record all the data you get, as is.
• Use Ignition on/off events as markers. Not as report calculation triggers.
• Trigger report calculation when periodic data shows the vehicle has been stationary for some fixed amount of time. Or just when someone requests it.