3 Replies Latest reply on Mar 31, 2016 1:26 AM by Bas Groothedde

    Compute Distance based on Lon/Lat

    Bas Groothedde

      Hi everyone,


      I am trying to compute distances based on latitudes and longitudes. I have created some paths

      and now I would like to add the distance as a tooltip for every path (not only between starting point

      and customer but also between every node on the path). If it is possible I do no want to

      rearrange the data, but keep it as it is with Path Order (1,2) as the starting and ending points.

      I have found the following: Calculating Distance | Tableau Software , but as I said the objective is

      not to rearrange any data. (Btw. I also do not understand why a SQL is needed in the example,

      because the results are obtained with out it as well).

       

      I am looking forward to your responses!

       

      PS: Attached to the Question you will find the Excel Sheet used)

        • 1. Re: Compute Distance based on Lon/Lat
          Adam Crahen

          Hi Bas-

           

          Have a look at this and see if it helps get you closer.

           

          I mapped out your paths and facility locations on a dual axis.  I used path order on size so you could see the direction of travel.  I didn't use the custom sql for the distance calc.

           

          Distance

          IF ISnull(ACOS((SIN(RADIANS(LOOKUP(AVG([Lat]), First())))

          * SIN(RADIANS(AVG([Lat]))) + COS(RADIANS(LOOKUP(AVG([Lat]), First()))) * COS(RADIANS(AVG([Lat]))) *

          COS(RADIANS(AVG([Lon])) - RADIANS(LOOKUP(AVG([Lon]), First()))) ))*[Units])

          THEN 0

          ELSE

          ACOS((SIN(RADIANS(LOOKUP(AVG([Lat]), First())))

          * SIN(RADIANS(AVG([Lat]))) + COS(RADIANS(LOOKUP(AVG([Lat]), First()))) * COS(RADIANS(AVG([Lat]))) *

          COS(RADIANS(AVG([Lon])) - RADIANS(LOOKUP(AVG([Lon]), First()))) ))*[Units]

          END

           

          You can select the units via a parameter.  i figure you are after km.

           

          Check the compute on the distance calc:

           

          A couple notes:

          1) When you hover over point order 1, it will say zero for the distance because it is calculating the distance from itself.

          2) This is not the driving distance which can vary greatly from the direct path.

           

          I didn't know what version you have, so I did this in 9.0.

          2 of 2 people found this helpful
          • 2. Re: Compute Distance based on Lon/Lat
            Bas Groothedde

            Adam, this is great!

            Thanks for taking the time answering my question!

            • 3. Re: Compute Distance based on Lon/Lat
              Bas Groothedde

              Hey Adam,

               

              After you have helped me impressively, I wonder whether you could help me with the following problem as well. It deals with flows in paths:
              Mapping-Adding Flow Details in Path

               

              Kind regards!