WL#2378: GIS: Add INFORMATION_SCHEMA views for GIS metadata
Affects: Server-7.0 — Status: Un-Assigned — Priority: Medium
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, 2018, Oracle Corporation and/or its affiliates. All rights reserved.