I’ve come home from work and my bin’s still full!
An update on what I’ve been up to as a developer in the Service Design team. Last time I wrote, it was around the development of forms and a dashboard for the Community Recycling Centres. This time, it’s a not totally unrelated topic, but also quite separate: missed bins.
When a bin’s not been emptied on the expected day, it might get reported to the Council, either by the resident completing an online form, or by them calling up the contact centre (who will complete the form on their behalf). Then that form data goes to Mayrise, the back office system for bin collections.
Reports may also go into Mayrise via other routes — for example, if the crew report they have had to miss a street because it was blocked and inaccessible to their truck.
Once reported, the bin will be scheduled for another collection.
So here’s another opportunity for me to make a dashboard! (This will probably become a familiar theme to regular readers).
Here in North East Lincolnshire, our household waste is divided into three types: general domestic waste in a green wheelie bin, garden waste in a brown wheelie bin, and then recyclables, which go into different coloured boxes according to type (glass, tins and plastics, and paper and card). Each household has fortnightly collections for domestic waste and recyclables, and subscribers who pay an annual fee for garden waste collections also have fortnightly collections for all but the winter months.
Any time we have a bin reported as missed, we have a wealth of data about it: the bin type, the property address, the ward in which the property belongs , the day on which it was due to be collected, the date and time of the report, the round and the crew, whether it’s an assisted collection, and so on.
This data gets extracted from Mayrise overnight and dropped into an Oracle database, and then it gets visualised as charts, tables and maps in an application written in Oracle Application Express (Apex).
We can see patterns emerge straight away. For example, missed garden waste reports seem to peak in April when the new billing year starts, when people have perhaps forgotten to resubscribe to the service, while there are few (but not zero) reports of missed garden waste in December when the service is not operating. But drilling down, other patterns may also exist.
Are particular properties being missed disproportionately more often than others? With the dashboard we will be able to see this more clearly, and ask why and how to avoid it happening in the future.
We can break down missed bin data in various ways, such as by type of waste over a given time period by round or by ward, and display it on charts or plot it on a Bing (Bing?! Yes, Bing. I’m as surprised as you are) map. From there, we can drill down to find out more specific data about individual properties. Really, there are endless ways of representing the data, but maybe not all of them are useful, so with great feedback from the service and the project lead, we’ve focused on what’s going to give most value to the service.
For now, all missed bin data is going in, but we will be taking into consideration withdrawn reports and also outcomes as the project progresses. No doubt, at a later date, I’ll post an update to this project.
