Пространственные типы данных
Точки, линии и полигоны в SQL
«Пространственные типы данных» — бесплатный урок SQL Academy на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.
Что такое пространственные данные
Пространственные данные описывают местоположение, форму и площадь объектов реального мира. В отличие от обычного числа или строки, пространственное значение содержит геометрический смысл — точку на карте, участок дороги или границу страны.
PostGIS — это расширение PostgreSQL, добавляющее мощные пространственные типы данных и функции и превращающее базу данных в полноценную географическую информационную систему (GIS).
-- Enable the PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;Тип GEOMETRY
Основным пространственным типом в PostGIS является геометрия. Она хранит фигуры в плоском (евклидовом) координатном пространстве. При создании столбца можно задать подтип, например POINT, LINESTRING или POLYGON, в качестве ограничения.
Указание SRID (идентификатор системы пространственной привязки) вместе с подтипом сообщает PostGIS, какую систему координат используют Ваши данные.
CREATE TABLE locations (
id SERIAL PRIMARY KEY,
name TEXT,
geom geometry(POINT, 4326)
);Добавление POINT
POINT представляет отдельное местоположение с координатами X (долгота) и Y (широта). Функция ST_GeomFromText преобразует строку Well-Known Text (WKT) в значение геометрии.
SRID 4326 — это система координат WGS 84, используемая устройствами GPS по всему миру.
INSERT INTO locations (name, geom)
VALUES
('Eiffel Tower', ST_GeomFromText('POINT(2.2945 48.8584)', 4326)),
('Big Ben', ST_GeomFromText('POINT(-0.1246 51.5007)', 4326)),
('Colosseum', ST_GeomFromText('POINT(12.4922 41.8902)', 4326));
SELECT name, ST_AsText(geom) AS wkt FROM locations;Тип LINESTRING
LINESTRING представляет упорядоченную последовательность из двух или более точек, соединённых прямыми отрезками. Этот тип идеально подходит для представления дорог, рек и любых линейных объектов.
Каждая пара чисел в WKT — это одна вершина (долгота, широта). PostGIS хранит весь путь как одно значение геометрии.
CREATE TABLE roads (
id SERIAL PRIMARY KEY,
name TEXT,
path geometry(LINESTRING, 4326)
);
INSERT INTO roads (name, path)
VALUES (
'High Street',
ST_GeomFromText(
'LINESTRING(-0.125 51.500, -0.120 51.502, -0.115 51.505)',
4326
)
);
SELECT name, ST_Length(path) AS length_degrees FROM roads;Тип POLYGON
POLYGON определяет замкнутую область. Его внешняя граница представляет собой кольцо — замкнутый LINESTRING, у которого первая и последняя точки совпадают. У полигона также могут быть внутренние кольца (отверстия), например для представления озера внутри парка.
Все кольца должны быть замкнутыми: повторите начальную координату в конце.
CREATE TABLE zones (
id SERIAL PRIMARY KEY,
name TEXT,
area geometry(POLYGON, 4326)
);
INSERT INTO zones (name, area)
VALUES (
'Central Park Approx',
ST_GeomFromText(
'POLYGON((-73.9817 40.7681, -73.9580 40.7681,
-73.9580 40.7964, -73.9817 40.7964,
-73.9817 40.7681))',
4326
)
);
SELECT name, ST_Area(area) AS area_sq_deg FROM zones;GEOGRAPHY и GEOMETRY
PostGIS предоставляет два семейства пространственных типов: геометрия использует плоскую декартову плоскость, а география моделирует Землю как искривлённый сфероид.
Используйте географию, когда Вам нужны точные расстояния в метрах в реальном мире на больших территориях. Используйте геометрию, когда работаете в локальной проецируемой системе координат или Вам нужна максимальная производительность.
CREATE TABLE cities (
id SERIAL PRIMARY KEY,
name TEXT,
geog geography(POINT, 4326)
);
INSERT INTO cities (name, geog) VALUES
('New York', ST_GeogFromText('POINT(-74.0060 40.7128)')),
('London', ST_GeogFromText('POINT(-0.1278 51.5074)'));
-- Distance in metres using geography
SELECT
a.name AS from_city,
b.name AS to_city,
ROUND(ST_Distance(a.geog, b.geog)::NUMERIC) AS distance_m
FROM cities a, cities b
WHERE a.name = 'New York' AND b.name = 'London';Well-Known Text (WKT) и WKB
Пространственные данные можно сериализовать в двух стандартных форматах. Well-Known Text (WKT) удобен для чтения человеком: POINT(2.29 48.86). Well-Known Binary (WKB) — это компактное двоичное представление, используемое внутри системы и для передачи данных.
PostGIS предоставляет функции ST_AsText и ST_AsBinary для преобразования между внутренним хранилищем и этими форматами.
SELECT
name,
ST_AsText(geom) AS wkt,
ST_AsEWKT(geom) AS ewkt,
encode(ST_AsBinary(geom), 'hex') AS wkb_hex
FROM locations
LIMIT 2;MULTIPOINT, MULTILINESTRING, MULTIPOLYGON
PostGIS также поддерживает типы составной геометрии, которые объединяют несколько фигур одного вида в одно значение. Это полезно, когда один объект реального мира состоит из раздельных частей — например, архипелаг представляется как MULTIPOLYGON.
Все стандартные пространственные функции работают с составной геометрией таким же образом.
SELECT ST_AsText(
ST_GeomFromText(
'MULTIPOLYGON(
((0 0, 4 0, 4 4, 0 4, 0 0)),
((10 10, 14 10, 14 14, 10 14, 10 10))
)'
)
) AS multi_poly;
SELECT ST_NumGeometries(
ST_GeomFromText(
'MULTIPOLYGON(
((0 0, 4 0, 4 4, 0 4, 0 0)),
((10 10, 14 10, 14 14, 10 14, 10 10))
)'
)
) AS part_count;GEOMETRYCOLLECTION
GEOMETRYCOLLECTION — самый универсальный пространственный тип: он может содержать в одном значении сочетание точек, линий и полигонов. Такая гибкость полезна, когда набор данных может содержать разнородные типы геометрии, хранящиеся в одном столбце.
Отдельные элементы можно извлечь с помощью ST_GeometryN.
SELECT ST_AsText(
ST_GeomFromText(
'GEOMETRYCOLLECTION(
POINT(1 1),
LINESTRING(0 0, 1 1, 2 2),
POLYGON((0 0, 3 0, 3 3, 0 3, 0 0))
)'
)
) AS collection;
SELECT ST_AsText(
ST_GeometryN(
ST_GeomFromText(
'GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(0 0, 1 1))'
),
1
)
) AS first_member;Пространственные индексы GIST
Пространственные запросы, проверяющие включение или близость, должны сравнивать геометрию каждой строки, что может привести к просмотру миллионов фигур. Индекс GIST значительно ускоряет эту операцию, индексируя ограничивающие рамки геометрий и позволяя PostgreSQL пропускать строки, которые заведомо не могут соответствовать условию.
Всегда создавайте индекс GIST для столбцов геометрии или географии, которые часто участвуют в запросах.
-- Create a GIST index on a geometry column
CREATE INDEX idx_locations_geom
ON locations
USING GIST (geom);
-- PostgreSQL will now use the index for spatial operators
EXPLAIN ANALYZE
SELECT name
FROM locations
WHERE ST_DWithin(
geom,
ST_GeomFromText('POINT(2.3 48.9)', 4326),
1.0
);Чтение свойств геометрии
PostGIS предоставляет функции доступа для извлечения свойств любой геометрии. ST_X и ST_Y возвращают координаты точки, ST_SRID возвращает идентификатор системы пространственной привязки, а ST_GeometryType возвращает имя подтипа.
Эти функции необходимы для просмотра и проверки пространственных данных.
SELECT
name,
ST_X(geom) AS longitude,
ST_Y(geom) AS latitude,
ST_SRID(geom) AS srid,
ST_GeometryType(geom) AS geom_type,
ST_IsValid(geom) AS is_valid
FROM locations;Быстрая проверка
Проверьте, насколько хорошо Вы понимаете пространственные типы данных PostGIS.
Итоги урока
В этом уроке Вы изучили основные пространственные типы данных, предоставляемые PostGIS. Вы узнали, что POINT, LINESTRING и POLYGON — это три базовых подтипа геометрии, а варианты составной геометрии (MULTIPOINT, MULTILINESTRING, MULTIPOLYGON) и универсальный тип GEOMETRYCOLLECTION охватывают более сложные фигуры.
Вы увидели разницу между плоским типом геометрии и сфероидальным типом географии, а также узнали, как выбирать между ними. Well-Known Text (WKT) позволяет читать и записывать пространственные значения в виде понятных человеку строк, а индексы GIST обеспечивают высокую скорость пространственных запросов при больших объёмах данных. Функции доступа, такие как ST_X, ST_SRID и ST_GeometryType, позволяют напрямую просматривать любое значение геометрии в SQL.
Часто задаваемые вопросы
Урок «Пространственные типы данных» бесплатный?
Да — полный текст урока «Пространственные типы данных» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.
Чему я научусь в уроке «Пространственные типы данных»?
Точки, линии и полигоны в SQL Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Academy?
Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.
Сколько времени занимает урок «Пространственные типы данных»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Academy?
Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Пространственные типы данных
- Расстояние и ближайшие соседи
- Пространственные объединения и вхождение
- Пространственные индексы (GiST)