PostGIS

Що це таке

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 виправдовує своє місце, коли питання стосуються відстані,
вкладеності чи перетину або коли в даних з'являються лінії й
полігони, а не лише окремі точки.

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

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

Інша робота почалася з даних, а не зі швидкості. Масштабний
геопросторовий аналіз виявив понад 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`.

```sql
i-- стовпець geography, індексований як зберігається, відстань у метрах		engineer.company	70
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.

hЦя сторінка у вебі	URL:https://engineer.company/uk/glossary/postgis/	engineer.company	70
