KNOWLEDGE BASE

Calculate the Distance Between Points on a Map


Published: 09 Mar 2017
Last Modified Date: 23 Oct 2017

Question

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

Environment

Tableau Desktop

Answer

Option 1: Tableau Desktop 10.0 and newer versions

Beginning with Tableau Desktop 10.0, you can use the radial tool to measure distance in a map view. For more information, see Measure Distances Between Data Points and Locations in a Map.

Option 2: Use Latitude, Longitude and Custom SQL

Step 1: Configure the Custom SQL

  1. Download the attached workbook, distance_example.twbx, and open it in Tableau.
  2. Click OK on the Import Data Source alert and Yes on the Custom SQL alert.
  3. 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.
  4. In the Data pane, right-click Sheet1 (distance.xslx) and select Edit Data Source.
    User-added image
  5. 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.
  6. 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.
    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])

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. Drag Long to Columns and Lat to Rows
  5. Place Location on Detail on the Marks card. 
  6. Select Analysis > Create Calculated Field. 
    The below calculation uses the Great Circle distance formula to balance complexity with accuracy, and uses the average radius of the Earth. 
  7. 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])) 
    )
  8. On the Marks card, select Line. 
  9. Place Distance on Color
  10. Place Distance on Label. 

Step 3: Position the Labels 

  1. On the Marks card, on the drop-down menu for Distance, select Dimension.

    Now the labels appear in two locations, one on top of the other.

  2. Right-click the Seattle number and select Mark Label > Never Show. Do the same for the San Mateo number.

    Now only three distance numbers remain: one at San Mateo and two at Bend.

  3. Select the number at San Mateo and drag it to the midpoint of the line between San Mateo and Seattle.
  4. Select the larger number at Bend and drag it to the midpoint of the line between Bend and San Mateo.
  5. Select the remaining number at Bend and drag it to the midpoint of the line between Bend and Seattle.
  6. 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?