Rechercher dans le manuel MySQL

13.1.19 CREATE SPATIAL REFERENCE SYSTEM Syntax

Contents
  1. CREATE OR REPLACE SPATIAL REFERENCE SYSTEM
  2.     srid srs_attribute ...
  3.  
  4. CREATE SPATIAL REFERENCE SYSTEM
  5.     [IF NOT EXISTS]
  6.     srid srs_attribute ...
  7.  
  8. srs_attribute: {
  9.     NAME 'srs_name'
  10.   | DEFINITION 'definition'
  11.   | ORGANIZATION 'org_name' IDENTIFIED BY org_id
  12.   | DESCRIPTION 'description'
  13. }
  14.  
  15. 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:

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

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

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

  • SRID 0 is a valid SRID but cannot be used with CREATE SPATIAL REFERENCE SYSTEM.

  • If the value is in a reserved SRID range, a warning occurs. Reserved ranges are [0, 32767] (reserved by EPSG), [60,000,000, 69,999,999] (reserved by EPSG), and [2,000,000,000, 2,147,483,647] (reserved by MySQL). EPSG stands for the European Petroleum Survey Group.

  • Users should not create SRSs with SRIDs in the reserved ranges. Doing so runs the risk that the SRIDs will conflict with future SRS definitions distributed with MySQL, with the result that the new system-provided SRSs are not installed for MySQL upgrades or that the user-defined SRSs are overwritten.

Attributes for the statement must satisfy these conditions:

  • Attributes can be given in any order, but no attribute can be given more than once.

  • The NAME and DEFINITION attributes are mandatory.

  • The NAME srs_name attribute value must be unique. The combination of the ORGANIZATION org_name and org_id attribute values must be unique.

  • The NAME srs_name attribute value and ORGANIZATION org_name attribute value cannot be empty or begin or end with whitespace.

  • String values in attribute specifications cannot contain control characters, including newline.

  • The following table shows the maximum lengths for string attribute values.

    Table 13.6 CREATE SPATIAL REFERENCE SYSTEM Attribute Lengths

    Attribute Maximum Length (characters)
    NAME 80
    DEFINITION 4096
    ORGANIZATION 256
    DESCRIPTION 2048

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

  1. CREATE SPATIAL REFERENCE SYSTEM 4120
  2. NAME 'Greek'
  3. ORGANIZATION 'EPSG' IDENTIFIED BY 4120
  4. DEFINITION
  5.   'GEOGCS["Greek",DATUM["Greek",SPHEROID["Bessel 1841",
  6.  6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],
  7.  AUTHORITY["EPSG","6120"]],PRIMEM["Greenwich",0,
  8.  AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,
  9.  AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],
  10.  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:

  • Only the <horz cs> production rule is implemented (that is, geographic and projected SRSs).

  • There is an optional, nonstandard <authority> clause for <parameter>. This makes it possible to recognize projection parameters by authority instead of name.

  • The specification does not make AXIS clauses mandatory in GEOGCS spatial reference system definitions. However, if there are no AXIS clauses, MySQL cannot determine whether a definition has axes in latitude-longitude order or longitude-latitude order. MySQL enforces the nonstandard requirement that each GEOGCS definition must include two AXIS clauses. One must be NORTH or SOUTH, and the other EAST or WEST. The AXIS clause order determines whether the definition has axes in latitude-longitude order or longitude-latitude order.

  • SRS definitions may not contain newlines.

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


Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-create-spatial-reference-system.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut