Tinkering With LOD Expressions & Distance

Calculating distances on a map just got a whole lot easier, and more flexible, with the introduction of Level Of Detail Expressions in 9.0. Here’s the distance formula Tableau recommends using to calculate the distance between two point on a map when you know the Latitude and Longitude for both points:

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

[Note: this is for miles. If you want kilometers use 6371 instead.]

This formula assumes there is a single record for each pair you want to calculate the distance for. What if you have lots of records for a single pair? Or even many records for many pairs? Then we need to aggregate the calc like this:

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

Using a MIN() aggregation gives us the single value we need to properly calculate the distance. That works fine, but now we’ve used up our aggregation. Before 9.0 we couldn’t aggregate an aggregate (without getting into the complexities of table calculation), so for instance if we want to wrap our distance calc in a MEDIAN() function to get the median distance to a single point from many points, we would get the ‘aggregate an aggregate’ error. One aspect of the new LOD Expressions I find interesting is that they allow us to aggregate an aggregate without error, and do it at the specific level we want.

Let’s move onto a more concrete example. I had three hospitals, each with hundreds of patients coming from several dozen ZIP codes. Here’s the final formula I used to do the distance calculations:

3959 * ACOS
(
SIN(RADIANS([LOD Lat Patient])) * SIN(RADIANS([LOD Lat Hospital])) +
COS(RADIANS([LOD Lat Patient])) * COS(RADIANS([LOD Lat Hospital])) *
COS(RADIANS([LOD Lon Hospital]) – RADIANS([LOD Lon Patient]))
)

So that’s where we are headed. But before we can write this calc we need to write the four other calcs, like this:

LOD Lat Patient:     {FIXED   [Patient Zip] : MIN( [Latitude Patient]) }

LOD Lon Patient:    {FIXED   [Patient Zip] : MIN( [Longitude Patient]) }

LOD Lat Hospital:    {FIXED [Hospital City] : MIN( [Latitude Hospital]) }

LOD Lon Hospital:    {FIXED [Hospital City] : MIN( [Longitude Hospital]) }

Fairly simple, right? So what’s going on? First note that I’m using FIXED LODs. This means the expression will be calculated using only the dimension in the list, in this case either Patient Zip or Hospital City. Since the expressions are all MIN() we know that each expression will return a single value for each Patient Zip and Hospital City respectively.

To see this in action go to Healthcorp on Public. If you download and open the workbook, you’ll see how I was able to show the distance from each ZIP to each area hospital, as well as then calculate the average and median distance travelled to each hospital by using an aggregation of an aggregation.

I’m really lovin’ these LOD Expressions; they seem to work much closer to the way my mind works, certainly more so than table calculations.