Глава 1. Создание пространственной БД#
Часть 1#
Методические указания (как делать)#
1 Выберите уникальный в пределах вашей группы регион России (за исключением городов федерального значения)
2 Зайдите на сайт Росстата и найдите Итоги Всероссийской переписи населения 2020 года
Например для Нижегородской области:
https://52.rosstat.gov.ru/folder/62175#
Где 52 это номер региона.
3 Скачайте Таблицу «Численность населения городских округов, муниципальных районов, муниципальных округов, городских и сельских поселений, городских населенных пунктов, сельских населенных пунктов с населением 3000 человек и более»
(Номер таблицы может отличаться! Формат csv, xsls, xls)
Пример данных переписи населения для Белгородской области (В отличие от Нижегородской пункт №4. Название совпадает)
4 Откройте скаченный файл и отредактируйте его следующим образом:
- Удалите «Шапку»
- Удалите строку 4, где указано «А 1 2 3 4 5)
- Удалите колонку «В общей численности населения, процентов)
- Добавьте название первой колонке
Question
Ответьте на вопрос: Почему не обязательно хранить данные из удаленной колонки?
Как это связано с процессом нормализации данных Лекция 3?
5 Полученный файл открой в QGIS (без геометрии)
6 Проверьте таблицу атрибутов
- Переименуйте слой в Rosstat
Если при открытии файла csv у вас вместо названия регинов стоят "нечитаемые" символы, то воспользуйтесь следующей инструкцией:
7 Экспортируйте таблицу (слой) в формате PostgreSQL SQL Дамп
При экспорте проверить корректность типов данных (Люди измеряются в целых числах! Если вы видите иной тип данных проверьте таблицу! Вернитесь к пункту 6!)
8 При экспорте в поле геометрия «Тип геометрии» изменить на "Без геометрии"
9 Создайте БД с именем «ВАШЕФИО_ОБЛ». Например «LebedevED_Nizn».
10 Импортируйте SQL-дамп (см. гиф)
- Откройте pgdmin
- Откройте панель "Query tool"
- Перенесите файл в панель и выполните код.
11 Отобразите содержимое Таблицы Rostat
12 Посчитайте сколько у вас NULL значений
13 Удалите все строки содержащие NULL значения одновременно в трех колонках
14 Выведите таблицу после удаления
Состав отчета Часть 1#
- Дайте краткую справку региону, который вы выбрали
- Опишите данные РОССТАТА, которые вы берете. Приложите ссылку на сайт росстата региона
- Покажите исходную таблицу в excel. (Назовите рис. 1 Исходные данные)
- Опишите процесс нормализации таблицы (Дополните рис. 2 Нормализованная таблица)
- Опишите какая таблица была создана в Postgres. Приведите для создания таблицы и первые 5 вставленных строчек командой INSERT INTO. Назовите эту «Листинг 1. Создание и наполнение таблицы {Ваше название таблицы}» Соблюдайте цветовую разметку!)
Tip
Пример Листинг 1. Создание и наполнение таблицы {Ваше название таблицы}
CREATE TABLE "public"."rosstat"();
ALTER TABLE "public"."rosstat"
ADD COLUMN "ogc_fid" SERIAL CONSTRAINT "rosstat_pk" PRIMARY KEY;
ALTER TABLE "public"."rosstat" ADD COLUMN "Название области" VARCHAR;
ALTER TABLE "public"."rosstat" ADD COLUMN "Мужчины и женщины" NUMERIC(10,0);
ALTER TABLE "public"."rosstat« ADD COLUMN "Мужчины" NUMERIC(10,0);
ALTER TABLE "public"."rosstat" ADD COLUMN "Женщины" NUMERIC(10,0);
INSERT INTO "public"."rosstat" ("Название области", "Мужчины и женщины", "Мужчины", "Женщины") VALUES ('Нижегородская область', 3119115, 1411929, 1707186);
6 Покажите запрос и таблицу в Pg admin (Листинг 2. Запрос для вывода таблицы Рис. 3 «Таблица с данными в PostgreSQL» )
7 Укажите сколько Null значений существует в таблице. (Листинг 3. Запрос для вывода Null –значений)
8 Удалите все строки, содержащие Null в трех и более столбцах (Листинг 4. Запрос для удаления пустых строк)
9 Рис. 4 Таблица после выполнения запроса
!!! tip Пример Листинг 4. Запрос для удаления пустых строк
```sql
DELETE FROM your_table_name
WHERE "Мужчины и женщины" IS NULL AND "Мужчины" IS NULL AND
"Женщины" IS NULL;
```
Отчет присылается в формате doc, docx или pdf.
Примечание: Следите, чтобы названия колонок были в двойных кавычках (""), а текст в одинарных ('')! Иначе запрос не будет выполняться!
Часть 2#
Методические указания (как делать)#
1 Установите плагин “QuickOSM” для QGIS для загрузки векторных данных (Модули -> Управление модулями -> Поиск QuickOSM)
Примечание: QuickOSM позволяет выгружать любые данные из OpenStreetMap сразу в проект без ограничений для размера территории выгрузки.
2 После установки найдите новый инструмент в разделе Вектор -> QuickOSM –> QuickOSM
3 OSM предполагает запросы с использованием атрибутов.
Выберем атрибут admin level = 6 в границах выбранного вами субъекта РФ
Примечание 1: Атрибут admin_level описывает границы на разных административных уровнях — от страны до города.Имеет значения от 2 до 10, где 2 границы страны, 10 – городских поселений
Примечание 2: Если после нажатия на «Выполнить запрос» ничего не произошло проверьте правильность правописания субъекта РФ!
4 Полученные вами данные загружены как «Временные слои». Сохраните их (ПКМ по слою -> сохранить на диск -> *.shp (или любой иной формат))
5 Откройте таблицу атрибутов векторного полигонального слоя.
6 Нажмите 
для редактирования
7 Нажмите 
для удаления лишних полей
Примечание:Вам необходимо оставить full_id , osm_id, name, official_name остальные поля удалить.
8 Сохраните изменения закончив редактирования (нажмите и сохраните)
9 Настройте проекцию проекта для этого выберите подходящую зону Гаусс-Крюгера. Например,дял Нижегородской области EPSG:20008 - Pulkovo 1995 / Gauss-Kruger zone 8
10 Проведите операции над линейным и точечным слоем.
11 Переименуйте слои как «Границы ИМЯ_СУБЪЕКТА_РФ», «Районы ИМЯ_СУБЪЕКТА_РФ» и «Административные центры ИМЯ_СУБЪЕКТА_РФ»
12 Экспортируйте все данные в формате SQL DUMP
13 Загрузите данные подобно прошлому заданию (пункт 10. часть 1)
14 Выполните запрос использовав JOIN для соединения таблицы ROSSTAT И Районы ИМЯ_СУБЪЕКТА_РФ
Tip
SELECT *
FROM ROSSTAT
JOIN «Районы ИМЯ_СУБЪЕКТА_РФ»
ON ROSSTAT.name = "Районы ИМЯ_СУБЪЕКТА_РФ».name"
Примечание: Следите, чтобы названия колонок были в двойных кавычках (""), а текст в одинарных ('')! Иначе запрос не будет выполняться!
15 Визуализируйте результат.
(Оранжевый результат запроса, бежевый – все районы)
Question
Почему не получилось «соединить» все данные?
16 Откройте ранее скаченный файл Росстата и сформируйте новую таблицу на их основе
- Выберите только районы из списка «Районы ИМЯ_СУБЪЕКТА_РФ»
- Обратите внимание, что в файле Росстата имена могут отличаться. Скорректируйте их
- Городское и сельское население вынесите в отдельные таблицы
- Для районов используйте OSM_ID
Пример:
17 Полученные таблицы сохрание в формате .csv (.xlsx, .xls)
Состав отчета Часть 2#
Примечание: Часть 2 продолжается в том же файле после части 1.
- Напишите характеристику порталу OSM. Опишите какие данные там можно получить
- Укажите параметры запросы для QuickOSM. Добавьте таблицу с описанием значений admin_level. Добавьте Рис. 5 Результаты запросы
- Добавьте запрос для вывода векторного слоя районов субъекта РФ (Листинг 5). Покажите результат запроса.
- Объясните операцию JOIN. Укажите по какому атрибуту производится соединение и почему? Добавьте запрос в виде Листинга 6. Добавьте визуализацию всех районов субъекта РФ и результатов соединения
- Объясните почему возникла проблема. Опишите принципы нормализации ко 2НФ. Таблицы «Общее население», «Городское население» и «Сельское население» Вставьте в конец файла и назоваите «Приложение А.1», «Приложение А.2», «Приложение А.3» соответственно
Отчет присылается в формате doc, docx или pdf.
Часть 3#
Методические указания (как делать)#
1 Загрузите *.csv с населением по вашему субъекту РФ в базу данных (они были сделаны в конце прошлой части)
2 Для загрузки новых таблиц вы можете пользоваться «Менеджером БД»
- Базы данных -> Менеджер БД
- Раскройте вашу БД -> из списка выберите public (одним щелчком) -> затем нажимаете Импорт слоя/файла
- Выбираете параметра импорта (какой слой, в какую Базу данных, как назвать таблицу)
Примечание: Чтобы QGIS понял в какую БД импортировать данные в должны выбрать БД из спиcка
(Раскройте POSTGIS -> раскройте выбранную БД -> выберите public [он загорится синим цветом])
3 Отобразите полученные таблицы при помощи запроса SELECT и выведите как слои QGIS
4 Чтобы соединить таблицу с векторным слоем нажмите ПКМ по Районы ИМЯ_СУБЪЕКТА_РФ
- Выберите вкладку «Связи». Нажмите на +. Установите связь между слоем и таблицей по атрибуту OSM_ID
Примечание: Вкладка Связь работает аналогично оператору JOIN. Только в данном случае вместо ручного запроса вы используете графический интерфейс
5 В поле «Присоединяемые поля» выберите все поля, кроме OSM_ID
6 Повторите операцию для всех 3 таблиц (Таблица Общее население, Таблица Городское население, Таблица Сельское населения)
7 Измените стиль отображения слоя, подобрав шкалу.
- Выведите сформированный слой при помощи оператора JOIN.
- Нажмите ПКМ по слою и перейдите в раздел свойства
- Выберите вкладку стиль
8 Измените стиль отображения с «Простой символики» на «Символизация по диапазонам значений»
9 Настройте карту для отображение общего состава населения
10 Шкалу и количество классов настройте по своему усмотрению, исходя из правил картографического отображения
11 Перейдите во вкладку «Диаграммы» (ПКМ по слою -> свойства -> Диаграммы)
- Выберите «Круговая диаграмма».
- Нажмите на ε и вычислите долю городского населения (поделите общее население на городское * 100)
- Нажмите на ε и вычислите долю сельского населения (поделите общее население на городское * 100)
- Подберите контрастные цвета для отображения
12 При необходимости настройте Размер круговых диаграмм (никакая диаграмма не должна выходить за пределы границ районов)
Пример Фрагмент карты Нижегородской области
13 Проведите компоновку карты
14 Проект -> Менеджер макетов ->создать ->Область Фамилия
15 Добавьте карту на Холст (холст направьте горизонтально или вертикально в зависимости от протяженности области)
16 Добавьте рамку
17 Добавьте герб региона. Добавьте масштаб
18 Итоговый макет экспортируйте в изображение
Состав отчета Часть 3#
Примечание: Часть 3 продолжается в том же файле после части 2.
1 Опишите все атрибуты внутри ваших таблиц в формате:
Таблица "Рейсы"
Код рейса (первичный ключ)
Номер рейса (Числовой, длинное целое)
Аэропорт вылета (Текстовый, 20)
Аэропорт назначения (Внешний ключ, ссылается на таблицу "Аэропорты назначения")
Продолжительность полета (Временной)
Цена билета (Денежный, в рублях)
2 В виде схемы отобразите соединения между имеющимися таблицами (можете воспользоваться сервисом https://dbdiagram.io/d). Представьте в виде рис. 6
3 Покажите таблицу атрибутов слоя Районы ИМЯ_СУБЪЕКТА_РФ после соединения
4 Из документации QGIS выпишите, чем отличаются алгоритмы формирования шкал. Посмотрите, какой из алгоритмов и сколькими классами вам подойдет. Полученную шкалу «округлите». Например,
Ступень от 999 до 1793 целесобразно для лучшего восприятия превратить в «от 1000 до 1800» (или от 1000 до 2000)
5 Дайте рис. 7, рис. 8 и рис.9 для иллюстрации разных параметров автоматической генерации
6 Приведите итоговую шкалу и карту на рис. 10
7 Полученную карту проанализируйте (есть ли закономерности в распределении населения?)
Опишите результаты пространственного анализа.
8 Добавьте рис. 11 (карту с картодиаграммами соотношения городского населения) Проведите пространственный анализ посредствам визуального изучения.
9 Сделайте запрос к БД, который сформирует таблицу с долями городского и сельского населения по административным районам. Добавьте эту таблицу в приложение А.4
10 Рис. 12 Добавьте итоговый макет. Укажите масштаб.
Отчет присылается в формате doc, docx или pdf.