Hey guys,
How are you?

In this post, I will demonstrate how to calculate the distance and time between two points, whether by entering the zip code, address or latitude and longitude. To meet this need, I will use the Google Maps API, which will perform the calculations, and the CLR (C#) or OLE Automation to perform Web requests to query and return the data.

After making the GET request to the Google API, the data will be returned in XML format, which is treated and manipulated by the native VALUE SQL Server function. To learn more about handling strings and XML files, see the post SQL Server – How to read, import and export data from XML files.

If you want to use a solution without using an API, just with geometric and mathematical calculations (especially useful in large volumes of data), see how in the article SQL Server – How to calculate the distance between two locations using latitude and longitude (without API).

It is important to note that the calculation without API considers a straight line between the 2 points, while the Google API takes into account routes and routes, according to the chosen means of transport, which can present values ​​closer to the real ones, according to the need, and very different from the geometric calculation.

Querying data using the CLR

To perform this query using the CLR (C#), simply use the Stored Procedure stpWs_Requisicao, where the source code is available in the post Performing POST and GET requests using CLR (C#) in SQL Server.

If you don't know or want to know more about SQLCLR (C#) in SQL Server, see these 2 links:

Example of use:

DECLARE
    @Cep_Origem VARCHAR(MAX) = '29090370',
    @Cep_Destino VARCHAR(MAX) = '29101120',
    @Url VARCHAR(MAX),
    @Retorno VARCHAR(MAX),
    @Retorno_XML XML


SET @Url = 'http://maps.googleapis.com/maps/api/distancematrix/xml?origins=' + @Cep_Origem + '&destinations=' + @Cep_Destino + '&mode=driving&language=pt-BR&sensor=false'


EXEC CLR.dbo.stpWs_Requisicao
    @Ds_Url = @Url, -- nvarchar(max)
    @Ds_Metodo = N'GET', -- nvarchar(max)
    @Ds_Parametros = N'', -- nvarchar(max)
    @Ds_Codificacao = N'UTF-8', -- nvarchar(max)
    @Ds_Retorno_OUTPUT = @Retorno OUTPUT -- nvarchar(max)


SET @Retorno = REPLACE(@Retorno, '<?xml version="1.0" encoding="UTF-8"?>', '')
SET @Retorno_XML = CAST(@Retorno AS XML)


SELECT
    @Cep_Origem AS CEP_Origem,
    @Retorno_XML.value('(/DistanceMatrixResponse/origin_address)[1]', 'varchar(500)') AS Endereco_Origem,
    @Cep_Destino AS CEP_Destino,
    @Retorno_XML.value('(/DistanceMatrixResponse/destination_address)[1]', 'varchar(500)') AS Endereco_Destino,
    @Retorno_XML.value('(/DistanceMatrixResponse/row/element/duration/value)[1]', 'varchar(500)') AS Qt_Duracao_Segundos,
    @Retorno_XML.value('(/DistanceMatrixResponse/row/element/duration/text)[1]', 'varchar(500)') AS Qt_Duracao_Tempo,
    @Retorno_XML.value('(/DistanceMatrixResponse/row/element/distance/value)[1]', 'varchar(500)') AS Qt_Distancia_Metros,
    @Retorno_XML.value('(/DistanceMatrixResponse/row/element/distance/text)[1]', 'varchar(500)') AS Qt_Distancia_KM

Result:

Querying data using OLE Automation

To perform this query using OLE Automation to make the requests, simply use the object MSXML2.ServerXMLHTTP.

If you don't know or want to know more about OLE Automation in SQL Server, see these 2 links:

Example of use:

DECLARE
    @Cep_Origem VARCHAR(MAX) = '29090370',
    @Cep_Destino VARCHAR(MAX) = '29101120',
    @Modo VARCHAR(50) = 'driving', -- BICYCLING | DRIVING | TRANSIT | WALKING
    @Url VARCHAR(600),
    @Retorno VARCHAR(4000),
    @Retorno_XML XML,
    @obj INT


SET @Url = 'http://maps.googleapis.com/maps/api/distancematrix/xml?origins=' + @Cep_Origem + '&destinations=' + @Cep_Destino + '&mode=' + @Modo + '&language=pt-BR&sensor=false'


EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT
EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, FALSE
EXEC sys.sp_OAMethod @obj, 'send'
EXEC sys.sp_OAGetProperty @obj, 'responseText', @Retorno OUT
EXEC sys.sp_OADestroy @obj

SET @Retorno = REPLACE(@Retorno, '<?xml version="1.0" encoding="UTF-8"?>', '')
SET @Retorno_XML = CAST(@Retorno AS XML)

SELECT
    @Cep_Origem AS CEP_Origem,
    @Retorno_XML.value('(/DistanceMatrixResponse/origin_address)[1]', 'varchar(500)') AS Endereco_Origem,
    @Cep_Destino AS CEP_Destino,
    @Retorno_XML.value('(/DistanceMatrixResponse/destination_address)[1]', 'varchar(500)') AS Endereco_Destino,
    @Retorno_XML.value('(/DistanceMatrixResponse/row/element/duration/value)[1]', 'varchar(500)') AS Qt_Duracao_Segundos,
    @Retorno_XML.value('(/DistanceMatrixResponse/row/element/duration/text)[1]', 'varchar(500)') AS Qt_Duracao_Tempo,
    @Retorno_XML.value('(/DistanceMatrixResponse/row/element/distance/value)[1]', 'varchar(500)') AS Qt_Distancia_Metros,
    @Retorno_XML.value('(/DistanceMatrixResponse/row/element/distance/text)[1]', 'varchar(500)') AS Qt_Distancia_KM

Result:

That's it, folks!
I hope you liked this post.

Hug!

SQL Server – How to calculate the distance between two points calculate distance between two 2 points (CEP or address) zip postal code or address latitude longitude using the Google API

SQL Server – How to calculate the distance between two points calculate distance between two 2 points (CEP or address) zip postal code or address latitude longitude using the Google API