SQL-подобные запросы
При проведении геоинформационного анализа часто возникает ситуация, при которой необходимо манипулировать с данными из различных источников: добавлять, удалять, объединять или обогащать. Для этих целей часто используется SQL — язык структурированных запросов для управления данными в реляционных базах данных. В инструментах разработчика ЭверГИС есть возможность манипулировать слоями и данными при помощи SQL-подобного языка запросов.
В данном примере мы разберем объединение двух наборов пространственных данных и их обогащение за счет третьего слоя. Также мы рассмотрим примеры создания дашбордов. Сначала мы создадим простой дашборд, в котором будет находиться круговая диаграмма по данным из обновленного слоя. Далее мы добавим к ней фильтр, позволяющий проводить более подробный анализ. В конце мы сделаем интерактивную диаграмму — она сама станет фильтром, позволяя манипулировать с представлением слоя.
Исходные данные
Ссылка для загрузки данных
Набор пространственных данных можно скачать по ссылке
Территорией исследования является город Нижний Новгород и его окрестности.
Описание и формат данных:
- Набор пространственных данных о зданиях в Нагорной части Нижнего Новгорода и городе Бор в формате ESRI Shapefile
- Набор пространственных данных о зданиях в Заречной части Нижнего Новгорода в формате ESRI Shapefile
- Набор пространственных данных о принадлежности зданий к обобщенным категориям функционального зонирования генерального плана Нижнего Новгорода в формате ESRI Shapefile
ESRI Shapefile (Здания Верхней части)
| build_id (Python: int) | build_type (Python: string) | district (Python: string) | geometry: ESRI White Paper – July 1998 |
|---|---|---|---|
| 575533842 | apartments | Приокский район | бинарное представление |
| 3823716 | commercial | Нижегородский район | бинарное представление |
| 171039918 | industrial | Город Бор | бинарное представление |
| … | … | … | … |
ESRI Shapefile (Здания Заречной части)
| build_id (Python: int) | build_type (Python: string) | district (Python: string) | geometry: ESRI White Paper – July 1998 |
|---|---|---|---|
| 9652538 | stadium | Канавинский район | бинарное представление |
| 32593107 | industrial | Автозаводский район | бинарное представление |
| 177084186 | residential | Сормовский район | бинарное представление |
| … | … | … | … |
ESRI Shapefile (Здания с функциональными зонами)
| build_id (Python: int) | func_zone (Python: string) | geometry: ESRI White Paper – July 1998 |
|---|---|---|
| 2863648 | Общественно-деловая зона | бинарное представление |
| 42315105 | Зона высокоэтажной застройки | бинарное представление |
| 197821284 | Зона малоэтажной застройки | бинарное представление |
| … | … | … |
Создание папки и карты
Создание папки
Мы рекомендуем создать рабочую директорию в каталоге и сохранить в неё файл карты. Если Вы сделаете это, то при импорте или создании слоев путь до нужной папки будет подставляться автоматически
Создание карты
Инструменты разработчика доступны только после создания и сохранения карты. В отличие от предыдущих примеров, в этом создание карты является обязательным этапом
Для создания папки, в которой будет храниться карта, слои и данные перейдите в главное меню и выберите в нем Каталог:
Внутри каталога вы увидите все доступные для вас файлы и папки. Для создания папки в верхнем уровне Каталога перейдите на вкладку Только мои. Для этого нажмите на кнопку с надписью Все доступные со стрелкой вниз. Откроется выпадающее меню и там нажмите Только мои.
После перехода к этому разделу вы увидите все карты, слои, данные, подключения и инструменты, созданные Вами. Для создания папки, которую будете видеть только вы нажмите на кнопку Создать папку в выпадающем меню кнопки Добавить.
После нажатия у вас откроется окно создания папки. Укажите название в окне названия. Также вы можете добавить тэги и описание проекта. Обязательным является лишь название. вы можете использовать символы русского и английского алфавитов, цифры, пробелы и нижнее подчеркивание в названии папки.
Для начала работы мы рекомендуем создать карту – текущую рабочую область, в которой сохранены данные о видимости слоев, открытых в системе источниках данных, созданных инструментах. Некоторые возможности системы, например инструменты разработчика, доступны после создания и сохранения карты. При входе в систему по умолчанию создается временная карта, которую необходимо сохранить в каталоге. Для того, чтобы сохранить карту, откройте главное меню и нажмите на кнопку сохранить.
При сохранении карты вам необходимо указать название карты. Также вы можете указать папку в каталоге, в которой будет сохранена карта, системное имя, описание и тэги. Для выбора пути нажмите на кнопку в виде папки во второй строчке окна импорта. Далее выберите и войдите в папку, в которую вы хотите сохранить свои данные. В тестовом примере сохранение производится в папку, созданную на предыдущем этапе. Системное имя задается автоматически и может содержать лишь английские буквы, цифры и нижнее подчеркивание. В данном примере мы не будем менять системное имя для карты и импортируемых данных.
Импорт данных
Импорт ESRI Shapefile
Информация в этом формате хранится в нескольких файлах. Для импорта в систему необходимо создать архив в формате .zip. Поместите в него файлы с расширениями .shp, .shx, .dbf. Остальные файлы являются необязательными. Импортируемые данные должны быть спроецированы в EPSG:4326
Три набора пространственных данных представлены в формате ESRI Shapefile. Для трех наборов пространственных данных должно быть три архива, которые Вы последовательно должны загрузить в систему. Для того, чтобы импортировать данные в систему необходимо открыть главное меню и нажать на кнопку Импорт. Далее нужно выбрать вариант С компьютера и выбрать архив с ESRI Shapefile в файловом менеджере.
Импорт данного формата состоит из двух шагов: настройки атрибутов и настройки названия слоя. На этапе настройки атрибутов необходимо выбрать какие атрибуты будут импортированы в слой. Для этого нужно поставить галочку напротив нужных атрибутов в чекбоксах. Также нужно указать имя атрибута после импорта и его тип. Имя атрибута может содержать только символы латиницы, цифры и нижние подчеркивания. Система автоматически определяет тип импортируемых данных, однако вы можете поменять их при необходимости. Например, вы можете импортировать числа с плавающей запятой как целые, тогда дробная часть будет отброшена. Для этого нажмите на кнопку рядом с названием атрибута и выберите нужный тип из выпадающего списка.
На этапе настройки названия слоя вам нужно выбрать название слоя, расположение в каталоге и системное имя в соответствующих полях. Название слоя может быть любым, а системное имя может включать только символы латиницы, цифры и нижние подчеркивания. Системное имя создается автоматически, однако вы можете его поменять. В случае, если со слоем предполагаются манипуляции при помощи использования SQL-подобных запросов или работы с ним при помощи скриптов в инструментах разработчика, то целесообразно самостоятельно задать системное имя. Оно должно быть уникальным среди всех данных пользователя. В дальнейшем это поможет при составлении запросов к слоям, делая их более понятными для разработчика. Если перед импортом вы создавали карту, то система автоматически настроит путь сохранения слоя до папки с картой. Если вы хотите изменить его, нажмите на кнопку с символом папки и выберите нужный путь. Опционально вы можете написать заполнить поля описание и тэги.
При импорте тестовых данных мы сохранили названия атрибутов из исходных данных. Слои были названы следующим образом:
- Набор пространственных данных о зданиях в Верхней части Нижнего Новгорода и городе Бор получил название Здания Верхней части и системное имя eromakh.nn_upper_buildings
- Набор пространственных данных о зданиях в Заречной части Нижнего Новгорода получил название Здания Заречной части и системное имя eromakh.nn_lowerpart_buildings
- Набор пространственных данных о принадлежности зданий к обобщенным категориям функционального зонирования генерального плана Нижнего Новгорода получил название Здания с функциональными зонами и системное имя eromakh.build_fzones
Работа с SQL-подобными запросами
Тип запроса
Все запросы в системе делаются при помощи оператора SELECT. Операторы DELETE и UPDATE не поддерживаются
Есть два способа открыть меню для работы с SQL-подобными запросами: в первом случае его можно открыть по нажатию на кнопку EQL-запрос в главном меню. Автоматически откроются инструменты разработчика, и вы увидите запрос в списке ресурсов карты. В этом окне вы увидите пустой временный запрос, который можно сохранить для последующего переиспользования. Для сохранения запроса нажмите на кнопку с дискетой и выберите папку в каталоге, в котором он будет сохранен. После этого вы в каталоге увидите объект типа Запрос и сможете открыть его в дальнейшем. В случае создания запроса через главное меню у вас есть возможность выбора типа запроса, всего доступно четыре типа запросов:
- Выборка (SELECT)
- Обновление (UPDATE)
- Добавление (ADD)
- Удаление (DELETE)
Второй вариант открытия окна SQL-подобных запросов связан с контекстным меню слоя. Для открытия окна запросов нажмите на кнопку EQL в контекстном меню слоя. По нажатию на эту кнопку также откроются инструменты разработчика. В списке ресурсов карты вы увидите новый временный запрос, который включает название слоя: EQL: название слоя. вы также можете сохранить запрос в каталоге для дальнейшего переиспользования. Для этого нажмите на кнопку в виде дискеты в инструментах разработчика. В окне запроса будет написан базовый SQL запрос: select * from owner_name.layer_system_name. В связи с использованием системного имени в запросах целесообразно задать его при импорте слоя. Открытие запроса через контекстное меню слоя позволяет делать лишь запросы типа SELECT (выборка). Для остальных запросов нужно открыть окно запросов через главное меню.
Выборка из слоя
В первую очередь при проведении геоинформационного анализа стоит убедиться, что при импорте не возникло ошибок с данными. Для этого мы используем базовый SELECT запрос, который выведет информацию в окно вывода. Для создания выборки нужно перейти в главное меню и нажать на EQL-запрос. В открывшемся окне запросов введем стандартный запрос на выборку всех данных в слое: SELECT * FROM eromakh.nn_upper_buildings. Для выбора SELECT запроса нужно нажать на кнопку выбора типа запроса и далее в выпадающем списке Выборка.
После того, как вы убедитесь в том, что вы написали правильный запрос и указали нужный тип запроса, можно запустить его выполнение по нажатию на кнопку Старт. После того как система обработает запрос, вы увидите результат в поле вывода. Вывод результата запроса в окне запросов ограничен 50 строками значений. вы не можете увеличить это значение, однако можете его сократить, используя стандартную для SQL инструкцию LIMIT.
Запрос на удаление данных
Как мы видим данные включают в себя информацию о зданиях, находящихся за пределами Нижнего Новгорода, а именно в городе Бор. Эти данные нам не нужны. Далее мы можем пойти двумя путями: если нам нужен и исходный, и новый слои, то мы можем при помощи выборки создать слой. Для этого нам нужно написать SQL-запрос, который бы фильтровал данные по значению, и далее нажать на кнопку Сохранить, где в выпадающем списке выбрать вариант Сохранить как слой.
Другой вариант — удаление данных из слоя. Он предпочтителен, если вы не хотите сохранять оригинальный слой. Это может быть полезно, если вы не хотите создавать дополнительные слои при геообработке. Для этого нужно написать SELECT запрос на удаление. Его является необходимость указания ресурса для обновления и уникального идентификатора в соответствующих полях. Поле Ресурс для обновления содержит выпадающий список из слоев, присутствующих на карте. В запросе на удаление вам нужно написать запрос, который бы выбрал все удаляемые данные. Например, запрос, который бы удалял все здания, относящиеся к городу Бор в слое Здания Верхней части, выглядит так: SELECT * FROM eromakh.nn_upper_buildings WHERE district = 'Город Бор'. В поле Ресурс для обновления будет указан слой Здания Верхней части, а в поле Уникальный идентификатор — gid. Здания Верхней части и города Бор оформлены при помощи красной заливки.
После удаления всех значений поля district = 'Город Бор' в слое останутся только значения, которые относятся к самому Нижнему Новгороду. В поле вывода SQL-подобного запроса вы увидите строку состояния, которая будет показывать сколько объектов в слое, было обработано, а также время выполнения запроса в милисекундах. После выполнения запроса слой автоматически обновится, и вы увидите изменения на экране.
Запрос на добавление данных
После удаления данных города Бор необходимо объединить два набора пространственных данных о зданиях на разных берегах реки Ока. Объединение данных осуществляется через запрос на добавление данных (INSERT INTO). Для создания этого запроса в меню запросов нужно выбрать Добавление (ADD). Далее вам нужно решить: хотите ли вы создать новый слой, который будет содержать в себе данные обоих объединяемых слоев или вы хотите добавить данные из одного слоя в другой. В первом случае в настройках запроса в нижней части окна выберите Новый слой. В соответствующих полях введите название, системное имя, а также расположение создаваемого слоя. Внимательно сопоставьте атрибуты между слоями, поставьте галочки в чекбоксах переносимых атрибутов. Во втором случае выберите Существующий слой и укажите ресурс для обновления, а также сопоставьте атрибуты. Так же как и в случае с удалением вам нужно составить SELECT запрос, который бы выбирал все нужные для переноса значения из копируемого слоя.
В данном примере мы создадим слой со всеми зданиями города. Для этого создадим два запроса на выборку. В первом запросе мы выберем все здания из слоя Здания Верхней части и создадим новый слой Здания Нижнего Новгорода. Во втором — перенесем все записи из слоя Здания Заречной части в слой Здания Нижнего Новгорода.
Первый запрос включает базовый выбор всех объектов из слоя Здания Верхней части (eromakh.nn_upper_buildings): SELECT * FROM eromakh.nn_upper_buildings. В поле название укажем Здания Нижнего Новогорода, в поле системное имя nn_all_buildings. В поле расположение папку проекта. Поставим галочки во всех чекбоксах атрибутов и не будем менять их название.
Для завершения создания слоя со всеми зданиями напишем второй запрос: SELECT * FROM eromakh.nn_lowerpart_buildings. В конфигурации запроса используем Существующий слой. В нем укажем ресурс для обновления: по нажатию на это поле откроется список всех слоев, которые на данный момент присутствуют на карте. По нажатию на кнопку с папкой вы можете открыть каталог и выбрать нужный слой для обновления из каталога. Мы откроем список слоев и выберем Здания Нижнего Новгорода, который был создан на предыдущем шаге. В списке сопоставление атрибутов оставим все галочки в чекбоксах и не будем менять названия атрибутов.
Оформление полигонального слоя
После объединения оформим результирующий слой (Здания Нижнего Новгорода). Оформлять слой мы будем по атрибуту district. Он является строковым, для него доступна классификация по уникальным значениям. Перед классификацией по уникальны значениям нужно определить количество классов. Для этого зайдем в статистику слоя, чтобы сделать это нужно открыть контекстное меню слоя и выбрать в нем Статистика. Откроется карточка слоя, аналогичная карточке объекта (добавить ссылку на справку). В окне карточки объекта нажмем на кнопку выбора атрибутов. По нажатию откроется список со всеми слоями, открытыми в карте. Поставим галочку напротив district. В карточке будет рассчитано количество уникальных значений, а также разбивка по категориям. Статистика по слою показывает, что в городе есть 8 районов с различным количеством зданий. Максимальное количество зданий в Автозаводском районе — 12006, минимальное в Московском — 4398.
Закроем карточку статистики слоя, и зная количество классов, перейдем к оформлению. Для этого откроем контекстное меню слоя и выберем в нем Оформление. В нем выберем оформление при помощи заливки. Для этого в соответствующем поле нажмем на кнопку классификации. В появившемся меню нажмем на строку Атрибут и выберем там district. В поле Алгоритм выберем метод классификации из выпадающего списка Уникальные значения и установим 8 классов. Для классификации нужно нажать на кнопку Рассчитать.
После установки параметров классификации нужно выбрать цветовую шкалу. Так как данные являются категориальными наиболее удачное решение — разноцветные шкалы. Такая шкала позволяет выбрать разные цвета для расположенных рядом объектов разных классов. Для выборки шкалы нужно нажать на кнопку с иконкой пипетки и в ней в выпадающем списке выбрать Разноцветная. После выбора шкалы нажмите на дискету для сохранения результата. После этого мы можем увидеть принадлежность зданий районам города на карте.
Запрос на обновление
Следующим этапом является обогащение данных. У нас на данный момент на карте есть два слоя с полезными данными: Здания Нижнего Новгорода и Здания с функциональными зонами. Для анализа застройки города нам необходимо перенести данные о функциональном зонировании из слоя Здания с функциональными зонами в Здания Нижнего Новгорода. Для этого нужно создать SELECT запрос, который бы нашел соответствие между системным уникальным идентификатором обновляемого слоя и столбца с данными для обновления из второго слоя.
Однако система не поддерживает создание столбца при помощи запроса. Чтобы создать столбец, в который будут копироваться данные, нужно найти этот слой в каталоге, а далее открыть ассоциированную с ним таблицу. Для этого нужно дважды нажать левой кнопкой мыши на слой в каталоге. Далее вы увидите таблицу, которая хранит атрибутивную информацию, имеющую тип Данные. Зайдите в Свойства этой таблицы и нажмите Атрибуты. Далее нажмите Добавить атрибут, введите системное имя атрибута (может содержать только символы латиницы, цифры и нижнее подчеркивание). Выберите тип данных для нового атрибута и сохраните изменения. В этом примере мы создадим строковый атрибут у слоя Здания Нижнего Новгорода и назовем его func_zone. Аналогичная информация сохранена в слое Здания с функциональными зонами под тем же названием. Совпадение названия столбцов при UPDATE запросе между исходным и обновляющим слоем является обязательным условием для обновления атрибутов.
После создания столбца, в который мы перенесем данные о функциональном зонировании, можно написать запрос для переноса данных. Мы выберем все системные уникальные идентификаторы исходного слоя, а также соответствующие им данные о функциональных зонах при помощи оператора INNER JOIN. Пользовательские идентификаторы зданий совпадают между слоями, что очень сильно упрощает задачу сопоставления. Перед написанием SQL-выражений убедитесь, что идентификаторы, по которым будет производиться сравнение, имеют одинаковый тип данных: либо оба имеют числовой тип, либо оба — строковый. Чтобы проверить это, зайдите в свойства слоя и перейдите на вкладку Атрибуты. После того, как вы убедитесь в правильности указания типов атрибутов, зайдите в окно создания SQL-подобных запросов и выберите Обновление UPDATE запрос.
В качестве ресурса для обновления укажем слой Здания Нижнего Новгорода. В поле Уникальный идентификатор укажем системный уникальный идентификатор слоя (gid). Сам запрос будет выглядеть следующим образом:
Показать SQL-запрос
SELECT
nn_all.gid, nn_fz.func_zone
FROM eromakh.nn_all_buildings AS nn_all
INNER JOIN eromakh.build_fzones AS nn_fz ON nn_fz.build_id = nn_all.build_id
После заполнения всех полей и написания запроса окно инструментов разработчика будет выглядеть следующим образом:
Итоговое оформление
Оператор INNER JOIN нужен для поиска совпадающих идентификаторов. Однако идентификаторы не всегда могут полностью совпадать между слоями. В случае если они не совпадают до конца нам нужно будет удалить несовпадающие значения из обновляемого слоя. Для этого напишем еще один запрос на удаление. На экране запросов поменяем тип запроса на Удаление DELETE и напишем следующий запрос:
Показать SQL-запрос
SELECT * FROM eromakh.nn_all_buildings
WHERE func_zone IS NULL
Теперь оформим получившийся слой со зданиями по атрибуту func_zone. Для этого откроем настройки оформления из контекстного меню слоя Здания Нижнего Новгорода. Всего у атрибута func_zone пять уникальных значений. Отменим предыдущие настройки оформления, которые мы настраивали на предыдущем этапе. Для этого нужно нажать на крестик рядом с настроенным оформлением.
Далее восстановим настройки оформления. Откроем настройки классификации и выберем атрибут func_zone в поле По атрибуту, укажем Уникальные значения в поле Алгоритм и пять классов в поле Классов.
Выбор цвета
Выбор цвета при оформлении можно сделать тремя способами: при помощи цветового поля, либо при помощи ввода координат в пространстве RGBA, либо при помощи ввода HEX-кода цвета
Далее вручную настроим цвета для каждого типа. Начнем с первого варианта значений: Зона малоэтажной застройки. На картах функциональных зон генеральных планов она обычно имеет желтый цвет. Для того, чтобы настроить цвет вручную, нажмите на цветовой квадрат. После этого откроются настройки цвета: цветовое поле, ползунки цвета и прозрачности, а также числовые значения в координатах RGBA. Для настройки цвета можно либо перемещать ползунок цвета и выбрать в цветовом поле или ввести координаты цвета в пространстве RGBA. Яркий непрозрачный желтый цвет имеет координаты 255, 255, 0, 1. Введем эти четыре значения в поле RGBA. Для применения изменений нажмите на голубую галочку. Зона среднеэтажной застройки обычно отмечается оранжевым цветом: введем координаты 255, 172, 0, 1 в поле RGBA. Зона высокоэтажной застройки обычно отмечается красным цветом, введем координаты 255, 0, 0, 1 в поле RGBA. Производственная зона обычно отмечается коричневым цветом, введем 138, 77, 0, 1 в поле RGBA. Категории, которые относятся к общественно-деловой зоне, обычно отмечаются розовым или фиолетовым цветом. Выберем цвет при помощи перемещения цветовых ползунков и цветового поля. Итоговый цвет имеет следующие координаты RGBA: 198, 0, 237, 1.
В примерах с созданием дашбордов в качестве исходных данных будет использоваться итоговый слой, полученный в этом примере. Для того, чтобы ознакомиться с примерами дашбордов в системе перейдите по ссылкам: