R - Matching two datasets using closest lat / lon coordinates

Question

Edit: I tried the following nested loop but I keep getting errors:

install.packages("hutilscpp")
library(hutilscpp)

# Initialize an empty list to store the matching results
matching_results <- list()

# Nested for loop to match each point in dataset1 with dataset2
for (i in 1:nrow(dataset)) {
  min_distance <- Inf
  matching_row <- NULL
  
  for (j in 1:nrow(dataset2)) {
    distance <- match_nrst_haversine(dataset[i, c(lat lon)], dataset2[j, c(lat2 lon2)])
    
    # Update the minimum distance and matching row if a closer point is found
    if (distance < min_distance) {
      min_distance <- distance
      matching_row <- dataset2[j, ]
    }
  }
  
  matching_results[[i]] <- cbind(dataset[i, ], matching_row)
}

I get the following error:

Error: unexpected symbol in:
"  for (j in 1:nrow(dataset2)) {
    distance <- match_nrst_haversine(dataset[i, c(lat lon"

I tried different syntax but nothing has worked. Thanks again.

Original question:

I have two datasets, one is a household survey with geolocations and the other is a climate dataset. I took a screenshot to illustrate, the points are the households, that fall within the climate data grid.

As you can see, the latitudes and longitudes are not the same. How can I merge them in R while keeping the original size of the household dataset (so with duplicates)?

My household dataset looks like this:

lat         lon         year    hhid    indiv
5.535456    7.531536    2010    10001   4
5.535456    7.531536    2010    10001   5
5.535456    7.531536    2010    10001   2
5.535456    7.531536    2010    10001   1
5.535456    7.531536    2010    10001   6
5.535456    7.531536    2010    10001   7
5.535456    7.531536    2010    10001   3


And here is the climate data: 


| lat      | lon      |SPEI
| -------- | -------- |-------
| 4.25     | 13. 25   |1.14703
| 4.75     | 13. 25   |0.961421



The final dates would look like this: 

lat         lon         year    hhid    indiv  SPEI
5.535456    7.531536    2010    10001   4      1.14703
5.535456    7.531536    2010    10001   5      1.14703
5.535456    7.531536    2010    10001   2      1.14703
5.535456    7.531536    2010    10001   1      1.14703

Yes, sure. This was just to illustrate the result I have in mind, not an actual approximation. — AS1, Jun 14 '23 at 22:01

score 0 · Answer 1 · answered Jun 14 '23 at 21:32

The most precise way is to use some form of triangulation to find the most proximal point from your original dataframe, there are many ways to to it, my choice would be using the package hutilscpp

Probably u iterate through the dataframe you are trying to match to your existing points one at a time using:

require(hutilscpp)
match_nrst_haversine(
      lat,
      lon,
      lat2,
      lon2)

You could use a nested for loop to evaluate each point against all other points, keeping the point with the minimum distance.

Thank you! I tried the nested loop in my edit but not sure if this is correct. — AS1, Jun 17 '23 at 00:59

score 0 · Answer 2 · answered Jun 14 '23 at 22:45

We could use fuzzyjoin::geo_left_join to get all the matches within a max distance (1000 miles here) and then pick the closest match for each location.

library(dplyr)
hh |>
  fuzzyjoin::geo_left_join(climate, max_dist = 1000, distance_col = "dist") |>
  slice_min(dist, by = c(lat.x, lon.x))

R - Matching two datasets using closest lat / lon coordinates

2 Answers2