Offline geocoding in R
The R package <code>gridpin</code> geocodes a vector or data frame of addresses on your machine; the duckdb package with the GridPin extension does the same in one SQL query. No API key, no service, and no address leaves your computer.
The R package
install.packages("gridpin", repos = c("https://gridpin.r-universe.dev", "https://cloud.r-project.org"))
library(gridpin)
gp_download("us") # once: fetched and checked against the release checksum
gp_geocode(c("1600 Pennsylvania Ave NW, Washington, DC 20500",
"233 S Wacker Dr, Chicago, IL 60606"), "us")
R-universe serves binary packages for Windows and macOS, which need no Rust; installing from source builds the Rust code and needs cargo and rustc 1.87 or newer. Answers carry precision, confidence and weak; sf = TRUE returns sf points, gp_geocode_df adds columns to a data frame and gp_reverse finds the nearest address. Reference and vignettes: R-universe. The rest of this page does the same through DuckDB.
Or with DuckDB: install
install.packages(c("duckdb", "sf"))
The duckdb R package carries its own DuckDB with the same version number: packageVersion("duckdb"). The community catalog serves the current release on DuckDB 1.5.6 or newer; older DuckDB versions install an older build. Check what you got with SELECT extension_version FROM duckdb_extensions();. CRAN builds new duckdb versions only for current R releases, so an old R stays on an old one (R 4.3 gets 1.3.2, for which there is no GridPin build at all); update R if yours is older.
Download a country once
# once: the United States file, 1.45 GB, checked against the release checksum
url <- "https://dl.gridpin.dev/v0.7.1/usa.bin"
dest <- file.path(tools::R_user_dir("gridpin", "cache"), "v0.7.1", "usa.bin")
dir.create(dirname(dest), recursive = TRUE, showWarnings = FALSE)
options(timeout = 3600)
download.file(url, dest, mode = "wb")
stopifnot(tools::sha256sum(dest) == "4f3422f4d95939aefbcf454ceebc9a8dd16f78f2a13c5e6660028ddfad712cc0")
The file is the same one gridpin-fetch us downloads for Python. tools::sha256sum needs R 4.5 or newer; on older R use digest::digest(dest, "sha256", file = TRUE). Other countries and file sizes: guides.
Geocode a data frame
library(DBI)
library(duckdb)
con <- dbConnect(duckdb())
dbExecute(con, "INSTALL gridpin_ext FROM community")
dbExecute(con, "LOAD gridpin_ext")
dbGetQuery(con, "SELECT gridpin_load(?)", params = list(dest))
orders <- data.frame(id = 1:3, address = c("1600 Pennsylvania Ave NW, Washington, DC 20500",
"233 S Wacker Dr, Chicago, IL 60606",
"Nowhere Street 99999, Atlantis"))
dbWriteTable(con, "orders", orders)
res <- dbGetQuery(con, "
SELECT id, hit ->> 'precision' AS precision,
(hit ->> 'lat')::DOUBLE AS lat, (hit ->> 'lon')::DOUBLE AS lon, hit ->> 'street' AS street
FROM (SELECT id, gridpin_geocode(address)::JSON AS hit FROM orders) ORDER BY id")
res
id precision lat lon street 1 1 house 38.89768 -77.03655 PENNSYLVANIA Avenue Northwest 2 2 house 41.87866 -87.63587 South WACKER Drive 3 3 <NA> NA NA <NA>
Output from R 4.6.2 with duckdb 2.0.0, which installs GridPin extension 0.5.0.
An address that is not found stays in the table with missing coordinates, not a guess. precision says how close the answer is: house is a door-level match, street and city are approximate. The whole answer is JSON; take more fields with ->>: housenumber, postcode, commune, confidence.
Points for sf
pts <- sf::st_as_sf(res, coords = c("lon", "lat"), crs = 4326, na.fail = FALSE)
Rows without coordinates become empty points. Reverse geocoding works the same way with gridpin_reverse(lat, lon); see geocode inside DuckDB.