I have a DF with about 1 million records. Each record includes latitude and longitude, and the records are grouped as in the example data (except with much larger groups)
data.frame(Latitude=c(-30.25,-30.89,-30.48,-30.10),
Longitude=c(116.321,116.98,116.78,116.38),
grp=c('a','a','b','b'))
Within each group I need to find the maximum distance between any two sets of coordinates. Once I have all combinations of coordinates in a DF with I can calculate distances no problems, but can not efficiently get each combination into a DF that looks something like this
data.frame(Latitude1=c(-30.25,-30.25,-30.89,-30.89,-30.48,-30.48,-30.10,-30.10),
Longitude1=c(116.321,116.32,116.98,116.98,116.78,116.78,116.38,116.38),
Latitude2=c(-30.25,-30.89,-30.25,-30.89,-30.48,-30.10,-30.48,-30.10),
Longitude2=c(116.321,116.98,116.98,116.321,116.78,116.38,116.38,116.78),
grp=c('a','a','a','a','b','b','b','b'))
I've written a nested loop to do this, but it is very slow and I'm, sure there is a better way. I looked at duplicating the columns and using expand.grid, but can find how to use it with multiple factors Any help would be appreciated. Thanks
If you're comfortable using development / non-released packages, I've written spatialdatatable to do efficient geo* calculations on data.table objects.
Here's a solution working on 100,000 rows of data. The steps are
library(data.table)
# devtools::install_github("SymbolixAU/spatialdatatable")
library(spatialdatatable)
## generate random data
lons <- sample(0:180, 1e5, replace = T)
lats <- sample(-90:1, 1e5, replace = T)
grp <- sample(letters, 1e5, replace = T)
df <- data.frame(lon = lons, lat = lats, grp = grp)
## set as a data.table object, and assign an 'id' to each point
setDT(df)
df[, id := .I]
## 1. join the df to itself to give all points to all other points
df <- df[
df
, on = "grp"
, nomatch = 0
, allow.cartesian = T
][id != i.id] ## remove points joined with themselves
## 2. calculate distances
df[, dist := spatialdatatable::dtHaversine(lat, lon, i.lat, i.lon)]
## 3. select greatest distance per group
df[ df[, .I[which.max(dist)], by = grp]$V1 ][order(grp)]
# lon lat grp id i.lon i.lat i.id dist
# 1: 1 0 a 27726 180 0 10996 19903920
# 2: 1 1 b 63425 180 -3 57218 19766508
# 3: 1 1 c 18255 177 -2 56 19556799
# 4: 0 -1 d 43560 179 0 8518 19857865
# 5: 178 -2 e 37485 0 0 34482 19700640
# 6: 1 -2 f 79879 180 1 70765 19857889
# 7: 178 1 g 84268 1 -3 44148 19614379
# 8: 178 -5 h 49310 1 1 1306 19459455
# 9: 0 1 i 92786 179 -2 55584 19857889
# 10: 180 0 j 92704 0 0 36757 20015115
# 11: 0 -1 k 75760 180 0 71050 19903920
# 12: 0 -1 l 42202 180 0 10839 19903920
# 13: 0 1 m 73069 177 -2 2708 19663598
# 14: 0 1 n 10830 180 -1 1236 20015115
# 15: 3 -2 o 43380 180 1 3829 19663598
# 16: 179 1 p 95740 0 -1 3061 19903937
# 17: 0 -1 q 49476 180 0 18257 19903920
# 18: 180 0 r 96154 1 0 42435 19903920
# 19: 180 -1 s 82115 1 0 47784 19857865
# 20: 178 -2 t 42861 0 0 22020 19700640
# 21: 180 0 u 22965 0 -1 12158 19903920
# 22: 178 0 v 18557 0 -2 17457 19700640
# 23: 178 -2 w 58321 1 -1 13906 19543390
# 24: 0 -1 x 93181 177 -3 67084 19459211
# 25: 0 -1 y 46491 178 1 5548 19792759
# 26: 3 1 z 43109 180 -3 769 19614379
library(geosphere)