/*
* -------------------------------------------------------------------- *
* Uvod do zpracovani prostorovych dat - semestralni projekt
* zimni semestr 2015/2016
* autor: Jaromir Rokusek
* schema: b15
* -------------------------------------------------------------------- *
*/

---------------------------------------------------------------------------------------
-- ZELEZNICNI STANICE
---------------------------------------------------------------------------------------
-- POLYGONY
CREATE TABLE b15.zeleznice_stanice_polygony (
	osm_id bigint,
	name varchar,
	geom geometry(MultiPolygon, 5514)
);

ALTER TABLE b15.zeleznice_stanice_polygony ADD PRIMARY KEY(osm_id);

-- polygony z tabulky czech_polygon
INSERT INTO b15.zeleznice_stanice_polygony
SELECT osm_id, name, ST_Transform(geom, 5514)
FROM osm.czech_polygon WHERE railway = 'station';

-- polygony vytvorene z linii
INSERT INTO b15.zeleznice_stanice_polygony
SELECT osm_id, name, ST_Transform(ST_Multi(ST_MakePolygon(geom)), 5514)
FROM osm.czech_line WHERE railway = 'station';

CREATE INDEX ON b15.zeleznice_stanice_polygony USING gist (geom);

--Odtsraneni stanic ktere nelezi v CR
DELETE FROM b15.zeleznice_stanice_polygony WHERE osm_id NOT IN(
	SELECT b.osm_id FROM b15.zeleznice_stanice_polygony as p
	JOIN ruian.staty as s ON st_within(p.geom, s.geom)
);

-- BODY

CREATE TABLE b15.zeleznice_stanice_body (
	osm_id bigint,
	name varchar,
	geom geometry(Point, 5514)
);

ALTER TABLE b15.zeleznice_stanice_body ADD PRIMARY KEY(osm_id);

-- body z tabulky czech_point
INSERT INTO b15.zeleznice_stanice_body
SELECT osm_id, name, ST_Transform(geom, 5514) FROM osm.czech_point
WHERE railway = 'station';

-- index
CREATE INDEX ON b15.zeleznice_stanice_body USING gist (geom);

-- buffer kolem polygonu stanice 100 m
CREATE TABLE b15.pbuffer_100 AS
	SELECT osm_id, st_buffer(geom, 100) as geom 
	FROM b15.zeleznice_stanice_polygony;

SELECT Populate_Geometry_Columns('b15.pbuffer_100'::regclass);

-- stanice ktere mame pouze jako polygony (muzeme z nich udelat body)
CREATE TABLE b15.samotne_polygony AS
SELECT * FROM b15.zeleznice_stanice_polygony
WHERE osm_id NOT IN(
		SELECT p.osm_id FROM b15.pbuffer_100 as p
		JOIN b15.zeleznice_stanice_body as b ON st_within(b.geom, p.geom)
);

INSERT INTO b15.zeleznice_stanice_body
SELECT osm_id, name, ST_Centroid(geom) FROM b15.samotne_polygony;

-- vyber stanic ktere nelezi uvnitr sveho polygonu
--SELECT b.geom FROM b15.zeleznice_stanice_body as b
--JOIN b15.pbuffer_100 as p_100 ON st_within (b.geom, p_100.geom)
--JOIN b15.zeleznice_stanice_polygony as p ON (st_within(p.geom, p_100.geom) AND st_disjoint(b.geom, p.geom));

--Odtsraneni stanic ktere nelezi v CR
DELETE FROM b15.zeleznice_stanice_body WHERE osm_id NOT IN(
	SELECT b.osm_id FROM b15.zeleznice_stanice_body as b
	JOIN ruian.staty as s ON st_within(b.geom, s.geom)
);

-- Odstraneni pomocnych tabulek
DROP TABLE b15.pbuffer_100;
DROP TABLE b15.samotne_polygony;

---------------------------------------------------------------------------------------
-- ZELEZNICE
---------------------------------------------------------------------------------------
CREATE TABLE b15.zeleznice (
	osm_id bigint,
	bridge varchar,
	tunnel varchar,
	geom geometry(LineString, 5514)
);

ALTER TABLE b15.zeleznice ADD COLUMN gid serial;

ALTER TABLE b15.zeleznice ADD PRIMARY KEY(gid);

CREATE INDEX ON b15.zeleznice USING gist (geom);

INSERT INTO b15.zeleznice
SELECT osm_id, bridge, tunnel, st_transform(geom, 5514) FROM osm.czech_line 
WHERE railway = 'rail';

DELETE FROM b15.zeleznice WHERE gid NOT IN(
	SELECT z.gid FROM b15.zeleznice as z
	JOIN ruian.staty as s ON st_within(z.geom, s.geom)
);

UPDATE b15.zeleznice 
SET bridge = CASE bridge WHEN 'yes' THEN 'yes' ELSE 'no' END;

UPDATE b15.zeleznice 
SET tunnel = CASE tunnel WHEN 'yes' THEN 'yes' ELSE 'no' END;

ALTER TABLE b15.zeleznice ADD COLUMN length numeric;

UPDATE b15.zeleznice SET length = st_length(geom);

---------------------------------------------------------------------------------------
-- OPUSTENE ZELEZNICE
---------------------------------------------------------------------------------------
CREATE TABLE b15.opustene_zeleznice (
	osm_id bigint,
	bridge varchar,
	tunnel varchar,
	geom geometry(LineString, 5514)
);

ALTER TABLE b15.opustene_zeleznice ADD COLUMN gid serial;

ALTER TABLE b15.opustene_zeleznice ADD PRIMARY KEY(gid);

CREATE INDEX ON b15.opustene_zeleznice USING gist (geom);

INSERT INTO b15.opustene_zeleznice
SELECT osm_id, bridge, tunnel, st_transform(geom, 5514) FROM osm.czech_line 
WHERE railway = 'abandoned';

DELETE FROM b15.opustene_zeleznice WHERE gid NOT IN(
	SELECT o.gid FROM b15.opustene_zeleznice as o
	JOIN ruian.staty as s ON st_within(o.geom, s.geom)
);

UPDATE b15.opustene_zeleznice 
SET bridge = CASE bridge WHEN 'yes' THEN 'yes' ELSE 'no' END;

UPDATE b15.opustene_zeleznice 
SET tunnel = CASE tunnel WHEN 'yes' THEN 'yes' ELSE 'no' END;

ALTER TABLE b15.opustene_zeleznice ADD COLUMN length numeric;

UPDATE b15.opustene_zeleznice SET length = st_length(geom);
---------------------------------------------------------------------------------------
-- METRO
---------------------------------------------------------------------------------------

-- metro - koleje
CREATE TABLE b15.metro AS
SELECT osm_id, st_transform(geom, 5514) as geom FROM osm.czech_line 
WHERE railway = 'subway';

SELECT Populate_Geometry_Columns('b15.metro'::regclass);

ALTER TABLE b15.metro ADD COLUMN gid serial;

ALTER TABLE b15.metro ADD PRIMARY KEY(gid);

CREATE INDEX ON b15.metro USING gist (geom);

ALTER TABLE b15.metro ADD COLUMN length numeric;

UPDATE b15.metro SET length = st_length(geom);

-- metro - stanice
CREATE TABLE b15.metro_stanice AS
SELECT DISTINCT b.osm_id, b.name, st_transform(b.geom, 5514) as geom 
FROM b15.zeleznice_stanice_body as b
JOIN b15.metro as m ON st_intersects(b.geom, m.geom) ORDER BY b.osm_id;

SELECT Populate_Geometry_Columns('b15.metro_stanice'::regclass);

ALTER TABLE b15.metro_stanice ADD PRIMARY KEY(osm_id);

CREATE INDEX ON b15.metro_stanice USING gist (geom);

-- odstraneni stanic metra z vrstvy zeleznice_stanice_body
DELETE FROM b15.zeleznice_stanice_body WHERE osm_id IN(
	SELECT DISTINCT b.osm_id FROM b15.zeleznice_stanice_body AS b
	JOIN b15.metro AS m ON st_intersects(b.geom, m.geom)
);

---------------------------------------------------------------------------------------
-- TRAMVAJE - KOLEJE
---------------------------------------------------------------------------------------
CREATE TABLE b15.tramvaje AS
SELECT osm_id, st_transform(geom, 5514) as geom FROM osm.czech_line 
WHERE railway = 'tram';

SELECT Populate_Geometry_Columns('b15.tramvaje'::regclass);

ALTER TABLE b15.tramvaje ADD COLUMN gid serial;

ALTER TABLE b15.tramvaje ADD PRIMARY KEY(gid);

CREATE INDEX ON b15.tramvaje USING gist (geom);

ALTER TABLE b15.tramvaje ADD COLUMN length numeric;

UPDATE b15.tramvaje SET length = st_length(geom);
---------------------------------------------------------------------------------------
-- TRAMVAJE - STANICE
---------------------------------------------------------------------------------------
CREATE TABLE b15.tramvaje_stanice AS
SELECT DISTINCT p.osm_id, p.name, st_transform(p.geom, 5514) as geom 
FROM osm.czech_point as p
WHERE p.railway = 'tram_stop';

SELECT Populate_Geometry_Columns('b15.tramvaje_stanice'::regclass);

ALTER TABLE b15.tramvaje_stanice ADD PRIMARY KEY(osm_id);

CREATE INDEX ON b15.tramvaje_stanice USING gist (geom);

-- odtsranit stanice ktere jsou mimo CR
DELETE FROM b15.tramvaje_stanice
WHERE osm_id NOT IN(
	SELECT osm_id FROM b15.tramvaje_stanice AS t
	JOIN ruian.staty AS s ON st_within(t.geom, s.geom)
);
---------------------------------------------------------------------------------------
-- MESTA
---------------------------------------------------------------------------------------
CREATE TABLE b15.mesta AS
SELECT nazev as name, statuskod as status, geom 
FROM ruian.obce WHERE statuskod IN(3, 4, 5);

ALTER TABLE b15.mesta ADD COLUMN gid serial;

ALTER TABLE b15.mesta ADD PRIMARY KEY(gid);

ALTER TABLE b15.mesta ADD COLUMN area numeric;

UPDATE b15.mesta SET area = st_area(geom);

ALTER TABLE b15.mesta ADD COLUMN population bigint;

CREATE TABLE b15.mesta_populace AS 
SELECT name, CASE WHEN population~E'^\\d+$' THEN population::integer ELSE 0 END as population
FROM osm.czech_point AS p  WHERE place = 'city'; 

UPDATE b15.mesta 
SET population = subquery.population
FROM (
	SELECT name, population FROM b15.mesta_populace
) AS subquery
WHERE b15.mesta.name = subquery.name;

DROP TABLE b15.mesta_populace;

CREATE INDEX ON b15.mesta USING gist (geom);
---------------------------------------------------------------------------------------
-- PARKOVACI PLOCHY
---------------------------------------------------------------------------------------
CREATE TABLE b15.parkoviste AS
SELECT osm.osm_id, osm.name, st_transform(osm.geom, 5514)  as geom
FROM osm.czech_polygon AS osm
JOIN ruian.staty AS s ON st_within(st_transform(osm.geom, 5514) , s.geom)
WHERE amenity = 'parking';

SELECT Populate_Geometry_Columns('b15.parkoviste'::regclass);

ALTER TABLE b15.parkoviste ADD COLUMN gid serial;

ALTER TABLE b15.parkoviste ADD PRIMARY KEY(gid);

ALTER TABLE b15.parkoviste ADD COLUMN area numeric;

UPDATE b15.parkoviste SET area = st_area(geom);

CREATE INDEX ON b15.parkoviste USING gist (geom);
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
COMMIT;

---------------------------------------------------
--Př. 1
--Kolik je v ČR tramvajových zastávek?

SELECT count(*) FROM b15.tramvaje_stanice;

--Výsledek: 921
---------------------------------------------------
--Př. 2
--Kolik kilometrů opuštěných železnic je v České
--republice ? Výsledek uveďte v km a zaokrouhlete.

SELECT round( sum( length ) / 1000 ) AS
celkova_delka
FROM opustene_zeleznice;

--Výsledek: 1140 km
---------------------------------------------------
--Př. 3
--Kolik je celkem v ČR železničních mostů a
--železničních tunelů?

SELECT count(*) FROM b15.zeleznice
WHERE bridge = 'yes' OR tunnel = 'yes';

--Výsledek: 10366
---------------------------------------------------
--Př.4
--Vytvořte 2 km buffer kolem města Brno.

CREATE TABLE brno_buffer_2km AS
SELECT st_buffer (geom, 2000) AS geom
FROM b15.mesta
WHERE name = 'Brno';
---------------------------------------------------
--Př. 5
--Kolik km železnic se nachází na území Prahy?
--Výsledek zaokrouhlete na km.

SELECT round(sum(z.length)/1000)
FROM b15.zeleznice AS z
JOIN b15.mesta as M
ON st_within (z.geom, m.geom)
WHERE m.name = 'Praha';

-- Výsledek: 672 km
---------------------------------------------------
--Př. 6
--Jakou plochu zabírají parkoviště, která se nacházejí
--do 5 km od Prahy?

SELECT round (sum(p.area)/1000)
FROM b15.parkoviste AS p
JOIN b15.mesta AS m
ON st_dwithin (p.geom, m.geom, 5000)
WHERE m.name = 'Praha';

--Výsledek: 3932 km 2
----------------------------------------------------
--Př.7
--Uveďte počet tramvajových zastávek v jednotlivých
--městech ČR. Města s nulovým počtem tramvajových
--zastávek neuvádějte a města seřaďte sestupně
--podle počtu zastávek.

SELECT m.name as mesto, count(t.name) as pocet
FROM b15.tramvaje_stanice AS t
JOIN b15.mesta AS m
ON st_within (t.geom, m.geom)
GROUP BY m.name
HAVING count(t.name) > 0
ORDER BY count(t.name) DESC;

--Výsledek:
--mesto		|	pocet
----------------|------------------
--Praha		|	415
--Brno		|	145
--Plzen		|	110
--Ostrava	|	96
--Olomouc	|	64
--Liberec	|	40
--Most		|	19
--Jablonec nad Nisou	8
--Litvinov	|	8
--Modrice	|	2
----------------------------------------------------
--Př. 8
--Jaká je plocha území v ČR, které se nachází do 50 m
--od železničních kolejí? Výsledek uveďte v km 2 .

WITH buffer AS (
SELECT st_union(st_buffer(geom, 50)) AS geom
FROM b15.zeleznice
)
SELECT round(st_area(st_intersection(z.geom, s.
geom))/1e6) as vymera
FROM buffer AS z
JOIN ruian.staty AS s ON st_intersects(z.geom,
s.geom);

--Výsledek: 1040 km 2
----------------------------------------------------
--Př. 9
--Uveďte tři města z ČR která mají nejvíce
--železničních stanic?

SELECT m.name as mesto, count(z.osm_id) as
nadrazi
FROM b15.zeleznice_stanice_body AS z
JOIN b15.mesta AS m
ON st_intersects(z.geom, m.geom)
GROUP BY m.name
ORDER BY count(z.osm_id) DESC;

--Výsledek:
--mesto		|	nadrazi
----------------|------------------
--Praha		|	30
--Decin		|	8
--Karlovy Vary	|	8
----------------------------------------------------
