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