Почтовые адреса используют в реляционных БД, обычно записывая их в одно текстовое поле или распределяя по отдельным полям типа город, улица, номер дома, корпус, квартира (возможно, вынося города и улицы в отдельные таблицы). В данной статье хочу поделиться одной моделью представления, которая коррелирует с моделью адресов ГАР ФИАС и позволяет выполнять широкий спектр действий с адресами средствами языка SQL.
Как известно, вручную написанные адреса обладают рядом неприятных свойств, затрудняющих их программное использование. Это и многовариативность написания одного и того же элемента, и пропуски, и искажения, и добавление лишнего. Идея состоит в том, чтобы выделить адресные элементы, нормализовать их, по возможности привязать к элементам ГАР ФИАС и сохранять в таблице БД не только нормализованные строки элементов адреса, но и GUID привязанных к ГАР элементов. При таком представлении возможно средствами SQL производить поисковые операции, находить дубликаты и пр., что затруднительно делать на исходных текстах адресов.
Данная модель применяется в проекте "Досье" компании Преферентум для системы загрузки и анализа неструктурированной и полуструктурированной информации (выгрузки разных баз и информационных систем). Для нормализации и привязки к ГАР используется SDK Pullenti Address, которое автор и разрабатывает.
Модель адреса
Адрес представляет собой последовательность адресных элементов. В принципе, элементы могут быть в произвольном порядке, хотя обычно начинают с высокого уровня (страны, региона) и заканчивая низким (дома, квартиры). Приведём типизацию элементов по уровням:
Страна;
Регион: край, область, города Москва, Санкт-Петербург, Севастополь (в ГАР уровень 1);
Административный район (в ГАР уровень 2);
Муниципальный район: городской\муниципальный округ (в ГАР уровень 3, но в иерархии всегда принадлежит региону);
Городское\сельское поселение, внутригородской район, межселенная территория (в ГАР уровень 4, всегда принадлежит муниципальному району);
Город (в ГАР уровень 5);
Населённый пункт: село, посёлок, деревня (в ГАР уровень 6);
Элемент планировочной структуры: кварталы, зоны, массивы, территории садовых товариществ, организаций и т.п. (в ГАР уровень 7);
Элемент улично-дорожной сети (в ГАР уровень 8);
Земельный участок (в ГАР уровень 9);
Здание, сооружение, гараж (в ГАР уровень 10);
Помещение, квартира, комната (в ГАР уровень 11);
Машиноместо (в ГАР уровень 17);
Модель описывалась в статье 2022 года, также там приводились примеры и способы нормализации элементов разных уровней. Отметим, что в данную модель укладываются не только собственно адреса, но и города, и страны (все значения кроме "страна" пустые).
Поля таблицы БД
Опыт показал, что для полноценного представления адреса в БД достаточно полей, содержащих элементы следующих уровней (имена полей условные):
Страна: COUNTRY_CODE
Регион: REGION_MNEM + REGION_GUID
Город: CITY_MNEM + CITY_GUID
Район: DISTRICT_MNEM + DISTRICT_GUID
Населенный пункт: LOCATION_MNEM + LOCATION_GUID
Элемент планировочной структуры (территория): TERRITORY_MNEM + TERRITORY_TYPE + TERRITORY_GUID
Элемент улично-дорожной сети (улица): STREET_MNEM + STREET_TYPE + STREET_GUID
Дом, строение, земельный участок: HOUSE_MNEM + HOUSE_GUID
Помещение, квартира, комната, машиноместо: APARTMENT_MNEM + APARTMENT_GUID
GPS_LAT и GPS_LON - координаты, если удалось привязаться к ГАР (в SDK объекты ГАР дополнены координатами, покрытие домов на текущий момент в среднем 67% по России)
Для страны достаточно хранить двухсимвольный код Alpha2 (RU, EN, KZ ...).
Поля с суффиксом _GUID хранят идентификаторы ГАР в случае их привязки. Желательно, чтобы тип колонок был массивом uuid[] (в терминах Postgres), так как из-за дублирования элементов в ГАР и параллельной структуры муниципального и административного деления элемент может иметь несколько Guid-ов (кроме регионов, где всегда один Guid). Отметим, что эти поля пустые для нероссийских адресов, которые всё равно вписываются в данное представление.
Поля с суффиксом _MNEM хранят текстовые нормализованные значения. Способ нормализации чуть ниже.
Территории и улицы дополнительно имеют поля _TYPE с типами элементов, которые вынесены из мнемонических полей. Здесь также возможна многозначность, поэтому желательно использовать массив типа varchar(32)[]. Например, "улица Цветочная аллея" - "улица" и "аллея" попадёт в типы, а "ЦВЕТОЧНАЯ" в мнемонику. Отметим, что ни районы, ни населённые пункты типов не имеют. Районы потому, то одни и те же названия, но разные типы в административных и муниципальных иерархиях, и смысла в детализации нет никакого. А для небольших населённых пунктов часто путают посёлки, сёла и поселения, например, и т.п. И с одинаковыми именами и разными типами в одном районе населённые пункты нам не встречались. А вот для улиц тип важен, так как часто имеют место одинаковые названия и разные типы в одном городе.
Нюансы:
Если задан город, то район не задаётся (но внутри города может быть населенный пункт), если города нет, то для населенного пункта указывается один ближайший в иерархии ГАР район;
Редко встречаются ситуации, когда в ГАР и адресе есть как квартира, так и комната внутри квартиры как отдельный объект. Можно было бы добавить ещё уровень, но это крайне редкая ситуация и мы ей пренебрегаем;
Для регионов-городов (Москва, Санкт-Петербург, Севастополь) значения дублируются на 2-м и 3-м уровнях (чтобы можно было искать как по Москве-региону, так и по Москве-городу).
Нормализация мнемоник осуществляется так, чтобы независимо от написания по возможности получилась одна буквенно-цифровая комбинация:
для именованных объектов (городов, улиц и пр.):
разбиваем на слова (типы отбрасываем), числа из символьного написания приводим к цифровому (Восьмого марта -> 8 марта), символы в верхний регистр, замена Ё на Е, Щ на Ш, удаление Ъ и Ь, замена рядом стоящих одинаковых букв на одну, обрубание окончаний (стемминг)
удаляем "стоп" слова: имени, конструктора, академика, для персон оставляем только фамилию (например, ул. маршала Георгия Жукова -> ЖУКОВ)
сортируем преобразованные слова (например, "ул. Петра I" -> "1 ПЕТР", "пер. Каретный Б." -> "БОЛШ КАРЕТН")
для домов в общем случае возможны 3 номера: номер дома/владения, номер корпуса и номер строения/сооружения/литера. Причём литера может цепляться к номеру дома. Также номер может состоять из двух номеров через дробь, второй номер может быть как номер на пересечении с другой улицей, так и номером корпуса или строения. Здесь мы используем такую нормализацию:
первая буква: У - для участков, Г - для гаражей, К - если нет номера, а есть корпус, С - если нет ни номера, ни корпуса, а есть строение, Д - для остального
далее через пробел добавляем номера дома\корпуса\строения, причём каждый номер разбиваем на элементы - цифры отдельно, буквы отдельно, латиницу по возможности переводим в кириллицу (например, "д.11/5А стр.7" -> "Д11 5 А 7")
для помещений и машиномест номер обычно один, но может быть тоже из нескольких номеров через дефис, например, и содержать буквы. Первая буква: К для квартир, М для машиномест, и П для всего остального. А сам номер нормализуется как для домов. Если номер отсутствует, то 0.
Для осуществления этих преобразований и получения нормализованных данных для записи в таблицу в SDK Pullenti Address есть класс AddressDbRecord. Ниже некоторые примеры его использования, в скобках идентификатор ГАР-объекта.
Исходный адрес: Кронштадт, Якорная пл, дом б/н, корпус А, литера А
Результат разбора: Россия, город Санкт-Петербург, город Кронштадт, площадь Якорная, дом б/н корп.А лит.А
Регион: ПЕТЕРБУРГ САНКТ (c2deb16a-0330-4f05-821f-1d09c93331e6)
Город: КРОНШТАДТ (7b1c7427-41b2-4ffc-bf39-550e8299b5cb)
Улица: ЯКОРН / ПЛОЩАДЬ (476cf4b7-b87b-4416-b0f0-a5f155c6a94e)
Здание: КА А (7633eed3-5c97-4afc-92a5-0f17f68f05dc)
GPS: 59.99202 29.77618 (уровень Street)
356300,с Александровское,ул Войтика,27,64
Результат разбора: Россия, край Ставропольский, муниципальный округ Александровский, село Александровское, улица Войтика, д.27, кв.64
Регион: СТАВРОПОЛСК (327a060b-878c-4fb4-8dc4-d5595871a3d8)
Район: АЛЕКСАНДРОВСК (03c8c390-8b6b-4a49-a58c-935843d6468e)
Нас.пункт: АЛЕКСАНДРОВСК (def12d5d-12e9-4ae7-b397-60d8f54b8146)
Улица: ВОЙТИК / УЛИЦА (8cbfef43-5595-4d21-ba7a-0451eda1323d)
Здание: Д27 (0dcb66d2-f31b-4460-b1b2-78699ea1c798)
Помещение: К64 (ac200ad7-517f-4af3-8ccb-0f959e9d220e)
GPS: 44.70804 43.00572 (уровень House)
Разное: индекс 356300
Бурятия, Заиграевский район, ДНТ Звезда, Воинская улица, 5
Результат разбора: Россия, республика Бурятия, район Заиграевский, территория ДНТ “Звезда”, улица Воинская, 5
Регион: БУРЯТ (a84ebed3-153d-4ba9-8532-8bdf879e1f5a)
Район: ЗАИГРАЕВСК (730dc732-140d-437b-81c5-aa927aec0471)
Территория: ЗВЕЗД / ДНТ (34b63c06-77b3-41a6-a865-23aef1a7c863)
Улица: ВОИНСК / УЛИЦА (в ГАР улицы отсутствуют в этом ДНТ)
Здание: Д5 (здесь непонятно дом или участок, по умолчанию считаем дом)
Расположение полей
Пусть в БД адрес находится в некотором поле в таблице. Чтобы использовать новую модель адреса в дополнение к старой, можно поступить несколькими способами:
Добавить новые поля к существующей таблице;
Создать отдельную таблицу, а в старой добавить поле ссылки на ключ записей новой таблицы.
При втором способе можно иметь одну адресную таблицу на многие неструктурированные поля, также в ней потенциально будет меньше дубликатов, чем при первом способе.
Если таблицы с адресами уже есть, то придётся извлечь все записи с адресами, обработать их и записать новые значения. При этом если новая таблица для адресов отдельная, то перед записью нужно через запрос SQL поискать уже существующую запись (см. далее), чтобы избежать дублирования. SDK Pullenti предлагает ещё некоторые полезные поля:
NORMAL - нормализованное полное текстовое представление адреса;
LEVEL - уровень самого нижележащего элемента (1 - страна, 2 - регион, 3 - город и т.д.);
COEF - коэффициент распознавания, MESSAGE - сообщение об ошибке. Адреса, для которых коэффициент менее 80, могут быть дефектными. Коэффициент 100 - наилучшее качество распознавания и привязки;
MISCS - сюда попадают разные второстепенные мелочи типа этажей, подъездов, почтового индекса, всяких относительных указателей типа "200 метров на юго-запад" и т.п. Можно игнорировать.
Индекс ГАР - постоянно модифицируемый и пополняемый ресурс. Алгоритмы SDK Pullenti также совершенствуются. Поэтому при последующем прогоне на одних и тех же данных может получиться лучший результат. Было бы уместно периодически переобрабатывать адреса, для которых COEF < 100, или у которых есть какой-либо элемент без привязки к ГАР (для российских адресов, разумеется). Возможно, что на новой версии такая привязка состоится.
Поиск адресов средствами SQL
Поисковый адрес - это прогнанный через аналогичное преобразование текстовой адрес, представляющий собой последовательность адресных элементов, часть из которых привязана к ГАР (то есть имеет уникальный идентификатор). Условие WHERE для поискового SELECT-запроса формируется следующим образом:
Перебираем элементы начиная с низшего уровня (например, уровня дома):
Если элемент не привязан к ГАР, то в условие добавляем условие по мнемонике "
and a.CITY_MNEM = '...'" (для городов), а для улиц и территорий дополнительно "and a.STREET_TYPE = '...'" и переходим к следующему элементу;Если элемент привязан к ГАР, то в условие добавляем "and a.STREET_GUID = '...' " и завершаем формирование - останов на ближайшем привязанном к ГАР элементе;
То есть идея в том, что если элемент привязан к ГАР, то поиск однозначный по его guid, и выше не поднимаемся (нет смысла добавлять условие на элементы выше), иначе поиск по мнемонике (и типу для улиц\территорий).
Например, для поиска "ул.Советская д.10" без населенного пункта привязки к ГАР не будет (точнее, их может быть очень много), и тогда поисковое условие "a.STREET_MNEM='СОВЕТСК' and a.TYPE='УЛИЦА' and a.HOUSE_MNEM='Д10' ".
А вот для "ростов на дону ул.Советская д.10" мы уже привяжем дом к ГАР, и условие запроса будет "a.HOUSE_GUID='264ba3ea-0963-4683-beb2-d6c4a4692144' ". Точнее, для Postgres в случае массива - " '264ba3ea-0963-4683-beb2-d6c4a4692144' = any(a.HOUSE_GUID) ".
С помощью аналогичных запросов можно найти дубликаты адреса, дома на конкретной улице, квартиры в том же доме. А благодаря наличию GPS для большинства объектов можно решать и некоторые географические задачи, например, поиска адресов в заданном квадрате.
В SDK Pullenti для формирования таких условий для SELECT есть служебный класс SqlHelper, решающий эту задачу. Для оптимальности формирование условий идёт с использованием параметров, то есть вместо констант подставляются имена параметров и делается словарь "имя параметра = значение".
Заключение
Неструктурированные адреса - головная боль для автоматической обработки. В статье автор поделился своим видением практического решения этой проблемы. Можно по-разному реализовывать эту схему, например, прикрепляя новые поля к существующей таблице или делая новые таблицы - одну или несколько (она для страны-города-района-населенного пункта, другая для территорий и улиц, третья для строений и помещений). В принципе, от конкретной СУБД это тоже не зависит. Идея - в разбиении на элементы, их нормализация и привязка к ГАР, приведение к единому вектору данных, содержащих как мнемонические значения, так и абсолютные идентификаторы ГАР. Это позволит качественнее оперировать адресами средствами SQL.
