Использование SQL-запросов при работе с векторными данными

Где используется в QGIS:

- Добавить виртуальный слой (Virtual Layer)
- Инструменты анализа - Выполнить SQL-запрос

Чаще всего используется SQL-оператор SELECT.

Форма записи:

SELECT [список полей через запятую, которые должны присутствовать в результате]
FROM [список слоев через запятую] 
 или 
FROM [слой1] JOIN [слой2] ON [условие соединения]

WHERE [условия фильтрации]
GROUP BY [список полей, по которым группируются результаты]
HAVING [условия фильтрации после группировки]
ORDER BY [список полей сортировки] ASC | DESC
LIMIT [ограничить вывод заданным числом строк]

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

boolean st_intersects(geometry1, geometry2) - Возвращает Да или Нет (пересескаются две геометрии или нет)
real st_area(geometry) - Возвращает площадь геометрии полигона. Для градусной системы координат не имеет смысла.
real st_length(geometry) - Возвращает длину геометрии линии. Для градусной системы координат не имеет смысла.
geometry st_transform(geometry, EPSG_CODE) - Перепроецирование. Возвращает новую геометрию указанной системе координат EPSG_CODE.
geometry st_buffer(geometry, radius) - Возвращает полигональную геометрию в виде буфера указанного радиуса вокруг исходной геометрии.

Список основных функций работы с геометрией.

В запросах, использующих группировку (GROUP BY), часто применяются "агрегатные" функции: sum(), max(), min(), count(), avg().

Примеры:

1. Набор данных "Аляска"

Данные можно взять с сайта qgis.org - Демонстрационный набор данных.
Слои - alaska, airports.
Задача: Найти острова размером меньше 1000 квадратных милей, на которых есть аэропорты.

Добавить виртуальный слой (Virtual Layer).
В поле Запрос написать:

SELECT alaska.*
FROM alaska JOIN airports ON st_intersects(alaska.geometry, airports.geometry)
WHERE alaska.AREA_MI < 1000;

2. Лабораторная с сайта qgistutorials.com - Выполнение пространственных присоединений

Задача: посчитать число мест в приютах по каждому району Нью-Йорка.

Пусть слои называются nybb, nursing.

Добавить виртуальный слой (Virtual Layer).
В поле Запрос написать:

select nybb.*, sum(nursing.Capacity) as sumcap
from nybb join nursing on st_intersects(nybb.geometry, nursing.geometry)
group by nybb.geometry;

3. Лабораторная с сайта qgistutorials.com - Анализ количества точек в полигонах

Задача: Посчитать число землетрясений в России (NAME='Russia')

Пусть слои называются countries, signif.

Добавить виртуальный слой (Virtual Layer).
В поле Запрос написать:

select countries.*, count(signif.geometry) as cnt
from countries join signif on st_intersects(countries.geometry, signif.geometry)
where countries.NAME='Russia'
group by countries.geometry;

Примечание. Запрос может выполняться очень долго!

4. Задача: Найти три самые длинные реки, протекающие по территории Удмуртии

Слой water-line из набора данных по Удмуртии RU-UD.zip.
Переименовать слой water-line в rivers (слои с дефисами в названии придется в кавычки заключать).
Добавить виртуальный слой (Virtual Layer).
В поле Запрос написать:

select NAME, sum(st_length(st_transform(geometry, 3395))) as length /*:real*/
from rivers
group by NAME
having NAME <> ''
order by length desc
limit 3;

Примечание. Результирующая таблица не будет иметь геометрии, так как мы ее не включили в список полей.

5. Задача: Найти города в мире, расположенные на водоемах (не далее 10 км от водоема)

Данные по городам и водоемам следует взять из Лабораторной - Выполнение пространственных запросов.
Пусть слои называются: towns, rivers.

Добавить виртуальный слой (Virtual Layer).
В поле Запрос написать:

select towns.geometry, towns.name, rivers.name
from towns join rivers 
on st_intersects(st_transform(st_buffer(st_transform(towns.geometry, 3395), 10000), 4326),rivers.geometry);

Примечание. В запросе происходит перепроецирование геометрий городов в World Mercator для построения буфера/радиуса в 10 км, и затем обратно в WGS'84.
Примечание 1. Запрос может выполняться очень долго!

Задачу 5 для ускорения расчетов можно разбить на части. Для этого мы дополнительно ограничим поиск территорией России.
Будем использовать инструмент "Выполнить SQL-запрос" (найти в инструментах анализа).

5.1. Добавим еще один слой со странами. Переименуем в countries.

5.2. Создадим полигональный слой rustowns с российскими городами, очертив радиус в 10 км вокруг точечного слоя towns (Попутно сделав трансформацию в метрическую проекцию).

select st_transform(st_buffer(st_transform(geometry,3857),10000),4326) as geom/*:polygon:4326*/, name, adm0name 
from towns 
where adm0name='Russia'

Переименуем результат SQL-запроса в rustowns.

5.3. Ограничим все реки территорией России (кроме странной реки "Луара"). Для этого используем слой countries.

select rivers.geometry, rivers.name_ru
from rivers join countries on st_intersects(rivers.geometry, countries.geometry)
where countries.name='Russia' and rivers.name_ru <> 'Луара'

Переименовать в rusrivers.

5.4. Вычислим города, стоящие на реках:

select rustowns.geometry, rustowns.name, rusrivers.name_ru
from rustowns join rusrivers
on st_intersects(rustowns.geometry, rusrivers.geometry)

Переименовать в rivtowns.

5.5. Запросы (5.2 - 5.4) можно объединить в один:

select rustowns.geom, rustowns.name, rusrivers.name_ru
from
   (
        select st_transform(st_buffer(st_transform(geometry,3857),10000),4326) as geom/*:polygon:4326*/, name, adm0name 
        from towns where adm0name='Russia'
    ) as rustowns
join
   (
        select rivers.geometry, rivers.name_ru
        from rivers join countries on st_intersects(rivers.geometry, countries.geometry)
        where countries.name='Russia' and rivers.name_ru <> 'Луара'
    ) as rusrivers
on st_intersects(rustowns.geom, rusrivers.geometry)

5.6. Дополнительно. Найти города, стоящие на слиянии нескольких рек.

select geometry, name, count(distinct name_ru) as cnt /*:int*/
from rivtowns
group by geometry 
having cnt > 1

Подпишите названия рек и городов.