How to Geocode Addresses in Microsoft SQL Server 2016 Utilizing Google Locations API

Hello Experts,

Here is the Ask:  How do I get the below code to run without utilizing the ‘sp_configure’ stored procedure in Microsoft SQL server 2016?  I have the ability to create new stored procedures, but I cannot use the stored procedures that natively exist in SQL Server.

Additional Context/Background:

I have about 200,000 addresses in a Microsoft SQL server that I need to geocode.  I am operating in a highly restricted environment that does not allow the use of unapproved applications.  The approval process for a new application that I might develop in Visual Studio to geocode the addresses utilizing Google’s Locations API would take about a year for approval.  


I do however have access to SQL Server Management Studio and the database itself.  Here is the kicker, in this highly restricted environment, I have the ability to create a stored procedure, but nearly all of the native stored procedures in SQL have been disabled due to security concerns.

Using the innerwebs, I created the below SQL code to geocodes my addresses.  It works like a champ in my test environment utilizing a Cursor in conjunction with the ‘Ole Automation Procedures’ stored procedure ‘sp_configure ' .  The problem I have is that in my production environment, the stored procedure ‘sp_configure’ is disabled.

I realize that I will probably be cursed up and down for using Cursors and ‘sp_configure’.  It wasn’t my first choice, I am just trying to play the hand I have been dealt in the best possible way that I know how.  At the end of the day, I have to run all of this natively within SQL.

/*
This T-SQL script is intended to geocode addresses nativly within SQL without the use of an external application.
The goal is generally designed for geocoding static (known in advance) addresses for placement of application content on a map; 
this service is not designed to respond in real time to user input. 
For dynamic geocoding (for example, within a user interface element), consult the documentation for the Maps JavaScript API client geocoder 
and/or the Google Play services Location APIs..
Ultimately we want to store the responses from google into a SQL table for future lookup and use with non-native SQL applications.
Sites used in developing this code
https://www.swc.com/blog/application-development/how-to-call-google-maps-geocode-api-from-sql-server-2016-for-your-power-bi-map-visualizations
https://www.sqlservercentral.com/articles/geocode-addresses-in-t-sql
https://www.youtube.com/watch?v=INw_KGjyfDw
https://www.youtube.com/watch?v=RHRjLd0bEaQ
https://www.codeproject.com/Articles/232452/How-to-Use-Update-Cursors-in-SQL-Server 
https://pradeep1210.wordpress.com/2010/09/01/%E2%80%9Cthe-cursor-is-read-only-the-statement-has-been-terminated%E2%80%9D-problem-with-update-cursors-in-sql-server/
https://forums.asp.net/t/1688780.aspx?Using+a+cursor+to+insert+into+a+table
*/


-- This query will let us know if 'Ole Automation Procedures' are installed.
/*
EXEC sp_configure 'Ole Automation Procedures';  
GO  
*/


-- This script will enable 'Ole Automation Procedures' if it is not currently enabled
/*
sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO 
*/


-- Query whether or not a user has access to the sp_OACreate stored procedure
/*
SELECT * 
FROM master.sys.database_permissions [dp] 
JOIN master.sys.system_objects [so] ON dp.major_id = so.object_id
JOIN master.sys.sysusers [usr] ON 
     usr.uid = dp.grantee_principal_id AND usr.name = 'DOMAIN\userid'
WHERE permission_name = 'EXECUTE' AND so.name = 'sp_OACreate'
*/


-- Script that grants users access to Ole Automation Procedures if userid does not already have access.
/*
use master
go

grant exec on sp_OACreate to [DOMAIN\userid]
grant exec on sp_OAGetErrorInfo to [DOMAIN\userid]
grant exec on sp_OAMethod to [DOMAIN\userid]
grant exec on sp_OAGetProperty to [DOMAIN\userid]
grant exec on sp_OADestroy to [DOMAIN\userid]

GO
*/


--This will create a table that will hold our GeoCode data.  
--It is assumed you already have addresses stored in a differient SQL table that you wish to geocode.
/*
CREATE TABLE [Database].[Schema].[GeoCodeAddress]
(
	[Input Address] varchar(100),
	[Input City] varchar(45),
	[Input State] varchar(2),
	[Input Zip] varchar(10),
	[Input Country] varchar(74),

	[Output Address] varchar(100),
	[Output City] varchar(25),
	[Output State] varchar(2),
	[Output Zip] varchar(10),
	[Output Zip Suffix] varchar(4),
	[Output Country] varchar(36),
	[Output County] varchar(40),
	[Output Latitude] numeric(18,9),
	[Output Longitude] numeric(18,9),


	[Status] varchar(20),
	[Type] varchar(40),
	[Formatted Address] varchar(200),
	[Street Number Long] varchar(10),
	[Street Number Short] varchar(10),
	[Route Long] varchar(40),
	[Route Short] varchar(40),
	[Neighborhood Long] varchar(40),
	[Neighborhood Short] varchar(40),
	[Locality Long] varchar(40),
	[Locality Short] varchar(40),
	[Admin Lv2 Long] varchar(40),
	[Admin Lv2 Short] varchar(40),
	[Admin Lv1 Long] varchar(40),
	[Admin Lv1 Short] varchar(40),
	[Country Long] varchar(36),
	[Country Short] varchar(2),
	[Postal Code Long] varchar(10),
	[Postal Code Short] varchar(10),
	[Postal Code Suffix Long] varchar(10),
	[Postal Code Suffix Short] varchar(10),
	[Location Latitude] numeric(18,9),
	[Location Longitude] numeric(18,9),
	[Location Type] varchar(40),
	[Viewport SW Latitude] numeric(18,9),
	[Viewport SW Longitude] numeric(18,9),
	[Viewport NE Latitude] numeric(18,9),
	[Viewport NE Longitude] numeric(18,9),
	[Bounds SW Latitude] numeric(18,9),
	[Bounds SW Longitude] numeric(18,9),
	[Bounds NE Latitude] numeric(18,9),
	[Bounds NE Longitude] numeric(18,9),
	[Place ID] varchar(100)
);
*/

/*
STEP 1: DECLARE YOUR INPUTE/OUTPUT VARIABLES
Declare all the variables for the address information you will be passing 
into Google and the information you will be pulling back out. 
Set the address for which you want to retrieve more data, or just validate.
*/
SET NOCOUNT ON;
Declare
	@Input_Address as varchar(100),
	@Input_City as varchar(45),
	@Input_State as varchar(2),
	@Input_PostalCode as varchar(10),
	@Input_Country as varchar(74),

	@Variable_Output_Address as varchar(100),
	@Output_City as varchar(25),
	@Output_State as varchar(2),
	@Output_PostalCode as varchar(10),
	@Output_Country as varchar(36),
	@Output_County as varchar(40),

	@status as varchar(20),
	@type as varchar(40),
	@formatted_address as varchar(200),
	@street_number_long as varchar(10),
	@street_number_short as varchar(10),
	@route_long as varchar(40),
	@route_short as varchar(40),
	@neighborhood_long as varchar(40),
	@neighborhood_short as varchar(40),
	@locality_long as varchar(40),
	@locality_short as varchar(40),
	@administrative_area_level_2_long as varchar(40),
	@administrative_area_level_2_short as varchar(40),
	@administrative_area_level_1_long as varchar(40),
	@administrative_area_level_1_short as varchar(40),
	@country_long as varchar(36),
	@country_short as varchar(2),
	@postal_code_long as varchar(10),
	@postal_code_short as varchar(10),
	@postal_code_suffix_long as varchar(10),
	@postal_code_suffix_short as varchar(10),
	@location_latitude as numeric(18,9),
	@location_longitude as numeric(18,9),
	@location_type as varchar(40),
	@viewport_southwest_latitude as numeric(18,9),
	@viewport_southwest_longitude as numeric(18,9),
	@viewport_northeast_latitude as numeric(18,9),
	@viewport_northeast_longitude as numeric(18,9),
	@bounds_southwest_latitude as numeric(18,9),
	@bounds_southwest_longitude as numeric(18,9),
	@bounds_northeast_latitude as numeric(18,9),
	@bounds_northeast_longitude as numeric(18,9),
	@place_id as varchar(100)


/*
STEP 1: SET UP THE CURSOR
Google returns the location results in XML format one address at a time.
Ideally, we would use a program to write the data into a SQL table, but 
this solution is designed to run natively in SQL processing one row at
a time without the use of an application.
*/

DECLARE GoogleGeocodeCursor CURSOR  -- Sets our cursor

FOR 
	SELECT -- Select address elements you want to pass to google for geocoding
        [ADDRESS], 
        [CITY],
		[STATE],
		[ZIP]
    FROM 
        [Database].[schema].[SourceTable];	 -- This will be the table which contains the addresses we wanto to geocode.
											 -- IF YOU INTEND TO USE THIS SCRIPT MULTIPLE TIMES:
											 -- You will likely want to create an unmatched view between your source table 
											 -- and destination table to prevent geocoding the same address multiple times.

 
OPEN GoogleGeocodeCursor

FETCH NEXT 
	FROM GoogleGeocodeCursor 
		INTO 
		@Input_Address,
		@Input_City,
		@Input_State,
		@Input_PostalCode

-- START CURSOR LOOP: This code will be conducted on each record sequentially.  Row by row.
WHILE @@FETCH_STATUS = 0
	BEGIN




/*
STEP 3: BUILD THE WEB API URL
You will need to build the URL call to the API so that it can be submitted to Google. 
This will contain the location information that you have.
*/

Declare @URL varchar(MAX)

Set @URL = 'https://maps.googleapis.com/maps/api/geocode/xml?address=' + 
	CASE WHEN @Input_Address IS NOT NULL THEN @Input_Address ELSE '' END +
	CASE WHEN @Input_City IS NOT NULL THEN ', ' + @Input_City ELSE '' END +
	CASE WHEN @Input_State IS NOT NULL THEN ', ' + @Input_State ELSE '' END +
	CASE WHEN @Input_PostalCode IS NOT NULL THEN ', ' + @Input_PostalCode ELSE '' END +
	CASE WHEN @Input_Country IS NOT NULL THEN ', ' + @Input_Country ELSE '' + 
/* add API Key Here after &key= */	'&key=AI205zaSyBtLx9CxsqIR2u5NWbPoUo' END

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

/*
STEP 4: CREATE THE OAUTH REQUEST
Create the variables required for the OAuth call to Google and submit the request. 
This will pass in the URL that was created. Then, submit the request and validate that that there were no errors with the submission.
*/

 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)

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

/*
STEP 5: CAPTURE THE RESPONSE
If there were no errors, you will get back an XML response from Google. 
You will need to parse the XML nodes back into the variables you declared in the earlier step.
*/

SET @XML = CAST(@Response AS XML)

 SET @status =					@XML.value('(/GeocodeResponse/status) [1]', 'varchar(20)')
 SET @type =					@XML.value('(/GeocodeResponse/result/type) [1]', 'varchar(40)')
 SET @formatted_address =		@XML.value('(/GeocodeResponse/result/formatted_address) [1]', 'varchar(200)')

 SET @street_number_long =			@XML.value('(/GeocodeResponse/result/address_component[type="street_number"]/long_name) [1]', 'varchar(10)') 
 SET @street_number_short =			@XML.value('(/GeocodeResponse/result/address_component[type="street_number"]/short_name) [1]', 'varchar(10)') 

 SET @route_long =					@XML.value('(/GeocodeResponse/result/address_component[type="route"]/long_name) [1]', 'varchar(45)') 
 SET @route_short =					@XML.value('(/GeocodeResponse/result/address_component[type="route"]/short_name) [1]', 'varchar(45)') 

 SET @neighborhood_long =			@XML.value('(/GeocodeResponse/result/address_component[type="neighborhood"]/long_name) [1]', 'varchar(40)')
 SET @neighborhood_short =			@XML.value('(/GeocodeResponse/result/address_component[type="neighborhood"]/short_name) [1]', 'varchar(40)')

 SET @locality_long =				@XML.value('(/GeocodeResponse/result/address_component[type="locality"]/long_name) [1]', 'varchar(45)')
 SET @locality_short =				@XML.value('(/GeocodeResponse/result/address_component[type="locality"]/short_name) [1]', 'varchar(45)')

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

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

 SET @country_long =				@XML.value('(/GeocodeResponse/result/address_component[type="country"]/long_name) [1]', 'varchar(36)')
 SET @country_short =				@XML.value('(/GeocodeResponse/result/address_component[type="country"]/short_name) [1]', 'varchar(2)')

 SET @postal_code_long =			@XML.value('(/GeocodeResponse/result/address_component[type="postal_code"]/long_name) [1]', 'varchar(10)')
 SET @postal_code_short =			@XML.value('(/GeocodeResponse/result/address_component[type="postal_code"]/short_name) [1]', 'varchar(10)')

 SET @postal_code_suffix_long =		@XML.value('(/GeocodeResponse/result/address_component[type="postal_code_suffix"]/long_name) [1]', 'varchar(10)')
 SET @postal_code_suffix_short =	@XML.value('(/GeocodeResponse/result/address_component[type="postal_code_suffix"]/short_name) [1]', 'varchar(10)')
  
 SET @location_latitude =			@XML.value('(/GeocodeResponse/result/geometry/location/lat) [1]', 'numeric(9,6)')
 SET @location_longitude =			@XML.value('(/GeocodeResponse/result/geometry/location/lng) [1]', 'numeric(9,6)')
 
 SET @location_type =				@XML.value('(/GeocodeResponse/result/geometry/location_type) [1]', 'varchar(40)')

 SET @viewport_southwest_latitude =	@XML.value('(/GeocodeResponse/result/geometry/viewport/southwest/lat) [1]', 'numeric(9,6)')
 SET @viewport_southwest_longitude =@XML.value('(/GeocodeResponse/result/geometry/viewport/southwest/lng) [1]', 'numeric(9,6)')

 SET @viewport_northeast_latitude =	@XML.value('(/GeocodeResponse/result/geometry/viewport/northeast/lat) [1]', 'numeric(9,6)')
 SET @viewport_northeast_longitude =@XML.value('(/GeocodeResponse/result/geometry/viewport/northeast/lng) [1]', 'numeric(9,6)')

 SET @bounds_southwest_latitude =	@XML.value('(/GeocodeResponse/result/geometry/bounds/southwest/lat) [1]', 'numeric(9,6)')
 SET @bounds_southwest_longitude =	@XML.value('(/GeocodeResponse/result/geometry/bounds/southwest/lng) [1]', 'numeric(9,6)')
 
 SET @bounds_northeast_latitude =	@XML.value('(/GeocodeResponse/result/geometry/bounds/northeast/lat) [1]', 'numeric(9,6)')
 SET @bounds_northeast_longitude =	@XML.value('(/GeocodeResponse/result/geometry/bounds/northeast/lng) [1]', 'numeric(9,6)')

 SET @place_id =					@XML.value('(/GeocodeResponse/result/place_id) [1]', 'varchar(100)')

 SET @Variable_Output_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"]/short_name) [1]', 'varchar(40)'), '???') 
 
/*
STEP 6: WRITE THE RESPONSE TO YOU LOOKUP TABLE
We are capturing each data element that Google sends back to us in a table for future lookups.
*/

		INSERT INTO [Database].[Schema].[GeoCodeAddress]	
			([Input Address], [Input City], [Input State], [Input Zip], [Input Country], [Output Address], 
			[Output City], [Output State], [Output Zip], [Output Zip Suffix], [Output County], [Output Country],
			[Output Latitude],[Output Longitude], [Status], [Type], [Formatted Address], [Street Number Long], 
			[Street Number Short], [Route Long], [Route Short], [Neighborhood Long], [Neighborhood Short], 
			[Locality Long], [Locality Short], [Admin Lv2 Long], [Admin Lv2 Short], [Admin Lv1 Long], [Admin Lv1 Short], 
			[Country Long], [Country Short], [Postal Code Long], [Postal Code Short], [Postal Code Suffix Long], 
			[Postal Code Suffix Short], [Location Latitude], [Location Longitude], [Location Type], [Viewport SW Latitude], 
			[Viewport SW Longitude], [Viewport NE Latitude], [Viewport NE Longitude], [Bounds SW Latitude], 
			[Bounds SW Longitude], [Bounds NE Latitude], [Bounds NE Longitude], [Place ID])
		VALUES 
			(@Input_Address, @Input_City, @Input_State, @Input_PostalCode, @Input_Country, @Variable_Output_Address, 
			@locality_long, @administrative_area_level_1_short, @postal_code_long, @postal_code_suffix_long, 
			@administrative_area_level_2_long, @country_long, @location_latitude, @location_longitude, @status, @type, 
			@formatted_address, @street_number_long, @street_number_short, @route_long, @route_short, @neighborhood_long, 
			@neighborhood_short, @locality_long, @locality_short, @administrative_area_level_2_long, @administrative_area_level_2_short, 
			@administrative_area_level_1_long, @administrative_area_level_1_short, @country_long, @country_short, @postal_code_long, 
			@postal_code_short, @postal_code_suffix_long, @postal_code_suffix_short, @location_latitude, @location_longitude, 
			@location_type, @viewport_southwest_latitude, @viewport_southwest_longitude, @viewport_northeast_latitude, 
			@viewport_northeast_longitude, @bounds_southwest_latitude, @bounds_southwest_longitude, @bounds_northeast_latitude, 
			@bounds_northeast_longitude, @place_id)

-- After we write the response for the first record, we will move onto the next record.

        FETCH NEXT FROM GoogleGeocodeCursor 
			INTO 
			@Input_Address,
			@Input_City,
			@Input_State,
			@Input_PostalCode
	END
-- END CURSOR LOOP: This code will be conducted on each record sequentially.  Row by row.

CLOSE GoogleGeocodeCursor;
DEALLOCATE GoogleGeocodeCursor;

Select all Open in new window