R Spatial Dataframes – Converting SQL Geometry Data Queries into R Spatial Dataframes

rsql-server-spatial

I'm interested in querying data with a geometry field from SQL server and using it in R as a spatialPolygonsDataFrame.

I've seen plenty of examples of using rgdal to import a shapefile, but shapefiles are far from the ideal storage medium for a map layer.

Can anyone point me to the correct syntax?

Does the geometry data have to be converted on it's way out of sql server in order for R to recognize it? to WKT or WKB for example?

In the end, I want a live connection to this data so that my maps are pulled directly from SQL and not from an exported shapefile.

Best Answer

The rgdal package can use any data format supported by the underlying OGR and GDAL libraries. You can see which drivers you have for vector data with:

 ogrDrivers()

In my case I can see:

           name                             long_name write  copy isVector
40 MSSQLSpatial Microsoft SQL Server Spatial Database  TRUE FALSE     TRUE

amongst them, which means I can probably read and write directly from an MS SQL Server Spatial Database.

To do that, you need the OGR dsn for your database. This is a string of the form "MSSQL:server=.\MSSQLSERVER2008;database=geodb;trusted_connection=yes" - where MSSQLSERVER2008 is an ODBC connection identifier, which you create with the Windows ODBC tool - this is where the host and database names are defined.

Examples here: http://r-sig-geo.2731867.n2.nabble.com/rgdal-and-MSSQL-Server-geometries-td7583193.html There seems to be some possible complications with how the database stores geometry, but I don't have a server to test on....

Related Question