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

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:

Find the Optimal Longitude
Calculated Field Name: Optimaly
Formula:

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