Last Modified Date: 16 Aug 2016
Article Note: This article is no longer actively maintained by Tableau. We continue to make it available because the information is still valuable, but some steps may vary due to product changes.
At times, you may need to calculate the distance between points on a map view. To perform such a calculation, the latitude and longitude coordinates must be available directly in your data or through a join.
The attached workbook, distance_example.twbx, contains a simple example of this, using a single data table and a self-join. The steps below show the custom SQL and calculated field used to create this example. They include additional tips for how you would perform the steps if you were creating the example from scratch in a new workbook.
Configure the custom SQL
Download the attached workbook, distance_example.twbx, and open it in Tableau.
Click OK on the Import Data Source alert and Yes on the Custom SQL alert.
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.
In the Data window, right-click Sheet1 (distance.xlsx) and select Edit Connection.
In the Excel Workbook Connection dialog box, for Step 2, where Custom SQL is selected, click the browse button () that appears to the right of it (not the Browse button that appears above it).
This opens the Edit Custom SQL dialog box, where you can make changes.
Note: If you were creating this example from scratch, you would save a step by selecting Custom SQL when you connect to the data source. For more information, see Connecting to a Custom SQL Query in the Tableau Desktop Help.
The example has three fields: Location, Lat, and Long. For the self-join, you 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):
[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])
Create the view
In this section you set up a view using the custom SQL connection you created in the previous section.
Note: The remaining steps in this article are presented as if you are creating this example from scratch.
Click OK on the Edit Custom SQL and Excel Workbook Connection dialog boxes.
In the Measures pane, a globe icon appears next to the data fields.
In the Measures pane, right-click Long and select Geographic Role > Longitude.
Do the same for Long2.
From the Measures pane, drag Long onto the Columns shelf, and Lat onto the Rows shelf.
A map appears, but the scale does not show the three locations.
From the Dimensions pane, drag Location onto Detail on the Marks card.
The map scale changes to show all three locations.
Select Analysis > Create Calculated Field.
This calculation uses the Great Circle Distance formula that balances complexity with accuracy. The average radius of the Earth is used.
In the Calculated Field dialog box, complete the following steps.
- For Name, type Distance.
- In the Formula box, build the following formula:
3959 * ACOS
SIN(RADIANS([Lat])) * SIN(RADIANS([Lat2])) +
COS(RADIANS([Lat])) * COS(RADIANS([Lat2])) * COS(RADIANS([Long2]) - RADIANS([Long]))
- Confirm that the status message indicates that the formula is valid, and then click OK.
On the Marks card drop-down list of views, select Line.
From the Measures pane, drag Distance onto Color, and then drag another instance of Distance onto Label.
The lines between points on the map are now color coded, and the distances appear at each point.
Complete the following steps to position the labels.
- 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.
- 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.
- Select the number at San Mateo and drag it to the midpoint of the line between San Mateo and Seattle.
- Select the larger number at Bend and drag it to the midpoint of the line between Bend and San Mateo.
- Select the remaining number at Bend and drag it to the midpoint of the line between Bend and Seattle.
- Right-click the number, select Format, and in the Format pane, select formatting options to make the numbers more apparent on the map (bold text, larger font size, and so on).
Alternate Search Terms:How To Calculations Maps