Using the Environment Agency’s bathing water quality data provided by DEFRA and UK Location, I’ve whipped up a very quick visualisation of my own – showing off indicators for faecal bacteria at the bathing sites.

The data source:
http://environment.data.gov.uk/doc/bathing-water.html?_view=basic&_properties=samplingPoint.lat,samplingPoint.long,latestSampleAssessment.faecalColiformCount,latestSampleAssessment.faecalStreptococciCount,latestSampleAssessment.totalColiformCount&_page=0&_sort=-latestSampleAssessment.faecalStreptococciCount

Notable parameters I used:

• view = “basic” view
• properties = lat, long, total coliform count, faecal coliform count, faecal streptococci count
• page size = 500
• sort = by faecal streptococci count (descending)

The steps to create the visualisation:

• Realised I was quite interested in seeing where the bathing sites were that contained the most faecal bacteria (Blackpool area by the looks of it)
• Tailored my own API call using instructions from the API documentation.
• Tried using Yahoo Pipes and other connecting API thingys to see what I could do within about 5-10 minutes.
• Decided to use Googles Fusion Tables as it can create maps from spreadsheet data
• The Enviroment API offers several formats, so I just changed my API call to include “.csv” intsead of “.html”.
• Then, after spending the last half year developing the LinkedGov extension for Google Refine – I immediately thought of it as the first go-to tool to shape the data and make it fit for importing into some sort of mapping API.
• I used Google Refine’s faceting & number range features to decide how to split the bacteria counts into low, medium and high.
• I exported the data from Refine as CSV to my computer.
• I uploaded the CSV into Fusion Tables – and all the hard work was done for me!
• Time taken = 15 minutes (I have experience with the Linked Data API – otherwise it would have taken me a little while longer to tailor the API call I wanted)

Please note: I have manually adjusted the banding points for the levels of bacteria so the visualisation showed a visually pleasing number of red, yellow and green markers. While a site may have a red marker – it could actually be of quite high water quality.

Total Coliform Count

Low-temperature electron micrograph of a cluster of E. coli bacteria, magnified 10,000 times. Each individual bacterium is oblong shaped. (Photo credit: Wikipedia)

Faecal Coliform Count

Faecal Streptococci Count

Gram-stained smear of streptococci (Photo credit: Wikipedia)

I’m not sure how long I’ve had this piece of kit, but damn it’s been a nightmare. It’s literally a matter of luck whether it works and I’ve always spent just long enough for me to hit a lucky setting or switch that seems to touch it’s sweet spot.

I’m documenting what I’ve done incase anyone else out there is Googling away while pulling their hair out.

Continue Reading →