I work in local government and I am constructing a BI report using telematics data from refuse collection vehicles combined with weighbridge data from our waste disposal partners to analyse waste composition from residential waste in specific geographic locations.
Our telematics system on our refuse collection fleet can be used to generate reports on the location of every lifted residential bin including datetime, vehicle registration and the WGS84 latitude and longitude.
Vehicles are unloaded at the waste disposal plant several times a day, providing a ticket number along with the weight, type and quality of the individual load which we receive as via email on a regular basis. This includes the vehicle registration and the datetime.
Complicating matters; vehicles are often swapped between teams depending on queues and breakdowns, can assist other times and vice versa, and spare vehicles are sometimes used. This means it is not adequate to simply match the daily summary total of bin lifts to a date and vehicle.
My solution to this is to join each telematics record of a bin being lifted to the specific ticket from the weighbridge for when the vehicle was next emptied, which over time will allow for spatial analysis indicating specific areas where non-recycling waste is being put into the recycling collection.
In short, I want the point data for each specific bin lift to be joined with the ticket number from the load its contents were tipped with.
My main issues are joins based on multiple criteria, joins based on less-than datetimes, and how to combine the two.
Any help with the join would be greatly appreciated.
Telematics Data:
Datetime, Vehicle Registration, Latitude, Longitude
Weighbridge Data:
Datetime, Vehicle Registration, Weight, Quality