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.
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.