SQL Server - How to calculate the distance between two points using the Google API (zip code, address or latitute and longitude)

Views: 2.287
Reading Time: 3 minutes

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:

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:

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