Общероссийский классификатор населённых пунктов (КЛАДР + ОКАТО) для MySQL - insbor.ru
insbor.ru

insbor.ru

Привет, это я

Читаю, пишу, перечитываю и исправляю.


Что здесь происходит


Предыдущие записи


Общероссийский классификатор населённых пунктов (КЛАДР + ОКАТО) для MySQL

Опубликовано :   |  Кем :   |  Категория :  Классификаторы

Добрый день! Не так давно остро встала проблема упорядоченного хранения адресов некоторых объектов в разрабатываемой системе. КЛАДР конечно скачать не сложно с официального сайта налоговой, да только данные там не в виде дампа SQL, а в файле DBF и структура нормализованная согласно всем правилам теории. Ладно бы формат, но второе уже совсем плохо.

Что именно плохого, спросят некоторые? А то, что нам мало знать название населённого пункта и его тип(деревня, город, район..), нужно показать название его региона, а это вызовет JOIN таблицы в 192к строк общим размером около 22мб(utf8, MySQL). Коды объектов хранятся в виде строки длиной 13 символов. Чтобы не пришлось разбивать её на части в ходе запросов, я сделал это один раз и отдельные фрагменты залил в соответствующие столбцы.

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

На всякий случай храню как коды КЛАДР(в едином и разбитом на составляющие виде), так и ОКАТО(только единый код), которые были в исходном файле. Это специфика моего  проекта, данные могут понадобиться в перспективе. Получается, что коды ОКАТО есть только для населённых пунктов, а объектов ОКАТО более высокого уровня вложенности нет. Здесь так же не учтена таблица обновлений и изменений данных КЛАДР, так что будьте осторожны, если для вас важна актуальность всяких бюрократических кодов.

Вот, к какому виду для классификатор населённых пунктов России пришёл в итоге:

CREATE TABLE IF NOT EXISTS `system_classifiers_cities` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(40) NOT NULL COMMENT 'Наименование',
`type_name` varchar(10) NOT NULL COMMENT 'Сокращённое наименование типа объекта',
`prefix_type_name` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Флаг, указывающй на то, что тип пишется перед названием объекта',
`code` varchar(13) NOT NULL COMMENT 'Код КЛАДР',
`postcode` varchar(7) NOT NULL COMMENT 'Почтовый индекс',
`ifts_code` varchar(4) NOT NULL COMMENT 'Код ИФНС',
`ifts_area_code` varchar(4) NOT NULL COMMENT 'Код территориального участка ИФНСnnЕсли у ИФНС нет подразделений или данный адресный объект находится на территории ИФНС, с которой объединились упраздненные инспекции, то это поле остается пустым (пробельным)',
`okato_code` varchar(11) NOT NULL COMMENT 'Код ОКАТО',
`status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Статусnn0 - объект не является центромn1 – райцентр;n2 – центр региона;n3 – одновременно и центр района и центр региона;n4 – центральный район, т.е. район, в котором находится центр региона',
`country_code` smallint(5) unsigned NOT NULL COMMENT 'Международный код страны',
`country_name` varchar(40) NOT NULL COMMENT 'Название страны',
`region_code` varchar(2) NOT NULL COMMENT 'Код региона',
`region_name` varchar(40) NOT NULL DEFAULT '' COMMENT 'Название региона, в котором расположен объект для вложенных объектов',
`region_type_name` varchar(10) NOT NULL DEFAULT '' COMMENT 'Тип региона, в котором расположен объект для вложенных объектов',
`region_prefix_type_name` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Флаг, указывающй на то, что тип региона пишется перед названием региона',
`district_code` varchar(3) NOT NULL COMMENT 'Код областного района',
`city_code` varchar(3) NOT NULL COMMENT 'Код города',
`is_locality` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Флаг, является ли объект населённым пунктом или регионом страны',
`relevance_code` varchar(2) NOT NULL COMMENT 'Признак актуальности',
`visible` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT 'Видимость',
`order` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Порядок сортировки (индекс популярности)',
`description` varchar(255) NOT NULL COMMENT 'Описание',
PRIMARY KEY (`id`),
KEY `cities_name_index` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Классификатор населённых пунктов России' AUTO_INCREMENT=191643;

Избыточность информации и денормализованная структура с лихвой окупаются простотой использования. Для каждого населённого пункта мы сразу знаем название его региона, его код и прочие данные. Размер таблицы вырос до 42мб. Можно удалить неиспользуемое пока поле description, данные из налоговой службы, можно даже удалить все коды регионов, если в них точно не возникнет нужды в будущем и занимаемый таблицей объём сократится.

Добавим сюда классификатор стран мира:

CREATE TABLE IF NOT EXISTS `system_classifiers_countries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT 'Наименование страны',
  `visible` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT 'Видимость в системе',
  `name_en` varchar(255) NOT NULL DEFAULT '' COMMENT 'Наименование страны на английском языке',
  `full_name` varchar(255) NOT NULL COMMENT 'Полное наименование страны',
  `code` varchar(4) NOT NULL COMMENT 'Международный числовой код',
  `phone_code` varchar(7) NOT NULL DEFAULT '' COMMENT 'Междунароный телефонный код',
  `alpha2` varchar(2) NOT NULL COMMENT 'Код альфа-2',
  `alpha3` varchar(3) NOT NULL COMMENT 'Код альфа-3',
  `flag` varchar(255) NOT NULL DEFAULT '' COMMENT 'Иконка с флагом страны',
  `active` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Страна активно включена в систему',
  `order` tinyint(2) NOT NULL DEFAULT '0' COMMENT 'Порядок сортировки',
  `description` varchar(255) NOT NULL DEFAULT '' COMMENT 'Описание',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`),
  UNIQUE KEY `alfa2_UNIQUE` (`alpha2`),
  UNIQUE KEY `alpha3_UNIQUE` (`alpha3`),
  UNIQUE KEY `full_name_UNIQUE` (`full_name`),
  UNIQUE KEY `name_en_UNIQUE` (`name_en`),
  UNIQUE KEY `number_code_UNIQUE` (`code`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Общероссийский классификатор стран мира ОКСМ' AUTO_INCREMENT=249;

Обратите внимание, что страны имеют полное русское наименование и общее. Общее может отличаться от официального справочника, некоторые типа США и Великобритании я приводил к общепринятому и более краткому виду.

Возможно вам пригодится и справочник сокращений из КЛАДР, засуну и его сюда на всякий случай:

CREATE TABLE IF NOT EXISTS `system_classifiers_cities_abbreviations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `level` varchar(2) NOT NULL COMMENT 'Уровень вложенности',
  `name` varchar(32) NOT NULL COMMENT 'Сокращение',
  `full_name` varchar(128) NOT NULL COMMENT 'Полное имя',
  `kod_t_st` varchar(3) NOT NULL COMMENT 'Код КЛАДР',
  `order` smallint(6) NOT NULL DEFAULT '0' COMMENT 'Порядок сортировки',
  `description` varchar(255) DEFAULT NULL COMMENT 'Описание',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Общероссийский классификатор аббревиатур адресов' AUTO_INCREMENT=147;

Cкачать архив (.zip 3.5мб) с дампом всех трёх таблиц.