A couple weeks ago, I came across a College Football Playoff blog post that mapped the distance of each team’s campus to each player’s hometown. While detailed and visually appealing, you could not interact with the map – making it impossible to do more detailed analysis. This inspired me to create a Tableau dashboard that would present the same information but give the user the ability to filter the visualization to show what is important to them.

First, check out the visualization. After that, you can see how it’s made. Here you go:

Distance Calculation

The purpose of the visualization is to show which team has logged the most miles in recruiting. To show this, I needed some way to calculate the distance between two cities. I racked my brain and recalled a project I had worked on that used a formula to calculate distance between cities. I found the formula and knew it would work if I set up the data set correctly.

Here’s the calculation:

3959 * ACOS

(

   SIN(RADIANS([Lat])) * SIN(RADIANS([Lat2])) +

   COS(RADIANS([Lat])) * COS(RADIANS([Lat2])) *

   COS(RADIANS([Long2]) – RADIANS([Long]))

)

The Lat and Long fields represent the latitude and longitude for the hometown of the player. Lat2 and Long2 represent the latitude and longitude for the school the player attends. So, each Oregon player would have the same Lat2 and Long2. 

One caveat about this distance calculation is that it’s just the straight-line distance between two cities, not the driving distance.  

About the Viz

To build the visualization, I needed to create a data set. My first step was to create a data set with the players from each team. I went to the following websites to achieve this:  

• Oregon
• Ohio State

For the distance calculation to work, I needed to get the latitude and longitude of all the players hometowns. I did a Google search for a data set that contained the city, state, latitude and longitude for US cities. I came across a good list here.

After downloading it, I joined it to my players data set on City. There were a of couple cities not in the data set, so I got their latitude and longitude from latlong.net. Next, I needed to create a path_id field so I could use the Line mark type on the map in Tableau. You could have done this manually but, I decided to download the data set as a CSV and used JavaScript to create the path_id for each row. The last thing I did to the data set was split out the city and state from the Hometown field. I used the MID() and FIND() functions to achieve this.  

NOTE: Ohio State does have one player from Australia. I purposely left him out of the data set for two reasons:

1. He skews the data
2. He didn’t come out of a high school; he came from the Melbourne, Australia, based punter/kicker training ground, Prokick Australia.  

Thanks for stopping by. I hope you enjoyed interacting with my dashboard and found some interesting info, such as Ohio State having 64 more recruits from Ohio or Oregon having 848 more total miles than Ohio State.  

If you have any questions or comments, don’t hesitate to use the comments section below.