Yesterday I gave myself a little time to muck about.
I saw Dan tweet about a collaborative google map about “Water Infrastructure Collapse”. I had a look and got the general idea to highlight instances where the infrastructure isn’t working. I’ve been interested in the concept of the mostly-invisible infrastructure stack since listening to Vinay talk about 6 ways to die and the work that Jay and others are doing around the idea of #stacktivism ie activism about the stack, it’s ownership, the political consequences etc.
I’d seen the Herne Hill flood in the news the other day, so I added that to the map. And then I searched for “burst water main UK” and found a number of other local news stories – if it’s big enough to stop traffic, it’s usually big enough to make the local paper, and that, these days, means it gets into Google News. So I added a few more, in Derby, Peterborough and S Norwood. [having just written that, I’m thinking now that what the map needs is something to show which water companies are involved…]
That got me thinking about leakage – surely someone must be monitoring this. Someone must be collecting data. So I had a look at the OfWat site and although I found the definitions of key performance indicators, I couldn’t actually find the data except by looking on each of the water companies websites. So I rang them up and put on my best information manager’s voice and asked whether anyone collated it all. No. They don’t, but they do require the companies to publish them and the woman I spoke to pointed me to the page where there are at least links to all the PDFs for this year. Once I’d downloaded all the PDFs I got to the bottom of the page where, “ta da!” there’s actually a summary spreadsheet for download. So I grabbed that.
And had a look. There’s an indicator for Leakage, it’s measured in Megalitres per day. That’s millions of litres per day – the range is between 4.2 and 646. But what’s a megalitre? The people on this friendly Australian forum tell me that an Olympic sized swimming pool might be 50m x 25m x 2m so 2500 cubic metres which would be 2.5 megalitres – so the smallest leakage is a little less than 2 Olympic pools per day and Thames water manages to lose more than 250 of the buggers. Per Day. This is news on the day that it gets announced. But the rest of the time we don’t think much about it, it’s just the way things are. What can we do? Should we do anything?
So then I got thinking about how much that might be per person. And the only thing I could find about populations served by each water company was this fact sheet from greenchristian.org.uk – thank you green Christians!
I put them together in this Google spreadsheet and that’s when my nightmares really began 🙂 It seems that the charts in Google spreadsheets are not very friendly to people wanting to make scatter plots and even less so if such people want to do some regression analysis add a line of best fit. The “Advanced Edit” is nothing of the sort. So I hacked something together.
That line of red dots is the line of best fit – the two companies way above the line are Dwr Cymru to the left and United Utilities (covering the North West) at about the 7 million people mark.
It’s a pretty good straight line (r2=0.88) which I interpret as meaning that they’re all equally bad at it – it’s not as if the little companies serving smaller populations are able to concentrate on it or that big old Thames just has a difficult time because it’s so big.
Anyway that’s as far as I got before my mucking around time ran out. Nothing to do with Social Artists Business Models or Hacking the Barbican, but I had fun!