10 Replies Latest reply on Nov 16, 2017 12:09 PM by Michael Hesser

Calculate the geographical distance between two points on a map

Hey guys,

is it possible to calculate the geographical distance between to points a map which are based on longitude and latitude?

For instance, I would like Tableau to show the distance to the nearest next point around the marked orange point.

Does anyone has an idea how to create a measure like this?

• 1. Re: Calculate the geographical distance between two points on a map

Hello David;

You may find an answer here:

BlueMM: Excel formula to calculate distance between 2 latitude, longitude (lat/lon) points (GPS positions)

They list a formula that should be able to work.

The formula they came up with was:

To calculate distances in miles, substitute R (6371) with 3958.756 (and for nautical miles, use 3440.065)

Hope this helps!

1 of 1 people found this helpful
• 2. Re: Calculate the geographical distance between two points on a map

Hello Michael,

how do I have to determine the "Lat1" and "Lat2", if the data comes from an excel file like the following?

Best, David

• 3. Re: Calculate the geographical distance between two points on a map

Hello David;

I'm using the information from your first sheet.

I started by promoting your Latitude/Longitude to dimensions; this allowed me to drop them on the map and get a nice scatter plot of your locations.

I also created two calculations-- [Focal Lat] and [Focal Long] to show hold the value of the city you want as your center.

Next I applied the distance formula,

Distance

I dropped this into color and got the nice display you should see. With a little finesse, you *SHOULD* be able to use that same distance formula (and a LOD MIN) to find the closest city.

IMPORTANT: I found three cities where the distance formula isn't working. Anyone have ideas why?

• 4. Re: Calculate the geographical distance between two points on a map

Hi Michael,

that goes definitely in the right direction!

Do you think it's possible to alter the [Focal Lat] and [Focal Lot] a bit more dynamic?

That the quick filter shows the distance to the closest store?

Thank a lot!

• 5. Re: Calculate the geographical distance between two points on a map

I'm most certain it can be done. Just HOW to do it may be a little beyond my skill set.

It seems like there should be an easy way to get this (dropping them into Context and then polling for results?) but I wasn't able to find it.

Let me keep thinking. The forum is filled with people a heck of a lot smarter than me: one of them may come along and solve this last step for you!

• 6. Re: Calculate the geographical distance between two points on a map

Hello David!

Well... I may have gotten you a step or two closer.

While this doesn't use Dashboard actions to specify the center city/station, it does allow the user to control it through a drop-down box.

Getting the Dashboard Action to play is escaping me now... but perhaps you can post and someone else can solve this dilemma?

--Michael

1 of 1 people found this helpful
• 7. Re: Calculate the geographical distance between two points on a map

Thanks Guys, Great discussion. I was able to implement the same thing on my report where I wanted to find the distance between two zips on the map. One being fixed (Focal Lat, Long) and the other on the map. I too used LOD, and AVG({Fixed:Avg(Focal Lat)}).

Now I am trying to get this information so that I can use it in a calculation within a table. But thats a whole new issue.

Thanks again.

• 8. Re: Calculate the geographical distance between two points on a map

Awesome! It's great to see that-- even though it's over a year old-- some discussions are still holding their own

• 9. Re: Calculate the geographical distance between two points on a map

I am a little new to tableau and have not created any formulas.  I have plotted two groups on my maps but now need to add the formula for plotting zip codes.  Basically, I want to enter one persons zip code and have tableau map the closest agent to that zip code.  I see the formula but how do I add this to tableau?

• 10. Re: Calculate the geographical distance between two points on a map

Hi Steve Varlas :

The bad news: in my admittedly limited work with Tableau and distance-finding, what you're asking for can be tricky. This is primarily because while Tableau can convert an element like State or City or zip-code into Lat/Long, those values cannot be easily "grabbed" and used in calculations. It's a case of wanting your cake and eating it too, I guess.

You do have some options:

Option #1: You COULD use a file of every single zip-code and its associated lat/long. When the user selects or enters the zip, the zip is cross referenced, returning a lat/long which can be used to find the nearest agent.

Option #2: You COULD push the zip to R or another fancy number-manipulator (Alertyx? Python?) to do the heavy lifting and pull the values for you. I regret I'm not skilled in any of these (not yet, at least), so I can't do anything more than say it's a possibility.

Option #3: You might be able to have Tableau output the calculated lat/long values from the zip into another file and pull from that. This probably not a clean answer because it requires your user to do some work, and if you have multiple users at once (or are using a .twbx), who knows what results you might get. Nevertheless, in a pinch I've used it to get the data I need.

Option #4: Hold onto your horses! Tableau will soon release its Extensions API package, which can turn Tableau from a visual tool into one that can actually change and manipulate data. In the presentation at TC17, they demonstrated an extensions packet that charted step-by-step turning directions for visiting various client (i.e. solving the travelling salesman conundrum, as they put it). It is likely that a similar packet will be released to do just what you're asking for: rank the agents from closest to furthest from zip-code ABCDE.

Option #5 (and the best option, probably): Stay tuned here for people that are smarter than me. They probably have better suggestions than just "waiting" Good luck!