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

空间参考系统语句

您可以使用 CREATE SPATIAL REFERENCE SYSTEM 语句创建空间参考系统，创建参考系统后，它将存储在数据字典中。

语法

以下是 CREATE SPATIAL REFERENCE SYSTEM 语句语法的示例：

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 子句，则如果存在具有给定名称的 SRS，则 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';