Showcase · Data layers · City of Melbourne open data

You can’t manage what you can’t map

The City of Melbourne publishes its own electricity, gas and water bills: 5,444 rows that name an asset, a start date, an end date and an amount, for the financial year 1 July 2013 to 30 June 2014, the newest metered data the council publishes. 106 of its 316 assets have no location in the council’s asset register. Of the 60 that use 90% of each of electricity, gas and water, 18 have none. Placed in Plotlay from the council’s own parcels, footprints and streets, those assets used 31% of the water on the map.

Bills arrive as a table

A facilities manager gets bills by asset name: “Garden - Treasury”, “Library - East Melbourne”, “Street Lighting - Docklands Network”. The council’s register gives a point for most of them, but not all: 106 of 316 assets with bills have no position, and 5 more share one stand-in point on Swanston Street. Without a place there is no map, no area and no “per square metre”.

Placing the assets with no location

Linked to an empty data layer, the asset table becomes a list of rows to place. Each row comes up in turn and takes the shape it is given: a council property parcel or a 2023 building footprint picked from the council’s own open layers (parts joined by their id, so Kings Domain’s five pieces are one key), a boulevard drawn as a line along the street, or a point where there is no outline. Six networks (street lighting, cameras, parking meters, signs, many small street reserves) have no single place and stay in the table, and two reserves are named in no council layer. Nothing here comes from OpenStreetMap or from aerial imagery.

The map

The 52 assets placed in Plotlay, coloured by the measure picked for the month on the slider, or the whole year. Electricity, gas and water are shown one at a time and never added together. Hover over an asset, or tap it on a phone, for its numbers and its months. Orange outlines mark the assets the register gave no location.

Summer water is park water

With the bills spread over their days and totalled by month, the parks and gardens use 7.5 times as much water in January as in July. The pattern is not a billing gap: for the 19 parks whose water bills cover every day of the year it is 8.0 times. Across all council assets, parks and open space used 85% of the year’s 995,741 kL.

Outliers: cap them, then ask why

Divided by the parcel each park was placed on, the year’s water is a depth: a median of 421 mm over the whole parcel. Capping the top tenth keeps one value from washing out the scale, and the two that stand out are questions about bills, not about parks. Parliament Reserve’s water bills cover 211 of 365 days: there is no bill from 15 November to 28 February, then 25,115 kL of its 27,257 kL arrive between 1 March and 13 May 2014. Southbank Promenade is billed on several meters at once. And the Fitzroy Gardens pavilion cafe’s meter recorded 38,105 kL, rising 3.8 times from July to January like the garden beside it: it likely serves garden watering too. The map finds them; the bill table explains them.

How it was made in Plotlay

1. A map project on the Geoscience Australia base map, and a data layer called Council assets (Data layers, in the Site workspace).
2. Start from the data. Link the council’s asset table to the empty layer: every row is one to place, with its address beside it.
3. Pick from a layer. Add the council’s 2023 building footprints (parts joined by structure_id) and property boundaries (parts joined by property_id), with their licence. A click takes the outline under it and gives it the row’s name. 21 footprints and 22 parcels.
4. Lines and points. Five boulevards and street reserves drawn as lines along the street (through the centres of the council’s road corridor segments); four sites with no outline as points.
5. Link the bills: spread each bill evenly over its days, keep only 1 July 2013 to 30 June 2014, group by month. The measure column keeps electricity, gas and water apart.
6. Formulas: estimated floor area (floors * area_m2), water as a depth (sum(amount, measure = "Water") / area_m2 * 1000), summer over winter.
7. Colour by water on the month slider (log scale), by depth with the outliers capped, by electricity divided by floor area with breaks set by hand, and raise it in 3D by value.
8. Export GeoJSON, TopoJSON for the Power BI Shape Map, CSV with WKT, the linked data and a Python starter.

Every shape was checked against the council’s parcels and footprints, with the register point marked where there is one. One building, the storage at 602 Little Bourke Street, was placed at its address: the register point is at the council’s depot in North Melbourne, 2.3 km away.

More about Data layers · Make a data layer of your own

Power BI

The same shapes and monthly usage as a Power BI Project (.pbip): a Shape Map from the TopoJSON keyed by the council’s asset names, a measure slicer (one of electricity, gas or water at a time), a scatter chart with the month as its play axis, per-area measures in DAX and a drill-through page for each asset, with the Plotlay theme. It reads the data from the GitHub repository, so it refreshes with no gateway.

The Power BI project is in the plotlay-melbourne-council-energy repository on GitHub, with the steps to open it in Power BI Desktop.

Python

A narrated Jupyter notebook loads the GeoJSON with GeoPandas, joins the monthly usage on the asset name, and works out every number on this page, including the billing coverage checks.

Download the notebook (with its outputs) and the data. Both are also in the plotlay-melbourne-council-energy repository on GitHub.

Read it carefully

• The bills are for 1 July 2013 to 30 June 2014, the newest metered data the council publishes. This is a method demonstration with real data, not a statement about the assets today.
• The map covers the 60 assets that use 90% of each of electricity, gas and water; 52 have a shape. The other rows stay in the table and count in the totals.
• Electricity is the council’s grid electricity rows. Council House 2 also makes electricity on site from gas, counted under gas.
• Water depth is over the whole parcel, not the irrigated area. Floor area is an estimate: the 2023 footprint times the floors in the council’s 2014 building census. Two leases share their building and are not compared.
• Some 2014 sites have changed: the City Square car park and the Federation Square visitor centre made way for the Metro Tunnel works, so they are points, not 2023 footprints.