I'm working on analyzing NYC MTA Bus traffic data.
There are numerous buses traveling along the route, emitting gps data at random locations.
I have about 30k records with bus_line, vehicle_id, longitude, latitude, bus_speed and timestamp. I refer to this dataset as dataset1
Calculating the speeds for an individual bus is trivial but I can't think of a simple way of averaging the speed in a particular leg along the route.
One idea I'm thinking about is to create another dataset (i.e., "dataset2") with longitude & latitude in 50 ft intervals. Then find a way to assign its points to the points from dataset1 based on proximity. At this point, it would be possible to find the average speed for each point because there will be multiple records with the same longitude and latitude.
The biggest challenge I see is in assigning the points from dataset2 to the points in dataset1: you need to sort the points exactly in the order they "appear" in the route. You can't simply order by longitude or latitude. You can sort conditionally such that if the bus is going west, you sort by longitude. If it's going north, you sort by latitude. However, I'd like something simpler if it exists. I'd appreciate any help!