rgdal and MSSQL Server geometries

Hi Shannon,

The following syntax has worked for me for the past year.

projstring <- CRS('+init=epsg:28355')

##Establish the dsn
# Note: use the odbc tool in Windows to create a dsn for your SQL Server
database beforehand
myMSSQLdsn <-
c("MSSQL:server=mysqlservername;database=mydatabase;trusted_connection=yes")

#Confirm connection is working
ogrListLayers(myMSSQLdsn)

#Reading sp object classes from SQL SERVER
lyr <- c("mssqlserverTablename")
spdf <-readOGR(dsn=myMSSQLdsn, layer=lyr,p4s=CRSargs(projstring))


#Writing sp object classes to SQL SERVER
lyrout= c("NewMSSQLSpatialTableName")
writeOGR(spdfname, dsn=myMSSQLdsn, layer=lyrout, driver="MSSQLSpatial",
layer_options=c("SRID=28355"))


If you haven't created a dsn, this could be a big part of your problem.

Craig



--
View this message in context: http://r-sig-geo.2731867.n2.nabble.com/rgdal-and-MSSQL-Server-geometries-tp7583193p7583203.html
Sent from the R-sig-geo mailing list archive at Nabble.com.

Craig, thanks for the idea. I did create a DSN using ODBC as well as using an explicit string connection. Both give me the same result of: 'Cannot open data source'

Thus, I am thoroughly confused because I can connect to the SQL Server via all of my other methods (RODBC) and software with no issues. I am wondering if there is something with rgdal that is having a miscommunication with the proper drivers. If so, I really do not know how to solve the issue. Hopefully some other ideas will surface from the r-sig-geo list that can provide additional troubleshooting that I can try. 

Cheers,
Shannon

-----Original Message-----
From: r-sig-geo-bounces at r-project.org [mailto:r-sig-geo-bounces at r-project.org] On Behalf Of cmundy
Sent: Thursday, April 04, 2013 7:52 AM
To: r-sig-geo at r-project.org
Subject: Re: [R-sig-Geo] rgdal and MSSQL Server geometries

Hi Shannon,

The following syntax has worked for me for the past year.

projstring <- CRS('+init=epsg:28355')

##Establish the dsn
# Note: use the odbc tool in Windows to create a dsn for your SQL Server database beforehand myMSSQLdsn <-
c("MSSQL:server=mysqlservername;database=mydatabase;trusted_connection=yes")

#Confirm connection is working
ogrListLayers(myMSSQLdsn)

#Reading sp object classes from SQL SERVER lyr <- c("mssqlserverTablename") spdf <-readOGR(dsn=myMSSQLdsn, layer=lyr,p4s=CRSargs(projstring))


#Writing sp object classes to SQL SERVER lyrout= c("NewMSSQLSpatialTableName") writeOGR(spdfname, dsn=myMSSQLdsn, layer=lyrout, driver="MSSQLSpatial",
layer_options=c("SRID=28355"))


If you haven't created a dsn, this could be a big part of your problem.

Craig



--
View this message in context: http://r-sig-geo.2731867.n2.nabble.com/rgdal-and-MSSQL-Server-geometries-tp7583193p7583203.html
Sent from the R-sig-geo mailing list archive at Nabble.com.

_______________________________________________
R-sig-Geo mailing list
R-sig-Geo at r-project.org
https://stat.ethz.ch/mailman/listinfo/r-sig-geo

Hi Shannon,

The other requirement to read tables from MS SQL Server is that details of
the table with geometry columns must be registered in a table called
geometry_columns in your database. If your database doesn't have this table,
do the following;

1)  write a small point or polygon object back to SQL Server using writeOGR. 
2) This process will create two additional tables, the geometry_columns
table and a spatial_ref_sys table.
3) open the geometry_columns table in SQL Server, and create a new record
with the details of the table you want to read into R, following the pattern
of the record created by the write process.
4) try reading the table again

I'm out of the office for a day or so, but happy to help work this out. I
use rgdal to exchange spatial data between  R and SQL Server on a weekly
basis, so can at least confirm that it works very nicely.

Craig



--
View this message in context: http://r-sig-geo.2731867.n2.nabble.com/rgdal-and-MSSQL-Server-geometries-tp7583193p7583209.html
Sent from the R-sig-geo mailing list archive at Nabble.com.

Craig, that did the trick, I didn't have the correct tables/schema for gdal to refer to. Now I can read into R, from SQL Server, data tables having a geometry data type. Below I describe the two additional tables that one needs to create to allow rgdal the ability to read from your SQL Server instance:

# for this example, we have an SQL Database named DB_Deer. 
# Within DB_Deer, we have a table containing GPS collar relocation points. The table is named 'tblPts'
# Within tblPts we have a column of type 'geometry' that is named 'SQL_Shape' that contains the XY pair of coordinates in Native format
# Create a new table named 'dbo.geometry_columns' and add the Primary Keys using SQL syntax

USE [DB_Deer]
GO

CREATE TABLE [dbo].[geometry_columns](
  [f_table_catalog] varchar(128) NOT NULL, 
  [f_table_schema] varchar(128) NOT NULL,
  [f_table_name] varchar(256) NOT NULL,
  [f_geometry_column] varchar(256) NOT NULL,
  [coord_dimension] int NOT NULL,
  [srid] int NOT NULL,
  [geometry_type] varchar(30) NOT NULL
  CONSTRAINT [PK_geometry_columns] PRIMARY KEY CLUSTERED
([f_table_catalog] ASC, [f_table_schema] ASC, [f_table_name] ASC, [f_geometry_column] ASC)
   )

# Create a new table named 'dbo.spatial_ref_sys' and add the Primary Key  using SQL syntax
USE [DB_Deer]
GO

CREATE TABLE [dbo].[spatial_ref_sys](
  [srid] int NOT NULL, 
  [auth_name] varchar(256) NULL,
  [auth_srid] int NULL,
  [srtext] varchar(2048) NULL,
  [proj4text] varchar(2048) NULL,
  CONSTRAINT [PK_spatial_ref_sys] PRIMARY KEY CLUSTERED
([srid] ASC)
   )


# Edit the 'spatial_ref_sys' table. In this example the points are projected in Albers Equal Area
USE [DB_Deer]
GO

INSERT INTO [dbo].[spatial_ref_sys]
           ([srid], [auth_name], [auth_srid], [srtext], [proj4text])
     VALUES
           (32768
           ,NULL
           ,NULL
           ,'PROJCS["unnamed",GEOGCS["NAD83",DATUM["North_American_Datum_1983",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6269"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9108"]],AUTHORITY["EPSG","4269"]],PROJECTION["Albers_Conic_Equal_Area"],PARAMETER["standard_parallel_1",29.5],PARAMETER["standard_parallel_2",45.5],PARAMETER["latitude_of_center",23],PARAMETER["longitude_of_center",-96],PARAMETER["false_easting",0],PARAMETER["false_northing",0],UNIT["Meter",1]]'
           ,'+proj=aea +lat_1=29.5 +lat_2=45.5 +lat_0=23 +lon_0=-96 +x_0=0 +y_0=0 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs')

# Edit the 'geometry_columns' table. 
USE [DB_Deer]
GO

INSERT INTO [dbo].[geometry_columns]
           ([f_table_catalog], [f_table_schema], [f_table_name], [f_geometry_column], [coord_dimension], [srid], [geometry_type])
     VALUES
           ('DB_Deer', 'dbo', 'tblPts', 'SQL_Shape', 2, 32768, 'POINT')


# With the two tables now describing the feature(s) within your SQL database, read them into R
require(sp)
require(rgdal)

#look to see if layers are visible to gdal
ogrListLayers("MSSQL:server=MyCPU\\MySQLInstance;database=DB_Deer;trusted_connection=yes")

#read in the deer point locations. This should behave very similarly to an ESRI Shapefile as a SpatialPointsDataFrame and bring along the proj4string automatically 
pts<- readOGR(dsn='MSSQL:server= MyCPU\\MySQLInstance;database=DB_Deer;trusted_connection=true', layer='tblPts')
plot(pts)

I greatly appreciate Craig's guidance. The r-sig-geo list serve continues to be a fantastic resource!

Cheers,
Shannon

Hi Shannon,

I can recreate your error message when I set an incorrect dsn. I've pasted
in your line of code to read from SQL Server. Can you replace the single
quote with double quotes and re try as is.
check your server and database name.

readOGR(dsn='MSSQL:server=CPU\\CPU_SQL2012EXP;database=Deer;uid=myUser;trusted_connection=true',
layer='CollarPoly')

From the line provided, I gather your computer name is CPU and the SQL

server instance is called CPU_SQL2012EXP. Can you confirm these two names
and the database name are correct.

Regards

Craig



--
View this message in context: http://r-sig-geo.2731867.n2.nabble.com/rgdal-and-MSSQL-Server-geometries-tp7583193p7583220.html
Sent from the R-sig-geo mailing list archive at Nabble.com.