Пространственные объединения и вхождение
Определяйте, какие точки находятся в каких областях
«Пространственные объединения и вхождение» — бесплатный урок SQL Academy на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.
Что такое пространственное соединение
Пространственное соединение объединяет две таблицы на основе географической связи, а не совпадения ключей. Вместо вопроса «равен ли этот ID тому ID?» задаются вопросы вроде «находится ли эта точка внутри этого полигона?» или «перекрываются ли эти две фигуры?»
PostGIS расширяет PostgreSQL типами геометрии и пространственными функциями, благодаря которым такие соединения становятся возможными. Результат аналогичен обычному SQL JOIN — строки из обеих таблиц объединяются, — но условие имеет геометрический характер.
Настройка таблиц для примеров
Давайте создадим две таблицы для работы: cities, содержащую координаты точек, и countries, содержащую границы полигонов. В обеих используется тип GEOMETRY из PostGIS с SRID 4326 (стандартные широта и долгота WGS84).
Идентификатор пространственной системы координат (SRID) сообщает PostGIS, какую систему координат использовать. SRID 4326 — наиболее распространённый вариант для данных GPS.
CREATE TABLE countries (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
border GEOMETRY(POLYGON, 4326)
);
CREATE TABLE cities (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
location GEOMETRY(POINT, 4326)
);Добавление данных для примера
Мы воспользуемся ST_GeomFromText, чтобы добавлять значения геометрии в формате текста с общеизвестной структурой (WKT). WKT — стандартное текстовое представление геометрий: POINT(lon lat) для точек и POLYGON((...)) для полигонов.
Обратите внимание: в WKT долгота указывается перед широтой — это соответствует соглашению X, Y в математике.
INSERT INTO countries (name, border) VALUES
('France', ST_GeomFromText('POLYGON((-5 42, 8 42, 8 51, -5 51, -5 42))', 4326)),
('Spain', ST_GeomFromText('POLYGON((-9 36, 3 36, 3 44, -9 44, -9 36))', 4326));
INSERT INTO cities (name, location) VALUES
('Paris', ST_GeomFromText('POINT(2.35 48.85)', 4326)),
('Madrid', ST_GeomFromText('POINT(-3.70 40.42)', 4326)),
('Bordeaux', ST_GeomFromText('POINT(-0.58 44.84)', 4326)),
('Lisbon', ST_GeomFromText('POINT(-9.14 38.72)', 4326));ST_Contains: основная функция
ST_Contains(geometry A, geometry B) возвращает TRUE, когда геометрия A полностью содержит геометрию B. В нашем случае ST_Contains(country.border, city.location) возвращает истину, когда точка города находится внутри полигона страны.
Это основа соединений по включению в PostGIS. Функция входит в стандарт OGC и работает с любым сочетанием типов геометрии.
SELECT
ci.name AS city,
co.name AS country
FROM cities ci
JOIN countries co
ON ST_Contains(co.border, ci.location);ST_Within: обратная перспектива
ST_Within(geometry A, geometry B) — точная противоположность ST_Contains: функция возвращает TRUE, когда геометрия A полностью находится внутри геометрии B. ST_Within(city, country) логически эквивалентна ST_Contains(country, city).
В данном случае обе функции дают один и тот же результат. Выбор между ними зависит от удобства чтения — выберите вариант, который естественнее выглядит в вашем запросе.
-- These two queries return identical results:
-- Using ST_Contains (country contains city)
SELECT ci.name, co.name
FROM cities ci
JOIN countries co ON ST_Contains(co.border, ci.location);
-- Using ST_Within (city is within country)
SELECT ci.name, co.name
FROM cities ci
JOIN countries co ON ST_Within(ci.location, co.border);Поиск несопоставленных точек с помощью LEFT JOIN
Обычный JOIN исключает города, которые не находятся внутри полигона ни одной страны. Используйте LEFT JOIN вместе с проверкой WHERE ... IS NULL, чтобы найти точки, для которых нет содержащего их полигона. Это полезно для выявления проблем с качеством данных или точек за пределами области покрытия.
SELECT
ci.name AS city,
co.name AS country
FROM cities ci
LEFT JOIN countries co
ON ST_Contains(co.border, ci.location)
ORDER BY co.name NULLS LAST;Подсчёт точек в каждом полигоне
Пространственные соединения особенно полезны в сочетании с агрегацией. Соединив города со странами, а затем сгруппировав данные по стране, можно подсчитать, сколько точек находится внутри каждого полигона. Этот шаблон очень распространён в географическом анализе: подсчёт магазинов по регионам, мероприятий по районам, датчиков по зонам и так далее.
SELECT
co.name AS country,
COUNT(ci.id) AS city_count
FROM countries co
LEFT JOIN cities ci
ON ST_Contains(co.border, ci.location)
GROUP BY co.name
ORDER BY city_count DESC;Использование пространственного индекса для повышения производительности
Пространственные соединения могут быть медленными на больших наборах данных, поскольку каждая точка проверяется на принадлежность каждому полигону. Индекс GIST (обобщённое дерево поиска) позволяет PostGIS использовать предварительный фильтр по ограничивающим прямоугольникам и пропускать большинство сравнений до выполнения точной проверки ST_Contains.
Всегда создавайте индекс GIST для столбцов геометрии, по которым выполняются соединения или фильтрация. Планировщик запросов будет использовать его автоматически.
-- Create GIST indexes on both geometry columns
CREATE INDEX idx_countries_border
ON countries USING GIST (border);
CREATE INDEX idx_cities_location
ON cities USING GIST (location);
-- The same join now benefits from index acceleration
SELECT ci.name, co.name
FROM cities ci
JOIN countries co
ON ST_Contains(co.border, ci.location);ST_Intersects: пересекающиеся геометрии
ST_Intersects(A, B) возвращает TRUE, если две геометрии имеют хотя бы одну общую точку, в том числе просто соприкасаются по границе. Эта функция менее строгая, чем ST_Contains: два перекрывающихся полигона пересекаются, даже если ни один из них полностью не содержит другой.
Для проверок принадлежности точки полигону ST_Intersects и ST_Contains эквивалентны, но ST_Intersects эффективнее использует индекс GIST и на практике часто оказывается предпочтительнее.
-- ST_Intersects is index-friendly and equivalent
-- to ST_Contains for point-in-polygon tests
SELECT
ci.name AS city,
co.name AS country
FROM cities ci
JOIN countries co
ON ST_Intersects(co.border, ci.location);Сопоставление точек с ближайшим полигоном
Если точка находится прямо на границе или очень близко к нескольким полигонам, может понадобиться ближайший полигон, а не все совпадения. ST_Distance измеряет расстояние между двумя геометриями, что позволяет использовать шаблоны ORDER BY + LIMIT или специализированное соединение LATERAL с ORDER BY ... LIMIT 1.
Это называется соединением с ближайшим соседом и часто применяется при привязке треков GPS к участкам дорог или при отнесении точки к ближайшему региону.
-- For each city, find the single closest country centroid
SELECT DISTINCT ON (ci.name)
ci.name AS city,
co.name AS nearest_country,
ST_Distance(ci.location, ST_Centroid(co.border)) AS dist
FROM cities ci
CROSS JOIN countries co
ORDER BY ci.name, dist;Практический пример: рестораны в районах
Рассмотрим реалистичный пример от начала до конца: определим, к какому городскому району относится каждый ресторан, а затем подсчитаем рестораны по районам. Этот шаблон применим к любому сценарию принадлежности точек полигонам: банкоматы в микрорайонах, происшествия в избирательных округах, заказы в зонах доставки.
Запрос использует ST_Within внутри пространственного соединения и группирует результаты для сводного отчёта.
-- Assume tables: districts(id, name, boundary GEOMETRY)
-- restaurants(id, name, location GEOMETRY)
SELECT
d.name AS district,
COUNT(r.id) AS restaurant_count,
STRING_AGG(r.name, ', ' ORDER BY r.name) AS names
FROM districts d
LEFT JOIN restaurants r
ON ST_Within(r.location, d.boundary)
GROUP BY d.name
ORDER BY restaurant_count DESC;Быстрая проверка
Проверьте своё понимание пространственных соединений и включения в PostGIS.
Итоги: пространственные соединения и включение
В этом уроке Вы научились отвечать на вопрос «какие точки находятся в каких областях?» с помощью пространственных соединений PostGIS.
Основные выводы:
- ST_Contains(полигон, точка) — возвращает истину, когда полигон полностью содержит точку.
- ST_Within(точка, полигон) — обратная функция, логически эквивалентная ST_Contains.
- ST_Intersects — более общая проверка перекрытия, эффективно использующая индекс для проверки принадлежности точки полигону.
- GIST-индексы для столбцов геометрии необходимы для высокой производительности при работе с большими объёмами данных.
- Сочетайте пространственные соединения с GROUP BY, чтобы подсчитывать или агрегировать точки по регионам.
- Используйте LEFT JOIN, чтобы находить точки, расположенные за пределами всех полигонов.
Пространственные соединения позволяют выполнять географический анализ непосредственно в SQL — внешний инструмент GIS не требуется.
Часто задаваемые вопросы
Урок «Пространственные объединения и вхождение» бесплатный?
Да — полный текст урока «Пространственные объединения и вхождение» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.
Чему я научусь в уроке «Пространственные объединения и вхождение»?
Определяйте, какие точки находятся в каких областях Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Academy?
Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.
Сколько времени занимает урок «Пространственные объединения и вхождение»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Academy?
Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Пространственные типы данных
- Расстояние и ближайшие соседи
- Пространственные объединения и вхождение
- Пространственные индексы (GiST)