[R-sig-DB] [R-sig-Geo] R and MS SQL Spatial

[Barry Rowlingson]

On Fri, Dec 6, 2013 at 9:08 AM, Domagoj Culinovic
<culinovic.domagoj using gmail.com> wrote:
> I have a lot of spatial and statistical data stored in MS SQL Spatial. Now
> i am using web-gis server to produce thematic maps, but for better analyses
> i need R.
> Can someone help me to find solution for MS SQL Spatial. Some examples code
> will be welcome too, because i am new in R.

Access to spatial data in R is best done using the rgdal package,
which uses the C library GDAL to access different types of spatial
data store - shapefiles, Mapinfo files, GML etc.

If you can install GDAL with support for MS SQL Spatial then you can
read your data using `readOGR` from the `rgdal` package. Install the
rgdal package, do require(rgdal);ogrDrivers() to get a list of drivers
that your rgdal package has. If it includes MSSQLSpatial then you may
be sorted. Mine does, and I'm not even running Windows, which
surprised me and I fell off my chair:

 > any(ogrDrivers()$name == "MSSQLSpatial")
 [1] TRUE

The next problem is simply working out the exact form of the
connection string (the "dsn") to the database so that it can pass your
server name, username, and password, and database name. The GDAL guide
might help:

http://www.gdal.org/ogr/drv_mssqlspatial.html

but I don't have a server I can experiment with.

[Someone has just suggested switching to MySQL, which is probably a
bit pointless - you'd be better off switching to PostGIS]

Barry