Reverse Google Maps Geocode with SQL Stored Procedure

  • All,

    I recently read the article about calling the google maps api web services from a SQL stored procedure and need to do something similar. I've taken the code from the Geocode article and modified it slightly but have not had any luck.

    I'm trying to pass a latitude and longitude to Google and have it return an address.

    Here is the stored procedure:

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Delcare variables

    DECLARE @api_key VARCHAR(100);

    DECLARE @url VARCHAR(MAX);

    -- Initialize variables

    SET @api_key = 'AIzaSyBawHTRvCHwZi26eA380UGNRX8LdzNzZ9M';

    SET @url = 'https://maps.googleapis.com/maps/api/geocode/xml?sensor=false&latlng=' + @latitude + ',' + @longitude + '&key=' + @api_key;

    PRINT 'URL to call ' + @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

    SET @XML = CAST(@Response AS XML)

    PRINT 'XML Result';

    PRINT CONVERT(NVARCHAR(MAX), @XML);

    PRINT '';

    SET @GPSLatitude = @XML.value('(/GeocodeResponse/result/geometry/location/lat) [1]', 'numeric(9,6)')

    SET @GPSLongitude = @XML.value('(/GeocodeResponse/result/geometry/location/lng) [1]', 'numeric(9,6)')

    When I run it it does look like it runs correctly but I get no data. The URL prints correctly and I can even cut and paste it into a web browser window and receive the XML data back.

    Any ideas?

    Thanks,

    Keith

  • Hi there -

    Did you maybe manage to resolve this issue? Struggling with the same thing...

    Thanks

    Estelle

  • What does @result contain? also, what is the @response. Not the converted value, but is there XML here?

  • Hi Steven -

    The @result contains NULL. I suspect it is because the GET request is to an HTTPS site? When I copy the URL into the browser, I do get a XML repsonse. Attached is my sp.

    Thanks

    Estelle

    Attachments:
    You must be logged in to view attached files.
  • Here is the code:

    CREATE PROCEDURE [dbo].[spGeocode]

    @GPSLatitude numeric(18, 6),

    @GPSLongitude numeric(18, 6)

    AS

    DECLARE

    @Address varchar(80),

    @City varchar(40),

    @State varchar(40),

    @Country varchar(40),

    @PostalCode varchar(20),

    @County varchar(40),

    @MapURL varchar(1024);

    SET NOCOUNT ON

    DECLARE @URL varchar(MAX)

    SET @URL = 'http://maps.google.com/maps/api/geocode/xml?latlng=' + CAST(@GPSLatitude AS varchar(20))+','+CAST(@GPSLongitude AS varchar(20))+'&key=x'

    SET @URL = REPLACE(@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 'MSXML.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

    select @HTTPStatus

    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

    SELECT @Response

    SET @XML = CAST(@Response AS XML)

    SET @City = @XML.value('(/GeocodeResponse/result/address_component[type="locality"]/long_name) [1]', 'varchar(40)')

    SET @State = @XML.value('(/GeocodeResponse/result/address_component[type="administrative_area_level_1"]/short_name) [1]', 'varchar(40)')

    SET @PostalCode = @XML.value('(/GeocodeResponse/result/address_component[type="postal_code"]/long_name) [1]', 'varchar(20)')

    SET @Country = @XML.value('(/GeocodeResponse/result/address_component[type="country"]/short_name) [1]', 'varchar(40)')

    SET @County = @XML.value('(/GeocodeResponse/result/address_component[type="administrative_area_level_2"]/short_name) [1]', 'varchar(40)')

    SET @Address =

    ISNULL(@XML.value('(/GeocodeResponse/result/address_component[type="street_number"]/long_name) [1]', 'varchar(40)'), '???') + ' ' +

    ISNULL(@XML.value('(/GeocodeResponse/result/address_component[type="route"]/long_name) [1]', 'varchar(40)'), '???')

    SET @MapURL = @URL

    SELECT

    --@GPSLatitude AS GPSLatitude,

    --@GPSLongitude AS GPSLongitude,

    @City AS City,

    @State AS [State],

    @PostalCode AS PostalCode,

    @Address AS [Address],

    @County AS County,

    @MapURL AS MapURL,

    @XML AS XMLResults

    --spGeoCode '-25.749088','28.146729'

    • This reply was modified 3 years, 11 months ago by  Stella86.
  • Thanks, https coudl be an issue. I'll to see if I Can get an API key and get this to work. Someone else was asking about this recently.

    As a side note, most of the articles I've seen that talk about this from SQL Server use SQLCLR to do this, not sp_oacreate.

  • Hi Steve -

    I managed to get this resolved by storing the @result value in a temporary table. Thanks for the side note on SQLCLR.

    I will share a copy of the code.

    Thanks

    Estelle

     

  • That's excellent. Glad it's working. If you'd like, I'd actually love a short article on this. Maybe show how it works, with a side note about the things that didn't work. If you wrap this in a few paragraphs of why and then explain the process for testing this, it would be interesting. You can submit a draft in the "Write for us" link in the upper right if you're interested. We'll help you get it looking good.

  • Hi Stella, Could you please share @result value in temporary table? Still i can't figure it out as i am new learner

     

  • Hi Everyone -

    Here is the latest copy of my code.

    Estelle


    /****** Object:  StoredProcedure [dbo].[spGeocode]    Script Date: 2020/05/28 10:39:12 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE [dbo].[spGeocode]

    @GPSLatitude numeric(18, 6),

    @GPSLongitude numeric(18, 6)

    AS

    IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml

    CREATE TABLE #xml ( yourXML XML )

    DECLARE

    @Country varchar(80),

    @Province varchar(80),

    @Region varchar(80),

    @Address varchar(200),

    @City varchar(40),

    @PostalCode varchar(20),

    @MapURL varchar(1024);

    SET NOCOUNT ON

    DECLARE @URL varchar(MAX)

    SET @URL = 'https://maps.google.com/maps/api/geocode/xml?latlng=' + CAST(@GPSLatitude AS varchar(20))+','+CAST(@GPSLongitude AS varchar(20))+'&key=YOURAPIKEY'

    SET @URL = REPLACE(@URL, ' ', '+')

    DECLARE @Response VARCHAR(MAX)

    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

    INSERT #xml ( yourXML )

    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

    SET @XML = (Select * from #XML)

    SET @City = @XML.value('(/GeocodeResponse/result/address_component[type="locality"]/long_name) [1]', 'varchar(40)')

    SET @PostalCode = @XML.value('(/GeocodeResponse/result/address_component[type="postal_code"]/long_name) [1]', 'varchar(20)')

    SET @Country = @XML.value('(/GeocodeResponse/result/address_component[type="country"]/long_name) [1]', 'varchar(40)')

    SET @Province = @XML.value('(/GeocodeResponse/result/address_component[type="administrative_area_level_1"]/long_name) [1]', 'varchar(40)')

    SET @Region = (CASE WHEN @XML.value('(/GeocodeResponse/result/address_component[type="sublocality_level_1"]/long_name) [1]', 'varchar(40)') IS NULL THEN @City END)

    SET @Address = @XML.value('(/GeocodeResponse/result/formatted_address) [2]', 'varchar(200)')

    SET @MapURL = @URL

    SELECT

    --@GPSLatitude AS GPSLatitude,

    --@GPSLongitude AS GPSLongitude,

    @Country AS Country,

    @City AS City,

    @Region As Region,

    @Province AS Province,

    @PostalCode AS PostalCode,

    @Address AS [Address],

    @MapURL AS MapURL,

    @XML AS XMLResults

    --spGeoCode '-34.049987', '24.922987'

    GO

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply