duckdb/sf

.R

``` r
library(sf)
#> Linking to GEOS 3.12.1, GDAL 3.8.0, PROJ 9.2.0; sf_use_s2() is TRUE
library(duckdb)
#> Loading required package: DBI

con <- dbConnect(duckdb())

dbExecute(con, "INSTALL spatial; LOAD spatial;")
#> [1] 0

nc <- st_read(system.file("shape/nc.shp", package = "sf"))[c("NAME", "AREA", "geometry")]
#> Reading layer `nc' from data source 
#>   `/home/filoche/R/x86_64-pc-linux-gnu-library/4.3/sf/shape/nc.shp' 
#>   using driver `ESRI Shapefile'
#> Simple feature collection with 100 features and 14 fields
#> Geometry type: MULTIPOLYGON
#> Dimension:     XY
#> Bounding box:  xmin: -84.32385 ymin: 33.88199 xmax: -75.45698 ymax: 36.58965
#> Geodetic CRS:  NAD27
nc
#> Simple feature collection with 100 features and 2 fields
#> Geometry type: MULTIPOLYGON
#> Dimension:     XY
#> Bounding box:  xmin: -84.32385 ymin: 33.88199 xmax: -75.45698 ymax: 36.58965
#> Geodetic CRS:  NAD27
#> First 10 features:
#>           NAME  AREA                       geometry
#> 1         Ashe 0.114 MULTIPOLYGON (((-81.47276 3...
#> 2    Alleghany 0.061 MULTIPOLYGON (((-81.23989 3...
#> 3        Surry 0.143 MULTIPOLYGON (((-80.45634 3...
#> 4    Currituck 0.070 MULTIPOLYGON (((-76.00897 3...
#> 5  Northampton 0.153 MULTIPOLYGON (((-77.21767 3...
#> 6     Hertford 0.097 MULTIPOLYGON (((-76.74506 3...
#> 7       Camden 0.062 MULTIPOLYGON (((-76.00897 3...
#> 8        Gates 0.091 MULTIPOLYGON (((-76.56251 3...
#> 9       Warren 0.118 MULTIPOLYGON (((-78.30876 3...
#> 10      Stokes 0.124 MULTIPOLYGON (((-80.02567 3...

duckdb_register(con, "nc", nc, overwrite = TRUE)

res <- dbSendQuery(con, "SELECT * from nc;") |>
  dbFetch() |>
  tibble::as_tibble()

res
#> # A tibble: 100 × 3
#>    NAME         AREA geometry  
#>    <chr>       <dbl> <list>    
#>  1 Ashe        0.114 <list [1]>
#>  2 Alleghany   0.061 <list [1]>
#>  3 Surry       0.143 <list [1]>
#>  4 Currituck   0.07  <list [3]>
#>  5 Northampton 0.153 <list [1]>
#>  6 Hertford    0.097 <list [1]>
#>  7 Camden      0.062 <list [1]>
#>  8 Gates       0.091 <list [1]>
#>  9 Warren      0.118 <list [1]>
#> 10 Stokes      0.124 <list [1]>
#> # ℹ 90 more rows

# This is working when executing a query on a spatial table created within
# duckdb
st_read(
  con,
  query = "SELECT * FROM nc;",
  geometry_column = "geometry"
)
#> Error in st_as_sfc.WKB(as_wkb_(x), EWKB = EWKB): vapply(x, is.raw, TRUE) are not all TRUE

# A working example with a spatial object created withing duckdb directly
dbExecute(con, "CREATE OR REPLACE table test AS SELECT ST_asWKB(ST_Point(-100, 40)) as geom;")
#> [1] 1

st_read(
  con,
  query = "SELECT * FROM test;",
  geometry_column = "geom"
)
#> Simple feature collection with 1 feature and 0 fields
#> Geometry type: POINT
#> Dimension:     XY
#> Bounding box:  xmin: -100 ymin: 40 xmax: -100 ymax: 40
#> CRS:           NA
#>              geom
#> 1 POINT (-100 40)
```

<sup>Created on 2024-01-05 with [reprex v2.0.2](https://reprex.tidyverse.org)</sup>