CODEWITHSUNDEEP
sql-servergoogle-mapssql-server-2016
Wil Salazar
Wil Salazar

Reputation: 1

Using SQL Server 2016 how can I retrieve Latitude and Longitude from Google platforms. Code not working

When I run the following SQL script to get the latitude and longtitude from Google maps I get odd results:

--Declare inpute/output variables
DECLARE @Address as varchar(100),
    @City as varchar(25),
    @State as varchar(2),
    @PostalCode as varchar(10),
    @Country varchar(40),
    @County varchar(40),
    @GPSLatitude numeric(18,9),
    @GPSLongitude numeric(18,9),
    @MapURL varchar(1024)

SET @Address = '333 W 35th St'
SET @City = 'Chicago'
SET @State = 'IL'

--Build the web API URL
DECLARE @URL varchar(MAX)
SET @URL = 'http://maps.google.com/maps/api/geocode/xml?sensor=false&address=' +
    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 @PostalCode IS NOT NULL THEN ', ' + @PostalCode ELSE '' END +
    CASE WHEN @Country IS NOT NULL THEN ', ' + @Country ELSE '' END

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

--Create the OAuth request
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

--Capture the response
SET @XML = CAST(@Response AS XML)

SET @GPSLatitude = @XML.value('(/GeocodeResponse/result/geometry/location/lat) [1]', 'numeric(18,9)')
SET @GPSLongitude = @XML.value('(/GeocodeResponse/result/geometry/location/lng) [1]', 'numeric(18,9)')
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 = 'http://maps.google.com/maps?f=q&h1=en&q=' + CAST(@GPSLatitude AS varchar(20)) + '+' + CAST(@GPSLongitude AS varchar(20))

--Review the results
SELECT @Address,
    @City,
    @State,
    @PostalCode,
    @Country,
    @County,
    @GPSLatitude,
    @GPSLongitude,
    @MapURL

The results set returned is:

??? ??? NULL NULL NULL NULL NULL NULL NULL NULL

Why am getting this response?

Upvotes: 0

Views: 1843

Answers (1)

strickt01
strickt01

Reputation: 4048

The reason you are getting the NULL response is that the XML you are getting back from Google is as follows:

<?xml version="1.0"?>
<GeocodeResponse>
<status>REQUEST_DENIED</status>
<error_message>You must use an API key to authenticate each request to Google Maps Platform APIs. For additional information, please refer to http://g.co/dev/maps-no-account</error_message>
</GeocodeResponse>

i.e. you are not passing them the API key.

You can check the response by just running:

select @Response

Try:

SET @URL = 'http://maps.google.com/maps/api/geocode/xml?key=YOUR_API_KEY&sensor=false&address=' +
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 @PostalCode IS NOT NULL THEN ', ' + @PostalCode ELSE '' END +
CASE WHEN @Country IS NOT NULL THEN ', ' + @Country ELSE '' END

Upvotes: 1

Related Questions