13 Replies Latest reply on Jun 20, 2018 6:45 PM by Chris McClellan

# Calculating Distance between multiple coordinates for multiple routes

Hi, I want to calculate distance of multiple points(from my lat long field) as shown in the pic. As this is a vehicle tracking data, the data comes in at every seconds/milliseconds, therefore causing numerous map points.

I've check in the forum but could not find any possible solution(for numerous point) for multiple route of different vehicle in 1 data source

Can i check if anyone is able to calculate the distance of such map points as this is just a tiny fraction of the whole map.

Timestamp is included in the data if needed.

Will greatly appreciate if anyone can help me with this!

• ###### 1. Re: Calculating Distance between multiple coordinates for multiple routes

Do you want the entire distance travelled ? as in start of the trip to the end of the trip ?

Do you have some sample data ?

If you have something you can put on Path (maybe even the timestamp will work), then you should get a line rather than points.

• ###### 2. Re: Calculating Distance between multiple coordinates for multiple routes

Hi,
Yes i will like to have the full travel distance of each vehicle from the start to the end.
My data is as shown below(similar data due to privacy considerations):

x = model

date time as 1 field

long

lat

x,14/1/2018 1:06:12 PM,103.764280,1.331031

x,14/1/2018 1:06:12 PM,103.764253,1.330950

x,14/1/2018 1:06:11 PM,103.764242,1.330870

x,14/1/2018 1:06:11 PM,103.764226,1.330784

x,14/1/2018 1:06:10 PM,103.764210,1.330709

x,14/1/2018 1:06:10 PM,103.764189,1.330645

x,14/1/2018 1:06:09 PM,103.764178,1.330575

x,14/1/2018 1:06:09 PM,103.764172,1.330495

x,14/1/2018 1:06:08 PM,103.764162,1.330414

x,14/1/2018 1:06:08 PM,103.764162,1.330355

x,14/1/2018 1:06:07 PM,103.764156,1.330275

x,14/1/2018 1:06:07 PM,103.764146,1.330205

x,14/1/2018 1:06:06 PM,103.764140,1.330108

x,14/1/2018 1:06:06 PM,103.764124,1.330039

• ###### 3. Re: Calculating Distance between multiple coordinates for multiple routes

Does this look right ?

Can you add in more data to check that the distance calculation is accurate ?

• ###### 4. Re: Calculating Distance between multiple coordinates for multiple routes

Hi!!!! It looks good.. Just want to check on your steps to do this then i will try it out. Due to confidential issues, not able to upload more datas.
But what you've done looks correct!

• ###### 5. Re: Calculating Distance between multiple coordinates for multiple routes

Did you see in the Distance calculated field I used a Great Circle calculation.  This simply calculates the distance between the current point and the previous one (based on time), then the title adds up all the calculations.

• ###### 6. Re: Calculating Distance between multiple coordinates for multiple routes

Hi
Is tableau smart enough to associate the current point and previous point with respect to specific models? As attached above was just 1 model. But what if i have multiple model?
Do I need to edit the Great circle calculations?

• ###### 7. Re: Calculating Distance between multiple coordinates for multiple routes

The way I designed the solution, the assumption was that you would only be filtered to a single Model value at any time.

What are you wanting to do if you're looking at multiple models - on a daily basis ?

It's easier when we know the full problem that you're trying to solve, because "point solutions" might not work when you try to implement in the real world (if you know what I mean)

• ###### 8. Re: Calculating Distance between multiple coordinates for multiple routes

Hello

As of now I tried to implement the solution filtered to a single model as mentioned by you.

But I cant generate the calculated distance. Can I check with you on the "WINDOW_SUM([Distance])" function. How does it operate and calculate the distance as comparing to the

great circle calculation. I don't know if my concept is correct, but i'm still trying to understand the whole thing. Thank you for your patience !!!

• ###### 9. Re: Calculating Distance between multiple coordinates for multiple routes

Think if it this way - your data is a series of points (you can see that in your original map).

The great circles calculation that I did takes every point and the previous point to that one (based on the timestamp) and calculates the distance between the 2. Because the two points are only about 1 second apart then the distance is very small (how far can you travel in 1 second ?).

The WINDOW_SUM simply takes all those distances and sums them together (for the part of the trip you have filtered for on the sheet).

I understand the need to protect your confidential data, but if you can provide more data (maybe a few different models) then it will be easier to show you.  It just depends on how confidential you feel the information is about where your vehicles were 6 months ago.

• ###### 10. Re: Calculating Distance between multiple coordinates for multiple routes

Thank you for your clear explanation!

I will request from my boss to see how much information can be shared before getting back to you!

Just a quick question to back my understanding, the distance is calculated from 1 point to the next point looking at the timestamp, right?

So if example, my 1st point (time: 3:45:15 PM, lat..., long...), 2nd point(3:45:17PM,lat...,long...) then my 3rd point at (6:30:00PM,lat long captured 10km away from point 2)
Does it means that the calculation of point 2 to 3 will be a direct straight line? Thus causing additional distance calculated?
Based on the system, the vehicle can actually pause their transmission of data before proceeding to another location then resuming their transmission again.

Can tableau work in a way that it is smart enough to isolate point 1 to point 2 as a single journey
Then from point 3 onward as another journey?

• ###### 11. Re: Calculating Distance between multiple coordinates for multiple routes

Because I wrote it the calculation as a LOOKUP -1 you're actually going backwards, so it does Point2 to Point1, Point3 to Point2, Point 4 to Point3, etc...

Tableau doesn't do anything else automatically, that's why I asked how you determine the finish of one journey and the start of the next.  Is that based on a difference in time; is there another field in the data; is it simply "midnight", as in no vehicles are mid-trip at midnight.

You're also correct - it can only calculate a straight line between each point because that's all the information it has ... your sample data seems to be 2 readings per second which is great.  If you have a reading every 3 hours then it's not going to be very accurate and can only do a straight line distance between those points.

• ###### 12. Re: Calculating Distance between multiple coordinates for multiple routes

The limitation of these data is that there isn't a field that indicates the end of a journey or the start of a journey.

The data transmission rate isn't consistent either.

There isn't any restriction for the vehicle to be active pass midnight. Understand that tableau doesn't work well if it crosses the midnight mark, but is it possible to calculate by each day before midnight, then piece them together?

But looking at the data we are having, I feel that there's lots of limitations for us to do duration and distance calculation accurately. Thus, is there any platform that you are aware of that you would recommend me to explore?

• ###### 13. Re: Calculating Distance between multiple coordinates for multiple routes

If you don't have the proper data then nothing will help really, you can use a few techniques to split the data for each day but nothing is going to give you accurate distance measurements if you don't have the points in between.