Service areas per branch on the postcode map: who serves which postcode?

Many companies with branches keep a table: the Gouda branch covers postcodes 2741 to 2900, Rotterdam 2901 to 3399. A table like that looks fine in Excel. Only on the map do you see what it really does. Click a branch.

The branches are made up. The postcode areas, the addresses and the distances are real. Click a branch in the list, and the map zooms in on its area. “Niet ingedeeld” (not assigned) shows all postcodes that belong to no branch, with a list you can download as CSV. Zoom with the mouse wheel or the buttons, drag to move, and “Heel Nederland” (all of the Netherlands) takes you back to the overview. Switch on the postcode numbers to see which area has which number.

The table the client keeps

This is the table behind the map. It has two errors, like real tables do. Can you spot them yet?

1
CREATE TABLE dbo.Werkgebied (
2
    Vestiging   nvarchar(40) NOT NULL,
3
    PostcodeVan char(4)      NOT NULL,
4
    PostcodeTot char(4)      NOT NULL
5
);
6

7
INSERT INTO dbo.Werkgebied (Vestiging, PostcodeVan, PostcodeTot) VALUES
8
    (N'Amsterdam',  '1000', '1199'),
9
    (N'Almere',     '1200', '1399'),
10
    (N'Alkmaar',    '1400', '1999'),
11
    (N'Haarlem',    '2000', '2199'),
12
    (N'Den Haag',   '2200', '2740'),
13
    (N'Gouda',      '2741', '2900'),
14
    (N'Rotterdam',  '2901', '3399'),
15
    (N'Utrecht',    '3400', '3999'),
16
    (N'Breda',      '3990', '4999'),
17
    (N'Eindhoven',  '5000', '5999'),
18
    (N'Maastricht', '6000', '6499'),
19
    (N'Arnhem',     '6500', '7299'),
20
    (N'Enschede',   '7400', '7999'),
21
    (N'Zwolle',     '8000', '8999'),
22
    (N'Groningen',  '9000', '9999');

The postcodes are stored as char(4), not as a number. A postcode always has four digits, so '2741' < '2900' is also true as text. This way you compare text with text, and SQL Server can use an index on the postcode.

Joining with BETWEEN

The postcode areas come from the file Postcodegebieden from postcode.store: all 4,071 four-digit areas, with their shape from the CBS (Statistics Netherlands). The join is one BETWEEN:

1
SELECT g.Postcode, w.Vestiging
2
FROM dbo.Postcodegebieden AS g
3
LEFT JOIN dbo.Werkgebied AS w
4
       ON g.Postcode BETWEEN w.PostcodeVan AND w.PostcodeTot;

That gives 4,079 rows, while there are 4,071 areas. So eight areas come back twice. And 42 rows have no branch. Those are the two errors.

Check 1: postcodes without a branch

1
SELECT g.Postcode
2
FROM dbo.Postcodegebieden AS g
3
WHERE NOT EXISTS (
4
    SELECT 1
5
    FROM dbo.Werkgebied AS w
6
    WHERE g.Postcode BETWEEN w.PostcodeVan AND w.PostcodeTot
7
);

The result: 42 areas, from 7311 to 7399. That is Apeldoorn with Ugchelen, 97,909 addresses in total. Arnhem stops at 7299 and Enschede starts at 7400. Nobody has 7300 to 7399. In this table a customer from Apeldoorn belongs to no branch at all.

On the map this area has red hatching. Pick “Niet ingedeeld”, and you get the list of all 42 postcodes with their town and their number of addresses.

Check 2: ranges that overlap

Two ranges overlap when one starts before the other ends, and the other way around. Join the table to itself:

1
SELECT a.Vestiging, b.Vestiging,
2
       CASE WHEN a.PostcodeVan > b.PostcodeVan THEN a.PostcodeVan ELSE b.PostcodeVan END AS Van,
3
       CASE WHEN a.PostcodeTot < b.PostcodeTot THEN a.PostcodeTot ELSE b.PostcodeTot END AS Tot
4
FROM dbo.Werkgebied AS a
5
JOIN dbo.Werkgebied AS b
6
  ON a.Vestiging < b.Vestiging
7
 AND a.PostcodeVan <= b.PostcodeTot
8
 AND b.PostcodeVan <= a.PostcodeTot;

a.Vestiging < b.Vestiging makes sure you get each pair once, and not a branch paired with itself. The result is one row: Breda and Utrecht share 3990 to 3999. Those are eight areas in and around Houten, with 23,888 addresses. A customer who calls from there gets two branches.

Check 3: closer to another branch

A range follows the numbers, not the map. Postcode 3888 in Uddel falls in the range of Utrecht, but it lies on the Veluwe, 49.2 km from Utrecht. Postcode 6029 in Sterksel lies in Brabant and still belongs to Maastricht, at 56.3 km.

To count this you need a centre point per area, and a location per branch. The address file Zilver has RD coordinates in metres for every address. The average of all addresses in a postcode is the centre point. The distance is then just Pythagoras:

1
WITH midden AS (
2
    SELECT PostcodeNummers AS Postcode,
3
           COUNT(*) AS Adressen,
4
           AVG(TRY_CAST(XCoordinaat AS float)) AS X,
5
           AVG(TRY_CAST(YCoordinaat AS float)) AS Y
6
    FROM dbo.PostcodetabelZilver
7
    GROUP BY PostcodeNummers
8
)
9
SELECT g.Postcode, w.Vestiging AS VolgensTabel, dichtst.Vestiging AS Dichtstbij, m.Adressen
10
FROM dbo.Postcodegebieden AS g
11
JOIN midden AS m ON m.Postcode = g.Postcode
12
JOIN dbo.Werkgebied AS w ON g.Postcode BETWEEN w.PostcodeVan AND w.PostcodeTot
13
CROSS APPLY (
14
    SELECT TOP (1) v.Vestiging
15
    FROM dbo.Vestiging AS v              -- per vestiging een X en Y in RD
16
    ORDER BY SQUARE(m.X - v.X) + SQUARE(m.Y - v.Y)
17
) AS dichtst
18
WHERE dichtst.Vestiging <> w.Vestiging
19
  AND NOT EXISTS (                        -- de overlap uit controle 2 laten we hier weg
20
      SELECT 1 FROM dbo.Werkgebied AS w2
21
      WHERE g.Postcode BETWEEN w2.PostcodeVan AND w2.PostcodeTot
22
        AND w2.Vestiging <> w.Vestiging);

For the order you do not need the square root: the branch with the smallest square also has the shortest distance. The result is big. 1,077 areas with 2,104,292 addresses in total are closer to another branch than to the branch of their range. For Utrecht alone that is 249,287 addresses, and 125,358 of those are closer to Almere.

That does not have to be an error. A branch can drive further on purpose, for example because there is water in between. But you want to know. On the map, switch on “Dichter bij een andere vestiging” (closer to another branch), and the areas get stripes in the colour of the nearest branch.

The shape on the map

The file Postcodegebieden has a column Vorm per area: the border as WKT text, in longitude and latitude. SQL Server reads it with geography::STGeomFromText. There is one catch. For some of the shapes the ring runs the other way. SQL Server then sees everything outside the area as the area. Postcode 1011 in Amsterdam came out at 510 million km² that way: the whole earth.

The fix takes two steps:

1
WITH v AS (
2
    SELECT Postcode, geography::STGeomFromText(Vorm, 4326).MakeValid() AS g0
3
    FROM dbo.Postcodegebieden
4
)
5
SELECT Postcode,
6
       CASE WHEN g0.EnvelopeAngle() > 90 THEN g0.ReorientObject() ELSE g0 END AS Vorm
7
FROM v;

EnvelopeAngle is greater than 90 degrees when a shape covers more than half the globe. No postcode area is that big, so you flip it. After that all 4,071 areas together come out at 35,074 km², and postcode 1011 at 1.06 km².

For the map on this page the shape is simplified to 8% of the points, with mapshaper. Mapshaper simplifies a shared border once, so neighbours stay connected. The file for all areas is then 758 KB.

Does the sum add up?

The script that makes the data for the map adds up all addresses on the map and compares that with the address file. The map holds 9,590,124 addresses. The address file holds 663 more, in postcodes that the CBS does not know yet, such as 1116 with 389 addresses. That check was also broken on purpose once, and the script then stopped with an error.

Try it yourself

You need two files: Postcodegebieden for the shapes, and an address file with coordinates for the distances. Put your own table with ranges next to them. Run check 1 and 2 first: in a few seconds they find the errors that you otherwise only notice when a customer calls.

In Power BI you colour the areas with the column Vorm and your own branch as the legend. If you want single addresses on the map instead of areas, read postcodes on a map. To see how the Netherlands grew by construction year, look at the construction year map.