Werkgebieden per vestiging op de postcodekaart: wie bedient welke postcode?

Veel bedrijven met vestigingen houden een tabel bij: vestiging Gouda doet postcode 2741 tot en met 2900, Rotterdam 2901 tot en met 3399. Zo’n tabel ziet er in Excel prima uit. Op de kaart zie je pas wat hij echt doet. Klik op een vestiging.

De vestigingen zijn verzonnen. De postcodegebieden, de adressen en de afstanden zijn echt. Klik op een vestiging in de lijst, en de kaart zoomt in op haar gebied. “Niet ingedeeld” toont alle postcodes die bij geen enkele vestiging horen, met een lijst die je als CSV downloadt. Zoom met het muiswiel of de knoppen, sleep om te schuiven, en “Heel Nederland” brengt je terug naar het overzicht. Zet de postcodenummers aan om te zien welk gebied welk nummer heeft.

De tabel die de klant bijhoudt

Dit is de tabel achter de kaart. Er zitten twee fouten in, zoals in echte tabellen. Vind je ze al?

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');

De postcodes staan als char(4), niet als getal. Een postcode heeft altijd vier cijfers, dus '2741' < '2900' klopt als tekst ook. Zo vergelijk je tekst met tekst, en kan SQL Server een index op de postcode gebruiken.

Koppelen met BETWEEN

De postcodegebieden komen uit het bestand Postcodegebieden van postcode.store: alle 4.071 gebieden van vier cijfers, met hun vorm van het CBS. De koppeling is één 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;

Dat geeft 4.079 rijen, terwijl er 4.071 gebieden zijn. Acht gebieden komen dus twee keer terug. En 42 rijen hebben geen vestiging. Dat zijn de twee fouten.

Controle 1: postcodes zonder vestiging

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
);

Het resultaat: 42 gebieden, van 7311 tot en met 7399. Dat is Apeldoorn met Ugchelen, samen 97.909 adressen. Arnhem stopt bij 7299 en Enschede begint bij 7400. Niemand heeft 7300 tot en met 7399. Een klant uit Apeldoorn valt in deze tabel bij geen enkele vestiging.

Op de kaart staat dit gebied rood gearceerd. Kies “Niet ingedeeld”, en je krijgt de lijst van alle 42 postcodes met hun plaats en hun aantal adressen.

Controle 2: reeksen die overlappen

Twee reeksen overlappen als de ene begint voordat de andere ophoudt, en andersom. Koppel de tabel aan zichzelf:

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 zorgt dat je elk paar één keer krijgt, en niet een vestiging met zichzelf. Het resultaat is één rij: Breda en Utrecht delen 3990 tot en met 3999. Dat zijn acht gebieden in Houten en omgeving, met 23.888 adressen. Wie daar belt, krijgt twee vestigingen.

Controle 3: dichter bij een andere vestiging

Een reeks volgt de nummers, niet de kaart. Postcode 3888 in Uddel valt in de reeks van Utrecht, maar ligt op de Veluwe, 49,2 km van Utrecht. Postcode 6029 in Sterksel ligt in Brabant en valt toch bij Maastricht, op 56,3 km.

Om dat te tellen heb je per gebied een middelpunt nodig, en per vestiging een plek. Het adressenbestand Zilver heeft voor elk adres RD-coördinaten in meters. Het gemiddelde van alle adressen in een postcode is het middelpunt. De afstand is dan gewoon 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);

Voor de volgorde is de wortel niet nodig: wie het kleinste kwadraat heeft, heeft ook de kortste afstand. Het resultaat is groot. 1.077 gebieden met samen 2.104.292 adressen liggen dichter bij een andere vestiging dan bij die van hun reeks. Bij Utrecht alleen al zijn het 249.287 adressen, en 125.358 daarvan liggen dichter bij Almere.

Dat hoeft geen fout te zijn. Een vestiging kan bewust verder rijden, bijvoorbeeld omdat het water ertussen ligt. Maar je wilt het weten. Zet op de kaart “Dichter bij een andere vestiging” aan, en de gebieden krijgen strepen in de kleur van de vestiging die het dichtstbij is.

De vorm op de kaart

Het bestand Postcodegebieden heeft per gebied een kolom Vorm: de grens als tekst in WKT, in lengte- en breedtegraden. SQL Server leest die met geography::STGeomFromText. Daar zit één val in. Bij een deel van de vormen loopt de ring de andere kant op. SQL Server ziet dan alles buiten het gebied als het gebied. Postcode 1011 in Amsterdam kwam zo uit op 510 miljoen km²: de hele aarde.

De oplossing is twee stappen:

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 groter dan 90 graden als een vorm meer dan een halve wereldbol beslaat. Geen postcodegebied is zo groot, dus dan draai je hem om. Daarna komen alle 4.071 gebieden samen uit op 35.074 km², en postcode 1011 op 1,06 km².

Voor de kaart op deze pagina is de vorm vereenvoudigd tot 8% van de punten, met mapshaper. Mapshaper vereenvoudigt een gedeelde grens één keer, dus buren blijven op elkaar aansluiten. Het bestand voor alle gebieden is daarna 758 KB.

Klopt de som?

Het script dat de data voor de kaart maakt, telt alle adressen op de kaart op en vergelijkt dat met het adressenbestand. Op de kaart staan 9.590.124 adressen. In het adressenbestand staan er 663 meer, in postcodes die het CBS nog niet kent, zoals 1116 met 389 adressen. Ook die controle is één keer bewust kapotgemaakt, en toen stopte het script met een fout.

Zelf proberen

Je hebt twee bestanden nodig: Postcodegebieden voor de vormen, en een adressenbestand met coördinaten voor de afstanden. Je eigen tabel met reeksen zet je ernaast. Draai eerst controle 1 en 2: die vinden in een paar seconden de fouten die anders pas opvallen als een klant belt.

In Power BI kleur je de gebieden met de kolom Vorm en je eigen vestiging als legenda. Wil je losse adressen op de kaart in plaats van gebieden, lees dan postcodes op een kaart. Hoe Nederland per bouwjaar is gegroeid, zie je op de bouwjaarkaart.