January 11, 2017 at 6:14 pm
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
August 5, 2019 at 4:26 pm
Hi there -
Did you maybe manage to resolve this issue? Struggling with the same thing...
Thanks
Estelle
August 5, 2019 at 7:28 pm
What does @result contain? also, what is the @response. Not the converted value, but is there XML here?
August 8, 2019 at 8:02 am
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'
August 8, 2019 at 3:15 pm
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.
August 8, 2019 at 4:52 pm
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
August 8, 2019 at 5:18 pm
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.
October 23, 2019 at 12:14 am
Hi Stella, Could you please share @result value in temporary table? Still i can't figure it out as i am new learner
May 28, 2020 at 9:03 am
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