# Dynamic Geo-Optimization in Tableau Using R-Integration

Here is a link to a sample workbook so that you can explore how it works

The starting dataset should be a list of latitudes and longitudes and a field for a weighting. The weighting could be implemented in a number of ways, for example, a list of stores using sales data or quantity of inventory as the weight. If the weight is the same for all of the locations then the center is basically the place which minimizes the sum of the distances of the locations, which will be very close to the average of the latitudes and longitudes, but when the weighting is applied then the centroid will move closer to the locations with the highest weightings.

In addition, by integrating into Tableau, the points on the map can be selected and the centroid is recalculated on the fly in R based on those selected points.

Here are the steps to create this visualization.

install.packages("Rserve") # only needed the first time you use the package

library("Rserve") # loads the Rserve package

Rserve() # starts the Rserve

Download the starting data file here

Setup the R connection in Tableau.

Under "Help" and "Settings and Performance" select "Manage R Connection"

Choose Server "localhost" and Port "6311" and click OK

Calculated Field Name:

Formula:

Calculated Field Name:

Formula:

Right click on each of these fields to set their geographic role. Set Optimalx to Latitude and Optimaly to Longitude.

Calculated Field Name:

Formula:

The "Weight" field can be any value you wish to assign. I used the calculation above to give a positive weighting on Prospects and a negative weighting where there is a current store. The result is a centroid that is in the center of the selected points based on prospect locations and weighting against existing locations. However, there is also a field in the CSV for Weight which is preloaded with a value of 100 for all points. Just substitute Weight Example in place of Weight in the R code for Optimalx and Optimaly above to use the calculation instead of the fixed value from the CSV.

Create a New Worksheet called "Locations"

Move Longitude to Columns

Move Latitude to Rows

Move Office/Prospects to Color

Move Name to Details

Change the colors as needed

Create a New Worksheet called "New Office"

Move Optimaly to Columns

Move Optimalx to Rows

Change the color of the dot for the centroid

The starting dataset should be a list of latitudes and longitudes and a field for a weighting. The weighting could be implemented in a number of ways, for example, a list of stores using sales data or quantity of inventory as the weight. If the weight is the same for all of the locations then the center is basically the place which minimizes the sum of the distances of the locations, which will be very close to the average of the latitudes and longitudes, but when the weighting is applied then the centroid will move closer to the locations with the highest weightings.

In addition, by integrating into Tableau, the points on the map can be selected and the centroid is recalculated on the fly in R based on those selected points.

Here are the steps to create this visualization.

**Step 1: Install and load the package Rserve and start Rserve**install.packages("Rserve") # only needed the first time you use the package

library("Rserve") # loads the Rserve package

Rserve() # starts the Rserve

**Step 2: Open Tableau and load some data with the fields Latitude, Longitude and Weight and Name or some sort of ID**Download the starting data file here

Setup the R connection in Tableau.

Under "Help" and "Settings and Performance" select "Manage R Connection"

Choose Server "localhost" and Port "6311" and click OK

**Step 3: Create some calculated fields***Find the Optimal Latitude*Calculated Field Name:

**Optimalx**Formula:

*SCRIPT_REAL("*

*distxy <- function(coorda, coordb)*

*{*

*dist <- acos(sin(pi/180*coorda[1])*sin(pi/180*coordb[1]) + cos(pi/180*coorda[1])* cos(pi/180*coordb[1])*cos(pi/180*coordb[2]-pi/180*coorda[2]))*6370*

*return(dist)*

*}*

*xstart <- 50*

*ystart <- 4*

*data <- data.frame(.arg1,.arg2,.arg3)*

*for (i in 1:20)*

*{*

*distance <- apply(data, 1, distxy, coordb=c(xstart,ystart))*

*nenner <- data[,3]/distance # denominator*

*zahler_x <- data[,3]*data[,1] / distance*

*xstart <- sum(zahler_x) / sum(nenner)*

*}*

*xstart*

*"*

*,ATTR([Latitude]), ATTR([Longitude]), ATTR([Weight])*

*)*

*Find the Optimal Longitude*

Calculated Field Name:

**Optimaly**

Formula:

*SCRIPT_REAL("*

*distxy <- function(coorda, coordb)*

*{*

dist <- acos(sin(pi/180*coorda[1])*sin(pi/180*coordb[1]) + cos(pi/180*coorda[1])* cos(pi/180*coordb[1])*cos(pi/180*coordb[2]-pi/180*coorda[2]))*6370

return(dist)

}

xstart <- 50

ystart <- 4

data <- data.frame(.arg1,.arg2,.arg3)

for (i in 1:20)

{

distance <- apply(data, 1, distxy, coordb=c(xstart,ystart))

nenner <- data[,3]/distance # denominator

zahler_y <- data[,3]*data[,2] / distance

ystart <- sum(zahler_y) / sum(nenner)

}

ystart

*"*

,ATTR([Latitude]), ATTR([Longitude]), ATTR([Weight])

*)*

Right click on each of these fields to set their geographic role. Set Optimalx to Latitude and Optimaly to Longitude.

*Sample Weighting Field*

Calculated Field Name:

**Weight Example**

Formula:

**IF [Office/Prospect]="Office" THEN -100 ELSE 100 END**

The "Weight" field can be any value you wish to assign. I used the calculation above to give a positive weighting on Prospects and a negative weighting where there is a current store. The result is a centroid that is in the center of the selected points based on prospect locations and weighting against existing locations. However, there is also a field in the CSV for Weight which is preloaded with a value of 100 for all points. Just substitute Weight Example in place of Weight in the R code for Optimalx and Optimaly above to use the calculation instead of the fixed value from the CSV.

**Step 4: Build the Viz**

Create a New Worksheet called "Locations"

Move Longitude to Columns

Move Latitude to Rows

Move Office/Prospects to Color

Move Name to Details

Change the colors as needed

Create a New Worksheet called "New Office"

Move Optimaly to Columns

Move Optimalx to Rows

Change the color of the dot for the centroid

Click Analysis and uncheck Aggregate Measures

Create a new dashboard

Place "Locations" on the dashboard

Place "New Office" on the dashboard

Select "Use as Filter" from the options dropdown on the "Locations" worksheet on the dashboard

Create a dashboard URL action

Select Dashboard and Actions and Add Action

Choose URL

Check the "New Office" check box and unselect the "Location" checkbox

Choose "Select" as the Run action on

Enter this URL in the box: http://maps.google.com/?q=,

You should now have a visualization to select various points on the map to find the geo optimized location based on the input locations and their weightings. Once the centroid is calculated, click on the point and it will be mapped in the browser on a Google map so you can explore the location.

A big thanks to Jeffrey Shaffer for his original blog post which inspired us to build this sample workbook.

Create a new dashboard

Place "Locations" on the dashboard

Place "New Office" on the dashboard

Select "Use as Filter" from the options dropdown on the "Locations" worksheet on the dashboard

Create a dashboard URL action

Select Dashboard and Actions and Add Action

Choose URL

Check the "New Office" check box and unselect the "Location" checkbox

Choose "Select" as the Run action on

Enter this URL in the box: http://maps.google.com/?q=

You should now have a visualization to select various points on the map to find the geo optimized location based on the input locations and their weightings. Once the centroid is calculated, click on the point and it will be mapped in the browser on a Google map so you can explore the location.

A big thanks to Jeffrey Shaffer for his original blog post which inspired us to build this sample workbook.

I just downloaded this and tried it out with our own R-Integration. It worked great and is very cool! Thank you so much for sharing.

ReplyDelete