Skip to contents

Register an sf object as a DuckDB virtual table

Source: R/sf_as_dbplyr.R
sf_as_dbplyr.Rd

A thin wrapper around duckdb::duckdb_register() that creates a virtual table, then casts the geometry column to DuckDB's GEOMETRY type in the returned dbplyr representation. Mostly useful for join and spatial operations within DuckDB. No data is copied.

Usage

sf_as_dbplyr(
  conn,
  name,
  sf_obj,
  geom_only = isFALSE(inherits(sf_obj, "sf")),
  overwrite = FALSE,
  ...
)

Arguments

conn

A DuckDB connection, created by dbConnect().

name

The name for the virtual table that is registered or unregistered

sf_obj

sf object to be registered to duckdb

geom_only

if TRUE, only the geometry column is registered. Always TRUE for sfc or sfg objects

overwrite

Should an existing registration be overwritten?

...

additional arguments passed to duckdb_register

Value

a dbplyr lazy table

Details

Behind the scenes, this function creates an initial view (name_init) with the geometry stored as well-known binary via sf::st_as_binary. It then creates the view name which replaces the geometry column with DuckDB's internal geometry type. DuckDB geometries carry no coordinate reference system; the geometry is registered in whatever system sf_obj uses.

Examples

if (FALSE) { # interactive()
library(sf)

con <- stage_conn()
sf_obj <- st_sf(a = 3, geometry = st_sfc(st_point(1:2)))
sf_as_dbplyr(con, "test", sf_obj)

DBI::dbDisconnect(con)
}