How the Netherlands was built: 9.3 million addresses by year of construction on a map

Every address in the Netherlands has a year of construction. It is in the BAG, the Dutch national register of addresses and buildings (Basisregistratie Adressen en Gebouwen). Put 9.3 million of those years on a map, and you see the Netherlands grow. Press play.

Each block is one square kilometre, and the height is the number of addresses. Blue is what was already there: the lighter, the more addresses. Orange is what was added in that period. The bars under the map show how many addresses were added per decade.

Click a bar to go to one period. Zoom in with the + button or the mouse wheel, on a phone with two fingers. Drag to move the map. Hover over a block to see how many addresses it holds. The 2D button lays the map flat.

What you see on the map

The seventies are the biggest building period. Of the addresses that stand today, 1,365,073 have a year of construction from 1970 up to and including 1979. Add up everything from before 1980 and you get 4,842,696 of the 9,291,729 addresses. So just over half of all addresses are more than 45 years old.

In the seventies Lelystad appears as a new orange patch in the polder. Almere only comes after that. Of the 103,393 addresses in Almere, 2,991 have a year of construction from before 1980.

You can see the war in the bars. In the thirties 478,990 addresses were added, in the forties 138,454. Then it speeds up: 651,727 in the fifties and 1,076,599 in the sixties.

Really old is rare. 295,682 addresses have a year of construction from before 1900, 3.2% of the total. In the first frame those are the orange city centres.

The map only shows addresses that still exist. A 1960 neighbourhood that was demolished in 2000 is not on it. That makes the early years look smaller than they were at the time.

What is coming

The last frame is called “straks” (soon). It holds 298,365 addresses that are not finished yet: 177,644 with a granted building permit and 120,721 where construction has started. The BAG knows those addresses before anyone lives there.

Amsterdam has the most: 27,271. Then come Rotterdam with 15,576, Utrecht with 12,830 and The Hague with 10,914. Of all 298,365, 252,991 have a residential use. If you plan utility connections, deliveries or the search for new customers, this gives you an address list with coordinates before the first resident moves in.

The data: four columns from one file

Everything on this page comes from one CSV: the Zilver address file from postcode.store. It has every address from the BAG on its own row. The map needs four columns:

The trick is in the RD coordinates. Rijksdriehoek is the Dutch coordinate system, and its unit is the metre. Divide X and Y by 1000 and round down, and you have the one-kilometre square the address is in. You need no geographic functions. With latitude and longitude you would first have to convert to a flat map.

After a CSV import the columns are text. That is why there is TRY_CAST everywhere: a value that is not a number becomes NULL and gives no error.

The query

1
WITH a AS (
2
    SELECT
3
        CAST(FLOOR(TRY_CAST(XCoordinaat AS float) / 1000) AS int) AS km_x,
4
        CAST(FLOOR(TRY_CAST(YCoordinaat AS float) / 1000) AS int) AS km_y,
5
        TRY_CAST(BouwJaar AS int) AS jaar
6
    FROM dbo.PostcodetabelZilver
7
    WHERE PandStatus NOT IN ('Bouwvergunning verleend', 'Bouw gestart', 'Pand gesloopt')
8
)
9
SELECT
10
    km_x,
11
    km_y,
12
    CASE WHEN jaar < 1900 THEN 1800 ELSE jaar / 10 * 10 END AS decennium,  -- 1800 = alles van vóór 1900
13
    COUNT(*) AS adressen
14
FROM a
15
WHERE km_x IS NOT NULL
16
  AND km_y IS NOT NULL
17
  AND jaar <= 2026
18
GROUP BY km_x, km_y, CASE WHEN jaar < 1900 THEN 1800 ELSE jaar / 10 * 10 END;

jaar / 10 * 10 is an integer division: 1974 / 10 is 197, times 10 is 1970. That way every address gets the start of its decade.

The filter on PandStatus takes three kinds of addresses out of the map. A building with a permit or under construction is not there yet. Those go to the “straks” frame. A demolished building is no longer there.

The query returns 246,190 rows: one per square kilometre per decade, only where there are addresses. Added up, that is 9,291,729 addresses.

Do the numbers add up?

A map that quietly loses addresses looks just as good as a map that is correct. That is why the script counts every row of the file. Of the 9,590,787 rows, 9,291,729 are on the map as built and 298,365 in “straks”. The last 693 drop out: 680 have no coordinates, and 13 are demolished or are a building in use with a year of construction after 2026.

If it does not add up to 9,590,787, the script stops with an error. That check was broken on purpose once: with an extra filter in the query, some of the addresses dropped out, and the script refused.

Years of construction with a pinch of salt

The year of construction is not always exact. For old buildings you see peaks on round years: 21,394 addresses with 1890, 17,382 with 1880 and 14,304 with 1850. That points to estimates. There are also 312 addresses with the year 1005. On a map per decade they all fall into “before 1900”, so they do not distort the picture.

The map itself is one HTML file

The map is a plain HTML page with one canvas, without a library. A small Node script runs the query with sqlcmd and writes the result to a JSON file of 1.2 MB. For each square it holds a row of numbers: the position and the number of addresses per period.

The page draws the blocks with WebGL, on the graphics card. There are 31,943 of them, and each block has a top and a front. With plain canvas functions one frame took over 100 milliseconds, and then dragging stutters. All periods sit in one buffer on the graphics card. Picking another period only moves two positions in that buffer, so no data has to be sent again.

The height of a block is the square root of the number of addresses. Without that square root the centre of Amsterdam would hide everything and you would no longer see the countryside. The place names sit on the average RD point of the addresses of that place, from the same file.

Open the map on its own page.

Try it yourself

With the free demo file from postcode.store you can test the query on a subset of the addresses, in the same format as the full file. This finds the new builds per municipality:

1
SELECT TOP (10) Gemeente, COUNT(*) AS adressen
2
FROM dbo.PostcodetabelZilver
3
WHERE PandStatus IN ('Bouwvergunning verleend', 'Bouw gestart')
4
GROUP BY Gemeente
5
ORDER BY adressen DESC;

Zilver also has the columns Doel, Oppervlakte, Buurt and Wijk. Replace km_x and km_y with Wijk, and you have the year of construction per district instead of per square kilometre.

If you want the addresses themselves as points on a map, read postcodes on a map on postcode.store. It also has an animation of address density per province. If you only need postcodes and house number ranges, start with the Dutch postcode table in SQL Server.