Для объектно-реляционной СУБД PostgreSQL (Postgres) существует множество расширений. Их также называют экстеншенами (от англ. extension - расширение). Некоторые из них помогают в оптимизации работы баз данных, другие нужны для эффективной работы с определенными типами данных. Именно об одном из последних мы поговорим в этой статье.
Что такое PostGIS?
PostGIS — это расширение с открытым исходным кодом для PostgreSQL. Он привносит в PostgreSQL три вещи, которые имеют решающее значение для работы с пространственными данными:
- Поддержка типов пространственных данных (точки, линии, полигоны, растры).
- Пространственные функции (подробнее о них ниже).
- Пространственное индексирование, чтобы ваши пространственные запросы выполнялись быстро.
Пространственные базы данных (spatial databases) хранят пространственные объекты и управляют ими так же, как и любыми другими объектами в базе данных. Таким образом, в обычной базе данных вы храните данные разных типов (числовые, текстовые, временные метки) и при необходимости вы можете запрашивать (извлекать) их, чтобы отвечать на вопросы с помощью ваших данных. Вопросы могут быть о том, «сколько людей зашло на ваш сайт» или «сколько транзакций было совершено в интернет-магазине». Вместо этого пространственные функции могут отвечать на такие вопросы, как «насколько близко находится ближайший магазин», «находится ли эта точка внутри этой области» или «каков размер этой страны».
Установка PostgreSQL на Ubuntu
Заводим сервер на Timeweb Cloud и устанавливаем PostgreSQL.
Для начала обновим программные пакеты. В системах Linux это можно сделать, воспользовавшись командой:
Затем установим Postgres, а именно пакет postgresql-contrib, содержащий дополнительные модули и функции дистрибутива PostgreSQL:
Запуск PostgreSQL
Когда установка заверена, следует проверить статус сервера базы данных:
Если статус down, сервис нужно запустить командой:
Или:
Выбор команды зависит от используемой системы инициализации: sysvinit или systemd. Проверить это можно при помощи команды:
В случае использования Ubuntu внутри Windows это будет первый вариант.
Роли и базы данных
Когда вы устанавливаете PostgreSQL, по умолчанию создается пользователь-администратор postgres. От его имени мы в первый раз заходим на сервер PostgreSQL.
Зададим пароль пользователю postgres:
Вы можете взаимодействовать с ядром базы данных несколькими способами. К примеру, инструмент pgAdmin предоставляет графический клиент для управления базами данных. Но мы будем использовать psql — клиентский инструмент командной строки. Вызовем его от имени пользователя postgres, чтобы начать интерактивный сеанс.
Или:
Во втором случае необходимо будет ввести пароль, заданный шагом ранее.
Наряду с пользователем-администратором postgres, PostgreSQL также создает базу данных по умолчанию с именем «postgres» и автоматически подключает вас к ней при первом запуске psql.
Проверьте детали вашего соединения после первого запуска psql, набрав \conninfo в интерпретаторе.
Вы также можете посмотреть список всех баз данных, доступных на сервере, используя команду \l.
А чтобы увидеть список всех пользователей с их привилегиями, — используйте команду \du.
Создание базы данных
Теперь, когда мы подключены к серверу базы данных через инструмент командной строки psql с полными правами доступа, пришло время создать новую базу данных.
И подключиться к ней:
DBaaS
оптимизируйте процессы DevOps и CI/CD.
Удаленный доступ
Вероятно, вы захотите подключиться к базе данных через удаленный сервер.
Сразу после установки сервер PostgreSQL доступен только локально, через петлевой IP-адрес вашего компьютера. Но вы можете разрешить удалённый доступ, изменив соответсвующий параметр в файле конфигурации PostgreSQL.
Получить доступ к файлу конфигурации postgresql.conf PostgreSQL версии 13 (ваша версия указывается при запуске psql) можно с помощью текстового редактора nano:
Раскомментируйте и отредактируйте атрибут listen_addresses, чтобы начать прослушивание всех доступных IP-адресов.
Теперь внесём изменения в файл конфигурации политики доступа PostgreSQL:
Добавьте новую политику подключения (шаблон [CONNECTION_TYPE][DATABASE][USER] [ADDRESS][METHOD]) внизу файла.
Мы разрешаем подключения по протоколу TCP/IP (host) ко всем базам данных (all) для всех пользователей (all) с любым адресом IPv4 (0.0.0.0/0) с использованием зашифрованного пароля MD5 для аутентификации (md5).
После изменения настроек перезапускаем службу postgresql:
Установка PostGIS
В Ubuntu 20.04 расширение присутствует в репозиториях и может быть установлено с помощью команды:
Функции PostGIS для каждой базы мы будем настраивать отдельно. Начнём с только что созданной базы test, а именно создадим расширения:
И убедимся, что оно создано:
В командной строке увидим примерно следующее:
Загрузка пространственных данных
Давайте загрузим некоторые данные PostGIS в нашу базу данных. Будем использовать набор данных из PostGIS tutorial.
Чтобы загрузить пространственные данные в базу, воспользуемся утилитой shp2pgsql. Она конвертирует шейп-файлы в SQL.
Что представляют собой шейп-файлы? Обозначение шейп-файл, как правило, имеет отношение к группе файлов, имеющих расширения .shp, .shx, .dbf и др., объединенных общим названием (например, nyc_subway_stations). Фактически же шейп-файлом является именно файл с расширением .shp. Тем не менее файл данного типа не является самодостаточным и не может распространяться без необходимых вспомогательных файлов.
Обязательные файлы:
- .shp — формат формы; сама геометрия объекта
- .shx — формат индекса формы; позиционный индекс геометрии объекта
- .dbf — формат атрибута; атрибуты (негеометрическая информация) географических объектов
Необязательные файлы включают в себя:
- .prj — формат проекции; система координат и информация о проекции.
Для загрузки используем следующую команду:
Где:
- Флаг
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.
Посмотреть столбцы и их типы в созданных таблицах можно следующим образом:
К примеру мы можем видеть, что станции метро имеют тип geometry(Point,26918), то есть являются точками, а районы — geometry(MultiPolygon,26918), и являются полигонами.
Пространственные запросы
Если выполнить SELECT * FROM для любой из наших таблиц, то в результате мы увидим, что столбец geom представляет собой непрозрачное кодирование геометрических данных. Вы, возможно, ожидали увидеть точки данных долготы/широты. Именно здесь начинают действовать функции PostGIS. К примеру, функция ST_AsText(geom) превращает данные столбца в точки геометрии.
Получим с помощью данной функции координаты долготы и широты станций метро:
В результате увидим:
Другая полезная функция — ST_Length(geom) вычисляет длину:
Получим с помощью нее самые короткие улицы города:
Или, например, найдем 10 самых больших районов Нью-Йорка, используя функцию ST_Area(geom), вычисляющую площадь:
Больше функций можно найти в документации или упомянутом выше туториале PostGIS.
Разверните PostgreSQL в облаке за пару кликов
447 ₽/мес
711 ₽/мес
Геометрия и география
Обсудим еще одну важную деталь — геометрия и география. В PostgreSQL и PostGIS есть важное различие между геометрией и географией: геометрия является декартовой, а география добавляет дополнительные вычисления кривизны земли. В общем, если вы имеете дело с небольшими областями, такими как город или здание, вам не нужно добавлять дополнительные вычислительные ресурсы для географии, но если вы пытаетесь рассчитать что-то более крупное, например маршруты авиакомпаний, — это необходимо.
Вот как вы могли бы создать таблицу географии (т.е. преобразовать данные функцией ST_Transform) из одной из наших существующих таблиц:
Таким образом, мы создали базу управления данными в PostgreSQL PostGIS, загрузили разнообразные пространственные данные, описывающие Нью-Йорк, и попробовали узнать некоторые факты о городе, используя функции для работы с геометрией.
Надеемся, эта статья была вам полезна!
