/*
UZPD
skupina d13
Lyzarske vleky, jejich dostupnost a moznost ubytovani v jejich okoli
Stepan Kaiser, David Hanousek
2013
*/

-- nastaveni vyhledavaci cesty cesty
-------------------------------------------------------------------------------------



-- TVORBA VRSTEV


-- VYTVORENI LINIOVE VRSTVY VLEKY

CREATE TABLE 	vleky 
AS SELECT 	osm_id, geom, aerialway AS typ  
FROM 		czech_line 
WHERE 		aerialway IN ('cable_car', 'gondola', 'chair_lift', 'mixed_lift', 't-bar', 'j-bar', 'drag_lift');

-- odstraneni vleku mimo uzemi cr
DELETE FROM 	vleky
WHERE 		vleky.geom NOT IN ( 	SELECT 	vleky.geom 
					FROM 	vleky 
					JOIN 	kraje 
					ON 	ST_Within(vleky.geom,kraje.geom));


-- nastaveni primarniho klice
ALTER TABLE 	vleky 
ADD PRIMARY KEY (osm_id);

-- nastaveni prostoroveho klice
CREATE INDEX 	vleky_geom 
ON 		vleky 
USING 		gist (geom);

-- pridani sloupce delka
ALTER TABLE 	vleky 
ADD 		delka float;

-- vypocet delky
UPDATE 	vleky
SET 	delka = ST_LENGTH(geom);

-- odstraneni "logicky" nesmyslnych vleku (1 typu poma o delce cca 1 m, 1 typu lanovka o delce 45 m)
DELETE FROM 	vleky
WHERE		delka < 50;

-- prejmenovani atributu ve sloupci typ
UPDATE vleky SET typ='lanovka' WHERE typ='cable_car';
UPDATE vleky SET typ='gondola' WHERE typ='gondola';
UPDATE vleky SET typ='sedackova_lanovka' WHERE typ='chair_lift';
UPDATE vleky SET typ='smisena_lanovka' WHERE typ='mixed_lift';
UPDATE vleky SET typ='poma' WHERE typ='t-bar';
UPDATE vleky SET typ='poma' WHERE typ='j-bar';
UPDATE vleky SET typ='kotva' WHERE typ='drag_lift';


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

-- VYTVORENI BODOVE VRSTVY OBCE

CREATE TABLE 	obce 
AS SELECT 	ogc_fid, kodob, nazob_eng, nazorp_eng, nk, geom FROM obce_b;

-- nastaveni primarniho klice
ALTER TABLE 	obce 
ADD PRIMARY KEY (ogc_fid);


-- vytvoreni prostoroveho indexu
CREATE INDEX 	obce_geom 
ON 		obce 
USING 		gist (geom);

-- transformace
ALTER TABLE 	obce 
RENAME COLUMN 	geom 
TO 		geom_beta;
 
SELECT AddGeometryColumn('obce', 'geom', 900913, 'point', 2);

UPDATE 	obce 
SET 	geom = ST_Transform(geom_beta, 900913);

SELECT DropGeometryColumn('obce','geom_beta');

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

-- VYTVORENI POLYGONOVE VRSTVY KRAJE

CREATE TABLE 	obce_pol 
AS SELECT 	ogc_fid, nazev, geom, nk FROM gis1.obce;

CREATE TABLE kraje (ogc_fid SERIAL PRIMARY KEY, nazev varchar(250), geom geometry); 

INSERT INTO	kraje (geom,nazev)
SELECT 		ST_Union(obce_pol.geom) AS geom, obce_pol.nk AS nazev
FROM 		obce_pol
GROUP BY 	nk;

-- vytvoreni prostoroveho indexu
CREATE INDEX 	kraje_geom 
ON 		kraje 
USING 		gist (geom);

-- odstraneni tabulky obce_pol
DROP TABLE obce_pol;

-- prevod na multipolygon
CREATE TABLE 	kraje_beta 
AS SELECT 	nazev, ST_Union(geom) AS geom 
FROM 		kraje 
GROUP BY 	nazev;

CREATE TABLE 	kraje_gama 
AS SELECT 	* 
FROM 		kraje;

DELETE FROM 	kraje_gama 
WHERE 		ctid 
NOT IN 		(SELECT MAX(ctid) FROM kraje GROUP BY nazev);

SELECT DropGeometryColumn('kraje_gama', 'geom');

SELECT AddGeometryColumn('kraje_gama', 'geom', 2065, 'MultiPolygon', 2);

UPDATE 		kraje_gama AS a 
SET 		geom = ST_Multi(b.geom) 
FROM 		kraje_beta AS b WHERE a.nazev = b.nazev;

DROP TABLE kraje_beta;

ALTER TABLE 	kraje_gama 
RENAME TO 	kraje_kraje;

DROP TABLE kraje;

ALTER TABLE 	kraje_kraje 
RENAME TO 	kraje;

-- transformace
ALTER TABLE 	kraje 
RENAME COLUMN 	geom 
TO 		geom_beta;
 
SELECT AddGeometryColumn('kraje', 'geom', 900913, 'multipolygon', 2);

UPDATE 		kraje 
SET 		geom = ST_Transform(geom_beta, 900913);

SELECT DropGeometryColumn('kraje','geom_beta');

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

-- VYTVORENI POLYGONOVE VRSTVY OBCE


CREATE TABLE 	obce_pol1 
AS SELECT 	kodob, ST_Union(geom) AS geom 
FROM 		gis1.obce 
GROUP BY 	kodob;

-- zkopirovani tabulky obce a priprava sloupecku multipolygonu
CREATE TABLE 	obce_pol2 
AS SELECT 	* 
FROM 		gis1.obce;

DELETE FROM 	obce_pol2 
WHERE 		ctid 
NOT IN 		(	SELECT 		MAX(ctid) 
			FROM 		gis1.obce 
			GROUP BY 	kodob);

-- Pridano az po zjisteni chyby ve validaci dat nize (odstranuje ring self-intersection)
UPDATE 	obce_pol1
SET 	geom=ST_Buffer(geom, 0.0);
			
SELECT DropGeometryColumn('obce_pol2', 'geom');
SELECT AddGeometryColumn('obce_pol2', 'geom', 2065, 'MultiPolygon', 2);


UPDATE 	obce_pol2 AS a 
SET 	geom = ST_Multi(b.geom) 
FROM 	obce_pol1 AS b 
WHERE 	a.kodob = b.kodob;


DROP TABLE 	obce_pol1;
ALTER TABLE 	obce_pol2 
RENAME TO 	obce_pol;

-- nastaveni primarniho klice 
ALTER TABLE 	obce_pol 
ADD PRIMARY KEY	(ogc_fid);

-- transformace
ALTER TABLE 	obce_pol 
RENAME COLUMN 	geom 
TO 		geom_beta;
 
SELECT AddGeometryColumn('obce_pol', 'geom', 900913, 'multipolygon', 2);

UPDATE 	obce_pol 
SET 	geom = ST_Transform(geom_beta, 900913);

SELECT DropGeometryColumn('obce_pol','geom_beta');

-- vytvoreni prostoroveho indexu
CREATE INDEX 	obce_pol_geom 
ON 		obce_pol 
USING 		gist (geom);

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

-- VYTVORENI LINIOVE VRSTVY LYZARSKE_CESTY

CREATE TABLE 	lyzarske_cesty 
AS SELECT 	osm_id, geom, name AS nazev 
FROM 		czech_line 
WHERE 		route IN ('piste', 'ski');

-- nastaveni prostoroveho klice
CREATE INDEX 	lyzarske_cesty_geom 
ON 		lyzarske_cesty 
USING 		gist (geom);

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

-- VYTVORENI POLYGONOVE VRSTVY LYZOVANI

CREATE TABLE 	lyzovani 
AS SELECT 	osm_id, geom 
FROM 		czech_polygon 
WHERE 		sport IN ('skiing');

-- nastaveni primarniho klice
ALTER TABLE 	lyzovani 
ADD PRIMARY KEY (osm_id);

-- nastaveni prostoroveho klice
CREATE INDEX 	lyzovani_geom 
ON 		lyzovani 
USING 		gist (geom);

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

-- VYTVORENI BODOVE VRSTVY VLAKY

CREATE TABLE 	vlaky 
AS SELECT 	ogc_fid, nazev_eng AS nazev, geom 
FROM 		zeleznice_stanice;

-- nastaveni primarniho klice
ALTER TABLE 	vlaky 
ADD PRIMARY KEY (ogc_fid);


-- vytvoreni prostoroveho indexu
CREATE INDEX 	vlaky_geom 
ON 		vlaky 
USING 		gist (geom);

-- transformace
ALTER TABLE 	vlaky 
RENAME COLUMN 	geom 
TO 		geom_beta;
 
SELECT AddGeometryColumn('vlaky', 'geom', 900913, 'point', 2);

UPDATE 	vlaky 
SET 	geom = ST_Transform(geom_beta, 900913);

SELECT DropGeometryColumn('vlaky','geom_beta');

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


-- VYTVORENI BODOVE VRSTVY UBYTOVANI

CREATE TABLE 	ubytovani 
AS SELECT 	osm_id, geom, name AS nazev, tourism AS typ 
FROM 		czech_point 
WHERE 		tourism IN ('chalet', 'guest_house', 'hostel', 'hotel');

--tvorba bodove vrstvy z polygonu
CREATE  TABLE 	ubytovani_pol
AS SELECT 	osm_id, geom, name AS nazev, tourism AS typ
FROM 		czech_polygon AS cp
WHERE 		tourism IN ('chalet', 'guest_house', 'hostel', 'hotel')
AND 		osm_id NOT IN (	SELECT	cz_p.osm_id 
	                        FROM 	ubytovani AS ub
	                        JOIN 	czech_polygon AS cz_p
	                        ON 	ST_contains(cz_p.geom,ub.geom)
	                        WHERE 	cz_p.tourism IN ('chalet', 'guest_house', 'hostel', 'hotel'));

CREATE TABLE	ubytovani_bod
AS SELECT	osm_id, ST_Centroid (ubytovani_pol.geom) AS geom, nazev, typ
FROM		ubytovani_pol; 

INSERT INTO 	ubytovani(osm_id, geom, nazev, typ) 
SELECT 		* 
FROM 		ubytovani_bod;

DROP TABLE	ubytovani_pol;
DROP TABLE	ubytovani_bod;


-- nastaveni primarniho klice (po zmene dat uz nebylo mozne pouzit osm_id)
ALTER TABLE ubytovani ADD column gid serial;
ALTER TABLE ubytovani ADD primary key (gid);

-- nastaveni prostoroveho klice
CREATE INDEX 	ubytovani_geom 
ON 		ubytovani 
USING 		gist (geom);

UPDATE ubytovani SET typ='apartman' WHERE typ='guest_house';
UPDATE ubytovani SET typ='chalupa' WHERE typ='chalet';

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

-- VYTVORENI LINIOVE VRSTVY ZELEZNICE

CREATE TABLE 	zeleznice 
AS SELECT 	ogc_fid, geom 
FROM 		zeleznice;

-- nastaveni primarniho klice
ALTER TABLE 	zeleznice 
ADD PRIMARY KEY (ogc_fid);


-- vytvoreni prostoroveho indexu
CREATE INDEX 	zeleznice_geom 
ON 		zeleznice 
USING 		gist (geom);

-- transformace
ALTER TABLE 	zeleznice 
RENAME COLUMN 	geom 
TO 		geom_beta;
 
SELECT AddGeometryColumn('zeleznice', 'geom', 900913, 'linestring', 2);

UPDATE 	zeleznice
SET 	geom = ST_Transform(geom_beta, 900913);

SELECT DropGeometryColumn('zeleznice','geom_beta');

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

-- KONTROLA DAT

-- VLEKY (0 chyb)
SELECT 		osm_id, ST_IsValidReason(geom) 
FROM 		vleky 
WHERE NOT 	ST_IsValid(geom);

-- OBCE (0 chyb)
SELECT 		ogc_fid, ST_IsValidReason(geom) 
FROM 		obce 
WHERE NOT 	ST_IsValid(geom);

-- KRAJE (0 chyb)
SELECT 		ogc_fid, ST_IsValidReason(geom) 
FROM 		kraje 
WHERE NOT 	ST_IsValid(geom);

-- OBCE_POL (1 chyba), po opravenem postupu tvorby teto vrstvy 0 chyb
SELECT 		ogc_fid, ST_IsValidReason(geom) 
FROM 		obce_pol 
WHERE NOT 	ST_IsValid(geom);


--LYZARSKE CESTY, LYZOVANI ___ PRAVDEPODOBNE BUDE ZRUSENO, JEN PRO ILUSTRACI

-- VLAKY (0 chyb)
SELECT 		ogc_fid, ST_IsValidReason(geom) 
FROM 		vlaky 
WHERE NOT 	ST_IsValid(geom);

-- UBYTOVANI (0 chyb)
SELECT 		osm_id, ST_IsValidReason(geom) 
FROM 		ubytovani 
WHERE NOT 	ST_IsValid(geom);

-- ZELEZNICE (0 chyb)
SELECT 		ogc_fid, ST_IsValidReason(geom) 
FROM 		zeleznice 
WHERE NOT 	ST_IsValid(geom);

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

-- AKTUALIZACE METADAT
SELECT populate_geometry_columns('d13.vleky'::regclass);
SELECT populate_geometry_columns('d13.obce'::regclass);
SELECT populate_geometry_columns('d13.obce_pol'::regclass);
SELECT populate_geometry_columns('d13.kraje'::regclass);
SELECT populate_geometry_columns('d13.vlaky'::regclass);
SELECT populate_geometry_columns('d13.ubytovani'::regclass);
SELECT populate_geometry_columns('d13.zeleznice'::regclass);

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

-- TVORBA TOPOLOGIE

SELECT CreateTopology ('topo_vleky');
SELECT CreateTopology ('topo_obce');
SELECT CreateTopology ('topo_obce_pol');
SELECT CreateTopology ('topo_kraje');
SELECT CreateTopology ('topo_vlaky');
SELECT CreateTopology ('topo_ubytovani');
SELECT CreateTopology ('topo_zeleznice');

SELECT AddTopoGeometryColumn ('topo_vleky', 'd13', 'vleky', 'topo', 'POLYLINE');
SELECT AddTopoGeometryColumn ('topo_obce', 'd13', 'obce', 'topo', 'POINT');
SELECT AddTopoGeometryColumn ('topo_obce_pol', 'd13', 'obce_pol', 'topo', 'POLYGON');
SELECT AddTopoGeometryColumn ('topo_kraje', 'd13', 'kraje', 'topo', 'POLYGON');
SELECT AddTopoGeometryColumn ('topo_vlaky', 'd13', 'vlaky', 'topo', 'POINT');
SELECT AddTopoGeometryColumn ('topo_ubytovani', 'd13', 'ubytovani', 'topo', 'POINT');
SELECT AddTopoGeometryColumn ('topo_zeleznice', 'd13', 'zeleznice', 'topo', 'POLYLINE');

-- vleky OK
UPDATE vleky 		SET topo = toTopoGeom (geom, 'topo_vleky', 1);

-- obce OK
UPDATE obce 		SET topo = toTopoGeom (geom, 'topo_obce', 1);

-- obce_pol OK (dobehl cca po 11 minutach)
UPDATE obce_pol 	SET topo = toTopoGeom (geom, 'topo_obce_pol', 1);

-- kraje OK
UPDATE kraje 		SET topo = toTopoGeom (geom, 'topo_kraje', 1);

-- vlaky OK
UPDATE vlaky 		SET topo = toTopoGeom (geom, 'topo_vlaky', 1);

-- ubytovani OK
UPDATE ubytovani 	SET topo = toTopoGeom (geom, 'topo_ubytovani', 1);

-- zeleznice OK
UPDATE zeleznice 	SET topo = toTopoGeom (geom, 'topo_zeleznice', 1);


-- Kontrola dat

SELECT TopologySummary 	('topo_vleky');
SELECT ValidateTopology ('topo_vleky');

SELECT TopologySummary 	('topo_obce');
SELECT ValidateTopology ('topo_obce');

SELECT TopologySummary 	('topo_kraje');
SELECT ValidateTopology ('topo_kraje');

SELECT TopologySummary 	('topo_vlaky');
SELECT ValidateTopology ('topo_vlaky');

SELECT TopologySummary 	('topo_ubytovani');
SELECT ValidateTopology ('topo_ubytovani');

SELECT TopologySummary 	('topo_obce_pol');
SELECT ValidateTopology ('topo_obce_pol');

SELECT TopologySummary 	('topo_zeleznice');
SELECT ValidateTopology ('topo_zeleznice');

--Vse vporadku.


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

-- DMT

-- TVORBA DMT

CREATE TABLE 	d13.dmt
AS SELECT 	*
FROM		dmt;

-- Pridani udaju o zacatecni vysce a koncove vysce pro vsechny sjezdovky


/*
SELECT 		St_Transform (rast, 900913) 
FROM 		dmt
nepoustet, trva asi 45 minut*/

-- jednodussi je prevest vleky do S-JTSK

CREATE TABLE 	vleky_jtsk
AS SELECT	*
FROM		vleky;

ALTER TABLE 	vleky_jtsk 
RENAME COLUMN 	geom 
TO 		geom_beta;
 
SELECT 		AddGeometryColumn('vleky_jtsk', 'geom', 2065, 'linestring', 2);

UPDATE 		vleky_jtsk 
SET 		geom = ST_Transform(geom_beta, 2065);

SELECT 		DropGeometryColumn('vleky_jtsk','geom_beta');

ALTER TABLE 	vleky_jtsk
ADD COLUMN 	sp GEOMETRY('point', 2065);

ALTER TABLE 	vleky_jtsk
ADD COLUMN 	ep GEOMETRY('point', 2065); 

UPDATE 		vleky_jtsk
SET		sp = ST_StartPoint(geom);

UPDATE 		vleky_jtsk
SET		ep = ST_EndPoint(geom);

ALTER TABLE	vleky_jtsk
ADD COLUMN 	sp_vyska FLOAT;

ALTER TABLE	vleky_jtsk
ADD COLUMN 	ep_vyska FLOAT;

UPDATE		vleky_jtsk
SET 		sp_vyska = st_value(rast, sp)
FROM 		dmt;

UPDATE		vleky_jtsk
SET 		ep_vyska = st_value(rast, ep)
FROM 		dmt;

ALTER TABLE	vleky
ADD COLUMN	sp_vyska float;

ALTER TABLE	vleky
ADD COLUMN	ep_vyska float;

UPDATE		vleky AS v1
SET		sp_vyska = v2.sp_vyska
FROM		vleky_jtsk AS v2
WHERE		v1.osm_id = v2.osm_id;

UPDATE		vleky AS v1
SET		ep_vyska = v2.ep_vyska
FROM		vleky_jtsk AS v2
WHERE		v1.osm_id = v2.osm_id;

DROP TABLE	vleky_jtsk;

ALTER TABLE	vleky
ADD COLUMN	prevyseni float;

UPDATE		vleky
SET		prevyseni = ABS(sp_vyska - ep_vyska); 

ALTER TABLE	vleky
ADD COLUMN	sklon float;

UPDATE		vleky
SET		sklon = (prevyseni/delka)*100; 


-- musi se pocitat s tim ze diky tomu ze rastr je rozliseni 60*90 m vzniknou i svahy s nulovym prevysenim, vysledky jsou
jen orientacni

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

-- PGROUTING (DIJKSTRA only)


CREATE VIEW 	zeleznice_ext AS
SELECT 		*, st_startpoint(geom), st_endpoint(geom)
FROM 		zeleznice;

CREATE TABLE 	node 
AS SELECT 	row_number() OVER (ORDER BY foo.p)::integer AS id, 
          	foo.p AS geom
FROM 		(         
      			SELECT DISTINCT st_startpoint AS p FROM zeleznice_ext
      			UNION
      			SELECT DISTINCT st_endpoint AS p FROM zeleznice_ext
   		) foo
GROUP BY 	foo.p;


CREATE TABLE 	zeleznice_network 
AS SELECT 	a.*, b.id as start_id, c.id as end_id
FROM 		zeleznice_ext AS a
JOIN 		node AS b 
ON 		a.st_startpoint = b.geom
JOIN 		node AS c 
ON 		a.st_endpoint = c.geom;

ALTER TABLE 	zeleznice_network
RENAME COLUMN 	ogc_fid TO gid;


ALTER TABLE 	zeleznice_network
RENAME COLUMN 	geom TO the_geom;

ALTER TABLE 	zeleznice_network
RENAME COLUMN 	start_id TO source;

ALTER TABLE 	zeleznice_network
RENAME COLUMN 	end_id TO target;

ALTER TABLE	zeleznice_network
ADD COLUMN	length float;

UPDATE		zeleznice_network
SET		length = st_distance(st_startpoint, st_endpoint);


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


-- DOTAZY

-- A) ATRIBUTOVE DOTAZY:--

-- pozn: aby bylo tento projekt mozno realizovat, pro zjednoduseni uvazuji vlek/lanovka = sjedovka

-- 1) Kolik je na uzemi CR sjezdovek delsich nez 2km?

SELECT 	delka, typ 
FROM 	vleky
WHERE 	delka > 2000;

-- 22

-- 2) Ktere vesnice v Olomouckem kraji zacinaji na pismeno U?

SELECT 	nazob_eng
FROM 	obce
WHERE 	nk = 'OL' AND nazob_eng LIKE 'U%';

--Uhelna, Ujezd, Unicov, Ustin, Urcice, Uhricice, Usti, Usov

-- 3) Kolik ubytovacich zarizeni z celkoveho poctu ma v databazi ulozen nazev?

SELECT 	count(*)
FROM 	ubytovani
WHERE 	nazev IS NOT NULL;

SElECT 	count(*)
FROM	ubytovani;

--2297/2477 (93%)

-- 4) Ve kterem kraji se nachazi nejvice obci?

SELECT 		nk, count(*) AS pocet
FROM 		obce
GROUP BY 	nk
ORDER BY 	pocet DESC
LIMIT 		1;

--ST - Stredocesky kraj (1146)

-- B) PROSTOROVE DOTAZY

-- 1) Vyjmenujte 5 obci s nejvetsim poctem ubytovacich zarizeni (+jejich pocet).

SELECT 		nazev_eng AS mesto, count (DISTINCT u.osm_id) AS pocet
FROM 		obce_pol AS o
JOIN 		ubytovani AS u
ON 		ST_Contains(o.geom, u.geom)
GROUP BY 	mesto
ORDER BY 	pocet DESC
LIMIT 		5;

-- Praha(286), Spindleruv Mlyn(114), Brno(53), Luhacovice(47), Bozi Dar(41)


-- 2) Ktere ubytovaci lezi nejblize nejake sjezdovce? A ve ktere obci lezi?

SELECT 		ROUND(ST_Distance(u.geom, v.geom)) AS vzdalenost, u.nazev, u.typ
FROM 		ubytovani AS u
JOIN		vleky AS v
ON		ST_Disjoint(u.geom, v.geom)
ORDER BY	vzdalenost
LIMIT		1;

SELECT 	DISTINCT nazev_eng AS obec
FROM	obce_pol
JOIN	ubytovani
ON 	ST_Contains(obce_pol.geom, (SELECT ubytovani.geom FROM ubytovani WHERE nazev='Apartmán Novako'));

-- Apartmán Novako, 26m od sjezdovky, obec Bozi Dar

-- 3) Ktera zeleznicni stanice je nejvhodnejsi vystupni stanice pro lyzare? (V jejim 2km okoli lezi nejvice sjezdovek?)

-- pred optimalizaci 	(ani po 5 minutach nedobehlo)

SELECT 		nazev, count(nazev) AS pocet_sjezdovek
FROM 		vlaky
JOIN   		vleky
ON   		ST_Intersects(vleky.geom, ST_Buffer(vlaky.geom, 2e3))
GROUP BY	nazev
ORDER BY	pocet_sjezdovek DESC
LIMIT		2 

-- po optimalizaci 	(83 ms)

SELECT 		nazev, count(nazev) AS pocet_sjezdovek
FROM 		vlaky
JOIN   		vleky
ON     		ST_Expand(vlaky.geom, 2e3) && vleky.geom   
AND    		ST_Intersects(vleky.geom, ST_Buffer(vlaky.geom, 2e3))
GROUP BY	nazev
ORDER BY	pocet_sjezdovek DESC
LIMIT		2 

-- Velke Karlovice (11 sjezdovek do 2 km), Velke Karlovice zast. (10 sjezdovek do 2 km)

-- 4) Na uzemi ktereho kraje je nejvetsi pomer obyvatel na pocet ubytovacich zarizeni?


CREATE TABLE 	pomoc
AS SELECT	kraje.nazev, 
		COUNT(ubytovani.nazev) AS pocet
		FROM		kraje
		JOIN		ubytovani
		ON		ST_Contains (kraje.geom, ubytovani.geom)
		GROUP BY	kraje.nazev;
ALTER TABLE	pomoc
ADD COLUMN	obyvatele float;
UPDATE		pomoc	
SET		obyvatele = (	SELECT		SUM(obyv02)
				FROM		obce_pol
				WHERE		pomoc.nazev = obce_pol.nk
				GROUP BY	nk)
FROM 		obce_pol
WHERE		pomoc.nazev = obce_pol.nk;

SELECT		nazev, obyvatele/pocet AS foo
FROM		pomoc
ORDER BY	foo DESC;

DROP TABLE	pomoc;

-- Pardubický kraj

-- 5) Existuje v CR nejaka sjezdovka, ktera se nachazi na uzemi dvou kraju/obci?

SELECT   	COUNT(DISTINCT osm_id)
FROM     	vleky
JOIN     	kraje
ON       	ST_Crosses(vleky.geom, ST_Boundary(kraje.geom));

-- na uzemi dvou kraju ne

-- 6) Nejblize, ktere obci (bod), zeleznicni zastavce a ubytovacimu zarizeni se nachazi sjezdovka s nejvetsim prevysenim?

SELECT 		osm_id 
FROM 		vleky
ORDER BY 	prevyseni desc
LIMIT 		2

-- limit 2, protoze prvni nema urcene prevyseni (27556621)

SELECT 		nazob_eng, St_Distance(o.geom, v.geom) AS vzdalenost
FROM		obce AS o
JOIN		vleky AS v
ON		v.osm_id = 27556621
ORDER BY	vzdalenost
LIMIT		1;

SELECT 		nazev, St_Distance(o.geom, v.geom) AS vzdalenost
FROM		vlaky AS o
JOIN		vleky AS v
ON		v.osm_id = 27556621
ORDER BY	vzdalenost
LIMIT		1;

SELECT 		nazev, o.typ,  St_Distance(o.geom, v.geom) AS vzdalenost
FROM		ubytovani AS o
JOIN		vleky AS v
ON		v.osm_id = 27556621 AND nazev LIKE '%'
ORDER BY	vzdalenost
LIMIT		1;


-- Rokytnice nad Jizerou, zeleznicni stanice Rokytnice nad Jizerou a nejlepe se ubytovat v hotelu Rokytka

-- 7) Kde se ubytovat pokud chceme lyzovat na nejprudsi sjezdovce ve vzdalenosti do 100 km od Prahy, ktera ma alespon 1500m ?



SELECT 		osm_id
FROM 		vleky
JOIN	   	obce
ON     		ST_Expand(obce.geom, 1.5e5) && vleky.geom   
AND		nazob_eng = 'Praha'
AND    		ST_Intersects(vleky.geom, ST_Buffer(obce.geom, 1.5e5))
AND 		delka > 1500
ORDER BY	sklon DESC
LIMIT		1;

--28080356

SELECT 		nazev, o.typ,  St_Distance(o.geom, v.geom) AS vzdalenost
FROM		ubytovani AS o
JOIN		vleky AS v
ON		v.osm_id = 28080356 AND nazev LIKE '%'
ORDER BY	vzdalenost
LIMIT		1;

--Hotel Ještěd


SELECT 		o.nazev_eng
FROM 		obce_pol AS o
JOIN 		ubytovani AS u
ON		ST_Contains(o.geom, u.geom)
AND		u.nazev = 'Hotel Ještěd'

-- obec Svetla pod Jestedem (skiareal Jested), Hotel Ještěd


-- 2-3 dotazy s pouzitim rastru + 2-3 na PGrouting(pokud dnes udelame)

-- 8) Kolik procent uzemi se nachazi v nadmorske vysce nad 1350m.

CREATE TABLE 	dmt1350 
AS SELECT 	ST_MapAlgebraExpr(rast, 1, NULL, 'CASE WHEN [rast] > 1350 THEN 1 ELSE 0 END') AS rast
FROM   		dmt;

SELECT 		ST_ValueCount(rast) from dmt1350;
DROP TABLE	dmt1350;


-- 5181 bunek (1 bunka = 0,54ha) 2797,74 ha
-- funkce Slope nedobehne (out of memory)

-- 9) Jaká je vymera uzemi, ktere je ve vzdalenosti 2000m od jakekoliv sjezdovky a nachazi se v horach (nad 1000 mnm)?

CREATE TABLE 	vleky_jtsk
AS SELECT	*
FROM		vleky;

ALTER TABLE 	vleky_jtsk 
RENAME COLUMN 	geom 
TO 		geom_beta;
 
SELECT 		AddGeometryColumn('vleky_jtsk', 'geom', 2065, 'linestring', 2);

UPDATE 		vleky_jtsk 
SET 		geom = ST_Transform(geom_beta, 2065);

SELECT 		DropGeometryColumn('vleky_jtsk','geom_beta');

CREATE TABLE 	vleky_buff 
AS SELECT 	ST_Union(ST_Buffer(geom, 2000)) AS geom
FROM   		vleky_jtsk;

SELECT (ST_SummaryStats(rast)).* AS stats
FROM   dmt;

CREATE TABLE 	dmt1000 
AS SELECT	ST_Reclass(rast, 1, '1000-1594:1', '4BUI') AS rast
FROM   		dmt;	

CREATE TABLE 	dmt_vleky_buff 
AS SELECT 	ST_Intersection(rast, geom) as geomval
FROM   		dmt1000
CROSS JOIN 	vleky_buff;

CREATE TABLE 	dmt_vleky_geom
AS SELECT 	(gv).geom AS geom, (gv).val AS val
FROM
(
SELECT 		geomval AS gv
FROM 		dmt_vleky_buff
) AS 		foo;

SELECT 		SUM(ST_Area(geom))
FROM 		dmt_vleky_geom
WHERE		val = 1;

DROP TABLE	vleky_jtsk;
DROP TABLE	vleky_buff;
DROP TABLE	dmt1000;
DROP TABLE	dmt_vleky_buff;
DROP TABLE	dmt_vleky_geom;
--28134 ha

-- 10) Jakou zeleznicni cestou jet, pokud se chci dostat k nejblizsi sjezdovce z Olomouce, ktera je alespon 1000m dlouha?

SELECT 		osm_id, ST_Distance(v.geom, o.geom) AS vzd
FROM 		vleky AS v
JOIN		obce AS o
ON		o.nazob_eng = 'Olomouc'
AND		delka > 2500
ORDER BY 	vzd;

-- (91641416)

SELECT 		nazev, ST_Distance(s.geom, v.geom) AS vzd
FROM 		vlaky AS s
JOIN		vleky AS v
ON		v.osm_id = 91641416
ORDER BY 	vzd
LIMIT		1;

-- (Kouty nad Desnou)

SELECT 		z.source, ST_Distance(z.the_geom, v.geom) AS vzd
FROM 		zeleznice_network AS z
JOIN		vlaky AS v
ON		v.nazev = 'Kouty nad Desnou'
ORDER BY 	vzd
LIMIT		1;

-- target id = 594

SELECT 		z.source, nazev, ST_Distance(z.the_geom, v.geom) AS vzd
FROM 		zeleznice_network AS z
JOIN		vlaky AS v
ON		v.nazev LIKE 'Olom%'
ORDER BY 	vzd
LIMIT		1;

-- source id: 635

SELECT * FROM shortest_path('SELECT gid as id, source, target, length as cost from zeleznice_network', 635, 594, FALSE, FALSE);

CREATE TABLE 	olomouc_kouty 
AS SELECT 	gid, the_geom 
FROM 		dijkstra_sp('zeleznice_network', 635, 594);

-- 11) Kolik ubytovacich zarizeni se nachazi do 2 km od zeleznicni trati Praha - Olomouc? A ktere je nejblize kolejim?

-- source id 635 (z predchozi ulohy)

SELECT 		z.source,nazev, ST_Distance(z.the_geom, v.geom) AS vzd
FROM 		zeleznice_network AS z
JOIN		vlaky AS v
ON		v.nazev = 'Praha hl.n.'
ORDER BY 	vzd
LIMIT		1;

--target id 258

CREATE TABLE 	olomouc_praha 
AS SELECT 	gid, the_geom 
FROM 		dijkstra_sp('zeleznice_network', 635, 258);

CREATE TABLE 	trat_buff 
AS SELECT 	ST_Union(ST_Buffer(the_geom, 2000)) AS geom
FROM   		olomouc_praha;

SELECT 		count(nazev)
FROM 		ubytovani as u
JOIN   		trat_buff as t
ON    		ST_Intersects(u.geom, t.geom);

SELECT 		nazev,typ, ST_Distance(u.geom, z.geom) AS vzd
FROM 		ubytovani as u
JOIN   		trat_buff as t
ON    		ST_Intersects(u.geom, t.geom)
JOIN		zeleznice as z
ON		ST_Distance(z.geom, u.geom) > 0
ORDER BY	vzd
LIMIT		1;

SELECT 		nazev_eng 
FROM 		obce_pol AS o
JOIN		ubytovani AS u
ON		ST_Within(u.geom, o.geom)
WHERE		u.nazev = 'Poprad'



--113, nejblize je hotel Poprad, Usti nad Orlici










