How to use the spatial extension from R?

[Robinlovelace]

I'm looking at this https://duckdb.org/docs/api/r.html and am wondering how to enable DuckDB's spatial extension from the R side?

Context: I'm an experienced R person and recently mentioned DuckDB in these course notes I'm putting together for OpenGeoHub, note there are some benchmarks here that may be of interest and if I can get DuckDB's spatial extension working I can try to add DuckDB to the benchmarks: https://ogh23.robinlovelace.net/tidy

[cboettig]

Hi @Robinlovelace ,

Running

  status <- DBI::dbExecute(conn, "INSTALL 'spatial';")
  status <- DBI::dbExecute(conn, "LOAD 'spatial';")

Should do it.

by the way, I have a thin R wrapper around duckdb which tries to make some of this less verbose, e.g. say you want to use duckdb spatial filters + dplyr filters on a remote csv:

library(duckdbfs)
  spatial_ex <- paste0("https://raw.githubusercontent.com/cboettig/duckdbfs/",
                     "main/inst/extdata/spatial-test.csv") |>
  open_dataset(format = "csv") 

spatial_ex |>
  mutate(geometry = ST_Point(longitude, latitude)) |>
  mutate(dist = ST_Distance(geometry, ST_Point(0,0))) |> 
  filter(site_id %in% c("a", "e") )
  to_sf()

curious what you (or other duckdb / R folks) think! https://github.com/cboettig/duckdbfs/

I also find this can be particularly compelling combined with large (geo)parquet data on S3.

[Robinlovelace]

Amazing, thanks for the reply @cboettig, will give duckdbfs a spin! And seeing as this is all nascent will link to this from the teaching materials..