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

Привет, Хабр! Сегодня быстро разберём, как разные СУБД справляются с геоданными. Вопрос простой: если нужно работать с координатами, полигонами, считать расстояния — какая база справится лучше? В сравнение пойдут 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;

Здесь:

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

2. Сортируем результат по расстоянию (для этого используем 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.

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

1. STGeomFromText / STPointFromText: функции, которые создают геометрические объекты из WKT (Well‑Known Text).

2. STDistance: вычисляет расстояние между двумя геообъектами. Если тип данных GEOGRAPHY, результат будет в метрах.

3. STContains: проверяет, содержится ли один объект внутри другого.

4. STWithin: аналог STContains, проверяет нахождение одного объекта внутри другого.

5. 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: для данных в плоской системе координат. Используется реже, т.к не подходит для глобальных расчётов.

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

1. $near и $nearSphere: операторы для поиска ближайших точек.

2. $geoWithin: находит объекты внутри заданной области.

3. $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 структурой, где гео-запросы должны быть лёгкими и быстрыми.

---

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

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

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

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