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]