Datasette ClusterMap Plugin – Querying UK Food Standards Agency (FSA) Food Hygiene Ratings Open Data

Earlier today, Simon Willison announced a new feature of in the datasette landscape: plugins. I’ve not had a chance to explore how to go about creating them, but I did have a quick play with the first of them – datasette-cluster-map.

This looks through the columns of a query results table and displays each row that contains latitude an longitude columns as a marker on an interactive leaflet map. Nearby markers are clustered toegther.

So I thought I’d have a play… Assuming you have python installed, on the command line tun the following (omit the comments). The data I’m going to use fo the demo is UK Food Standards Agency (FSA) food hygiene ratings opendata.

# Install the base datasette package
pip install datasette

# Install the cluster map plugin
pip install datasette-cluster-map 

# Get some data - I'm going to grab some Food Standards Agency ratings data
# Install ouseful-datasupply/food_gov_uk downloader
pip install https://github.com/ouseful-datasupply/food_gov_uk.git
# Grab the data into default sqlite db - fsa_ratings_all.db
oi_fsa collect

# Launch datasette
datasette serve fsa_ratings_all.db

This gives me a local URL to view the datasette UI on in a browser (but it didn’t open the page automatically into the browser?). For example: http://127.0.0.1:8001

By default we see a listing of all the tables in the database:

I want the ratings_table:

We can use selectors to query into the table, but I want to write some custom queries and see the maps:

So – what can we look for? How about a peek around Newport on the Isle of Wight, which has an ish postcode of PO30

Looking closely, there doesn’t seem to be any food rated establishments around the Isle of Wight Prisons?

This is a bit odd, because I’ve previously used FSA data as a proxy for trying to find prison locations. Here’s a crude example of that:

So… all good fun, and no coding really required, just glueing stuff together.

Here are some more example queries:

# Search around a postcode district
SELECT BusinessName, BusinessType, RatingValue, AddressLine1, AddressLine2 , AddressLine3, Latitude, Longitude FROM ratingstable WHERE postcode LIKE 'PO30%'


# Crude search for prisons
SELECT BusinessName, BusinessType, RatingValue, AddressLine1, AddressLine2 , AddressLine3, Latitude, Longitude FROM ratingstable WHERE (AddressLine1 LIKE '%prison%' COLLATE NOCASE OR BusinessName LIKE '%prison%' COLLATE NOCASE OR AddressLine1 LIKE '%HMP%' COLLATE NOCASE OR BusinessName LIKE '%HMP%' COLLATE NOCASE)

# Can we find Amazon distribuion centres?
SELECT BusinessName, BusinessType, RatingValue, AddressLine1, AddressLine2 , AddressLine3, Latitude, Longitude FROM ratingstable WHERE AddressLine1 LIKE 'Amazon%' OR BusinessName LIKE 'Amazon%'

# How about distribution centres in general?
SELECT BusinessName, BusinessType, RatingValue, AddressLine1, AddressLine2 , AddressLine3, Latitude, Longitude FROM ratingstable WHERE AddressLine1 like '%distribution%' COLLATE NOCASE  or BusinessName LIKE '%distribution%' COLLATE NOCASE

# Another handy tick is to get your eye in to queryable things by looking at where
#  contract catering businesses ply their trade. For example, Compass
SELECT BusinessName, BusinessType, RatingValue, AddressLine1, AddressLine2 , AddressLine3, Latitude, Longitude FROM ratingstable WHERE AddressLine1 LIKE '%compass%' COLLATE NOCASE  OR BusinessName LIKE '%compass%' COLLATE NOCASE

One thing I could do is share the database via an online UI. datasette makes this easy – datasette publish heroku fsa_ratings_all.db` would do the trick if you have Heroku credentials handy, but I’ve already trashed my monthly Heroku credits…