JGD2011の定義を変えちゃう!
ST_SPATIAL_REFERENCE_SYSTEMSテーブルのに格納されている定義を変更してしまいます。やりたいことは、JGD2011の定義に、JGD2000にあるのと同様の形となるよう TOWGS84 の情報を追加することです。 ST_SPATIAL_REFERENCE_SYSTEMS テーブルは INFORMATION_SCHEMAにあり、一見普通のテーブルに見えますが、これを直接書き換えることはできません。専用の命令「CREATE OR REPLACE SPATIAL_REFERENCE_SYSTEM」文を使用します。
まず現在のJGD2011の定義を確認します。今回は 地理座標計と2系だけを見れば良かったのですが、ちょっと目視しておこうと、周辺のものも少し見てみました。
SELECT * FROM information_schema.ST_Spatial_reference_systems WHERE srs_id in (6668,6669,6670,6671);
+------------------------------------------+--------+--------------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| SRS_NAME | SRS_ID | ORGANIZATION | ORGANIZATION_COORDSYS_ID | DEFINITION | DESCRIPTION |
+------------------------------------------+--------+--------------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
| JGD2011 | 6668 | EPSG | 6668 | GEOGCS["JGD2011",DATUM["Japanese Geodetic Datum 2011",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],AUTHORITY["EPSG","1128"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","6668"]] | NULL |
| JGD2011 / Japan Plane Rectangular CS I | 6669 | EPSG | 6669 | PROJCS["JGD2011 / Japan Plane Rectangular CS I",GEOGCS["JGD2011",DATUM["Japanese Geodetic Datum 2011",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],AUTHORITY["EPSG","1128"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","6668"]],PROJECTION["Transverse Mercator",AUTHORITY["EPSG","9807"]],PARAMETER["Latitude of natural origin",33,AUTHORITY["EPSG","8801"]],PARAMETER["Longitude of natural origin",129.5,AUTHORITY["EPSG","8802"]],PARAMETER["Scale factor at natural origin",0.9999,AUTHORITY["EPSG","8805"]],PARAMETER["False easting",0,AUTHORITY["EPSG","8806"]],PARAMETER["False northing",0,AUTHORITY["EPSG","8807"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",NORTH],AXIS["Y",EAST],AUTHORITY["EPSG","6669"]] | NULL |
| JGD2011 / Japan Plane Rectangular CS II | 6670 | EPSG | 6670 | PROJCS["JGD2011 / Japan Plane Rectangular CS II",GEOGCS["JGD2011",DATUM["Japanese Geodetic Datum 2011",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],AUTHORITY["EPSG","1128"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","6668"]],PROJECTION["Transverse Mercator",AUTHORITY["EPSG","9807"]],PARAMETER["Latitude of natural origin",33,AUTHORITY["EPSG","8801"]],PARAMETER["Longitude of natural origin",131,AUTHORITY["EPSG","8802"]],PARAMETER["Scale factor at natural origin",0.9999,AUTHORITY["EPSG","8805"]],PARAMETER["False easting",0,AUTHORITY["EPSG","8806"]],PARAMETER["False northing",0,AUTHORITY["EPSG","8807"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",NORTH],AXIS["Y",EAST],AUTHORITY["EPSG","6670"]] | NULL |
| JGD2011 / Japan Plane Rectangular CS III | 6671 | EPSG | 6671 | PROJCS["JGD2011 / Japan Plane Rectangular CS III",GEOGCS["JGD2011",DATUM["Japanese Geodetic Datum 2011",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],AUTHORITY["EPSG","1128"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","6668"]],PROJECTION["Transverse Mercator",AUTHORITY["EPSG","9807"]],PARAMETER["Latitude of natural origin",36,AUTHORITY["EPSG","8801"]],PARAMETER["Longitude of natural origin",132.177777777778,AUTHORITY["EPSG","8802"]],PARAMETER["Scale factor at natural origin",0.9999,AUTHORITY["EPSG","8805"]],PARAMETER["False easting",0,AUTHORITY["EPSG","8806"]],PARAMETER["False northing",0,AUTHORITY["EPSG","8807"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",NORTH],AXIS["Y",EAST],AUTHORITY["EPSG","6671"]] | NULL |
+------------------------------------------+--------+--------------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
別途、JGD2000の定義を見て、TOWGS84を入れる場所を確認しておきます。
また、その測地系が使用されていると更新できないらしいので、念のため、使用されていないことも確認しておきます。
-- JGD2011(geom)
mysql> SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS WHERE SRS_ID=6668;
Empty set (0.00 sec)
-- JGD2011(平面直角2系)
mysql> SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS WHERE SRS_ID=6670;
Empty set (0.00 sec)
さて、定義の書き換えです! まず地理座標計のほう。 DEFINITIONにTOWGS84を加えた以外は元の値をそのままコピーして記述しています。WARNINGが出るのは、EPSGの予約範囲のID(0~32767と6000万台)および20億台の一部に該当するIDであるためです。warningは出るけど正常にデータは更新されます。
mysql> CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 6668
-> NAME 'JGD2011'
-> ORGANIZATION 'EPSG' IDENTIFIED BY 6668
-> DEFINITION 'GEOGCS["JGD2011",DATUM["Japanese Geodetic Datum 2011",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","1128"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","6668"]]';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3715 | The SRID range [0, 32767] has been reserved for system use. SRSs in this range may be added, modified or removed without warning during upgrade. |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
同様に平面直角座標2系の定義も更新。
mysql> CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 6670
-> NAME 'JGD2011 / Japan Plane Rectangular CS II'
-> ORGANIZATION 'EPSG' IDENTIFIED BY 6670
-> DEFINITION 'PROJCS["JGD2011 / Japan Plane Rectangular CS II",GEOGCS["JGD2011",DATUM["Japanese Geodetic Datum 2011",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","1128"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","6668"]],PROJECTION["Transverse Mercator",AUTHORITY["EPSG","9807"]],PARAMETER["Latitude of natural origin",33,AUTHORITY["EPSG","8801"]],PARAMETER["Longitude of natural origin",131,AUTHORITY["EPSG","8802"]],PARAMETER["Scale factor at natural origin",0.9999,AUTHORITY["EPSG","8805"]],PARAMETER["False easting",0,AUTHORITY["EPSG","8806"]],PARAMETER["False northing",0,AUTHORITY["EPSG","8807"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["X",NORTH],AXIS["Y",EAST],AUTHORITY["EPSG","6670"]]';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3715 | The SRID range [0, 32767] has been reserved for system use. SRSs in this range may be added, modified or removed without warning during upgrade. |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
これでJGD2011の地理座標と2系のSRS定義が更新されました。