PostNL API aanroepen vanuit SQL of Power BI

Aan de hand van de “Adrescheck Nationaal” API van PostNL wordt beschreven hoe met SQL adressen gevalideerd en gecorrigeerd kunnen worden. Deze mogelijkheden zijn ook beschikbaar binnen tabel-gebaseerde platformen zoals Azure Data Factory of Power BI door te werken met bijvoorbeeld een in-memory tabel die in de “On Startup” trigger van een Invantive Cloud database de PostNL API aanroept. De gepresenteerde SQL statements werken met enige beperkingen ook met de gratis versies van SQL in het Invantive Query.

De beschreven aanpak kan gebruikt worden om buiten de ruim 70 ondersteunde platformen relatief eenvoudige API’s aan te roepen als bron voor datareplicatie of verwerkingsprocessen.

Mogelijkheden PostNL API

De PostNL API is een eenvoudige reeks van API’s om gegevens uit te wisselen met PostNL. Denk hierbij aan:

  • Shipment
  • Tracking
  • Adresvalidatie
  • Bulkpost

De API’s zijn allen gelijkend qua structuur en mogelijkheden. De API manager is een beheeromgeving.

Voorbereidingen PostNL API

Voor gebruik van de PostNL API is een API sleutel nodig. Die is online te verkrijgen en wordt normaliter binnen 24 uur toegezonden. Hij bestaat uit een productie sleutel en een sandbox sleutel, elk met mogelijk verschillende diensten. Een deel van de diensten is gratis.

Adrescheck Nationaal

De API “Adrescheck Nationaal” biedt onder andere de mogelijkheid om op basis van postcode en huisnummer het volledige adres op te vragen. De invoer is een JSON-bericht zoals:

{
    "PostalCode": "3734GG", 
    "HouseNumber": "3",
}

Door dit JSON-bericht op te sturen samen met de API-sleutel in de header apikey en content-type application/JSON komt een resultaat terug zoals:

[{"City": "DEN DOLDER", "PostalCode": "3734GG", "Street": "Paduaweg", "HouseNumber": 3, "FormattedAddress": ["Paduaweg 3", "3734GG DEN DOLDER"]}
, {"City": "DEN DOLDER", "PostalCode": "3734GG", "Street": "Paduaweg", "HouseNumber": 3, "Addition": "A", "FormattedAddress": ["Paduaweg 3 A", "3734GG DEN DOLDER"]}, {"City": "DEN DOLDER", "PostalCode": "3734GG", "Street": "Paduaweg", "HouseNumber": 3, "Addition": "B", "FormattedAddress": ["Paduaweg 3 B", "3734GG DEN DOLDER"]}
, {"City": "DEN DOLDER", "PostalCode": "3734GG", "Street": "Paduaweg", "HouseNumber": 3, "Addition": "C", "FormattedAddress": ["Paduaweg 3 C", "3734GG DEN DOLDER"]}, {"City": "DEN DOLDER", "PostalCode": "3734GG", "Street": "Paduaweg", "HouseNumber": 3, "Addition": "D", "FormattedAddress": ["Paduaweg 3 D", "3734GG DEN DOLDER"]}
...
]

Het resultaat bevat meerdere adressen, elk met een andere huisnummertoevoeging.

SQL PostNL API Aanroep

Vanuit Invantive SQL kan de PostNL API aangeroepen worden met httpdownload@DataDictionary. De API-sleutel en content type worden als header doorgegeven en de postcode en het huisnummer als payload als volgt via een POST:

select htp.contents_char*
from   httpdownload@datadictionary
       ( url => 'https://api.postnl.nl/address/national/v1/validate'
       , method => 'POST'
       , contentType => 'application/json'
       , headers => 'apikey=secret'
       , textPayload => '{ "PostalCode": "3734GG", "HouseNumber": "3" }'
       ) htp

Het antwoord ziet er uit zoals boven. Dit antwoord kan omgezet worden naar een tabel met de jsontable:

select dta.*
from   httpdownload@datadictionary
       ( url => 'https://api.postnl.nl/address/national/v1/validate'
       , method => 'POST'
       , contentType => 'application/json'
       , headers => 'apikey=NAvj0SEeQlRf7tU7aJ2UoB62gqhWPRJX'
       , textPayload => '{ "PostalCode": "3734GG", "HouseNumber": "3" }'
       ) htp
join   jsontable
       ( '[*]'
         passing htp.contents_char
         columns city             varchar2 path 'City'
         ,       postal_code      varchar2 path 'PostalCode'
         ,       street           varchar2 path 'Street'
         ,       addition         varchar2 path 'Addition'
         ,       house_number     varchar2 path 'HouseNumber'
         ,       formatted_line_0 varchar2 path 'FormattedAddress[0]'
         ,       formatted_line_1 varchar2 path 'FormattedAddress[1]'
       ) dta

Het resultaat ziet er dan ongeveer zo uit:

In- en uitvoer met Tabellen

Soms is het wenselijk om een lijst van te valideren postcodes te verwerken en die weg te schrijven naar een tabel. Dergelijke output kan ook verwerkt worden door bijvoorbeeld Power Query of bijvoorbeeld door in Invantive Control for Excel een Excel tabel (TABELNAAM@InvantiveControl) uit te lezen en de gevalideerde resultaten in een blok weer te retourneren in Excel.

Een voorbeeld hiervan is de volgende query die drie adressen valideert uit een CSV-tabel en wegschrijft naar een in-memory tabel output:

create or replace table output@inmemorystorage
as
select dta.*
from   csvtable
       ( passing '3734GG,3'
                 || '#3734AA,3'
                 || '#3734EB,3'
         row     delimiter '#' 
         column  delimiter ','
         columns postal_code   varchar2 position 1
         ,       house_number  varchar2 position 2
       ) crd
join   httpdownload@datadictionary
       ( url => 'https://api.postnl.nl/address/national/v1/validate'
       , method => 'POST'
       , contentType => 'application/json'
       , headers => 'apikey=secret'
       , textPayload => '{ "PostalCode": "' || crd.postal_code || '", "HouseNumber": "' || crd.house_number || '" }'
       ) htp
join   jsontable
       ( '[*]'
         passing htp.contents_char
         columns city             varchar2 path 'City'
         ,       postal_code      varchar2 path 'PostalCode'
         ,       street           varchar2 path 'Street'
         ,       addition         varchar2 path 'Addition'
         ,       house_number     varchar2 path 'HouseNumber'
         ,       formatted_line_0 varchar2 path 'FormattedAddress[0]'
         ,       formatted_line_1 varchar2 path 'FormattedAddress[1]'
       ) dta

Het verwerken duurt circa 500 ms per postcode/huisnummercombinatie. Het resultaat ziet er als volgt uit: