Skip to content

Instantly share code, notes, and snippets.

@PaulC91
Created February 18, 2024 10:16
Show Gist options
  • Save PaulC91/3788bbf2251bb4375fff85be82d3eb0f to your computer and use it in GitHub Desktop.
Save PaulC91/3788bbf2251bb4375fff85be82d3eb0f to your computer and use it in GitHub Desktop.
sf to duckdb via gpkg and back again
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment