从R向DuckDB空间表插入数据时遇类型转换错误求助

问题重现 #

用DuckDB命令行创建带geometry列的表后，通过R的sf包插入空间数据时触发错误：

Error: rapi_execute: Failed to run query Error: Conversion Error: Unimplemented type for cast (DOUBLE[] -> GEOMETRY)

原因分析 #

dbAppendTable默认会把sf对象的几何列解析成Double数组（对应空间坐标的数值存储），但DuckDB的geometry列需要的是WKB（Well-Known Binary）格式的空间数据，直接传递数组类型无法完成转换。

解决方法 #

方法1：手动转换为WKB格式插入 #

把sf的几何列转成十六进制WKB字符串，通过SQL的ST_GeomFromWKB函数完成类型转换后插入：

library(duckdb)
library(sf)
library(tibble)

# 连接数据库并加载空间扩展
db <- dbConnect(duckdb(), "~/duck.db")
dbExecute(db,'install spatial;')
dbExecute(db,'load spatial;')

# 构造空间数据
pts <- st_as_sf(tibble(id=0:3,x=10:13,y=20:23),coords=c('x','y'),crs=4326)
st_geometry(pts) <- 'loc'

# 将几何列转为十六进制WKB字符串
pts$loc_wkb <- st_as_binary(pts$loc, hex = TRUE)

# 使用参数化查询批量插入
for (i in 1:nrow(pts)) {
  dbExecute(db, 
            "INSERT INTO test_spatial (id, loc) VALUES (?, ST_GeomFromWKB(?))",
            params = list(pts$id[i], pts$loc_wkb[i]))
}

# 断开连接
dbDisconnect(db)

方法2：用dbWriteTable指定字段类型 #

直接通过dbWriteTable并显式声明几何列的类型为GEOMETRY，让驱动自动处理格式转换：

library(duckdb)
library(sf)
library(tibble)

db <- dbConnect(duckdb(), "~/duck.db")
dbExecute(db,'install spatial;')
dbExecute(db,'load spatial;')

pts <- st_as_sf(tibble(id=0:3,x=10:13,y=20:23),coords=c('x','y'),crs=4326)
st_geometry(pts) <- 'loc'

# 追加数据到已有表，指定loc字段类型为GEOMETRY
dbWriteTable(db, "test_spatial", pts, append = TRUE, field.types = list(loc = "GEOMETRY"))

dbDisconnect(db)

验证数据 #

插入完成后可以在DuckDB命令行验证：

SELECT id, ST_AsText(loc) FROM test_spatial;

应该会输出每个点的WKT格式坐标，确认数据插入成功。

内容的提问来源于stack exchange，提问作者Ben Carlson