MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

13.1.19 CREATE SPATIAL REFERENCE SYSTEM Syntax

CREATE OR REPLACE SPATIAL REFERENCE SYSTEM
    srid srs_attribute ...

CREATE SPATIAL REFERENCE SYSTEM
    [IF NOT EXISTS]
    srid srs_attribute ...

srs_attribute: {
    NAME 'srs_name'
  | DEFINITION 'definition'
  | ORGANIZATION 'org_name' IDENTIFIED BY org_id
  | DESCRIPTION 'description'
}

srid, org_id: 32-bit unsigned integer

This statement creates a spatial reference system (SRS) definition and stores it in the data dictionary. The definition can be inspected using the INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS table. This statement requires the SUPER privilege.

If neither OR REPLACE nor IF NOT EXISTS is specified, an error occurs if an SRS definition with the SRID value already exists.

With CREATE OR REPLACE syntax, any existing SRS definition with the same SRID value is replaced, unless the SRID value is used by some column in an existing table. In that case, an error occurs. For example:

mysql> CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 4326 ...;
ERROR 3716 (SR005): Can't modify SRID 4326. There is at
least one column depending on it.

To identify which column or columns use the SRID, use this query:

SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS WHERE SRS_ID=4326;

With CREATE ... IF NOT EXISTS syntax, any existing SRS definition with the same SRID value causes the new definition to be ignored and a warning occurs.

SRID values must be in the range of 32-bit unsigned integers, with these restrictions:

Attributes for the statement must satisfy these conditions:

Here is an example CREATE SPATIAL REFERENCE SYSTEM statement. The DEFINITION value is reformatted across multiple lines for readability. (For the statement to be legal, the value actually must be given on a single line.)

CREATE SPATIAL REFERENCE SYSTEM 4120
NAME 'Greek'
ORGANIZATION 'EPSG' IDENTIFIED BY 4120
DEFINITION
  'GEOGCS["Greek",DATUM["Greek",SPHEROID["Bessel 1841",
  6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],
  AUTHORITY["EPSG","6120"]],PRIMEM["Greenwich",0,
  AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,
  AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],
  AUTHORITY["EPSG","4120"]]';

The grammar for SRS definitions is based on the grammar defined in OpenGIS Implementation Specification: Coordinate Transformation Services, Revision 1.00, OGC 01-009, January 12, 2001, Section 7.2. This specification is available at http://www.opengeospatial.org/standards/ct.

MySQL incorporates these changes to the specification:

If an SRS definition specifies an authority code for the projection (which is recommended), an error occurs if the definition is missing mandatory parameters. In this case, the error message indicates what the problem is. The projection methods and mandatory parameters that MySQL supports are shown in Table 13.7, “Supported Spatial Reference System Projection Methods” and Table 13.8, “Spatial Reference System Projection Parameters”.

For additional information about writing SRS definitions for MySQL, see Geographic Spatial Reference Systems in MySQL 8.0 and Projected Spatial Reference Systems in MySQL 8.0

The following table shows the projection methods that MySQL supports. MySQL permits unknown projection methods but cannot check the defintion for mandatory paramters and cannot convert spatial data to or from an unknown projection. For detailed explanations of how each projection works, including formulas, see EPSG Guidance Note 7-2.

Table 13.7 Supported Spatial Reference System Projection Methods

EPSG Code Projection 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

The following table shows the projection parameters that MySQL recognizes. Recognition occurs primarily by authority code. If there is no authority code, MySQL falls back to case-insensitive string matching on the parameter name. For details about each parameter, look it up by code in the EPSG Online Registry.

Table 13.8 Spatial Reference System Projection Parameters

EPSG Code Fallback Name (Recognized by MySQL) EPSG Name
1026 c1 C1
1027 c2 C2
1028 c3 C3
1029 c4 C4
1030 c5 C5
1031 c6 C6
1032 c7 C7
1033 c8 C8
1034 c9 C9
1035 c10 C10
1036 azimuth Co-latitude of cone axis
1038 ellipsoid_scale_factor Ellipsoid scaling factor
1039 projection_plane_height_at_origin Projection plane origin height
8617 evaluation_point_ordinate_1 Ordinate 1 of evaluation point
8618 evaluation_point_ordinate_2 Ordinate 2 of evaluation point
8801 latitude_of_origin Latitude of natural origin
8802 central_meridian Longitude of natural origin
8805 scale_factor Scale factor at natural origin
8806 false_easting False easting
8807 false_northing False northing
8811 latitude_of_center Latitude of projection centre
8812 longitude_of_center Longitude of projection centre
8813 azimuth Azimuth of initial line
8814 rectified_grid_angle Angle from Rectified to Skew Grid
8815 scale_factor Scale factor on initial line
8816 false_easting Easting at projection centre
8817 false_northing Northing at projection centre
8818 pseudo_standard_parallel_1 Latitude of pseudo standard parallel
8819 scale_factor Scale factor on pseudo standard parallel
8821 latitude_of_origin Latitude of false origin
8822 central_meridian Longitude of false origin
8823 standard_parallel_1, standard_parallel1 Latitude of 1st standard parallel
8824 standard_parallel_2, standard_parallel2 Latitude of 2nd standard parallel
8826 false_easting Easting at false origin
8827 false_northing Northing at false origin
8830 initial_longitude Initial longitude
8831 zone_width Zone width
8832 standard_parallel Latitude of standard parallel
8833 longitude_of_center Longitude of origin