-----------------------------------------------------------------------
%% TVORBA DATASETU

%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%% Vytvoreni tabulky obci %%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%

CREATE TABLE b10.sidla
AS SELECT osm_id, name, place, way
FROM czech_point
WHERE place IN ('city','town','village','hamlet');

SELECT Populate_Geometry_Columns('b10.sidla'::regclass);

ALTER TABLE b10.sidla ADD PRIMARY KEY (osm_id);

GRANT SELECT ON b10.sidla TO postgis;

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%% Vytvoreni tabulky nadrazi %%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

CREATE TABLE b10.nadrazi
AS SELECT osm_id, name, ref, way
FROM czech_point
WHERE railway IN ('station', 'halt');

ALTER TABLE b10.nadrazi ADD PRIMARY KEY (osm_id);

SELECT Populate_Geometry_Columns('b10.nadrazi'::regclass);

GRANT SELECT ON b10.nadrazi TO postgis;

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%% Vytvoreni tabulky zeleznic %%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

CREATE TABLE b10.zeleznice
AS SELECT osm_id, ref, way
FROM czech_line
WHERE railway IN ('rail');

ALTER TABLE b10.zeleznice ADD PRIMARY KEY (osm_id);

SELECT Populate_Geometry_Columns('b10.zeleznice'::regclass);

GRANT SELECT ON b10.zeleznice to postgis;

-----------------------------------------------------------------------
%% DOTAZY

%%%%%%%%%%%%%%%%
%% ATRIBUTOVE %%
%%%%%%%%%%%%%%%%

- O kolik zeleznicnich stanic me Praha vice, nez Brno (tzn. zastavky, ktere obsahuji nazev mista)?

SELECT DISTINCT 
	((SELECT count(*)
	  FROM b10.nadrazi
	  WHERE name like 'Praha%') - (SELECT count(*)
				       FROM b10.nadrazi
				       WHERE name like 'Brno%')) AS rozdil
FROM b10.nadrazi;

 rozdil
--------
     40
(1 row)



- Ktere ze stanic ma identifikacni cislo (ref) 565903? S nazvem zobrazte i toto cislo.

SELECT name, ref
FROM b10.nadrazi
WHERE ref = 565903;

  name  |  ref
--------+--------
 Semily | 565903
(1 row)



- Ktere trate v databazi maji trojmistne oznaceni (ref) zacinajici jednickou a kolik maji useku?

SELECT ref, count(*)
FROM b10.zeleznice
GROUP BY ref
HAVING ref BETWEEN 100 AND 199;

 ref | count
-----+-------
 130 |    42
 122 |     2
 132 |    24
(3 rows)




- Ktere obce maji nad 100 000 obyvatel (tzn. oznaceni 'city')? Mesta zobrazte v abecednim poradi.

SELECT name
FROM b10.sidla
WHERE place = 'city'
ORDER BY name;

       name
------------------
 Brno
 CeskÊ Budeovice
 Hradec KrålovÊ
 Liberec
 Olomouc
 Ostrava
 Pardubice
 Plzen
 Praha
 Ustí nad Labem
(10 rows)



%%%%%%%%%%%%%%%%
%% PROSTOROVE %%
%%%%%%%%%%%%%%%%

- Jaka je delka zeleznic v CR v kilometrech? 

SELECT (sum(ST_Length(way))/1e3)::int AS delka_km
FROM b10.zeleznice;

 delka_km
----------
    15991
(1 row)



- Jaka je delka zeleznic v kilometrech okruhu 10 km kolem centra Prahy?

SELECT (sum(ST_Length(zel.way))/1e3)::int AS delka_km
FROM b10.zeleznice AS zel
JOIN (SELECT * 
      FROM b10.sidla
      WHERE name = 'Praha') AS pha
ON st_distance(zel.way,pha.way)<1*1e4;

 delka_km
----------
      370
(1 row)



- Kolik zeleznicnich uzlu (styk aspon 3 linii) se nachazi v okruhu 10 km od nadrazi 'Ostrava-Svinov'?

SELECT count(uzel)
FROM b10.nadrazi AS nadr
JOIN (SELECT uzel 
      FROM (SELECT ST_StartPoint(way) AS uzel
	    FROM b10.zeleznice
            UNION ALL
            SELECT ST_EndPoint(way) AS uzel
            FROM b10.zeleznice) AS uzly
      GROUP BY uzel
      HAVING count(*) >= 3) AS uzly
ON ST_Distance(nadr.way,uzly.uzel)<1e4
WHERE nadr.name = 'Ostrava-Svinov';

 count
-------
     5
(1 row)



- Kolik je zeleznicnich mostu pres reku Labe na uzemi CR?

SELECT count(zel.osm_id)
FROM b10.zeleznice AS zel
JOIN b10.reky AS reky
ON ST_Crosses(zel.way,reky.way)
WHERE reky.name = 'Labe';

 count
-------
    17
(1 row)



- Pres ktere vodni plochy vede zeleznice?

SELECT DISTINCT vp.name
FROM b10.vodni_plochy_naz AS vp
JOIN b10.zeleznice AS zel
ON ST_Intersects(zel.way, vp.way);

          name
------------------------
 Bezdrývka
 Dolejťí Ka�ezský ryb
 Dubový rybník
 Hnakovský ryb.
 Jesenice
 Jesenický ryb.ní nadrz
 Kyjský rní rybník
 MoravaLuz
 Ohre
 Orlickå prehrada
 Podhorník
 Šåreckýní rybník
 Vltava Luz
 v.n. Hracholusky
 Vodni nadrz Lipno
(18 rows)



- Jaka je celkova rozloha vsech vodnich ploch v hektarech?

SELECT round(sum(ST_Area(way))/1e4)
FROM b10.vodni_plochy_naz;

 round
--------
 138619
(1 row)



- Jaka je vzalenost v kilometrech mezi sidly, ktere jsou prvni a posledni v abecede (tzn. maji nazev v databazi)? Vypiste obe obce a vzdalenost mezi nimi.

SELEECT abc, xyz, (ST_Distance(away,zway)/1e3)::int AS vzdal_km
FROM ((SELECT name AS abc, way AS away 
       FROM b10.sidla
       WHERE name IS NOT NULL
       ORDER BY abc
       LIMIT 1) AS aa
       CROSS JOIN (SELECT name AS xyz, way AS zway
                   FROM b10.sidla
                   WHERE name IS NOT NULL
                   ORDER BY xyz DESC
                   LIMIT 1) AS zz) AS az;

   abc    |  xyz  | vzdal_km
----------+-------+----------
 Abertamy | Zvůle |      347
(1 row)
