WL#2378: GIS: Add INFORMATION_SCHEMA views for GIS metadata

Affects: Server-7.0   —   Status: Un-Assigned

MySQL will add two views -- GEOMETRY_COLUMNS and SPATIAL_REF_SYS -- to 
the INFORMATION_SCHEMA, to store GIS-related metadata.

Rationale
---------
These views are required so that MySQL can claim compliance with OpenGIS; 
see WL#2377 for full details.
One task that must be completed before MySQL can claim compliance with 
OpenGIS is adding two metadata tables, that store GIS-related metadata, 
to INFORMATION_SCHEMA.

There is no need to add SHOW syntax for this metadata.


INFORMATION_SCHEMA.GEOMETRY_COLUMNS view
----------------------------------------

The GEOMETRY_COLUMNS view consists of one row for each geometry column 
in the database. It is based on the following table definition:

CREATE TABLE GEOMETRY_COLUMNS (
  F_TABLE_CATALOG VARCHAR(256) NOT NULL,
  F_TABLE_SCHEMA VARCHAR(256) NOT NULL,
  F_TABLE_NAME VARCHAR(256) NOT NULL,
  F_GEOMETRY_COLUMN VARCHAR(256) NOT NULL,
  COORD_DIMENSION INTEGER,
  SRID INTEGER REFERENCES SPATIAL_REF_SYS,
  CONSTRAINT GC_PK PRIMARY KEY
    (F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME,
     F_GEOMETRY_COLUMN));

Column explanation:
-- F_TABLE_CATALOG: despite the definition above, this column is always 
NULL (until MySQL supports the concept of a database catalog).
-- F_TABLE_SCHEMA: the name of the schema (database) in which a table 
containing a geometry column resides.
-- F_TABLE_NAME: the name of the table that contains the geometry column.
-- F_GEOMETRY_COLUMN: the name of the column defined with a geometry data 
type.
-- COORD_DIMENSION: the coordinate dimension for the geometry values in 
the column; the value is equal to the number of dimensions in the spatial 
reference system.
-- SRID: the ID of the spatial reference system used for the coordinate 
geometry in this table. It is a foreign key reference to the SPATIAL_REF_SYS 
table.


INFORMATION_SCHEMA.SPATIAL_REF_SYS view
---------------------------------------

The SPATIAL_REF_SYS view stores information on each Spatial Reference System 
in the database. It is based on the following table definition:

CREATE TABLE SPATIAL_REF_SYS (
  SRID INTEGER NOT NULL PRIMARY KEY,
  AUTH_NAME VARCHAR (256),
  AUTH_SRID INTEGER,
  SRTEXT VARCHAR (2048));

Column explanation:
-- SRID: an integer that uniquely identifies a spatial reference system 
within a database.
-- AUTH_NAME: the name of the standard (or standard's body) that defines 
this spatial reference system (e.g., EPSG would be a valid AUTH_NAME). 
This can be NULL until MySQL fully supports the use of SRID in the manner 
intended by the OpenGIS specification.
-- AUTH_SRID: the ID of the spatial reference system as defined by the 
standard specified in the AUTH_NAME column. This can be NULL until MySQL 
has meaningful information to store in AUTH_NAME.
-- SRTEXT: the Well-known Text (WKT) representation of the spatial reference 
system.