Hello people,
How are you?
In this post, I will demonstrate how to calculate the distance and time between two points, either 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 performing the GET request in the Google API, the data will be returned in XML format, which is handled 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 API, just with geometric and mathematical calculations (especially useful in large 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 2 points, while the Google API takes into account the routes and routes, according to the chosen mode of transport, which may have values closer to the real, of according to necessity, and quite different from geometric calculation.
Querying data using the CLR
To perform this query using CLR (C #), simply use the Stored Procedure stpWs_Requiringwhere 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, check out these 2 links:
Example of use:
| 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:
Querying Data Using OLE Automation
To perform this query using OLE Automation to perform the requests, simply use the object MSXML2.ServerXMLHTTP.
If you don't know or want to know more about OLE Automation in SQL Server, check out these 2 links:
Example of use:
| 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:
That's it folks!
I hope you enjoyed this post.
Hug!
SQL Server - How to calculate the distance between two points calculate distance between two 2 points (zip code 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 (zip code or address) zip postal code or address latitude longitude using the Google API
Very good content…
do you need to pass the parameter below?
Exec sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Authorization',
In google it is possible to calculate the distance of car, bus, etc and not only in a straight line. Is there any way, procedure to perform this calculation from the means of transport?
Show too Dirceu, helped me and a lot !!!
Thanks for the feedback, Thiago! If you have any questions, do not hesitate to ask.
Dirceu ball show, I will implement a new rule in the company.
Thanks for sharing!