top-poop / top-of-the-poops Goto Github PK

View Code? Open in Web Editor NEW

14.0 3.0 5.0 12.48 MB

Analysing Sewage Information from the UK Environment Agency

Home Page: https://top-of-the-poops.org

License: Other

Makefile 7.45% Shell 0.16% Python 37.65% CSS 10.10% HTML 3.74% JavaScript 40.36% PLpgSQL 0.53%

sewage uk-government environment-agency

top-of-the-poops's Introduction

Top Of The Poops

Website: top-of-the-poops.org

Seems that #sewage is on people's minds right now.

The UK publishes some information about sewage outfalls - here are some scripts to get this information, analyse it, and perhaps publish some interesting findings.

Data Reuse and Attribution

Please re-use our data.

Press contact: press [at] top-of-the-poops.org

If you publish data, content, or images from our site, please note it is CC-BY-SA 4.0, and as such we require suitable attribution

Derived Data / General Content - should be attributed, with name and hyperlink Top of the Poops
Images / Maps - should have caption '(c) top-of-the-poops.org', or similar, either as plain text or hyperlink, and ther should be a hyperlink as above in the main body of the text.

Please refer to: https://wiki.creativecommons.org/wiki/Recommended_practices_for_attribution

Derived data is (C) Top-Of-The-Poops - CC-BY-SA 4.0, all original data is (C) the original data owner, and is used under appropriate licence

Maps

We previously used mapbox but after getting very popular we couldn't afford it anymore! Maps now rendered ourselves, but it's not going to be as fast as MapBox.

We use TileServer GL in combination with a UK Vector map from MapTiler

How to use

You can clone the repo - I use IntelliJ IDEA to make a hot-reloading web page. The build runs locally with make watch

All data files are generated on developer machine, only the javascript build runs on CI. This ensures the CI build is acceptably fast. Currently it runs in about 10 seconds. Which is OK, could be faster.

make watch uses inotify - this may not work on MacOS.

Contributing

Contributions are welcome - especially CSS / Javascript improvements! But please chat before doing any real work - to make sure everyone is aligned with direction.

Development Environment

This has been developed on Linux, the makefiles may or may not work on a Mac.

Setting up the database

make python
cd db/data
make load-all

Generating json data files

You'll need to have set up the database stuff first

make generated

React

Why is there a React app per page? Because it makes it easy to write the software

Source: Office for National Statistics licensed under the Open Government Licence v.3.0

Contains OS data © Crown copyright and database right 2021

Sewage Data

Event Duration Monitoring

https://environment.data.gov.uk/dataset/21e15f12-0df8-4bfc-b763-45226c16a8ac https://environment.data.gov.uk/portalstg/home/item.html?id=045af51b3be545b79b0c219811d3d243 https://environment.data.gov.uk/portalstg/sharing/rest/content/items/045af51b3be545b79b0c219811d3d243/data

2022

https://environment.data.gov.uk/portalstg/home/item.html?id=2f8d9b7628dd4f60a30fb1a8483fc2ae

Consented Discharges with Conditions

https://environment.data.gov.uk/dataset/5fe5ab2e-d465-11e4-8a42-f0def148f590 https://environment.data.gov.uk/portalstg/sharing/rest/content/items/5e618f2b5c7f47cca44eb468aa2e43f0/data

Wales

Consented Discharges with Conditions

https://lle.gov.wales/catalogue/item/ConsentedDischargesToControlledWatersWithConditions/?lang=en https://naturalresourceswales.sharefile.eu/share/view/s05adea6ab5d4df58/fo289e69-abc0-4acb-9923-271512440118 https://storage-eu-205.sharefile.com/download.ashx?dt=dt99e5eec3bd194293acd60049575d41ee&cid=9AQXBd2ldhvlRrRbQ8tE-w&zoneid=zpc3159d90-01f7-41a7-a8ab-3704157466&exp=1637152468&zsid=FB&h=F%2BC3TQBtcWx%2BYjb4jglnxmRAZLWwiRKrwDw7xn%2BoShI%3D

Event Duration Monitoring

2020 - Can't find! - Partial information at: https://www.dwrcymru.com/en/our-services/wastewater/combined-storm-overflows/valleys-and-south-east-wales

2021 - Main page: https://www.dwrcymru.com/en/our-services/wastewater/river-water-quality/combined-storm-overflows 2021 - Seems to be split over 3 files (with different formats), unknown overlap with Environment Agency data.

Bathing

Bathing Water Monitoring Locations https://www.data.gov.uk/dataset/dcb8bd46-c4cf-4749-bad0-7663da96845c/bathing-waters-monitoring-locations Name + Classification by year

Sensitive Areas Bathing https://www.data.gov.uk/dataset/4e2bbdb4-15d3-49dc-ba22-904045b091fb/sensitive-areas-bathing-waters https://datamap.gov.wales/layers/inspire-nrw:NRW_UWWTD_SA_BATHING_WATERS

Postcodes

https://geoportal.statistics.gov.uk/datasets/ons-postcode-directory-february-2020/about

https://data.gov.uk/dataset/6de48d19-b3a0-4e45-b98e-01bd781b035c/ons-postcode-directory-latest-centroids

http://geoportal1-ons.opendata.arcgis.com/datasets/75edec484c5d49bcadd4893c0ebca0ff_0.csv?outSR={%22latestWkid%22:27700,%22wkid%22:27700}

Software

You'll need the following:

python3
libreoffice
gdal-bin

Things to do

Link with voting results - need to find the division results...
Rivers and beaches by constituency?
Constituency page showing all the things by constituency?

Data Quality

To be sure the quality of the data is unbelievably poor. Perhaps it is so poor so that it is hard to understand?

2021 Data

Issues

Distributed as an Excel file, which is hard to process
- Should ideally be a machine readable format. I'll say simple XML, with schema, but a consistent CSV file would be OK.
Mix and match of data types
- Numeric columns have "N/A", "#N/A", and "#NA"
- Name columns have "0" in
- Percentage values scale from 0-1 in some sheets, and 0-100 in others, because some sheets have cells set to "Numeric", and others to "Percent"
Continuation rows
- A few of the sheets don't stick to "one row per record", which is kinda mandatory in a machine readable file.
Inconsistent data
- Particularly consent ids don't match consent ids in the consent database - the formatting differs
- Consent ids don't have a consistent format.
- Loads of EDM rows don't match valid consents.
Duplicate data rows
- Some data rows are duplicates in many of the source files. It is not clear why, it looks like an extract from a database upstream has maybe repeated rows where there are multiple assets with the same consent information?
Wales data is spread over multiple files, with different formats, and may or may not overlap with EA data.

Noted Improvements

The files are now tabs in a single document with almost consistent data across the tabs.

Example Duplicate Data

'Anglian Water', 'DAVENTRY SEWER SYSTEM', 'AW5NF181', 'A1'
'Dwr Cymru Welsh Water', '#TBC', '#N/A', '', '', '', '0.25', '1', '100', ''
'Severn Trent Water', 'WITTON - GEORGE ROAD XXX (CSO)', 'TBC', '', '', '', '', '', '', ''
'South West Water', 'KINGSAND SEWAGE PUMPING STATION', '301903', 'A1', '', 'KINGSAND BEACH', '33.72', '21', '100', ''

top-of-the-poops's People

Contributors

Stargazers

Watchers

Forkers

amyslack nininn asibs josieailsa

top-of-the-poops's Issues

The data pop up data for Colwall in North Herefordshire is different to the data in the list below the map. Why?

COLWALL SO
Cradley Brook
79 Dumps / 579 hours by Severn Trent Water

Severn Trent Water
Cradley Brook
Colwall STW
103 sewage dumps 1360 hours 84.5 % reporting

Accuracy of Information

Hi,

Good work on the web site. I generally support what you are aiming to do, but have some questions and possible concerns about the validity of the data. I've seen the warning you give.

We have a local press report regarding a "cocktail of filth dumped into seas". I believe it is based on ToTPs information. It lists the 10 worst North Wales beaches for sewage spills in 2022. Morfa Nefyn, close to where I live is listed as number 2 with 521 spills and 1571 hours total duration.

I assume this is related to information provided here by Dwr Cymru (Welsh Water):

https://corporate.dwrcymru.com/en/community/environment/combined-storm-overflows

A couple of questions...

When you refer to a beach, I assume you are using the water companies catchment area designation, and not actually the beach. Is that correct? In our case the waste water overflow discharges will be in areas east and west of Morfa Nefyn beach and not actually associated with Morfa Nefyn beach.

Adding all the hours of overflow, for all the facilities in the catchment, I see where the total hours come from. The bulk of the overflows are associated with a WWTW in this area. This is serving the small local community. I agree overflows are not good, but an overflow from a small treatment works is a lot less volume than an overflow from from a large treatment works serving a larger population.

Consequently, I think the press article gives Morfa Nefyn beach a bit of an undeserved label. The bathing water quality is assessed by Natural resources Wales and is assessed as excellent.

https://environment.data.gov.uk/wales/bathing-waters/profiles/

Is there any way any of this could be addressed to be provide more representative information?

Just Getting In Touch

Hi!

Apologies for logging an issue when I actually just want to get in touch - I am not sure how to use this site so not quite sure how to message you!

My name is Amy Slack and I am Head of Campaigns & Policy at Surfers Against Sewage. As you may be awear, we have been campaigning on the issue of sewage pollution for over 30 years and now lead a coalition of organisations and community groups called the #EndSewagePollution coalition who have come together over the last 18 months to drive the agenda with the public and with politicians, particularly through the Environment Act.

The reason for getting in touch is mainly to connect with you. 'Top of the Poops' is a great website and we love it here at Surfers Against Sewage! It would be great to have an informal chat with you and to introduce ourselves more than anything. It would be great to understand your plans for this site going forward and to share our campaign plans a bit with you.

You can reach me directly on xxx private information redacted by top-poop

Look forward to talking!

Many thanks

Amy

Actual timelines

See https://its-airborne.org/technologies which works with github-backed sites such as this.

Missing data for River Frome

The data you have for Rode (on the River Frome, in the Somerton & Frome constituency) is missing the figures for the Rode Wastewater Treatment Works (permit 11560 - 82 overflows, for 1248 hours). You are only showing the data for the much smaller overflow - permit 100190)

Constituency data fails to download

Never having used Node before I had to install a long list of undocumented stuff I've never previously heard of. Eventually make seemed to work, however...

When it came to download the constituency data the install ground to a halt. It seems the location of said data has changed?

Jim

Not the UK

Hi
Just to say that you describe your data as covering the UK but I don't believe you are covering Northern Ireland which is part of the UK
Good job otherwise
David Brown

Southern Water Self-reporting Data

Hi there

Love your work!

I have programmatically pulled 'event' data from Southern Water's self reporting tool: Beachbuoy

I've uploaded it to a github repo here: https://github.com/cleansouthernwater/Beach-Buoy-Event-Data

Perhaps it may be of use to you...

Irwell River ? duplicate Radcliffe Central CSO?

Hi. Trying to understand the ?duplicate? Radcliffe Central CSO[s] on the River Irwell.

One shows 23 dumps over 23 hours UU and the other shows 20 dumps over 48 hours UU.

I don't want to criticise UU unduly. They do enough of that by themselves...

Much appreciate the work that you do folks.

I hope this image is correct and helps your understanding. I took a screen shot and pasted it.

Regards

Some data for River Lim is missing

The data for the River Lim doesn't include the Uplyme Sewage Treatment Works which it should - it says it's on a tributary but is actually within a metre or so of the main river. The spillage hours are over 2000 in total for the river according to the EA data.
Spill data for 2022 - Lyme Regis.xlsx

River discharge proportionality to River length

As longer rivers would be expected to receive more discharge, Is it possible to display the data proportional to river length?

I am struggling to find a comprehensive dataset on length of UK rivers to do a comparison myself, thanks.

Missing data for an MP/Constituency

The data for my local area/MP seems to be missing – is it possible to add the info for Stephen McPartland MP in the constituency of Stevenage..?

Thanks.

Why don't you express the figures as a percentage

Why don't you express the hours of discharge as a % of operating hrs?

How I've been getting on with South West Water

This isn't really an "issue", but you did say "let us know how you get on" down here in Cornwall. Please let me know if there's a more appropriate place for this sort of thing.

I've sorted out my 2020/21 data source issues, and I am now eagerly awaiting the data for 2022. When does it usually emerge?

Since we last spoke I've appeared on ITV, albeit regarding "industrial" pollution of rivers. I've also appeared on the BBC, regarding the ongoing drought in Kernow.

I've had numerous discussions with @SWWHelp on Twitter, but they still haven't answered the £64 billion question:

https://twitter.com/DavidstowInfo/status/1634238007652827209

"How much poop does the Environment Agency permit SWW to flush into the ocean?"

I've also (a bit belatedly) received a response from SWW to an EIR request:

"Storm overflows (SO) are built into the sewer network system so that at times of high network pressure, when flows are increased by heavy rainfall for example, they can take excess volumes of water out of the system, discharging flows to a river or stream. This ensures homes and businesses are not flooded. SOs were built as part of the system and we have permits from the Environment Agency (EA) to operate them, SO releases are not deemed pollutions , they are designed to spill when flows are increased.

If a SO spill is categorised as a ‘wastewater pollution’ incident by the EA, it is because it has operated outside its permit...

A SWW Beachlive alert is generated at bathing water site level, not SWW SO level. An alert is triggered when an overflow event occurs which could temporarily affect bathing water quality, this is when an overflow/s operates for more than a specified duration in a rolling 12-hour period.

The specified duration at each bathing water is based upon EA’s assessments. They may differ at each bathing water due to the position, length and diameter of the overflows and other local factors such as hydrodynamics. We issue Beachlive alerts 24/7/265"

There are other EIRs from your truly in the SWW queue, albeit more to do with the supply side at the moment.

Also Feargal "retweeted" my Cornish alter ego yesterday:

https://twitter.com/DavidstowInfo/status/1634321040791728128

15K views and counting.

Jim

Press query

Hi all,

We're going to use this data for a story about sewage discharges in west Wales, in particular Dwyfor Meirionnydd and Ceredigion. We've been in communication with Welsh Water, as you can imagine. Its comms people are suggesting we ought to be cautious about using this data as it could be inaccurate.

I wonder, therefore, whether they have contacted you and offered you fully accurate data? Given your figures could be so reputationally damaging to them, you'd think they would have had a rebuttal prepared, no? If they haven't it tells me your data must be fairly accurate, as you've suggested. But I just want to check.

Also, surely for the Welsh discharges you've looked at Welsh Water data as well as NRW data. Or just WW?

Please get back to me as soon as possible. We are publishing early next week.

Please offer comment too should you wish.

Thanks very much.

Regards,

Felix Nobes

Press Query Urgent

Hello,

We're looking to publish a story in the next few days and want to clarify which river No. 20 is on the worst polluted rivers list?

Is it The River Taff/Taf that runs through Cardiff or the Taf that runs through West Wales

Thanks

Show data by new UK constituencies (post-boundary-review)

Hi 👋

Great work on this site!

I've checked out the code, and been trying to get all the data in order to generate data / rankings for the new UK constituencies (post the final boundary review recommendations which came out this year).

I've managed to source a combined dataset of the new shapefiles from MySociety:

And I've got that downloading and loaded into the PostGIS database (I'll put in a PR shortly to show where I'm up to).

However, when I get to the stage of actually loading the water company data, I'm getting errors on the Wales dataset (there's a comment - link looks dodgy - might be time limited? - in the makefile against the consents-wales.zip file, which is the one I'm unable to download).

Do you know where you managed to get this link from? As per the comment, it may be a time-limited download token, but I couldn't find a data download on Wales Water (just a link to an online map...)

Thanks!

You missed a water company

There are two Water and Sewage Companies in Wales, Dwr Cymru and Hafren Dyfrydwy. They're small (~50 EDMs), but it fills in that gap in north-mid Wales that looks a bit blank on your map of EDMs ;)