<< Back

Drivetime analysis in Tableau using R

During our recent back to school week, my cohort was given mini-projects related to Tableau and Alteryx. One of those projects was to create use cases with either the R or Python integration within Tableau. *For information on how to set up R integration in Tableau read this guide – https://benjnmoss.wordpress.com/2017/01/03/r-and-tableau-installing-and-connecting-to-r-local/

I decided to work around the limitations of the R integration and create a tableau dashboard that can perform drivetime analysis using OSRM. OSRM is a routing service based on Open Street Map (https://cran.r-project.org/web/packages/osrm/README.html). *This uses a demo version of OSRM so it can be unstable (read the description on this page – https://github.com/rCarto/osrm)

The final outcome, see above, is a dashboard where the user inputs a location’s latitude and longitude to receive a 30-minute drivetime polygon, broken into 5-minute segments.

The R integration was designed using table calculations, this heavily restricts the amount of data that can be sent from and returned to Tableau. The biggest problem to tackle was the returning data as this varied dependant on the location of the drivetime analysis. To add further complexity, I also wanted the output to overwrite the underlying data source.

To work around this limitation, I created a script that returned a simple string value after R had published the drivetime data to a google sheet. This prevented the calculation from always returning an error when trying to retrieve the new data. It also allowed me to give simple instructions to the user such as ‘Enter latitude and longitude’.

Below is an annotated version of the script used.

SCRIPT_STR(‘

if (arg1>180) {

” Enter latitude and longitude ”

// if longitude parameter is > 180 return above string. Important as it prevents the script from running when the dashboard is initially opened

}else{

library(jsonlite);

library(googlesheets);

library(osrm);

library(rgdal);

library(raster);

library(spbabel);

library(ggplot2);

library(rCarto);

// import necessary libraries. These must be installed on the R console

olddata <- gs_title(“rtest”);

// load original google sheet

createdata <- osrmIsochrone(loc = c(.arg1,.arg2), breaks = seq(from = 0,to = 30, by = 5));

// run osrm to create drivetime polygons

newcrs <- CRS(“+proj=robin +datum=WGS84”);

createdata <- spTransform(createdata, newcrs);

createdata <- spTransform(createdata, CRS(“+proj=longlat +datum=WGS84”));

createdata <- ggplot2::fortify(createdata);

// these steps convert OSRM polygons to latitude and longitude and create a data frame useable in tableau

gs_edit_cells(ss = olddata, ws = 1, input = createdata, anchor = “R1C1”, byrow = FALSE, col_names = NULL, trim = TRUE);

// overwrite google sheet with new dataframe

“Make longitude > 180 and refresh datasource”}’,[longitude],[latitude])

// to prevent the script re-running, instruct the user to change parameter before refreshing the data source.

If the project was longer, I would have created additional parameters to give the user more functionality. For example, the drivetime distance and segments would become adjustable. Furthermore, I would try to add geocoding to the script, so the entry of addresses and postcodes could be made possible. Finally, a database would be used instead of google sheets to increase speed.

Jamie Gough

London, UK

Leave a Reply

Your email address will not be published. Required fields are marked *