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 Tool.
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 PostNL 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 (postcode check PostNL)
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:
