Оглавление:

• Потребность в PostGIS

• Проблематика

• Установка контейнера

• Настройка контейнера

• Загрузка данных в БД при помощи osm2pgsql

• Запросы и визуализация данных

• Пример работы с геоинформацией в базе данных

Потребность в PostGIS

В одном из проектов нужно было анализировать треки городского транспорта и сопоставлять информацию с OpenStreetMap (OSM)

И для удобного хранения и работы с геоинформацией выбрал популярное расширение PostGIS для PostgreSQL.

Проблематика

Самый простой вариант, который я видел – это установить данное расширение при установки PostgreSQL, но меня он не устраивал, так как в локальной Базе Даных (БД) лежат данные и завязана часть процессов на текущий PostgreSQL.

Установить PostGIS в текущий PostgreSQL не удалось из-за конфликта версий, необходимо было обновлять свой PostgreSQL, а старые версии PostGIS для своего PostgreSQL я не нашёл.

Исходя из вышесказанного я выбрал вариант – использовать PostGIS в Docker. Благо для этого есть уже собранный образ. Также Docker добавляет изолированность проекта и после анализа можно будет спокойно удалить контейнер.

Установка контейнера

Для запуска контейнера необходимо выполнить в терминале команду:

docker run -d --name <container_name> 
-e POSTGRES_PASSWORD=<password> 
-e POSTGRES_DB=<name_database> 
-p 1:5432 postgis/postgis

Необходимо заполнить поля своими значениями, без кавычек

• <container_name> – имя контейнера (пр. postgis)

• <password> – пароль (пр. "0")

• <name_database> – название базы данных, в которой будет установлено расширение PostGIS (пр. geo)

После этого мы сможем подключиться через DBeaver к своей БД.
Стандартный порт PostgreSQL «5432» я сменил на «1», потому что на данном порту находится локальный PostgreSQL, по идеи это ни на что не влияет, таких портов у нас много.

Как выглядит подключение к БД:

Так выглядит подключённая БД в DBeaver, со всеми необходимыми нам расширениями:

Настройка контейнера

Основным гео-источником данных является OpenStreetMap (OSM) – это википедия геоинформации, с чем могут возникнуть некоторые тонкости при работе, что данных не будет хватать, они не всегда будут "чистыми" или до конца верными. Но большая часть информации достоверна.

Для загрузки нам необходимы файлы в формате .pbf

Существует два источника для получения необходимых .pbf файлов:

• GeoFabrik – содержит в себе большие дампы стран и округов.

• BBBike – позволяет выгрузить данные определённой области

Чтобы экспортировать данные в БД, необходимо в нашем контейнере установить пакет osm2pgsql

Чтобы зайти в контейнер, нужно выполнить следующие скрипты в терминале:

• docker ps – посмотреть активные контейнеры

• затем скопировав ID контейнера выполнить команду: docker exec -it <container_id> sh

Изначально пакет osm2pgsql не установлен, для установки необходимо выполнить следующие команды:

apt-get update

apt-get install make cmake g++ libboost-dev libboost-system-dev \
libboost-filesystem-dev libexpat1-dev zlib1g-dev \
libbz2-dev libpq-dev libproj-dev lua5.2 liblua5.2-dev

apt-get install osm2pgsql

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

osm2pgsql --version

При успехе должны получить сообщение такого вида:

Также необходимо установить пакет wget следующей командой:

apt-get install wget

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

Загрузка данных в БД при помощи osm2pgsql

Перед загрузкой необходимо определиться, что будете загружать и после этого создать схему В БД под свои нужды, я для примера загружу свой город и для этого создам схему irkutsk в БД. osm2pgsql сам не создаёт схемы под ваши задачи, если загружать данные в несуществующую схему, то вы получите исключение.

Предварительно я сделал выгрызку с сайта BBBike  по Иркутску и поэтому буду получать этот файл.

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

wget https://download.bbbike.org/osm/extract/planet_104.066,52.123_101.536,52.423.osm.pbf

Загрузка прошла успешно и давайте проверим находится ли файл в контейнере при помощи команды ls

Файл лежит в контейнере и теперь его можно загрузить в БД. Напомню, что контейнер – это изолированная среда и поэтому нет возможности загружать файлы со своего ПК.

Для загрузки данных из файла в БД необходимо воспользоваться следующей командой

osm2pgsql -U postgres -W -l -d geo 
-H localhost --output-pgsql-schema=irkutsk 
--number-processes 24 -C 20480 planet_104.066,52.123_101.536,52.423.osm.pbf

Не буду объяснять все атрибуты данной команды, просто скажу, что в документации пакета osm2pgsql всё хорошо объяснено. Также есть хорошое дополнение к основной документации.

Но пару момент всё равно необходимо прояснить:

1. Рекомендую использовать атрибут -l, чтобы координаты были в привычном формате (широта, долгота). Если не установить данный атрибут, то координаты будут в формате x, y (псевдо-меркатор). При добавлении данных нужно будет свои данные прогонять через функции, чтобы преобразовать широту и долготу в x и y соответственно. Подробнее тут (см. Geographic projections).

2. Небольшие файлы грузятся быстро и не сильно задействуя ресурсы вашего устройства, но для загрузки больших файлов необходимо использовать довольно много оперативной памяти. 
   
   Поэтому для загрузки больших файлов необходимо рекомендуется использовать атрибут --slim, который снижает потребление оперативной памяти, но увеличивает время выполнения загрузки данных. Но если у вас нескончаемая оперативная память, то можно и без этого атрибута. Пример загрузки Центрального федерального округа ниже.

Замечу, что скрипт сам создаёт все таблицы и индексы.

Теперь вернёмся к Иркутску.

Успешное выполнение и все логи выглядят следующим образом:

Так выглядят данные в БД:

Данные лежат в БД  и теперь можно перейти к запросам

Запросы и визуализация данных

Выполним простой запрос:

SELECT
   population,
   "name", 
   way
FROM
   irkutsk.planet_osm_point
WHERE
   population IS NOT NULL

И получим такой результат:

Замечу, что в DBeaver уже установлены инструменты для визуализации геоинформации и поэтому нам не нужно думать на этот счёт.

Немного синтезированный пример

В PostGIS множество функций, которые позволяют работать с геоинформацией. Все функции находятся в схеме public

В документации описаны все функции, но мы сейчас рассмотрим функции для расчёта дистанции:

Для начала создадим таблицу с нужными нам точками данным запросом:

CREATE TABLE irkutsk.point
(
   id int, 
   name text,
   latitude NUMERIC(128,20), 
   longitude NUMERIC(128,20),
   way public.geometry(point, 4326),
   PRIMARY KEY(id)
)

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

INSERT INTO irkutsk.point
VALUES
(
   254, 
   'Советская', 
   52.280067, 
   104.32905,
   ST_SetSRID(ST_MakePoint(104.32905, 52.280067), 4326)
)

Теперь можно посмотреть на результат запроса и увидеть, что точка была успешно добавлена в БД:

И теперь обещанный синтезированный пример: нам нужно рассчитать расстояние между данным домом и остановкой общественного транспорта. Этот пример предназначен для демонстрации функций в PostGIS и поэтому о его логике лучше пока не задумываться :)

WITH stop AS (
   SELECT
      p.way AS point_stop,
      p.name AS name_stop,
      (SELECT way AS point_branch FROM irkutsk.point)
   FROM
      irkutsk.planet_osm_point AS p
   WHERE
      p.osm_id = 1527941151
)
SELECT 
   public.st_distance(
      public.st_transform(point_stop::geometry, 3857),
      public.st_transform(point_branch::geometry, 3857)
   )
   ,* 
FROM stop

И получим следующий результат:

Также это всё было визуализировано в DBeaver:

Резюме: PostGIS является сильным инструментом для работы с геоинформацией, обладает множеством функций, которые помогут достичь поставленного результата. И также благодаря DBeaver можно сразу видеть результат своих действий, не загружая данные в сторонние сервисы, не подключая Python к данной работе.