postcode distances using google

Question

I have a two lists of postcodes (in R)...one of children's addresses with their academic score and one of schools...

i would like to be able to get the closest school for each child...so presumably a calculation of distance would been needed between postcodes by converting to long and lat values?

And then I would like to be able to plot on a google map all the children per school...and see if the children who live closer to school get better grades...perhaps ploting schools a different colour to kids, and the kids having a gradient of colour according to their score?

perhaps something using the googleVis package?

so for example...

if we have the data for 3 kids and 2 schools...

student.data <- cbind(post.codes=c("KA12 6QE", "SW1A 0AA", "WC1X 9NT"),score=c(23,58,88))
school.postcodes <- c("SL4 6DW", "SW13 9JT")

(N.B. My actual data is obviously significantly larger than the one given so scalability would be useful...)

what should be done with googleVis or any other package for that matter to be able to complete the above?

Answer 1

I would start by something like this to get the lat/long

Get lat/long for each post code

library(XML)
school.postcodes <- c("KA12 6QE", "SW1A 0AA", "WC1X 9NT")
ll <- lapply(school.postcodes,
    function(str){
       u <- paste('http://maps.google.com/maps/api/geocode/xml?sensor=false&address=',str)
       doc <-  xmlTreeParse(u, useInternal=TRUE)
       lat=xpathApply(doc,'/GeocodeResponse/result/geometry/location/lat',xmlValue)[[1]]
       lng=xpathApply(doc,'/GeocodeResponse/result/geometry/location/lng',xmlValue)[[1]]
       c(code = str,lat = lat, lng = lng)
})
# get long/lat for the students
ll.students <- lapply(student.data$post.codes,
             function(str){
               u <- paste('http://maps.google.com/maps/api/geocode/xml?sensor=false&address=',str)
               doc <-  xmlTreeParse(u, useInternal=TRUE)
               lat=xpathApply(doc,'/GeocodeResponse/result/geometry/location/lat',xmlValue)[[1]]
               lng=xpathApply(doc,'/GeocodeResponse/result/geometry/location/lng',xmlValue)[[1]]
               c(code = str,lat = lat, lng = lng)
             })

ll <- do.call(rbind,ll)
ll.students <- do.call(rbind,ll.students)

do.call(rbind,ll)
      code         lat          lng         
[1,] "KA12%206QE" "55.6188429" "-4.6766226"
[2,] "SW1A%200AA" "51.5004864" "-0.1254664"
[3,] "WC1X%209NT" "51.5287992" "-0.1181098"

get the distance matrix

library(RJSONIO)
dist.list <- lapply(seq(nrow(ll)),
                    function(id){
                      url <- paste("http://maps.googleapis.com/maps/api/distancematrix/json?origins=",
                                   ll[id,2],",",ll[id,3],
                                   "&destinations=",
                                   paste( ll.students[,2],ll.students[,3],sep=',',collapse='|'),
                                   "&sensor=false",sep ='')
                      res <- fromJSON(url)
                        hh <- sapply(res$rows[[1]]$elements,function(dest){
                          c(distance= as.numeric(dest$distance$value),
                                     duration = dest$duration$text)
                        })
                      hh <- rbind(hh,destination =  ll.students[,1])

                    })
names(dist.list) <- ll[,1]

dist.list
$`SL4 6DW`
            [,1]              [,2]      [,3]     
distance    "664698"          "36583"   "41967"  
duration    "6 hours 30 mins" "43 mins" "49 mins"
destination "1"               "2"       "3"      

$`SW13 9JT`
            [,1]              [,2]      [,3]     
distance    "682210"          "9476"    "13125"  
duration    "6 hours 39 mins" "22 mins" "27 mins"
destination "1"               "2"       "3"