Нормализация адресов в реляционной БД: как разложить адрес на GUID-ы

Андрей Отинов -

В офисе аналитик открывает таблицу с адресами, которые записаны в одном поле как «ул. Ленина, д. 5, кв. 12».
В статье предлагается разбить адрес на отдельные элементы, привязать каждый к GUID из государственного реестра ГАР ФИАС и хранить в таблице как отдельные колонки.
Это позволяет быстро находить дубликаты, выполнять геокодирование и делать запросы по отдельным компонентам адреса.
Перед внедрением стоит проверить, есть ли у вас доступ к SDK Pullenti Address и как покрытие ФИАС выглядит для ваших регионов.

Источник: Habr

Что изменилось в практике

  • Нормализованный формат: вместо одного текста адреса создаётся таблица с полями 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 при наличии привязки к ФИАС.
  • GUID‑привязка: каждый элемент может иметь массив GUID‑ов (PostgreSQL uuid[]), что отражает дублирование в реестре и позволяет точно сопоставлять записи.
  • SQL‑операции: благодаря разложению на поля можно писать простые JOIN‑запросы, GROUP BY по GUID‑ам и WHERE‑условия для поиска дубликатов.

Почему это важно сейчас

  • Проблемы ручных адресов: вариативность написания, пропуски, искажения затрудняют поиск и анализ.
  • Дублирование данных: без нормализации одинаковые адреса могут храниться в разных строках, что приводит к ошибкам в отчетах и расходу ресурсов.
  • Геокодирование: наличие координат позволяет интегрировать адреса в карты, логистику и сервисы доставки.
  • Стабильность ФИАС: покрытие домов в России сейчас около 67 %, но оно растёт, и привязка к реестру повышает надёжность данных.

Как превратить это в повторяемый workflow

  1. Сбор исходных данных
  2. Экспортируйте адреса из существующей системы в CSV/JSON.
  3. Парсинг и нормализация
  4. Запустите SDK Pullenti Address (Python/Java) на каждом адресе.
  5. Получите структуру: country, region, city, street, house, apartment и т.д.
  6. Сохранение в БД
  7. Создайте таблицу addresses с полями, перечисленными выше.
  8. Для каждого адреса вставьте нормализованные строки и массив GUID‑ов.
  9. Проверка дубликатов
    sql SELECT COUNT(*) AS cnt, STRING_AGG(id::text, ', ') AS ids, city_guid FROM addresses GROUP BY city_guid HAVING COUNT(*) > 1;
  10. Геокодирование
  11. Если GPS_LAT/GPS_LON пусты, запустите запрос к ФИАС через API или используйте локальный сервис для получения координат.
  12. Регулярное обновление
  13. Периодически запускайте синхронизацию с ФИАС, обновляя GUID‑ы и координаты.

Где ограничения и риски

  • Зависимость от ФИАС: модель работает только для России; в других странах потребуется аналогичный реестр.
  • Коммерческий SDK: Pullenti Address – платный продукт; лицензия и стоимость могут стать препятствием для небольших команд.
  • Покрытие ФИАС: 67 % домов покрыто, но в некоторых регионах покрытие ниже, что может оставить адреса без координат.
  • Сложность интеграции: требуется написать парсер, настроить БД и обучить команду работе с GUID‑ами.

Что делать дальше

  1. Проверить доступ
  2. Убедитесь, что у вас есть лицензия Pullenti Address и доступ к API ФИАС.
  3. Тестовый запуск
  4. Выберите 100–200 адресов из вашей базы, запустите парсинг и сохраните в тестовую таблицу.
  5. Оценить покрытие
  6. Сравните количество заполненных GPS_LAT/GPS_LON с общим числом записей.
  7. Сравнить дубликаты
  8. Выполните примерный запрос на дубликаты и посчитайте, сколько записей можно объединить.
  9. Решить
  10. Если покрытие > 70 % и дублирующиеся адреса уменьшаются более чем на 30 %, стоит продвигать модель в продакшн.
  11. Если покрытие низкое или лицензия слишком дорогая, рассмотрите альтернативы (libpostal, собственный парсер).

Источники

Дополнительные соображения

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

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

Рассмотрим конкретный пример преобразования адреса. Исходная строка: "г. Москва, ул. Тверская, д. 7, кв. 15". После парсинга с помощью Pullenti Address получаем структуру: регион — "Москва" (GUID: 0c5b2444-70a0-4932-980c-b4dc0d3f02b5), город — "Москва" (GUID: 0c5b2444-70a0-4932-980c-b4dc0d3f02b5), улица — "Тверская" (GUID: 9d136253-4b92-4e1c-9cf4-8c7b0f7a8e3d), дом — "7" (GUID: 1a2b3c4d-5e6f-7a8b-9c0d-1e2f3a4b5c6d), квартира — "15". В таблицу записываются соответствующие мнемоники и GUID-ы. Если в ФИАС дом имеет дублирующую запись, в поле HOUSE_GUID сохраняется массив из двух GUID-ов. Это позволяет в будущем корректно объединять данные из разных источников.

Сравнение подходов: нормализованная модель vs. текстовая строка

Для наглядности сравним два подхода к хранению адресов. Текстовая строка "г. Москва, ул. Тверская, д. 7, кв. 15" занимает около 40 байт, но поиск всех адресов на улице Тверская требует LIKE-запроса с шаблоном '%Тверская%', что медленно и ненадёжно. Нормализованная модель с отдельными колонками занимает больше места (около 200 байт с учётом GUID-ов), но позволяет выполнять точный поиск по STREET_MNEM = 'Тверская' с использованием индекса. При объёме базы в 1 миллион записей время выполнения запроса сокращается с нескольких секунд до миллисекунд. Дополнительно, нормализация упрощает обнаружение дубликатов: два адреса с одинаковым набором GUID-ов гарантированно идентичны, даже если исходные строки различаются (например, "ул. Тверская, д. 7" и "Тверская ул., дом 7").

Метрики успеха внедрения

Для оценки эффективности модели рекомендуется отслеживать следующие показатели. Процент дубликатов: после нормализации количество повторяющихся адресов должно снизиться на 30–50%. Время выполнения запросов: среднее время поиска по адресу должно уменьшиться в 5–10 раз. Покрытие геокодированием: доля адресов с заполненными GPS-координатами должна превышать 70% для регионов с хорошим покрытием ФИАС. Точность парсинга: не менее 95% адресов должны корректно разбиваться на компоненты. Регулярный мониторинг этих метрик позволит своевременно выявлять проблемы и корректировать workflow.

Что почитать дальше