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:

Journey of all 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:

Desired outcome

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

  • Welcome to GIS SE. As a new user, please take the Tour. The focus here is the problem, so I suggest you rewrite the question to remove your first two paragraphs, and get directly into the problem, providing details of what you are actually doing. Diagrams would not be inappropriate. – Vince Nov 6 '17 at 15:10
  • Could you clarify: you say twice that there is no GPS position for the following vehicles, only for veh. 1, but your map shows GPS points for all 3, and then you mention lat/lon, timestamp data in the database. Which is it? Also, if you have an SQL solution working 80%, could you share it, and explore why the 20% isn’t working? – Simbamangu Nov 9 '17 at 4:52
  • @Simbamangu - I've updated my question, hopefully that provides the clarification you need? – kaigoh Nov 10 '17 at 8:05
  • How fast dou you need the result? Can you wait for the train to move? And how often do you receive gps psotition? – Legisey Nov 10 '17 at 9:05
  • Another question: do you want to find a solution for a SINGLE 'position event', where you have one time where you have 1 accurate position (lead car) and 2 low-accuracy positions (following cars)? OR ... do you have multiple samples of positions at different times? – Simbamangu Nov 11 '17 at 6:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.