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.



( (([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 


 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.

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Thanks for the informative post! Does the distance calculation take the curvature of the earth into account?


    Reply Like
login to reply
Like2 Follow
  • 2 Liked by
  • 2 wk agoLast active
  • 1Replies
  • 457Views
  • 3 Following