Управление данными в PostgreSQL PostGIS

Для объектно-реляционной СУБД PostgreSQL (Postgres) существует множество расширений. Их также называют экстеншенами (от англ. extension - расширение). Некоторые из них помогают в оптимизации работы баз данных, другие нужны для эффективной работы с определенными типами данных. Именно об одном из последних мы поговорим в этой статье. 

Что такое PostGIS?

PostGIS — это расширение с открытым исходным кодом для PostgreSQL. Он привносит в PostgreSQL три вещи, которые имеют решающее значение для работы с пространственными данными:

1. Поддержка типов пространственных данных (точки, линии, полигоны, растры).
2. Пространственные функции (подробнее о них ниже).
3. Пространственное индексирование, чтобы ваши пространственные запросы выполнялись быстро.

Пространственные базы данных (spatial databases) хранят пространственные объекты и управляют ими так же, как и любыми другими объектами в базе данных. Таким образом, в обычной базе данных вы храните данные разных типов (числовые, текстовые, временные метки) и при необходимости вы можете запрашивать (извлекать) их, чтобы отвечать на вопросы с помощью ваших данных. Вопросы могут быть о том, «сколько людей зашло на ваш сайт» или «сколько транзакций было совершено в интернет-магазине». Вместо этого пространственные функции могут отвечать на такие вопросы, как «насколько близко находится ближайший магазин», «находится ли эта точка внутри этой области» или «каков размер этой страны». 

Установка PostgreSQL на Ubuntu

Заводим сервер на Timeweb Cloud и устанавливаем PostgreSQL.

Для начала обновим программные пакеты. В системах Linux это можно сделать, воспользовавшись командой:

sudo apt update

Затем установим Postgres, а именно пакет postgresql-contrib, содержащий дополнительные модули и функции дистрибутива PostgreSQL:

sudo apt install postgresql postgresql-contrib

Запуск PostgreSQL

Когда установка заверена, следует проверить статус сервера базы данных:

service postgresql status

Если статус down, сервис нужно запустить командой:

sudo service postgresql start

Или:

sudo systemctl status postgresql.service
sudo systemctl start postgresql.service

Выбор команды зависит от используемой системы инициализации: sysvinit или systemd. Проверить это можно при помощи команды:

ps -p 1 -o comm=

В случае использования Ubuntu внутри Windows это будет первый вариант.

Роли и базы данных

Когда вы устанавливаете PostgreSQL, по умолчанию создается пользователь-администратор postgres. От его имени мы в первый раз заходим на сервер PostgreSQL.

Зададим пароль пользователю postgres:

sudo passwd postgres 

Вы можете взаимодействовать с ядром базы данных несколькими способами. К примеру, инструмент pgAdmin предоставляет графический клиент для управления базами данных. Но мы будем использовать psql — клиентский инструмент командной строки. Вызовем его от имени пользователя postgres, чтобы начать интерактивный сеанс.

sudo -u postgres psql

Или:

su postgres -c psql

Во втором случае необходимо будет ввести пароль, заданный шагом ранее.

Наряду с пользователем-администратором postgres, PostgreSQL также создает базу данных по умолчанию с именем «postgres» и автоматически подключает вас к ней при первом запуске psql.

Проверьте детали вашего соединения после первого запуска psql, набрав \conninfo в интерпретаторе.

postgres=# \conninfo

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5433".

Вы также можете посмотреть список всех баз данных, доступных на сервере, используя команду \l.

А чтобы увидеть список всех пользователей с их привилегиями, — используйте команду \du. 

Создание базы данных

Теперь, когда мы подключены к серверу базы данных через инструмент командной строки psql с полными правами доступа, пришло время создать новую базу данных.

CREATE DATABASE test;

И подключиться к ней:

\c test

dbaas

Удаленный доступ

Вероятно, вы захотите подключиться к базе данных через удаленный сервер.

Сразу после установки сервер PostgreSQL доступен только локально, через петлевой IP-адрес вашего компьютера. Но вы можете разрешить удалённый доступ, изменив соответсвующий параметр в файле конфигурации PostgreSQL.

Получить доступ к файлу конфигурации postgresql.conf PostgreSQL версии 13 (ваша версия указывается при запуске psql) можно с помощью текстового редактора nano:

sudo nano /etc/postgresql/13/main/postgresql.conf

Раскомментируйте и отредактируйте атрибут listen_addresses, чтобы начать прослушивание всех доступных IP-адресов.

listen_addresses = '*'

Теперь внесём изменения в файл конфигурации политики доступа PostgreSQL:

sudo nano /etc/postgresql/13/main/pg_hba.conf

Добавьте новую политику подключения (шаблон [CONNECTION_TYPE][DATABASE][USER] [ADDRESS][METHOD]) внизу файла.

host all all 0.0.0.0/0 md5

Мы разрешаем подключения по протоколу TCP/IP (host) ко всем базам данных (all) для всех пользователей (all) с любым адресом IPv4 (0.0.0.0/0) с использованием зашифрованного пароля MD5 для аутентификации (md5).

После изменения настроек перезапускаем службу postgresql:

sudo service postgresql restart

Установка PostGIS

В Ubuntu 20.04 расширение присутствует в репозиториях и может быть установлено с помощью команды:

sudo apt install postgis

Функции PostGIS для каждой базы мы будем настраивать отдельно. Начнём с только что созданной базы test, а именно создадим расширения:

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

И убедимся, что оно создано:

SELECT PostGIS_version();

В командной строке увидим примерно следующее:

            postgis_version
---------------------------------------
 3.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Загрузка пространственных данных

Давайте загрузим некоторые данные PostGIS в нашу базу данных. Будем использовать набор данных из PostGIS tutorial.

mkdir nyc
cd nyc
wget http://s3.cleverelephant.ca/postgis-workshop-2020.zip
unzip postgis-workshop-2020.zip

Чтобы загрузить пространственные данные в базу, воспользуемся утилитой shp2pgsql. Она конвертирует шейп-файлы в SQL.

Что представляют собой шейп-файлы? Обозначение шейп-файл, как правило, имеет отношение к группе файлов, имеющих расширения .shp, .shx, .dbf и др., объединенных общим названием (например, nyc_subway_stations). Фактически же шейп-файлом является именно файл с расширением .shp. Тем не менее файл данного типа не является самодостаточным и не может распространяться без необходимых вспомогательных файлов. 

Обязательные файлы:

• .shp — формат формы; сама геометрия объекта
• .shx — формат индекса формы; позиционный индекс геометрии объекта
• .dbf — формат атрибута; атрибуты (негеометрическая информация) географических объектов

Необязательные файлы включают в себя:

• .prj — формат проекции; система координат и информация о проекции.

Для загрузки используем следующую команду:

shp2pgsql -I -s 26918 nyc/postgis-workshop/data/nyc_subway_stations.shp nyc_subway_stations | sudo -u postgres psql dbname=test

Где:

• Флаг I указывает программе создать пространственный индекс для таблицы после завершения загрузки.
• Флаг s передает программе идентификатор пространственной ссылки (SRID - spatial reference identifier) данных. Он определяет все параметры географической системы координат и проекции наших данных. Удобство SRID заключается в том, что он упаковывает всю информацию о картографической проекции (которая может быть довольно сложной) в одно число. Для используемых данных SRID — 26918.
• nyc/postgis-workshop/data/nyc_subway_stations.shp указывает расположение шейп-файла для чтения
• nyc_subway_stations — имя для целевой таблицы
• | sudo -u postgres psql dbname=test 

Оператор | принимает сгенерированный утилитой поток sql и использует его в качестве входных данных для терминала psql. Подключаемся через psql к целевой базе данных.

Таким же образом создадим в базе остальные таблицы nyc_census_blocks, nyc_homicides, nyc_neighborhoods, nyc_streets.

Посмотреть столбцы и их типы в созданных таблицах можно следующим образом: 

\d <имя_таблицы>

К примеру мы можем видеть, что станции метро имеют тип geometry(Point,26918), то есть являются точками, а районы — geometry(MultiPolygon,26918), и являются полигонами.

Пространственные запросы

Если выполнить SELECT * FROM для любой из наших таблиц, то в результате мы увидим, что столбец geom представляет собой непрозрачное кодирование геометрических данных. Вы, возможно, ожидали увидеть точки данных долготы/широты. Именно здесь начинают действовать функции PostGIS. К примеру, функция ST_AsText(geom) превращает данные столбца в точки геометрии.

Получим с помощью данной функции координаты долготы и широты станций метро:

SELECT name, ST_AsText(geom)
FROM nyc_subway_stations;

В результате увидим:

           name           |                st_astext
--------------------------+------------------------------------------
Allerton Ave              | POINT(595454.531087843 4524436.50062031)                                                    
Baychester Ave            | POINT(597856.15605485 4525936.78154763)                                                     
Bedford Park Blvd         | POINT(593712.996886201 4525155.29643926)                                                    
Bedford Park Blvd         | POINT(593550.086762622 4525318.36272269)                                                    
Bronx Park East           | POINT(595390.744161286 4522610.04524754)                                                    
Brook Ave                 | POINT(591158.462734484 4517957.5457551)                                                     
Buhre Ave                 | POINT(598436.457893145 4522431.7978544)                                                     
Burke Ave                 | POINT(595458.276100251 4525102.84081136)                                                    
Burnside Ave              | POINT(592095.242680619 4523101.90192447)                                                    
Castle Hill Ave           | POINT(596821.240860527 4520985.43791063)                                                    
Cypress Ave               | POINT(591563.743613455 4517738.30592931)                                                    
Dyre Ave                  | POINT(598514.310720232 4527104.05810844)                                                    
E 143rd St                | POINT(592119.080134076 4518038.84260289)

Другая полезная функция — ST_Length(geom) вычисляет длину:

Получим с помощью нее самые короткие улицы города:

SELECT name, ST_Length(geom) as street_length
FROM nyc_streets
ORDER BY street_length ASC
LIMIT 5;

    name     |   street_length
-------------+--------------------
 W 66 St     | 0.9209664156687739
 Linden Blvd | 1.1366550633216888
 Village Ct  | 3.0275240748000387
 Bay 38 St   |   3.30965835413287
 Wilder Ave  |   3.62538546315785

Или, например, найдем 10 самых больших районов Нью-Йорка, используя функцию ST_Area(geom), вычисляющую площадь:

SELECT boroname, name, ST_Area(geom) as neighborhood_area
FROM nyc_neighborhoods
ORDER BY neighborhood_area DESC
LIMIT 10;

   boroname    |           name            | neighborhood_area
---------------+---------------------------+--------------------
 Queens        | The Rockaways             |  27956418.73731064
 Brooklyn      | Gravesend-Sheepshead Bay  |  19920099.58695366
 Brooklyn      | Canarsie                  | 17702913.620151885
 Queens        | Douglastown-Little Neck   | 17532480.147214886
 Staten Island | Bloomfield-Chelsea-Travis | 14118408.808009561
 Queens        | Jamaica                   | 13848478.727482272
 Staten Island | Richmondtown              | 12904057.943439055
 Brooklyn      | Mapleton-Flatlands        | 12197264.514111498
 Brooklyn      | East Brooklyn             |  11090294.27801337
 The Bronx     | Eastchester               | 10786177.928620113

Больше функций можно найти в документации или упомянутом выше туториале PostGIS.

Разверните PostgreSQL в облаке за пару кликов

Геометрия и география

Обсудим еще одну важную деталь — геометрия и география. В PostgreSQL и PostGIS есть важное различие между геометрией и географией: геометрия является декартовой, а география добавляет дополнительные вычисления кривизны земли. В общем, если вы имеете дело с небольшими областями, такими как город или здание, вам не нужно добавлять дополнительные вычислительные ресурсы для географии, но если вы пытаетесь рассчитать что-то более крупное, например маршруты авиакомпаний, — это необходимо.

Вот как вы могли бы создать таблицу географии (т.е. преобразовать данные функцией ST_Transform) из одной из наших существующих таблиц:

CREATE TABLE nyc_subway_stations_geog AS
SELECT
  ST_Transform(geom,4326)::geography AS geog,
  name,
  routes
FROM nyc_subway_stations;

Таким образом, мы создали базу управления данными в PostgreSQL PostGIS, загрузили разнообразные пространственные данные, описывающие Нью-Йорк, и попробовали узнать некоторые факты о городе, используя функции для работы с геометрией.

Надеемся, эта статья была вам полезна!