Dashboard week day 4 kicked off in style, with a spatial and API challenge coming our way. This was going to be an interesting one for me, because I hadn’t worked with API’s since our training with Brisbane Head Coach, Shane. We were required to answer a question of our liking using data from the City of Melbourne’s API. I decided to try and answer the question “which pub should my mates and I go to before the footy this weekend?” Here are the steps I took to get my data from a table on the City of Melbourne’s website, to an interactive dashboard that you can use to find pubs close to Marvel Stadium.

 

Step 1: Find the data

As I mentioned before, we were required to get our data from the City of Melbourne’s API. We had to do this using SQL, which meant I needed a little revision on SQL before jumping into it. With my data I wanted to look at the pubs in the CBD and see which ones were closest to Marvel Stadium. I also didn’t want to go to a pub that was going to be dead, but also too packed, so I only extracted the pubs that had a venue capacity of more than 200, but less than 1000. My SQL query looked like this:

SELECT *

FROM cafes-and-restaurants

WHERE industry_(anzsic4)_description = “pubs, taverns and bars” AND number_of_seats >= 200 AND number_of_seats <= 1000

In plain language, this SQL query is saying give me all of the columns from the cafes and restaurants dataset, but only the rows that are pubs, taverns, and bars, and that also have between 200 and 1000 patrons. This allowed me to only focus on the data that I wanted, instead of filtering it all out in Alteryx.

 

Step 2: Extract the data in Alteryx

As the above SQL query from the City of Melbourne API only gives you a link, you must then use Alteryx to actually extract the data. This is done in several parts. You must first use a text input tool to input the URL. Secondly, use a download tool to extract all of the text from the given URL. Thirdly, bring in a JSON Parse tool to get the data you need. From there, the data just needed a bit of cleaning to create the centroids for Tableau. You can view a photo of my Alteryx workflow below

 

Step 3: Create the map in Tableau

People are always fascinated with maps in Tableau, so making these kinds of dashboards is fun. This map required 3 layers. The first layer being all the centroids from the pubs, second being Marvel Stadium’s centroid and the third being a buffer that I create in Tableau. I used the average walking speed of humans to determine how far someone could walk in 5, 10, 15 and 20 minutes. This would determine the radius of the size of the radius of the buffer. I would then use this to filter out the pubs that weren’t in this radius.

 

Step 4: Create the dashboard

The dashboard pretty much created itself. I just used the map that I created, then added a title, the logo of each of the teams playing and finally the filters that allows users to interact with the dashboard. After some careful analysis, I decided Hightail, which is 562m and a 6 minute walk from Marvel was where we should go before the game. You can view and interact with the dashboard at the link below

https://public.tableau.com/app/profile/jack.moroney/viz/WellTheresaBigBigSound/Dashboard1

The Data School
Author: The Data School