Search code examples
rsql-servershinyrgdal

Connect to Spatial SQL Tables from RStudio Connect Server

I have a shiny app which includes the following connection to a spatial table (containing data uploaded from an ESRI shapefile) in a SQL Server:

dsn <- paste0("MSSQL:server=host\\instance;",
              "database=database;",
              "UID=my_username;",
              "PWD=my_password;",
              "trusted_connection=no")

spdf <- rgdal::readOGR(dsn = dsn, layer = "my_spatial_table")

It works fine from my local Windows machine and retrieves the spatial data as expected.

When I publish this to RSConnect, the connection fails. I can however access the table itself and read it into an R session as tabular data (using the RODBC package, and much the same DSN, but without MSSQL at the front).

So my question is:

  1. Is it possible to read spatial data from tables in SQL Server from an RSConnect Server R session?

  2. If not, is there a way to turn the encoded spatial data from tabular form to a SpatialPointsDataFrame within R?

Versions

I'm using rgdal v1.2-15, R version 3.4.2, SQL Server 2016, RSConnect v1.5.8-12

Many thanks for any advice - let me know if more technical detail is needed.

Edit: To access the data in tabular form I use the 'ODBC Driver 13 for SQL Server' driver. I'm not sure if there's a different driver I should be using for spatial data.

Solution

  • Turns out the devil is in the detail - although the two machines are running the same versions of R and all R packages, they had different versions of the underlying GDAL and PROJ4 system components, which rgdal uses.

    Lesson: Be extra careful when using packages that rely on system components!

    It seems that GDAL v1.11.4 and PROJ4 v4.8.0 is insufficient for readOGR() to read spatial tables correctly off MS SQL Server.

    GDAL v2.2.0 and PROJ4 v4.9.3 is sufficient (but not perhaps necessary - haven't tested versions in between).