ИТ Блог. Администрирование серверов на основе Linux (Ubuntu, Debian, CentOS, openSUSE)
MySQL 8.0.11 поставляется с каталогом 5108 пространственных справочных систем (SRS) . 4628 проекций (плоские карты), 479 географических (эллипсоидальных) представлений Земли и одной картезианской многоцелевой абстрактной плоскости (SRID 0). И если по какой-то причине этого недостаточно, мы можем создать свои собственные.
Создание (и удаление) SRS в настоящее время требует привилегии SUPER (это может быть позже разделено на отдельную привилегию). SRS создаются с помощью операторов CREATE SPATIAL REFERENCE SYSTEM. Синтаксис прост:
CREATE SPATIAL REFERENCE SYSTEM srid NAME 'a unique name' DEFINITION 'the definition';
SRID – это числовой идентификатор, который MySQL будет использовать для этой SRS. Это имя предназначено только для людей, но MySQL будет уверен, что он всегда уникален. Определение состоит в том, что все детали скрыты. Предыдущие сообщения в блогах охватывали географические и прогнозируемые определения SRS, поэтому я не буду их повторять. Вернитесь к этим сообщениям для всех деталей.
Кроме того, существует два необязательных предложения: задает строку с текстовым описанием SRS и указывает организацию, являющуюся источником этого определения SRS, и SRID организации для этой SRS (которая не должна соответствовать SRID MySQL) , Оба необязательных предложения являются чисто информационными. MySQL не использует их. Однако, поскольку стандарт SQL / MM указывает его, MySQL гарантирует, что пара имени организации и конкретного SRID организации уникальна
DESCRIPTION 'description text' IDENTIFIED BY number ORGANIZATION 'organization name'
Все определения SRS, установленные по умолчанию, указывают, что координаты – это широта первой долготы секунды. В качестве примера новой SRS давайте создадим широтно-широтную версию WGS 84 (SRID 4326). Все, что нам нужно сделать, это поменять порядок двух AXIS предложений в определении:
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'; Query OK, 0 rows affected (0,00 sec)
Если мы запросим представление ST_SPATIAL_REFERENCE_SYSTEMS, мы найдем наш SRS:
mysql> 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 1 row in set (0,00 sec)
Этот SRS ведет себя так же, как SRID 4326, но с той разницей, что первая координата в SRID 1004326 является долготой, а вторая – широтой. Мы можем легко убедиться, что, вычислив расстояние между Тронхеймом (Норвегия) и Лондоном (Великобритания) в двух SRS. Если SRS одинаковы независимо от порядка оси, мы должны получить одинаковое расстояние в обеих SRS:
mysql> SELECT ST_Distance(
-> ST_GeomFromText('POINT(63.4269 10.3958)', 4326),
-> ST_GeomFromText('POINT(51.5081 -0.0761)', 4326)
-> ) / 1000 AS km;
+-------------------+
| km |
+-------------------+
| 1464.250079639081 |
+-------------------+
1 row in set (0,00 sec)
mysql> SELECT ST_Distance(
-> ST_GeomFromText('POINT(10.3958 63.4269)', 1004326),
-> ST_GeomFromText('POINT(-0.0761 51.5081)', 1004326)
-> ) / 1000 AS km;
+-------------------+
| km |
+-------------------+
| 1464.250079639081 |
+-------------------+
1 row in set (0,00 sec)
Расстояние действительно то же самое.
Между определениями SRS, которые установлены по умолчанию и определенными пользователем SRS, нет существенной разницы. MySQL относится к ним точно так же, и при использовании пользовательской SRS нет штрафа за производительность.
Какой SRID следует выбрать для вашей домашней SRS? SRID представляет собой неподписанное 32-битное целое число, поэтому у нас есть 4294967296 возможности для начала. Но в будущем MySQL будет расширен больше SRS, и вы должны выбрать SRID, который не вступает в конфликт с любыми новыми системными SRS. В настоящее время MySQL резервирует следующие диапазоны SRID:
Нам разрешено создавать SRS в этих диапазонах. В конце концов, у нас есть привилегия SUPER. Но MySQL предупреждает нас, что это не очень хорошая идея:
mysql> CREATE SPATIAL REFERENCE SYSTEM 1 -> NAME 'Don\'t do this at home, kids' -> 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["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; Query OK, 0 rows affected, 1 warning (0,00 sec) Warning (Code 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.
Поэтому, пожалуйста, будьте внимательны и не делайте этого. 🙂
Как и ожидалось, мы также можем отказаться от SRS, которые мы создаем:
mysql> DROP SPATIAL REFERENCE SYSTEM 1004326; Query OK, 0 rows affected (0,00 sec)
И все! Мы также можем отказаться от SRS в зарезервированных регионах, включая те, которые установлены по умолчанию, но, опять же, пожалуйста, прислушайтесь к предупреждению и не делайте этого. Это не приятно.
Но мы не можем всегда отказываться от SRS, даже если мы SUPER. Рассмотрим этот пример:
mysql> CREATE SPATIAL REFERENCE SYSTEM 2004326 -> NAME 'Copy of WGS 84' -> 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["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]'; Query OK, 0 rows affected (0,00 sec) mysql> CREATE TABLE places ( -> id INTEGER PRIMARY KEY, -> name VARCHAR(200) NOT NULL, -> position POINT SRID 2004326 NOT NULL -> ); Query OK, 0 rows affected (0,00 sec) mysql> DROP SPATIAL REFERENCE SYSTEM 2004326; ERROR 3716 (SR005): Can't modify SRID 2004326. There is at least one column depending on it.
Поскольку MySQL требует определения SRS для большинства пространственных операций, он отслеживает SRID, упомянутые в ограничениях столбцов, и останавливает нас, если мы попытаемся удалить SRS, который используется. В этом случае это только столбец, который ограничен SRID, но также могут быть индексы в этом столбце. Если SRS исчезнет, пока существует индекс, использующий его, MySQL не будет знать, как искать или обновлять индекс. Поэтому нам не разрешено удалять SRS, которые используются.
В этом случае у нас нет данных в таблице, поэтому мы можем просто удалить или изменить ограничение SRID, чтобы указать на другой SRID:
mysql> ALTER TABLE places MODIFY COLUMN position POINT SRID 4326 NOT NULL; Query OK, 0 rows affected (0,00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DROP SPATIAL REFERENCE SYSTEM 2004326; Query OK, 0 rows affected (0,00 sec)
Обратите внимание, что эта защита SRS применяется только к SRID, указанным в определениях таблиц. MySQL не отслеживает SRID, используемые в самих значениях. В этом примере столбец позиции не ограничен каким-либо SRID, но новый SRID используется в значениях точки в этом столбце:
mysql> CREATE SPATIAL REFERENCE SYSTEM 2004326
-> NAME 'Copy of WGS 84'
-> 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["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]';
Query OK, 0 rows affected (0,00 sec)
mysql> CREATE TABLE places (
-> id INTEGER PRIMARY KEY,
-> name VARCHAR(200) NOT NULL,
-> position POINT NOT NULL
-> );
Query OK, 0 rows affected (0,00 sec)
mysql> INSERT INTO places VALUES (0, 'Origin', ST_GeomFromText('POINT(0 0)', 2004326));
Query OK, 1 row affected (0,00 sec)
mysql> INSERT INTO places VALUES (1, 'Trondheim', ST_GeomFromText('POINT(63.4305 10.3950)', 2004326));
Query OK, 1 row affected (0,00 sec)
mysql> DROP SPATIAL REFERENCE SYSTEM 2004326;
Query OK, 0 rows affected (0,00 sec)
Теперь мы находимся в ситуации, когда SRID, используемый двумя нашими геометриями, в настоящее время не определен. MySQL будет громко жаловаться на это. Например, это не позволит нам делать какие-либо вычисления:
mysql> SELECT ST_Distance(a.position, b.position) FROM places AS a, places AS b; ERROR 3548 (SR001): There's no spatial reference system with SRID 2004326.
Зачем? Потому что вычисление зависит от определения SRS. MySQL ничего не знает об этой SRS, если она географическая или проецируемая, какова форма эллипсоида или какая единица используется. Но мы не все потеряли. Мы все еще можем посмотреть на координаты:
mysql> SELECT ST_SRID(position), ST_AsText(position) FROM places; +-------------------+-----------------------+ | ST_SRID(position) | ST_AsText(position) | +-------------------+-----------------------+ | 2004326 | POINT(0 0) | | 2004326 | POINT(10.395 63.4305) | +-------------------+-----------------------+ 2 rows in set, 4 warnings (0,00 sec) Warning (Code 3519): There's no spatial reference system with SRID 2004326. Warning (Code 3565): There's no spatial reference system with SRID 2004326. The axis order is unknown. Warning (Code 3519): There's no spatial reference system with SRID 2004326. Warning (Code 3565): There's no spatial reference system with SRID 2004326. The axis order is unknown.
Мы должны страдать от некоторых предупреждений, но мы не можем обвинять кого-либо в этом. Это наша собственная ошибка для сброса SRS, который использовался. Как выйти из этой ситуации? Мы можем либо воссоздать нашу SRS, либо мы можем отнести наши данные к другому (существующему) SRID. В этом случае мы знаем, что SRID 2004326 на самом деле совпадает с SRID 4326, поэтому мы можем изменить наши данные на этот SRID:
mysql> UPDATE places SET position = ST_SRID(position, 4326); Query OK, 2 rows affected (0,09 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> SELECT ST_Distance(a.position, b.position) FROM places AS a, places AS b; +-------------------------------------+ | ST_Distance(a.position, b.position) | +-------------------------------------+ | 0 | | 7088897.097920951 | | 7088897.097920951 | | 0 | +-------------------------------------+ 4 rows in set (0,00 sec)
Это завершает наш тур по CREATE и DROP SPATIAL REFERENCE SYSTEM. Попробуйте, получайте удовольствие, и, пожалуйста, оставайтесь за пределами зарезервированных диапазонов SRID!
Благодарим вас за использование MySQL!
