KNOWLEDGE BASE

Calculate the Distance Between Points on a Map


Published: 09 Mar 2017
Last Modified Date: 30 Dec 2019

Question

How to calculate the distance between points on a map view.

Environment

Tableau Desktop

Answer

CLICK TO EXPAND STEPS

Step 1: Configure the Custom SQL
  1. Download the attached workbook, distance_example.twbx, and open it in Tableau. Please take note of the following information:
  2. The example data file is named distance.xlsx. It contains a worksheet called Sheet1, which lists the latitude and longitude for Seattle WA, San Mateo CA, and Bend OR.
  3. In the Data pane, right-click Sheet1 (distance.xslx) and select Edit Data Source.

    User-added image
  4. Click the down arrow on TableauSQL and select Edit Custom SQL Query.
    User-added image
    Note: When using your own data, you save a step by selecting Custom SQL when you connect to the data source. For more information, see Connect to a Custom SQL Query.
  5. The example data source has three fields: Location, Lat, and Long. Create an inner join on a second instance of the table, where the locations from the two instances are not equal.
  6. In the Edit Custom SQL dialog box, type the following query (or copy it from here and paste it), then click OK:
    SELECT 
    
      [Sheet1$].[Location] AS [Location], 
      [Sheet1$].[Long] AS [Long], 
      [Sheet1$].[Lat] AS [Lat], 
    
      [Sheet1b$].[Location] AS [Location2], 
      [Sheet1b$].[Long] AS [Long2], 
      [Sheet1b$].[Lat] AS [Lat2] 
    
    FROM [Sheet1$] INNER JOIN [Sheet1$] [Sheet1b$] 
    ON ([Sheet1$].[Location] <> [Sheet1b$].[Location])
    
    
CLICK TO EXPAND STEPS
Step 2: Create the View
  1. Select Worksheet > New Worksheet
  2. In the Measures pane, right-click Long and select Geographic Role > Longitude.
  3. Do the same for Long2
  4. In the Measures pane, right-click Lat and select Geographic Role > Latitude.
  5. Do the same for Lat2
  6. Drag Long to Columns and Lat to Rows
  7. Place Location on Detail on the Marks card. 
  8. Select Analysis > Create Calculated Field. 
  9. Name the field Distance. Enter the following formula, then click OK
        3959 * ACOS 
    		(
    		SIN(RADIANS([Lat])) * SIN(RADIANS([Lat2])) + 
    		COS(RADIANS([Lat])) * COS(RADIANS([Lat2])) * COS(RADIANS([Long2]) - RADIANS([Long])) 
    		)
    
    • Note: The above calculation uses the Great Circle distance formula to balance complexity with accuracy, and uses the average radius of the Earth. 
  10. On the Marks card, select Line
  11. Place Distance on Color
  12. Place Distance on Label
CLICK TO EXPAND STEPS
Step 3: Position the Labels 
  1. On the Marks card, on the drop-down menu for Distance, select Dimension.
    • Now, you will notice that the the labels appear in two locations, one on top of the other.
  2. Right-click the Seattle number and select Mark Label > Never Show.
  3. Do the same for the San Mateo number. Now only three distance numbers remain: one at San Mateo and two at Bend.
  4. Select the number at San Mateo and drag it to the midpoint of the line between San Mateo and Seattle.
  5. Select the larger number at Bend and drag it to the midpoint of the line between Bend and San Mateo.
  6. Select the remaining number at Bend and drag it to the midpoint of the line between Bend and Seattle.
  7. (Optional) Right-click the number, select Format, and in the Format pane, select formatting options to make the numbers more apparent on the map.

Additional Information

To view the above steps in action, see the video below.
Note: the video has no sound. To view the video in higher quality, click the YouTube icon below to watch it on YouTube directly.

Discuss this article... Feedback Forum
Did this article resolve the issue?