Примеры использования виртуальных слоёв

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

Виртуальный слой основан на Представлении (View) - сущности базы данных, которая сама по себе не содержит никаких данных. Её содержимое выбирается из других слоёв с помощью выполнения SQL-запроса. Этот механизм предоставляет широкие возможности по видоизменению исходных данных (например, объединение слоёв, фильтрация, группировка, агрегация, сортировка и т.д.) без их фактической модификации. При помощи функций расширения PostGIS в виртуальных слоях можно осуществлять различные операции с геометрией.

Подробнее о языке SQL и особенностях СУБД PostgreSQL можно узнать в документации. Информация о расширении PostGIS: официальный сайт PostGIS (на английском), сайт GIS-Lab (на русском).

Общая информация

Виртуальные слои подразумевают использование SQL-запросов только с операциями выборки данных.

В результате запроса должен присутствовать уникальный идентификатор объекта (колонка orbis_id). В случае необходимости, orbis_id может быть сгенерирован конструкцией (row_number() OVER ())::int AS orbis_id, которая номерует каждую запись в виртуальном слое по порядку.

Опционально в виртуальном слое может присутствовать колонка geom, содержащая пространственные данные объектов в «проекции Меркатора» - EPSG:3857.

---

Примеры задач по работе с данными

1. Сделать копию слоя без фактического дублирования

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

SELECT *
FROM layer1;

Подробнее...

2. Сделать виртуальную копию слоя с меньшим количеством записей

Используется для ограничения количества записей в виртуальном слое.

SELECT *
FROM layer1
LIMIT 10;

Подробнее...

3. Сделать виртуальную копию слоя с меньшим количеством полей

Используется для конкретного указания списка полей в виртуальном слое.

SELECT 
    orbis_id, 
    geom, 
    value AS "name" 
FROM layer1;

Конструкция value AS "name" означает, что колонка value в виртуальном слое получит код name. Подробнее...

4. Сделать виртуальную копию слоя с новым вычисляемым значением

Используется если в исходных данных нет нужного значения, но его можно вычислить.

SELECT 
    *, (value1 + value2) / 1000 AS "value3"
FROM layer1;

(value1 + value2) / 1000 - вычисляемое значение из колонок слоя, а value3 - код новой вычисляемой колонки. Для получения результата могут использоваться различные функции и операторы, например математические, строковые, приведения типов и многие другие.

5. Сделать виртуальную копию слоя с объектами, удовлетворяющими условию

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

SELECT *
FROM layer1
WHERE name = 'Москва' AND value > 1000;

name = 'Москва' AND value > 1000; - условие фильтрации. Оно может быть задано при помощи функций и операторов сравнения, логических операторов, операторов поиска по шаблону и многих других.

6. Соединить данные двух слоев

Используется при необходимости одновременного обращения к данным нескольких слоёв или даже для повторного обращения к данным одного слоя.

SELECT 
    (row_number() OVER ())::int AS orbis_id, l1.geom, 
    l2.value, 
    l1.value * l2.value as result 
FROM layer1 as l1
INNER JOIN layer2 as l2
ON l1.orbis_id = l2.layer1_id;

Существует несколько типов соединений, в данном примере для соединения layer1 c layer2 используется INNER JOIN - каждому объекту в layer1 будут сопоставлены объекты из layer2, удовлетворяющие условию ON l1.orbis_id = l2.layer1_id, лишние записи будут откинуты.

Для обеспечения уникальности идентификатора объекта (orbis_id) используется конструкция (row_number() OVER ())::int AS orbis_id, которая номерует каждую запись в виртуальном слое по порядку.

7. Сгруппировать данные слоя

Используется при необходимости вычислить агрегированные значения по слою - например, количество объектов определённого типа, их сумму, среднее и т.д.

SELECT 
    (row_number() OVER ())::int AS orbis_id, 
    null::geometry AS "geom", 
    count(*),
    SUM(column1) AS "area", 
    column2 AS "name"
FROM 
    layer1
GROUP BY
    column2;

В блоке GROUP BY определяется набор колонок, которые будут использоваться для разделения объектов на категории. Каждому уникальному набору значений этих колонок будет соответствовать новая категория. Эти колонки могут быть вынесены в блок SELECT в исходном виде без агрегатных функций. В данном примере такая колонка одна - column2. Подробнее...

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

Если необходимо объединить весь слой в один объект, необходимо использовать агрегатную функцию без конструкции GROUP BY

---

Примеры задач по работе с геометрией при помощи расширения PostGIS

1. Вычислить площадь объектов слоя

SELECT *, 
    ST_Area(ST_Transform(geom, 4326)::geography) AS "area"
FROM layer

Функция ST_Area() принимает на вход геометрию, которая соответствует типу Polygon или MultiPolygon. Для получения площади в квадратных метрах используется конструкция ST_Area(ST_Transform(geom, 4326)::geography), в которой геометрия предварительно переводится в метрическую систему координат. Подробнее...

2. Вычислить длину линейных объектов

SELECT *,
    ST_Length((ST_Transform(geom, 4326)::geography)) AS "length"
FROM 
    layer 

ST_Length() принимает на вход геометрию, которая соответствует типу Line или MultiLine. Для получения длины в метрах используется конструкций ST_Length((ST_Transform(geom, 4326)::geography)), в которой геометрия предварительно переводится в метрическую систему координат. Подробнее...

3. Вычисление центра объектов

SELECT 
    (row_number() OVER ())::int AS orbis_id,
    ST_Centroid(geom) AS geom
FROM 
    layer 

ST_Centroid() вычисляет точку геометрического центра геометрии. Подробнее...

4. Вычисление расстояния между объектами

SELECT 
    (row_number() OVER ())::int AS orbis_id,
    l1.geom,
    ST_Distance(l1.geom, l2.geom) AS "distance"
FROM 
    layer1 as l1
INNER JOIN 
    layer2 AS l2
ON 
    l2.geom NOTNULL

Для каждого объекта layer1 будут созданы записи с полем, в котором содержится расстояние до каждого объекта layer2. Количество записей в виртуальном слое зависит от условия соединения таблиц. ST_Distance() принимает на вход две геометрии. На выходе расстояние между геометриями. Подробнее...

5. Создание точки из колонок широты и долготы

SELECT 
    orbis_id,
    ST_Transform(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326), 3857) AS geom
FROM 
    layer

Функция создания точки - ST_MakePoint(). После создания точки необходимо привязать геометрию к определенной системе координат. Функция привязки к системе координат - ST_SetSRID(). В завершении, необходимо установить правильную проекцию EPSG:3857 - ST_Transform

6. Создание линии из точек

SELECT 
    orbis_id,
    ST_MakeLine(geom) AS geom
FROM 
    layer

ST_MakeLine() является агрегатной функцией. Принимает на вход массив Point (точек), два значения Point или набор Point. Подробнее...

7. Создание полигона

SELECT 
    orbis_id,
    ST_MakePolygon(geom) AS geom
FROM 
    layer

ST_MakePolygon() является агрегатной функцией. Принимает на вход Line или набор данных, состоящий из внешнего и внутреннего значения Line. Подробнее...

8. Создание окружности вокруг точки

SELECT 
    orbis_id, 
    ST_Transform(ST_Buffer((ST_Transform(geom, 4326)::geography), radius)::geometry, 3857) AS geom
FROM
    layer

ST_Buffer() принимает на вход центр окружности(geom), ее радиус (radius) и ряд дополнительных параметров. Для возможности задания радиуса окружности в метрах используется конструкция ST_Transform(ST_Buffer((ST_Transform(geom, 4326)::geography), radius)::geometry, 3857), которая переводит геометрию в метрическую систему координат, строит окружность в метрах и возвращает в исходную проекцию. Подробнее...

9. Получение объектов, полностью находящихся внутри других объектов

SELECT
    l1.*
FROM 
    layer1 AS l1 
INNER JOIN 
    layer2 AS l2
ON 
    l2.orbis_id = 1
WHERE 
    ST_Contains(l2.geom, l1.geom);

Мы объединяем два слоя, при этом в слое layer2(l2) находится полигон, по которому производится проверка - содержится ли геометрия объектов внутри данного полигона или нет. В данном примере считаем, что полигон проверки имеет значение orbis_id равное 1. Для проверки можно использовать сразу несколько полигонов, в этом случае при объединении таблиц необходимо указать поля которые нам необходимы. Например, для извлечения всех полигонов проверки необходимо поставить условие ON l2.orbis_id NOTNULL. Для получения объектов, которые не находятся в области, необходимо поставить отрицательное условие по функции ST_Contains(). Пример: WHERE NOT ST_Contains(l2.geom, l1.geom); ST_Contains() принимает на вход два значения или массива геометрии. На выходе boolean значение (true, false). Подробнее...

10. Получение объектов, пересекающих другие объекты

SELECT
    l1.*
FROM 
    layer1 AS l1 
INNER JOIN 
    layer2 AS l2
ON 
    l2.orbis_id = 1
WHERE 
    ST_Intersects(l2.geom, l1.geom);

Объединение слоев описано в пункте 9. Для получения объектов, которые не пересекают область, необходимо поставить отрицательное условие по функции ST_Intersects(). Пример: WHERE NOT ST_Intersects(l2.geom, l1.geom); ST_Intersects() принимает на вход два значения или массива геометрии. На выходе boolean значение (true, false). Подробнее...

11. Получение объектов, соприкасающихся c другими объектами

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

SELECT 
    l1.*
FROM 
    layer1 AS l1 
INNER JOIN 
    layer2 AS l2
ON 
    l2.orbis_id = 1
WHERE 
    ST_Touches(l2.geom, l1.geom);

Объединение слоев описано в пункте 9. Для получения объектов, которые не пересекают область, необходимо поставить отрицательное условие по функции ST_Touches(). Пример: WHERE NOT ST_Touches(l2.geom, l1.geom); ST_Touches() принимает на вход два значения или массива геометрии. На выходе boolean значение (true, false). Подробнее...

12. Получение объектов, находящихся на определенном расстоянии от других объектов

SELECT
    l1.*
FROM 
    layer1 AS l1 
INNER JOIN 
    layer2 AS l2
ON 
    l2.orbis_id = 1
WHERE 
    ST_DWithin(ST_Transform(l1.geom, 4326)::geography, ST_Transform(l2.geom, 4326)::geography, 100);

Объединение слоев описано в пункте 9. Для получения объектов, которые находятся на расстоянии больше указанного, необходимо поставить отрицательное условие по функции ST_DWithin(). Пример: WHERE NOT ST_DWithin(.geom, l2.geom, 10000); ST_DWithin() принимает на вход два значения или массива геометрии и расстояние. На выходе boolean значение (true, false). Для возможности указания расстояния в метрах используется конструкция ST_Transform(l1.geom, 4326)::geography, которая переводит геометрию в метрическую систему координат. Подробнее...

13. Получение объектов с одинаковой геометрией

SELECT 
    l1.*
FROM 
    layer1 AS l1 
INNER JOIN 
    layer2 AS l2
ON 
    l2.orbis_id = 1
WHERE 
    ST_Equals(l1.geom, l2.geom);

Объединение слоев описано в пункте 9. Для получения объектов, которые не находятся в области, необходимо поставить отрицательное условие по функции ST_Equals(). Пример: WHERE NOT ST_Equals(l1.geom, l2.geom); ST_Equals() принимает на вход два значения или массива геометрии. На выходе boolean значение (true, false). Подробнее...

14. Объединение геометрии объектов

SELECT 
    (row_number() OVER ())::int AS orbis_id,
    ST_UNION(geom) AS geom,
    column
FROM 
    layer
GROUP BY
    column

ST_UNION() является агрегатной функцией, в данном примере осуществляется группировка по колонке column. Принимает на вход набор или массив геометрии одно типа (Point, Polygon, Line). На выходе создает Multi* геометрию соответствующего типа. Подробнее...

15. Исключить геометрию одного объекта из геометрии другого

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

SELECT 
    (row_number() OVER ())::int AS orbis_id,
    ST_Difference(l1.geom, l2.geom) AS "geom"
FROM 
    layer1 AS l1 
INNER JOIN 
    layer2 AS l2
ON 
    l2.orbis_id = 1

Объединение слоев описано в пункте 9. ST_Difference() принимает на вход исходную геометрию, и геометрию, по которой мы будем её обрезать. Подробнее...

16. Получить геометрию пересечения двух объектов

SELECT 
    (row_number() OVER ())::int AS orbis_id,
    ST_Intersection(l1.geom, l2.geom) AS "geom"
FROM 
    layer1 AS l1 
INNER JOIN 
    layer2 AS l2
ON 
    l2.orbis_id = 1

Объединение слоев описано в пункте 9. ST_Intersection() принимает на вход две геометрии объектов, возвращает геометрию для их пересечения. Подробнее...