Postcode table of the Netherlands + SQL Script

Q: Where do Dutch postcodes come from?

From the Kadaster (the Dutch land registry). It supplies the BAG files with all addresses and buildings. You have to process those files first before you can use them in a database.

Q: How do I link a customer address to the postcode table?

With an INNER JOIN on postcode, house number and addition. Then you find, per customer, the row with the town, the province and the coordinates.

Q: Is the free postcode table from sqlblog.nl still current?

No. The file is from before 2010 and has 471,993 rows. For current addresses you need a file that is updated from the BAG every month.

Q: How do I turn latitude and longitude into a geographic point in SQL Server?

With a computed column of the type geography: geography::STGeomFromText('POINT(lon lat)', 4326). The table in the script already does this in the column geographypoint.

Q: Does the SQL script also work in MySQL?

No. The script is written for Microsoft SQL Server. MySQL has no geography type and gives a syntax error.

Introduction

The Dutch postcode table holds information about all postcodes known in the Netherlands. This is very useful when you want more information about a certain postcode, such as a geographic location or the town that belongs to a postcode. In this article we show what you can do with these postcodes.

Source of the Dutch postcode table

The source of Dutch postcodes is the Kadaster (the Dutch land registry). The Kadaster supplies so-called “BAG files” through its site. They hold information about homes and buildings: the postcode among other things, but also a lot more interesting data. You have to process these files first before you can use them. This is quite complicated, and in practice not doable. My advice is not to reinvent the wheel but to use a ready-made file. There are initiatives for the data extraction, but studying them takes a lot of time (time=money). I work with a paid version of the postcode table (address file) myself, which holds over 9 million addresses. With this data you can find the exact location of your customers, because the address with house number is unique. In short, every address is a unique record with extra information such as Lat/Lon, province, town, etc.

Say you have a customer record in the database of your ERP system, with a postcode and an address. With an INNER JOIN (a link, a kind of VLOOKUP) to the postcode table on postcode + house number + addition, you can find the record that belongs to it. Then you can take the coordinates from the postcode file. The great thing about this: you now know where in the Netherlands the customer is. You can show this data in Excel in Excel Maps, but also in PowerBI. I must say, this works brilliantly! If you use the 6-digit table you can also link coordinates, but they are less precise. For example, you can analyse in which areas of the Netherlands most of your customers are. Even more interesting may be where you have no customers yet!

How does the SQL postcode table work?

Every postcode, together with its minimum and maximum house number range, has its own row in the table. The X-Y coordinates, also called latitude/longitude, are useful too. I used an SQL Function to calculate the GeoLocation directly, as you can see in the SQL statement below.

The free postcode table has 471,993 rows in total, so it is quite a big table. With the attached SQL Script you can import all data into Microsoft SQL Server in one go. At the bottom of this post you find a download link, the SQL script. Note that this file is not up to date and is < 2010. My advice is to buy the newest postcode table from postcodestore. It costs 249 euros and gives you over 9 million up-to-date rows of data. The file is updated every month, so you can also buy a new file again later. The website also has tutorials on how to import the data.

If you know your way around SQL, you will of course know how to work with this data. I will soon publish an article about how to show the geographic data from a table with a postcode/house number, for example in Excel Maps or Power BI.

Creating the postcode table

Run the SQL below to create the postcode table. Then run the script in SQL Server to fill the table with postcodes.

1
CREATE TABLE [dbo].[REF_Postcode_NL](
2
[id] [float] NULL,
3
[postcode] [nvarchar](255) NULL,
4
[postcode_id] [float] NULL,
5
[pnum] [float] NULL,
6
[pchar] [nvarchar](255) NULL,
7
[minnumber] [float] NULL,
8
[maxnumber] [float] NULL,
9
[numbertype] [nvarchar](255) NULL,
10
[street] [nvarchar](255) NULL,
11
[city] [nvarchar](255) NULL,
12
[city_id] [float] NULL,
13
[municipality] [nvarchar](255) NULL,
14
[municipality_id] [float] NULL,
15
[province] [nvarchar](255) NULL,
16
[province_code] [nvarchar](255) NULL,
17
[lat] [float] NULL,
18
[lon] [float] NULL,
19
[rd_x] [float] NULL,
20
[rd_y] [float] NULL,
21
[location_detail] [nvarchar](255) NULL,
22
[changed_date] [datetime] NULL,
23
[geographypoint] AS ([geography]::STGeomFromText(((('POINT('+CONVERT([varchar](20),[lon],(0)))+' ')+CONVERT([varchar](20),[lat],(0)))+')',(4326)))
24
) ON [PRIMARY]

Enriching data with the postcode table in Excel

What you can do with the postcode table is show data on the map. For example, with the first four digits of the postcode you can show data from Excel on the map with Power Maps. Power Maps comes free with Excel from version 2013. In this article we discuss how to do this. That article has a number of practical examples with Excel and SQL.

Postcode table

Excel file

Conclusion

The postcode table of the Netherlands is a useful table. You can use it to make data entry easier, to enrich data, or to show data on the map. To learn more about postcode tables and data analysis, read the other articles on this topic.

This article is part of a series:

Part 1: Dutch postcode table + SQL Script for import
Part 2: Splitting postcode ranges into single records
Part 3: Enriching customer data with the postcode table
Part 4: Visualising data with coordinates in Power BI

Frequently asked questions

Where do Dutch postcodes come from?

From the Kadaster (the Dutch land registry). It supplies the BAG files with all addresses and buildings. You have to process those files first before you can use them in a database.

How do I link a customer address to the postcode table?

With an INNER JOIN on postcode, house number and addition. Then you find, per customer, the row with the town, the province and the coordinates.

Is the free postcode table from sqlblog.nl still current?

No. The file is from before 2010 and has 471,993 rows. For current addresses you need a file that is updated from the BAG every month.

How do I turn latitude and longitude into a geographic point in SQL Server?

With a computed column of the type geography: geography::STGeomFromText('POINT(lon lat)', 4326). The table in the script already does this in the column geographypoint.

Does the SQL script also work in MySQL?