Практики · занятие 7

Оператор JOIN и нормализация: демографическая карта России

О чём это занятие: Большой сквозной проект: от поиска открытых данных о населении России — через пользовательскую проекцию Альберса и загрузку в PostGIS — к соединению таблиц оператором JOIN и демографической карте 1990 года. По дороге — тренировка навыка находить нарушения нормальных форм.
Аннотация: Разминка занятия — три таблицы автосалона с нарушениями первой, второй и третьей нормальных форм: тренажёр просит опознать нарушение и показывает исправление. Затем начинается проект «Демографические показатели РФ»: где брать данные о численности населения и векторные границы субъектов, как загрузить CSV с правильной кодировкой. Знакомая проблема «Чукотки» решается новым способом — созданием пользовательской проекции Альберса из PROJ-строки. Слои загружаются в базу через менеджер БД с первичным ключом, геометрией и пространственным индексом. Перед соединением выполняется проверка соответствия имён оператором EXCEPT — приём, который сэкономит часы отладки. Оператор JOIN собирает слой «население субъектов на 1990 год», а раздел о стилях учит превращать текстовые числа в числа функцией to_real, вручную строить ступени шкалы и переносить готовый стиль между слоями копированием. Домашнее задание связывает занятие с главой 1 РГР.
Пререквизиты: Практика 6 — JOIN и менеджер БД; нормальные формы — лекция 3; проекции — лекция 4.
Материалы к занятию: Данные о численности населения и границы субъектов РФ — в подборке курса (раздел «УД → Данные» на сайте презентаций).

1. Разминка: найдите несоответствия НФ

Три таблицы — три нарушения. Для каждой сформулируйте требования нормальной формы, объясните, почему таблица им не удовлетворяет, и как её привести к норме. Тренажёр проверит ваш диагноз и покажет исправленную схему.

Тренажёр: какая нормальная форма нарушена?

Выберите диагноз — здесь появится разбор.

2. Проект: демографические показатели РФ

Прикладная задача: создание картографической анимации «Демографические показатели РФ». Откуда брать данные?

демография — открытые данные о численности населения субъектов по годам (Росстат и производные наборы); подборка курса: раздел «УД → Данные» на сайте презентаций;
векторные границы — субъекты РФ из OpenStreetMap (admin_level = 4, как в лекции 6) или из той же подборки курса.

Загрузка данных в проект: файл можно просто перебросить в окно «Слои». Проверьте таблицу атрибутов: если атрибуты на русском языке читаются, кодировка выбрана верно; иначе поменяйте её на Windows-1251 — кодировка зависит от компьютера, на котором готовился файл.

2.1. Проблема «Чукотки» и проекция Альберса

Знакомая картина из практики 9: в географической системе координат Чукотка разрывается по 180-му меридиану. В этот раз решим проблему пользовательской проекцией.

Проекция Альберса — картографическая проекция, разработанная в 1805 году немецким картографом Хайнрихом Альберсом. Она используется для изображения регионов, вытянутых в широтном направлении (с запада на восток), — идеальный случай для России.

Откройте пользовательские проекции (Установки → Пользовательские проекции → +), задайте название новой проекции.
Формат — Proj; скопируйте описание:

+proj=aea +lat_1=52 +lat_2=64 +lat_0=0 +lon_0=45 +x_0=8500000 +y_0=0
+ellps=krass +units=m +towgs84=28,-130,-95,0,0,0,0 +no_defs

После ввода нажмите «Проверить», затем сохраните и примените проекцию к проекту.

Разбор PROJ-строки +proj=aea — равновеликая коническая Альберса; +lat_1=52 +lat_2=64 — стандартные параллели; +lon_0=45 — центральный меридиан; +ellps=krass — эллипсоид Красовского с параметрами перехода +towgs84 (сравните с разбором PROJ-строк МСК в лекции 4).

2.2. Загрузка в базу

В менеджере БД присоединитесь к своей базе: выберите её из списка, раскройте, выберите схему public.
Импортируйте векторный слой границ: включите пункты первичный ключ, поле геометрии, создать пространственный индекс.
Импортируйте табличный слой численности населения: достаточно первичного ключа.

3. Проверка на имена: EXCEPT

Соединять таблицы будем по названиям субъектов — но написания в разных источниках различаются. Прежде чем строить JOIN, выясним расхождения оператором EXCEPT (разность множеств из лекции 2 в синтаксисе SQL):

-- значения из "субъекты_РФ", которых нет в "Численность_населения"
SELECT DISTINCT sr.name FROM public."субъекты_РФ" AS sr
EXCEPT
SELECT DISTINCT cn.field_2 FROM public."Численность_населения" AS cn;

-- и в обратную сторону
SELECT DISTINCT cn.field_2 FROM public."Численность_населения" AS cn
EXCEPT
SELECT DISTINCT sr.name FROM public."субъекты_РФ" AS sr;

Каждый субъект из результата — будущая «дырка» на карте: строка без пары в JOIN просто выпадет (вспомните Чувашию из главы 2 РГР). Найденные расхождения исправляются оператором UPDATE до соединения.

4. JOIN: демографическая карта 1990 года

Соединим таблицы и выведем данные только за 1990-й год:

SELECT sr.geom,
       cn.field_3 AS "Население"
FROM public."субъекты_РФ" AS sr
JOIN public."Численность_населения" AS cn
  ON sr.name = cn.field_2
WHERE cn.field_1 = '1990';

Полученный слой назовём «1990» и загрузим в проект из окна SQL (галочка «Загрузить как новый слой», столбец геометрии — geom).

4.1. Работа со стилями

ПКМ по слою → Свойства → вкладка «Стиль» → «Символизация по диапазонам значений». И сразу сюрприз: при сохранении таблицы числовой формат не был распознан и колонка осталась текстовой. Чтобы выполнить преобразование, нажмите ε (кнопку выражения) и используйте функцию to_real:

to_real("Население")

Преобразование текстовой колонки функцией to_real

Типичная ошибка Выбрать текстовую колонку в символизации по диапазонам и удивляться пустой классификации. Числа, сохранённые строками, сортируются лексикографически («1000» < «2»); безопасный путь — обернуть колонку в to_real() прямо в поле значения, а при импорте таблиц следить за типами (вспомните проверку типов в главе 1 РГР).

Создадим шкалу вручную: кнопка «+» добавляет ступень; подписи в колонке «Легенда» вбиваются самостоятельно; по окончании — «Применить».

4.2. Автоматизация: слои по годам и копирование стиля

Слой 1991 года формируется тем же запросом с заменой условия: WHERE cn.field_1 = '1991'. Поскольку для всех карт используется одна шкала, стиль можно перенести: ПКМ по слою 1990 → Стили → Копировать стиль; затем ПКМ по слою 1991 → Стили → Вставить стиль.

Заметьте: при выбранной шкале изменения между соседними годами малозаметны — как превратить серию слоёв в наглядную анимацию изменений, разберёмся в практике 8.

5. Домашнее задание

Домашнее задание этого занятия — часть 3 главы 1 расчётно-графической работы: загрузить свои таблицы населения в базу, связать их со слоем районов по OSM_ID через вкладку «Связи», настроить шкалу, построить картодиаграммы долей городского и сельского населения и собрать макет с гербом региона. Полные методические указания и чек-лист отчёта — на странице главы 1 РГР (часть 3).

Контрольные вопросы

Первая: в ячейке «Модель» несколько значений, атрибуты не атомарны. Исправление — по строке на каждую модель: BMW–X5, BMW–318, BMW–X6, BMW–M2 Turbo, Porsche–Cayenne. 1НФ требует исключительно скалярных значений на пересечении строки и столбца.
Нарушена 2НФ: скидка зависит не от модели (ключа), а от производителя — неключевого атрибута, то есть от части данных. Исправление — вынести скидку в таблицу «Производитель — Скидка» (BMW — 15 %, Porsche — 25 %), в основной таблице оставить модель, производителя и стоимость.
В географической СК Чукотка разрывается по 180-му меридиану; коническая равновеликая проекция Альберса подходит для территорий, вытянутых с запада на восток. Ключевые параметры: +proj=aea, стандартные параллели lat_1=52 и lat_2=64, центральный меридиан lon_0=45, эллипсоид Красовского (+ellps=krass) с параметрами перехода +towgs84.
EXCEPT возвращает разность множеств: значения первого запроса, отсутствующие во втором (реляционное вычитание из лекции 2). Запуск в обе стороны до JOIN показывает, какие субъекты не найдут пару из-за различий в написании имён — их нужно исправить UPDATE-ом, иначе они молча выпадут из результата.
Числовой формат не распознался при сохранении, и колонка осталась текстовой: строки сортируются лексикографически, диапазоны не строятся. В поле значения символизации вводится выражение to_real("Население") — функция QGIS преобразует строку в число.
Настроить шкалу один раз на слое 1990 года (при необходимости — вручную, со ступенями через «+» и подписями легенды), затем ПКМ → Стили → Копировать стиль и вставить его в каждый следующий слой (Стили → Вставить стиль). Одинаковая шкала обязательна для честного сравнения годов.

Источники

PostgreSQL 15 Documentation. Combining Queries (UNION, INTERSECT, EXCEPT) : [сайт]. — URL: https://www.postgresql.org/docs/15/queries-union.html (дата обращения: 18.08.2026).
PROJ Documentation. Albers Equal Area : [сайт]. — URL: https://proj.org/en/stable/operations/projections/aea.html (дата обращения: 18.08.2026).
Описание основ нормализации базы данных — Microsoft Learn : [сайт]. — URL: https://learn.microsoft.com/ru-ru/office/troubleshoot/access/database-normalization-description (дата обращения: 18.08.2026).