使用sf::st_write()将R中空间数据写入MS SQL Server的问题求助

背景 #

我正在为大型监测项目搭建数据库，已完成数据库schema配置，当前需要向表中录入数据。我使用R的DBI::包实现R到SQL Server的数据传输，除空间数据外其余数据类型均写入成功，表格数据一直通过DBI::dbWriteTable()完成写入。

参考技术社区相关内容后，我了解到空间数据更适合通过sf::包的sf::st_write()函数写入，但运行时遇到两类报错：

• 本地SQL Server实例报错，提示未提供geometry数据类型的有效实例，下方可复现示例会触发该错误；
• 网络部署的SQL Server实例报错Invalid object name 'spatial_ref_sys'，暂无法用示例数据复现该问题。

注意：下方代码的连接字符串中，SQL Server本地实例名称需替换为你自己的实例名。

复现代码 #

## 加载所需包
library(DBI)
library(sf)
library(spData)

## 从R的spData包获取示例数据
data("us_states")

## 在本地MS SQL Server实例中创建测试数据库
con<-dbConnect(odbc::odbc(), .connection_string="driver={SQL Server Native Client 11.0};
               server=localsqlserver;trusted_connection=yes")

dbSendQuery(con, "CREATE DATABASE test;")
dbDisconnect(con)

## 修改连接字符串直接连接到测试数据库
con2<-dbConnect(odbc::odbc(), .connection_string="driver={SQL Server Native Client 11.0};
               server=localsqlserver;database=test;trusted_connection=yes")

## 将表格数据写入测试库的新表
DF<-us_states_df
dbWriteTable(con2, "States", DF)

## 新增空间数据列
dbSendQuery(con2, "ALTER TABLE dbo.States ADD geom geometry")

## 写入空间数据到新列
geom_tmp<-us_states$geometry
geom<-st_transform(geom_tmp, "+init=epsg:2992")
st_write(obj=geom, dsn=con2, layer = Id(schema="dbo", table="States"), driver="MSSQLSpatial", append=TRUE)

需求 #

将geom中的空间数据写入test.dbo.states表的geom列，也欢迎提供其他可实现该需求的方案，提前感谢大家的帮助。

内容的提问来源于stack exchange，提问作者Sean McKenzie