Writing spatial data from R into MS SQL Server using sf::st

I am creating a database for a large monitoring project. I have already set up the schema on the database and am now trying to populate the tables. I am using R's DBI:: package to transfer data from R to SQL. I have been successful in transferring all datatype except for my spatial data. For the tabular data I have been using DBI::dbWriteTable() However, from scouring other posts, it seems like loading spatial data is better done using the sf::st_write() function in the sf:: package. However, I am getting several errors:

In my local instance of SQL Server, I am getting an error that I am not providing a valid instance of datatype geometry. The reproducible example below will throw this error.
On my network instance of SQL Server, I am getting an error Invalid object name 'spatial_ref_sys' Unfortunately, I was unable to reproduce this error with example data.

N.B.: In the code below, you will need to replace the name of your local instance of sql server in the connection strings

##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)

My goal at the end of the day is simply to add the spatial data in geom to the geom column in test.dbo.states I am open to other avenues that might accomplish this. Thanks in advance for any help. Take Care, -Sean

CodePudding user response：

After much tinkering I think I found the solution. Admittedly it's a bit of a workaround, but it isn't too ugly. Instead of trying to write a single column, I wrote the entire table using sf::st_write(). Importantly, while I could not find a way to write geometries directly into SQL, I found out that I could write a Well-Known-Text to SQL. Once it was in the SQL database I used the geometery::STGeomFromText() stored procedure to convert from WKT to geometry. Below is the updated code:

N.B.: Change the server to the name of your sql server instance in the connection strings below for reproducibility

##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