MySQL - CREATE SPATIAL REFERENCE SYSTEM Statement

SPATIAL REFERENCE SYSTEM Statement

You can create a spatial reference system using the CREATE SPATIAL REFERENCE SYSTEM Statement, once you create a reference system it will be stored in the data dictionary −

Syntax

Following is the example of the syntax of the CREATE SPATIAL REFERENCE SYSTEM Statement −

CREATE OR REPLACE SPATIAL REFERENCE SYSTEM srid srs_attribute ...

Example

Following query create a SPATIAL REFERENCE SYSTEM in MySQL database −

CREATE SPATIAL REFERENCE SYSTEM 1004326 NAME 'WGS 84 (long-lat)' 
DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",
SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],
AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],
AXIS["Lon",EAST],AXIS["Lat",NORTH]]' DESCRIPTION 'WGS 84 with 
coordinate axes swapped to be longitude-latitude instead 
of latitude-longitude';

Verification

You can verify the above created special reference as shown below −

SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE 
SRS_ID=1004326\G;

Following is the output of the above query −

*************** 1. row ***************
                SRS_NAME: WGS 84 (long-lat)
                  SRS_ID: 1004326
	        ORGANIZATION: NULL
ORGANIZATION_COORDSYS_ID: NULL
              DEFINITION: GEOGCS["WGS 84",DATUM["World Geodetic System 
			              1984",
			              SPHEROID["WGS 84",6378137,298.257223563,
						  AUTHORITY["EPSG","7030"]],
						  AUTHORITY["EPSG","6326"]],
						  PRIMEM["Greenwich",0,
						  AUTHORITY["EPSG","8901"]],
						  UNIT["degree",0.017453292519943278,
						  AUTHORITY["EPSG","9122"]],AXIS["Lon",EAST],
						  AXIS["Lat",NORTH]]
		     DESCRIPTION: WGS 84 with coordinate axes swapped to be 
			              longitude-latitude instead of 
						  latitude-longitude	

The replace and IF NOT EXISTS clauses

Usually, if you try to create a spatial reference system with the name same as an existing SRS an error will be generated as shown as −

ERROR 3712 (SR004): There is already a spatial reference system 
with SRID 1004326.

If you use the replace clause the existing SRS will be replaced.

CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 1004326 NAME 'WGS 84 
(long-lat)' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 
1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],
AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],
AXIS["Lon",EAST],AXIS["Lat",NORTH]]' DESCRIPTION 'WGS 84 with 
coordinate axes swapped to be longitude-latitude instead of 
latitude-longitude';

In the same way if you use the IF NOT EXISTS clause the CREATE SPATIAL REFERENCE SYSTEM statement creates a SRS if an SRS exists with the given name this query will be ignored.

CREATE SPATIAL REFERENCE SYSTEM IF NOT EXISTS 1004326 NAME 'WGS 84 
(long-lat)' DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",
SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],
AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],
AXIS["Lon",EAST],AXIS["Lat",NORTH]]' DESCRIPTION 'WGS 84 with 
coordinate axes swapped to be longitude-latitude 
instead of latitude-longitude';