r - 使用 sf::st_write() 将空间数据从 R 写入 MS SQL Server

Question

我正在为一个大型监控项目创建一个数据库。我已经在数据库上设置了架构，现在正在尝试填充表。我正在使用 R 的 DBI::将数据从 R 传输到 SQL 的包。我已成功传输除空间数据以外的所有数据类型。对于表格数据，我一直在使用 DBI::dbWriteTable()然而，从搜索其他帖子来看，加载空间数据似乎最好使用 sf::st_write() 来完成。 sf:: 中的函数包裹。但是，我遇到了几个错误:

1. 在我的本地 SQL Server 实例中，我收到一条错误消息，指出我没有提供有效的数据类型几何实例。下面的可重现示例将抛出此错误。

2. 在我的 SQL Server 网络实例上，出现错误 Invalid object name 'spatial_ref_sys'不幸的是，我无法使用示例数据重现此错误。

注意:在下面的代码中，您需要在连接字符串中替换您本地的 sql server 实例的名称

##Loading Necessary Packages##
library(DBI)
library(sf)
library(spData)

##Getting Example Data from R's spData package##
data("us_states")

##Creating a test database in a local instance of 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)

##Changing the connection string to connect directly to test database##
con2<-dbConnect(odbc::odbc(), .connection_string="driver={SQL Server Native Client 11.0};
               server=localsqlserver;database=test;trusted_connection=yes")

##Writing tabular data to new table in test##
DF<-us_states_df
dbWriteTable(con2, "States", DF)

##Adding a column for spatial data##
dbSendQuery(con2, "ALTER TABLE dbo.States ADD geom geometry")

##Writing spatial data to new column##
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 中到 geom test.dbo.states 中的列我对可能实现这一目标的其他途径持开放态度。在此先感谢您的帮助。小心，-肖恩

Answer 1

经过多次修改，我想我找到了解决方案。不可否认，这是一种变通方法，但它并不太难看。我没有尝试编写单个列，而是使用 sf::st_write() 编写了整个表。重要的是，虽然我找不到将几何图形直接写入 SQL 的方法，但我发现我可以将 Well-Known-Text 写入 SQL。一旦它进入 SQL 数据库，我就使用 geometery::STGeomFromText() 存储过程将 WKT 转换为几何。以下是更新后的代码:

注意:在下面的连接字符串中将服务器更改为您的 sql server 实例的名称以实现重现性

##Loading Necessary Packages##
library(DBI)
library(sf)
library(spData)

##Getting Example Data from R's spData package##
data("us_states")

##Creating a test database in a local instance of 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)

##Changing the connection string to connect directly to test database##
con2<-dbConnect(odbc::odbc(), .connection_string="driver={SQL Server Native Client 11.0};
               server=localsqlserver;database=test;trusted_connection=yes")

##Writing tabular data to new table in test##
DF<-as.data.frame(us_states)
geom<-DF$geometry
DF[,"geom"]<-st_as_text(st_transform(geom,"+init=epsg:2992"))

##Writing table to database##
dbWriteTable(con2, Id(schema="dbo", table="States"), DF[,-7])

##Writing a SQL Statement to create new column with geometry datatype##
##Adding a column for spatial data##
dbSendQuery(con2, "ALTER TABLE dbo.States ADD geom2 geometry")

##Writing spatial data to new column##
dbSendQuery(con2, "UPDATE dbo.States
            Set geom2 = geometry::STGeomFromText(geom, 2992)")

##Dropping the WKT column##
dbSendQuery(con2, "ALTER TABLE dbo.States
            DROP COLUMN geom")

##View the results##
DB<-dbGetQuery(con2, "SELECT * FROM dbo.States")
DB