Data analytics case

[Rick]

Hi

Over the last few years, I have added R to the book to support the demand for data analytics.

Here is a case that can be used as a capstone to the R material.

http://people.terry.uga.edu/rwatson/general/Merck.html

The core R code follows. This is not the answer, but just code for checking that the data are available and examples of how to use the distance functions.

I plan to use this as a group assignment. 

Cheers  

Rick

 

library(readr)

library(DBI)

library(geosphere)

library(ggmap)

# get the employee data

url <-  'http://people.terry.uga.edu/rwatson/data/merck.csv'

t <-  read_delim(url,delim=',')

# access the zip code databases

conn <- dbConnect(RMySQL::MySQL(), "wallaby.terry.uga.edu", dbname="zipcode", user="student", password="student")

# Query the database and create file z for use with R

loc1 <-  dbGetQuery(conn,"SELECT zipLat, zipLon from zip where zip = 30606;")

loc2 <-  dbGetQuery(conn,"SELECT zipLat, zipLon from zip where zip = 08889;")

# compute the great circle distance in miles

distGeo(c(loc1$zipLat,loc1$zipLon),c(loc2$zipLat,loc2$zipLon))*0.000621371

# compute the road distance in miles

mapdist("Athens, GA", "Whitehouse Station, NJ", mode = c("driving"), output = c("simple", "all"))[[5]]

[Rick]

Hi

David Eargle suggested some improvements to the exercise I posted some months back.

The R code I provided requires accessing a UGA server. David  found a csv of US postal addresses that can be used instead.

http://download.geonames.org/export/zip/US.zip

He revised the R code and highlighted the changes:

library(readr)

library(DBI)

library(geosphere)

library(ggmap)

library(sqldf)

# get the employee data

url <-  'http://people.terry.uga.edu/rwatson/data/merck.csv'

t <-  read_delim(url,delim=',')

# access the zip code databases

col.names <- c('country','zip','place_name','state_name','state_code','county_name','count_code','subdivision_name','subdivision_code','zipLat','zipLon','acc')

zip <- read.delim("<path to extracted US.txt>", header=FALSE, col.names=col.names)

#conn <- dbConnect(RMySQL::MySQL(), "wallaby.terry.uga.edu", dbname="zipcode", user="student", password="student")

# Query the database and create file z for use with R

#loc1 <-  dbGetQuery(conn,"SELECT zipLat, zipLon from zip where zip = 30606;")

#loc2 <-  dbGetQuery(conn,"SELECT zipLat, zipLon from zip where zip = 08889;")

loc1 <-  sqldf("SELECT zipLat, zipLon from zip where zip = 30606;")

loc2 <-  sqldf("SELECT zipLat, zipLon from zip where zip = 08889;") 

# compute the great circle distance in miles

distGeo(c(loc1$zipLat,loc1$zipLon),c(loc2$zipLat,loc2$zipLon))*0.000621371

# compute the road distance in miles

mapdist("Athens, GA", "Whitehouse Station, NJ", mode = c("driving"), output = c("simple", "all"))[[5]]

Cheers  

Rick