sf to duckdb via gpkg and back again

sf_to_duckdb.R

library(sf)
library(DBI)
library(duckdb)

# sf object
nc <- st_read(system.file("shape/nc.shp", package = "sf"))
nc_crs <- st_crs(nc)

# write it to local gpkg
path_gpkg <- here::here("local.gpkg")
st_write(nc, path_gpkg, layer = "nc")

# lauch in memory duckdb and install and load spatial ext
con <- dbConnect(duckdb())
dbExecute(con, "INSTALL spatial;")
dbExecute(con, "LOAD spatial;")

# create table in duckdb from gpkg
dbExecute(
  con,
  glue::glue_sql(
    "CREATE OR REPLACE TABLE nc
     AS SELECT * FROM ST_Read({path_gpkg}, layer = 'nc')",
    .con = con
  )
)

# read back into R with st_read passing the connection
# and a SQL query. note you can run any query on the table before 
# the data is brought into R as sf, including duckdb
# spatial operations. you just have to convert duckdb's
# geometry type to a WKB blob before reading so sf can
# understand and translate the geom. you also should explicity
# pass the crs of the data otherwise it will be NA
nc_duck <- st_read(
  con,
  query = "SELECT * EXCLUDE(geom), ST_AsWKB(geom) as geom FROM nc",
  geometry_column = "geom",
  crs = nc_crs
)

# close and shutdown duckdb
dbDisconnect(con, shutdown = TRUE)