MS SQL: How to calculate distance using Google Maps API?

How to calculate distance between two addresses?

This article will show how to calculate the distance beween two addresses using Google Maps API. Blog.

Lists (numbered)

  1. Google Maps Distance Matrix API
  2. Stored Procedure
  3. Note

Google Maps Distance Matrix API:

I was looking to an easy way to calculate the distance between two addresses using SQL and Google Maps Distance Matrix API and I found this post from stackoverflow very useful.

Stored Procedure:

Here is how I use the Google Maps Distance Matrix API:

CREATE PROCEDURE [dbo].[CalculateDistance] ( @ToAddress NVARCHAR(100) = '', @FromAddress NVARCHAR(100) = '', @DistanceistanceInKm FLOAT OUTPUT ) AS

BEGIN

DECLARE @Object INT DECLARE @ResponseonseText NVARCHAR(4000) DECLARE @StatuserviceUrl NVARCHAR(500)

SET @StatuserviceUrl = 'http://maps.googleapis.com/maps/api/distancematrix/xml?origins=' + @ToAddress + '&destinations=' + @FromAddress +'&mode=driving&language=en-EN&units=metric;'

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; EXEC sp_OAMethod @Object, 'open', NULL, 'get', @StatuserviceUrl, 'false' EXEC sp_OAMethod @Object, 'send' EXEC sp_OAMethod @Object, 'responseText', @ResponseonseText OUTPUT

DECLARE @Response XML

SET @Response = CAST(CAST(@ResponseonseText AS NVARCHAR(MAX)) AS XML)

DECLARE @Status NVARCHAR(20) DECLARE @Distance NVARCHAR(20)

SET @Status = @Response.value('(DistanceMatrixResponse/row/element/status)[1]', 'NVARCHAR(20)')

IF(@Status = 'ZERO_RESULTS') SET @Distance = NULL ELSE SET @Distance = @Response.value('(DistanceMatrixResponse/row/element/distance/value)[1]', 'NVARCHAR(20)')

SET @DistanceistanceInKm = ROUND(CAST(@Distance AS FLOAT) / 1000, 1)

PRINT @DistanceistanceInKm

END

Comments

After you started using Google Maps Distance Matrix API, you need to remmember that you have a limit of 120 transactions for each 120 seconds sent to the webservice, so will be 1 transaction per second. I needed to limit my batches to not get errors from the Google web service.