Більше

Синтаксис PostGIS (function ()).*


Під час читання документації щодо використання функції PostGIS ST_PixelAsCentroids () мене це трохи збентежило(ST_PixelAsPoints (rast, 1)).*синтаксис, і мені, як новачку в SQL, стало цікаво, чи може хтось пояснити цю угоду і чому вона використовується?

Очевидно, це використовується як метод для роз’єднання складних результатів, але я навіть не знаю, як це називається, щоб правильно погуглити, щоб отримати офіційне розуміння цього.

Я розумію, що ця публікація може бути краще підходить для форуму SQL на Stack, але я побачу, що придумує GIS Stack.


Функція повертає таблицю з набраними стовпцями (схема) та рядками (значеннями)..*в кінці означає "всі стовпці з таблиці".ВИБІРИ * З f (x)дав би подібний результатSELECT (f (x)).*.

Через примхи PostgreSQL функції з кількома стовпцями можуть бути викликані одноразово для кожного стовпця планувальником запитів у наведених вище випадках. Тому рекомендується використовувати Common Table Expression (CTE) або підзапит, щоб запобігти цій поведінці, написавши, наприклад:

SELECT (mf).* FROM (SELECT my_func (x) AS mf FROM some_table OFFSET 0) sub;

або в PostgreSQL 9.3

ВИБІРТЕ mf.* ВІД some_table ВЛІВО ПРИЄДНАЙТЕСЬ ЛАТЕРАЛЬНО my_func (some_table.x) ЯКЩО mf ON true;

Це скоріше питання PostgreSQL, аніж запит PostGIS, але є гарним.

Функції типуST_PixelAsPoints,ST_PixelAsPolygons, іST_PixelAsCentroidsповертає набір складених типів даних запису, які мають кілька полів. Це дуже схоже на ST_Dump, описане тут. Щоб отримати доступ до всіх повернутих полів із цих складених типів даних, вони повинні бути у формі(функція (x)).*, як описано в посібнику PostgreSQL.

Зауважте, щофункція (x).*не працюватиме, і це загальна проблема.


Найближча проблема сусідів у Postgis 2.0 за допомогою GIST Index (функція)

Я ’m намагаюся використовувати нову функцію Postgis 2.0 & lt- & gt (Geometry Distance Centroid), щоб обчислити для кожного рядка моєї таблиці (cosn1) відстань до найближчого багатокутника того ж класу.

Я намагався використати такий код:

Але потім я усвідомлюю застереження:

Примітка: Індекс починає діяти, лише якщо одна з геометрій є константою (не в підзапиті/cte). наприклад ‘SRID = 3005POINT (1011102 450541) ’ :: геометрія замість a.geom

Це означає, що Індекс взагалі не буде використовуватися, і запит займе майже той самий час, що і перед використанням:

Чи може хтось підказати мені обхідний шлях, який дозволить мені покращити продуктивність мого запиту?

Одна відповідь

Виконання деяких тестів на моїй машині показало, що цей оператор & lt- & gt не працює належним чином. Я не впевнений, що це помилка, але він повідомив про нульову відстань на геометриях, що не перекриваються.

Я спробував справедливу традиційну оптимізацію запитів SQL. Оскільки ці несподівані результати з & lt- & gt оператором я замінюю його на st_centroid. Отримав набагато кращі результати у швидкості.

Сподіваюся, що семантика зі st_overlaps залишається незмінною. Принаймні це я зрозумів з документації про & lt- & gt

Для інших типів геометрії повертається відстань між центроїдами обмежувальної коробки з плаваючою комою.

Прискорилося 5,5 тис. Полігонів

5 секунд без просторової індексації.

Я бачу, що деякі люди використовують DISTINCT ON для групування, але не існує групи для того, щоб усунути дублікати.

Ваш запит зі стандартною оптимізацією SQL без помилки st_centroid


4.5. Рядки

А. рядок рядка - це шлях між локаціями. Він набуває вигляду впорядкованої серії з двох або більше точок. Дороги та річки зазвичай представлені у вигляді ліній. Кажуть, що це рядок рядка зачинено якщо він починається і закінчується в одній точці. Кажуть, що так простий якщо він не перетинається і не торкається себе (за винятком його кінцевих точок, якщо він закритий). Рядок рядків може бути обома зачинено та простий.

Вулична мережа Нью -Йорка (nyc_streets) була завантажена раніше в майстерні. Цей набір даних містить такі дані, як ім’я та тип. Одна вулиця реального світу може складатися з багатьох рядків, кожна з яких представляє відрізок дороги з різними ознаками.

Наступний запит SQL поверне геометрію, пов'язану з одним рядком рядка (у стовпці ST_AsText).

Деякі специфічні просторові функції для роботи з рядками рядків:

ST_Length (геометрія) повертає довжину рядка рядка

ST_StartPoint (геометрія) повертає першу координату як точку

ST_EndPoint (геометрія) повертає останню координату як точку

ST_NPoints (геометрія) повертає кількість координат у рядку рядків

Отже, довжина нашого рядка:


Просторове Приєднання до PostGIS

Ми побачили набір даних і перевірили CRS набору даних. Зараз все налаштовано. Щоб об'єднати обидва набори даних, ми можемо використовувати різні просторові відношення, включаючи ST_Within, ST_Contains, ST_Covers або ST_Crosses. У цьому прикладі ми використовуємо ST_Within, щоб з'ясувати, яка точка всередині якого багатокутника.

Результатом є ця таблиця зі списками Airbnb з додатковим стовпцем, у якому зазначено, до якого статистичного коду області належить кожна точка, як показано в таблиці нижче.

Чудово! Просторове з'єднання може стати вашим остаточним результатом, якби ви потім дізналися лише, де належить кожна точка. Однак нам може знадобитися узагальнити результат просторового об’єднання, щоб з’ясувати уявлення про розподіл набору даних.


Давайте ознайомимося з графічним інтерфейсом PgAdmin, який ми будемо використовувати у цьому уроці. Наступний GIF показує інтерфейс. Ми також створюємо розширення PostGIS, яке надає просторові функції, які ми будемо використовувати пізніше.

Ми можемо просто створити розширення PostGIS за допомогою такої команди, реалізованої в інструменті запиту PgAdmin.

Після виконання цієї команди у нас є багато нових просторових функцій.

Давайте також створимо базу даних за допомогою PgAdmin4. Наступні інструкції GIF показують, як створити нову базу даних.


Полігони

Полігони будуються з замкнених рядків LineStrings. Полігон містить всю замкнену площу та її межу, тоді як замкнута рядок лінії на межі називається зовнішнім кільцем

У наступному прикладі ми обчислюємо площу кожної будівлі за допомогою функції ST_Area (geom) у PostGIS.

Ось деякі основні моменти функцій Polygon у PostGIS:

    , ST_Polygon, ST_PolygonFromText → Для створення геометрії багатокутника. → Повертає рядок рядка, що представляє зовнішнє кільце геометрії POLYGON. Поверніть NULL, якщо геометрія не є багатокутником. Не працюватиме з MULTIPOLYGON-Якщо геометрія є багатокутником або багатокутником, повертається кількість кілець. - Повертає true, якщо геометрія A повністю знаходиться всередині геометрії B.

Ваші перші кроки з типом даних географії

Географічні додатки є скрізь: системи GPS та супутникової навігації, карти, програми отримання таксі, портали нерухомості тощо. За кожним із них розташовується просторова база даних, що зберігає географічні дані та підтримує просторові запити. У цій статті ми познайомимо вас з PostGIS, головним менеджером просторових баз даних з відкритим вихідним кодом.

PostGIS - це просторове розширення бази даних для реляційної бази даних PostgreSQL. Він додає підтримку географічних об’єктів, дозволяючи виконувати запити про місцезнаходження в SQL.

PostGIS додає до PostgreSQL два основних типи даних: географію та геометрію. Обидва дозволяють зберігати точки в таблиці, а також інші складніші форми, такі як лінії (лінія визначається двома точками), багатоточкові лінії (визначені N точками), багатокутники (визначені замкнутою багатоточковою лінією) та точки з певною висотою (визначається третьою координатою). Цей подовжувач також пропонує набір просторових функцій для розрахунку відстані, обчислення площі, перетину та включення, серед багатьох інших. Усі ці нові типи даних та функції можна використовувати в поєднанні зі звичайними реляційними даними в SQL, збільшуючи потужність запитів.

У цій статті ми розглянемо перші кроки використання PostGIS, вивчивши такі функції, як тип даних географії та деякі просторові функції, такі як відстань, площа та перетин. Для ілюстрації ми додамо кілька прикладів SQL.

Почнемо з пояснення основного будівельного блоку у просторовій базі даних: пари координат, що представляють одну точку на земній поверхні.

ГЕОГРАФІЯ Тип даних

Першою особливістю PostGIS, яку ми дізнаємося, є тип даних географії. Ми почнемо з його використання для представлення точки, визначеної двома координатами: широтою та довготою. Як приклад у цій статті ми будемо використовувати таблицю художній твір , де зберігається список найважливіших у світі творів мистецтва та де вони експонуються. Ця таблиця матиме таку схему:

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

Щоб заповнити стовпець where_is, нам потрібно викликати PostGIS ST_POINT функція. Це повертає a географії data point, як ми бачимо в наступному операторі SQL:

Після цього оновлення у нас перший географії точки даних у базі даних. Якщо ми спробуємо побачити його значення за допомогою SELECT, ми побачимо лише шістнадцяткові значення:

Тут ми маємо уточнити два пункти. По -перше, коли ми використовували географії типу даних, ми не вказали категорію. PostGIS припустив, що ми маємо на увазі категорію за замовчуванням, яка є POINT. Однак - і як ми вже згадували - ми можемо зберігати багато різних категорій за допомогою географії тип даних.

Другий пункт стосується значень у стовпці where_is. Ці значення зберігаються всередині PostGIS у форматі WKB (добре відомий двійковий файл), і ми не можемо легко зрозуміти, що вони представляють у цьому форматі. Нам потрібне програмне забезпечення для інтерпретації їх значення, наприклад, якщо ми використовуємо QGIS, ми можемо показати чотири географічні точки на карті. Подивіться результати на наведеному нижче скріншоті:

Добре відомий текст (WKT)

Попередній параграф відкриває двері до центральної концепції стандарту просторової бази даних. Кожен просторовий елемент (наприклад, точка, лінія чи багатокутник) можна виразити або визначити за допомогою двох різних форматів:

Давайте тут поговоримо про формат добре відомого тексту. Як ми бачили у попередньому прикладі, кожен географічний елемент у PostGIS представлений внутрішньо у вигляді рядка шістнадцяткових цифр у форматі WKB. Очевидно, що людям це дійсно важко зрозуміти. WKT-це більш чіткий, зручний для людини спосіб представити будь-який геометрія/географія елемент. Деякі приклади WKT:

Крім того, функція під назвою ST_AsText () можна отримати WKT будь -якого геометрія або географії просторовий елемент, як ми бачимо в наступному прикладі:

Існує також функція під назвою ST_GeoFromText () . Це зворотна функція для формату WKT, вона повертає відповідну географію.

Найпростіша просторова функція: відстань

У цій статті ми розглянемо лише типи географічних даних, які ґрунтуються на широті та довготі. Однак PostGIS дозволяє нам представляти та зберігати інші просторові елементи (наприклад, географічну точку на основі широти, довготи та висоти) та геометрія тип даних (що дозволяє простим способом представити просторові точки двох або трьох координат).

Основа для PostGIS геометрія тип даних - площина. Найкоротший шлях між двома точками на площині - це пряма лінія. Це означає, що обчислення геометрії (площі, відстані, довжини, перетини тощо) можна проводити за допомогою декартової математики та векторів прямої лінії.

З іншого боку, основа для PostGIS географії тип даних - сфера. Найкоротший шлях між двома точками на сфері - це велика дуга кола. Це означає, що розрахунки за географією (площі, відстані, довжини, перетини тощо) повинні базуватися на сфері, використовуючи більш складну математику. Більш того, Земля не є ідеальною кулею, тому розрахунки повинні розглядати світ як сфероїдальну форму. Занадто складно! Продовжимо використовувати PostGIS.

Повернемося до відстань функція для географічних точок. Наступний SQL -запит обчислює відстань між нашими чотирма шедеврами, показуючи всі можливі комбінації.

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

Ми не просто зберігаємо точки: лінії, полігони, багатоточки

географії тип даних має модифікатор, який визначає, який елемент ми будемо зберігати у стовпці. У нашому першому прикладі ми не вказали модифікатор, тому за замовчуванням ми зберігали POINT. Однак ми можемо зберігати ЛІНІЇ, ПОЛІГОНИ та навіть складні просторові форми, такі як MULTIPOINTS (масив точок). Наприклад, якщо ми хочемо зберегти багатокутник, щоб представляти периметр музею, ми можемо створити таку таблицю:

Для створення значення багатокутника ми будемо використовувати функцію PostGIS ST_GeoFromText. Як ми вже згадували, це повертає значення геометрії, коли йому надано представлення WKT. WKT для багатокутника має такий вигляд:

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

Примітка: Периметри не є суворо реальними, але вони включають територію музею.

Обчислення площі складних фігур

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

Цікавим моментом тут є простота. Подумайте, наскільки складним може бути обчислення площі неправильної форми. Тут ми робимо це за допомогою приємного короткого запиту:

Примітка: Периметри музею є приблизними, тому значення площ наближені.

Як ознайомитися з умовами приєднання

Якщо ми розглянемо схему таблиць музею та ілюстрації, немає сторонніх ключів, які дозволяють об’єднатись. Як ми можемо пов’язати витвір мистецтва з музеєм, де він експонується? Чи є якісь просторові умови для приєднання до обох таблиць? Давайте поглянемо на наступне об’єднання SQL:

Спробуйте самі: вправа для читача

Просторові бази даних мають більш складні елементи, функції та алгоритми, але основні поняття, які ми обговорювали, присутні за кожним просторовим елементом. Один з елементів, які тут не охоплені, - це третій вимір точки. Пам’ятайте, що просторові бази даних також дозволяють представити висоту даної точки. Уявімо собі музей, де два твори мистецтва знаходяться в однакових широтних і поздовжніх координатах, але на різних поверхах.


є фундаментальний для ефективного

Нормалізація - це формалізація процесу проектування створення бази даних, що відповідає концепції нормальної форми.

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

Існує кілька рівнів нормальної форми, і кожен рівень вимагає задоволення попереднього рівня.

Процес нормалізації ґрунтується на зборі вичерпного списку всіх елементів даних, які мають зберігатися в базі даних, та початку проектування з кількома таблицями & quotsuperset & quot.


Хороші читання

Ви можете отримати дані з відділу у файлі шейпів або іншому форматі геоданих. Якщо ви хочете використовувати PostGIS, вам потрібно буде отримати ці дані у нашій базі даних Postgres.

Ви можете використовувати QGIS за допомогою вищезгаданого менеджера баз даних. Натиснувши кнопку «Імпортувати шар/файл», відкриється вікно, у якому вам буде запропоновано вибрати файл та кілька варіантів, пов’язаних із ним.

  • У спадному меню виберіть схему, на яку ви її надсилаєте (ви повинні мати дозволи на запис у цю схему)
  • Введіть, якою має бути назва таблиці
  • Виберіть опцію «Стовпець Геометрія» та встановіть бажане ім’я. Для нас у Місті ми називаємо стовпці геометрії geom + SRID для системи координат, в якій знаходяться дані: geom2249.
  • Переконайтеся, що поля “Source SRID” та “Target SRID” точні.
  • Виберіть «Перетворити назви полів у нижній регістр». Це лише для того, щоб відповідати нашій політиці управління у місті, щоб усі назви полів використовували малі літери.
  • Виберіть «Створити просторовий покажчик». Докладніше див. У розділі Просторове індексування.
  • Після натискання кнопки «Гаразд», можливо, вам доведеться оновити схему для появи нової таблиці.

Використання існуючих таблиць PostGIS

ArcSDE може успішно використовувати таблиці, що містять стовпці геометрії PostGIS, створені зовні іншими програмами або з використанням SQL (також називається сторонньою таблицею), якщо таблиці відповідають таким передумовам:

    Кожна таблиця повинна належати користувачу, який її реєструє.

Реєстрація сторонніх таблиць, що містять стовпці геометрії PostGIS

Команда адміністрування ArcSDE sdelayer –o register реєструє таблицю як клас об’єктів. Це означає, що до таблиці додано запис у системних таблицях sde_layers, sde_table_registry та sde_geometry_columns. Записи також додаються до системної таблиці sde_column_registry для кожного стовпця таблиці.

Нижче наведено приклад реєстрації таблиці, властивостей, що містять геометричні точки (–e p) у просторовому стовпці, фігуру. У таблиці є цілочисельний стовпець, fid, який буде використовуватися як стовпець унікального ідентифікатора функції (–C fid, SDE), що підтримується SDE.

Як зазначено вище, sdelayer лише додає таблицю до системних таблиць ArcSDE. Щоб мати змогу користуватися функціями бази геоданних ArcGIS Desktop, такими як топологія, створення версій та мережі, необхідно також зареєструвати таблицю в базі геоданих. Докладніше про реєстрацію таблиць див. У розділі Реєстрація таблиць, які будуть використовуватися ArcGIS Desktop.

ПРИМІТКА. Не можна перейменувати просторові таблиці, збережені за допомогою типу геометрії PostGIS. Це пов'язано з тим, що немає функції PostGIS для оновлення назви таблиці в таблиці public.geometry_columns.


Подивіться відео: Tutorial 42 - Creating Functions in PostgreSQL (Жовтень 2021).