Сравнение реализаций гео-решений в СУБД
Привет, Хабр! Сегодня быстро разберём, как разные СУБД справляются с геоданными. Вопрос простой: если нужно работать с координатами, полигонами, считать расстояния — какая база справится лучше? В сравнение пойдут 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 | Да |
| Да | Средняя |
|
| Комплексные геоданные и карты, аналитика, ГИС |
MySQL | Нет |
| Нет | Высокая |
|
| Простые геозапросы, базовая фильтрация |
Oracle Spatial | Да |
| Да | Сложная |
|
| Корпоративные GIS, территориальный анализ |
SQL Server | Частично |
| Частично | Средняя |
|
| Базовые геозапросы, ограниченный анализ |
MongoDB | Да (только | GeoJSON | Нет | Высокая |
|
| Лёгкие запросы в NoSQL среде, фильтрация по локациям |
Итак, мой выбор пал на PostqreSQL, однако, для легких задач лучше не переусердствовать и обратиться к простым решениям.
Кстати, уже скоро на бесплатном вебинаре от OTUS, спикеры расскажут о том, что нового в PostgreSQL 17. Регистрация доступна по ссылке.