On this publish I clarify the way to use Google Maps APIs to retrieve helpful data out of Google Maps. The use case state of affairs could possibly be getting tackle, postal code, and so on. from current latitude and longitude values. The info could possibly be generated by any form of GPS monitoring gadget like your Garmin biking GPS pc, your Fitbit watch and so on. I do know you’ll be able to load your GPS monitoring information into athletic social networks to analyse your actions. However, if you wish to do some extra particular information analytics like during which space of the town you created extra energy throughout your biking actions then these web sites may not provide you with what you need at no cost.
As an example, you’ll be able to export your gadget information to CSV then import and append all CSV information right into a Energy BI mannequin and create superb analytical studies. Learn how to import your CSV information right into a Energy BI mannequin is out of scope of this text so I depart it to you for any additional investigations.
GPS monitoring units are creating plenty of information together with geographic coordinates which may be simply utilized in Energy BI. You possibly can merely put latitude and longitude on a Map visualisation and also you’re good to go.
You can even concatenate the latitude and longitude information and use it as Location in your Map visualisation.
This may be carried out from Question Editor in M language.
However, in some circumstances you want some extra geo-information like Nation, Metropolis, Put up Code and Road Deal with in a desk as properly. Otherwise you would possibly wish to use postal code in a slicer. On this article I present you the way to get all of those data out of Google Maps by passing current coordinates to Google Maps geocoding API.
To have the ability to observe this publish steps it’s essential:
- Have a Google account
- Activate the Google Maps Geocoding API
- Discover a desired API in Google Builders Information
- Use the API in Energy BI
- A GPS gadget information exported to CSV file
I assume you have already got an current Google account or you know the way to create a brand new one, so I leap straight into the following step which is activating geocoding API. To have the ability to begin utilizing the Google Maps Geocoding API it’s essential get an API key first.
- Scroll down and click on “Get A Key”
- This may navigate you to Google Builders Console
- Click on “Sure” then click on “Agree and proceed”
- Kind in a reputation then click on “Create”
You efficiently created an API key. We are going to use this key within the subsequent sections of this text.
Now that we’ve an API key we will use plenty of Google Geocoding APIs accessible right here. As you’ll be able to see there are many helpful APIs you should use to leverage your Energy BI mannequin. In my pattern I must retrieve Nation, Put up Code and Road Deal with out of current coordinates generated by my bike GPS pc. So I take advantage of Reverse Geocoding (Deal with Lookup). Here’s a pattern:
https://maps.googleapis.com/maps/api/geocode/json?latlng=-36.8484213,174.7457047&key=YOUR_API_KEY
For those who copy and paste the above API in your internet browser you’ll be able to see its output which is a JSON offering detailed details about the situation.
Notice: Don’t forget to repeat and paste your API key on the finish of the API.
The concept is to outline a perform in Energy BI then go latitude and longitude to the perform to get Nation, Postal Code and Road Deal with.
The way it works
- Open Energy BI Desktop
- Get Knowledge from Net
- Paste the API within the URL part then click on OK
- Stick to the default and click on “Join”
- This opens “Question Editor”
- Click on “Listing”
- Convert the checklist to desk by clicking “To Desk” button from “Rework” tab from the ribbon
- Click on on develop advanced column icon (
) to develop the desk then tick “formatted_address” and “sorts” columns then click on OK
- Increase the “sorts” column as properly
- Filter “sorts” column to indicate “Nation”, “Postal Code” and “Steer Deal with”
- We have to the columns as rows so we have to transpose the desk. So go to “remodel” tab and click on “Transpose”
- As you see the second row incorporates the column names. Click on “Reverse Rows” to indicate the second row first
- Click on “Use First Row As Headers”
- Rename the question then save the mannequin
Create a Operate from an Current Question
Up to now we loaded the geo-location data for only one coordinate. Now we have to flip the question to a perform in order that we will invoke it for all coordinates we’ve in our dataset.
A perform building is as under:
- Click on “Superior Editor”
- Change the code as per screenshot under
1– Operate inner identify
2 & 4 – Enter parameters
3 & 5 – Parameters’ information sorts
6 – Operate physique
7– Output
Notice: The identify that will probably be used to revoke the perform is the question identify which is “fn_GeoLocation” in our pattern.
- Proceed modifying the codes as under then click on “Performed”
- What we’ve carried out above is that we changed the static latitude and longitude with parameters. We used “&” to concatenate each side of the supply.
- As quickly as you click on “Performed” Energy BI detects the question as a perform
- To check the perform simply click on “Invoke” and enter latitude and longitude then click on OK
- Delete the invoked perform step
We efficiently outlined a question as a perform in order that we will invoke it anytime.
The following step is to load GPS information from CSV. I’m utilizing my bike pc information that I exported to CSV format earlier.
Obtain pattern CSV file right here.
- In Question Editor, click on “New Supply” from “Dwelling” tab then click on “CSV”
- Browse and open the CSV file
- Click on OK
- Click on “Add Customized Column” from “Add Column” tab and identify it “Geolocation”
- Enter the next method to invoke the fn_GeoLocation perform and click on OK
Notice: I used “Quantity.ToText” perform to transform latitude and longitude information sort from quantity to textual content as we outlined the perform parameters as textual content.
- You’ll get a “Data is required about information privateness” warning message. Click on “Proceed”
- Choose a desired privateness stage from the checklist then click on “Save”
Notice: Be taught extra about “Privateness ranges” right here.
- Scroll proper to search out the brand new added column
- Increase “Geolocation” column
Voila! We’ve got acquired all data wanted
- Click on “Shut & Apply” from “Dwelling” tab from the ribbon and wait till the information masses into the mannequin.
Notice: It would take someday to load information into the mannequin based mostly in your dataset measurement. That is due to sending coordinates to Google Maps row by row then loading the outcomes into the mannequin, so the method could possibly be gradual in case you have a big dataset. There are additionally some limitations utilized to the API utilization.
Now you’ll be able to simply use new columns for various functions like placing Nation or Postal Code in slicers, making a Location hierarchy and so forth. So there are many totally different fascinating issues you are able to do with the data we acquired from Google Maps.
Geo-Location APIs in Common
As you realize Google is just not the one one who supplies geo-location APIs. You are able to do the identical utilizing Microsoft Bing Map or HERE Maps.
Extra studying supplies:
Limitations
Utilizing Geocoding API is just not free so some limitations apply if you end up on a free utilization plan. As an example you should use solely 2,500 requests per day and 10 requests per second. Take a look at right here to study extra in regards to the API utilization limitations.
The opposite options like Microsoft Bing Map and HERE Map have some limitations as properly.
For extra data have take a look at the hyperlink under:
