Перейти к содержанию

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

Материалы к практическому занятию#

Скачайте Данные_на_практику.rar.
В директории Student создайте папку Группа_ФИО. Разархивируйте скаченные материалы

Презентация к практике

Подготовка к работе#

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

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

На практике мы будем использовать pgAdmin, так как он упрощает работу с базами данных за счёт удобного интерфейса и визуализации. Чтобы начать работу, выполните следующие шаги:

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

https://ratcatcher.ru/media/bd/pr/pr3/Server.png

  1. Для этого нажмите правой кнопкой мыши (ПКМ) на Servers и выберите опцию Create -> Server (в некоторых версиях это может быть Register -> Server).
  2. В открывшемся окне введите название сервера, например, Postgres, в поле Name.

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

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

https://ratcatcher.ru/media/bd/pr/pr3/Server_p.png

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

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

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

https://ratcatcher.ru/media/bd/pr/pr3/db1.png

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

https://ratcatcher.ru/media/bd/pr/pr3/db2.png

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

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

https://ratcatcher.ru/media/bd/pr/pr3/db3.png

  1. Нажмите правой кнопкой мыши (ПКМ) по Extensions и выберите Create -> Extension.
  2. В новом окне в поле Name выберите «PostGIS» из списка доступных расширений.
  3. Нажмите Save для активации расширения.

https://ratcatcher.ru/media/bd/pr/pr3/db4.png

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

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

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

https://ratcatcher.ru/media/bd/pr/pr3/ext1.png

  1. В открывшемся окне введите следующий SQL-запрос:
SELECT postgis_full_version();

https://ratcatcher.ru/media/bd/pr/pr3/ext2.png

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

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

  1. Разархивируйте файл Данные_на_практику.rar. Внутри вы найдете файл резервной копии базы данных nyc_data.backup.

  2. Создайте новую базу данных для восстановления данных. Для этого нажмите правой кнопкой мыши (ПКМ) на сервере, выберите Create -> Database, дайте имя базе данных, например, nyc_data, и укажите владельца.

  3. После создания базы данных выполните восстановление резервной копии:

  4. Нажмите ПКМ на созданной базе данных nyc_data.

  5. Выберите Restore из выпадающего меню.

  6. В окне восстановления:

  7. В поле Filename укажите путь к файлу nyc_data.backup (или нажмите на кнопку выбора файла и найдите его).

  8. Оставьте остальные настройки по умолчанию и нажмите Restore.

  9. Процесс восстановления начнётся. После его завершения база данных будет готова к использованию, и вы сможете работать с данными, которые были загружены.

Описание данных#

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

Ниже описаны количество записей и атрибуты каждой из наших таблиц. Эти данные и их взаимосвязи важны для последующего анализа.

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

Таблица nyc_census_blocks#

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

https://ratcatcher.ru/media/bd/pr/pr3/nyc_census_blocks.png

Количество записей: 38 794

Атрибут Описание
blkid Уникальный 15-значный код блока переписи (например: 360050001009000)
popn_total Общее количество людей в блоке переписи
popn_white Количество людей, идентифицирующих себя как "Белые"
popn_black Количество людей, идентифицирующих себя как "Темнокожие"
popn_nativ Количество людей, идентифицирующих себя как "Коренные американцы"
popn_asian Количество людей, идентифицирующих себя как "Азиаты"
popn_other Количество людей, относящихся к другим категориям
boroname Название района Нью-Йорка (Манхэттен, Бронкс, Бруклин, Статен-Айленд, Куинс)
geom Полигональная граница блока

Таблица nyc_neighborhoods#

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

Количество записей: 129

https://ratcatcher.ru/media/bd/pr/pr3/nyc_neighborhoods.png

Атрибут Описание
name Название района
boroname Название района Нью-Йорка
geom Полигональная граница района

Таблица nyc_streets#

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

Количество записей: 19 091

Атрибуты:

name — название улицы.
oneway — является ли улица односторонней («yes» = да, «» = нет).
type — тип дороги (primary, secondary, residential, motorway).
geom — центральная линия улицы.

https://ratcatcher.ru/media/bd/pr/pr3/nyc_streets.png

Атрибут Описание
name Название улицы
oneway Является ли улица односторонней («yes» = да, «» = нет)
type Тип дороги (primary, secondary, residential, motorway)
geom Центральная линия улицы

Таблица nyc_subway_stations#

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

Количество записей: 491

https://ratcatcher.ru/media/bd/pr/pr3/nyc_subway_stations.png

Атрибут Описание
name Название станции
borough Название района Нью-Йорка
routes Линии метро, проходящие через станцию
transfers Линии, на которые можно пересесть на этой станции
express Является ли станция остановкой для экспресс-поездов («express» = да, «» = нет)
geom Точечное местоположение станции

Таблица nyc_census_sociodata#

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

Атрибут Описание
tractid Уникальный 11-значный код переписного участка
transit_total Общее количество работников в участке
transit_private Количество работников, использующих частный транспорт
transit_public Количество работников, использующих общественный транспорт
transit_walk Количество работников, ходящих пешком
transit_other Количество работников, использующих другие виды транспорта
transit_none Количество работников, работающих из дома
transit_time_mins Общее количество минут, проведенных в пути
family_count Количество семей в участке
family_income_median Медианный доход семьи
family_income_mean Средний доход семьи
edu_total Общее количество людей с образовательной историей
edu_no_highschool_dipl Количество людей без диплома средней школы
edu_highschool_dipl Количество людей с дипломом средней школы
edu_college_dipl Количество людей с дипломом колледжа
edu_graduate_dipl Количество людей с дипломом магистра

Простые запрос на sql#

SQL, или язык структурированных запросов, используется для работы с реляционными базами данных, позволяя задавать вопросы к данным и обновлять их. Мы уже видели SQL в действии на прошлых практиках.

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

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

Как вывести названия всех районой Нью-Йорка?

Чтобы выполнить этот запрос, откройте SQL-окно запросов в pgAdmin, нажав кнопку Query Tool.

Tip 
SELECT name FROM nyc_neighborhoods;

Нажмите кнопку "Выполнить запрос" (зелёный треугольник). Этот запрос вернёт 129 результатов, представляющих все районы Нью-Йорка.

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

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

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

Запросы SELECT#

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

SELECT столбцы FROM таблица WHERE условие;

Где:

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

Рассмотрим другой пример:

"Какие районы входят в состав Бруклина?"

Для этого используем запрос с фильтром:

Tip 
SELECT name
FROM nyc_neighborhoods
WHERE boroname = 'Brooklyn';

Этот запрос вернёт 23 результата.

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

"Сколько букв в названиях всех районов в Бруклине?"

К счастью, PostgreSQL имеет встроенную функцию для работы с длиной строк, char_length(string):

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

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

"Какова средняя длина названий районов в Бруклине и стандартное отклонение?"

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

Результат будет выглядеть так:

avg stddev
11.74 3.91

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

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

"Какова средняя длина названий районов в Нью-Йорке, распределённая по районам?"

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

Результат покажет средние значения и стандартные отклонения по каждому району:

boroname avg stddev
Brooklyn 11.74 3.91
Manhattan 11.82 4.31
The Bronx 12.04 3.67
Queens 11.67 5.01
Staten Island 12.29 5.20

Задание на практику#

https://ratcatcher.ru/media/bd/pr/pr3/data.png

Таблица 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(): среднее значение (mean) в наборе записей.
- sum(): сумма значений в наборе записей.
- count(): количество записей в наборе записей.

Сколько записей в таблице nyc_streets?

Ответ: 19091

Сколько улиц в Нью-Йорке начинается на букву 'B'?

Ответ: 1282

Каково население города Нью-Йорка?

Ответ: 8175032

Каково население Бронкса?

Ответ: 1385108

Сколько «районов» в каждом округе?

Результат:

boroname count
Queens 30
Brooklyn 23
Staten Island 24
The Bronx 24
Manhattan 28

Каков процент белого населения в каждом районе?

Результат:

boroname white_pct
Brooklyn 42.80%
Manhattan 57.45%
The Bronx 27.90%
Queens 39.72%
Staten Island 72.89%