Tuesday, February 4, 2014

Fun with Custom Geocoding and Hawaii Frustration

Well folks...at long last it’s time to say goodbye to this ridiculous system that we called the Bowl Championship Series (BCS). For 16 years we have been almost haphazardly crowning a team that may or may not be the best team in college football as National Champion. 16 years, 16 champs and a whole lot of angry football fans (none more so than Auburn in 2004) the least I could do was put together a viz. Let’s have some fun with geocoding while we’re at it.

The most challenging part of this viz, was BY FAR, getting my data set up. Not only did I have to pull together results from the 72 BCS bowls, but I also had to manually look up the longitude/latitude for the 51 participants. Thanks to our good friends at Google, I was able to quickly find the coordinates for each of the campuses.

For a larger data set you may want to use a geocoding API. If you go the manual route, keep in mind that Tableau is going to want a “-“ sign in front of western and southern coordinates. Once, I had my coordinates together I saved them in a csv file with the structure shown below.

Getting my custom geocoding in to Tableau was as simple as navigating to Map -> Geocoding -> Import Custom Geocoding. Once the import completed I just right clicked on my “Team” dimension and changed the “Geographic Role” to the new “Teams” geographic role that I just imported.

Now by double-clicking teams, I am able to map the location of each team’s campus. I switched out the blue dots for custom shapes, based the size on Appearances / Wins / Losses, and the first layer of my map was ready to go. To color each state by conference, I simply created a dual axis map and used “Conference” as color and put "State" in the detail shelf. Tableau would not have known how to map Conference without providing State, a known geographic role. I also put "Team" on the detail shelf so that clicking on a state will pass a team value to my details worksheet (bottom left in the final viz). This way if a user misses a logo and accidentally clicks a state a value will still be passed.

Don’t panic, Hawaii fans, I didn’t forget about you. While I was terribly inconvenienced by your one BCS bowl appearance, I wasn’t going to let your distance from the contiguous states rob you of the glory that Colt Brennan and your 2008 Sugar Bowl appearance so rightly deserve. I thought about building a separate worksheet for Hawaii, but this would give me a 2nd "About Tableau Maps" footer. To work around this, I simply took a screenshot of the Tableau map and cropped Hawaii. I made sure to take my screenshot on the map after finalizing the size of the map and zoom on the final dashboard. This allowed me to make sure I had the appropriate scale. I saved my screenshot and went to the “Details” tab of the “Properties” screen for my image to find out that it was 162 x 86 pixels. I then set my screenshot up as a Background image and used two calculated fields as the X and Y axis. I set the right most value as 162 and the top value of 86. When I added the worksheet to my dashboard, I also made sure it had a width of 162 and height of 86.

The formula for my calculated fields, Longitude (Hawaii) and Latitude (Hawaii) were simply ‘118’ and ‘41’ (no quotes). You can initially make these 162 and 86 for the purposes of setting up your background image. After getting the background image showing, I determined the final values by annotating the point on the map where I wanted the logo to appear. You will also want to fix your axes to 0-162 and 0-86 and then turn off "Show Headers". You can get more detail on this trick from the "Background Images" training video available on the Tableau website.

And that was it...I added some filters, dashboard actions and a timeline at the bottom to show results by each conference. See below for the finished product. If you have any questions or suggestions for the dashboard don't hesitate to DM me @mondaymviz or leave a question in the comments below. Happy vizzing everybody!