Fetching geodata information using Bing Maps API in Power BI

Sometimes you might have to perform analysis based on geographical information. Be it that you have to calculate the distance or travel duration between two points, you need to display some points of interest on a map or simply the need to fetch information such as latitude or longitude for an address to use it in a geospatial visualisation. This is where the web datasource in Power Query comes in handy. Here is how this can be done utilizing Bing Maps API:

What is Bing Maps API and what is it good for?

Bing Maps is an online map service from Microsoft through which various spatial data can be viewed and spatial services can be used. Maps, aerial photos and satellite images can be viewed free of charge. A special feature is oblique aerial images from a bird's eye view, which is usually only available of urban areas. 3D models are available for various cities. In addition to the data provided, various services can be used, including location search and route planning.

 

Bing maps provides a couple of endpoints via REST API. At the time of writing those are:

  • Locations - Provides geocode and reverse geocode location data: Find a location based on address or point (latitude/longitude). This endpoint also provides information on local business, points of interest or the ability to search nearby businesses. 
  • Elevations - Provides elevation information for a set of locations. This includes information on their hight above or below a fixed reference point (e.g. sea level)
  • Imagery - This endpoint offers maps and images according to your specifications. This includes static maps as well as display of routes on maps and getting streetside or bird's eye view images.
  • Routes - Enables calculation of routes between locations for driving, walking or transit. This includes parameters for calculating best routes for vehicles of special dimensions or routes that are optimal in terms of time or distance.
  • Traffic - Provides information on traffic incidents and issues in a specific area at a specific time.
  • Autosuggest - Returns a list of autosuggested entities based on a users query (phrase) the user is most likely to search for
  • TimeZone - Using this endpoint you can fetch information on time zones for specific locations. It also enables converting UTC datetimes to other time zones even taking into account daylight saving time

Of course available APIs and their capabilities may be subject to changes. To stay up to date please reach out to the Microsoft documentation "Bing Maps REST Service".

How to connect to Bing Maps REST API?

In order to use Bing Maps API Services you need a Bing Maps Key. You can receive it from your Bing Maps Developer Account by signing in or logging in on www.bingmapsportal.com. Dustin Ryan gives a good walkthrough on how to create the key in his article on "Power BI and the Bing Maps API". Here is a short recap of the steps:

Bing Maps login
1.) Signing in
Create Bing Maps Key
2.) Create Bing Maps Key
Create Bing Maps Key
3.) Fill in form
  1. Create an account or logging in using your existing Microsoft account or Enterprise Azure AD account
  2. Go to "My account" in the menü bar at the top and klick "My Keys"
  3. Click the link "click here to create a new key" and fill in the form. Provide a name, leave "Application URL" blank and stick to default for the rest. Click "create" when done.
  4. Click "Show key" and copy your Bing Maps Key to the clipboard to use it in your code
Please find the menu reference in the bing maps portal for these steps to the left.

The basic URL to connect to the Bing Maps services is:

 

https://dev.virtualearth.net/REST/version/restApi/resourcePath?queryParameters&key=YourBingMapsKey  

 

Depending on the endpoint you want to use, this pattern will form the call for one of the options mentioned above. Lets assume you want to connect to the Locations-endpoint. This will enhance the URL as folllows:

 

https:// dev.virtualearth.net/REST/v1/Locations

 

As "resourcePath" you might provide a string containing the address of your point of interest. In the "queryParameters" section you can enhance your request by a couple of parameters that depend on the endpoint you use and the task you want to perform. One example might be the parameter "o=xml" which sets the output format to xml.

 

For example  the URL for fetching the geodata information for the address of Microsofts Visitors Center in Redmond, Washington is:

 

https:// dev.virtualearth.net/REST/v1/Locations/”15010 NE 36th St Building 92, Redmond, WA 98052, United States”?o=xml&key=YourBingMapsKey

 

Here "YourBingMapsKey" is the place to provide your key.

Fetching geocodes for addresses or vise versa using the Location API is a very useful and frequent application for a service like Bing Maps. 


Create Bing Maps API Key
4.) Copy Bing Maps Key

Dustin Ryan did a great job describing how this and fetching information on travel distances and durations is done in detail. So I want to repeat my recommendation on reading his post on this topic ("Power BI and the Bing Maps API"). I also recommend reaching out to the Microsoft Documentation for more examples and details on the capabilities of all the individual APIs.

A step by step example

Lets assume your company follows an initiative for carbon neutrality and therefor is interested in the estimated CO2 emissions that their employees cause when traveling by taxi. Fortunatelly your company uses a platform like FreeNow to book all its taxi rides. In this case you are able to get some data similar to the following to base your calculation on:

Using addresses Bing Maps API with Power BI
List of addresses
Using adresses Bing Maps API with Power BI
List of trips with start, destination, date and costs

You get a list of all taxi rides containing addresses of starting points and destinations as well as dates and travel fees. But unfortunatelly there are a lot of information missing to estimate the carbon footprint of that trips such as travel distances and durations. This is how Bing Maps API can help you out: 

Step 1: Load the list of addresses and the list containing the trip information ("RawData") to the Power Query Window. Do not load it to the model, but keep it as a connection.


Step 2: In order to enhance the list of addresses with the corresponding geocodes we can create the function "fnFetchGeocodes" to query the codes for each address in the list.

Fetching geocodes from Bing Maps API using Power BI, Power Query and M-Code
Fetching geocode data from Bing Maps API
Fetching latitude and longitude via Bing Maps API using Power BI, Power Query and M
List of addresses enhanced by geocodes

This function takes an address as parameter and returns the related geocode from the Bing Maps API. The part of interest here is the Source-step. It uses the Web.Contents-Function to call the Locations endpoint as explained before. As you might have noticed I am using the RelativePath and Query options of the Web.Contents() function to make it easier to build the needed URL and also increase readability following the suggestion by Chris Webb from this blog post: Using The RealtivePath and Query Options With Web.Contents() In Power Query and Power BI M Code.

While the first part reflects the base URL for the needed endpoint, the parameter "relativePath" enables us to enhance that base URL, in this case by the address. Via the second parameter "Query" we can provide additional parameters, e.g. for the output format, which is xml and the necessary API-key.

All other steps are just several steps of expanding the column containing the fetched records via the GUI, till we get the latitudes, longitudes and all the other information we might need.

Step 3: Join the geocodes from the list of addresses to your list of trips in the RawData query using the merge functionality of Power Query. You have to merge it two times: First for the TourStartID to provide the latitude and longitude for the origin. And second for the TourEndID to provide it for the destination. After that you can simply expand the latitude and longitude information for your origin and destination locations in the RawData query.

Bing Maps API, Locations endpoint, Power Query, Power BI, M
Merge list of addresses to list of trips using TourStartID/TourEndID and expand information

Step 4: Create the function "fnFetchTimeDistance" to get information about the distance in km and the duration in minutes between start and end of each taxi trip based on the respective latitude and longitude:

Fetching geocodes from Bing Maps API using Power BI, Power Query and M-Code
Fetching travel times and distances by car from Bing Maps API

Again, the most important part of this function is the Source-step. This time we are using the DistanceMatrix of the Routes endpoint in the URL to enable calculation of distances and durations. Besides our well known parameters for the output format and the API key we also provide some specification on how to determine the needed information like the "travelMode", units for time and distance and the geocodes for origin and destination for the current row in the RawData query.

Of course I would suggest to persist the results of those queries to some kind of datastore like e.g. SQL Server. In that way you can query the API only for new addresses and trips and not for each and every location in your RawData as I did for this little example. This would speed up the refresh process drastically because querying the API for a lot of locations takes quite some time.

 

This is how the resultset looks like:

Fetching durations and distances from Bing Maps API using Power Query, Power BI and M Code
RawData enhanced with duration an distance between origin and destination

At least now we have everything we need to report on the estimated time and distances our employees traveled by taxi. Multipliing this by an average CO2 emission per km for cars in Europe will enable us to get a feeling on the carbon footprint of our company. We could simply load the data to the model, build our measures and create the report.

But just for the fun of playing around with the Bing Maps API I wanted to add some visual impression of the trips that were done, their starting points and destination. So I went for the Imagery endpoint which offers maps, images and street side- and bird's eye-views of locations. So I added three additional custom columns to the RawData before loading it to the model:

Step 5: Creating two custom columns (BirdViewStart and BirdViewEnd),one for the origin and one for the destination, both constructing the necessary URL to receive a bird's eye impression of the respective location:

Using Bingmaps Imagery endpoint with Power Query, Power BI and M

Those URLs use the latitudes and longitudes for the start- and end-points of each trip and of course the necessary API key. All other parameters used are just for configuring the angle and size of the picture that is returned by Bing Maps.

Step 6: Creating a custom column (Routes) containing the URL to receive a map with the route from start to end displayed:

Using Bingmaps Imagery endpoint with Power Query, Power BI and M

Besides the obvious parameters like the geocodes for the origin and the destination and the API key this URL also contains the "travelMode" which is set to "driving" to get the shortest route by car. 

With all this information in place we can create the following report about our estimated travel activities and emissions:

For display of the bird's eye views of start and end and the travel route I downloaded the "Simple Image" custom visual from the App Store and simply dumped the URLs from the "BirdViewStart", "BirdViewEnd" and "Routes" column into them.

If you want to take a closer look on the code for the Bing Maps calls or play around a bit with the sample data on your own feel free to download the following files:

Download
Sample Code for columns and functions
Download sample code for Bing Maps API calls used in this post
Bing Maps Sample Code.zip
Komprimiertes Archiv im ZIP Format 2.2 KB
Download
Sample data file
Download if you need some data to play around with the Bing Maps APIs
Demo Daten.xlsx
Microsoft Excel Tabelle 44.7 KB

Conclusion

Whenever you need to enhance your data with geographical information Bing Maps API can help you out. Although some services are not available for all countries (e.g. street side views are disabled for Germany) Bing maps offers a wide range of endpoints providing information such as geocodes, local points of interest, elevations, traffic incidents or determining optimal routes according to specific criteria. It also provides images and maps that enable you to add some additional visual impressions of your data.

Key to this is creating a Bing Maps API Key which can be done using your Bing Maps Developer Account on www.bingmapsportal.com.

I hope you enjoyed this article and will be back for my next post. You can also visit my YouTube-Channel for more content on Power BI, SSAS, Power Pivot, Power Query, DAX und M!

Greetings from Hamburg, Germany

 

Uwe

SHARE...

RSS-FEED BI BLOG...



STAY IN TOUCH...

BESTENS INFORMIERT
Entwickeln Sie Ihr Know-How und Ihre
Möglichkeiten im Reporting und erhalten
Sie mit dem Newsletter exklusive
Beispieldateien
zu unseren Artikeln

LATEST ARTICLES ON BI BLOG...



Kommentar schreiben

Kommentare: 0