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:
| 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 |
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:
| 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 |
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
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!