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

    Calculate the geographical distance between two points on a map

    David Heinz

      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?

       

      Thanks a lot in advance!

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

          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:

           

          ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371

           

          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
            David Heinz

            Hello Michael,

             

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

             

            Thanks for giving advices!

             

            Best, David

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

              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

              ACOS(COS(RADIANS(90-[Focal Lat])) *COS(RADIANS(90-[Latitude])) +SIN(RADIANS(90-[Focal Lat])) *SIN(RADIANS(90-[Latitude])) *COS(RADIANS([Focal Long]-[Longitude]))) *6371

               

              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?

              Lat Long Outliers.jpg

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

                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
                  Michael Hesser

                  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
                    Michael Hesser

                    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
                      Siddharth Patil

                      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
                        Michael Hesser

                        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
                          Steve Varlas

                          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
                            Michael Hesser

                            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!