2015 CFP Championship: Calculating Recruiting Distances in Tableau

Data

2015 CFP Championship: Calculating Recruiting Distances in Tableau

by Nick Joslyn

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:  

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.    

More About the Author

Nick Joslyn

Analytics Consultant
2015 CFP Championship: Calculating Recruiting Distances in Tableau A couple weeks ago, I came across a College Football Playoff blog post that mapped the distance of each team’s campus to each ...
What’s New in Tableau 8.2 – Visual Data Window With Tableau 8.2 now available, the InterWorks team wants to give you an in-depth look at some of our favorite new features. Our What’s ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072