Оптимизация базы данных Федеральной информационной адресной системы (КЛАДР)

Работа с большими объемами данных отнимает много времени и терпения, особенно когда ты вносишь информацию вручную. Строчку за строчкой – информация появляется в нужных разделах… Но каким трудом? Кажется, что этот процесс бесконечен. А что делать, если внесенная вручную информация содержит опечатки?

Чтобы сэкономить силы и время сотрудников, не допустить неточности/опечатки и гарантировать успешную доставку писем, было принято решение оптимизировать базу данных Федеральной информационной адресной системы (КЛАДР) – большой объём почтовых адресов Российской Федерации, которые использовались для деловой рассылки.

Федеральная информационная адресная система, в прошлом известная как классификатор адресов Российской Федерации, находится в свободном доступе на сайте Федеральной службы. Обновления для этой системы выходят регулярно, примерно раз в неделю. О новом обновлении можно узнать через запрос к веб-сервису. Сама база данных этой системы содержит информацию о следующих компонентах:

  • субъектах РФ;
  • округах;
  • административных районах, муниципальных районах и т.д.;
  • городах, пунктах городского типа;
  • сельских населенных пунктах;
  • улицах и прочей улично-дорожной сети населенных пунктов;
  • домах и интервалах домов.

Но есть причины, из-за которых её нельзя было использовать в чистом виде:

  • данные доступны в двух форматах: таблиц DBF и XML-файлов;
  • хранить данные нужно было постоянно на клиентской машине заказчика, постоянно пополняя их актуальной информацией; учитывая размер файлов с данными и скорость работы с ними, такая процедура
  • была бы слишком проблематичной;
  • плохая структуризация: данные о всех объектах, кроме домов, хранились в одной и той же таблице, которая занимала около 1 Гб на жестком диске и содержала 1,5 миллиона записей; так же
  • отсутствие реляций.
  • избыточность данных для поставленной задачи: данных, используемых строго для нужд Федеральной налоговой службы, было слишком много, а также в таблицах можно было найти устаревшую информацию для одного и того же адресного объекта.

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

Было решено разбить по разным таблицам адресные объекты, которые относятся к разным уровням иерархии. Все адресные объекты, как было сказано выше, хранились в одной и той же таблице. Разбиение было произведено по полю AOLEVEL, которое принимало одно из следующих значений: 1 – регион, 3 – район, 4-6 – город или населенный пункт, 7 – улица. Благодаря этому выстроилась четкая иерархия:

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

Для оптимизации скорости поиска, помимо внешних ключей, были проиндексированы названия адресных объектов, номера домов и почтовые коды.

Всего в базе данных около 100 таблиц, большую часть из которых занимают таблицы с информацией о домах. Каждая таблица названа по шаблону HouseN, где N – номер региона. Объединять данные домов в одну единственную таблицу было бессмысленно, поскольку общее количество домов было около 20 миллионов, и мы бы потеряли преимущества проиндексированных полей. Остальные таблицы имеют следующее предназначение:

  • Region – субъекты РФ;
  • District – районы;
  • City – города и населенные пункты;
  • Street – улицы;
  • HouseInterval – интервалы номеров домов;
  • RegionalPostalRange – справочник с диапазонами почтовых индексов для каждого региона;
  • ObjAbbreviation – справочник, содержащий типы адресных объектов в полном и сокращенном виде.

Реализация алгоритмов нормализации и обновления данных

В качестве СУБД для новой базы данных был выбран Microsoft SQL Server Express. Для поиска адресов, устранения опечаток и восстановления пропущенных почтовых индексов был реализован нечеткий поиск с использованием хранимых процедур на языке T-SQL. Благодаря иерархической структуре и наличию проиндексированных полей, теперь возможно нормализовать или восстановить десятки тысяч почтовых адресов за считаные минуты.

Так как новые данные для базы данных доступны каждую неделю, необходимо поддерживать актуальность данных. Для этого в исходных данных используется поле ACTSTATUS. Благодаря этому полю можно разделить актуальные или устаревшие данные. Логика обновления так же реализована с помощью хранимых процедур и производится очень быстро.

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

Заключение

После реструктуризации исходной базы данных ФИАС была создан независимый инструмент, содержащий информацию о всех адресах Российской Федерации и позволяющий легко и быстро осуществлять поиск, обновление и восстановление информации о почтовых адресах.

В будущем мы планируем создать облачный сервис КЛАДР,  с помощью которого разработчики смогут легко использовать инструмент в своих проектах.