I have a set of vehicles (think railway) that are in a (semi)fixed formation. I know the lead vehicle and I know the GPS coordinates of the lead vehicle at any given time. Sometimes, I can rely on the on board systems on the vehicle to report the other vehicles in the set, along with the order of the vehicle. This information is not always available though, depending on the type of vehicle.
I have already got a pretty good method of determining which vehicles are travelling together. What I need to solve is in what order are they travelling, starting with the lead vehicle and working backwards.
I have limited spatial data to work with, but what I do have is vehicle number, timestamp, latitude and longitude. The data is stored in MySQL, the coordinates are stored both separate and as a Point(long, lat) for spatial indexing.
I think I'm getting close, around 80% accuracy, based on a small sample. What I am basically doing is measuring the distance from the known lead vehicle at any given timestamp to the coordinates from the other vehicles using ST_Distance_Sphere. It's pretty good, but not quite there yet.
Has anyone done anything similar before? Or does anyone have any good ideas how I can tackle this in a better way? If possible, I'd prefer to use the GIS functions in MySQL, but this isn't a prerequisite.
Here is the map of the combined GPS points for three vehicles:
This is a simple diagram of the real life situation, with vehicle 1 being accurate, 2 and 3 being known but the positions unknown:
Update 1: Clarification
I have GPS coordinates and timestamps for all vehicles. What I need is the order in which the vehicles are travelling relative to the lead vehicle.
The SQL I already have gives me the definitive lead vehicle (based on some other data) and the other vehicles that are travelling with the lead. What it doesn't give me is the ordering of the vehicles (except the lead, or position 1).