Создание пространственной базы данных

1. Подготовка к работе

PostgreSQL предоставляет несколько инструментов для администрирования. Основной — psql: командная строка, через которую выполняются SQL-запросы. Она предназначена для ввода команд вручную и позволяет выполнять любые операции с базой данных, включая создание, изменение и удаление данных и объектов базы.

Помимо psql, широкое распространение получил графический интерфейс pgAdmin — бесплатный и открытый инструмент для работы с базами данных PostgreSQL в наглядной форме. В pgAdmin все те же команды и запросы доступны через удобный графический интерфейс, что делает его предпочтительным для пользователей, предпочитающих визуальные средства управления. На практике мы будем использовать именно pgAdmin.

Чтобы начать работу, выполните следующие шаги:

1. Откройте pgAdmin, найдя его в меню «Пуск».
2. При первом запуске, если во вкладке Servers нет ни одной записи (см. рисунок ниже), добавьте сервер вручную.

3. Нажмите правой кнопкой мыши (ПКМ) на Servers и выберите опцию Create → Server (в некоторых версиях — Register → Server).
4. В открывшемся окне введите название сервера, например Postgres, в поле Name.

После этого настройте подключение к серверу базы данных:

1. Перейдите во вкладку Connection.
2. В поле Host name/address введите localhost, если вы работаете на локальном компьютере, либо адрес сервера, если база данных находится удалённо.
3. Поле Port оставьте по умолчанию — 5432.
4. В поле Username введите postgres, если используете стандартного администратора базы данных.
5. В поле Password укажите пароль, установленный при установке PostgreSQL.

После настройки подключения нажмите Save — сервер добавится в список. Теперь можно работать с базой данных через pgAdmin: создавать, изменять и просматривать данные, выполнять SQL-запросы.

1.1. Создание базы данных

Чтобы создать базу данных, достаточно нажать ПКМ по названию сервера в pgAdmin:

1. Выберите опцию Create → Database.

2. В открывшемся окне задайте имя базы данных, например Tests.
3. В поле Owner укажите владельца базы данных (по умолчанию — пользователь postgres, если другого владельца не назначали).

1.2. Активация расширения PostGIS

После создания базы данных необходимо активировать расширение PostGIS — изначально оно недоступно и требует ручной активации:

1. Раскройте список баз данных, нажав на имя сервера, затем выберите вашу базу (например, Tests).
2. Перейдите в раздел Extensions.

3. Нажмите ПКМ по Extensions и выберите Create → Extension.
4. В новом окне в поле Name выберите «postgis» из списка доступных расширений.
5. Нажмите Save для активации расширения.

Теперь расширение PostGIS активировано, и вы можете работать с пространственными данными, используя все его функции.

Для проверки, что расширение успешно активировано, выполните следующие шаги:

1. Выберите БД, щёлкнув по ней (она выделится голубым цветом).
2. Перейдите в меню Tools и выберите Query Tool.

3. В открывшемся окне введите SQL-запрос:

SELECT postgis_full_version();

2. Загрузка готовой БД

Чтобы загрузить готовую базу данных из архива и восстановить её в PostgreSQL через pgAdmin, выполните следующие шаги:

1. Разархивируйте файл Данные_на_практику.rar. Внутри вы найдёте файл резервной копии базы данных nyc_data.backup.
2. Создайте новую базу данных для восстановления: ПКМ на сервере, Create → Database, имя — например, nyc_data — и владелец.
3. Выполните восстановление резервной копии: ПКМ на созданной базе nyc_data → Restore.
4. В окне восстановления в поле Filename укажите путь к файлу nyc_data.backup (или найдите его кнопкой выбора файла); остальные настройки оставьте по умолчанию и нажмите Restore.
5. После завершения процесса база данных готова к использованию.

3. Описание данных

Данные практикума включают четыре шейп-файла для города Нью-Йорк и одну атрибутивную таблицу с социодемографическими переменными. Шейп-файлы загружены как таблицы PostGIS; социодемографические данные мы присоединим позже. Эти данные и их взаимосвязи важны для последующего анализа.

Для изучения структуры таблиц в pgAdmin нажмите ПКМ на выбранной таблице и выберите Properties: краткое описание свойств таблицы, включая список атрибутов, находится в разделе Columns.

3.1. Таблица nyc_census_blocks

Блок переписи населения — самая мелкая географическая единица, для которой собираются данные переписи. Все более крупные единицы (группы блоков, тракты, метрополии, округа) могут быть сформированы объединением блоков. К блокам прикреплены демографические данные. Количество записей: 38 794.

3.2. Таблица nyc_neighborhoods

Нью-Йорк имеет богатую историю районных названий и границ. Районы — социальные конструкции, не всегда совпадающие с административными границами. Количество записей: 129.

3.3. Таблица nyc_streets

Центральные линии улиц формируют транспортную сеть города. Улицы классифицированы по типам, чтобы различать, например, аллеи, магистрали и пешеходные улицы. Количество записей: 19 091.

3.4. Таблица nyc_subway_stations

Станции метро соединяют поверхностный мир с подземной сетью метро. Расположение станций определяет доступность для разных категорий населения. Количество записей: 491.

3.5. Таблица nyc_census_sociodata

Таблица содержит социоэкономические данные, собранные на уровне переписных участков. Чтобы проводить пространственный анализ, эти данные необходимо связать с географическими данными блоков или участков переписи.

4. Простые запросы на SQL

SQL, язык структурированных запросов, используется для работы с реляционными базами данных: он позволяет задавать вопросы к данным и обновлять их. Теперь, когда данные загружены в базу, используем SQL для извлечения информации.

Например, задайтесь вопросом:

SELECT name FROM nyc_neighborhoods;

Чтобы выполнить этот запрос, откройте SQL-окно запросов в pgAdmin кнопкой Query Tool и нажмите «Выполнить запрос» (зелёный треугольник). Запрос вернёт 129 результатов — все районы Нью-Йорка.

Но что произошло на самом деле? Для понимания рассмотрим четыре основных «глагола» SQL:

• SELECT — возвращает строки в ответ на запрос;
• INSERT — добавляет новые строки в таблицу;
• UPDATE — изменяет существующие строки;
• DELETE — удаляет строки из таблицы.

В рамках курса мы будем работать преимущественно с SELECT, чтобы формулировать запросы к таблицам.

4.1. Запросы SELECT

Запрос типа SELECT обычно имеет следующую структуру:

• столбцы — имена столбцов или функции, применяемые к значениям столбцов;
• таблица — источник данных: одна таблица или результат соединения нескольких;
• условие — фильтр, ограничивающий количество возвращаемых строк.

SELECT name
FROM nyc_neighborhoods
WHERE boroname = 'Brooklyn';

Иногда нужно применить функцию к результатам запроса.

SELECT char_length(name)
FROM nyc_neighborhoods
WHERE boroname = 'Brooklyn';

Часто нас интересует статистика по всем результатам, а не отдельные строки. В таких случаях используются агрегатные функции, которые обрабатывают несколько строк и возвращают одно значение.

SELECT avg(char_length(name)), stddev(char_length(name))
FROM nyc_neighborhoods
WHERE boroname = 'Brooklyn';

4.2. Группировка результатов с помощью GROUP BY

Иногда нужно агрегировать данные по группам — для этого используется оператор GROUP BY.

SELECT boroname, avg(char_length(name)), stddev(char_length(name))
FROM nyc_neighborhoods
GROUP BY boroname;

5. SQL-песочница

Все конструкции раздела 4 можно попробовать, не отходя от конспекта. В песочницу загружен фрагмент учебной базы: 15 районов из nyc_neighborhoods и 12 улиц из nyc_streets (полные таблицы — 129 и 19 091 запись, поэтому численные ответы здесь отличаются от pgAdmin). Поддерживаются SELECT, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, DISTINCT, JOIN и агрегаты count / sum / avg / min / max / stddev.

6. Задание на практику

Задания выполняются в pgAdmin на полной базе nyc_data. Таблица nyc_census_blocks была дополнена атрибутами жилья:

Определение таблицы nyc_census_blocks:

• blkid — уникальный 15-значный код блока переписи (например, «360050001009000»);
• popn_total — общее количество людей в блоке;
• popn_white — количество людей, идентифицирующих себя как «белые»;
• popn_black — количество людей, идентифицирующих себя как «темнокожие»;
• popn_nativ — количество людей, идентифицирующих себя как «коренные американцы»;
• popn_asian — количество людей, идентифицирующих себя как «азиаты»;
• popn_other — количество людей других категорий;
• hous_total — общее количество жилых единиц в блоке;
• hous_own — количество жилых единиц, принадлежащих владельцам;
• hous_rent — количество жилых единиц, занимаемых арендаторами;
• boroname — название округа Нью-Йорка;
• geom — полигональная граница блока.

Полезные агрегатные функции SQL: avg() — среднее значение, sum() — сумма значений, count() — количество записей в наборе.

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

• Чем psql отличается от pgAdmin и что мы используем на практике?
  psql — командная строка для ручного ввода SQL-команд; pgAdmin — графический интерфейс с теми же возможностями в наглядной форме. На практике используется pgAdmin: он упрощает создание баз, просмотр структуры таблиц и выполнение запросов через Query Tool.
• Перечислите параметры подключения к локальному серверу PostgreSQL.
  Host name/address — localhost (для локальной машины), Port — 5432 (по умолчанию), Username — postgres (стандартный администратор), Password — пароль, заданный при установке PostgreSQL.
• Как активировать PostGIS в новой базе данных и как проверить активацию?
  Раскрыть базу → раздел Extensions → ПКМ → Create → Extension → выбрать postgis → Save. Проверка: в Query Tool выполнить SELECT postgis_full_version(); — функция вернёт версию PostGIS и его компонентов. Расширение активируется отдельно для каждой базы.
• Назовите четыре «глагола» SQL и их назначение.
  SELECT — возвращает строки по запросу; INSERT — добавляет новые строки; UPDATE — изменяет существующие; DELETE — удаляет строки. В курсе основная работа идёт с SELECT.
• Из каких частей состоит запрос SELECT и за что отвечает каждая?
  SELECT столбцы FROM таблица WHERE условие. Столбцы — имена полей или функции над ними; таблица — источник данных (таблица или соединение таблиц); условие — фильтр строк. Для групповой статистики добавляется GROUP BY со столбцом группировки.
• Что делает агрегатная функция? Приведите примеры из практики.
  Агрегатная функция обрабатывает набор строк и возвращает одно значение: count() — число записей, sum() — сумма, avg() — среднее, stddev() — стандартное отклонение. Пример: avg(char_length(name)) по районам Бруклина даёт 11.74.
• Какие пять наборов данных входят в учебную базу nyc_data и что в них хранится?
  nyc_census_blocks (38 794 блока переписи с демографией и полигонами), nyc_neighborhoods (129 районов), nyc_streets (19 091 улица с типами), nyc_subway_stations (491 станция метро, точки), nyc_census_sociodata (социоэкономика переписных участков, без геометрии — присоединяется по tractid).

Источники

1. Introduction to PostGIS — PostGIS Workshop : [сайт]. — URL: https://postgis.net/workshops/postgis-intro/ (дата обращения: 18.08.2026).
2. PostgreSQL 15 Documentation : [сайт]. — URL: https://www.postgresql.org/docs/15/ (дата обращения: 18.08.2026).
3. pgAdmin 4 Documentation : [сайт]. — URL: https://www.pgadmin.org/docs/ (дата обращения: 18.08.2026).