Using data to avoid food poisoning
How we’re looking after the participants at GovHack Whangarei
Free food is just one of the reasons to head along to your nearest #GovHackNZ event next month. Although it may be free, the saying “you get what you pay for” definitely doesn’t apply! At GovHack Whangarei we are going out of our way to source only the best food that the district has to offer, and we are using open data to do it.
Going on a data hunt
District councils are responsible for inspecting premises that prepare and sell food to the public. In the Whangarei district the council has passed a by-law that requires operators to display their food grade certificate on the premises. If you want to look up the grade before you arrive at the location, you can look up the food outlet using the council’s Food Grading Database Search.
It’s great that the council publishes this data on their site, but I wanted to have a play around with the data and put it into a map. There is no link that enables you to download the database, but the search function does enable the results to be returned based on grade or suburb. One way of pulling the data from the website is to select and copy the search results and put them into a spreadsheet for exporting as a csv file. The other option is one that I used to gather the same data set a year or so back before it was surfaced on the council’s website.
At an open data event put on by NetHui a couple of years back I found out about a super handy tool call Tabula, which extracts data from tables within pdf documents. I used this tool to select the data that was presented in the monthly reports to Council’s Planning Committee. After loading a pdf file into Tabula, you select all the tables you want to extract from within the document.
Once selected, Tabula gives you the option of copying the data tables to your clipboard, or saving them as a csv file. I used the latter option because I needed a csv file for geolocating the data.
Geolocating the data
If you want to make a basic web map of the data you can use Google’s My Maps. After creating a new map, you can open the csv file in a new map layer. That application will drop a pin on the addresses contained in the csv data table, and highlight any that it can’t find. For those, you can manually update the address data or drag the pins to the correct locations.
I wanted to do a bit more and display the data using CartoDB , so I used the geocoding plugin in QGIS to geolocate the address data. You can do this by importing the csv file into QGIS, running the tool, and then creating new latitude and longitude fields in the attribute table to hold the location data. From there, you can zip and upload the shapefile into CartoDB, allowing you to display the data in a web map.
See my map of food outlets in Whangarei coloured by grade
I had my own copy of this database from over a year ago, so the information I have also shows the grading certificates from previous inspections. That enables me to also see whether the grade is staying constant, increasing, or whether standards are slipping. That’s a metric that the live database doesn’t show, but I think it is important as an indication of behaviour. I’d prefer to eat food from an establishment that is getting better over time rather than worse!
Happy eating!
At GovHack Whangarei you can rest assured that we have gone the extra mile to make sure the food is great. It’s just one more reason for you to sign up for a weekend of awesome!
Want to have a play around with the data for yourself? I’ve published it here:
