(SQL Server) Azure Maps Get Search Address

Get information for a given address.

For more information, see https://docs.microsoft.com/en-us/rest/api/maps/search/getsearchaddress

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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