Calculating Distance

At times, you may need to calculate the distance between points on a map view. This calculation requires that the data include the latitude and longitude coordinates. You also need a self join in the data connection and a calculated field. See the attached workbook, distance_example.twbx.

For this calculation, the latitude and longitude coordinates must be available directly in your data or through a join. The simplest scenario has just one original data table and a self join.

Step 1

Open a new workbook in Tableau Desktop and connect to your data file. In the example, the data file is distance.xls, which lists the latitude and longitude for Seattle WA, San Mateo CA, and Bend OR.

Step 2

In the Excel Workbook Connection dialog box, select Single Table, and then select Sheet1.

Step 3

Select Custom SQL.

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.

Step 4

Modify the default SQL statement to:
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 5

When finished, click OK.

Step 6

In the Data Connection dialog box, select the way you want to connect to your data.

Tableau automatically assigns the Lat and Lat2 fields their geographic role, and they appear in the Measures pane with a globe icon. You assign the geographic role to Long and Long2 manually.

Step 7

In the Measures pane, right-click Long and select Geographic Role > Longitude.

Step 8

Perform Step 7 again for Long2.

Step 9

From the Measures pane, drag Long onto the Columns shelf, and Lat onto the Rows shelf.

A map appears, but the scale is too large to all three locations.

Step 10

From the Dimensions pane, drag Location onto the Level of Detail shelf.

The map scale changes to show all three locations.

Step 11

This example calculated field uses the Great Circle Distance formula that balances complexity with accuracy.
The average radius of the Earth is used.

The formula is:

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

Complete the following steps to create this calculated field:

1. Select Analysis > Create Calculated Field.
2. In the Name text box, type Distance.
3. Press the Tab key to move the cursor to the Formula text box.
4. Type 3959 *.
5. Space, and then in the Functions list, double-click ACOS.
6. Double-click SIN, then RADIANS.
7. In the Fields list, double-click Lat.
8. Move the cursor past two of the three closing parentheses, space, type an asterisk, and space again.
9. In the Functions list, double-click SIN, then RADIANS.
10. In the Fields list, double-click Lat2.
11. Move the cursor past two of the three closing parentheses, space, type a plus sign, and space again.
12. In the Functions list, double-click COS, then RADIANS.
13. In the Fields list, double-click Lat.
14. Move the cursor past two of the three closing parentheses, space, type an asterisk, and space again.
15. In the Functions list, double-click COS, then RADIANS.
16. In the Fields list, double-click Lat2.
17. In the Functions list, double-click COS, then RADIANS.
18. In the Fields list, double-click Long2.
19. Move the cursor past ONE closing parenthesis, space, type a minus sign, and space again.
20. In the Functions list, double-click RADIANS.
21. In the Fields list, double-click Long.

Step 12

When finished, click OK.

Step 13

On the Marks card, in the list, select Line.

Lines appear on the map between the locations.

Step 14

From the Measures pane, drag Distance onto the Color shelf and the Label shelf on the Marks card.

The lines between the three points on the map are now color coded and the distances between the points appear at each point on the map.

Step 15

On the Color shelf, right-click Distance and select Dimension.

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

Step 16

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

Now the two farthest locations show only one distance number.

Step 17

Right-click the remaining Seattle number and select Mark Label > Never Show.

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

Step 18

Select the number at San Mateo and drag it to the midpoint of the line between San Mateo and Seattle.

Step 19

Select the larger number at Bend and drag it to the midpoint of the line between Bend and San Mateo.

Step 20

Select the remaining number at Bend and drag it to the midpoint of the line between Bend and Seattle.

Step 21

Right-click the number and select Format.

Step 22

In the Format pane, select formatting options to make the numbers show up better on the map (bold, larger font size, etc.)

The formatting selections modify all three distance numbers.