# 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 виправдовує своє місце, коли питання стосуються відстані, вкладеності чи перетину або коли в даних з'являються лінії й полігони, а не лише окремі точки. ## Як це виглядає на практиці Заради PostGIS нерідко міняють базу даних. Картографічний застосунок, що обслуговував просторові запити в реальному часі для багатьох користувачів, працював на Microsoft SQL Server без вбудованої геопросторової індексації, і затримки шкодили і карті, і сервісам за нею. [Розв'язанням став перехід на PostgreSQL із PostGIS](/uk/portfolio/improved-geographical-map-application-performance-by-10x-through-6/): схема, спроєктована для просторових даних, індекси GiST і SP-GiST на стовпцях geometry та geography і обмеження на просторові координати. Понад 50 мільйонів просторових записів пройшли через ETL-конвеєри, які дорогою перетворили їх у EPSG:4326. На PostGIS просторовий запит у середньому відповідав менш ніж за 250 мілісекунд, тоді як на старій базі на це йшло 2,5 секунди, тож виграш склав 10 разів, а навантаження на CPU бекенду водночас знизилося на 65%. Інша робота почалася з даних, а не зі швидкості. Масштабний геопросторовий аналіз виявив понад 1 000 невідповідностей у географічних наборах даних і часових рядах. Для пошуку й виправлення просторових помилок використовували GDAL, QGIS і ArcGIS, а [PostGIS виконував просторові запити й індексацію](/uk/portfolio/resolved-1-000-issues-in-geographical-data-and-7/); процедури на 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](https://postgis.net/documentation/) — власний покажчик проєкту до посібника, FAQ і навчальних матеріалів. - [Data Management](https://postgis.net/docs/using_postgis_dbmanagement.html) — geometry і geography, SRID, правила дійсності та три типи індексів так, як їх визначає сам посібник. - [Spatial Indexing](https://postgis.net/workshops/postgis-intro/indexing.html) — розділ воркшопу про обмежувальні прямокутники й функції, які користуються індексом без підказки. - [ST_DWithin](https://postgis.net/docs/ST_DWithin.html) — довідкова сторінка перевірки відстані з одиницями для кожного типу. - [Simple Feature Access – Part 1](https://www.ogc.org/standards/sfa/) — стандарт OGC, на якому стоять типи геометрії, що їх зберігає PostGIS.