Данные это один из наиболее важных компонентов
геопространственных технологий и, пожалуй, любой другой отрасли. К
управлению данными сейчас относятся серьезно во всех отраслях,
поэтому знания по этой дисциплине имеют важное значение для карьеры
ИТ-специалистов. Этот цикл статей задуман как универсальное
руководство, в котором мы рассмотрим тему от и до, начиная с
вопроса Что такое данные? и заканчивая изучением и применением
геопространственных запросов.
Основные понятия баз данных
Что такое данные?
Данные могут представлять собой любую информацию, которая
сохраняется с целью обращения к ней в будущем. Эта информация может
включать числа, текст, аудио- и видеоматериалы, местонахождение,
даты и т.д. Она может быть записана на бумаге либо сохранена на
жестком диске компьютера или даже в облаке.
Что такое база данных?
Множество записей данных, собранных вместе, образуют базу
данных. Базы данных обычно создаются для того, чтобы пользователи
могли обращаться к большому количеству данных и массово выполнять с
ними определенные операции.База данных может хранить что угодно:
представьте себе, например, блокнот вашей бабушки со всеми ее
вкусными рецептами, учетную книгу ваших родителей, куда они
записывают все доходы и расходы, или свою страницу в Facebook со
списком всех ваших друзей. Из этих примеров видно, что все данные в
базе данных относятся более-менее к одному типу.
Зачем нужна база данных?
Создание базы данных упрощает разным пользователям доступ к
наборам информации. Приведенные выше примеры показывают, что в базе
данных мы можем хранить записи с информацией похожего типа, но это
правда лишь отчасти, поскольку с появлением баз данных NoSQL это
определение меняется (подробнее читайте далее в статье).Так как
размер веб-сайтов становится все больше и степень их
интерактивности все выше, данные о пользователях, клиентах, заказах
и т.д. становятся важными активами компаний, которые испытывают
потребность в надежной и масштабируемой базе данных и инженерах,
способных в ней разобраться.
Система управления базами данных (СУБД)
Итак, мы уже знаем, что данные и базы данных важны, но как
осуществляется работа с базами данных в компьютерных системах? Вот
тут на сцену и выходит СУБД. СУБД это программное обеспечение,
предоставляющее нам способ взаимодействия с базами данных на
компьютере для выполнения различных операций, таких как создание,
редактирование, вставка данных и т.д. Для этого СУБД предоставляет
нам соответствующие API. Редко какие программы не используют СУБД
для работы с данными, хранящимися на диске.Помимо операций с
данными СУБД также берет на себя резервное копирование, проверку
допуска, проверку состояния базы данных и т.д. Поэтому
рекомендуется всегда использовать СУБД при работе с базами
данных.
Пространственные данные и база данных
Особое внимание мы уделим обработке пространственных данных,
поэтому я хотел бы обсудить здесь этот тип данных. Пространственные
данные несколько отличаются от остальных. Координаты необходимо
сохранять в особом формате, который обычно указан в документации на
веб-сайте о базе данных. Этот формат позволяет базе считывать и
правильно воспринимать координаты. Если обычно для поиска данных мы
используем запросы типа Получить все результаты, где
возраст>15, то пространственный запрос выглядит как-то так:
Получить все результаты в радиусе 10км от определенной
точки.Поэтому пространственные данные необходимо хранить в
надлежащем формате.
Типы баз данных
Базы данных обычно делятся на два типа:
реляционные и нереляционные. Оба
типа имеют свои плюсы и минусы. Было бы глупо утверждать, что один
лучше другого, поскольку это будет зависеть от варианта
использования. Конкретно для пространственных данных я в 99%
случаев использую реляционные базы данных, и вы скоро поймете
почему.
Реляционные базы данных и РСУБД
Допустим, ваш начальник просит вас создать электронную таблицу с
важной информацией, включающей имена, местонахождения, адреса
электронной почты, номера телефонов и должности всех сотрудников.
Вы сразу же откроете таблицу Excel или Google Spreadsheets,
напишете все эти названия столбцов и начнете собирать
информацию.
Образец
таблицы с информацией
Закономерность здесь заключается в том, что каждая запись
содержит ограниченный и фиксированный набор полей, которые нам
нужно заполнить. Таким образом мы создали таблицу
со всей информацией, где у каждой записи имеется уникальный
первичный ключ, который определяет ее однозначным
образом и делает ее доступной для всех операций. В реляционных
базах данных любая таблица содержит фиксированное
количество столбцов, и можно устанавливать связи между разными
столбцами.
Связь между
двумя столбцами
Взаимосвязи в реляционных базах данных мы подробно рассмотрим
позже.
По сравнению с базами данных NoSQL, недостатком реляционных баз
данных является относительно медленное получение результатов, когда
количество данных стремительно увеличивается (по мнению автора
статьи прим. пер.). Еще один недостаток заключается в том, что
при добавлении каждой записи нужно следовать определенным правилам
(типы столбцов, количество столбцов и т.д.), мы не можем просто
добавить отдельный столбец только для одной записи.В реляционных
базах данных используется SQL(Structured
Query Language язык структурированных запросов), с помощью которого
пользователи могут взаимодействовать с данными, хранящимися в
таблицах. SQL стал одним из наиболее широко используемых языков для
этой цели. Мы подробнее поговорим об SQL чуть позже.Вот примеры
некоторых известных и часто используемых реляционных баз данных:
PostgreSQL,
MySQL,
MSSQL и т.д. У каждой крупной компании, занимающейся
реляционными базами данных, есть собственная версия SQL. В
большинстве аспектов они выглядят одинаково, но иногда требуется
немного изменить какой-нибудь запрос, чтобы получить те же
результаты в другой базе данных (например, при переходе из
PostgreSQL в MySQL).
Нереляционные базы данных (NoSQL)
Все базы данных, не являющиеся реляционными, относятся к
категории нереляционных баз данных. Обычно данные хранятся в
нетабличном формате, например:
-
Пара ключ-значение
-
Формат JSON, XML
-
Графовый формат
Основное преимущество баз данных NoSQL состоит в том, что все
строки независимы и могут иметь разные столбцы. Как показано на
изображении ниже, оба пользователя относятся к одной и той же
таблице Core_user, но их записи содержат разную
информацию.
База данных NoSQL реального времени в Google Firebase
База данных NoSQL реального времени в Google Firebase
При использовании баз данных NoSQL пользователям иногда
приходится прописывать собственную логику, чтобы добавить
уникальный ключ к каждой записи и тем самым обеспечить доступ к
записям. В большинстве стандартных баз данных NoSQL, таких как
Firebase и MongoDB,
для хранения данных используется формат JSON. Благодаря этому очень
легко и удобно выполнять операции с данными из веб-приложений,
используя JavaScript, Python, Ruby и т.д.
Рекомендации по выбору типа
базы для хранения пространственных данных
Очевидно, что нам хотелось бы сохранить точку, линию,
многоугольник, растры и т.д. так, чтобы это имело смысл, вместо
того чтобы сохранять просто координаты. Нам нужна СУБД, которая
позволяет не только сохранять данные, но и запрашивать их
пространственными методами (буфер, пересечение, вычисление
расстояния и т.д.). На сегодняшний день для этого лучше всего
подходят реляционные базы данных, поскольку в SQL есть функции,
помогающие выполнять подобные операции. Использование таких
дополнительных средств, как PostGIS для
PostgreSQL, открывает разработчикам возможности для написания
сложных пространственных запросов. С другой стороны, NoSQL тоже
работает в области геопространственных технологий: например,
MongoDB предоставляет
кое-какие функции для выполнения геопространственных
операций. Однако реляционные базы данных все же лидируют на рынке с
большим отрывом.
Работа с РСУБД
Основное внимание мы уделим РСУБД, так как именно эти системы в
большинстве случаев мы будем использовать для хранения
пространственных данных и работы с ними. В качестве примера мы
будем использовать PostgreSQL, поскольку это самая
перспективная реляционная база данных с открытым исходным
кодом, а ее расширение PostGIS
позволяет работать и с пространственными данными. Вы можете
установить PostgreSQL, следуя инструкциям из документации.
Помимо PostgreSQL рекомендуется также загрузить и установить
pgAdmin.
Платформа pgAdmin предоставляет веб-интерфейс для взаимодействия с
базой данных. Также для этого можно загрузить и установить
какое-либо другое совместимое ПО или использовать командную
строку.
pgAdmin4 на Mac
Пользователи могут изменять множество настроек для баз данных,
включая порт, имя пользователя, пароль, доступность извне,
выделение памяти и т.д., но это уже другая тема. В этой статье мы
сосредоточимся на работе с данными, находящимися в базе.
Создание базы данных. Нам нужно создать базу
данных (в идеале должно быть по одной базе данных для каждого
проекта).
Создание новой базы данных для проекта
В инструменте запросов (Query Tool) база данных создается
следующим образом:
CREATE DATABASE <database_name>
Создание таблиц. Создание таблицы требует
некоторых дополнительных соображений, поскольку именно здесь нам
нужно определить все столбцы и типы данных в них. Все типы данных,
которые можно использовать в PostgreSQL, вы найдете
здесь.
pgAdmin
позволяет нам выбрать в таблице различные
ключи и ограничения, например Not Null
(запрет на
отсутствующие значения), Primary Key
(первичный ключ)
и т.д. Обсудим это подробнее чуть позже.
Создание
таблицы пользователей
Заметьте, что мы не добавляли столбец первичного идентификатора
в список столбцов, поскольку PostgreSQL делает это автоматически.
Мы можем создать сколько угодно таблиц в одной базе данных. После
того как таблицы созданы, мы можем установить связи между разными
таблицами, используя определенные столбцы (обычно столбцы с
идентификаторами).В инструменте запросов таблица создается
следующим образом:
CREATE TABLE <table_name> (<column_1> <datatype>,<column_2> <datatype>,.....<column_n> <datatype>PRIMARY KEY (<column>));
CRUD-операции с данными в таблицах
CRUD-операции (создание, чтение, обновление и удаление Create,
Retrieve, Update, Delete) это своего рода hello world в мире СУБД.
Поскольку эти операции используются наиболее часто, команды для их
выполнения одинаковы во всех РСУБД. Мы будем писать и выполнять
запросы в инструменте запросов в pgAdmin, который вызывается
следующим образом:
Инструмент запросов (Query Tool) в pgAdmin
1. Создание новой записи
Для добавления новой записи в таблицу используйте следующую
команду:
INSERT INTO <tablename> (column1, column2, column3,...) VALUES (value1, value2, value3,...);
INSERT, INTO, VALUE являются ключевыми словами
в SQL, поэтому их нельзя использовать в качестве переменных,
значений и т.д. Чтобы добавить новую запись в нашу таблицу
пользователей, мы напишем в инструменте запросов следующий
запрос:
INSERT INTO users(name, employed, address) VALUES ('Sheldon Cooper', true, 'Pasadena');
Обратите внимание: строки всегда следует заключать в''
(одинарные кавычки), а не в"" (двойные
кавычки).
2. Получение записей (всех или
нескольких)
Данные, хранящиеся в базе данных, можно извлечь и отобразить на
экране. При этом мы можем получить все данные или ограниченное
количество записей. Код для получения данных:
select <column1, column2 ,...> from <tablename>
Этот код извлекает весь набор данных. Если вы хотите получить
только 20записей, напишите:
select <column1, column2 ,...> from <tablename> limit 20
Если вы хотите получить данные из всех столбцов, то вместо
перечисления названий всех столбцов можно написать:
select * from <tablename>
Если вы хотите получить результат с определенным условием,
используйте ключевое слово WHERE, как показано
ниже:
select * from <tablename> where <key> = <value>
Вы можете создавать даже сложные запросы, о которых мы поговорим
позже.В нашем примере мы можем получить нужные нам данные:
--Retrieving Specific columns for all usersselect name,employed from users--Retrieving all columns for all usersselect * from users--Retrieving all columns for first 3 usersselect * from users limit 3--Retrieving all columns for all users where employed = trueselect * from users where employed = true
3. Обновление записей (всех или
нескольких)РСУБД позволяет нам обновить все или только
некоторые записи данных, указав новые значения для столбцов.
UPDATE <tablename> SET <column1> = <value1>, <column2> = <value2>
Если вы хотите обновить определенные строки, добавьте условия с
использованием ключевого слова WHERE:
UPDATE <tablename> SET <column1> = <value1>, <column2> = <value2>WHERE <column> = <value>
В нашем случае мы обновим таблицы с помощью следующих
запросов:
-- Make all rows as employed = trueupdate users set employed = true-- change employed = false for entries with address = 'nebraska'update users set employed = false where address = 'nebraska'
Обновление записей
4. Удаление записей (всех или
нескольких)Удалять записи в SQL легко. Пользователь может
удалить либо все строки, либо только определенные строки, добавив
условие WHERE.
-- Deleting all entries Delete from <tablename> -- Deleting entries based on conditionsDelete from <tablename> where <column> = <value>
-- Deleting all entries Delete from users-- Deleting entries based on conditionsDelete from users where employed = false
Удаление
записей из таблицы
CRUD-операции используются очень часто, поскольку выполняют
основные функции в базе данных.
Перевод подготовлен в рамках курса Базы
данных. Все желающих приглашаем на бесплатный
двухдневный онлайн-интенсив Бэкапы и репликация PostgreSQL.
Практика применения. Цели занятия: настроить бэкапы; восстановить
информацию после сбоя. Регистрация здесь.