Использование расширения PostGIS в PostgreSQL

Введение

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

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

Создание пространственной базы данных и включение PostGIS

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

Сначала откройте терминал и подключитесь к серверу PostgreSQL, используя интерактивный терминал psql. Вы подключитесь как суперпользователь postgres по умолчанию.

sudo -u postgres psql

Вы увидите приглашение psql, которое выглядит как postgres=#.

Далее создайте новую базу данных с именем spatial_db. Все команды SQL в psql должны заканчиваться точкой с запятой (;).

CREATE DATABASE spatial_db;

Вы должны увидеть следующее подтверждающее сообщение:

CREATE DATABASE

Теперь подключитесь к только что созданной базе данных. Команда \c используется для подключения к другой базе данных.

\c spatial_db

Ваше приглашение изменится, указывая, что вы теперь подключены к spatial_db:

You are now connected to database "spatial_db" as user "postgres".
spatial_db=#

После подготовки базы данных включите расширение PostGIS. Эта команда загрузит все пространственные типы, функции и таблицы PostGIS в вашу базу данных.

CREATE EXTENSION postgis;

Вывод подтвердит, что расширение было создано:

CREATE EXTENSION

Чтобы проверить правильность установки PostGIS, вы можете проверить его версию.

SELECT PostGIS_full_version();

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

Создание пространственной таблицы и вставка данных

Теперь, когда у вас есть пространственная база данных, вы можете создавать таблицы для хранения географических данных. На этом шаге вы создадите таблицу cities и вставите координаты нескольких крупных городов.

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

Сначала создайте таблицу с именем cities. Эта таблица будет иметь стандартные поля id и name, а также столбец location типа GEOMETRY.

Понимание GEOMETRY(Point, 4326)

• GEOMETRY: Это фундаментальный тип данных PostGIS для хранения пространственных объектов.
• Point: Указывает, что столбец геометрии будет хранить только точки. Другие типы включают LINESTRING и POLYGON.
• 4326: Это идентификатор системы пространственных ссылок (SRID). SRID 4326 соответствует WGS 84, стандартной системе координат, используемой GPS и Google Maps.

Выполните следующую команду для создания таблицы:

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    location GEOMETRY(Point, 4326)
);

Вы увидите подтверждение:

CREATE TABLE

Далее вставьте данные в таблицу cities. Вы будете использовать функцию ST_GeomFromText для преобразования текстовых представлений точек в формате Well-Known Text (WKT) в объекты геометрии. Формат: POINT(долгота широта).

INSERT INTO cities (name, location) VALUES
    ('New York', ST_GeomFromText('POINT(-74.0060 40.7128)', 4326)),
    ('London', ST_GeomFromText('POINT(-0.1278 51.5074)', 4326)),
    ('Tokyo', ST_GeomFromText('POINT(139.6917 35.6895)', 4326));

Вывод указывает, что было вставлено три строки:

INSERT 0 3

Чтобы проверить правильность вставки данных, вы можете выполнить запрос к таблице. Используйте функцию ST_AsText для преобразования объекта геометрии обратно в удобочитаемый текстовый формат.

SELECT id, name, ST_AsText(location) FROM cities;

Вывод должен отобразить только что вставленные вами данные:

 id |   name   |          st_astext
----+----------+-----------------------------
  1 | New York | POINT(-74.006 40.7128)
  2 | London   | POINT(-0.1278 51.5074)
  3 | Tokyo    | POINT(139.6917 35.6895)
(3 rows)

Вы успешно создали пространственную таблицу и заполнили ее данными.

Выполнение пространственных запросов и анализа

Настоящая мощь PostGIS заключается в его способности выполнять пространственные запросы. На этом шаге вы будете использовать функции PostGIS для расчета расстояния между двумя точками и для определения, какие точки попадают в определенную область.

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

Сначала давайте рассчитаем расстояние между Нью-Йорком и Лондоном. Для точного расчета расстояния на поверхности Земли лучше всего преобразовать тип geometry в тип geography. Тип geography учитывает кривизну Земли, а ST_Distance вернет результат в метрах.

SELECT ST_Distance(
    (SELECT location::geography FROM cities WHERE name = 'New York'),
    (SELECT location::geography FROM cities WHERE name = 'London')
);

Запрос вернет расстояние по большому кругу в метрах. Результатом будет большое число, примерно 5,57 миллиона метров.

   st_distance
-----------------
 5570299.7888681
(1 row)

Далее давайте выполним анализ, чтобы найти, какие из наших городов находятся в пределах 200 километров от Лондона. Для этого вы сначала создадите круговой буфер вокруг местоположения Лондона, а затем проверите, какие местоположения городов пересекаются с этим буфером.

• ST_Buffer: Создает геометрию, представляющую все точки, расстояние которых от исходной геометрии меньше или равно указанному расстоянию. При использовании с типом geography расстояние указывается в метрах.
• ST_Intersects: Возвращает true, если две геометрии "пространственно пересекаются" — если у них есть общая область.

WITH london_buffer AS (
  SELECT ST_Buffer(location::geography, 200000) AS geom FROM cities WHERE name = 'London'
)
SELECT c.name
FROM cities c, london_buffer lb
WHERE ST_Intersects(c.location::geography, lb.geom);

Этот запрос использует Common Table Expression (CTE) для создания буфера радиусом 200 000 метров (200 км) вокруг Лондона. Затем он находит все города, которые пересекаются с этим буфером. Как и ожидалось, только сам Лондон находится в этой области.

  name
--------
 London
(1 row)

Теперь вы выполнили базовый пространственный анализ. Вы можете выйти из оболочки psql, набрав \q.

\q

Очистка базы данных

Хорошей практикой является очистка вашей среды после завершения работы. На этом шаге вы удалите базу данных spatial_db. Удаление базы данных означает удаление самой базы данных вместе со всеми содержащимися в ней объектами, такими как таблицы и расширения.

Вы должны вернуться к обычному приглашению терминала (labex@vm:~$).

Чтобы удалить базу данных, вы должны быть подключены к другой базе данных. Подключитесь к стандартной базе данных postgres.

sudo -u postgres psql

Теперь выполните команду DROP DATABASE.

DROP DATABASE spatial_db;

Вы увидите подтверждающее сообщение:

DROP DATABASE

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

\l

Вы увидите, что spatial_db больше нет в списке.

Наконец, выйдите из оболочки psql.

\q

Резюме

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