Create GDAL Tables

Now that you have a Relational Spatial Database, we want to make sure that we can use this Spatial data within several types of software. Therefore, the steps listed in this page are what need to occur if you wish to use this within Program R. 

  1. For Program R and the 'rgdal' package to know that your SQL Database contains 'geometries', we need to create two separate tables within your database for gdal to reference. To begin: 
    • Create a New Query, making sure that you are in the correct database before moving forward.
    • Use the follow script to create the two tables programmatically (I have also copied an SQL file to your cloud directory named 'CreateGDAL_RefTables.sql' that contains the same SQL code):SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO 
  2. Once the tables have been created, we must populate them with the correct data. These tables are for reference only, so we can fill them with information about Tables and/or Queries that contain spatial geometries. However, because they reference data, building dynamic types of information isn't allowed at the moment (i.e. SQL Queries not saved as a View). Knowing these requirements, what tables have Geometries? ............ fcPlots and fcTrapArrays
    • Right-click on the geometry_columns table and select 'Edit Top 200 Rows' 
    • Set the column values as follows:
      • f_table_catalog = Your DB Name (i.e. AlbekeDB)
      • f_table_schema = dbo (this means Database Owner)
      • f_table_name = fcPlots
      • f_geometry_column = Shape (you need to look at the fcPlots table and determine which column is the geometry data type)
      • coord_dimension = 3 (use 2 for points, 3 for line or polygon)
      • srid is the next column. This requires a little bit more knowledge. We know that the data were collected in NAD27 - Zone 17N. I would suggest looking this up at http://spatialreference.org to find the necessary descriptive information for this projection. In this case you will use srid = 26717
      • geometry_type = POLYGON 
    • Perform the same steps for fcTrapArrays 
    • Now we have to update the spatial reference table so that the srid from the geometry table know what the spatial reference really is. Right-click on spatial_ref_sys and select 'Edit Top 200 Rows'. 
    • Set the column values as follows:
      • srid = 26717
      • auth_name = EPSG (taken from spatialreference.org)
      • auth_srid = 26717
      • srtext = PROJCS["NAD27 / UTM zone 17N",GEOGCS["NAD27",DATUM["North_American_Datum_1927",SPHEROID["Clarke 1866",6378206.4,294.9786982138982,AUTHORITY["EPSG","7008"]],AUTHORITY["EPSG","6267"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4267"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-81],PARAMETER["scale_factor",0.9996],PARAMETER["false_easting",500000],PARAMETER["false_northing",0],AUTHORITY["EPSG","26717"],AXIS["Easting",EAST],AXIS["Northing",NORTH]](this can be found by selecting the OGC WKT hyperlink from the website)
      • proj4text = +proj=utm +zone=17 +ellps=clrk66 +datum=NAD27 +units=m +no_defs(this can be found by selecting the proj4 hyperlink from the website) 
  3. Connect to SQL Server using R and rgdal
    • poly<- readOGR(dsn='MSSQL:server=SALTRIVER\\ECOINFO_TDM; database=AlbekeDB;trusted_connection=true', layer="fcPlots", disambiguateFIDs=TRUE) 
  4. Download this R script to get methods for connecting to your SQL Database using Program R: RODBC_Connection