Data + Science

11/2/2015
Dynamic Geo-Optimization in Tableau Using Integration with R - Instructions

Last year I did a blog post on Dynamic Geo-Optimization in Tableau Using Integration with R with a sample video and a description of how this might work. The original R code is from R-Bloggers posted here. Last month, Silke Govaert from Datatonic, emailed me. She had implemented this in Tableau and we discussed doing an instructional post to supplement my original post. This is also posted on the Datatonic blog here.

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

The starting data set should be a list of latitude and longitude 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, 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 based on those selected points. Here is a screenshot of Silke's visualization.

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)
  }
  costs <- c(NULL)
  xstart <- 50
  ystart <- 4
  data2 <- data.frame(.arg1,.arg2,.arg3)
   for (i in 1:20)
   {
   distance <- apply(data2, 1, distxy, coordb=c(xstart,ystart))
   nenner <- data2[,3]/distance # denominator
   zahler_x <- data2[,3]*data2[,1] / distance
   xstart <- sum(zahler_x) / sum(nenner) 
   costs<-append(costs, data2[,3]*distance) 
    }
    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)
  }
  costs <- c(NULL)
  xstart <- 50
  ystart <- 4
  data2 <- data.frame(.arg1,.arg2,.arg3)
   for (i in 1:20)
   {
   distance <- apply(data2, 1, distxy, coordb=c(xstart,ystart))
   nenner <- data2[,3]/distance # denominator
   zahler_y <- data2[,3]*data2[,2] / distance
   ystart <- sum(zahler_y) / sum(nenner) 
   costs<-append(costs, data2[,3]*distance) 
    }
    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. Silke 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 Silke Govaert and Datatonic for exploring my original blog post and building this sample workbook. I hope you find this information useful. If you have any questions feel free to email me at Jeff@DataPlusScience.com

Jeffrey A. Shaffer

Follow on Twitter @HighVizAbility