Olá pessoal,
Como vocês estão?

Neste post, vou demonstrar como calcular a distância e o tempo entre dois pontos, seja informando o CEP, Endereço ou latitude e longitude. Para atender a essa necessidade, vou utilizar a API do Google Maps, que vai realizar os cálculos e o CLR (C#) ou OLE Automation para realizar as requisições Web para consultar e retornar os dados.

Após realizar a requisição GET na API do Google, os dados serão retornados no formato XML, que são tratados e manipulados pela função nativa VALUE SQL Server. Para saber mais sobre tratamento de strings e arquivos XML, veja o post SQL Server – Como ler, importar e exportar dados de arquivos XML.

Caso você queira utilizar uma solução sem utilizar API, apenas com cálculos geométricos e matemáticos, (especialmente útil em grandes volumes de dados), veja como no artigo SQL Server – Como calcular a distância entre dois locais utilizando latitude e longitude (sem API).

É importante observar que o cálculo sem API considera uma linha reta entre os 2 pontos, enquanto a API do Google leva em consideração os trajetos e rotas, de acordo com o meio de transporte escolhido, o que pode apresentar valores mais próximos do reais, de acordo com a necessidade, e bem diferentes do cálculo geométrico.

Consultando os dados utilizando o CLR

Para realizar essa consulta utilizando o CLR (C#), basta utilizar a Stored Procedure stpWs_Requisicao, onde o código-fonte está disponível no post Realizando requisições POST e GET utilizando CLR (C#) no SQL Server.

Caso você não conheça ou queira conhecer mais sobre o SQLCLR (C#) no SQL Server, veja esses 2 links:

Exemplo de utilização:

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

Resultado:

Consultando os dados utilizando o OLE Automation

Para realizar essa consulta utilizando o OLE Automation para realizar as requisições, basta utilizar o objeto MSXML2.ServerXMLHTTP.

Caso você não conheça ou queira conhecer mais sobre o OLE Automation no SQL Server, veja esses 2 links:

Exemplo de utilização:

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

Resultado:

É isso aí, pessoal!
Espero que vocês tenham gostado desse post.

Abraço!

SQL Server – Como calcular a distância entre dois pontos calculate distance between two 2 points (CEP ou endereço) zip postal code or address latitude longitude utilizando a API do Google

SQL Server – Como calcular a distância entre dois pontos calculate distance between two 2 points (CEP ou endereço) zip postal code or address latitude longitude utilizando a API do Google