Information + Science

11/2/2015
Dynamic Geo-Optimization in Tableau Utilizing Integration with R – Directions

Final yr I did a weblog put up on Dynamic Geo-Optimization in Tableau Using Integration with R with a sample video and an outline of how this may work. The unique R code is from R-Bloggers posted here. Final month, Silke Govaert from Datatonic, emailed me. She had carried out this in Tableau and we mentioned doing an tutorial put up to complement my authentic put up. That is additionally posted on the Datatonic weblog here.

Here is a link to a pattern workbook with the intention to discover the way it works

The beginning information set needs to be an inventory of latitude and longitude and a discipline for a weighting. The weighting may very well be carried out in plenty of methods, for instance, an inventory of shops utilizing gross sales information or amount of stock as the load. If the load is similar for the entire areas then the middle is mainly the place which minimizes the sum of the distances of the areas, which will probably be very near the typical of the latitudes and longitudes, however when the weighting is utilized, the centroid will transfer nearer to the areas with the very best weightings.

As well as, by integrating into Tableau, the factors on the map could be chosen and the centroid is recalculated on the fly based mostly on these chosen factors. Here’s a screenshot of Silke’s visualization.

Listed below are the steps to create this visualization.

Step 1: Set up and cargo the package deal Rserve and begin Rserve

set up.packages(« Rserve ») # solely wanted the primary time you utilize the package deal
library(« Rserve ») # hundreds the Rserve package deal
Rserve() # begins the Rserve

Step 2: Open Tableau and cargo some information with the fields Latitude, Longitude and Weight and Title or some form of ID

Obtain the beginning information file here

Setup the R connection in Tableau.
Below « Assist » and « Settings and Efficiency » choose « Handle R Connection »
Select Server « localhost » and Port « 6311 » and click on OK

Step 3: Create some calculated fields

Discover the Optimum Latitude
Calculated Discipline Title: Optimalx
Formulation:
 SCRIPT_REAL( » distxy <- operate(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) } prices <- c(NULL) xstart <- 50 ystart <- 4 data2 <- information.body(.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) prices<-append(prices, data2[,3]*distance) } xstart » ,ATTR([Latitude]), ATTR([Longitude]), ATTR([Weight]) )

Discover the Optimum Longitude
Calculated Discipline Title: Optimaly
Formulation:
 SCRIPT_REAL( » distxy <- operate(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) } prices <- c(NULL) xstart <- 50 ystart <- 4 data2 <- information.body(.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) prices<-append(prices, data2[,3]*distance) } ystart » ,ATTR([Latitude]), ATTR([Longitude]), ATTR([Weight]) )

Proper click on on every of those fields to set their geographic position. Set Optimalx to Latitude and Optimaly to Longitude.

Pattern Weighting Discipline
Calculated Discipline Title: Weight Instance
Formulation:
IF [Office/Prospect]= »Workplace »
THEN -100
ELSE 100
END

The « Weight » discipline could be any worth you want to assign. Silke used the calculation above to present a constructive weighting on Prospects and a adverse weighting the place there’s a present retailer. The result’s a centroid that’s within the middle of the chosen factors based mostly on prospect areas and weighting in opposition to current areas. Nevertheless, there’s additionally a discipline within the CSV for Weight which is preloaded with a worth of 100 for all factors. Simply substitute Weight Instance rather than Weight within the R code for Optimalx and Optimaly above to make use of the calculation as an alternative of the fastened worth from the CSV.

Step 4: Construct the Viz

   Create a New Worksheet known as « Places »
   Transfer Longitude to Columns
   Transfer Latitude to Rows
   Transfer Workplace/Prospects to Colour
   Transfer Title to Particulars
   Change the colours as wanted

   Create a New Worksheet known as « New Workplace »
   Transfer Optimaly to Columns
   Transfer Optimalx to Rows
   Change the colour of the dot for the centroid
   Click on Evaluation and uncheck Mixture Measures

   Create a brand new dashboard
   Place « Places » on the dashboard
   Place « New Workplace » on the dashboard
   Choose « Use as Filter » from the choices dropdown on the « Places » worksheet on the dashboard

   Create a dashboard URL motion
   Choose Dashboard and Actions and Add Motion
   Select URL
   Examine the « New Workplace » verify field and unselect the « Location » checkbox
   Select « Choose » because the Run motion on
   Enter this URL within the field: http://maps.google.com/?q=,

You need to now have a visualization to pick numerous factors on the map to seek out the geo optimized location based mostly on the enter areas and their weightings. As soon as the centroid is calculated, click on on the purpose and it is going to be mapped within the browser on a Google map so you may discover the placement.

An enormous because of Silke Govaert and Datatonic for exploring my authentic weblog put up and constructing this pattern workbook. I hope you discover this data helpful. If in case you have any questions be at liberty to e-mail me at Jeff@DataPlusScience.com

Jeffrey A. Shaffer

Comply with on Twitter @HighVizAbility