Skip to main content
SPRINTEN

TOM TOM Gebruiken in SQL Server

Het importeren van adressen in je eigen databases kan een lastig klus zijn. Er zijn immers nogal wat varianten waarop een adres aangeleverd kan worden.  Zo zal de ene database keurig de straatnaam en het huisnummer, genormaliseerd, hebben opgeslagen terwijl de ander ook nog de toevoeging van het huisnummer in een aparte kolom aanlevert.

Met het onderstaande script laat ik zien hoe je, middels de TOM TOM API, kunt voorzien van een longitude en latitude, valideert, normaliseert en   ontleed.

Varianten

Zoals in de inleiding al is aangegeven zijn er verschillende varianten waarop de data kan worden aangeleverd.

Bijvoorbeeld :

Adres : Tingietersstraat 14, 8601WJ  Sneek

OF

Straat : Tingietersstraat 14

Postcode : 8601WJ

Plaats : Sneek

In beide gevallen wil ik het huisnummer en de straatnaam graag opgesplitst terug zien en mis ik ook de longitude en latitude die in de planning nodig is.

Nu is bovenstaande voorbeeld niet heel complex want ook het huisnummer met een toevoeging is redelijk eenvoudig te splitsen. Echter ik wil ook zeker weten dat de schijfwijze juist is en de posities aan het adres toevoegen.

ActiveX

Eerst moeten we ervoor zorgen dat ActiveX-ondersteuning is ingeschakeld in de database:

 

EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE
EXEC sp_configure ‘Ole Automation Procedures’, 1
RECONFIGURE

De opgeslagen procedure

CREATE PROCEDURE [dbo].[Usp_TomTomGeo]
@ADDRESS VARCHAR(80) = NULL OUTPUT,
@STREET VARCHAR(128) = NULL OUTPUT,
@HOMENUMBER VARCHAR(80) = NULL OUTPUT,
@HOMENUMBERADD VARCHAR(80) = NULL OUTPUT,
@CITY VARCHAR(40) = NULL OUTPUT,
@STATE VARCHAR(40) = NULL OUTPUT,
@COUNTRY VARCHAR(40) = NULL OUTPUT,
@ZIP VARCHAR(20) = NULL OUTPUT,
@COUNTY VARCHAR(40) = NULL OUTPUT,
@LON NUMERIC(9,6) = NULL OUTPUT,
@LAT NUMERIC(9,6) = NULL OUTPUT,
@URI VARCHAR(1024) = NULL OUTPUT,
@KEY VARCHAR(128)

AS
BEGIN
SET NOCOUNT ON

DECLARE @URL varchar(MAX)
SET @URL = ‘https://api.tomtom.com/search/2/geocode/’ +
CASE WHEN @ADDRESS IS NOT NULL THEN @ADDRESS ELSE ” END +
CASE WHEN @CITY IS NOT NULL THEN ‘, ‘ + @CITY ELSE ” END +
CASE WHEN @STATE IS NOT NULL THEN ‘, ‘ + @STATE ELSE ” END +
CASE WHEN @ZIP IS NOT NULL THEN ‘, ‘ + @ZIP ELSE ” END +
CASE WHEN @COUNTRY IS NOT NULL THEN ‘, ‘ + @COUNTRY ELSE ” END
SET @URL = @URL+’.xml’+’?key=’+@KEY+’&limit=1′
print @URL
DECLARE @RESPONSE VARCHAR(8000)
DECLARE @XML XML
DECLARE @OBJ INT
DECLARE @RESULT INT
DECLARE @HTTPSTATUS INT
DECLARE @ERRORMSG VARCHAR(MAX)

EXEC @RESULT = sp_OACreate ‘MSXML2.ServerXMLHttp’, @OBJ OUT

BEGIN TRY
EXEC @RESULT = sp_OAMethod @OBJ, ‘open’, NULL, ‘GET’, @URL, false
EXEC @RESULT = sp_OAMethod @OBJ, ‘setRequestHeader’, NULL, ‘Content-Type’, ‘application/x-www-form-urlencoded’
EXEC @RESULT = sp_OAMethod @OBJ, send, NULL, ”
EXEC @RESULT = sp_OAGetProperty @OBJ, ‘status’, @HTTPSTATUS OUT
EXEC @RESULT = sp_OAGetProperty @OBJ, ‘responseXML.xml’, @RESPONSE OUT
END TRY
BEGIN CATCH
SET @ERRORMSG = ERROR_MESSAGE()
END CATCH

EXEC @RESULT = sp_OADestroy @OBJ

IF (@ERRORMSG IS NOT NULL) OR (@HTTPSTATUS <> 200) BEGIN
SET @ERRORMSG = ‘Error in spGeocode: ‘ + ISNULL(@ERRORMSG, ‘HTTP result is: ‘ + CAST(@HTTPSTATUS AS varchar(10)))
RAISERROR(@ERRORMSG, 16, 1, @HTTPSTATUS)
RETURN
END
print @RESPONSE
SET @XML = CAST(@RESPONSE AS XML)

SET @STREET = @XML.value(‘(/response/results/item/address/streetName) [1]’, ‘nvarchar(128)’)
SET @HOMENUMBER = @XML.value(‘(/response/results/item/address/streetNumber) [1]’, ‘nvarchar(80)’)
SET @CITY = @XML.value(‘(/response/results/item/address/localName) [1]’, ‘nvarchar(80)’)
SET @ZIP = @XML.value(‘(/response/results/item/address/extendedPostalCode) [1]’, ‘nvarchar(80)’)
SET @STATE = @XML.value(‘(/response/results/item/address/countrySubdivision) [1]’, ‘nvarchar(80)’)
SET @COUNTRY = @XML.value(‘(/response/results/item/address/countryCode) [1]’, ‘nvarchar(80)’)
SET @COUNTY = @XML.value(‘(/response/results/item/address/municipality) [1]’, ‘nvarchar(80)’)
SET @LON = @XML.value(‘(/response/results/item/position/lon) [1]’, ‘nvarchar(80)’)
SET @LAT = @XML.value(‘(/response/results/item/position/lat) [1]’, ‘nvarchar(80)’)

SELECT
@STREET AS Street
,@HOMENUMBER AS Homenumber
,@CITY AS Residence
,@ZIP AS Zip
,@STATE AS State
,@COUNTRY AS Country
,@COUNTY AS County
,@LON AS Longitude
,@LAT As Latitude
END
GO

Testen van de procedure

Middels onderstaande script kan je de procedure testen. Kijk ook naar de validatie die TomTom uitvoert. De straatnaam was in het request verkeerd gespeld en in het resultaat juist weergegeven.

 

EXEC [dbo].[Usp_TomTomGeo] @ADDRESS = ‘Tingieterstraat 14, 8601WJ Sneek NL’, @KEY = N’Vul hier je eigen key in’