SET NAMES utf8;

CREATE TABLE adresa (
  `adresa_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `objekt_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `ulice_kod` INTEGER UNSIGNED DEFAULT NULL,
  `cisdom_hod` INTEGER UNSIGNED DEFAULT NULL,
  `cisor_hod` INTEGER UNSIGNED DEFAULT NULL,
  `cisor_pis` VARCHAR(16) DEFAULT NULL,
  `psc` decimal(5,0) NOT NULL DEFAULT '0',
  `stav`TINYINT(4) NOT NULL DEFAULT '0',
  `pcd` decimal(10,0) DEFAULT NULL,
  `geom` GEOMETRY DEFAULT NULL,
  PRIMARY KEY (`adresa_kod`),
  KEY `ulice_kod` (`ulice_kod`),
  KEY `objekt_kod` (`objekt_kod`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE cobce (
  `cobce_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `obec_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `nazev` VARCHAR(48) NOT NULL,
  `zkratka` VARCHAR(16) NOT NULL,
  `stav` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  `helptext` varchar(255) NULL DEFAULT NULL,
  `geom` GEOMETRY DEFAULT NULL,
  PRIMARY KEY (`cobce_kod`),
  KEY `obec_kod` (`obec_kod`),
  KEY `nazev` (`nazev`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE katastralni_uzemi (
  `ku_kod` INTEGER UNSIGNED NOT NULL,
  `nazev` VARCHAR(250) CHARACTER SET utf8 COLLATE utf8_czech_ci DEFAULT NULL,
  `obec_kod` INTEGER UNSIGNED NOT NULL,
  `stav` TINYINT(4) UNSIGNED NOT NULL,
  `geom` GEOMETRY DEFAULT NULL,
  PRIMARY KEY (`ku_kod`),
  KEY `obec_kod` (`obec_kod`),
  KEY `nazev` (`nazev`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE kraj (
  `kraj_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `nuts3` VARCHAR(5) NOT NULL,
  `oblast_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `nazev` VARCHAR(32) NOT NULL,
  `zkratka` VARCHAR(16) NOT NULL,
  `stav` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  `geom` GEOMETRY DEFAULT NULL,
  PRIMARY KEY (`kraj_kod`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


CREATE TABLE mcast (
  `mcast_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `obec_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `pobvod_kod` INTEGER UNSIGNED DEFAULT NULL,
  `typ` decimal(1,0) NOT NULL DEFAULT '0',
  `nazev` VARCHAR(48) NOT NULL,
  `zkratka` VARCHAR(16) NOT NULL,
  `stav` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  `sobvod_kod` INTEGER UNSIGNED DEFAULT NULL,
  `nobvod_kod` INTEGER UNSIGNED DEFAULT NULL,
  `geom` GEOMETRY DEFAULT NULL,
  `helptext` varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (`mcast_kod`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE okres (
  `okres_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `kraj_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `nazev` VARCHAR(32) NOT NULL,
  `zkratka` VARCHAR(16) NOT NULL,
  `nuts4` VARCHAR(5) NOT NULL,
  `stav` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  `geom` GEOMETRY DEFAULT NULL,
  `helptext` varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (`okres_kod`),
  KEY `kraj_kod` (`kraj_kod`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


CREATE TABLE parcela (
  `parcela_kod` BIGINT UNSIGNED NOT NULL DEFAULT '0',
  `ku_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `kmenove_cislo` INTEGER UNSIGNED NOT NULL,
  `pododdeleni_cisla` INTEGER UNSIGNED NOT NULL,
  `vymera_parcely` decimal NOT NULL,
  `druh_cislovani` decimal NOT NULL DEFAULT '0',
  `druh_pozemku` decimal NOT NULL DEFAULT '0',
  `stav` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  `geom` GEOMETRY DEFAULT NULL,
  PRIMARY KEY (`parcela_kod`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


CREATE TABLE obec (
  `obec_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `okres_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `nazev` VARCHAR(48) NOT NULL,
  `zkratka` VARCHAR(16) NOT NULL,
  `stav` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  `nuts5` VARCHAR(12) NOT NULL,
  `typ_obce` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `pou_kod` INTEGER UNSIGNED DEFAULT NULL,
  `geom` GEOMETRY DEFAULT NULL,
  `helptext` varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (`obec_kod`),
  KEY `okres_kod` (`okres_kod`),
  KEY `nazev` (`nazev`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE objekt (
  `objekt_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `cobce_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `cisdom_typ` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `cisdom_hod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `parcela_kod` BIGINT UNSIGNED NOT NULL DEFAULT '0',
  `druh_konstrukce` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `pocet_bytu` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `pocet_podlazi` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `pripojeni_kanalizace` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `pripojeni_plyn` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `pripojeni_vodovod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `vytah` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `zpusob_vytapeni` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `zpusob_vyuziti` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `datum_dokonceni` VARCHAR(50) NOT NULL DEFAULT '',
  `obestaveny_prostor` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `plocha_zastavena` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `plocha_podlahova` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `stav` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  `mcast_kod` INTEGER UNSIGNED DEFAULT NULL,
  `geom` GEOMETRY DEFAULT NULL,
  PRIMARY KEY (`objekt_kod`),
  KEY `cobce_kod` (`cobce_kod`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE oblast (
  `oblast_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `nuts2` VARCHAR(4) NOT NULL,
  `nazev` VARCHAR(32) NOT NULL,
  `zkratka` VARCHAR(16) NOT NULL,
  `stav` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  `geom` GEOMETRY DEFAULT NULL,
  PRIMARY KEY (`oblast_kod`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


CREATE TABLE pobvod (
  `pobvod_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `obec_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `nazev` VARCHAR(32) NOT NULL,
  `zkratka` VARCHAR(16) NOT NULL,
  `stav` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  `geom` GEOMETRY DEFAULT NULL,
  `helptext` varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (`pobvod_kod`),
  KEY `obec_kod` (`obec_kod`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;



CREATE TABLE ulice (
  `ulice_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `obec_kod` INTEGER UNSIGNED NOT NULL DEFAULT '0',
  `nazev` VARCHAR(48) COLLATE utf8_czech_ci NOT NULL,
  `zkratka` VARCHAR(16) COLLATE utf8_czech_ci NOT NULL,
  `stav` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
  `helptext` varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (`ulice_kod`),
  KEY `obec_kod` (`obec_kod`),
  KEY `nazev` (`nazev`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

-- doplnění zrušeného okresu Praha
INSERT INTO okres (okres_kod, kraj_kod, nazev, zkratka, nuts4, stav, geom, helptext) VALUES (3100, 19, 'Hlavní město Praha', 'Hl. m. Praha', 'CZ010', 1, 0x000000000101000000DC679599D20A494007B7B585E7D52C40, null);
