
--===================
--DOTAZY
--===================
--Dotaz 1
--Jaka je prumerna rozloha parku(v ha) na 1km2 na uzemi Prahy?
SELECT sum(pa.rozloha)/(obce.area/1e6) AS prum_roz
  FROM parky AS pa
  JOIN obce
    ON ST_contains(obce.geom,pa.way)
 GROUP BY obce.nazev, obce.area
HAVING obce.nazev='Praha';
--vysledek 3.4108 ha
--===================
--dotaz 2
--Jaka je celkova vymera(v km2) parku 10 nejvetsich mest?
SELECT sum(pa.rozloha)/1e6 AS rozloha
  FROM parky AS pa
  JOIN obce AS ob_1
    ON ST_contains(ob_1.geom,pa.way)
 WHERE ob_1.nazev in  (SELECT ob_2.nazev
			 FROM obce AS ob_2
		     ORDER BY ob_2.area DESC
			LIMIT 10);
--vysledek 23.34 km2
--===================
--dotaz 3
--Kolik hotelu ma v okruhu 500m nejaky park?
SELECT COUNT(DISTINCT ub.gid)
  FROM ubytovani AS ub
  JOIN parky AS pa
    ON ST_DWithin(pa.way,ub.way,500)
 WHERE ub.typ='hotel';
--vysledek 224
--===================
--dotaz 4
--v kolika parcich je hriste na jine micove hry nez tenis?
SELECT COUNT(DISTINCT pa.gid)
  FROM micove_hry AS mh
  JOIN parky AS pa
    ON ST_contains(pa.way,mh.way)
 WHERE mh.hra!='tennis';
--vysledek 12
--===================
--dotaz 5
--Ve ktere obci lze lyzovat, ubytovat se v hotelu do 500m od sjezdovky a hrat tenis?
SELECT DISTINCT(ob.nazev)
  FROM obce AS ob
  JOIN zimni_sporty AS zs
    ON ST_contains(ob.geom,zs.way)
  JOIN micove_hry AS mh
    ON ST_contains(ob.geom,zs.way)
  JOIN ubytovani AS ub
    ON ST_DWithin(zs.way,ub.way,500)
 WHERE ub.typ = 'hotel'
   AND sport = 'lyzovani'
   AND hra = 'tenis';
--vysledek: Novy Rychnov; Zlin; Velke Karlovice
--===================
--dotaz 6
--Ve kterem kraji je nejmensi pocet (uvedte kolik) moznosti ubytovani?
SELECT ob.nazev_kraje, COUNT(DISTINCT ub.gid) AS pocet
  FROM ubytovani AS ub
  JOIN obce AS ob
    ON ST_contains(ob.geom,ub.way)
 GROUP BY ob.nazev_kraje
 ORDER BY pocet ASC
 LIMIT 1;
--vysledek Pardubicky (PA),24
--===================
--dotaz 7
--Ktere parky na uzemi Prahy ma vesti rozlohu nez obec s nejmensi rozlohou?
SELECT DISTINCT pa.nazev, pa.rozloha
  FROM parky AS pa
  JOIN obce AS ob
    ON ST_intersects(ob.geom,pa.way)
 WHERE ob.nazev = 'Praha'
   AND pa.rozloha>(SELECT ob2.area
		     FROM obce AS ob2
		    ORDER BY ob2.area ASC
		    LIMIT 1);
--vysledek tabulka 7x2
--===================
--dotaz 8
--V kterem parku je nejvice hrist na micove sporty?
--Uvedte kolik.
SELECT pa.nazev,COUNT(pa.gid) AS pocet
  FROM parky AS pa
  JOIN micove_hry AS mh
    ON ST_contains(pa.way,mh.way)
 GROUP BY pa.nazev,pa.gid
 ORDER BY pocet DESC
 LIMIT 1;
--park Luzanky, 3
--===================
--dotaz 9
--Ktera obec ma na svem uzemi park, fotbalove hriste a hokejovy stadion?
SELECT DISTINCT(ob.nazev)
  FROM obce AS ob
  JOIN micove_hry AS mh
    ON ST_contains(ob.geom,mh.way)
  JOIN zimni_sporty AS zs
    ON ST_contains(ob.geom,zs.way)
  JOIN parky AS pa
    ON ST_contains(ob.geom,pa.way)
 WHERE sport = 'hokej'
   AND hra = 'fotbal';
--vysledek tabulka 32x1
--===================
--dotaz 10
--Jake obce najdeme do vzdálenosti 2.5km od nejmene obydlene obce?
SELECT ob1.nazev
  FROM obce AS ob
  JOIN obce AS ob1
    ON ST_DWithin(ob.geom,ob1.geom,2500)
 WHERE ob.gid in (SELECT ob2.gid
		    FROM obce AS ob2
		   ORDER BY obyv02 ASC
		   LIMIT 1);
--vysledek tabulka 40x1
--===================
--dotaz 11
--Jaky kod(nuts4) ma okres s nejvetsim poctem sportovnich zarizeni na micove hry?
SELECT ob.nuts4
  FROM obce AS ob
  JOIN micove_hry AS mh
    ON ST_Contains(ob.geom,mh.way)
 GROUP BY ob.nuts4
 ORDER BY COUNT(ob.nuts4) desc
 LIMIT 1;
--vysledek: CZ0110
--===================
--dotaz 12
--V jakem kraji je nejvice autokempu? Kolik? 2 vysledky
SELECT ob.nazev_kraje, COUNT(DISTINCT ub.gid)
  FROM obce AS ob
  JOIN ubytovani AS ub
    ON ST_Contains(ob.geom,ub.way)
 GROUP BY ob.nazev_kraje, ub.typ
HAVING ub.typ='autokemp'
 ORDER BY COUNT(DISTINCT ub.gid) desc
 LIMIT 2;
 
--vysledek: Vysocina(VY)  4
	    Jihocesky(JC) 4
--===================
--dotaz 13
--Jake ubytovaci zarizeni(id a nazev) ma v okruhu 1km moznost lezeni a hriste na tenis a volejbal?
SELECT DISTINCT(ub.gid),ub.nazev
  FROM ubytovani AS ub
  JOIN lezeni AS le
    ON ST_DWithin(le.way,ub.way,1000)
  JOIN micove_hry AS mh
    ON ST_DWithin(mh.way,ub.way,1000)
 WHERE mh.hra in ('volejbal','tenis');
--vysledek: 509, NH Olomouc Congress; 900,NULL
--===================
--dotaz 14
--Kolik parku se nachází v kraji Vysočina?
SELECT COUNT(pa.gid)
  FROM parky AS pa
  JOIN obce AS ob
    ON ST_Contains(ob.geom,pa.way)
 WHERE ob.nazev_kraje='VY';
--vysledek: 56
--===================
--dotaz 15
--Kolik obci je mensich nez nejvetsi prazsky park?
SELECT COUNT(DISTINCT ob.gid)
  FROM obce AS ob
 WHERE ob.area<(SELECT pa.rozloha
		  FROM parky AS pa
		  JOIN obce AS ob2
		    ON ST_Contains(ob2.geom,pa.way)
		 WHERE ob2.nazev = 'Praha'
		 ORDER BY pa.rozloha DESC
		 LIMIT 1);
--vysledek: 259
--===================
--dotaz 16
--Jake sporty lze provozovat v Moste a kolik hrist k tomu je k dispozici?
SELECT DISTINCT(le.typ) AS typ, COUNT(DISTINCT le.gid)
  FROM obce AS ob
  JOIN lezeni AS le
    ON ST_Contains(ob.geom,le.way)
 GROUP BY typ,ob.nazev
HAVING ob.nazev='Most'
UNION(
SELECT DISTINCT(mh.hra) AS typ, COUNT(DISTINCT mh.gid)
  FROM obce AS ob
  JOIN micove_hry AS mh
    ON ST_Contains(ob.geom,mh.way)
 GROUP BY typ,ob.nazev
HAVING ob.nazev='Most')
UNION(
SELECT DISTINCT(zs.sport) AS typ, COUNT(DISTINCT zs.gid)
  FROM obce AS ob
  JOIN zimni_sporty AS zs
    ON ST_Contains(ob.geom,zs.way)
 GROUP BY typ,ob.nazev
HAVING ob.nazev='Most');
--vysledky: fotbal 2, tenis 6
--===================
--dotaz 17
--V kterych obcich se nachazeji prirodni lezecke steny(skaly)?
SELECT ob.nazev
  FROM obce AS ob
  JOIN lezeni AS le
    ON ST_Contains(ob.geom,le.way)
 WHERE le.typ='skala';
--vysledky:Zduchovice,Zupanovice,Mirkovice,Nova Ves v Horach
--===================
--dotaz 18
--V jakem okrese(nuts4) maji nejvice zimnich stadionu(sport='hokej')? Kolik?
SELECT ob.nuts4, COUNT(DISTINCT zs.gid)
  FROM obce AS ob
  JOIN zimni_sporty AS zs
    ON ST_contains(ob.geom,zs.way)
 GROUP BY ob.nuts4,zs.sport
HAVING zs.sport='hokej'
 ORDER BY COUNT(DISTINCT zs.gid) DESC
 LIMIT 1;
--vysledek: CZ0612,6
