Эта статья предназначена для ГИС-специалистов, которые слышали про SQL, но не решались начать работу с ним.
Многие хотят научиться работать с SQL, но их останавливает перспектива променять QGIS на другое ПО с непривычным интерфейсом, которое еще нужно как-то настраивать. Но современные версии QGIS и формат файла GeoPackage дают возможность попробовать и ощутить часть возможностей SQL, получить начальный опыт использования этого языка и решить, окунаться ли дальше в мир баз данных или нет.
Небольшая база про SQL
SQL – это язык запросов для работы с базами данных, представляющих собой по большей части таблицы, подобные тем, что мы видим в Excel. Только в базе данных может быть не одна табличка, а множество. И с помощью кода мы можем обращаться к разным таблицам, чтобы их сравнить, объединить в новые или провести вычисления между столбцами. Язык SQL появился более 50 лет назад и открыл нам множество возможностей, в том числе для работы с пространственными данными.
По синтаксису SQL очень похож на язык запросов в QGIS, которым вы могли пользоваться в генераторе геометрии или в калькуляторе полей. Множество функций в QGIS и в SQL называются одинаково, поэтому ГИС-специалисты найдут в SQL знакомые элементы.
Как использовать SQL в QGIS
В QGIS есть различные способы использования этого языка. Самые простые и требующие минимальных настроек – через формат GeoPackage (.gpkg). Этот формат пришел на замену шейп-файлу, он уже используется повсеместно и легко открывается в QGIS. Если вы вдруг еще не работали с таким форматом, почитайте про его возможности.
Чтобы превратить любой файл .shp в .gpkg, достаточно просто его пересохранить в этот формат. Более того, вы можете сохранить несколько шейп-файлов в один .gpkg, и получится компактная база данных, которую можно скинуть одним файлом коллеге.
Для примера работы с SQL-запросами я возьму .gpkg-файл со всеми данными из OpenStreetMap на целую страну – Исландию. Страна небольшая, но распакованный файл весит больше 200 Мб.
Для того, чтобы можно было делать SQL запросы в нашем файле, достаточно сделать одно маленькое действие – подключить файл. Делается это просто: в окне браузера ищем строку GeoPackage, нажимаем правой клавишей мыши, «Создать соединение» и открываем файл.
Готово – наш файл подключен к QGIS, он появился в браузере, и с ним теперь можно выполнять SQL-запросы.
Если раскрыть список под файлом iceland.gpkg, увидим список слоев из OSM. Их можно перетащить в проект, и они сразу откроются на карте. Если вы закроете QGIS и откроете его заново, то подключение сохранится.
Ваш первый SQL-запрос
Самое простое и в то же время основополагающее, что может сделать SQL – выбрать объекты по какому-то критерию из выборки.
Чтобы сделать запрос, правой клавишей мыши кликаем на название gpkg-файла, в моем случае – iceland.gpkg, выбираем «Выполнить SQL запрос…», и перед нами откроется аскетичное пустое окно, в котором мы будем писать простенький код.
Для начала попробуем выбрать определенные объекты из слоя – например, вулканы, которыми славится Исландия.
Быстренько разберем структуру запроса, чтобы понять самые основы SQL:
SELECT * – выбрать всю информацию из всей колонки. Звездочкой (*) в SQL означает все
FROM gis_osm_natural_free – из таблицы (слоя) gis_osm_natural_free.
WHERE fclass = 'volcano' – только в случае, когда значение в колонке fclass для объекта равно ‘volcano’
Загружаем результат как слой проекта и видим нашу выборку в виде точечного слоя:
Менеджер БД (DB manager)
Еще один вариант использования SQL запросов в QGIS – Менеджер БД, то есть баз данных (DB Manager). Этот инструмент тоже встроен в QGIS.
Менеджер БД скрывается под вкладкой «База данных» на главной панели QGIS. Откроем его:
Чтобы получить возможность писать SQL-запросы в Менеджере БД, нужно в окне «Провайдеры» слева выбрать нужный gpkg-файл, раскрыть список слоев в нем, а после этого нажать на значок «Окно SQL» (с гаечным ключом на фоне листка).
SQL позволяет в запросе выбирать, информацию из каких колонок нам нужно взять, какие объекты необходимо выбрать, а еще он позволяет посчитать какую-либо характеристику, например, площадь. Запрос на скриншоте выше позволяет сделать все это за 0.027 секунд для слоя, в котором более 100 тысяч объектов.
А теперь разберем код:
SELECT fid, name, ST_Area(geom) as Area – в отличие от прошлого кода мы берем не всю информацию, а только из определенных колонок. Причем колонки «fid» и «name» уже существовали. К ним мы добавляем колонку «Area», для которой считаем площадь с помощью функции ST_Area. «geom» – это невидимая колонка с информацией о геометрии
FROM gis_osm_buildings_a_free – из таблицы (слоя) со зданиями gis_osm_buildings_a_free
WHERE type = 'retail' – где все здания соответствуют типу «retail», то есть предназначены для розничной торговли
ORDER BY name DESC – сортируем полученный результат по колонке «name» по убыванию (DESC), чтобы сверху у нас были объекты с названиями
Как видите, SQL позволяет проводить некоторые операции заметно быстрее функций QGIS. Там где QGIS зависнет и не выполнит операцию, SQL может нас спасти, причем результат мы получим довольно быстро.
Помимо этого не забываем про плюсы кода — вместо использования нескольких разных функций мы пишем всего один запрос, который можно использовать многократно.
Куда идти дальше за знаниями по SQL
Надеюсь вам была полезна эта статья, и вы прикоснулись к SQL самостоятельно. Если вы хотите узнать больше про применение SQL в ГИС-задачах, почитайте, зачем ГИС-специалисту SQL.
А чтобы увереннее использовать SQL, разобраться в типах данных и лучше понимать, как работают SQL запросы, записывайтесь на курс PostgreSQL и QGIS.