1

I'm working on looping through long and latitude points for the googleways api. I've come up with two ways to do that in an effort to access the points sections shown in the following link:

https://cran.r-project.org/web/packages/googleway/vignettes/googleway-vignette.html

Unforuntaely since this uses a unique key I can't provide a reproducible example but Below are my attempts, one using mapply and the other with a loop. Both work in producing a response in list format, however I am not sure how to unpack it to pull out the points route as you would when passing only one point:

df$routes$overview_polyline$points

Any suggestions? 

library(googleway)

dir_results = mapply(
  myfunction,
  origin = feed$origin,
  destination = feed$destination,
  departure = feed$departure
)

OR

empty_df = NULL
for (i in 1:nrow(feed)) {
  print(i)
  output = google_directions(feed[i,"origin"], 
                             feed[i,"destination"], 
                             mode = c("driving"), 
                    departure_time = feed[i,"departure"], 
                    arrival_time = NULL,
                    waypoints = NULL, alternatives = FALSE, avoid = NULL,
                    units = c("metric"), key = chi_directions, simplify = T)
  empty_df = rbind(empty_df, output)
}

EDIT**

The intended output would be a data frame like below: where "id" represents the original trip fed in. 

     lat       lon                                                                    id
1  40.71938 -73.99323 40.7193908691406+-73.9932174682617 40.7096214294434+-73.9497909545898
2  40.71992 -73.99292 40.7193908691406+-73.9932174682617 40.7096214294434+-73.9497909545898
3  40.71984 -73.99266 40.7193908691406+-73.9932174682617 40.7096214294434+-73.9497909545898
4  40.71932 -73.99095 40.7193908691406+-73.9932174682617 40.7096214294434+-73.9497909545898
5  40.71896 -73.98981 40.7193908691406+-73.9932174682617 40.7096214294434+-73.9497909545898
6  40.71824 -73.98745 40.7193908691406+-73.9932174682617 40.7096214294434+-73.9497909545898
7  40.71799 -73.98674 40.7193908691406+-73.9932174682617 40.7096214294434+-73.9497909545898
8  40.71763 -73.98582 40.7193908691406+-73.9932174682617 40.7096214294434+-73.9497909545898

EDIT**** dput provided for answering question on dataframe to pair list:

structure(list(origin = c("40.7193908691406 -73.9932174682617", 
"40.7641792297363 -73.9734268188477", "40.7507591247559 -73.9739990234375"
), destination = c("40.7096214294434-73.9497909545898", "40.7707366943359-73.9031448364258", 
"40.7711143493652-73.9871368408203")), .Names = c("origin", "destination"
), row.names = c(NA, 3L), class = "data.frame")

sql code is basic looks like such:

feed = sqlQuery(con, paste("select top 10
                             longitude as px,
                             latitude as py,
                             dlongitude as dx ,
                             dlatitude as dy,
                             from mydb"))

and then before feeding it my data frame feed looks like so (u can ignore departure i was using that for the distance api):

                origin                       destination           departure
1  40.7439613342285 -73.9958724975586  40.716911315918-74.0121383666992 2017-03-03 01:00:32
2  40.7990493774414 -73.9685516357422 40.8066520690918-73.9610137939453 2017-03-03 01:00:33
3  40.7406234741211 -74.0055618286133 40.7496566772461-73.9834671020508 2017-03-03 01:00:33
4  40.7172813415527 -73.9953765869141 40.7503852844238-73.9811019897461 2017-03-03 01:00:33
5  40.7603607177734 -73.9817123413086 40.7416114807129-73.9795761108398 2017-03-03 01:00:34
LoF10
  • 1,907
  • 1
  • 23
  • 64

1 Answers1

3

As you know the result of the API query returns a list. And if you're doing multiple calls to the API you'll return multiple lists.

So to extract the data of interest you have to do standard operations on lists. In this example it can be done with a couple of *applys

Using the data.frame feed where each row consists of an origin lat/lon (px/py) and a destination lat/lon (dx/dy)

feed <- data.frame(px = c(40.7193, 40.7641),
                   py = c(-73.993, -73.973),
                   dx = c(40.7096, 40.7707),
                   dy = c(-73.949, -73.903))

You can use an apply to query the google_directions() API for each row of the data.frame. And within the same apply you can do whatever you want with the result to extract/format it how you want.

lst <- apply(feed, 1, function(x){
    ## query Google Directions API
    res <- google_directions(key = key, 
                             origin = c(x[['px']], x[['py']]),
                             destination = c(x[['dx']], x[['dy']]))

    ## Decode the polyline
    df_route <- decode_pl(res$routes$overview_polyline$points)

    ## append the original coordinates as an 'id' column
    df_route[, "id"] <- paste0(paste(x[['px']], x[['py']], sep = "+")
                                ," "
                                , paste(x[['dx']], x[['dy']], sep = "+")
                                , collapse = " ")

    ## store the results of the query, the decoded polyline, 
    ## and the original query coordinates in a list
    lst_result <- list(route = df_route, 
                       full_result = res, 
                       origin = c(x[['px']], x[['py']]), 
                       destination = c(x[['dx']],x[['dy']]))

    return(lst_result)

})

So now lst is a list that contains the result of each query, plus the decoded polyline as a data.frame. To get all the decoded polylines as a single data.frame you can do another lapply, and then rbind it all together

## do what we want with the result, for example bind all the route coordinates into one data.frame
df <- do.call(rbind, lapply(lst, function(x) x[['route']]))
head(df)
       lat       lon                              id
1 40.71938 -73.99323 40.7193+-73.993 40.7096+-73.949
2 40.71992 -73.99292 40.7193+-73.993 40.7096+-73.949
3 40.71984 -73.99266 40.7193+-73.993 40.7096+-73.949
4 40.71932 -73.99095 40.7193+-73.993 40.7096+-73.949
5 40.71896 -73.98981 40.7193+-73.993 40.7096+-73.949
6 40.71824 -73.98745 40.7193+-73.993 40.7096+-73.949
SymbolixAU
  • 25,502
  • 4
  • 67
  • 139
  • This is very helpful, thank you. Assuming a dataframe named "feed" with two columns, namely "origin" and "destination", how would I change that into a pair list as you have it? I tried pairlist() and setNames(as.list(feed$origin), feed$destination), but neither gave me the name output as yours and I'm new to working with lists. I've included the dput in edits above. – LoF10 Dec 13 '16 at 22:16
  • how do you generate the `feed` data.frame? It might be easier to change how it's created, rather than change it in its current state (as you have to separate each value into it's component lat/lon cooridnates) – SymbolixAU Dec 13 '16 at 22:34
  • Gotcha, well in my setup I'm pulling these coordinates from a sql data base as two columns so they default to a dataframe. Any suggestions for pulling to start with a list as you did? You mention you where using a list: "Using the data set feed (here I'm using a list of origin/destination pairs)" – LoF10 Dec 15 '16 at 16:39
  • A dataframw is fine to work with, however, in the database are the latitude and longitude values in the same colum? (they shouldn't be) – SymbolixAU Dec 15 '16 at 17:44
  • No they aren't in the same column. So then what I did was paste them together as pairs like so: -37.84317 144.93818. I do this for both origin and destination. Added another edit to show the dataframe – LoF10 Dec 15 '16 at 21:27
  • ok - you shouldn't paste them together: i'll update my answer with a `data.frame` solution – SymbolixAU Dec 15 '16 at 21:28
  • Thanks, appreciate the help – LoF10 Dec 15 '16 at 21:29
  • try it now - I've used the column names you've specified in your SQL query – SymbolixAU Dec 15 '16 at 21:40
  • awesome, i got the results you got, but I did notice that the function only runs the first line of the data frame. How do I output both rows of the data frame? – LoF10 Dec 21 '16 at 23:00
  • @LoF10 what do you mean by `output both rows of the data frame` ? – SymbolixAU Dec 21 '16 at 23:14
  • I mean output directions for both trips in the data frame feed. The data frame feed has two rows which designate two different trips as far as I understand it. However when you run your function it outputs the route directions for only the first route. Is there a way to use that function to extract routes for both route 1 and route 2. Thanks! – LoF10 Dec 28 '16 at 15:27
  • @LoF10 both results **are** there. Look at `lst[[1]]` and `lst[[2]]`. – SymbolixAU Dec 30 '16 at 22:19
  • my mistake, i meant when running do.call to turn into a data frame. Works well thank you – LoF10 Jan 13 '17 at 15:33
  • @LoF10 glad it's all sorted :) – SymbolixAU Jan 14 '17 at 02:22