For the Province of South-Holland we’re currently helping them to extend their open data provision. Next to looking at data they hold relevant to key policy domains, we also look at what other data is available elsewhere for those domains. For instance nationwide datasets with local granular level of detail. In those cases it can be of interest to take the subset relevant for the Province and republish that through their own channels.

One of the relevant topics is energy transition (to sustainable energy sources). Current and historic household usage is of interest here. The companies that maintain the grid publish yearly data per postcode, or at least some of them do. There are seven of these companies.
Luckily all three companies active in South-Holland do publish that data.


In South-Holland three companies are active (number 3, 5 and 6)
(Source: Energielevernanciers.nl

Having this subset of data is useful for any organisation in the region that wants to limit the amount of data they have to dig through to get what they need, for the provincial organisation itself, and for individual citizens. Households that have digital meters have access to their daily energy usage readings online. This data allows them to easily compare their personal usage with their neighbours and wider surrounding area. For instance I established that our usage is lower for both electricity and gas than average in our street. It is also easier to map, or otherwise visualise, in a meaningful way for the province and relevant regional stakeholders.

Here’s a brief overview of the steps we’re taking to get to a province-wide data set.

  • Download the data for the years available for Westland, Liander and Stedin (Westland goes back to 2010, the others to 2008)
  • Check the data formats: Westland and Stedin provide CSV, Liander XLSX
  • Check data structure: all use the same structure of fields and conventions
  • To get only the data for South-Holland we use the postcode that is mentioned in the data.
  • The Dutch postcode zones do not conform to provincial boundaries however, so we take the list of four position postcodes and determine the ones that fall within South-Holland:
    • 1428-1429
    • 2159-2164
    • 2170-3381
    • 3465-3466
    • 4126-4129
    • 4140-4146
    • 4163-4169
    • 4200-4209
    • 4213
    • 4220-4249
  • The data contains 6 position postcodes of the structure 1234AB. We need to split them into the four digits and the two letters, to be able to match them with the ranges that fall within the province.
  • For personal data protection purposes, in the data, for 6 position postcodes where the number of addresses in that postcode is less than 10, the data is aggregated with a neighbouring postcode, until the number of addresses is higher than 9. It is not certain that those aggregations fall within a single province. The data provides a ‘from’ 6 position postcode and a ‘to’ 6 position postcode. This is the same value where the number of addresses in a postcode is high enough but can be a wider range.
    • We need to test if the entire postcode range in a single data record falls within one of the ranges of postcodes that belong in South-Holland.
    • For the small number of aggregates that fall into two provinces we can adopt the average usage number, but need to mark that the number of households in that area is unknown,
    • or retrieve the actual number of addresses from the national address and building database, and mark that the average energy usage values are from a larger number of addresses.
    • Alternatively we can keep the entire range, including the part outside the province,
    • or we exclude the entire range and leave a ‘hole in the map’.
    • In any case we need to mark in the data what we did, and why.
  • The result is then a data set in CSV that consolidates the three sources for all those records that fall within the province.
  • This dataset can then be mapped, e.g. in Q-GIS or other tools in use within the province South-Holland.
  • We provide a recipe and/or script from the above steps that can take the future yearly data sets from the three sources and turn them into a consolidated subset for South-Holland, so that the province can automate keeping the data up to date.

Which energy data is available as open data in the Netherlands, asked Peter Rukavina. He wrote about postal codes on Prince Edward Island where he lives, and in the comments I mentioned that postal codes can be used to provide granular data on e.g. energy consumption, while still aggregated enough to not disclose personally identifiable data. This as I know he is interested in energy usage and production data.

He then asked:

What kind of energy consumption data do you have at a postal code level in NL? Are your energy utilities public bodies?
Our electricity provider, and our oil and propane companies are all private, and do not release consumption data; our water utility is public, but doesn’t release consumption data and is not subject (yet) to freedom of information laws.

Let’s provide some answers.

Postal codes

Dutch postal codes have the structure ‘1234 AB’, where 12 denotes a region, 1234 denotes a village or neighbourhood, and AB a street or a section of a street. This makes them very useful as geographic references in working with data. Our postal code begins with 3825, which places it in the Vathorst neighbourhood, as shown on this list. In the image below you see the postal code 3825 demarcated on Google maps.

Postal codes are both commercially available as well as open data. Commercially available is a full set. Available as open data are only those postal codes that are connected to addresses tied to physical buildings. This as the base register of all buildings and addresses are open data in the Netherlands, and that register includes postal codes. It means that e.g. postal codes tied to P.O. Boxes are not available as open data. In practice getting at postal codes as open data is still hard, as you need to extract them from the base register, and finding that base register for download is actually hard (or at least used to be, I haven’t checked back recently).

On Energy Utilities

All energy utilities used to be publicly owned, but have since been privatised. Upon privatisation all utilities were separated into energy providers and energy transporters, called network maintainers. The network maintainers are private entities, but are publicly owned. They maintain both electricity mains as well as gas mains. There are 7 such network maintainers of varying sizes in the Netherlands

(Source: Energielevernanciers.nl

The three biggest are Liander, Enexis and Stedin.
These network maintainers, although publicly owned, are not subject to Freedom of Information requests, nor subject to the law on Re-use of Government Information. Yet they do publish open data, and are open to data requests. Liander was the first one, and Enexis and Stedin both followed. The motivation for this is that they have a key role in the government goal of achieving full energy transition by 2050 (meaning no usage of gas for heating/cooking and fully CO2 neutral), and that they are key stakeholders in this area of high public interest.

Household Energy Usage Data

Open data is published by Liander, Enexis and Stedin, though not all publish the same type of data. All publish household level energy usage data aggregated to the level of 6 position postal codes (1234 AB), in addition to asset data (including sub soil cables etc) by Enexis and Stedin. The service areas of all 7 network maintainers are also open data. The network maintainers are also all open to additional data requests, e.g. for research purposes or for municipalities or housing associations looking for data to pan for energy saving projects. Liander indicated to me in a review for the European Commission (about potential changes to the EU public data re-use regulations), that they currently deny about 2/3 of data requests received, mostly because they are uncertain about which rules and contracts apply (they hold a large pool of data contributed by various stakeholders in the field, as well as all remotely read digital metering data). They are investigating how to improve on that respons rate.

Some postal code areas are small and contain only a few addresses. In such cases this may lead to personally identifiable data, which is not allowed. Liander, Stedin and I assume Enexis as well, solve this by aggregating the average energy usage of the small area with an adjacent area until the number of addresses is at least 10.

Our address falls in the service area of Stedin. The most recent data is that of January 1st 2018, containing the energy use for all of 2017. Searching for our postal code (which covers the entire street) in their most recent CSV file yields on lines 151.624 and 625:

click for full sizeclick to enlarge

The first line shows electricity usage (ELK), and says there are 33 households in the street, and the avarage yearly usage is 4599kWh. (We are below that at around 3700kWh / year, which is higher than we were used to in our previous home). The next line provides the data for gas usage (heating and cooking) “GAS”, which is 1280 m3 on average for the 33 connections. (We are slightly below that at 1200 m3).