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

Question

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

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中的空间数据添加到test.dbo.states中的geom列中。我对其他可能实现这一目标的途径持开放态度。提前感谢任何帮助。当心肖恩

Answer 1

经过一番修修补补，我想我找到了解决办法。诚然，这是一种变通方法，但它并不太难看。我没有尝试编写单个列，而是使用sf::st_write()编写了整个表。重要的是，虽然我无法找到将几何图形直接写入SQL的方法，但我发现可以将一个众所周知的文本写入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