/*
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;
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE