如何使用 R 和 duckdb 读取 CSV 文件并将其转换为 sf 对象

我正在探索新的空间扩展--duckdb，并且正在try 读取CSV文件并将其转换为SF对象.

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(sf)
#> Linking to GEOS 3.11.1, GDAL 3.7.1, PROJ 9.2.0; sf_use_s2() is TRUE
library(duckdb)
#> Loading required package: DBI

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


dbExecute(conn, "CREATE OR REPLACE TABLE df AS FROM
  read_csv_auto('https://raw.githubusercontent.com/cboettig/duckdbfs/main/inst/extdata/spatial-test.csv')")
#> [1] 10

dbSendQuery(conn, "SHOW df;") |>
  dbFetch()
#>   column_name column_type null  key default extra
#> 1        site     VARCHAR  YES <NA>    <NA>    NA
#> 2    latitude      BIGINT  YES <NA>    <NA>    NA
#> 3   longitude      BIGINT  YES <NA>    <NA>    NA

在这里，我创建了几何柱，看起来它起作用了.

dbExecute(conn, "CREATE OR REPLACE TABLE df_sf AS SELECT *,
  ST_Point(latitude, longitude) as geometry, FROM df;")
#> [1] 10

dbSendQuery(conn, "SHOW df_sf;") |>
  dbFetch()
#>   column_name column_type null  key default extra
#> 1        site     VARCHAR  YES <NA>    <NA>    NA
#> 2    latitude      BIGINT  YES <NA>    <NA>    NA
#> 3   longitude      BIGINT  YES <NA>    <NA>    NA
#> 4    geometry    GEOMETRY  YES <NA>    <NA>    NA

我的问题是，如何将df_sf表读入R作为sf对象？

st_read(
  conn,
  query = "SELECT * FROM df_sf;",
  geometry_column = "geometry"
)
#> Error in CPL_read_wkb(x, EWKB, spatialite): reading wkb type 0 is not supported

# Any pointers would be greatly appreciated.

^{创建于2023-09-12年第reprex v2.0.2页}