# Calculating average speed in various legs of bus route

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.

The general approach to such problems is to

1. match the points to the street network, see Different approaches for map matching : links / ideas?
2. route between consecutive points
3. using route length and time difference between consecutive points, calculate the vehicle's average speed and assign it to the used street segments
4. average the individual speed values on each street segment to get average travel speed