MySQL - 创建空间参考系统语句

• 空间参考系统语句
• replace 和 IF NOT EXISTS 子句

空间参考系统语句

您可以使用创建空间参考系统语句创建空间参考系统，创建后，参考系统将存储在数据字典中 -

语法

以下是创建空间参考系统语句的语法示例 -

CREATE OR REPLACE SPATIAL REFERENCE SYSTEM srid srs_attribute ...

示例

以下查询在 MySQL 数据库中创建一个空间参考系统 -

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';

验证

您可以验证上面创建的特殊引用，如下所示 -

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

以下是上述查询的输出 -

*************** 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	

replace 和 IF NOT EXISTS 子句

通常，如果您尝试创建与现有 SRS 同名的空间参考系统，将生成如下所示的错误 -

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

如果使用 replace 子句，现有的 SRS 将被替换。

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';

同样，如果您使用 IF NOT EXISTS 子句，CREATE SPATIAL REFERENCE SYSTEM 语句将创建一个 SRS，如果存在具有给定名称的 SRS，则此查询将被忽略。

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';