Leveraging map layers with geospatial data using Power BI

Today businesses use geospatial data to choose the right location to add stores, optimize supply and distribution routes, segment and target specific customers, analytic consumption patterns and much more. Bottom line, leveraging map layers to collaborate two sets of geospatial data can maximize your businesses efficiency in visualizing the proximity and clustering of your data.

Building upon our article Choosing the Right Geospatial Clustering Algorithm for Your Business Need, this blog shows you how to visualize geospatial data and aggregate geospatial data in Power BI using layered maps.

Layered Maps can be a useful tool for a variety of different business needs, such as:

1. Integrating demographic information such as age, income, and education with location data such as vendor storefronts or competitor locations.

2. Understanding optimal resource management by layering warehouse locations or shipping/receiving facilities with transportation routes, customer location, or competitor facilities.

3. Assessing risks to assets by overlaying your businesses assets and storefront locations on crime hotspots, environmental factors, and property value factors.

Developing a map visual that allows for strong insight into your data can be a difficult feat requiring outsourcing or hiring in a GIS (Geographic Information System) Developer. However, using two very user-friendly tools – Mapbox and Power BI – we can simplify the process.

Generating a Layered Map

Let’s walk through an example of generating a layered map like the above resource management. We will be utilizing two sets of public data from the Chicago data portal:

• Bike route data for our ICON map visual – This data identifies all bike routes in Chicago.

• Bike rack data for our base layer - This data identifies safe, designated, and optimal areas to leave a bike.

A bike messenger company in Chicago wants to understand the most efficient routes their messengers should take based on traffic, safety, and availability of bike racks close to their intended destinations. The goal is to map the available bike routes and create a base layer that allows us to visually see the available resources (bike racks) to our messengers.

Step 1: Prepare the Data

As is essential to any data-oriented project, first we must adapt our data to our needs. For our need the required format is geocoded (Latitude and Longitude) or well-known text (WKT). If the data you have available to you is not already in one of these formats, tools for geocoding can be utilized to turn addresses, postal codes, or boundary data into geocode or WKT. Below are a couple of options to transform the data:

Option 1: Geocoding address data using python libraries

Python libraries, such as geopy, can identify the geocode of specified addresses. The code snippet below reads a csv file with a list of addresses in it and returns the geocode of that location using the Nominatim package in geopy (Figure 1):

Figure 1

It is important to note that blank values will be output in the case where the address is not able to be found by geopy. To fix this issue, standardize the addresses using the USPS postal address format prior to running geopy.

Option 2 - Well Known Text (WKT)

WKT is a text representation of geometric objects. WKT is based on specifications from the Open Geospatial Consortium and is used as the standard format in Geographic Information Systems (GIS). WKT is used to represent LINES(), POINTS(), POLYGONS(), etc using respective coordinates (Xi, Yi) to define where and what object a GIS software is to create. Due to the simple and human readable format, a WKT field can be manually created but it is much more efficient to utilize software such as Mapbox's Direction API or QGIS. These are user friendly platforms to plot, connect, and export a file with objects and coordinate parameters that will best display our data when referenced by any other GIS software. Figure 2 below shows an example of WKT data.

Figure 2

Step 2: Creating a Power BI Map – Using route data

Using the Icon Map visualization in Power BI, we used WKT text to map our bike routes in Chicago as shown in Figure 3. Here we can see our location-based data, however we are unable to map the bike rack data in the same visualization. As a result, we do not know where our bike messengers can leave their bikes along these routes.

Figure 3

Step 3: Create a Base Layer - Using bike rack data

For our base layer map, we used Mapbox Studio with geocode data generated by python. The basic steps are below:

1. Create a new map.

2. Add a new layer with the geocode data and define the latitude and longitude fields.

3. Add a metric field to display relativity between our locations. For our bike rack scenario, we used the number of slots available per location. Other metrics for typical resource management scenarios include shipping capacities of facilities, the amounts of material needed by clients, or the traffic congestion. The map in Figure 4 was created using the circle map style.

4. Once completed with your map, select publish in the right-hand corner and a reference link will be created to use this as a base layer. To be used in Power-Bi, set the developer resource to third party and select CARTO before copying.

Figure 4

Step 4: Create layered map

Adding the base layer generated in Step 3 to the Power BI map generated in Step 2 can be done in 3 easy steps:

1. Open the Bike route report.

2. Navigate to the background layer menu under the format visual tab

3. Set your background layer to custom URI and paste in your Map Box link.

A zoomed in view of the final map is shown in Figure 5 below.

Figure 5

At this stage, we can really start to see the value in adding a base layer. Adding in our bike rack data to the bike route map assists in understanding why one path might be more efficient than another based on where one can safely stop relative to their destination, if there are time constraints based on bike rack availability, or choosing safer alternative locations if there are concerns of theft.

Conclusion

Implementing layered maps in Power BI is a great way to visualize multiple factors in your map data such as competitor locations, foot traffic volume, crime statistics, or residential hubs. Layered maps allow for an additional perspective on any location-based data that influences the actions your business takes to mitigate or collaborate with its surrounding area.

Want to do even more with visualizing your data? We are here to help. Contact us at Scalesology and let’s have a quick chat on how you too can display multiple map layers with geospatial data.