Конец года дается Деду Морозу и его эльфам непросто. Они очень заняты приготовлением и доставкой подарков миллионам детей. Я уверен, что им действительно понадобится дополнительная помощь! Используя возможности геопространственной базы данных, мы можем анализировать статистические данные и даже планировать идеальный путь для саней Санты.

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

Функции

Типы данных

Главной особенностью PostGIS является то, что он дает вам возможность создавать пространственные типы данных и манипулировать ими. Эти объекты представлены в базе данных с использованием специального языка описания векторной геометрии: WKT (общеизвестный текст), который является удобочитаемым языком разметки и представляет собой двоичную версию для внутреннего использования БД WKB (хорошо известный двоичный файл). Он поддерживает несколько типов геометрических объектов, включая:

  • Геометрия (общий суперкласс для всех других типов)
  • Точка POINT(20.1 23.5)
    (например, текущее положение оленя)
  • LineString (многоугольная цепочка) LINESTRING(0 0, 2.3 1.3, 14 6)
    (например, путь для саней Санты)
  • Многоугольник POLYGON ((30 10, 40 40, 20 40, 30 10))
    (например, контур фабрики Санты)

Каждая геометрия включает также параметр SRID, который является ссылкой на систему координат, которая изменяет работу всех функций и вычислений. Например, для географической системы координат мы будем использовать SRID 4326 (насколько я знаю, Санта доставляет подарки только на Земле). Полный список см. в docs PostGIS.

Функции

PostGIS имеет широкий набор функций для создания, сравнения, обработки и вычислений пространственных данных. Некоторые из них:

Конструкторы

  • ST_GeomFromText(text,[<srid>]) (создает объект из любого WKT)
  • ST_MakePoint(<x>, <y>)
  • ST_MakeLine(point, point, point...)
  • ST_MakePolygon(linestring)
  • ST_Union(geometries set) (объединяет все геометрические формы)

Расчеты

  • ST_Distance(geometry, geometry, use_spheroid) (расстояние между двумя геометрическими фигурами)
  • ST_Length(geometry, use_spheroid) (длина геометрии, например, кривая)
  • ST_Area(geometry, use_spheroid) (область геометрии, например, многоугольник)

use_spheroid логический флаг указывает, должны ли вычисления выполняться на сфероиде, например. поверхность земли.

Предикаты

  • ST_Intersects(geometry, geometry) (верно, если две геометрии пересекаются)
  • ST_Crosses(geometry, geometry) (верно, если пересекаются две геометрические фигуры)
  • ST_Within(geometry A, geometry B) (верно, если A полностью в B)

Другое

  • ST_AsText(geometry) (преобразует WKB в WKT)
  • ST_SRID(geometry) (изменяет SRID геометрии)

Полный список (+1000) можно найти здесь.

Настраивать

Начнем с простой настройки. Я сосредоточусь на OSX и Ubuntu, но вы можете найти подробные инструкции почти для каждой ОС на сайте PostGIS.

OSX

Самый простой способ получить Postgres и PostGIS на OSX - это загрузить и установить Postgres.app, который поставляется со встроенной поддержкой PostGIS.
Другой вариант - использовать менеджер пакетов Homebrew и запустить:

$ brew install postgis

Ubuntu

Начните с установки PostgreSQL с помощью Aptitude:

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

К сожалению, пакет Ubuntu не включает PostGIS, но мы можем получить его через репозиторий UbuntuGIS, который включает множество пакетов ГИС с открытым исходным кодом. Сначала добавьте новый репозиторий в свои источники:

$ sudo add-apt-repository ppa:ubuntugis/ubuntugis-unstable

Нажмите [Enter] для подтверждения, а затем установите желаемый пакет со всеми его необходимыми зависимостями:

$ sudo apt-get update
$ sudo apt-get install postgis

Включить суперсилы PostGIS

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

Создайте пустую базу данных и подключитесь к ней с помощью psql. Затем включите расширение PostGIS в этой недавно созданной базе данных:

$ createdb santa_project
$ psql santa_project
santa_project=# CREATE EXTENSION postgis;

Теперь вы готовы присоединиться к команде Санты!

База данных

Наконец-то пришло время по-настоящему поработать с нашими пространственными данными. Чтобы иметь возможность работать с функциями PostGIS, вы можете загрузить образцы данных для своей базы данных, предоставленные эльфами, работающими в Центре данных Северного полюса Санты. Для его загрузки используйте:

$ psql santa_project < santa_db.sql

Страны

Первая таблица в базе данных Санты - это countries, в которой хранится информация о странах, включая их географические очертания.

CREATE TABLE countries (
    id integer NOT NULL PRIMARY KEY,
    name character varying(256),
    geom geometry(MultiPolygon, 4326)
);

Вы можете увидеть тип PostGIS geometry с необязательными параметрами MultiPolygon (набор полигонов) и RSID 4326.
(Существует соглашение о присвоении географическому названию столбец geom )

Столицы

Для упрощения анализа мы будем рассматривать только столицу каждой страны.

create table capitals (
    id integer NOT NULL PRIMARY KEY,
    name character varying(256),
    geom geometry(Point, 4326)
);

Дети

Конечно, Санте также нужен список детей с их текущим местонахождением, чтобы доставить подарки. (это всего лишь образец данных из ~ 3500 случайных точек, потому что база данных настоящего Санты, конечно же, СОВЕРШЕННО СЕКРЕТНА)

CREATE TABLE children (
    id integer NOT NULL PRIMARY KEY,
    name character varying(256),
    geom geometry(Point, 4326)
);

Напишите немного SQL

После этого долгого вступления, наконец, пришло время написать несколько простых запросов.
Для годового отчета Санте нужны статистические данные:

Проблема 1

Список стран, упорядоченный по их региону.

Для этой задачи мы должны использовать функцию ST_Area и передать ей геометрию данной страны. Не забудьте установить флаг use_spheroid как true.
Окончательный запрос будет выглядеть следующим образом:

SELECT name, ST_Area(geom, true) AS area
FROM countries
ORDER BY area DESC;

name                    |     area [m2]
------------------------+------------------
Russia                  | 16899340799018.3
Antarctica              | 12307608053238.4
Canada                  | 9690145858384.64
United States           | 9552731304330.88
China                   | 9360697736226.59
...

Проблема 2

Список из 5 стран с наибольшим количеством детей.

На этот раз мы обязательно должны присоединить дочернюю таблицу к странам в зависимости от их местоположения. Но как это сделать? Ответ - ST_Within функция.

SELECT countries.name, COUNT(children.id) AS children_count
FROM countries
LEFT JOIN children ON ST_Within(children.geom, countries.geom)
GROUP BY countries.id, countries.name
ORDER BY children_count DESC
LIMIT 5;

    name      | children_count
--------------+----------------
Russia        |            672
Canada        |            389
United States |            288
China         |            210
Australia     |            169

Проблема 3

Список 5 столиц с наибольшим количеством детей в радиусе 200 км.

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

SELECT capitals.name, COUNT(children.id) AS children_count
FROM capitals
LEFT JOIN children ON
    ST_Distance(children.geom, capitals.geom, true) <= 200000
GROUP BY capitals.id, capitals.name
ORDER BY children_count DESC
LIMIT 5;

 name   | children_count
--------+----------------
Ottawa  |              8
Berlin  |              7
Lusaka  |              7
Astana  |              7
Vilnius |              6

Проблема 4

Один из секретных центров распространения Санты находится в этих координатах:
lat -15.953918, lon -64.504169. Давайте узнаем, в какой это стране.

Как и в Задаче 2, мы должны назначить страну данной точке с помощью функции ST_Within, но на этот раз точка задается координатами, поэтому мы должны создать новую точку с помощью конструктора точки с правильным RSID.
(поскольку в нашем наборе данных координаты хранятся в формате (lon, lat), мы должны записывать данные точки в обратном порядке)

SELECT name
FROM countries
WHERE ST_Within(
        ST_SetSRID(ST_MakePoint(-15.953918, -64.504169), 4326),
        geom
      );

name
---------
Bolivia

Проблема 5

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

Чтобы выяснить это, мы можем использовать функцию ST_Intersects, потому что граница является линией пересечения.
(поскольку Боливия, конечно, пересекается сама с собой, мы должны исключить ее из окончательного результата, используя простое условие) 

SELECT name
FROM countries
WHERE ST_Intersects(
        (SELECT geom FROM countries WHERE name='Bolivia'),
        geom
      )
      AND name != 'Bolivia';

name
-----------
Argentina
Brazil
Chile
Paraguay
Peru

Это все на сегодня!

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

Оставайтесь с нами и с Рождеством Христовым! 🎅 🎄🎁

Если вам понравился этот пост, не забудьте нажать ❤! Вы также можете подписаться на нас в Facebook, LinkedIn и Twitter.