# 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
-- стовпець 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.

=> /uk/portfolio/improved-gis-map-performance-10x-on-postgresql/ Розв'язанням став перехід на PostgreSQL із
PostGIS
=> /uk/portfolio/resolved-1000-issues-in-geographical-data/ PostGIS виконував просторові запити й індексацію
=> https://postgis.net/documentation/ PostGIS Documentation
=> https://postgis.net/docs/using_postgis_dbmanagement.html Data Management
=> https://postgis.net/workshops/postgis-intro/indexing.html Spatial Indexing
=> https://postgis.net/docs/ST_DWithin.html ST_DWithin
=> https://www.ogc.org/standards/sfa/ Simple Feature Access – Part 1

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