January 5, 2012

Tableau Tip: Calculating the distance between two points

I’m working on a project that requires me to calculate the distance between stores in order to plan resource allocation.  Pretty cool stuff that could have a huge impact if it pans out.

Naturally I want to do this in Tableau, but I since I hadn’t done this before I turned to the Tableau Forum and found this great step-by-step tutorial

TIP: For those of you that may be new to Tableau, I would highly recommend that you use the forum if you’re approaching something you’ve never done before.  You’ll often find that someone has already done something similar and it’ll save you a lot of time versus re-inventing the work yourself.

Tableau has outlined this as a 22 step process, but they go through it in extreme detail.  Note that your data source MUST have latitude and longitude available in this example.  Here’s a slimmed down version for you (some of this is taken directly from the article):

  1. Connect to your data source, select Single Table, then select Custom SQL
  2. Create an inner join on a second instance of the table where the locations from the two instances are not equal (refer to the SQL script in the detailed instructions)
  3. Click OK, then Extract the data.  For me, I’m looking at 7271 stores, so the self join will result in about 50M records.  Leverage the power of Tableau’s data extracts!
  4. Double-click your latitude and longitude fields to start building the map.  You may need to set the geographic role of the fields if you don’t have them named Latitude and Longitude.
  5. Use the Great Circle Distance formula by creating a calculated field named Distance (or the name of your choice)

    The formula is:
    3959 * ACOS
    (
    SIN(RADIANS([Lat])) * SIN(RADIANS([Lat2])) +
    COS(RADIANS([Lat])) * COS(RADIANS([Lat2])) * COS(RADIANS([Long2]) - RADIANS([Long]))
    )

    NOTE: To calculate miles, use 3959 as the first number.  For kilometers, use 6371 as the first number (thanks to Shawn Wallwork for the comment)

     
  6. On the Marks card, in the list, select Line. This will create lines between all locations on the map.  Start with only a couple locations if you have a huge dataset, otherwise it could take some time to draw all of the lines.

From this point, you can perform tons of different analysis.  One example would be to drag Distance onto the Color shelf and the Label shelf on the Marks card to color code and label the distances between each point on the map. 

Think about how you could blend other data source.  For me, I might have home zip codes for employees in another data source and I want to see all stores within a certain radius of each employee.  The possibilities are almost endless! 

You can find a sample workbook for how all of this is done here.  I know I’ll be using this technique over and over again.

5 comments:

  1. This is great, Andy: Both the tip and the reference to the Forum.

    I had done this calculation in an Excel spreadsheet where you type a zip code in one box and it then calculates the distance between that zip and every other to make a table, but never knew how to make it dynamic. A big time saver.

    ReplyDelete
  2. Andy nice job. For those needing kilometers the first number will be 6371.

    ReplyDelete
  3. Shawn, thanks for the tip re: kilometers. I've updated the blog post.

    Much appreciated!

    ReplyDelete
  4. Credit where credit is due, I got the number from Richard Leeke. It was part of his great circles_rl.twbx workbook that you can find here: http://www.tableausoftware.com/support/forum/topic/concentric-circles

    ReplyDelete