Dan Paul Smith

Interface and visualisation developer.

Monday, 23 April 2012

Bathing Water Quality visualisation

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:


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 clust...
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
Gram-stained smear of streptococci (Photo credit: Wikipedia)


Stuart Williams said...

Hi Dan,

Nice work. I don't know where you set your thresholds of High, Medium, Low etc. But if you want to be selective in your API calls you can used the notations on the sample classifications. For weekly in-season assessment:

eg: http://environment.data.gov.uk/doc/bathing-water?latestSampleAssessment.sampleClassification.complianceCodeNotation=G

or for annual compliance assessments:


Code notations go: G, I, F, C, N (Higher/Excellent, Minimum/Good, Fail/Poor, Closed and Not-Assessed respectively).

Bet you liked being able to get 500 plus items at a shot :-)


danpaulsmith said...

Hi Stuart, cheers,

Yes, I've taken all this into account - the visualisation is very rough (I set the thresholds so I'd get a nice balance of red, yellow and green markers).

There's a bunch of things I'd do to complete the visualisation:

- Use the notations as a way of categorising the bacteria levels like you sugguest
- Visually include the actual assessment quality rankings
- Explain what the bacteria do to us
- Include some history about England's bathing water
- Compare a cup of water/london's sewage system bacteria levels so people understand what level of bacteria is being talked about...

Yes - getting 500 items back from the API was like taking a bite out of a cake and finding out it's moist.