Distance-Based Geo Analysis
by Michael Raam, Principal Data Analytics, Pyramid Analytics
and David Novick, Technical Writer, Pyramid Analytics
In the world of data analytics, one of the primary usages of maps is to perform distance-based analysis. Some common business applications might be:
- Distance-Based Clustering
- Distance-Based Filtering
- Distance-Based Distribution
- Distance-Based Advertising
In this blog, we will illustrate some basic BI Office techniques for highlighting locations based on their distance from a given city.
In the example below, all California cities within 1.5 radius units of the city named “Calpack” have been highlighted on the map. When the user changes the distance (radius) or the center (origin point), the highlighting is updated interactively within the map.
In the following steps, we explain how this particular example was created within BI Office.
California cities within 1.5 radius units.
STEP 1 – Using Data with Geo-Coordinates
In order for distance analysis to operate properly, we first need to ensure that the source data includes some type of geo-coordinates. In this case, we begin with a simple MS Excel file that includes latitude and longitude figures for each city in California.
Longitude and latitude coordinates for California cities.
STEP 2 – Uploading Data into BI Office
Now we upload the MS Excel contents into BI Office using the Data Modeling module. During the upload process, we need to properly define the category of each data item. For example, the city item (left) must be assigned the city category (right).
Match Category items (at right) to Display Names (left).
STEP 3 – Creating Calculated Measures
In this example, we create two calculated measures within BI Office. The first measure is named Inside and has the following values:
- Value = 1 (if city is inside specified radius)
- Value = 0 (if city is outside of radius)
The second measure is named Outside and has the following values:
- Value = 1 (if city is outside specified radius)
- Value = 0 (if city is inside radius)
STEP 4 – Custom MDX Code
In this example, we have created custom MDX code (shown below) in order to achieve the desired geo-functionality for the variable named “Distance” and the parameter named “Center”. For more information on working with MDX code, variables and parameters, refer to the BI Office help.
case when ( (([USA-CAL-cities].[State City Hierarchy].CURRENTMEMBER,[Measures].[_Latitude_]) - ([USA-CAL-cities].[State City Hierarchy].[!@Center@!],[Measures].[_Latitude_]) )^2 + (([USA-CAL-cities].[State City Hierarchy].CURRENTMEMBER,[Measures].[_Longitude_]) - ([USA-CAL-cities].[State City Hierarchy].[!@Center@!],[Measures].[_Longitude_]) )^2 )^0.5 <= [Variable].[!##Distance##!] then 1 else 0 end
Example of variable and parameter within MDX code.
STEP 5 – Interactive Geo Analysis
Once we place the calculated measures (Inside/Outside) on columns and the City hierarchy on rows, the BI Office mapping becomes operational. The user can access the two slicers to change the distance (radius) and center (origin point) and view the results.
Interactive use of slicers to change visual results.