PostGIS

розширення, що додає PostgreSQL геометрію

Що це таке

PostGIS — розширення з відкритим кодом для сервера баз даних PostgreSQL, яке дає змогу зберігати в стовпці форму, наприклад адресу доставки, річку чи межу земельної ділянки, і звичайним SQL питати, як ці форми розташовані одна щодо одної. Воно починалося як дослідницький проєкт у Refractions Research, консалтинговій компанії з ГІС і баз даних, а сьогодні розвивається й випускається як окремий проєкт з відкритим кодом.

Розширення встановлюють у кожну базу окремо однією командою, CREATE EXTENSION postgis;, і разом із ним з’являються три речі.

Просторові типи. geometry описує форми на пласкій площині в тій системі координат, у якій дані зібрали. geography описує їх на вигнутій поверхні Землі й вимірює в метрах.
Просторові функції. Велика родина функцій, назви яких починаються з ST_: ST_Intersects і ST_Contains для взаємного розташування, ST_Area і ST_Length для вимірювань, ST_Transform для переходу між системами координат, ST_AsGeoJSON для передачі результату вебкарті.
Просторові індекси. PostGIS будує R-дерево поверх механізму GiST у PostgreSQL, а для окремих видів даних підтримує ще SP-GiST та індекси діапазонів блоків.

Форми відповідають моделі Simple Features від Open Geospatial Consortium, яку також ухвалено як ISO 19125: точки, лінії й полігони, їхні складені варіанти та колекції змішаних типів. Кожне значення несе SRID — число, яке вказує на рядок у таблиці spatial_ref_sys і пояснює, що означають координати. SRID 4326 — це довгота й широта в градусах на датумі WGS 84, у якому рахує GPS. Проєкційна система натомість вимірює в метрах на схід і на північ від фіксованого початку координат і точна лише в межах регіону, для якого її створили.

Растрові дані, топологія та частина 3D-операцій винесені в окремі розширення, postgis_raster, postgis_topology і postgis_sfcgal, тож база несе лише ті частини, якими справді користується.

Чому це важливо

PostGIS змушує вирішити, де житимуть дані про місця. Без нього координати зазвичай лежать у двох числових стовпцях, а справжня просторова робота відбувається деінде: у коді застосунку, який перебирає рядки й рахує відстані, у настільній ГІС, куди хтось щопонеділка робить експорт, або в окремому геосервісі з власною копією даних. Кожна з цих копій поступово розходиться з рештою. З PostGIS питання «які клієнти живуть за два кілометри від складу, де товар є в наявності» стає одним запитом, що об’єднує залишки, дані клієнтів і форми в одній транзакції, під тими самими резервними копіями й тими самими правами, що й решта бази.

Так просторові дані отримують гарантії, які решта схеми вже має. Обмеження CHECK може відхилити недійсний полігон ще під час вставки. Row-level security може обмежити регіональну команду ділянками її власного регіону. Імпорт, що зламався на півдорозі, відкочується, а не лишає по собі половину шару.

Друге рішення — geometry чи geography, і посібник PostGIS говорить про це прямо. Дані в межах міста чи країни зазвичай найкраще зберігати як geometry у проєкції, підібраній саме для цієї території, бо тоді доступний увесь набір функцій, а обчислення швидкі. Дані, що охоплюють континенти, або випадки, коли користувачі хочуть відстань у метрах між будь-якими точками планети, простіше зробити правильно з geography: цей тип сам рахує на вигнутій поверхні, хоч і має менше функцій.

А іноді відповідь така: PostGIS — більше, ніж потрібно задачі. Застосунку, який лише зберігає широту й довготу кожного магазину та ставить шпильки на карті, цілком досить двох числових стовпців. PostGIS виправдовує своє місце, коли питання стосуються відстані, вкладеності чи перетину або коли в даних з’являються лінії й полігони, а не лише окремі точки.

Як це виглядає на практиці

Заради PostGIS нерідко міняють базу даних. Картографічний застосунок, що обслуговував просторові запити в реальному часі для багатьох користувачів, працював на Microsoft SQL Server без вбудованої геопросторової індексації, і затримки шкодили і карті, і сервісам за нею. Розв’язанням став перехід на PostgreSQL із PostGIS: схема, спроєктована для просторових даних, індекси GiST і SP-GiST на стовпцях geometry та geography і обмеження на просторові координати. Понад 50 мільйонів просторових записів пройшли через ETL-конвеєри, які дорогою перетворили їх у EPSG:4326. На PostGIS просторовий запит у середньому відповідав менш ніж за 250 мілісекунд, тоді як на старій базі на це йшло 2,5 секунди, тож виграш склав 10 разів, а навантаження на CPU бекенду водночас знизилося на 65%.

Інша робота почалася з даних, а не зі швидкості. Масштабний геопросторовий аналіз виявив понад 1 000 невідповідностей у географічних наборах даних і часових рядах. Для пошуку й виправлення просторових помилок використовували GDAL, QGIS і ArcGIS, а PostGIS виконував просторові запити й індексацію; процедури на PL/pgSQL перетворювали дані всередині PostgreSQL, а очищений результат показували в Mapbox. Час виконання просторових запитів скоротився на 35%.

Де виникають проблеми

Відстані повертаються крихітними дробами. Запит питає, наскільки далеко одне від одного два міста, і отримує 0.08. Стовпець має тип geometry у SRID 4326, тож відповідь у градусах, а градус довготи стискається приблизно від 111 кілометрів на екваторі до нуля на полюсах. Приведіть значення до geography, щоб отримати метри, або перенесіть дані через ST_Transform у проєкційну систему, що підходить для території, і вже тоді вимірюйте.

Пошук у радіусі читає кожен рядок. Таблиця має просторовий індекс, а план показує послідовне сканування. Звична причина — фільтр, записаний як ST_Distance(a, b) < 500. У списку посібника з функціями, які самі використовують індекс, є ST_DWithin, а ST_Distance там немає. Обгортання індексованого стовпця в приведення типу чи трансформацію дає той самий ефект, бо індекс на geom нічого не знає про geom::geography.

-- стовпець geography, індексований як зберігається, відстань у метрах
CREATE INDEX depots_geog_idx ON depots USING GIST (geog);

SELECT id FROM depots
WHERE ST_DWithin(geog, ST_MakePoint(12.57, 55.68)::geography, 500);

Якщо дані мають лишитися в стовпці geometry з SRID 4326, додайте стовпець geography, як у прикладі, й проіндексуйте його або створіть індекс на тому самому виразі, який використовує запит. Після великого завантаження запустіть ANALYZE, щоб планувальник знав, що індекс вартий уваги.

Точки опиняються в морі. Кожна форма зберігає свій малюнок, але стоїть не там, часто на іншому континенті. Таку картину дають дві помилки. ST_SetSRID лише змінює мітку геометрії й не чіпає чисел, тож якщо викликати його там, де малося на увазі ST_Transform, метри отримають штамп градусів. Або десь раніше в ланцюжку переплутали довготу й широту: PostGIS, як і GeoJSON, чекає x перед y, тобто довготу перед широтою. Використовуйте ST_SetSRID лише для того, щоб оголосити, чим числа вже є, ST_Transform — щоб їх перерахувати, і перевіряйте кожен імпорт на одній точці, розташування якої відоме.

Обчислення площ і перетинів падають з помилкою. Імпорт із shapefile проходить, а згодом ST_Intersection падає з топологічним винятком. PostGIS навмисно зберігає недійсну геометрію, щоб її можна було знайти й виправити, а полігон, межа якого перетинає сама себе, недійсний. Перевіряйте через ST_IsValid, виправляйте через ST_MakeValid, який зберігає всі вхідні вершини, і додайте обмеження CHECK, щоб погану форму відхиляли на вході, а не виявляли у звіті.

Що почитати далі

PostGIS Documentation — власний покажчик проєкту до посібника, FAQ і навчальних матеріалів.
Data Management — geometry і geography, SRID, правила дійсності та три типи індексів так, як їх визначає сам посібник.
Spatial Indexing — розділ воркшопу про обмежувальні прямокутники й функції, які користуються індексом без підказки.
ST_DWithin — довідкова сторінка перевірки відстані з одиницями для кожного типу.
Simple Feature Access – Part 1 — стандарт OGC, на якому стоять типи геометрії, що їх зберігає PostGIS.

Словник