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:
Usage example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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:
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:
Usage example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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:
And that's it, folks!
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
![SQL Server – The activated proc ‘[dbo].[sp_syspolicy_events_reader]’ running on queue ‘msdb.dbo.syspolicy_event_queue’](http://dirceuresende.com/wp-content/themes/boxcard/img/thumb-medium.png)
Portuguese 
English
Bom dia Dirceu. Vi que agora se faz necessário uma chave de autenticação consumirmos essa API. Teria como adicionarmos esse parâmetro na query?
Bom dia Dirceu. Vi que agora se faz necessário uma chave de autenticação consumirmos essa API. Teria como adicionarmos esse parâmetro na query?
Olá Dirceu. Estou tentando utilizar o método pelo CLR, porém o meu select só me traz as informações dos CEPS já informados nas variáveis. Ele não me retorna os valores do XML, as colunas vem como NULL
Muito bom o conteúdo…
precisa passar o parâmetro abaixo ?
Exec sys.sp_OAMethod @obj, ‘setRequestHeader’, NULL, ‘Authorization’,
no google, é possivel calcular a distancia de carro, de onibus, etc e nao somente em linha reta. Existe alguma forma, procedure de realizar esse calculo a partir do meio de transporte?
Show demais Dirceu , me ajudou e muito !!!
Obrigado pelo feedback, Thiago! Qualquer dúvida, é só falar.
show de bola Dirceu, vou implementar uma nova regra na empresa.
obrigado por compartilhar!