Hi John,
OK, i have a generic stored procedure that calls the API and returns XML. You can pass in either a Latitude/Longitude or an Address for a reverse lookup, the call to google is not that different. In some cases i have a customer address, in others i have GPS data from where our vehicles stopped.
This could be done outside of SQL using powershell or similar, but i used SQL as i wanted it to be inside SQL and that’s the environment i’m most comfortable in. You will need to enable sp_OACreate etc and check permissions… also we don’t use a proxy server… good luck if you do…
I have an error output table… I don’t THROW or RAISERROR i just want to continue on, the error table allows me to see what actually failed.
CREATE TABLE MetaData.Errors(
[ID] [int] IDENTITY(1,1) PRIMARY KEY,
[InsertDate] [datetime] NULL,
[ControlName] [varchar](100) NULL,
[Parameter1] [varchar](100) NULL,
[Parameter2] [varchar](100) NULL,
[Parameter3] [varchar](100) NULL,
[Parameter4] [varchar](100) NULL,
[Parameter5] [varchar](100) NULL,
[Parameter6] [varchar](100) NULL,
[ErrorMessage] [varchar](500) NULL,
[Output1] [varchar](max) NULL,
[Output2] [varchar](max) NULL,
[Output3] [varchar](max) NULL,
[Output4] [varchar](max) NULL,
[Output5] [varchar](max) NULL,
[Output6] [varchar](max) NULL,
[Output7] [varchar](max) NULL
)
Stored Procedure:
CREATE PROCEDURE [dbo].[usp_GeoCode]
@Type INT = 1,
@Latitude VARCHAR(40) = '',
@Longitude VARCHAR(40) = '',
@Address VARCHAR(500) = '',
@URL VARCHAR(800) OUTPUT,
@XML XML OUTPUT
AS
BEGIN
BEGIN TRY
DECLARE @Response VARCHAR(MAX), @Obj INT, @Result INT, @HTTPStatus INT, @ErrorMsg VARCHAR(MAX);
DECLARE @Key VARCHAR(100) = 'ABCDEFGHIJKLMNOP';
DROP TABLE IF EXISTS #xml
CREATE TABLE #xml ( yourXML XML )
IF @Type = 1
SET @URL = 'https://maps.google.com/maps/api/geocode/xml?sensor=false&latlng=' + @Latitude + ',' + @Longitude + '&key=' + @Key
IF @Type = 2
BEGIN
SET @Address = REPLACE(@Address,CHAR(13) + CHAR(10),' ')
SET @Address = REPLACE(@Address,'Cnr', ' Corner ')
SET @Address = REPLACE(@Address,'&', ' and ')
SET @Address = REPLACE(@Address,' ', '+')
SET @URL = 'https://maps.google.com/maps/api/geocode/xml?sensor=false&address=' + @Address + '&key=' + @Key
END
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 INTO #xml
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'
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 usp_geocode: ' + ISNULL(@ErrorMsg, 'HTTP result is: ' + CAST(@HTTPStatus AS varchar(10)))
RAISERROR(@ErrorMsg, 16, 1, @HTTPStatus)
RETURN
END
SELECT @XML = yourXML FROM #XML
END TRY
BEGIN CATCH
DECLARE @ErrorMessage VARCHAR(MAX), @ProcName VARCHAR(100)
SET @ErrorMessage = 'Failure: ' + ERROR_MESSAGE()
SET @ProcName = (SELECT SCHEMA_NAME(schema_id) + '.' + name FROM sys.objects WHERE object_id = @@PROCID)
INSERT INTO MetaData.Errors
(InsertDate,ControlName,ErrorMessage,Parameter1,Parameter2,Parameter3,Parameter4,Output1,Output2,Output3)
SELECT GETDATE(), @ProcName, @ErrorMessage, @Type, @Latitude, @Longitude, @Address, CAST(@XML AS VARCHAR(MAX)), @ErrorMsg, @HTTPStatus
END CATCH
END
There is the call of the Stored Procedure… This would be inside a cursor, or while loop.
EXEC usp_GeoCode @Type = 1, @Latitude = @Latitude, @Longitude = @Longitude, @URL = @URL OUTPUT, @XML = @XML OUTPUT
or
EXEC usp_GeoCode @Type = 2, @Address = @SourceAddress, @URL = @URL OUTPUT, @XML = @XML OUTPUT
The processing of the XML output… Note that the XML contains lots of info and i have only pulled the fields that i wanted. Also in the Cursor or while loop.
UPDATE a
SET a.URL = @URL,
a.XMLData = @XML,
a.FullAddress = @XML.value('(/GeocodeResponse/result/formatted_address) [1]', 'varchar(500)'),
a.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)'), '???'),
a.Suburb = @XML.value('(/GeocodeResponse/result/address_component[type="locality"]/long_name) [1]', 'varchar(40)'),
a.State = @XML.value('(/GeocodeResponse/result/address_component[type="administrative_area_level_1"]/short_name) [1]', 'varchar(40)'),
a.PostalCode = @XML.value('(/GeocodeResponse/result/address_component[type="postal_code"]/long_name) [1]', 'varchar(20)'),
a.Country = @XML.value('(/GeocodeResponse/result/address_component[type="country"]/long_name) [1]', 'varchar(30)'),
a.LocationType = @XML.value('(/GeocodeResponse/result/geometry/location_type) [1]', 'varchar(40)'),
a.Latitude = @XML.value('(/GeocodeResponse/result/geometry/location/lat) [1]', 'varchar(40)'),
a.Longitude = @XML.value('(/GeocodeResponse/result/geometry/location/lng) [1]', 'varchar(40)'),
a.Type1 = @XML.value('(/GeocodeResponse/result/type) [1]', 'varchar(100)'),
a.Type2 = @XML.value('(/GeocodeResponse/result/type) [2]', 'varchar(100)'),
a.Type3 = @XML.value('(/GeocodeResponse/result/type) [3]', 'varchar(100)'),
a.Type4 = @XML.value('(/GeocodeResponse/result/type) [4]', 'varchar(100)'),
a.Type5 = @XML.value('(/GeocodeResponse/result/type) [5]', 'varchar(100)'),
a.Type6 = @XML.value('(/GeocodeResponse/result/type) [6]', 'varchar(100)'),
a.PlaceID = @XML.value('(/GeocodeResponse/result/place_id) [1]', 'varchar(500)'),
a.PartialMatch = @XML.value('(/GeocodeResponse/result/partial_match) [1]', 'varchar(10)'),
a.UpdateDate = GETDATE()
FROM MyAddressTable a
WHERE ID = @ID
`
Use at your own risk…
I can tell you that 300,000 geocodes will cost roughly $1800 AUD