root/kladr/sql/kladr2mysql.sql

-- ��������CREATE TABLE types(
    id INT(1) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    short_name VARCHAR(10) NOT NULL,
    name VARCHAR(40) NOT NULL,
    INDEX (short_name)
) TYPE = innodb;
INSERT INTO types(short_name, name)
    SELECT DISTINCT scname, socrname FROM SOCRBASE;


-- ������CREATE TABLE regions (
   id INT(1) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(40) NOT NULL,
   type INT(1) UNSIGNED NOT NULL,
   _index CHAR(6) NOT NULL,
   code CHAR(2) NOT NULL,
   INDEX (name),
   INDEX (type),
   UNIQUE INDEX (code)
) TYPE = innodb;
INSERT INTO regions(name, type, _index, code)
    SELECT KLADR.name, types.id, _index, LEFT(code,2)
    FROM KLADR
        INNER JOIN types ON KLADR.socr = types.short_name
    WHERE RIGHT(code,11)="00000000000";

-- �����
CREATE TABLE zones (
   id INT(1) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(40) NOT NULL,
   type INT(1) UNSIGNED NOT NULL,
   _index CHAR(6) NOT NULL,
   parent_region INT(1) UNSIGNED NULL,
   code CHAR(5) NOT NULL,
   INDEX (name),
   INDEX (type),
   UNIQUE INDEX (code),
   INDEX (parent_region)
) TYPE = innodb;
INSERT INTO zones(name, type, _index, code)
    SELECT KLADR.name, types.id, _index, LEFT(code,5)
    FROM KLADR
        INNER JOIN types ON KLADR.socr = types.short_name
    WHERE RIGHT(code,8)="00000000" AND SUBSTRING(code,3,3) <>"000";
UPDATE zones SET parent_region = (SELECT id FROM regions WHERE code = LEFT(zones.code,2));


CREATE TABLE cities (
   id INT(1) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(40) NOT NULL,
   type INT(1) UNSIGNED NOT NULL,
   _index CHAR(6) NOT NULL,
   parent_region INT(1) UNSIGNED NULL,
   parent_zone INT(1) UNSIGNED NULL,
   code CHAR(8) NOT NULL,
   INDEX (name),
   INDEX (type),
   UNIQUE INDEX (code),
   INDEX (parent_region),
   INDEX (parent_zone)
) TYPE = innodb;
INSERT INTO cities(name, type, _index, code)
    SELECT KLADR.name, types.id, _index, LEFT(code,8)
    FROM KLADR
        INNER JOIN types ON KLADR.socr = types.short_name
    WHERE RIGHT(code,5)="00000" AND SUBSTRING(code, 6, 3)<>"000";
UPDATE cities SET parent_region = (SELECT id FROM regions WHERE code = LEFT(cities.code,2));
UPDATE cities SET parent_zone = (SELECT id FROM zones WHERE code = LEFT(cities.code,5));

-- ������ ���CREATE TABLE settlements (
   id INT(1) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(40) NOT NULL,
   type INT(1) UNSIGNED NOT NULL,
   _index CHAR(6) NOT NULL,
   parent_region INT(1) UNSIGNED NULL,
   parent_zone INT(1) UNSIGNED NULL,
   parent_city INT(1) UNSIGNED NULL,
   code CHAR(11) NOT NULL,
   INDEX (name),
   INDEX (type),
   UNIQUE INDEX (code),
   INDEX (parent_region),
   INDEX (parent_zone),
   INDEX (parent_city)
) TYPE = innodb;
INSERT LOW_PRIORITY INTO settlements(name, type, _index, code)
    SELECT SQL_BIG_RESULT KLADR.name, types.id, _index, LEFT(code,11)
    FROM KLADR
        INNER JOIN types ON KLADR.socr = types.short_name
    WHERE RIGHT(code,2)="00" AND SUBSTRING(code, 9, 3)<>"000";
UPDATE settlements SET parent_region = (SELECT id FROM regions WHERE code = LEFT(settlements.code,2));
UPDATE settlements SET parent_zone = (SELECT id FROM zones WHERE code = LEFT(settlements.code,5));
UPDATE settlements SET parent_city = (SELECT id FROM cities WHERE code = LEFT(settlements.code,8));

-- ����
CREATE TABLE streets (
   id INT(1) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(40) NOT NULL,
   type INT(1) UNSIGNED NOT NULL,
   _index CHAR(6) NOT NULL,
   parent_region INT(1) UNSIGNED NULL,
   parent_city INT(1) UNSIGNED NULL,
   parent_settlement INT(1) UNSIGNED NULL,
   code CHAR(15) NOT NULL,
   INDEX (name),
   INDEX (type),
   UNIQUE INDEX (code),
   INDEX (parent_region, parent_city, parent_settlement, name),
   INDEX (parent_city, parent_settlement, name),
   INDEX (parent_settlement, name)
) TYPE = innodb;
INSERT INTO streets(name, type, _index, code)
    SELECT SQL_BIG_RESULT STREET.name, types.id, _index, LEFT(code,15)
    FROM STREET
        INNER JOIN types ON STREET.socr = types.short_name
    WHERE RIGHT(code,2)="00" AND SUBSTRING(code, 12, 4)<>"0000";
UPDATE streets SET parent_region = (SELECT id FROM regions WHERE code = LEFT(streets.code,2));
UPDATE streets SET parent_city = (SELECT id FROM cities WHERE code = LEFT(streets.code,8));
UPDATE streets SET parent_settlement = (SELECT id FROM settlements WHERE code = LEFT(streets.code,11));

-- �����
CREATE TABLE buildings (
   id INT(1) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(40) NOT NULL,
   korp CHAR(1) NOT NULL,
   type INT(1) UNSIGNED NOT NULL,
   _index CHAR(6) NOT NULL,
   parent_city INT(1) UNSIGNED NULL,
   parent_settlement INT(1) UNSIGNED NULL,
   parent_street INT(1) UNSIGNED NULL,
   code CHAR(19) NOT NULL,
   INDEX (name),
   INDEX (type),
   INDEX (parent_city, name),
   INDEX (parent_settlement, name),
   INDEX (parent_street, name),
   UNIQUE INDEX (code)
) TYPE = innodb;
INSERT INTO buildings(name, korp, type, _index, code)
    SELECT SQL_BIG_RESULT DOMA.name, DOMA.korp, types.id, _index, code
    FROM DOMA
        INNER JOIN types ON DOMA.socr = types.short_name
    WHERE RIGHT(code, 4)<>"0000";
UPDATE buildings SET parent_city = (SELECT id FROM cities WHERE code = LEFT(buildings.code,8));
UPDATE buildings SET parent_settlement = (SELECT id FROM settlements WHERE code = LEFT(buildings.code,11));
UPDATE buildings SET parent_street = (SELECT id FROM streets WHERE code = LEFT(buildings.code,15));

-- �������code'
ALTER TABLE regions DROP code;
ALTER TABLE zones DROP code;
ALTER TABLE cities DROP code;
ALTER TABLE settlements DROP code;
ALTER TABLE streets DROP code;
ALTER TABLE buildings DROP code;

-- ����������/*
SELECT name, id, type FROM streets FORCE INDEX(parent_settlement) WHERE parent_settlement = 12
   AND name LIKE '�'
   LIMIT 10;
SELECT name, id, type FROM streets FORCE INDEX(parent_city) WHERE parent_city = 85
   AND parent_settlement IS NULL
   AND name LIKE '�'
   LIMIT 10;
SELECT name, id, type FROM streets FORCE INDEX(parent_region) WHERE parent_region = 85
   AND name LIKE '�'
   AND parent_city IS NULL
   AND parent_settlement IS NULL
   LIMIT 10;

SELECT name, id, type FROM settlements WHERE parent_city = 12
   AND name LIKE '�' LIMIT 10;
SELECT name, id, type FROM settlements WHERE parent_zone = 12
   AND name LIKE '�' LIMIT 10;
SELECT name, id, type FROM settlements WHERE parent_region = 12
   AND name LIKE '�'
   AND parent_zone IS NULL
   AND parent_city IS NULL LIMIT 10;

SELECT name, id, type FROM cities WHERE parent_zone = 12
   AND name LIKE '�' LIMIT 10;
SELECT name, id, type FROM cities WHERE parent_region = 12
   AND name LIKE '�'
   AND parent_zone IS NULL LIMIT 10;

SELECT name, id, type FROM zones WHERE parent_region = 12
   AND name LIKE '�' LIMIT 10;
SELECT name, id, type FROM regions WHERE name LIKE '�' LIMIT 10;
*/