SQL Server
SQL Server
Azure Maps Get Search Address
See more Azure Maps Examples
Get information for a given address.Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @http, 'Accept', 'application/json'
DECLARE @url nvarchar(4000)
SELECT @url = 'https://atlas.microsoft.com/search/address/{$format}?subscription-key=[subscription-key]&api-version=1.0&query={$query}'
DECLARE @success int
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'format', 'json'
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'query', '15127 NE 24th Street, Redmond, WA 98052'
DECLARE @strResp nvarchar(4000)
EXEC sp_OAMethod @http, 'QuickGetStr', @strResp OUT, @url
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
RETURN
END
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
PRINT 'Response Status Code: ' + @iTmp0
DECLARE @jsonResponse int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResponse OUT
EXEC sp_OAMethod @jsonResponse, 'Load', @success OUT, @strResp
EXEC sp_OASetProperty @jsonResponse, 'EmitCompact', 0
EXEC sp_OAMethod @jsonResponse, 'Emit', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonResponse
RETURN
END
-- Sample output...
-- (See the parsing code below..)
--
-- Use this online tool to generate parsing code from sample JSON:
-- Generate Parsing Code from JSON
-- {
-- "summary": {
-- "query": "15127 NE 24th Street, Redmond, WA 98052",
-- "queryType": "NON_NEAR",
-- "queryTime": 58,
-- "numResults": 1,
-- "offset": 0,
-- "totalResults": 1,
-- "fuzzyLevel": 1
-- },
-- "results": [
-- {
-- "type": "Point Address",
-- "id": "US/PAD/p0/19173426",
-- "score": 14.51,
-- "address": {
-- "streetNumber": "15127",
-- "streetName": "NE 24th St",
-- "municipalitySubdivision": "Redmond",
-- "municipality": "Redmond, Adelaide, Ames Lake, Avondale, Earlmount",
-- "countrySecondarySubdivision": "King",
-- "countryTertiarySubdivision": "Seattle East",
-- "countrySubdivision": "WA",
-- "postalCode": "98052",
-- "extendedPostalCode": "980525544",
-- "countryCode": "US",
-- "country": "United States Of America",
-- "countryCodeISO3": "USA",
-- "freeformAddress": "15127 NE 24th St, Redmond, WA 980525544",
-- "countrySubdivisionName": "Washington"
-- },
-- "position": {
-- "lat": 47.6308,
-- "lon": -122.1385
-- },
-- "viewport": {
-- "topLeftPoint": {
-- "lat": 47.6317,
-- "lon": -122.13983
-- },
-- "btmRightPoint": {
-- "lat": 47.6299,
-- "lon": -122.13717
-- }
-- },
-- "entryPoints": [
-- {
-- "type": "main",
-- "position": {
-- "lat": 47.6315,
-- "lon": -122.13852
-- }
-- }
-- ]
-- }
-- ]
-- }
--
DECLARE @summaryQuery nvarchar(4000)
DECLARE @summaryQueryType nvarchar(4000)
DECLARE @summaryQueryTime int
DECLARE @summaryNumResults int
DECLARE @summaryOffset int
DECLARE @summaryTotalResults int
DECLARE @summaryFuzzyLevel int
DECLARE @i int
DECLARE @count_i int
DECLARE @v_type nvarchar(4000)
DECLARE @id nvarchar(4000)
DECLARE @score nvarchar(4000)
DECLARE @addressStreetNumber nvarchar(4000)
DECLARE @addressStreetName nvarchar(4000)
DECLARE @addressMunicipalitySubdivision nvarchar(4000)
DECLARE @addressMunicipality nvarchar(4000)
DECLARE @addressCountrySecondarySubdivision nvarchar(4000)
DECLARE @addressCountryTertiarySubdivision nvarchar(4000)
DECLARE @addressCountrySubdivision nvarchar(4000)
DECLARE @addressPostalCode nvarchar(4000)
DECLARE @addressExtendedPostalCode nvarchar(4000)
DECLARE @addressCountryCode nvarchar(4000)
DECLARE @addressCountry nvarchar(4000)
DECLARE @addressCountryCodeISO3 nvarchar(4000)
DECLARE @addressFreeformAddress nvarchar(4000)
DECLARE @addressCountrySubdivisionName nvarchar(4000)
DECLARE @positionLat nvarchar(4000)
DECLARE @positionLon nvarchar(4000)
DECLARE @viewportTopLeftPointLat nvarchar(4000)
DECLARE @viewportTopLeftPointLon nvarchar(4000)
DECLARE @viewportBtmRightPointLat nvarchar(4000)
DECLARE @viewportBtmRightPointLon nvarchar(4000)
DECLARE @j int
DECLARE @count_j int
EXEC sp_OAMethod @jsonResponse, 'StringOf', @summaryQuery OUT, 'summary.query'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @summaryQueryType OUT, 'summary.queryType'
EXEC sp_OAMethod @jsonResponse, 'IntOf', @summaryQueryTime OUT, 'summary.queryTime'
EXEC sp_OAMethod @jsonResponse, 'IntOf', @summaryNumResults OUT, 'summary.numResults'
EXEC sp_OAMethod @jsonResponse, 'IntOf', @summaryOffset OUT, 'summary.offset'
EXEC sp_OAMethod @jsonResponse, 'IntOf', @summaryTotalResults OUT, 'summary.totalResults'
EXEC sp_OAMethod @jsonResponse, 'IntOf', @summaryFuzzyLevel OUT, 'summary.fuzzyLevel'
SELECT @i = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_i OUT, 'results'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'I', @i
EXEC sp_OAMethod @jsonResponse, 'StringOf', @v_type OUT, 'results[i].type'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @id OUT, 'results[i].id'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @score OUT, 'results[i].score'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @addressStreetNumber OUT, 'results[i].address.streetNumber'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @addressStreetName OUT, 'results[i].address.streetName'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @addressMunicipalitySubdivision OUT, 'results[i].address.municipalitySubdivision'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @addressMunicipality OUT, 'results[i].address.municipality'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @addressCountrySecondarySubdivision OUT, 'results[i].address.countrySecondarySubdivision'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @addressCountryTertiarySubdivision OUT, 'results[i].address.countryTertiarySubdivision'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @addressCountrySubdivision OUT, 'results[i].address.countrySubdivision'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @addressPostalCode OUT, 'results[i].address.postalCode'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @addressExtendedPostalCode OUT, 'results[i].address.extendedPostalCode'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @addressCountryCode OUT, 'results[i].address.countryCode'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @addressCountry OUT, 'results[i].address.country'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @addressCountryCodeISO3 OUT, 'results[i].address.countryCodeISO3'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @addressFreeformAddress OUT, 'results[i].address.freeformAddress'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @addressCountrySubdivisionName OUT, 'results[i].address.countrySubdivisionName'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @positionLat OUT, 'results[i].position.lat'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @positionLon OUT, 'results[i].position.lon'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @viewportTopLeftPointLat OUT, 'results[i].viewport.topLeftPoint.lat'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @viewportTopLeftPointLon OUT, 'results[i].viewport.topLeftPoint.lon'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @viewportBtmRightPointLat OUT, 'results[i].viewport.btmRightPoint.lat'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @viewportBtmRightPointLon OUT, 'results[i].viewport.btmRightPoint.lon'
SELECT @j = 0
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_j OUT, 'results[i].entryPoints'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'J', @j
EXEC sp_OAMethod @jsonResponse, 'StringOf', @v_type OUT, 'results[i].entryPoints[j].type'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @positionLat OUT, 'results[i].entryPoints[j].position.lat'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @positionLon OUT, 'results[i].entryPoints[j].position.lon'
SELECT @j = @j + 1
END
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonResponse
END
GO