WL#8579: Spatial Reference Systems

Affects: Server-8.0   —   Status: Complete

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 SRID1 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 SRS1, 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 02 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 02 MUST accept parameters with SRIDs for projected SRSs.
F-4.6
GIS functions that do not depend on the SRS3 MUST NOT raise exception or completion conditions for any SRIDs.

1 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.

2 ST_ISVALID, ST_MAKEENVELOPE, and ST_VALIDATE.

3 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.

Contents


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:

ST_MaxSRSNameLength 256
ST_MaxOrganizationNameLength 256
ST_MaxSRSDefinitionLength 4096
ST_MaxDescriptionLength 2048

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.

EPSG code Name Mandatory parameters (EPSG codes)
1024 Popular Visualisation Pseudo Mercator 8801, 8802, 8806, 8807
1027 Lambert Azimuthal Equal Area (Spherical) 8801, 8802, 8806, 8807
1028 Equidistant Cylindrical 8823, 8802, 8806, 8807
1029 Equidistant Cylindrical (Spherical) 8823, 8802, 8806, 8807
1041 Krovak (North Orientated) 8811, 8833, 1036, 8818, 8819, 8806, 8807
1042 Krovak Modified 8811, 8833, 1036, 8818, 8819, 8806, 8807, 8617, 8618, 1026, 1027, 1028, 1029, 1030, 1031, 1032, 1033, 1034, 1035
1043 Krovak Modified (North Orientated) 8811, 8833, 1036, 8818, 8819, 8806, 8807, 8617, 8618, 1026, 1027, 1028, 1029, 1030, 1031, 1032, 1033, 1034, 1035
1051 Lambert Conic Conformal (2SP Michigan) 8821, 8822, 8823, 8824, 8826, 8827, 1038
1052 Colombia Urban 8801, 8802, 8806, 8807, 1039
9801 Lambert Conic Conformal (1SP) 8801, 8802, 8805, 8806, 8807
9802 Lambert Conic Conformal (2SP) 8821, 8822, 8823, 8824, 8826, 8827
9803 Lambert Conic Conformal (2SP Belgium) 8821, 8822, 8823, 8824, 8826, 8827
9804 Mercator (variant A) 8801, 8802, 8805, 8806, 8807
9805 Mercator (variant B) 8823, 8802, 8806, 8807
9806 Cassini-Soldner 8801, 8802, 8806, 8807
9807 Transverse Mercator 8801, 8802, 8805, 8806, 8807
9808 Transverse Mercator (South Orientated) 8801, 8802, 8805, 8806, 8807
9809 Oblique Stereographic 8801, 8802, 8805, 8806, 8807
9810 Polar Stereographic (variant A) 8801, 8802, 8805, 8806, 8807
9811 New Zealand Map Grid 8801, 8802, 8806, 8807
9812 Hotine Oblique Mercator (variant A) 8811, 8812, 8813, 8814, 8815, 8806, 8807
9813 Laborde Oblique Mercator 8811, 8812, 8813, 8815, 8806, 8807
9815 Hotine Oblique Mercator (variant B) 8811, 8812, 8813, 8814, 8815, 8816, 8817
9816 Tunisia Mining Grid 8821, 8822, 8826, 8827
9817 Lambert Conic Near-Conformal 8801, 8802, 8805, 8806, 8807
9818 American Polyconic 8801, 8802, 8806, 8807
9819 Krovak 8811, 8833, 1036, 8818, 8819, 8806, 8807
9820 Lambert Azimuthal Equal Area 8801, 8802, 8806, 8807
9822 Albers Equal Area 8821, 8822, 8823, 8824, 8826, 8827
9824 Transverse Mercator Zoned Grid System 8801, 8830, 8831, 8805, 8806, 8807
9826 Lambert Conic Conformal (West Orientated) 8801, 8802, 8805, 8806, 8807
9828 Bonne (South Orientated) 8801, 8802, 8806, 8807
9829 Polar Stereographic (variant B) 8832, 8833, 8806, 8807
9830 Polar Stereographic (variant C) 8832, 8833, 8826, 8827
9831 Guam Projection 8801, 8802, 8806, 8807
9832 Modified Azimuthal Equidistant 8801, 8802, 8806, 8807
9833 Hyperbolic Cassini-Soldner 8801, 8802, 8806, 8807
9834 Lambert Cylindrical Equal Area (Spherical) 8823, 8802, 8806, 8807
9835 Lambert Cylindrical Equal Area 8823, 8802, 8806, 8807

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 code Name
1026 c1
1027 c2
1028 c3
1029 c4
1030 c5
1031 c6
1032 c7
1033 c8
1034 c9
1035 c10
1036 azimuth
1038 ellipsoid_scale_factor
1039 projection_plane_height_at_origin
8617 evaluation_point_ordinate_1
8618 evaluation_point_ordinate_2
8801 latitude_of_origin
8802 central_meridian
8805 scale_factor
8806 false_easting
8807 false_northing
8811 latitude_of_center
8812 longitude_of_center
8813 azimuth
8814 rectified_grid_angle
8815 scale_factor
8816 false_easting
8817 false_northing
8818 pseudo_standard_parallel_1
8819 scale_factor
8821 latitude_of_origin
8822 central_meridian
8823 standard_parallel_1
8824 standard_parallel_2
8826 false_easting
8827 false_northing
8830 initial_longitude
8831 zone_width
8832 standard_parallel
8833 longitude_of_center

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

Category Condition Class Subcondition Subclass
X data exception 22 invalid SRS type S00
X invalid SRS identifier SR (no subclass) 000
X invalid SRS identifier SR unknown spatial reference system 001
X invalid SRS identifier SR SRS definition invalid 002
X invalid SRS identifier SR SRS definition missing required information 003

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