-- *UZPD - tvorba tématických vrstev
-- *skupina C (Dyrc, Těthal)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- CESTA

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- TVORBA VRSTEV
-- 
-- 1) cesko
 
CREATE TABLE cesko AS
SELECT nazev, kod, geom
FROM staty;

ALTER TABLE cesko ADD PRIMARY KEY (kod);

CREATE index sidx_cesko_index ON cesko(geom);

SELECT kod FROM cesko WHERE not st_isvalid(geom);
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 2) kraje
-- 
CREATE TABLE kraje AS
SELECT kod, geom
FROM vusc;

UPDATE kraje SET kod = 3018 WHERE kod = 19;
UPDATE kraje SET kod = 3026 WHERE kod = 27;
UPDATE kraje SET kod = 3034 WHERE kod = 35;
UPDATE kraje SET kod = 3042 WHERE kod = 43;
UPDATE kraje SET kod = 3051 WHERE kod = 51;
UPDATE kraje SET kod = 3069 WHERE kod = 60;
UPDATE kraje SET kod = 3077 WHERE kod = 78;
UPDATE kraje SET kod = 3085 WHERE kod = 86;
UPDATE kraje SET kod = 3093 WHERE kod = 94;
UPDATE kraje SET kod = 3107 WHERE kod = 108;
UPDATE kraje SET kod = 3115 WHERE kod = 116;
UPDATE kraje SET kod = 3123 WHERE kod = 124;
UPDATE kraje SET kod = 3131 WHERE kod = 141;
UPDATE kraje SET kod = 3140 WHERE kod = 132;

ALTER TABLE kraje ADD PRIMARY KEY (kod);

CREATE index kraje_index ON kraje(geom);

SELECT kod FROM kraje WHERE not st_isvalid(geom);
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 3) obce

CREATE TABLE obce AS SELECT kod, geom FROM obce;

ALTER TABLE obce ADD PRIMARY KEY (kod);

CREATE index obce_index ON obce(geom);

SELECT kod FROM obce WHERE not st_isvalid(geom);
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 4) sldb

ogr2ogr -f PGDump sldb.sql -oo AUTODETECT_TYPE=YES -lco SCHEMA=c16 sldb.csv sldb;

psql pgis_uzpd -h geo102.fsv.cvut.cz -U uzpd_c16 -f sldb.sql;

ALTER TABLE sldb ADD PRIMARY KEY (ogc_fid); 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 5) strany

-- PYTHON SKRIPT PRO TVORBU SQL DAVKY

-- from lxml import etree
-- import os
-- 
-- fname = raw_input("File name:")
-- 
-- if os.path.exists(fname) != True:
--     print("Open Failed")
-- else:
--     print "\nInput file: ", fname
-- 
-- xmldoc = etree.parse(fname).getroot()
-- print "CREATE TABLE strany_all (id INT NOT NULL, jmeno CHAR(60)); \n"
-- 
-- for kraj in xmldoc.findall('KRZAST'):
-- 
--     for strana in kraj.findall('STRANA'):
--         cislo = strana.get('KSTRANA')
--         jmeno = strana.get('NAZ_STR')
-- 
--         print "INSERT INTO strany_all (id, jmeno)"
--         print "VALUES (", cislo, ",'",jmeno,"');"
-- 
-- print "CREATE TABLE strany AS SELECT DISTINCT id, jmeno FROM strany_all ORDER BY id ASC; \n"
-- print "DROP TABLE strany_all; \n"
-- print "ALTER TABLE strany ADD PRIMARY KEY (id);"
-- 
-- 
-- tisk = raw_input("Print to file (y/n): ")
-- if tisk == "y":
--     file_name = raw_input("Enter name of text file: ")+'.sql'
--     if os.path.exists(file_name) == True:
--         print "File already exists"
--         file_ex = raw_input("Replace existing file? (y/n): ")
--     else:
--         file_ex = "y"
--     if file_ex == "y":
--         with open(file_name,'w') as new_file:
-- 
--             new_file.write("CREATE TABLE strany_all (id INT NOT NULL UNIQUE, jmeno CHAR(60)); \n")
-- 
--             for kraj in xmldoc.findall('KRZAST'):
-- 
--                 for strana in kraj.findall('STRANA'):
--                     cislo = strana.get('KSTRANA')¬
--                     jmeno = strana.get('NAZ_STR')
-- 
--                     new_file.write("INSERT INTO strany_all (id, jmeno) \n")
--                     new_file.write("VALUES (%s, '%s'); \n" % (cislo, jmeno.encode('utf-8')))
-- 
--             new_file.write("CREATE TABLE strany AS SELECT DISTINCT id, jmeno FROM strany_all ORDER BY id ASC; \n")
--             new_file.write("DROP TABLE strany_all; \n")
--             new_file.write("ALTER TABLE strany ADD PRIMARY KEY (id);")
-- 
--     new_file.close()


psql pgis_uzpd -h geo102.fsv.cvut.cz -U uzpd_c16 -f strany_davka.sql;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 6) kraje2strany
-- 
-- PYTHON SKRIPT PRO TVORBU SQL DAVKY

-- from lxml import etree
-- import os
-- 
-- fname = raw_input("File name:")
-- 
-- if os.path.exists(fname) != True:
--     print("Open Failed")
-- else:
--     print "\nInput file: ", fname
-- 
-- xmldoc = etree.parse(fname).getroot()
-- id= 0;
-- 
-- print "CREATE TABLE kraje2strany (kraje_id int not null unique, strana_id int, hlasy int, proc_hlasu float);"
-- 
-- for okres in xmldoc.findall('KRZAST'):
--     cislo_okresu = okres.get('CIS_KRZAST')
-- 
--     for strana in okres.findall('STRANA'):
--         id_strana = strana.get('KSTRANA')
-- 
--         for data in strana.findall('HODNOTY_STRANA'):
--             hlasy = data.get("HLASY")
--             procento = data.get("PROC_HLASU")
-- 
--         print "INSERT INTO kraje2strany (kraje_id, strana_id, hlasy, proc_hlasu)"
--         print "VALUES (",id_strana,",",cislo_okresu,",",hlasy,",",procento,");"
-- 
-- 
-- tisk = raw_input("Print to file (y/n): ")
-- if tisk == "y":
--     file_name = raw_input("Enter name of text file: ")+'.sql'
--     if os.path.exists(file_name) == True:
--         print "File already exists"
--         file_ex = raw_input("Replace existing file? (y/n): ")
--     else:
--         file_ex = "y"
--     if file_ex == "y":
--         with open(file_name,'w') as new_file:
-- 
--             new_file.write("CREATE TABLE kraje2strany (id INT NOT NULL UNIQUE PRIMARY KEY, kraje_id INT, strana_id INT, hlasy INT, proc_hlasu FLOAT); \n")
-- 
--             for okres in xmldoc.findall('KRZAST'):
--                 cislo_okresu = okres.get('CIS_KRZAST')
-- 
--                 for strana in okres.findall('STRANA'):
--                     id_strana = strana.get('KSTRANA')
-- 
--                     for data in strana.findall('HODNOTY_STRANA'):
--                         hlasy = data.get("HLASY")
--                         procento = data.get("PROC_HLASU")
--                         id += 1
-- 
--                     new_file.write("INSERT INTO kraje2strany (id, kraje_id, strana_id, hlasy, proc_hlasu) \n")
--                     new_file.write("VALUES (%s, %s , %s , %s , %s ); \n" % (id, cislo_okresu, id_strana, hlasy, procento))
-- 
--     new_file.close()
    
psql pgis_uzpd -h geo102.fsv.cvut.cz -U uzpd_c16 -f strany_davka.sql;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- DOTAZY

-- 1) ATRIBUTOVÉ

-- Jaký je kraj s nejmenší volební účastí v Krajských volbách za rok 2016:
--  Ústecký kraj (23%)

select distinct nazev, sum(hlasy)/obyv_celk volebni_ucast
from kraje2strany  k 
join sldb s on k.kraje_id = s.uzkod
where uzcis = 100
group by nazev, obyv_celk
order by volebni_ucast asc limit 1;


-- Jaké jsou tři nejvolenější strany v Krajských volbách pro rok 2016:
-- ANO 2011, ČSSD, KSČM

select jmeno, sum(hlasy) pocet_hlasu
from strany s
join kraje2strany k on s.id = k.strana_id
group by jmeno
order by pocet_hlasu desc limit 3;


-- Jaká je obec s největším a nejmenším počtem obyvatel na jeden byt:
-- Březina (1.5 obyvatel)
-- Všehrdy (14.83 obyvatel)

(select nazev, obyv_celk/obydlene_byty obyv_byt
from sldb
where uzcis = 43
order by obyv_byt asc limit 1)
union
(select nazev, obyv_celk/obydlene_byty obyv_byt
from sldb
where uzcis = 43
order by obyv_byt desc limit 1);

-- Jaké je procento mužů a žen v ČR:
-- 49% mužů, 51% žen

select round(cast(muzi/obyv_celk as numeric), 2) procento_muzu, round(cast(zeny/obyv_celk as numeric), 2) procento_zen
from sldb
where uzcis = 97;

-- V jakých krajích získala Pirátská strana více než 5% hlasů a kolik to bylo:
-- Karlovarský kraj (5.46%)

select nazev, proc_hlasu
from sldb s
join kraje2strany k on s.uzkod = k.kraje_id
join strany st on st.id = k.strana_id
where uzcis = 100 and proc_hlasu > 5 and jmeno = 'Česká pirátská strana';
-- 
-- Jak se jmenuje obec s nejmenším počtem obyvatel v ČR:
-- Březina (3 obyvatele)

select nazev, obyv_celk pocet_obyvatel
from sldb
order by obyv_celk asc limit 1;

-- Jak se jmenuje obec s největší mírou nezaměstnanosti v ČR:
-- Bílá Voda (56%)

select nazev, ((ekonomicky_aktivni - ekonomicky_aktivni_zamestnani)/(ekonomicky_aktivni)) mira_nez
from sldb
where uzcis = 43
order by mira_nez desc limit 1;

-- Jaká strana je volena v kraji, kde je největší procento obyvatel nad 65 let:
-- ANO 2011

select st.jmeno 
from strany st 
join kraje2strany ks on st.id = ks.strana_id 
join sldb s on ks.kraje_id = s.uzkod 
where uzkod = (
	select uzkod 
	from sldb 
	where uzcis = 100 
	order by over65/obyv_celk desc limit 1)
order by hlasy desc limit 1;

-- Jak se jmenují obce s nejmenší mírou nezaměstnanosti v ČR:
-- Líšná, Račice, Kyjov, Březina, Kubova Huť, Vrbice, Třeštice, Jedlany, Kunčina Ves, Zvolenovice, Bácovice, Únice, Dobrá Voda u Pacova, Dubovice, Oplany, Vísky, Eš, Kratušín, Hracholusky, Lesná, Sekeřice, Pustá Rybná, Bratronice, Záhoří, Sedliště, Kostelec, Bečice, Bořetice, Pšánky, Horní Paseka, Minice, Brdy, Vysoká Lhota, Bludov, Zábrdí, Hornosín, Slověnice, Nicov, Rosička, Mlýnské Struhadlo, Kaničky, Řemíčov, Chleby, Krasíkovice, Borovnice, Pivkovice, Řikonín

select nazev, ((ekonomicky_aktivni - ekonomicky_aktivni_zamestnani)/(ekonomicky_aktivni)) mira_nez
from sldb
where uzcis = 43
order by mira_nez asc limit (
	select count(*) from sldb where ((ekonomicky_aktivni - ekonomicky_aktivni_zamestnani)/(ekonomicky_aktivni))  = 
		(select ((ekonomicky_aktivni - ekonomicky_aktivni_zamestnani)/(ekonomicky_aktivni))  mira_nez
		from sldb
		where uzcis = 43
		order by mira_nez asc limit 1));


-- 2) PROSTOROVÉ

-- Jaká je hustota obyvatel v Praze na km2:
-- 2554.48 obyvatel/ km2

select obyv_celk/(st_area(geom)/1000000) hustota_obyvatel
from kraje k 
join sldb s on k.kod = s.uzkod 
where s.nazev = 'Hlavní město Praha' ;


-- Kolik obcí je v kraji s největším obvodem:
-- 973

select count(*) pocet_obci
from obce o
join (select geom
	from kraje 
	order by st_perimeter(geom) desc limit 1) k
on st_within(o.geom, k.geom);

-- Jaká je průměrná míra nezaměstnanosti obcí na hranici ČR:
-- 14.9%

WITH cesko as
(SELECT st_boundary(geom) geom FROM cesko)
SELECT avg((ekonomicky_aktivni - ekonomicky_aktivni_zamestnani)/(ekonomicky_aktivni)) mira_nezam
FROM obce o
JOIN cesko c ON st_intersects(o.geom, c.geom)
JOIN sldb s ON o.kod = s.uzkod;

-- Jaká je vzdálenost mezi obcemi s největším poměrem žen a mužů:
-- 237.94 km

select st_distance(geom,(select geom 
from obce o
join sldb s on o.kod = s.uzkod
order by obyv_celk/muzi desc limit 1))/1000 vzdalenost
from obce o
join sldb s on o.kod = s.uzkod
order by obyv_celk/zeny desc limit 1;

-- Jaká je nejmenší obec v ČR:
-- Závist (0.42 km2)

select s.nazev, st_area(geom)/1000000 rozloha
from sldb s 
join obce o on s.uzkod = o.kod
order by st_area(geom) asc limit 1;

-- Jak se jmenuje obec s největší hustotou obyvatel nad 65 let (nejstarší obec):
-- Strukov

select s.nazev
from obce o
join sldb s on o.kod = s.uzkod
where uzcis = 43 and st_area(geom) is not NULL
order by (over65/obyv_celk)/st_area(geom) desc limit 1;

-- Jaká je obec s nejmenší hustotou zalidnění v ČR:
-- Březina (1.89 obyv/km2)

select s.nazev, obyv_celk/st_area(geom)/1000000 hustota_zalidneni
from sldb s
join obce o on s.uzkod = o.kod
order by  obyv_celk/(st_area(geom)/1000000) asc limit 1;
