Перейти до вмісту
Engineer
Меню
Дизайн
Українська

Engineer / Словник

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

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

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

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

Контакти

Ще визначаєтесь, що саме вам потрібно?

Розкажіть, що йде не так або що ви намагаєтесь побудувати. Ми скажемо, що для цього потрібно, скільки це приблизно коштуватиме і чи ми ті люди, які вам потрібні.

Від 1 400 DKK за годину, без ПДВ — і дешевша година на довшому замовленні.

welcome@engineer.company +45 50 10 61 84 Копенгаген, Данія