Issue with SQL Server Driver provided by RStudio for spatial databases

[tbradley]

I am currently running RStudio Connect on Ubuntu 16.04 and am using the professional odbc drivers provided by RStudio. These have worked great for me when connecting to both PostgreSQL and MSSQL Server databases. However, these drivers seem to be causing an issue when it comes to reading in spatial data using gdal via the sf R package. To be clear, the issue is not with the sf package (as discussed here), as the same errors are seen in the command line using ogrinfo. Rather the issue appears to be with the driver. A reprex is hard given that the data is in a MSSQL Server database, but hopefully the below will illustrate the issue.

library(sf)
#> Linking to GEOS 3.7.1, GDAL 2.4.0, PROJ 5.2.0

# define drivers
driver_rstudio <- "SQL Server"
driver_microsoft <- "ODBC Driver 17 for SQL Server"

# define dsn values for sf
dsn_rstudio <- glue::glue("MSSQL:server=xxx;database=xxx;UID={Sys.getenv('uid')};PWD={Sys.getenv('pwd')};Driver={driver_rstudio}")
dsn_microsoft <- glue::glue("MSSQL:server=xxx;database=xxx;UID={Sys.getenv('uid')};PWD={Sys.getenv('pwd')};Driver={driver_microsoft}")

# Try reading in layer with RStudio's driver - FAILS
st_read(dsn_rstudio, "dbo.SampleSites")
#> Reading layer `dbo.SampleSites' from data source `MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;Driver=SQL Server' using driver `MSSQLSpatial'
#> Error in CPL_read_ogr(dsn, layer, query, as.character(options), quiet,  :
#>   NULL error in sfc_from_ogr
#> In addition: Warning message:
#> 2: In CPL_read_ogr(dsn, layer, query, as.character(options), quiet,  :
#>   GDAL Error 1: [00000](0)

# Try reading in layer with Microsoft Driver - SUCCESS
st_read(dsn_microsoft, "dbo.SampleSites")
#> Reading layer `dbo.SampleSites' from data source `MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;Driver=ODBC Driver 17 for SQL Server' using driver `MSSQLSpatial'
#> Simple feature collection with 92 features and 67 fields
#> geometry type:  POINT
#> dimension:      XY
#> bbox:           xmin: 2669698 ymin: 207794.9 xmax: 2742221 ymax: 297992
#> epsg (SRID):    NA
#> proj4string:    NA

This problem persists to all spatial layers. The driver is able to identify that there are spatial tables but when the spatial elements are actually queried, the driver fails. The same results are seen both with sf and the cmd line tool ogrinfo.

I am not sure where is the best place to post issues regarding the RStudio Drivers, but if there is a github page taht is more appropriate, I can redirect this there.

Thanks

[cole]

Thanks for sharing this! It's hard for me to tell if this is a driver issue or driver + package sync. If the latter, it should go on the package's repo. If the former, you can submit professional support tickets for the Pro Drivers!

In any case, apologies for taking so long to respond! Do you have any way to provide some sample data that we could use to test with? I.e. ideally it would be R code that we could execute that would create/populate the table (even a temp table) and reproduce the issue

[tbradley]

Unfortunately, I do not have write access to the SQL Server database and obviously it is not easy to create a dummy SQL Server database (as far as I know). I can potentially provide you with a sf object with dummy data that can be written to a database, but I don't know if I will be able to test whether it causes issues on my system. Would this be helpful?

When you say that it could be a driver+package sync issue, do you mean driver + gdal or driver + sf?

[cole]

Yes, that would definitely be helpful! Just a representative sf object that is some way similar to what caused the problem, if that's possible

And yes, by driver + package sync issue, I was thinking that if the driver is interpreting things in a way that is unexpected by the packages, it could be that the package needs to be patched. But to be honest, it feels more like a driver issue when it works for the MSFT driver and not for RStudio's

The other thing is if you have / can share the table spec (or something like it) from SQL Server, that could be helpful. There are no guarantees that writing the R data to the database will result in the same column types, for instance.