PostgreSQL — одна из самых популярных реляционных БД, однако, для работы с геоданными и географическими объектами, её стандартные возможности могут оказаться недостаточными. Тогда приходит на помощь расширение PostGIS.
В этой статье мы познакомимся с ним поближе и узнаем, как установить и использовать расширение.
Что такое PostGIS?
PostGIS — это свободно распространяемое дополнение к СУБД PostgreSQ для работы с географическими объектами. Оно предоставляет мощный инструментарий для работы с пространственными данными, позволяя хранить, обрабатывать и анализировать географическую информацию.
Одной из ключевых особенностей PostGIS является возможность работы с различными форматами географических данных, такими как шейп-файлы, GeoJSON, KML и другие. Это позволяет импортировать и экспортировать данные между различными геоинформационными системами и форматами, обеспечивая максимальную гибкость и удобство в работе.
Расширение предоставляет мощные функции для работы с геометрическими объектами. Например, есть возможность делать пространственные запросы, такие как определение пересечений, поиск ближайших объектов, расчет площади и периметра и т.д. Также имеются функции для трансформации координатных систем, агрегации пространственных данных.
PostGIS обладает высокой производительностью и масштабируемостью, что позволяет эффективно работать с большими объёмами данных. Он активно используется во множестве областей, включая геологию, экологию, городское планирование, транспортное моделирование и другие, где важно анализировать пространственную информацию и принимать обоснованные решения на основе географических данных. Например, можно определить расстояние, площадь или найти ближайший объект к заданной точке.
Установка PostgreSQL на Ubuntu
Шаг 1: обновляем систему.
Перед началом установки рекомендуется обновить систему Ubuntu до последней версии. sudo apt-get update
Шаг 2: устанавливаем PostgreSQL.
После успешного обновления системы можно приступить к инсталляции.
sudo apt install postgresql postgresql-contrib
Шаг 3: проверяем.
После завершения установки можно проверить, что PostgreSQL успешно работает, уточнить статус сервера.
service postgresql status
В ответ вы должны получить информацию о версии PostgreSQL.
Шаг 4: запуск PostgreSQL.
Если статус сервера обозначен down, запускаем его.
При использовании Ubuntu внутри Windows.
sudo service postgresql start
В других случаях.
sudo systemctl status postgresql.service
sudo systemctl start postgresql.service
Проверяется система инициализации так.
ps -p 1 -o comm=
Шаг 5: установка ролей и базы данных
По стандарту в процессе инсталляции создается пользователь postgres с паролем, который вы указали во время процесса.
Задаём пароль.
sudo passwd postgres
Чтобы запустить графический клиент, введите команду pgAdmin.
На нашем примере мы рассмотрим работу с командной строкой.
sudo -u postgres psql
Расширение создаст БД, которая также называется postgres, подключение к ней будет автоматически выполнено, как только вы запустите psql.
Уточним информацию о соединении.
postgres=# \conninfo
Система выдаст небольшой отчёт. Дополнительно можно перечень доступных БД на сервере.
\l
Посмотрим привилегированных пользователей.
\du
Шаг 6: Создаём БД.
CREATE DATABASE test;
Теперь подключимся к ней.
\c test
Шаг 7: удалённый доступ.
По умолчанию PostgreSQL настроен на локальный доступ, что означает, что вы сможете подключиться к БД исключительно с этого же сервера. Однако существует возможность выполнить подключение через удалённый сервер, для этого нужно изменить конфигурационный файл. Сделаем это в редакторе nano – вместо 13 укажите актуальную версию.
sudo nano /etc/postgresql/13/main/postgresql.conf
Теперь нужно раскомментировать и отредактировать атрибут
listen_addresses. 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
Рассмотрим указанные значения.
- Host – это подключение по протоколу TCP/IP.
- All – ко всем БД.
- Следующее all – для всех пользователей.
- 0.0.0.0/0 означает любой адрес IPv4.
- md5 – зашифрованный пароль MD5.
Мы разрешаем подключения по протоколу TCP/IP (host) ко всем базам данных (all) для всех пользователей (all) с любым адресом IPv4 (0.0.0.0/0) с использованием зашифрованного пароля MD5 для аутентификации (md5).
Перезапускаем.
sudo service postgresql restart
Установка PostGIS
Расширение уже присутствует в репозиториях, ставим его.
sudo apt install postgis
Функции для всех БД настраиваются отдельно.
Попробуем сделать расширение.
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
Выполним проверку.
SELECT PostGIS_version();
В ответ система выдаст небольшой отчёт.
Пространственные запросы
Одним из ключевых шагов в использовании PostGIS является загрузка пространственных данных в базу данных PostgreSQL. Этот процесс позволяет передать геоданные из внешних источников в PostGIS и обеспечить их доступность и управление через SQL-запросы и геопространственные функции.
В нашем примере используется набор данных из PostGIS tutorial.
mkdir nyc
cd nyc
wget http://s3.cleverelephant.ca/postgis-workshop-2020.zip
unzip postgis-workshop-2020.zip Наиболее простой и распространённый способ загрузки пространственных данных - использование командной строки. В нашем случае это shp2pgsql для конвертации шейп-файлов.
Шейп-файлы – это формат, используемый в геоинформационной системе PostGIS для хранения пространственных данных. Сюда входит набор файлов, которые содержат информацию о геометрии объектов, а также соответствующие атрибуты. Они состоят из нескольких файлов с расширениями .shp, .shx, .dbf и других. Файлы .shp содержат геометрическую информацию, .shx – индексы, а .dbf – атрибутивные данные.
Преимуществом шейп-файлов в PostGIS считается их универсальность и широкая поддержка другими геоинформационными системами. Они позволяют хранить и обрабатывать разнообразные географические объекты, такие как точки, линии, полигоны и даже многогранники. Кроме того, шейп-файлы в PostGIS поддерживают множество атрибутов для каждого объекта, что делает их гибкими для хранения и анализа пространственных данных.
Загрузим их.
shp2pgsql -I -s 26918 nyc/postgis-workshop/data/nyc_subway_stations.shp nyc_subway_stations | sudo -u postgres psql dbname=test
В данном случае Флаг I нужен для создания пространственного индекса для таблицы. S направляет ID пространственной ссылки данных SRID. Затем указывается путь файла и название таблицы. Оператор | получает и применяет поток. А через psql выполняется подключение к БД.
По тому же принципу создаются остальные таблицы. Затем можем их посмотреть.
\d <имя_таблицы>
Пример работы с пространственными запросами
Допустим, нам нужно узнать координаты станций метрополитена. Для этого нам потребуется функция ST_AsText(geom), которая представит столбцы таблицы в виде геометрических точек.
SELECT name, ST_AsText(geom)
FROM nyc_subway_stations;
Отчёт будет выглядеть так.
Рассчитывает длину функция ST_Length(geom). Например, нам нужно узнать самые короткие улицы в населённом пункте.
SELECT name, ST_Length(geom) as street_length
FROM nyc_streets
ORDER BY street_length ASC
LIMIT 5;
Система выдаст такие данные.
Функция ST_Area(geom) рассчитывает площадь. Например, мы можем узнать наиболее крупные районы города.
SELECT boroname, name, ST_Area(geom) as neighborhood_area
FROM nyc_neighborhoods
ORDER BY neighborhood_area DESC
LIMIT 10;
Различие между геометрией и географией
PostGIS поддерживает два типа данных – геометрию и географию.
Геометрия – это тип данных для работы с объектами в плоскости (двухмерное пространство). Он основан на системе координат X, Y, где X представляет собой долготу, а Y – широту. Главное преимущество геометрии заключается в том, что она обеспечивает быструю обработку и анализ объектов, таких как точки, линии и полигоны. Можно выполнять операции пересечения, объединения, вычитания и т.д., а также находить расстояния между объектами. Геометрия идеально подходит для картографических приложений и задач, связанных с геометрическими расчётами.
География – это тип данных, который используется для работы с объектами на поверхности Земли (трехмерное пространство), используя географические координаты – широту и долготу. География учитывает искривление Земли и позволяет выполнять более точные географические расчёты с учётом примерности масштабирования. Кроме того, позволяет выполнять операции над объектами, такие как поиск ближайших точек, вычисление расстояния по кратчайшему пути, определение площади и многое другое.
Несмотря на отличия в системах координат и пространствах, геометрия и география в PostGIS тесно взаимосвязаны. Это расширение предоставляет широкий выбор функций для обоих типов данных, а также позволяет конвертировать геометрию в географию и наоборот. Это особенно полезно, когда необходимо совместить данные разных пространств в одном приложении или анализировать информацию, полученную из различных источников.
И здесь стоит обратить внимание на ещё одну функцию – ST_Transform. С её помощью можно преобразовать данные из уже существующей таблицы для создания таблицы географии.
CREATE TABLE nyc_subway_stations_geog AS
SELECT
ST_Transform(geom,4326)::geography AS geog,
name,
routes
FROM nyc_subway_stations;
Как сделать работу с PostGIS ещё эффективнее
Виртуальный сервер для использования расширения PostGIS предоставляет уникальную возможность расширить функциональность вашей базы данных и обеспечить более эффективное и точное хранение, управление и анализ геопространственных данных.
Аренда сервера в RU-CENTER с поддержкой PostGIS дает вам возможность создавать сложные запросы, выполнять пространственный анализ и визуализацию, а также реализовывать географические функции непосредственно в базе данных. Это предоставляет вам гибкость и высокую точность при работе с геоданными.
Подведём итоги
Итак, мы познакомились с полезным расширением PostGIS, которое помогает работать с пространственными данными. мы научились устанавливать его, загружать данные и делать запросы. Надеемся, представленная информация будет вам полезна.