KNOWLEDGE BASE

Calculate the Distance Between Points on a Map

Published: 09 Mar 2017
Last Modified Date: 14 Mar 2023

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.
Did this article resolve the issue?    
PRINT THIS PAGE
Trending Articles
Attachments
distance_example.twbx

All Support

SUPPORT BY PRODUCT
TOOLS & DOWNLOADS
INFORMATION
ASSISTANCE