• Mapping Distances – An Alternate Approach

    The Issue

    A common technique for calculating distances in Tableau is to use a custom SQL statement to join a dataset to itself. This causes each data row to contain two latitude and longitude values. Then a row-level calculation can use a formula (such as Haversine) to determine the distance. However, many data source connections such as Salesforce and Google Analytics don’t support custom SQL. Furthermore, cross joining large datasets can be quite costly and, even if possible, may not be practical.

    Here is an alternative: use a table calculation to calculate the distance using latitude and longitude from different records!

     

    The Alternate Solution

    The basic approach is simple and is made up of two parts:

    1. Create a row level calculated field to set the order in which the geographic points should be evaluated. In the example dashboards above, the origin is set to 1 and all other points are set to 2.
    2. Use the LOOKUP function to determine latitude and longitude for the origin to compute the distance to every other location.

    The first part is relatively easy. In the example dashboards above, a parameter is used to allow the user to select the origin (city or customer). For each record, if the field value matches the parameter value, then the result is 1. Otherwise it is 2. This calculation will serve as the ordering of the table calculation in the second part.

    The second part is a little trickier. The initial row-level distance calculation, assuming that the latitude and longitude of both points are contained in the same record, looks like this:

    3959 * ACOS
    (
    SIN(RADIANS(Lat)) * SIN(RADIANS(Lat2)) +
    COS(RADIANS(Lat)) * COS(RADIANS(Lat2)) *
    COS(RADIANS(Lon2) – RADIANS(Long))
    )

    Using a couple of table calculations, the calculation becomes:

    3959 * 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())))
    )

     

    You’ll notice that every reference to Lat from the original formula was replaced with LOOKUP(AVG(Lat), First()). The same is true of the original Lon.  The lookup of the first values is the origin’s latitude and longitude. The other latitude and longitude references are for the destination. Since this is a table calculation all references are now aggregates: e.g. AVG(Lat).

    The last step to a working solution, is to make sure the table calculation is addressed and ordered correctly.

    • Right click the field in the view and select “Edit Table Calculation…”
    • Select the “Advanced…” option
    • Make sure to Address by the fields that determine an individual geographic location (e.g. City and State, or Customer)
    • Sort the calculation using the first calculated field that set 1 for the origin and 2 for everything else.
    • clip_image002
    clip_image004

    That’s the basic approach.  Download the workbook above and take a look to see the details!

20 Responsesso far.

  1. Jorge says:

    Hi guys. This is great. Couple questions:

    How do I set the origin city? I have been able to add the distance formula and the look up formula, but I cant get my map to understand which one is the origin and which one is the destination. Perhaps a little further explanation on how to set up these two would be helpful. Also, I’d like to add the star icon when a origin city is selected to make it easier to understand.

    Look forward to your comments.

    Jorge

    • Jorge the Origin city is a calculation that compares the value selected in the parameter with the name of the city. The calculation gives the origin city alone a value of 1 and all other cities the value of 2. That allows for the distance calculation to use the LOOKUP table calculation to find the latitude and longitude of the origin city in comparison with every other city.

      Feel free to download the workbook and take a look at the internal workings!

      Regards,
      Joshua

  2. Jorge says:

    Thanks Josh, unfortunately my free trial expired and my companys executives wont let me pay for the service until I show them a finished product. So I was able to get everything you specify here, but I am missing a small detail somwhere. I got the parameter drop down, and I got Tableau to identify the 1 and the 2 between the origin and the destination. However, on the distance formula, the latitude and longitude for the origin dont seem to be working, thus giving me a distance of 0.0 miles. I am sure if I opened your workbook and looked at it I could find within a minute, but unfortunately that is not an alternative for now.

    Below is a link to the workbook I am working on.

    http://public.tableausoftware.com/views/FUELSNewsRackPricingMap/RackPrices?:embed=y&:display_count=no

    Thanks,

    Jorge

  3. Candy says:

    Hi Joshua,

    I have read your distance calculation in Tableau. But I have some problems,
    1. How can I get the blue and the orange label besides the data (the excel file name)

    • Candy,

      Do you mean the data blending? Blue indicates a primary data source and orange indicates a secondary. When you start a new view, whatever data source you use to add the first field will be the primary data source. All others will be secondary.

  4. Vishant says:

    The Distance Range is getting calculated for a fixed origin.
    Is this because in the “Distance from Origin” the “At Level” and “Restarting every” are not highlighted and I am not able to update as mentioned in the above screen shot.
    Please advice.

    Thanks,
    Vishant

  5. Vishant Nayak says:

    This works Great!!…Thanks Joshua Milligan.
    How can I apply the “Distance from Origin filter” to other sheets.
    Thanks,
    Vishant

  6. Sam says:

    What does the 3959 refer to in the second step of your process? Is this something unique to your data?

  7. Sam says:

    At the moment, though my formulas are the same as yours, I can’t get one location to identify as the origin, ie irrespective of which parameter you have selected, if you were to upload the data, no row would have the value of 1 under ‘Origin Order’

  8. Chris says:

    Great map! I have a question about using road distances instead of inserting the lat/long based calculated field.

    I have a set of cities in a distance matrix with the road distance/travel time between each city pair (thanks Google Maps API). How can I import and reference this data instead of the calculated distance field?

  9. Tom says:

    Hi,

    I can’t seem to get my head around this, can the workbook be downloaded to show your workings?

    Cheers!

  10. Robert Hansen says:

    Hello,

    I am trying to figure out how i would plot auto dealers across the country and customers that belong to said dealership (with the distance). I want to have a STAR where the dealers Long/Lat is and still plot all their customers Long/Lat.

    So in short i have customer_long and customer_lat and on the same record i also have a dealer_long and dealer_lat.

    Any suggestions on how i would accomplish what you have above with this dataset?

    • benmelek_admin says:

      Robert,

      Right now, there isn’t an easy way to do this. If you can union together the existing data with a copy of itself (Custom SQL or Tableau’s built in union) so you have both sets of Lat/Long in only 2 fields (with a 3rd field indicating whether the row is for a customer or a dealer, then it becomes a lot easier. Tableau did preview an upcoming feature that would allow you to plot multiple geographies on a single map, but that’s not out yet (not even in 10.2 beta).

      Hope that helps!

  11. Ashish says:

    Hi Josh,

    Thanks for the wonderful post which helped in creating something similar. But it would be great if you could suggest something like this:
    Once I select the Range filter to filter out the cities say from 0 to 100 miles from the origin, then I want to see the total Sales of all displayed cities only.

    Now the issue where I am stuck is that because the Distance calculation is a Table Calculation, it does not filter out the Cities rather just hide the cities outside the Range. So, how to define a sum or grand total calculation which shows only the value for the displayed cities?

    Hope you to see some guidance from you!

    Thanks,
    Ashish

    • benmelek_admin says:

      Ashish,

      Thanks for the comment! You are right that a table calc filter won’t work in this case, but you could use another calculation to get only the values you wanted. For example, let’s say the existing calculation is named [In Radius?]. You could write another calculation with logic like:

      WINDOW_SUM(
      IF [In Radius?]
      THEN SUM([Value])
      END)

      Hope that helps!

Leave a Reply

Your email address will not be published. Required fields are marked *