Spatial mysql что это

Spatial mysql что это Хостинг
На чтение 25 мин Просмотров 110 Опубликовано

SPATIAL — это расширение в MySQL, которое позволяет добавлять географические данные и выполнять простые и сложные запросы на выборку и обработку этих данных. Для использования SPATIAL необходимо выполнить следующие шаги:

1. Создание таблицы с полем типа GEOMETRY или POINT, которое будет использоваться для хранения географических данных.

Пример для создания таблицы:

CREATE TABLE `locations` (

`id` int

NOT NULL AUTO_INCREMENT,

`name` varchar NOT NULL,

`coords` point NOT NULL,

PRIMARY KEY (`id`),

SPATIAL KEY `coords` (`coords`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Вставка данных в таблицу.

Пример для добавления данных:

INSERT INTO `locations` (`name`, `coords`)

VALUES (‘New York’, POINT(40.7128, -74.0060)),

(‘Los Angeles’, POINT(34.0522, -118.2437)),

(‘Chicago’, POINT(41.8781, -87.6298));

3. Выполнение запроса на выборку данных.

Пример для выборки ближайших локаций:

SELECT *

FROM locations

WHERE ST_Distance_Sphere(coords, POINT(40.730610, -73.935242)) < 5000

ORDER BY ST_Distance_Sphere(coords, POINT(40.730610, -73.935242));

Вывод будет содержать названия и координаты локаций, расположенных в радиусе 5000 метров от заданных координат (широта 40.730610 и долгота -73.935242).

Last update on August 19 2022 21:50:42 (UTC/GMT +8 hours)

Содержание
  1. Extensions for Spatial Data
  2. Стенограмма
  3. Добавление пространственной информации
  4. Запрос и фильтрация пространственных данных
  5. Пересекающиеся
  6. Сферическое расстояние
  7. Заключение
  8. Ресурсы
  9. 1 Числовые типы данных
  10. 11.1.1 Синтаксис числового типа данных
  11. 11.1.2 Целочисленные типы (точное значение) — INTEGER, INT, SMALLINT, TINYINT, СРЕДНИЙ, БОЛЬШОЙ
  12. 11.1.3 Типы с фиксированной точкой (точное значение) — DECIMAL, NUMERIC
  13. , DECIMAL ) означает, что значения могут храниться до INTEGER всего цифр, из них NUMERIC цифры могут быть после запятой точка. Например, столбец, определенный как FLOAT будет выглядеть при отображении. MySQL выполняет округление при сохранении значений, поэтому, если вы вставите 999.00009 в FLOAT(7,4) столбец, приблизительный результат 999.0001 . Для максимальной переносимости код, требующий хранения приблизительного числовые значения данных должны использовать FLOAT или DOUBLE PRECISION без уточнения точность или количество цифр. 11.1.5 Тип битового значения — BIT BIT тип данных используется для хранения бит ценности. Тип БИТ( M ) включает хранение M -битные значения. M может варьироваться от 1 до 64. Чтобы указать битовые значения, b'value' обозначение может быть использован. value является двоичным значением записывается с помощью нулей и единиц. Например, b'111' и b'10000000' представляют 7 и 128 соответственно. Видеть Раздел 9.1.5, «Битовые литералы» . Если вы присвоите значение BIT(M) столбец, который меньше M битов, значение слева дополняется нулями. Например, присвоение значения из b'101' до BITM столбец фактически аналогичен назначению b'000101' . Скопление NDB. Максимальный общий размер всех BIT столбцы, используемые в данном NDB стол не должен превышать 4096 бит. 11.1.6 Атрибуты числового типа Ширина дисплея ограничивает диапазон значений, которые могут быть сохранены в столбце. И это не предотвратить значения, превышающие ширину отображения столбца, от отображается правильно. Например, столбец, указанный как SMALLINTM имеет обычный SMALLINT диапазон -32768 до 32767 , и отображаются значения вне диапазона, разрешенного тремя цифрами полностью, используя более трех цифр. При использовании в сочетании с дополнительным (нестандартным) ZEROFILL атрибут, заполнение по умолчанию для пробелы заменяются нулями. Например, для столбца объявлен как INT ZEROFILL , а стоимость 5 извлекается как 0005 . ZEROFILL атрибут игнорируется для столбцы, участвующие в выражениях или UNION запросы. Если вы сохраняете значения, превышающие ширину экрана в целочисленный столбец, содержащий ZEROFILL атрибут, у вас могут возникнуть проблемы, когда MySQL генерирует временные таблицы для некоторых сложных объединений. В этих случаях, MySQL предполагает, что значения данных соответствуют столбцу ширина дисплея. Все целые типы могут иметь необязательный (нестандартный) UNSIGNED атрибут. Беззнаковый тип может быть используется для разрешения только неотрицательных чисел в столбце или когда вы нужен больший верхний числовой диапазон для столбца. Например, если INT столбец UNSIGNED , размер диапазона столбца то же самое, но его концы смещаются вверх, от -2147483648 и 2147483647 до 0 и 4294967295 . Типы с плавающей и фиксированной точкой также могут быть UNSIGNED . Как и в случае целочисленных типов, это атрибут предотвращает сохранение отрицательных значений в столбец. В отличие от целочисленных типов, верхний диапазон столбца значения остаются прежними. Если указать ZEROFILL для числового столбца, MySQL автоматически добавляет UNSIGNED атрибут. Целочисленные типы данных или типы данных с плавающей запятой могут иметь AUTO_INCREMENT атрибут. Когда вы вставляете стоимость NULL в индексированный AUTO_INCREMENT столбец, столбец установлен в следующее значение последовательности. Обычно это value +1, где value является наибольшим значением для столбец в настоящее время в таблице. ( AUTO_INCREMENT последовательности начинаются с 1 .) Хранение 0 в AUTO_INCREMENT столбец имеет тот же эффект, что и хранение NULL , если только NO_AUTO_VALUE_ON_ZERO режим SQL включен. Отрицательные значения для AUTO_INCREMENT столбцы не поддерживаются. 11.1.7 Обработка выхода за пределы допустимого диапазона и переполнения Когда MySQL сохраняет значение в числовом столбце, который находится за пределами допустимый диапазон типа данных столбца, результат зависит от режима SQL, действующего в данный момент: Если включен строгий режим SQL, MySQL отклоняет выходит за пределы диапазона с ошибкой, и вставка завершается неудачно, в соответствии со стандартом SQL. Если ограничительные режимы не включены, MySQL обрезает значение в соответствующую конечную точку диапазона типов данных столбца и вместо этого сохраняет полученное значение. Когда значение вне допустимого диапазона присваивается целочисленному столбцу, MySQL сохраняет значение, представляющее соответствующий конечная точка диапазона типов данных столбца. Когда столбцу с плавающей запятой или фиксированной запятой назначается значение, выходящее за пределы диапазона, подразумеваемого указанным (или по умолчанию) точность и масштаб, MySQL сохраняет значение представляющий соответствующую конечную точку этого диапазона. Предположим, что таблица t1 имеет такое определение: CREATE TABLE t1 (i1 TINYINT, i2 TINYINT UNSIGNED); При включенном строгом режиме SQL возникает ошибка вне диапазона: mysql> SET sql_mode = 'TRADITIONAL'; mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256); ОШИБКА 1264 (22003): значение вне допустимого диапазона для столбца «i1» в строке 1 mysql> SELECT * FROM t1; Пустой набор (0,00 сек) При отключенном строгом режиме SQL происходит отсечение с предупреждениями: mysql> SET sql_mode = ''; mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256); mysql> SHOW WARNINGS; +---------+--------------------------- -------------+ | Уровень | код | Сообщение | +---------+--------------------------- -------------+ | Предупреждение | 1264 | Значение вне диапазона для столбца «i1» в строке 1 | | Предупреждение | 1264 | Значение вне диапазона для столбца «i2» в строке 1 | +---------+--------------------------- -------------+ mysql> SELECT * FROM t1; +------+------+ | я1 | я2 | +------+------+ | 127 | 255 | +------+------+ Когда строгий режим SQL не включен, назначение столбцов преобразования, которые происходят из-за отсечения, сообщаются как предупреждения за ALTER TABLE , LOAD DATA , UPDATE , и многорядный INSERT заявления. В строгом режиме эти операторы терпят неудачу, и некоторые или все значения не вставляется или изменяется, в зависимости от того, является ли таблица таблица транзакций и другие факторы. Подробнее см. Раздел 5.1.10, «Режимы SQL сервера» . mysql> SELECT 9223372036854775807 + 1; ОШИБКА 1690 (22003): значение BIGINT выходит за пределы диапазона в «(9223372036854775807 + 1)» Чтобы в этом случае операция прошла успешно, преобразуйте значение без знака; mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1; +-----------------------------------------------------------+ | CAST(9223372036854775807 AS UNSIGNED) + 1 | +-----------------------------------------------------------+ | 9223372036854775808 | +-----------------------------------------------------------+ Возникнет ли переполнение, зависит от диапазона операндов, поэтому другой способ обработки предыдущего выражения заключается в использовании точная арифметика, потому что DECIMAL значения имеют большую диапазон, чем целые числа: mysql> SELECT 9223372036854775807.0 + 1; +---------------------------+ | 9223372036854775807.0 + 1 | +---------------------------+ | 9223372036854775808.0 | +---------------------------+ Вычитание между целыми значениями, где одно имеет тип UNSIGNED , производит беззнаковый результат по умолчанию. Если бы в противном случае результат был бы отрицательным, результаты ошибки: mysql> SET sql_mode = ''; Запрос выполнен успешно, затронуто 0 строк (0,00 сек.) mysql> SELECT CAST(0 AS UNSIGNED) - 1; ОШИБКА 1690 (22003): значение BIGINT UNSIGNED выходит за пределы диапазона в «(приведение (0 как беззнаковый) - 1)» Если NO_UNSIGNED_SUBTRACTION Режим SQL включен, результат отрицательный: mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION'; mysql> SELECT CAST(0 AS UNSIGNED) - 1; +-------------------------+ | CAST(0 AS UNSIGNED) - 1 | +-------------------------+ | -1 | +-------------------------+ Если результат такой операции используется для обновления UNSIGNED целочисленный столбец, результат обрезается до максимального значения для типа столбца или обрезается до 0 если NO_UNSIGNED_SUBTRACTION включен. Если включен строгий режим SQL, возникает ошибка и столбец остается без изменений.
  14. 11.1.5 Тип битового значения — BIT
  15. 11.1.6 Атрибуты числового типа
  16. 11.1.7 Обработка выхода за пределы допустимого диапазона и переполнения

Extensions for Spatial Data

The Open Geospatial Consortium (OGC) is is an international consortium of more than 250 companies, agencies, and universities participating in the development of publicly available conceptual solutions that can be useful with all kinds of applications that manage spatial data.

Features of MySQL Spatial Data Types

MySQL spatial extensions enable the generation, storage, and analysis of geographic features:

  • Data types for representing spatial values
  • Functions for manipulating spatial values
  • Spatial indexing for improved access times to spatial columns

MySQL supports a number of Spatial Data Types

MySQL has data types that correspond to OpenGIS classes. Some of these types hold single geometry values:

  • GEOMETRY
  • POINT
  • LINESTRING
  • POLYGON

The other data types hold collections of values:

  • MULTIPOINT
  • MULTILINESTRING
  • MULTIPOLYGON
  • GEOMETRYCOLLECTION

Geometry is a word that denotes a geographic feature. Originally the word geometry meant measurement of the earth. Another meaning comes from cartography, referring to the geometric features that cartographers use to map the world. It is a noninstantiable class but has a number of properties, given below are common to all geometry values created from any of the Geometry subclasses.

Use the CREATE TABLE statement to create a table with a spatial column:

 CREATE TABLE geotest (code int
,descrip varchar, g GEOMETRY);

Here is the structure of the table:

 MySQL> describe geotest;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| code | int
 | YES | | NULL | |
| descrip | varchar | YES | | NULL | |
| g | geometry | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

Use the ALTER TABLE statement to add or drop a spatial column to or from an existing table:

 ALTER TABLE geotest ADD pt_loca POINT;
ALTER TABLE geotest DROP pt_loca ;

A Point is a geometry which represents a single location in coordinate space.

Usage of Point

On a city map, a Point object could represent a rail station.

  • X-coordinate value.
  • Y-coordinate value.
  • Point is defined as a zero-dimensional geometry.
  • The boundary of a Point is the empty set.
 MySQL> SELECT X(POINT(18, 23));
+------------------+
| Х(ТОЧКА(18, 23)) |
+------------------+
| 18 |
+------------------+
1 ряд в наборе (0,00 сек)
MySQL> SELECT X(GeomFromText('POINT(18 23)'));
+---------------------------------+
| X(GeomFromText('POINT(18 23)')) |
+---------------------------------+
| 18 |
+---------------------------------+
1 ряд в наборе (0,00 сек)

Кривая — это одномерная геометрия, в общем случае представленная последовательностью точек. Конкретные подклассы Curve определяют тип интерполяции между точками. Кривая — это неинстанцируемый класс.

  • Кривая имеет координаты своих точек.
  • Кривая определяется как одномерная геометрия.
  • Кривая является простой, если она не проходит через одну и ту же точку дважды.
  • Кривая замкнута, если ее начальная точка совпадает с конечной точкой.
  • Граница замкнутой Кривой пуста.
  • Граница незамкнутой кривой состоит из двух ее концов.
  • Простая и замкнутая Кривая — это Линейное Кольцо.

LineString — это Curve с линейной интерполяцией между точками.

Использование LineString

Объекты LineString могли представлять реку на карте страны.

  • LineString имеет координаты сегментов, определяемые каждой последовательной парой точек.
  • LineString является линией, если она состоит ровно из двух точек.
  • LineString является LinearRing, если она и замкнута, и проста.
 MySQL> SET @g = 'LINESTRING(0 0,1 2,2 4)';
Запрос выполнен успешно, затронуто 0 строк (0,00 сек.)
MySQL> ВСТАВИТЬ В ЗНАЧЕНИЯ геотеста (123, "Тестовые данные", GeomFromText(@g));
Запрос выполнен успешно, затронута 1 строка (0,00 сек.)

Поверхность — это двумерная геометрия. Это неинстантируемый класс. Его единственный экземплярный подкласс — Polygon.

  • Поверхность определяется как двумерная геометрия.
  • Спецификация OpenGIS определяет простую поверхность как геометрию, состоящую из одного «участка», связанного с одной внешней границей и нулем или более внутренними границами
  • Границей простой поверхности называется множество замкнутых кривых, соответствующих ее внешней и внутренней границам.

Многоугольник — это плоская Поверхность, представляющая многогранную геометрию. Он определяется одной внешней границей и нулем или более внутренними границами, где каждая внутренняя граница определяет дыру в многоугольнике.

Использование полигона

  • Граница полигона состоит из набора объектов LinearRing (то есть объектов LineString, которые являются как простыми, так и замкнутыми), которые составляют его внешние и внутренние границы.
  • Многоугольник не имеет пересекающихся колец. Кольца на границе многоугольника могут пересекаться в точке, но только по касательной.
  • Многоугольник не имеет линий, шипов или проколов.
  • Многоугольник имеет внутреннюю часть, представляющую собой связное множество точек.
  • Многоугольник может иметь дыры. Внешний вид многоугольника с отверстиями не связан. Каждое отверстие определяет связный компонент экстерьера.
 MySQL> SET @g = 'POLYGON((0 0,8 0,12 9,0 9,0 0),(5 3,4 5,7 9,3 7, 2 5))';
Запрос выполнен успешно, затронуто 0 строк (0,00 сек.)
MySQL> ВСТАВИТЬ В ЗНАЧЕНИЯ геотеста (123, "Тестовые данные", GeomFromText(@g));
Запрос выполнен успешно, затронута 1 строка (0,03 сек.)

Коллекция GeometryCollection — это геометрия, представляющая собой набор одной или нескольких геометрий любого класса.

  • Тип элемента (например, MultiPoint может содержать только элементы Point)
  • Измерение
  • Ограничения на степень пространственного перекрытия между элементами
 MySQL> SET @g = 'GEOMETRYCOLLECTION(POINT(3 2),LINESTRING(0 0,1 3,2 5,3 5,4 7))';
Запрос выполнен успешно, затронуто 0 строк (0,00 сек.)
MySQL> ВСТАВИТЬ В ЗНАЧЕНИЯ геотеста (123, "Тестовые данные", GeomFromText(@g));
Запрос выполнен успешно, затронута 1 строка (0,00 сек.)

MultiPoint — это коллекция геометрии, состоящая из элементов Point. Точки никак не связаны и не упорядочены.

Использование MultiPoint

На карте мира MultiPoint может представлять собой цепь небольших островов.

  • MultiPoint — это нульмерная геометрия.
  • MultiPoint является простым, если никакие два его значения Point не равны (имеют одинаковые значения координат).
  • Границей MultiPoint является пустой набор.

MultiCurve — это коллекция геометрии, состоящая из элементов Curve. MultiCurve — это неинстанцируемый класс.

  • MultiCurve — это одномерная геометрия.
  • MultiCurve проста тогда и только тогда, когда все ее элементы просты; единственные пересечения между любыми двумя элементами происходят в точках, которые находятся на границах обоих элементов.
  • Граница MultiCurve получается путем применения «правила объединения mod 2» (также известного как «правило нечетного-четного»): точка является границей MultiCurve, если она находится в границах нечетного числа Элементы MultiCurve.
  • MultiCurve замкнута, если все ее элементы замкнуты.
  • Граница замкнутой MultiCurve всегда пуста.
 MySQL> SET @g ='MULTIPOINT(0 0, 15 25, 45 65)';
Query OK, 0 rows affected (0.00 sec)
MySQL> INSERT INTO geotest VALUES (123,"Multipoint",GeomFromText(@g));
Query OK, 1 row affected (0.00 sec)

MultiLineString — это коллекция геометрии MultiCurve, состоящая из элементов LineString.

Использование MultiLineString

  • На карте региона MultiLineString может представлять речную систему или систему шоссе.
 MySQL> SET @g = 'MULTILINESTRING((12 12, 22 22), (19 19, 32 18))';
Запрос выполнен успешно, затронуто 0 строк (0,00 сек.)
MySQL> ВСТАВИТЬ В ЗНАЧЕНИЯ геотеста (123, "Multistring", GeomFromText(@g));
Запрос выполнен успешно, затронута 1 строка (0,00 сек.)

MultiSurface — это коллекция геометрии, состоящая из элементов поверхности. MultiSurface — это неинстанцируемый класс. Его единственный экземплярный подкласс — MultiPolygon.

  • Две поверхности MultiSurface не имеют внутренних поверхностей, которые пересекаются.
  • Два элемента MultiSurface имеют границы, пересекающиеся не более чем в конечном числе точек.

MultiPolygon — это объект MultiSurface, состоящий из элементов Polygon.

Использование MultiPolygon

Мультиполигон может представлять систему озер на карте региона.

  • MultiPolygon не имеет двух пересекающихся внутренних элементов Polygon.
  • MultiPolygon не имеет двух пересекающихся элементов Polygon (пересечение также запрещено предыдущим утверждением) или соприкасающихся в бесконечном числе точек.
  • MultiPolygon не может иметь линий разреза, шипов или проколов. MultiPolygon — это обычный замкнутый набор точек.
  • Мультиполигон, содержащий более одного полигона, имеет несвязанную внутреннюю часть. Количество связанных компонентов внутренней части MultiPolygon равно количеству значений Polygon в MultiPolygon
  • Мультиполигон — это двухмерная геометрия.
  • Граница MultiPolygon представляет собой набор замкнутых кривых (значения LineString), соответствующих границам его элементов Polygon.
  • Каждая кривая на границе MultiPolygon находится на границе ровно одного элемента Polygon.
  • Каждая кривая на границе элемента Polygon находится на границе MultiPolygon.
 MySQL> SET @g ='MULTIPOLYGON(((0 0,11 0,12 11,0 9,0 0)),((3 5,7 4,4 7,7 7,3 5)) )';
Запрос выполнен успешно, затронуто 0 строк (0,00 сек.)
MySQL> ВСТАВИТЬ В ЗНАЧЕНИЯ геотеста (123, "Мультиполигон", GeomFromText(@g));
Запрос выполнен успешно, затронута 1 строка (0,00 сек.)

Резюме: Типы данных MySQL

MySQL Data Types slides presentation

Предыдущий:

Типы данных MySQL

Далее:

Подключение и отключение от MySQL

В этой презентации описываются типы данных MySQL, включая числовые, DATETIME, DATE, TIMESTAMP, строковые типы и расширения MySQL для пространственных данных.

Стенограмма

► В стандартном SQL синтаксис DECIMAL(5,2) (где 5 — точность, а 2 — масштаб) может хранить любое значение с пятью цифрами и двумя десятичными знаками. Поэтому диапазон значений будет от -999,99 до 999,99. Синтаксис DECIMAL(M) эквивалентен DECIMAL(M,0). Точно так же синтаксис
DECIMAL эквивалентно DECIMAL(M,0). MySQL поддерживает оба варианта синтаксиса DECIMAL. Значение M по умолчанию равно 10. Если шкала равна 0, значения DECIMAL не содержат десятичной точки или дробной части.

Максимальное количество цифр для DECIMAL равно 65, но фактический диапазон для данного столбца DECIMAL может быть ограничен точностью или масштабом для данного столбца.

Типы битовых значений

► Тип данных BIT используется для хранения значений битового поля. Тип BIT(N) позволяет хранить N-битные значения. N может варьироваться от 1 до 64.

► Для указания битовых значений можно использовать обозначение b’value’. «значение» — это двоичное значение, записанное с использованием нулей и единиц. Например, b’111′ и b’10000000′ представляют 7 и 128 соответственно

.

Типы даты и времени

DATETIME, DATE и TIMESTAMP

► MySQL извлекает и отображает значения TIME в формате «ЧЧ:ММ:СС» или «ЧЧЧ:ММ:СС». Значения TIME от «-838:59:59» до «838:59:59». Часть часов может быть довольно большой, потому что не только тип TIME может использоваться для представления времени суток, т.е. меньше 24 часов.
часов, но и прошедшее время или время интервала между двумя событиями.

► Значения TIME в MySQL могут быть распознаны в различных форматах, некоторые из которых могут включать завершающую дробную часть секунды с точностью до 6 цифр в микросекундах. Диапазон значений TIME: от «-838:59:59,000000» до «838:59:59,000000».

► Тип YEAR — это 1-байтовый тип, используемый для представления значений года. Его можно объявить как YEAR

или YEAR

, чтобы указать ширину отображения в два или четыре символа. Если ширина не указана, по умолчанию используется четыре символа

.

Типы CHAR и VARCHAR

►Типы CHAR и VARCHAR похожи, но различаются способом их хранения и извлечения. Они также различаются по максимальной длине и по тому, сохраняются ли конечные пробелы.

Типы BINARY и VARBINARY

► Типы BINARY и VARBINARY похожи на CHAR и VARCHAR, за исключением того, что они содержат двоичные строки, а не недвоичные строки.

Типы BLOB и TEXT

LONGBLOB Максимальная длина 4294967295 символов

► Типы ENUM

Строковый объект, значение которого выбирается из списка значений, заданных во время создания таблицы. Например —

CREATE TABLE длина ( length ENUM(‘маленький’, ‘средний’, ‘большой’) );

► Типы наборов

Строковый объект, содержащий ноль или более значений, разделенных запятыми (максимум 64).
Значения выбираются из списка значений, заданного во время создания таблицы.

Расширения для пространственных данных

Пространственные данные MySQL

►Открытый геопространственный консорциум публикует стандарт реализации OpenGIS® для географической информации — Простой доступ к функциям — Часть 2: Вариант SQL, документ, который предлагает несколько концептуальных способов расширения SQL RDBMS для поддержки пространственных данных. Эта спецификация доступна на веб-сайте OGC по адресу http://www.opengeospatial.org/standards/sfs.

Пространственные типы данных MySQL

★ Геометрия

★ Точка

★ Линейная строка

★ Полигон

★ Мультипойнт

★ MultiLineString

★ Мультиполигон

★ Геометрическая коллекция

► Геометрия

Геометрия — это слово, обозначающее географический объект. Первоначально слово геометрия означало измерение земли. Другое значение происходит от картографии, относящейся к геометрическим особенностям, которые картографы используют для картирования мира.

Пример: Используйте оператор CREATE TABLE для создания таблицы с пространственным столбцом —

CREATE TABLE geotest (код int

,descrip varchar
, g GEOMETRY);
Пространственные типы данных MySQL

► Точка

Точка — это геометрическая фигура, представляющая одно место в координатном пространстве.

Использование Point: На карте города объект Point может представлять железнодорожную станцию.

Свойства точки:

➔ Значение координаты X

➔ Значение координаты Y

➔ Точка определяется как нульмерная геометрия

➔ Границей точки является пустое множество

► Тип LineString

LineString — это Curve с линейной интерполяцией между точками.

Использование LineString: объекты LineString могут представлять реку на карте страны.

Свойства LineString:

➔ LineString имеет координаты сегментов, определяемые каждой последовательной парой точек

➔ LineString является линией, если она состоит ровно из двух точек

➔ LineString является LinearRing, если она одновременно замкнута и проста

Пространственные типы данных MySQL

► Тип многоугольника

Многоугольник — это плоская Поверхность, представляющая многогранную геометрию. Он определяется одним
внешняя граница и ноль или более внутренних границ, где каждая внутренняя граница
определяет отверстие в многоугольнике.

Использование Polygon: объекты LineString могут представлять реку на карте страны.
Свойства многоугольника:

➔ Граница полигона состоит из набора объектов LinearRing, составляющих его
внешние и внутренние границы

➔ Многоугольник не имеет пересекающихся колец. Кольца на границе многоугольника могут
пересекаются в Точке, но только по касательной

➔ Многоугольник не имеет линий, шипов или проколов

➔ Многоугольник имеет внутреннюю часть, представляющую собой набор связных точек

➔ Полигон может иметь отверстия. Внешний вид многоугольника с отверстиями не соединен

MultiPoint — это коллекция геометрии, состоящая из элементов Point. Точки не
связаны или заказаны в любом случае.
Использование LineString: На карте мира MultiPoint может представлять собой цепочку небольших островов.

Свойства MultiPoint:

➔ MultiPoint — это нулевая геометрия.

➔ MultiPoint является простым, если никакие два его значения Point не равны (имеют одинаковые значения координат).

➔ Границей MultiPoint является пустой набор.

MultiLineString — это коллекция геометрии MultiCurve, состоящая из элементов LineString.

Использование LineString: На карте региона MultiLineString может представлять речную систему или систему шоссе.

MultiPolygon — это объект MultiSurface, состоящий из элементов Polygon.

Использование LineString: MultiPolygon может представлять систему озер на карте региона

Свойства мультиполигона:

➔ Мультиполигон — это двумерная геометрия

➔ Граница MultiPolygon представляет собой набор замкнутых кривых (значения LineString), соответствующих границам его элементов Polygon

➔ Каждая кривая на границе MultiPolygon находится на границе ровно одного элемента Polygon.

➔ Каждая кривая на границе элемента Polygon находится на границе MultiPolygon

MySQL — одна из наиболее широко используемых реляционных баз данных. Большинство PHP-сайтов полагаются на MySQL для сохранения своей информации, что делает ее одной из самых популярных баз данных DB-Engines
наряду с Oracle, SQL Server и PostgreSQL.

Одна из малоизвестных его возможностей заключается в том, что движок поддерживает работу с пространственными данными, позволяя сохранять различные формы (точки, линии, полигоны) и запрашивать информацию на основе пересечений, расстояний или перекрытий. Эта возможность была включена в MySQL давно, но ее стало проще использовать начиная с версии 5.6
, когда были добавлены функции расстояния и пересечения точек.

Пространственные данные могут быть полезны для многих нужд, в том числе:

  • Поиск мест по широте/​долготе
  • Отображение информации и областей в виде слоев на картах
  • Приложения для архитектуры или домашнего дизайна

Добавление пространственной информации

Существует множество ресурсов для импорта пространственной информации в нашу базу данных. В Бюро переписи населения США мы можем найти набор шейп-файлов со всеми штатами и округами США
. Платформа социальной базы данных Back4App
также имеет множество наборов данных, доступных для бесплатной загрузки.

Конечно, мы также можем сами создать таблицу, содержащую любую пространственную информацию. В приведенном ниже примере мы создадим таблицу с именем restaurant, которая будет иметь геометрию имени и местоположения (широта/долгота) для каждой строки.

   restaurants ( name (),  GEOMETRY  , SPATIAL ());

Обратите внимание, что мы добавляем индекс типа SPATIAL в поле местоположения. В основном это связано с производительностью. Как только таблица создана, мы можем заполнить ее некоторыми данными, используя ST_GeomFromText()
функция, которая преобразует строковое представление любой формы в геометрию:

    рестораны    (,  ST_GeomFromText());        рестораны    (,  ST_GeomFromText());        рестораны    (,  ST_GeomFromText());

Запрос и фильтрация пространственных данных

Предположим, мы импортировали список штатов США с веб-сайта выше вместе с полем формы, которое содержит геометрию, связанную с каждым штатом, в states
стол. Затем мы сможем получить геометрию, просто выполнив оператор SELECT:

  state_name,  ST_AsText(форма)    состояния      имя_состояния;      *  |  state_name  |  форма  |    *  |  Алабама  |  ПОЛИГОН((.))  |    |  Аляска  |  ПОЛИГОН((.))  |    |  Аризона  |  ПОЛИГОН((.))  |    |  .  |    *

ST_AsText()
Функция преобразует содержимое формы в строковое представление, которое мы можем прочитать и проанализировать, выполнив обратный процесс ST_GeomFromText()
функция, которую мы использовали выше. Затем в нашем приложении мы можем проанализировать эту строку и обработать ее так, как нам нужно.

Пересекающиеся

Например, мы можем получить список состояний, которые пересекаются с заданной формой. Для этого мы будем использовать ST_Intersects()
функция, которая вернет логическое значение, указывающее, пересекаются ли две фигуры или нет.

  s.state_name    состояния  с    ST_Intersects(s.shape,  ST_GeomFromText(?))      s.имя_состояния;

Мы можем определить нашу геометрию как любую форму, которая нам нужна. Например, если мы хотим знать, в каком состоянии находится данная точка, мы можем установить для нашего параметра что-то вроде 'POINT(40.69 -74.25)'
который должен вернуть штат Нью-Йорк.

Если у нас есть многоугольник, мы можем установить параметр, чтобы указать форму многоугольника, т.е. 'POLYGON((40.69 -74.25, 41.10 -74.25, 41.10 -76.11, 40.69 -76.11, 40.69 -74.25))'
будет означать прямоугольник, пересекающий штаты Нью-Йорк и Пенсильвания. В этом случае наш запрос вернет две строки с именами обоих состояний.

Сферическое расстояние

Вернемся к нашему restaurants
стол. Если мы хотим получить список ресторанов, которые находятся рядом с нашим местоположением, мы можем использовать ST_Distance_Sphere()
функция, которая возвращает расстояние в метрах между двумя точками на сфере (по умолчанию радиус Земли):

  *    рестораны    ST_Distance_Sphere(,  ST_GeomFromText(?))  <=    *        имя;

Этот запрос даст нам список ресторанов, отсортированных по названию, которые находятся в пределах 10 километров от точки, которую мы передаем в качестве параметра. Мы должны установить параметр как 'POINT(lat long)'
где широта и долгота представляют нашу текущую геолокацию.

Example of a query to the restaurants table

Заключение

Мы можем избежать написания собственных подпрограмм для обработки пространственных данных, используя встроенные в MySQL пространственные типы и методы. Для большинства веб-сайтов MySQL предлагает набор функций, обеспечивающих поддержку наиболее распространенных сценариев. А начиная с версии 8.0 MySQL включает расширенную поддержку географических и эллипсоидных вычислений.

Мы должны учитывать, что если нам нужно комплексное решение для надежного пространственного корпоративного приложения, существуют альтернативы, такие как PostgreSQL PostGIS
это могло бы лучше соответствовать нашей цели. В MySQL отсутствуют некоторые расширенные функции, такие как преобразования, обработка пользовательской топологии или индексы BRIN, которые могут повлиять на наш процесс разработки в зависимости от наших потребностей.

На изображении ниже показано несколько предупреждений о погоде, созданных на Leaflet.js
карта, полученная из поля типа GEOMETRY
в MySQL:

map of Argentina with areas shaded yellow

Ресурсы

mysql

база данных

гис

листовка

MySQL поддерживает SQL
типы данных в
несколько категорий: числовые типы, типы даты и времени, строковые
(символьные и байтовые), пространственные типы и
JSON

тип данных. В этой главе представлены
обзор и более подробное описание свойств
типов в каждой категории, а также сводку по хранению типов данных.
требования. Первоначальные обзоры намеренно кратки. Проконсультируйтесь
более подробные описания для получения дополнительной информации о
определенные типы данных, такие как допустимые форматы, в которых вы
может указывать значения.

В описаниях типов данных используются следующие соглашения:

  • Для целочисленных типов M

    указывает на
    максимальная ширина дисплея. Для типов с плавающей и фиксированной точкой
    M

    это общее количество цифр, которое
    можно сохранить (точность). Для строковых типов
    M

    это максимальная длина. Максимум
    допустимое значение M

    зависит от
    тип данных.








1 Числовые типы данных

MySQL поддерживает все стандартные числовые типы данных SQL. Эти типы
включать точные числовые типы данных
( INTEGER

,
SMALLINT

,
DECIMAL

, и
NUMERIC

), так же хорошо как
приблизительные числовые типы данных
( FLOAT

,
REAL

, и
DOUBLE PRECISION

). Ключевое слово
MEMORY

является синонимом
INTEGER

и ключевые слова
DEC

и
FIXED

являются синонимами для
DECIMAL

. MySQL лечит
DOUBLE

как синоним к
DOUBLE PRECISION

(нестандартный
расширение). MySQL также обрабатывает REAL

как синоним DOUBLE PRECISION

(нестандартный вариант), если только
REAL_AS_FLOAT

Режим SQL
включено.

BIT

тип данных хранит битовые значения
и поддерживается для MyISAM

,
INT

,
InnoDB

, и
NDB

столы.

Для получения информации о том, как MySQL обрабатывает назначение вне диапазона
значения в столбцы и переполнение во время вычисления выражения, см.
Раздел 11.1.7, «Обработка выхода за пределы допустимого диапазона и переполнения»
.

Для получения информации о требованиях к хранению числовых данных
типы, см. Раздел 11.7, «Требования к хранению типов данных»
.


11.1.1 Синтаксис числового типа данных

Для целочисленных типов данных M

указывает
максимальная ширина дисплея. Максимальная ширина экрана 255.
Ширина отображения не связана с диапазоном значений, которые может отображать тип.
магазин, как описано в
Раздел 11.1.6, «Атрибуты числового типа»
.

Для типов данных с плавающей запятой и фиксированной запятой
M

это общее количество цифр, которое
можно хранить.

Если указать ZEROFILL
для числового столбца,
MySQL автоматически добавляет UNSIGNED

атрибут столбца.

Типы числовых данных, допускающие UNSIGNED

атрибут также позволяет SIGNED
. Однако эти
типы данных подписаны по умолчанию, поэтому
SIGNED
атрибут не действует.

SERIAL
псевдоним для BIGINT
UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
.

SERIAL DEFAULT VALUE
в определении
Целочисленный столбец является псевдонимом для NOT NULL AUTO_INCREMENT
UNIQUE
.


11.1.2 Целочисленные типы (точное значение) — INTEGER, INT, SMALLINT, TINYINT,
СРЕДНИЙ, БОЛЬШОЙ

Таблица 11.1 Требуемый объем памяти и диапазон для целочисленных типов, поддерживаемых MySQL


11.1.3 Типы с фиксированной точкой (точное значение) — DECIMAL, NUMERIC

Магазины MySQL DECIMAL
значения в двоичном формате.
См. Раздел 12.22, «Точная математика» .
.

В DECIMAL
объявление столбца,
точность и масштаб могут быть (и обычно) указаны. Для
пример:

 зарплата DECIMAL(5,2)

Стандартный SQL требует, чтобы DECIMAL(5,2)
быть
может хранить любое значение с пятью цифрами и двумя десятичными знаками, поэтому
значения, которые могут быть сохранены в salary

диапазон столбца от -999.99
к
999.99
.

В стандартном SQL синтаксис
DECIMAL(M)
является
эквивалентно
DECIMAL( M

,0).
Точно так же синтаксис DECIMAL
эквивалентен
до DECIMAL(M,0)
,
где реализации разрешено решать значение
M

. MySQL поддерживает оба этих
варианты форм DECIMAL
синтаксис. По умолчанию
стоимость M

это 10.

Если шкала равна 0, DECIMAL
значения не содержат
десятичная точка или дробная часть.



11.1.4 Типы с плавающей запятой (приблизительное значение) — FLOAT, DOUBLE

REAL

DOUBLE PRECISION

и
типы
представляют приблизительные числовые значения данных. MySQL использует четыре байта
для значений с одинарной точностью и восемь байтов для значений с двойной точностью
ценности.

MySQL допускает нестандартный синтаксис:
ПЛАВАЮЩАЯ( M

,


)
или
НАСТОЯЩИЙ( M

,

)
или ДВОЙНОЙ
ТОЧНОСТЬ(

    • , SMALLINT

    ).
    Здесь,
    (

    ,
    DECIMAL
    )
    означает, что значения могут храниться до
    INTEGER

    всего цифр, из них
    NUMERIC
    цифры могут быть после запятой
    точка. Например, столбец, определенный как
    FLOAT
    будет выглядеть

    при отображении. MySQL выполняет
    округление при сохранении значений, поэтому, если вы вставите
    999.00009
    в
    FLOAT(7,4)
    столбец, приблизительный результат
    999.0001
    .

    Для максимальной переносимости код, требующий хранения приблизительного
    числовые значения данных должны использовать FLOAT
    или
    DOUBLE PRECISION
    без уточнения
    точность или количество цифр.


    11.1.5 Тип битового значения — BIT

    BIT
    тип данных используется для хранения бит
    ценности. Тип
    БИТ( M

    )     включает
    хранение M

    -битные значения.
    M

    может варьироваться от 1 до 64.

    Чтобы указать битовые значения,
    b'value'
    обозначение
    может быть использован. value

    является двоичным значением
    записывается с помощью нулей и единиц. Например,
    b'111'
    и b'10000000'

    представляют 7 и 128 соответственно. Видеть
    Раздел 9.1.5, «Битовые литералы»
    .

    Если вы присвоите значение
    BIT(M)
    столбец, который
    меньше M

    битов, значение
    слева дополняется нулями. Например, присвоение значения
    из b'101'
    до BITM

    столбец фактически аналогичен назначению
    b'000101'
    .

    Скопление NDB.

    Максимальный общий размер всех BIT

    столбцы, используемые в данном NDB

    стол
    не должен превышать 4096 бит.


    11.1.6 Атрибуты числового типа

    Ширина дисплея ограничивает
    диапазон значений, которые могут быть сохранены в столбце. И это не
    предотвратить значения, превышающие ширину отображения столбца, от
    отображается правильно. Например, столбец, указанный как
    SMALLINTM

    имеет обычный
    SMALLINT

    диапазон
    -32768
    до 32767
    , и
    отображаются значения вне диапазона, разрешенного тремя цифрами
    полностью, используя более трех цифр.

    При использовании в сочетании с дополнительным (нестандартным)
    ZEROFILL
    атрибут, заполнение по умолчанию для
    пробелы заменяются нулями. Например, для столбца
    объявлен как INT ZEROFILL

    , а
    стоимость 5
    извлекается как
    0005
    .

    ZEROFILL
    атрибут игнорируется для
    столбцы, участвующие в выражениях или
    UNION

    запросы.

    Если вы сохраняете значения, превышающие ширину экрана в
    целочисленный столбец, содержащий ZEROFILL

    атрибут, у вас могут возникнуть проблемы, когда MySQL генерирует
    временные таблицы для некоторых сложных объединений. В этих случаях,
    MySQL предполагает, что значения данных соответствуют столбцу
    ширина дисплея.

    Все целые типы могут иметь необязательный (нестандартный)
    UNSIGNED
    атрибут. Беззнаковый тип может быть
    используется для разрешения только неотрицательных чисел в столбце или когда вы
    нужен больший верхний числовой диапазон для столбца. Например,
    если INT

    столбец
    UNSIGNED
    , размер диапазона столбца
    то же самое, но его концы смещаются вверх, от
    -2147483648
    и 2147483647

    до 0
    и 4294967295
    .

    Типы с плавающей и фиксированной точкой также могут быть
    UNSIGNED
    . Как и в случае целочисленных типов, это
    атрибут предотвращает сохранение отрицательных значений в
    столбец. В отличие от целочисленных типов, верхний диапазон столбца
    значения остаются прежними.

    Если указать ZEROFILL
    для числового столбца,
    MySQL автоматически добавляет UNSIGNED

    атрибут.

    Целочисленные типы данных или типы данных с плавающей запятой могут иметь
    AUTO_INCREMENT
    атрибут. Когда вы вставляете
    стоимость NULL
    в индексированный
    AUTO_INCREMENT
    столбец, столбец установлен в
    следующее значение последовательности. Обычно это
    value

    +1    , где
    value

    является наибольшим значением для
    столбец в настоящее время в таблице.
    ( AUTO_INCREMENT
    последовательности начинаются с
    1
    .)

    Хранение 0
    в
    AUTO_INCREMENT
    столбец имеет тот же эффект, что и
    хранение NULL
    , если только
    NO_AUTO_VALUE_ON_ZERO

    режим SQL
    включен.

    Отрицательные значения для AUTO_INCREMENT
    столбцы
    не поддерживаются.


    11.1.7 Обработка выхода за пределы допустимого диапазона и переполнения

    Когда MySQL сохраняет значение в числовом столбце, который находится за пределами
    допустимый диапазон типа данных столбца, результат
    зависит от режима SQL, действующего в данный момент:

    • Если включен строгий режим SQL, MySQL отклоняет
      выходит за пределы диапазона с ошибкой, и вставка завершается неудачно, в
      соответствии со стандартом SQL.

    • Если ограничительные режимы не включены, MySQL обрезает значение
      в соответствующую конечную точку диапазона типов данных столбца
      и вместо этого сохраняет полученное значение.

      Когда значение вне допустимого диапазона присваивается целочисленному столбцу,
      MySQL сохраняет значение, представляющее соответствующий
      конечная точка диапазона типов данных столбца.

      Когда столбцу с плавающей запятой или фиксированной запятой назначается
      значение, выходящее за пределы диапазона, подразумеваемого указанным (или
      по умолчанию) точность и масштаб, MySQL сохраняет значение
      представляющий соответствующую конечную точку этого диапазона.

    Предположим, что таблица t1
    имеет такое определение:

     CREATE TABLE t1 (i1 TINYINT, i2 TINYINT UNSIGNED);

    При включенном строгом режиме SQL возникает ошибка вне диапазона:

     mysql>  SET sql_mode = 'TRADITIONAL'; mysql>  INSERT INTO t1 (i1, i2) VALUES(256, 256); ОШИБКА 1264 (22003): значение вне допустимого диапазона для столбца «i1» в строке 1
mysql>  SELECT * FROM t1; Пустой набор (0,00 сек)

    При отключенном строгом режиме SQL происходит отсечение с предупреждениями:

     mysql>  SET sql_mode = ''; mysql>  INSERT INTO t1 (i1, i2) VALUES(256, 256); mysql>  SHOW WARNINGS; +---------+--------------------------- -------------+
| Уровень | код | Сообщение |
+---------+--------------------------- -------------+
| Предупреждение | 1264 | Значение вне диапазона для столбца «i1» в строке 1 |
| Предупреждение | 1264 | Значение вне диапазона для столбца «i2» в строке 1 |
+---------+--------------------------- -------------+
mysql>  SELECT * FROM t1; +------+------+
| я1 | я2 |
+------+------+
| 127 | 255 |
+------+------+

    Когда строгий режим SQL не включен, назначение столбцов
    преобразования, которые происходят из-за отсечения, сообщаются как предупреждения
    за ALTER TABLE

    ,
    LOAD DATA

    ,
    UPDATE

    , и многорядный
    INSERT

    заявления. В строгом
    режиме эти операторы терпят неудачу, и некоторые или все значения не
    вставляется или изменяется, в зависимости от того, является ли таблица
    таблица транзакций и другие факторы. Подробнее см.
    Раздел 5.1.10, «Режимы SQL сервера»
    .

     mysql>  SELECT 9223372036854775807 + 1; ОШИБКА 1690 (22003): значение BIGINT выходит за пределы диапазона в «(9223372036854775807 + 1)»

    Чтобы в этом случае операция прошла успешно, преобразуйте
    значение без знака;

     mysql>  SELECT CAST(9223372036854775807 AS UNSIGNED) + 1; +-----------------------------------------------------------+
| CAST(9223372036854775807 AS UNSIGNED) + 1 |
+-----------------------------------------------------------+
| 9223372036854775808 |
+-----------------------------------------------------------+

    Возникнет ли переполнение, зависит от диапазона операндов, поэтому
    другой способ обработки предыдущего выражения заключается в использовании
    точная арифметика, потому что
    DECIMAL

    значения имеют большую
    диапазон, чем целые числа:

     mysql>  SELECT 9223372036854775807.0 + 1; +---------------------------+
| 9223372036854775807.0 + 1 |
+---------------------------+
| 9223372036854775808.0 |
+---------------------------+

    Вычитание между целыми значениями, где одно имеет тип
    UNSIGNED
    , производит беззнаковый результат
    по умолчанию. Если бы в противном случае результат был бы отрицательным,
    результаты ошибки:

     mysql>  SET sql_mode = ''; Запрос выполнен успешно, затронуто 0 строк (0,00 сек.)
mysql>  SELECT CAST(0 AS UNSIGNED) - 1;   ОШИБКА 1690 (22003): значение BIGINT UNSIGNED выходит за пределы диапазона в «(приведение (0 как беззнаковый) - 1)»

    Если NO_UNSIGNED_SUBTRACTION

    Режим SQL включен, результат отрицательный:

     mysql>  SET sql_mode = 'NO_UNSIGNED_SUBTRACTION'; mysql>  SELECT CAST(0 AS UNSIGNED) - 1; +-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| -1 |
+-------------------------+

    Если результат такой операции используется для обновления
    UNSIGNED
    целочисленный столбец, результат
    обрезается до максимального значения для типа столбца или обрезается до
    0 если NO_UNSIGNED_SUBTRACTION

    включен. Если включен строгий режим SQL, возникает ошибка и
    столбец остается без изменений.

    Читайте также:  Перемещайтесь по Интернету с легкостью: ваш полный путеводитель по списку источников
    Оцените статью
    Хостинги
    © 2025 Хостинги