CODEWITHSUNDEEP
rdistanceggmap
Rick Pack
Rick Pack

Reputation: 1074

Distance between unequal number of locations in R (latitude and longitude are inputs)

How do I calculate the distance between each location in one data frame versus each in another when they have a different number of rows?

For example, say I have dataframe A with 3 rows and dataframe B with 4 rows. These are from the SNAP (United States federal food / beverage assistance for low-income households) Retailer Locator :

A
      Store_Name     Longitude Latitude
1     Food Lion 2213 -80.86581 35.59477
2 THE CORNER GROCERY -81.09917 35.26776
3          FISH WING -80.88245 35.21639


B
         Store_Name     Longitude Latitude
1    SUPERIOR GROCERIES -79.80839 35.73597
2     MORVEN TRUCK STOP -80.01122 34.88312
3       GREENHILL STORE -81.99146 35.34768
4 NORTHSIDE FOOD MARKET -77.94242 34.24158

Here are some failed attempts:

mapdist(as.character(a), as.character(b)) </code>

YIELDS: from and to entries that resemble the following and only one distance calculation that is possibly useful: c(35.594765, 35.267761, 35.216393)

distcomp <- mapdist(from = c(lon = as.character(a$Longitude), lat = as.character(a$Latitude)), to = c(lon = as.character(b$Longitude),   lat = as.character(b$Latitude)), mode = "driving")

YIELDS: Error <code> arguments imply differing number of rows: 6, 8

# row-bind the rows even though this would mean extra work so that I could only have the distances from those in <code>a</code> to those in <code>b</code>:

c <- rbind(a,b)
distcomp <- mapdist(from = c(lon = as.character(c$Longitude), lat = as.character(c$Latitude)), 
                     to = c(lon = as.character(c$Longitude),   lat = as.character(c$Latitude)), mode = "driving")

YIELDS: A bunch of NAs in the results. Nothing helpful.

Upvotes: 3

Views: 207

Answers (1)

SymbolixAU
SymbolixAU

Reputation: 26248

You can merge the tables together so that every row of each table is joined to every row on the other table (often referred to as a full-join), and then calculate the distances between each set of lat/lon coordinates.

In your question it appears you're asking for the driving distance, as given by Google Maps. Here I'm giving both the driving and the haversine distance.

Joining the data

## create a key column to join on
A$key <- 1
B$key <- 1

## Join together
AB <- merge(A, B, by = "key")

Distance Calculation - haversine

## Here I'm using the haversine distance calculation from geosphere
library(geosphere)

AB$distance <- distHaversine(p1 = matrix(c(AB$Longitude.x, AB$Latitude.x), ncol = 2),
                             p2 = matrix(c(AB$Longitude.y, AB$Latitude.y), ncol = 2))


head(AB)
#   key     Store_Name.x Longitude.x Latitude.x        Store_Name.y Longitude.y Latitude.y  distance
# 1   1     FoodLion2213   -80.86581   35.59477   SUPERIORGROCERIES   -79.80839   35.73597  96915.65
# 2   1     FoodLion2213   -80.86581   35.59477     MORVENTRUCKSTOP   -80.01122   34.88312 110963.56
# 3   1     FoodLion2213   -80.86581   35.59477      GREENHILLSTORE   -81.99146   35.34768 105691.88
# 4   1     FoodLion2213   -80.86581   35.59477 NORTHSIDEFOODMARKET   -77.94242   34.24158 306403.99
# 5   1 THECORNERGROCERY   -81.09917   35.26776   SUPERIORGROCERIES   -79.80839   35.73597 128061.51
# 6   1 THECORNERGROCERY   -81.09917   35.26776     MORVENTRUCKSTOP   -80.01122   34.88312 107968.35

Distance calculation - Google Maps driving

To use Google maps distances you can use mapdist from ggmap as you were doing (but for some reason I could only get it to work in an apply function)

library(ggmap)

mdist <- apply(AB, 1, function(x){
    f = as.numeric(c(x[['Longitude.x']], x[['Latitude.x']]))
    t = as.numeric(c(x[['Longitude.y']], x[['Latitude.y']]))
    mapdist(f, t)
})

AB$mapDist <- do.call(rbind, mdist)

head(AB)
# key     Store_Name.x Longitude.x Latitude.x        Store_Name.y Longitude.y Latitude.y  distance
# 1   1     FoodLion2213   -80.86581   35.59477   SUPERIORGROCERIES   -79.80839   35.73597  96915.65
# 2   1     FoodLion2213   -80.86581   35.59477     MORVENTRUCKSTOP   -80.01122   34.88312 110963.56
# 3   1     FoodLion2213   -80.86581   35.59477      GREENHILLSTORE   -81.99146   35.34768 105691.88
# 4   1     FoodLion2213   -80.86581   35.59477 NORTHSIDEFOODMARKET   -77.94242   34.24158 306403.99
# 5   1 THECORNERGROCERY   -81.09917   35.26776   SUPERIORGROCERIES   -79.80839   35.73597 128061.51
# 6   1 THECORNERGROCERY   -81.09917   35.26776     MORVENTRUCKSTOP   -80.01122   34.88312 107968.35
# mapDist.from                                       mapDist.to mapDist.m
# 1 507-543 NC-150, Mooresville, NC 28117, USA 1504N N Fayetteville St, Asheboro, NC 27203, USA    118648
# 2 507-543 NC-150, Mooresville, NC 28117, USA                2876 US-52, Morven, NC 28119, USA    135294
# 3 507-543 NC-150, Mooresville, NC 28117, USA   136 Firethorn Ln, Rutherfordton, NC 28139, USA    149211
# 4 507-543 NC-150, Mooresville, NC 28117, USA      603 Red Cross St, Wilmington, NC 28401, USA    359219
# 5  101 McAdenville Rd, Lowell, NC 28098, USA 1504N N Fayetteville St, Asheboro, NC 27203, USA    160581
# 6  101 McAdenville Rd, Lowell, NC 28098, USA                2876 US-52, Morven, NC 28119, USA    137167
# mapDist.km mapDist.miles mapDist.seconds mapDist.minutes mapDist.hours
# 1    118.648      73.72787            5049        84.15000      1.402500
# 2    135.294      84.07169            5574        92.90000      1.548333
# 3    149.211      92.71972            5812        96.86667      1.614444
# 4    359.219     223.21869           13264       221.06667      3.684444
# 5    160.581      99.78503            5782        96.36667      1.606111
# 6    137.167      85.23557            5639        93.98333      1.566389

Upvotes: 3

Related Questions