In my experience developing Power BI dashboards for clients, I’ve come across many instances where the customer wants to use Power BI to map visualizations. The client may have zip code information entered into their database tables, but when these locations are plotted on a map, the surface area of the plot points are too large to show enough distinction from one point to the next. This becomes especially problematic when you’re trying to analyze objects such as buildings that are very close together.

In these cases, using latitude and longitude is a better way to create distinction between plot points. If you don’t already have the latitude and longitude of your location (commonly referred to as “geocodes” in the developer world) you’ll need to find them. This is where Google Maps Geocode API comes in handy.

What is Google Map’s Geocode API?

For those of you who don’t know, Google’s mapping API is one of the most popular application interfaces on the web. Whenever you’re programming a custom application, using Google’s API will help you convert zip codes, city names, or addresses to latitude and longitude points, which can then be used as highly targeted markers on a map.

How to Retrieve Latitude and Longitude via SQL Server 2016

In this blog, I want to show you how to take advantage of Google Maps API to call for the information you need using SQL Server 2016. This simple call enables you to retrieve latitude and longitude information without having to write custom C# or other languages to access the API.
 

Step 1: Declare your input/output variables

Declare all the variables for the address information you will be passing into Google and the information you will be pulling back out. Set the address for which you want to retrieve more data, or just validate.

Step 2: Build the web API URL

You will need to build the URL call to the API so that it can be submitted to Google. This will contain the location information that you have.

Step 3: Create the OAuth request

Create the variables required for the OAuth call to Google and submit the request. This will pass in the URL that was created. Then, submit the request and validate that that there were no errors with the submission.

Step 4: Capture the response

If there were no errors, you will get back an XML response from Google. You will need to parse the XML nodes back into the variables you declared in the earlier step.

Step 5: Review the results

Query the variables to see what Google has returned. The example above shows a single address being verified. However, there are better ways to operationalize these calls. Some ideas might be to wrap this in a stored proc, query tables in your environment, or make the address calls to Google and update the results in the source table. Another thing you will want to account for is handling timeouts or errors in the request and making sure to retry on those scenarios.