WL#8579: Spatial Reference Systems

Currently, MySQL supports only one coordinate system, a unitless 2d plane that is not referenced to positions on Earth. This WL adds support for georeferenced 2d projections.

This includes data dictionary storage and caching of spatial reference system (SRS) definitions, a parser for the WKT description of SRSs, and a set of default SRSs from the EPSG Dataset.

Since projected SRSs are Cartesian, this does not change the actual calculations. However, it provides a way to add SRS metadata to geometric objects.

This WL does not add geographic (ellipsoid) support to GIS functions. All computations for geographic SRSs will still be done in SRID 0, but a warning will be issued.

Creation, modification and deletion of SRSs is not part of this worklog.

F-1.1

When populating an SRS object in the data dictionary, if the SRS definition can't be parsed, an exception condition MUST be raised.

F-2.1

The WKT parser MUST accept geographic and projected SRSs as defined in OGC CT, Sect. 7.2.

F-2.2

The WKT parser MUST NOT accept other SRSs than those mentioned in req. F-2.1.

F-2.3

The WKT parser MUST accept both brackets ("[" and "]") and parentheses as left/right delimiters.

F-2.4

The WKT parser MUST NOT accept mixing brackets and parentheses in the same SRS definition.

F-2.5

The WKT parser MUST NOT accept projections where projection parameters are missing, cf. Sect. E.2 and E.3 in OGC 12-063r5: Geographic information - Well-known text representation of coordinate reference systems.

F-3.1

SRSs imported from the EPSG Dataset MUST be parsable by the WKT parser.

F-3.2

SRSs imported from the EPSG Dataset MUST use the AUTHORITY clause for projection parameters.

F-4.1

GIS functions that depend on the SRS but traditionally have accepted parameters with any SRID¹ MUST raise a completion condition if the SRID of the input geometries is not found in the data dictionary.

F-4.2

GIS functions MUST NOT raise exception or completion or completion conditions for SRID 0, even though it's not registered in the data dictionary.

F-4.3

GIS functions that depend on the SRS^{1, 2} MUST raise an exception condition if the SRID of the input geometries refers to an SRSs with an unparseable definition string.

F-4.4

GIS functions that depend on the SRS but traditionally have rejected parameters with other SRIDs than 0² MUST raise an exception condition if the SRID of the input geometries is not found in the data dictionary.

F-4.5

GIS functions that depend on the SRS but traditionally have rejected parameters with other SRIDs than 0² MUST accept parameters with SRIDs for projected SRSs.

F-4.6

GIS functions that do not depend on the SRS³ MUST NOT raise exception or completion conditions for any SRIDs.

¹ MBRCONTAINS, MBRCOVEREDBY, MBRCOVERS, MBRDISJOINT, MBREQUALS, MBRINTERSECTS, MBROVERLAPS, MBRTOUCHES, MBRWITHIN, ST_AREA, ST_BUFFER, ST_CENTROID, ST_CONTAINS, ST_CONVEXHULL, ST_CROSSES, ST_DIFFERENCE, ST_DISJOINT, ST_DISTANCE, ST_ENVELOPE, ST_EQUALS, ST_INTERSECTION, ST_INTERSECTS, ST_ISSIMPLE, ST_LENGTH, ST_OVERLAPS, ST_SIMPLIFY, ST_SYMDIFFERENCE, ST_TOUCHES, ST_UNION and ST_WITHIN.

² ST_ISVALID, ST_MAKEENVELOPE, and ST_VALIDATE.

³ ST_ASBINARY, ST_ASGEOJSON, ST_ASTEXT, ST_ASWKB, ST_ASWKT, ST_BUFFER_STRATEGY, ST_DIMENSION, ST_DISTANCE_SPHERE, ST_ENDPOINT, ST_EXTERIORRING, ST_GEOHASH, ST_GEOMCOLLFROMTEXT, ST_GEOMCOLLFROMTXT, ST_GEOMCOLLFROMWKB, ST_GEOMETRYCOLLECTIONFROMTEXT, ST_GEOMETRYCOLLECTIONFROMWKB, ST_GEOMETRYFROMTEXT, ST_GEOMETRYFROMWKB, ST_GEOMETRYN, ST_GEOMETRYTYPE, ST_GEOMFROMGEOJSON, ST_GEOMFROMTEXT, ST_GEOMFROMWKB, ST_INTERIORRINGN, ST_ISCLOSED, ST_ISEMPTY, ST_LATFROMGEOHASH, ST_LINEFROMTEXT, ST_LINEFROMWKB, ST_LINESTRINGFROMTEXT, ST_LINESTRINGFROMWKB, ST_LONGFROMGEOHASH, ST_MLINEFROMTEXT, ST_MLINEFROMWKB, ST_MPOINTFROMTEXT, ST_MPOINTFROMWKB, ST_MPOLYFROMTEXT, ST_MPOLYFROMWKB, ST_MULTILINESTRINGFROMTEXT, ST_MULTILINESTRINGFROMWKB, ST_MULTIPOINTFROMTEXT, ST_MULTIPOINTFROMWKB, ST_MULTIPOLYGONFROMTEXT, ST_MULTIPOLYGONFROMWKB, ST_NUMGEOMETRIES, ST_NUMINTERIORRING, ST_NUMINTERIORRINGS, ST_NUMPOINTS, ST_POINTFROMGEOHASH, ST_POINTFROMTEXT, ST_POINTFROMWKB, ST_POINTN, ST_POLYFROMTEXT, ST_POLYFROMWKB, ST_POLYGONFROMTEXT, ST_POLYGONFROMWKB, ST_SRID, ST_STARTPOINT, ST_X and ST_Y.

I-1

No new files.

I-2

No new syntax.

I-3

No new commands.

I-4

No new tools.

I-5

No impact on existing functionality.

Data dictionary objects for spatial reference systems

Use the existing data dictionary framework to implement the SRS definition storage and caching. Implement SRSs as strong entities in the dictionary.

Store SRS definitions as WKT strings. Parse the string on demand when the data dictionary reads the definition from disk and return an error if the string doesn't contain a valid SRS definition. The GIS function will catch this error and raise an exception condition.

Interface of class dd::Spatial_reference_system

Class dd::Spatial_reference_system is a subclass of dd::Dictionary_object. Most public functions are getters/setters for parameter values.

To begin with, only one property is needed: whether the SRS is a projection or not. As geography support is implemented, the interface will be extended with functions to access other properties of the SRS (i.e., getters for attributes of gis::srs::Spatial_reference_system). The plan is to access these properties indirectly through the dd::Spatial_reference_system object.

Non-inherited public member functions

ulonglong created() / set_created(ulonglong)

Getter/setter for creation timestamp.

ulonglong last_altered() / set_altered(ulonglong)

Getter/setter for last altered timestamp.

std::string &organization() / set_organization(std::string &)

Getter/setter for defining organization name.

longlong organization_coordsys_id() / set_organization_coordsys_id(longlong)

Getter/setter for the SRSs ID in the defining organization's registry.

std::string &definition() / set_definition(std::string &)

Getter/setter for the SRS definition.

std::string &description() / set_description(std::string &)

Getter/setter for the SRS description.

bool is_projected()

Check if the SRS is a projected SRS.

... other funcions to get SRS properties ...

Implemented by later WLs.

Table mysql.st_spatial_reference_systems

Implement the table as suggested in SQL/MM 2011, Section 18.3, without the CHECK constraints. Use the following definitions for implementation-defined meta-variables:

These definitions are similar to the recommendations in OGC CT Sect. B.3.

In addition, add columns for catalog, creation timestamp and last modification timestamp, similar to other data dictionary tables.

Table definition

USE mysql;
CREATE TABLE IF NOT EXISTS st_spatial_reference_systems (
id INTEGER UNSIGNED NOT NULL,
catalog_id BIGINT UNSIGNED NOT NULL,
name CHARACTER VARYING(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
last_altered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
organization CHARACTER VARYING(256) CHARACTER SET utf8 COLLATE utf8_general_ci,
organization_coordsys_id INTEGER UNSIGNED,
definition CHARACTER VARYING(4096) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
description CHARACTER VARYING(2048) CHARACTER SET utf8 COLLATE utf8_general_ci,
CONSTRAINT PRIMARY KEY (srs_id),
CONSTRAINT UNIQUE (catalog_id, srs_name),
CONSTRAINT FOREIGN KEY (catalog_id) REFERENCES catalogs (id)
)
ENGINE=INNODB,
DEFAULT CHARSET=utf8,
COLLATE=utf8_bin,
STATS_PERSISTENT=0;

The names of the id and name columns deviates from the suggestion in SQL/MM, but is in line with the other dictionary tables.

The catalog_id is left out of the primary key because the current data dictionary implementation expects the ID to be a globally unique number. This makes the SRID globally unique. The ID type can be changed to make SRIDs catalog specific when catalog support is implemented.

Names are taken from the EPSG Dataset and are defined in OGC 373-7-1, Annex C. That definition does not mention case sensitivity, but it seems safe to assume that OGP will not use case differences to make names unique. Therefore, the name column, and the index on it, uses a case insensitive collation.

The order of the last_altered and created colums is chosen to avoid any problems with the implicit defaults of the first TIMESTAMP column in a table (cf. http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html).

Locking

Implement a new metadata lock namespace, "SRID", to protect SRS definitions in the cache. MDL keys have namespace "SRID", DB/schema "" (empty string), and the SRID number (ASCII string) as name.

Take a transaction lifetime shared read lock before retrieving the SRS definition and a transaction lifetime exclusive lock when adding/updating SRS definitions.

SRS definition WKT parser

Implement the SRS definition grammar defined in OGC CT, Sect. 7.2. Only the <horz cs> production rule is implemented (i.e., geographic and projected SRSs).

Add an optional, non-standard <authority> clause to <parameter>. This makes it possible to recognize projection parameters by authority instead of name.

Grammar

<horz cs> = <geographic cs> | <projected cs>

<projected cs> = PROJCS <left delimiter> "<name>", <geographic cs>,
                 <projection>, {<parameter>,}* <linear unit>
                 {,<twin axes>}{,<authority>} <right delimiter>

<projection> = PROJECTION <left delimiter> "<name>" {,<authority>}
               <right delimiter> 

<parameter> = PARAMETER <left delimiter> "<name>", <value>
              {,<authority>} <right delimiter>

<geographic cs> = GEOGCS <left delimiter> "<name>", <datum>,
                  <prime meridian>, <angular unit> {,<twin axes>}
                  {,<authority>} <right delimiter>

<datum> = DATUM <left delimiter> "<name>", <spheroid> {,<to wgs84>}
          {,<authority>} <right delimiter>

<spheroid> = SPHEROID <left delimiter> "<name>", <semi-major axis>,
             <inverse flattening> {,<authority>} <right delimiter>

<semi-major axis> = <number>

<inverse flattening> = <number>

<prime meridian> = PRIMEM <left delimiter> "<name>", <longitude>
                   {,<authority>} <right delimiter>

<longitude> = <number>

<angular unit> = <unit>

<linear unit> = <unit>

<unit> = UNIT <left delimiter> "<name>", <conversion factor>
         {,<authority>} <right delimiter>

<conversion factor> = <number>

<value> = <number>

<authority> = AUTHORITY <left delimiter> "<name>", "<code>"
              <right delimiter>

<twin axes> = <axis>, <axis>

<axis> = AXIS <left delimiter> "<name>", NORTH | SOUTH | EAST | WEST |
         UP | DOWN | OTHER <right delimiter>

<to wgs84> = TOWGS84 <left delimiter> <seven param> <right delimiter>

<seven param> = <dx>, <dy>, <dz>, <ex>, <ey>, <ez>, <ppm>

<dx> = <number>

<dy> = <number>

<dz> = <number>

<ex> = <number>

<ey> = <number>

<ez> = <number>

<ppm> = <number>

<left delimiter> = <left paren> | <left bracket>

<right delimiter> = <right paren> | <right bracket>

Projection methods

Projection methods are recognized by EPSG authority codes. The authority name must be "EPSG" (case insensitive match). If an EPSG code is recognized, projection parameters are checked. Unknown projections, i.e., projections with an unknown EPSG code or without an EPSG code, are accepted regardless of parameters.

The name of the projection method in the SRS definition is ignored.

Projection parameters

Projection parameters are recognized by EPSG authority code when provided. If no authority is provided, or the authority is not "EPSG" (case insensitive match), parameters are matched by name (case insensitive match).

EPSG Dataset

Import SRS definitions from the EPSG dataset version 8.7 during data dictionary initialization (mysqld --initialize) and on upgrades using mysql_upgrade.

Since CREATE/ALTER statements for SRSs aren't implemented yet, use INSERT IGNORE statements to create the SRS definitions.

For new initializations, add the definitions to mysql_system_tables_data.sql.

For use by mysql_upgrade, add the same definitions to mysql_system_tables_data_fix.sql and use that to generate a new file sql_commands_system_tables_data_fix.h containing the array mysql_system_data_fix and add a step to mysql_upgrade to execute the statements.

Once CREATE/ALTER statements for SRSs are supported, the initialization/upgrade statements should be rewritten to use those.

Warnings and errors

In all GIS functions that depend on SRS, look up the SRS in the dictionary on evaluation. If the SRS doesn't exists, raise a completion condition warning the user that SRID 0 will be used in the computations. Rationale: Since all SRIDs are valid before this WL, and this WL doesn't provide mechanisms for adding user defined SRIDs, an exception condition is too strict.

For now, direct INSERT/UPDATE/DELETE on the dictionary table is allowed. That will change in the future when dictionary tables are hidden, so the interface should not be documented. We need to add a user accessible interface to managing SRSs.

New SQLSTATE classes and subclasses

References

[http://www.opengeospatial.org/standards/ct OGC 01-009 Coordinate Transformation Service (OGC CT) rev. 1.00]

ISO/IEC 13249-3:2011(E) SQL/MM Part 3: Spatial