Spatial Reference Tables

Spatial Reference Tables are the metadata of your spatial database:

There are two tables that are defined by the OGC standard for use with spatial column databases.

  1. geometry_columns – this table stores your catalog of data, where to find the geometry column, the dimensions of the coordinate system, the geometry type, and the SRID – Spatial Reference IDentifier (internal SRID, authority is not set here)
  2. spatial_ref_sys – this table contains the SRID  used locally and associates it with an authoritative SRID (such as the EPSG) the spatial reference text and the proj4text.

These tables (with a potential for a variation on name) are used in all spatial database systems that support the OGC. Even ESRI uses this method with their SDE data model.

One can choose to use these tables or create them as views to support other views. For example I may have a table of features, and a table for locations. In my database I dont care if a feature has mixed geometries (points lines or polygons) because that is just an attribute of the thing I am interested in.  (ESRI tries to make you tie everything to a point, line or polygon, and then attributes. This is backwards, and wrong).

In any case, in my database, I can create views that organize my data into a flat file type dataset, with only one type of geometry per view. I would then want to use a view to make my spatial_reference sys tables, to catalog the views.

Spatial References help:

I have found that spatialreference.org is really useful for working with the projects and creating these tables. There is even a link to get the SQL insert statement for the spatial reference that you are interested in.

 

Here is the create code for each of the tables if this helps.

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,
 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]

 

AND

 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 [geometry_columns_pk] 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]

 

ogr2ogr to load a database from a shapefile

GDAL, the  Geospatial Data Abstraction Library

Is a great set of tools for managing data with a spatial data type.

I use the ogr2ogr tool to load shapefiles or other data into databases. It will automatically create the spatial metadata tables that are part of the OGC standard.

Spatial Metadata Tables

Two tables are created for letting the user (and applications) know where the spatial column is in what tables they are located, and what the projection is. This is all in another post.

ogr2ogr

You can load the database using ogr2ogr. This will handily create the spatial metadata tables for you during load.

For example, on MSSQL server, loading from a shape file in WGS84 (EPSG: 4326) to WA State Plane (EPSG 2927):

ogr2ogr -overwrite -progress -f MSSQLSpatial “MSSQL:server=SERVER1;database=GPSTrails;trusted_connection=yes” Points.shp -s_srs EPSG:4326 -t_srs EPSG:2927

Here I am overwriting the table if it exists, showing progress information, indicating that I have a trusted connection converting from 4326 to 2927.

If you look over the data types you can use  for ogr2ogr, you can see that you could load from sqlite, ESRI FileGeodatabase, csv, and doxens of other formats.