Сравнение реализаций гео-решений в СУБД

Простой

10 мин

4.8K

Блог компании OTUSАдминистрирование баз данных*

Обзор

Привет, Хабр! Сегодня быстро разберём, как разные СУБД справляются с геоданными. Вопрос простой: если нужно работать с координатами, полигонами, считать расстояния — какая база справится лучше? В сравнение пойдут PostgreSQL (с его крутым PostGIS), MySQL, Oracle, SQL Server и MongoDB.

PostgreSQL + PostGIS

PostGIS — это самый функциональный, по‑хорошему «жирный» набор расширений для PostgreSQL, который делает с пространственными данными всё, что вам может присниться.

Он умеет работать с двумя типами координат: GEOMETRY и GEOGRAPHY. Вот в чем их разница:

GEOMETRY — для «плоских» вычислений, где форма Земли не учитывается. Это оптимально для локальных данных (например, в пределах одного города).
GEOGRAPHY — для сферических вычислений. Этот тип позволяет учитывать кривизну Земли и использовать геодезические координаты, что хорошо впишется для глобальных задач (например, расчёта расстояния между городами на разных континентах).

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

Создание геометрии

Чтобы начать работу, создаем таблицу и добавляем GEOGRAPHY или GEOMETRY поля. Пример на GEOGRAPHY:

CREATE TABLE cafes (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    location GEOGRAPHY(Point, 4326)
);

Здесь GEOGRAPHY(Point, 4326) задаёт тип Point в системе координат 4326 (WGS 84, которая чаще всего используется в GPS и на картах).

Вставка данных: ST_SetSRID и ST_MakePoint

Функции ST_MakePoint и ST_SetSRID — это базовые инструменты для работы с точками в PostGIS.

ST_MakePoint(x, y) — создаёт точку с координатами x (долгота) и y (широта). Но эта точка будет без системы координат.
ST_SetSRID(geometry, srid) — устанавливает систему координат для объекта. Для глобальных координат мы используем SRID 4326 (WGS 84).

Пример вставки точек:

INSERT INTO cafes (name, location)
VALUES
('Кафе А', ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326)),
('Кафе Б', ST_SetSRID(ST_MakePoint(30.3351, 59.9343), 4326));

Индексы: GIST и SP‑GiST

Чтобы ускорить пространственные запросы, создаём индекс. В PostGIS для геоданных используются индексы типа GIST и SP-GiST, которые оптимизированы для поиска по координатам и обработке пространственных данных.

CREATE INDEX idx_cafes_location ON cafes USING GIST (location);

Без индекса запросы на больших данных будут работать медленно. GIST — более распространённый индекс для большинства пространственных запросов, SP-GiST используется реже, но может в целом хорош для специфических структур данных, например, для структур данных, которые имеют иерархическую природу или нерегулярные разделения, например, для работы с неравномерными географическими зонами или деревьями.

Поиск ближайших объектов: ST_Distance и ST_DWithin

Допустим, задача — найти ближайшие кафе в радиусе 5 км от заданной точки. Здесь на хороши две функции:

ST_Distance(geometry, geometry) — вычисляет расстояние между двумя объектами. Если тип данных GEOGRAPHY, результат будет в метрах.
ST_DWithin(geometry, geometry, distance) — возвращает true, если объекты находятся в пределах заданного расстояния. Отличается от ST_Distance тем, что позволяет сразу фильтровать результаты.

Пример запроса на ближайшие кафе в радиусе 5 км:

SELECT name, ST_Distance(location, ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326)) AS distance
FROM cafes
WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326), 5000)
ORDER BY distance;

Здесь:

Используем ST_DWithin, чтобы найти все кафе в радиусе 5 км от точки (37.6173, 55.7558).
Сортируем результат по расстоянию (для этого используем ST_Distance).

Построение буферов и проверка пересечений

Помимо поиска ближайших объектов, PostGIS поддерживает буферные зоны и операции с полигонами. Например:

ST_Buffer(geometry, distance) — создаёт буферную зону вокруг объекта (например, круг радиусом distance метров вокруг точки).
ST_Intersects(geometry, geometry) — проверяет, пересекаются ли два объекта.

Пример: проверка, попадает ли кафе в буфер 2 км от точки:

SELECT name
FROM cafes
WHERE ST_Intersects(location, ST_Buffer(ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326), 2000));

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

MySQL: легковес для базовых задач

MySQL тоже умеет работать с геоданными, но тут всё куда проще. Поддерживается только GEOMETRY, поэтому все вычисления будут на плоскости, без учета кривизны Земли. Подходит для лёгких задач: нужно проверить, попадает ли пользователь в определенный район? Отлично! Но для сложных задач — расчёта сферических расстояний, построения буферов — увы, не подойдет.

MySQL предлагает базовые функции для пространственных запросов. Основные из них:

ST_GeomFromText: конвертирует текстовое описание геометрии в объект GEOMETRY.
ST_Contains: проверяет, содержится ли один объект в другом, например, точка внутри полигона.
ST_Distance: вычисляет расстояние между двумя объектами в плоской системе координат.
ST_Within: проверяет, находится ли объект полностью внутри другого (аналогично ST_Contains)

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

Создадим таблицу delivery_zones, где храним районы доставки как полигоны, и добавим пространственный индекс SPATIAL для ускорения поиска. Важно: SPATIAL INDEX поддерживается только на типе GEOMETRY и не может быть применён к типу GEOGRAPHY, который в MySQL не поддерживается, помимо этого он работает только с таблицами InnoDB и MyISAM.

CREATE TABLE delivery_zones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    zone GEOMETRY NOT NULL,
    SPATIAL INDEX(zone)
);

После юзаемST_GeomFromText, чтобы создать полигон из текстового описания в формате WKT

INSERT INTO delivery_zones (name, zone)
VALUES ('Район А', ST_GeomFromText('POLYGON((x1 y1, x2 y2, x3 y3, x4 y4, x1 y1))'));

P.S: замкнутые полигоны должны начинаться и заканчиваться одной и той же точкой.

Далее приходит время ST_Contains, чтобы проверить, содержится ли точка (координаты x, y) внутри полигона. В MySQL важно учитывать, что функция ST_Contains не учитывает точки на границе полигона. Если точка лежит на границе, результат будет FALSE.

SELECT name
FROM delivery_zones
WHERE ST_Contains(zone, ST_GeomFromText('POINT(x y)'));

Этот запрос вернёт имя района, если точка находится внутри полигона.

Хотя MySQL ограничен в пространственном функционале, он имеет еще несколько хороших операций:

ST_Intersects: проверяет, пересекаются ли два объекта.
ST_Touches: возвращает TRUE, если объекты касаются друг друга (например, если два полигона имеют общую границу).
ST_Centroid: возвращает центральную точку (центроид) для полигона.

Итак, MySQL — это быстрый и лёгкий вариант для базовых задач, таких как проверка точки на вхождение в полигон или простое определение пересечений. Однако без учёта кривизны Земли и с минимальными аналитическими возможностями, он подходит только для ограниченных геозадач.

Oracle Spatial

Oracle Spatial — это полноценный модуль в Oracle Database для работы с пространственными данными. С Oracle Spatial можно выполнять многослойные пространственные операции, анализировать пересечения, строить буферные зоны и управлять большими массивами пространственных данных на корпоративном уровне. Oracle Spatial очень хорош подходит для задач территориального анализа, планирования инфраструктуры, мониторинга и создания довольно сложных ГИС.

Основные возможности:

Oracle Spatial поддерживает SDO_GEOMETRY, который включает объекты типа Point, LineString, Polygon, а также мультиобъекты (например, MultiPolygon).
Функции для проверки пересечений SDO_RELATE, нахождения расстояний SDO_WITHIN_DISTANCE, построения буферов SDO_BUFFER и других операций.
Индексы R‑Tree оптимизированы для поиска и анализа больших объемов пространственных данных.

Рассмотрим их в примерах.

SDO_GEOMETRY — основной тип данных в Oracle Spatial для хранения пространственной информации. Он имеет несколько полей, которые задают тип объекта, координаты, систему координат и элементы геометрии. Структура SDO_GEOMETRY выглядит так:

SDO_GEOMETRY(
    2003,              -- тип объекта (2001 для точки, 2003 для полигона и т.д.)
    4326,              -- система координат (SRID)
    NULL,              -- точка, если это одиночный объект (например, для типа Point)
    SDO_ELEM_INFO_ARRAY(1, 1003, 1), -- описание элементов (начало, тип, интерпретация)
    SDO_ORDINATE_ARRAY(x1, y1, x2, y2, ...) -- массив координат
)

Тип объекта: указывает, что за объект хранится (точка, линия, полигон и т. д.).

SRID: система координат, например, 4326 для WGS 84.

SDO_ELEM_INFO_ARRAY: задаёт структуру геометрии (кольцо, внешний или внутренний контур полигона).

SDO_ORDINATE_ARRAY: массив координат, который хранит реальные данные объекта.

Пример создания полигона (района города):

INSERT INTO coverage_zones (id, name, zone)
VALUES (
    1,
    'Район А',
    SDO_GEOMETRY(
        2003,
        4326,
        NULL,
        SDO_ELEM_INFO_ARRAY(1, 1003, 1),
        SDO_ORDINATE_ARRAY(37.6173, 55.7558, 37.6175, 55.7559, 37.6180, 55.7560, 37.6173, 55.7558)
    )
);

Для ускорения работы с пространственными данными в Oracle Spatial используется R‑Tree индексация. Это деревообразная структура, которая позволяет быстрее находить объекты, попадающие в определённые пространственные границы.

CREATE INDEX idx_coverage_zones ON coverage_zones(zone) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Oracle Spatial предлагает мощные функции для анализа данных. Вот самые полезные из них.

А теперь рассмотрим аналитическую задачу конкурентного анализа. Допустим, нужно понять, где зоны покрытия двух магазинов пересекаются.

Создадим таблицы зон покрытия:

CREATE TABLE coverage_zones (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    zone SDO_GEOMETRY
);

Добавляем зоны в виде полигонов с SDO_GEOMETRY:

INSERT INTO coverage_zones (id, name, zone)
VALUES (
    1,
    'Зона Магазин 1',
    SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(37.6, 55.7, 37.61, 55.71, 37.62, 55.72, 37.6, 55.7))
);

INSERT INTO coverage_zones (id, name, zone)
VALUES (
    2,
    'Зона Магазин 2',
    SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(37.615, 55.705, 37.625, 55.715, 37.635, 55.725, 37.615, 55.705))
);

После используем функцию SDO_RELATE с маской ANYINTERACT, чтобы найти все зоны, которые пересекаются с другими:

SELECT a.name AS zone_a, b.name AS zone_b
FROM coverage_zones a, coverage_zones b
WHERE a.id <= b.id
  AND SDO_RELATE(a.zone, b.zone, 'mask=ANYINTERACT') = 'TRUE';

Маска ANYINTERACT указывает, что нас интересует любое пересечение. Результат запроса покажет пары зон, которые пересекаются.

Oracle Spatial подходит для задач, где требуется детальный территориальный анализ и работа с большими объемами пространственных данных.

SQL Server и MongoDB

SQL Server

SQL Server поддерживает работу с геоданными с помощью типов GEOMETRY и GEOGRAPHY, что позволяет выполнять как плоские, так и сферические расчеты. В отличие оPostgreSQL/PostGIS и Oracle Spatial, SQL Server больше подходит для простых геозадач.

По типам данных все стандартно: GEOMETRY и GEOGRAPHY.

Основные функции:

STGeomFromText / STPointFromText: функции, которые создают геометрические объекты из WKT (Well‑Known Text).
STDistance: вычисляет расстояние между двумя геообъектами. Если тип данных GEOGRAPHY, результат будет в метрах.
STContains: проверяет, содержится ли один объект внутри другого.
STWithin: аналог STContains, проверяет нахождение одного объекта внутри другого.
STIntersects: проверяет, пересекаются ли два объекта.

Теперь создадим таблицу Stores с полем Location для хранения координат и создаём индекс для оптимизации пространственных запросов.

CREATE TABLE Stores (
    StoreID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Location GEOGRAPHY
);

CREATE SPATIAL INDEX idx_location ON Stores(Location);

Для вставки данных используем GEOGRAPHY::Point, задавая широту и долготу.

INSERT INTO Stores (StoreID, Name, Location)
VALUES 
(1, 'Магазин А', GEOGRAPHY::Point(55.7558, 37.6173, 4326)),
(2, 'Магазин Б', GEOGRAPHY::Point(59.9343, 30.3351, 4326));

Допустим, требуется найти все магазины в радиусе 10 км от указанной точки.

DECLARE @UserLocation GEOGRAPHY = GEOGRAPHY::Point(55.7558, 37.6173, 4326);

SELECT Name
FROM Stores
WHERE Location.STDistance(@UserLocation) <= 10000;

Функция STDistance возвращает расстояние между объектами в метрах, т.к используется тип GEOGRAPHY.

Пример использования STIntersects для проверки пересечений:

DECLARE @Polygon GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON((x1 y1, x2 y2, x3 y3, x4 y4, x1 y1))', 4326);

SELECT Name
FROM Stores
WHERE Location.STIntersects(@Polygon) = 1;

Этот запрос вернёт все магазины, попадающие в указанный полигон.

MongoDB

MongoDB позволяет работать с геоданными через коллекции, используя индексы 2dsphere для сферических данных и 2d для плоской геометрии. Основная фича MongoDB в том, что он отлично подходит для структур, где пространственные данные объединены с неструктурированными данными.

Типы индексов для геоданных в MongoDB:

2dsphere: используется для сферических данных (учитывает кривизну Земли), поддерживает запросы типа $nearSphere, $geoWithin и другие. Рекомендуется для географических координат.
2d: для данных в плоской системе координат. Используется реже, т.к не подходит для глобальных расчётов.

Основные гео-функции и операторы:

$near и $nearSphere: операторы для поиска ближайших точек.
$geoWithin: находит объекты внутри заданной области.
$geoIntersects: проверяет пересечение объектов.

Создадим коллекцию stores и добавим индекс 2dsphere на поле location для поддержки сферических запросов.

db.stores.createIndex({ location: "2dsphere" });

MongoDB использует формат GeoJSON для хранения геоданных. Создадим документы с полями location, содержащими координаты точек.

db.stores.insertMany([
    { name: "Магазин А", location: { type: "Point", coordinates: [37.6173, 55.7558] } },
    { name: "Магазин Б", location: { type: "Point", coordinates: [30.3351, 59.9343] } }
]);

Теперь используем оператор $near, чтобы найти магазины в пределах 5 км от указанной точки.

db.stores.find({
    location: {
        $near: {
            $geometry: { type: "Point", coordinates: [37.6173, 55.7558] },
            $maxDistance: 5000  // 5 км
        }
    }
});

Этот запрос вернёт ближайшие магазины в радиусе 5 км от точки [37.6173, 55.7558].

Для поиска объектов внутри полигона используем $geoWithin.

db.stores.find({
    location: {
        $geoWithin: {
            $geometry: {
                type: "Polygon",
                coordinates: [
                    [[x1, y1], [x2, y2], [x3, y3], [x4, y4], [x1, y1]]
                ]
            }
        }
    }
});

Этот запрос вернёт все магазины, которые находятся внутри указанного полигона.

Итак, MongoDB хорош для для приложений с NoSQL структурой, где гео-запросы должны быть лёгкими и быстрыми.

Сравнительная таблица

Подготовили сравнительную таблицу:

База данных	Сфер.координаты	Основные типы данных	Оптимизация	Легкость	Индексы	Функции	Применение
PostgreSQL + PostGIS	Да	`GEOMETRY`, `GEOGRAPHY`	Да	Средняя	`GIST`, `SP-GiST`	`ST_Distance`, `ST_DWithin`, `ST_Buffer`, `ST_Intersects`	Комплексные геоданные и карты, аналитика, ГИС
MySQL	Нет	`GEOMETRY`	Нет	Высокая	`SPATIAL INDEX`	`ST_GeomFromText`, `ST_Contains`, `ST_Distance`, `ST_Within`	Простые геозапросы, базовая фильтрация
Oracle Spatial	Да	`SDO_GEOMETRY`	Да	Сложная	`R-Tree`	`SDO_RELATE`, `SDO_WITHIN_DISTANCE`, `SDO_BUFFER`, `SDO_INTERSECTS`	Корпоративные GIS, территориальный анализ
SQL Server	Частично	`GEOMETRY`, `GEOGRAPHY`	Частично	Средняя	`SPATIAL INDEX`	`STDistance`, `STContains`, `STWithin`, `STIntersects`	Базовые геозапросы, ограниченный анализ
MongoDB	Да (только `2dsphere`)	GeoJSON	Нет	Высокая	`2dsphere`, `2d`	`$near`, `$geoWithin`, `$geoIntersects`	Лёгкие запросы в NoSQL среде, фильтрация по локациям

Итак, мой выбор пал на PostqreSQL, однако, для легких задач лучше не переусердствовать и обратиться к простым решениям.

Кстати, уже скоро на бесплатном вебинаре от OTUS, спикеры расскажут о том, что нового в PostgreSQL 17. Регистрация доступна по ссылке.

Теги:

Хабы: