Геопространственная индексация с PostGIS
Добавляйте расширение PostGIS, храните данные geometry/geography и запрашивайте пространственные отношения с помощью индексов GiST
«Геопространственная индексация с PostGIS» — бесплатный урок SQL Academy на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.
Что такое PostGIS?
Самое мощное геопространственное расширение среди всех открытых СУБД. Оно добавляет типы геометрии и географии, сотни пространственных функций, а также индексы GiST/SP-GiST для быстрых пространственных запросов.
Подключение PostGIS
Это расширение из набора contrib:
CREATE EXTENSION postgis;
SELECT PostGIS_Version();GEOMETRY и GEOGRAPHY
- GEOMETRY — плоская модель, высокая скорость, единицы системы координат (часто метры или градусы)
- GEOGRAPHY — сферическая модель Земли, ниже скорость, единицы всегда метры
Выбирайте GEOGRAPHY для «расстояний на Земле» при работе с данными из разных регионов; GEOMETRY — для проецированных систем координат (региональных GIS и быстрых операций).
Хранение точек
Распространённый вариант: хранить широту и долготу как точку GEOGRAPHY:
CREATE TABLE places (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
location GEOGRAPHY(POINT, 4326)
);
INSERT INTO places (name, location)
VALUES ('Berlin', ST_GeogFromText('SRID=4326;POINT(13.405 52.520)'));SRID
«Идентификатор пространственной системы отсчёта» определяет систему координат. WGS84 (идентификатор 4326) — стандарт для широты и долготы GPS.
Пространственный индекс GiST
Создайте индекс GiST для столбца геометрии:
CREATE INDEX places_location_idx ON places USING GIST (location);
-- Now spatial filters are fast.Ближайший сосед: <->
Оператор KNN <-> вместе с индексом возвращает ближайшие результаты за миллисекунды:
SELECT name, ST_Distance(location, ST_GeogFromText('SRID=4326;POINT(13.4 52.5)')) AS m
FROM places
ORDER BY location <-> ST_GeogFromText('SRID=4326;POINT(13.4 52.5)')
LIMIT 10;В пределах расстояния
Найдите места в пределах 2 км от точки:
SELECT name FROM places
WHERE ST_DWithin(
location,
ST_GeogFromText('SRID=4326;POINT(13.4 52.5)'),
2000 -- meters
);Полигоны и области
Храните и запрашивайте полигоны (границы городов, зоны доставки):
CREATE TABLE zones (
id BIGSERIAL PRIMARY KEY,
name TEXT,
area GEOGRAPHY(POLYGON, 4326)
);
-- Find places inside a zone:
SELECT p.name FROM places p
JOIN zones z ON ST_Within(p.location::GEOMETRY, z.area::GEOMETRY)
WHERE z.name = 'Mitte';Пересечение, содержание, принадлежность
Богатый набор пространственных предикатов: ST_Intersects, ST_Contains, ST_Within, ST_Touches, ST_Disjoint, ST_Equals.
Загрузка GeoJSON
Импортируйте GeoJSON непосредственно в PostGIS:
INSERT INTO zones (name, area)
SELECT 'Mitte', ST_GeogFromGeoJSON('{"type":"Polygon","coordinates":[...]}');Создание векторных плиток
PostGIS 3.0+ может напрямую создавать векторные плитки Mapbox с помощью ST_AsMVT — это позволяет строить интерфейсы карт прямо на основе СУБД.
Итоги
PostGIS превращает PostgreSQL в полноценный движок GIS.
- GEOMETRY — для проецированных систем, GEOGRAPHY — для расстояний на Земле
- Индекс GiST — для пространственных фильтров и KNN
- ST_DWithin, ST_Distance, ST_Within
- SRID 4326 = широта и долгота WGS84
Быстрая проверка
Вы храните местоположение пользователей как GEOGRAPHY(POINT). Какой тип индекса ускорит запросы «найти в пределах 5 км»?
Изучай SQL с ИИ-репетитором — бесплатно
Пиши и запускай код прямо в браузере, получай мгновенную помощь от ИИ-репетитора 24/7 и продолжи учиться на сайте или в приложении.
- Курсы
- 46
- Уроки
- 183
Часто задаваемые вопросы
Урок «Геопространственная индексация с PostGIS» бесплатный?
Да — полный текст урока «Геопространственная индексация с PostGIS» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.
Чему я научусь в уроке «Геопространственная индексация с PostGIS»?
Добавляйте расширение PostGIS, храните данные geometry/geography и запрашивайте пространственные отношения с помощью индексов GiST Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Academy?
Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.
Сколько времени занимает урок «Геопространственная индексация с PostGIS»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Academy?
Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Поиск по триграммам (pg_trgm) для нечёткого сопоставления
- Полнотекстовый поиск с tsvector и GIN
- Геопространственная индексация с PostGIS
- Векторный поиск с pgvector