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
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.