-- ÚVOD DO ZPRACOVÁNÍ PROSTOROVÝCH DAT --
      ---------------------------
----     SEMESTRÁLNÍ PROJEKT     ----
      ---------------------------
----   SKUPINA C - Petra Jílková   ----
      ---------------------------
----         28.01.2019         ----
-----------------------------------------
-----------------------------------------

--------1/VYTVOŘENÍ+ÚPRAVA VRSTEV--------

--nastavení cesty

--1a/OBCE_FAZE3_OBYV

--vytvoření tabulky+import dat
CREATE TABLE obce_faze3_obyv(gid serial PRIMARY KEY, kod INT, nazev VARCHAR(50), pocet_obyv INT);

/*COPY obce_faze3_obyv(kod, nazev, pocet_obyv) 
FROM 'C:\GIS_data\obce_faze3_obyv.csv' WITH DELIMITER AS ',' CSV HEADER;*/ -- nelze použít - povoleno pouze pro roli superuser (import s využitím pgAdmin III)

--1b/OBCE_FAZE3

--vytvoření tabulky

CREATE TABLE obce_faze3 AS 
SELECT  o.kod, o.nazev, o.geom, of3.pocet_obyv
FROM obce AS o JOIN obce_faze3_obyv AS of3 ON o.kod=of3.kod;

--nastavení primárního klíče
ALTER TABLE obce_faze3 ADD PRIMARY KEY (kod);

--vytvoření prostorového indexu
CREATE INDEX obce_faze3_gix ON obce_faze3 USING GIST (geom);

--transformace 
ALTER TABLE obce_faze3 ADD COLUMN geom1 geometry(multipolygon, 3857);

UPDATE obce_faze3 SET geom1 = ST_Transform(geom, 3857);

--vytvoření prostorového indexu
CREATE INDEX obce_faze3_gix1 ON obce_faze3 USING GIST (geom1);


--1c/KOMUNIKACE_FAZE3

--vytvoření tabulky
CREATE TABLE komunikace_faze3 AS 
SELECT k.highway AS typ_kom, ST_Length(k.geom) AS delka_kom, k.geom, o.kod
FROM osm_line AS k JOIN obce_faze3 AS o ON st_intersects(k.geom, o.geom1)
AND k.highway IN ('motorway', 'trunk', 'primary', 'secondary', 'tertiary', 'unclassified', 'residential', 'service', 'motorway_link', 'trunk_link', 'primary_link', 'secondary_link', 'tertiary_link', 'living_street', 'pedestrian', 'road');

--nastavení primárního klíče
ALTER TABLE komunikace_faze3 ADD COLUMN gid serial;

ALTER TABLE komunikace_faze3 ADD PRIMARY KEY (gid);

--vytvoření prostorového indexu
CREATE INDEX komunikace_faze3_gix ON komunikace_faze3 USING GIST (geom);

--1d/BUDOVY_FAZE3

--vytvoření tabulky
CREATE TABLE budovy_faze3 AS 
SELECT b.building AS typ_budovy, b.geom, o.kod 
FROM osm_polygon AS b JOIN obce_faze3 AS o ON st_intersects(b.geom, o.geom1)
WHERE b.building IS NOT NULL; -- při použití AND místo WHERE rozdíl v čase vykonání příkazu (ca 70s vs. 15.7s)

--nastavení primárního klíče
ALTER TABLE budovy_faze3 ADD COLUMN gid serial;

ALTER TABLE budovy_faze3 ADD PRIMARY KEY (gid);

--vytvoření prostorového indexu
CREATE INDEX budovy_faze3_gix ON budovy_faze3 USING GIST (geom);

--1e/ZASTAVBA_FAZE3

--vytvoření tabulky
CREATE TABLE zastavba_faze3 AS
(
WITH c AS (SELECT st_clusterDBSCAN(geom, 200, 5) over () as cid, b.geom
FROM budovy_faze3 AS b
)
SELECT cid, st_simplifypreservetopology((st_concavehull(st_collect(c.geom),0.99)),1) AS z_geom
FROM c
WHERE cid IS NOT NULL
GROUP BY cid
); --více pokusů se změnou hodnoty tolerance - trvá mnohem déle - nepoužito (přes dvě hodiny s parametrem 0.98)

-- vytvoření tabulky - nevyužité pokusy
/*CREATE TABLE zastavba AS
(
WITH c AS (SELECT st_clusterDBSCAN(geom, 200, 5) over () as cid, b.geom
FROM budovy_faze3 AS b
)
SELECT cid, st_convexhull(st_collect(c.geom)) AS zs_geom, sum(st_area(c.geom)) as zs_plocha
FROM c
WHERE cid IS NOT NULL
GROUP BY cid
);*/

/*CREATE TABLE zastavba3 AS
(
WITH c AS (
SELECT st_clusterDBSCAN(geom, 200, 5) over () as cid, b.geom
FROM budovy_faze3 AS b
), c2 AS (
SELECT cid, st_collect(c.geom) AS zs_geom, sum(st_area(c.geom)) as zs_plocha
FROM c
WHERE cid IS NOT NULL
GROUP BY cid
)
SELECT aggregatepolygons(c2.zs_geom, 200, true)
FROM c2
GROUP BY cid
);*/

/*CREATE TABLE zastavba_test_cid AS
SELECT st_clusterDBSCAN(geom, 200, 5) over () as cid, geom
FROM budovy_faze3 

CREATE INDEX zastavba_test_cid_gix ON zastavba_test_cid USING GIST (geom);

CREATE TABLE zastavba_test_cid_agg35 AS
SELECT aggregatepolygons(geom, 150, true)
FROM zastavba_test_cid
WHERE cid IN (2890,2893,3488)
GROUP BY cid*/ --testy výsledku funkce - změny parametru vzdálenosti (50-250) - neuspokojivé - nakonec s ohledem na charakter ostatních datových dat (komunikace) zvolena konkávní obálka

--nastavení primárního klíče
ALTER TABLE zastavba_faze3 ADD COLUMN gid serial;

ALTER TABLE zastavba_faze3 ADD PRIMARY KEY (gid);

--vytvoření prostorového indexu
CREATE INDEX zastavba_faze3_gix1 ON zastavba_faze3 USING GIST (z_geom);

--1f/OBCE_VYJEZDY

--vytvoření tabulky+import dat
CREATE TABLE obce_vyjezdy(gid serial PRIMARY KEY, nazev VARCHAR(50), x_coord FLOAT, y_coord FLOAT);
   --data importována skrze pgAdmin III (podobně jako obce_faze3_obyv)
   
--přidání geometrie
ALTER TABLE obce_vyjezdy ADD COLUMN geom geometry(point, 3857);

UPDATE obce_vyjezdy
SET geom= st_transform(st_setsrid(st_point(x_coord, y_coord), 5514),3857);

--vytvoření prostorového indexu
CREATE INDEX obce_vyjezdy_gix ON obce_vyjezdy USING GIST (geom);


---------------2/VALIDACE---------------

--2a/OBCE_FAZE3
SELECT kod, ST_IsValidReason(geom1) AS duvod FROM obce_faze3
WHERE ST_IsValid(geom1) = FALSE;

--2b/KOMUNIKACE_FAZE3
SELECT gid, ST_IsValidReason(geom) AS duvod FROM komunikace_faze3
WHERE ST_IsValid(geom) = FALSE;

--2c/BUDOVY_FAZE3
SELECT gid, ST_IsValidReason(geom) AS duvod FROM budovy_faze3
WHERE ST_IsValid(geom) = FALSE;

--2d/ZASTAVBA_FAZE3
SELECT gid, ST_IsValidReason(z_geom) AS duvod FROM zastavba_faze3
WHERE ST_IsValid(z_geom) = FALSE;

--2e/OBCE_VYJEZDY
SELECT gid, ST_IsValidReason(geom) AS duvod FROM obce_vyjezdy
WHERE ST_IsValid(geom) = FALSE;

---->všechny vrstvy v pořádku


-----------3/VLASTNÍ ANALÝZA-----------
--3a/VÝPOČET UKAZATELŮ

--zastavěná plocha v obci
----vytvoření pomocné tabulky
CREATE TABLE zastavba_faze3_obce AS
SELECT o.kod as kod_obec, o.nazev as nazev_obec, z.cid as cid, st_area(st_intersection(z.z_geom, o.geom1)) as zp_plocha, st_intersection(z.z_geom, o.geom1) as zo_geom
FROM zastavba_faze3 AS z JOIN obce_faze3 AS o ON st_intersects(z.z_geom, o.geom1)

ALTER TABLE zastavba_faze3_obce ADD COLUMN gid serial;

ALTER TABLE zastavba_faze3_obce ADD PRIMARY KEY (gid);

CREATE INDEX zastavba_faze3_obce_gix ON zastavba_faze3_obce USING GIST (zo_geom);


--prům. vzdálenost mezi zastavěnými plochami v obci (mezi centroidy)
----vytvoření pomocné tabulky
CREATE TABLE zastavba_faze3_vzd AS
SELECT st_distance(st_centroid(z.zo_geom), st_centroid(zt.zo_geom)) as vzd, z.cid as cid1, zt.cid as cid2, z.kod_obec as kod1, zt.kod_obec as kod2
FROM zastavba_faze3_obce as z, zastavba_faze3_obce as zt
WHERE z.kod_obec=zt.kod_obec AND z.cid<>zt.cid;

ALTER TABLE zastavba_faze3_vzd ADD COLUMN gid serial;

ALTER TABLE zastavba_faze3_vzd ADD PRIMARY KEY (gid);

----odstranění duplicitních vzdáleností
DELETE 
FROM zastavba_faze3_vzd as z1
USING zastavba_faze3_vzd as z2
WHERE z1.gid<z2.gid
AND z1.vzd=z2.vzd;

--délka komunikace v zástavbě a nejbližším okruhu 200m v obci
ALTER TABLE obce_faze3 
ADD COLUMN kom_delka_km FLOAT;

UPDATE obce_faze3
SET kom_delka_km=zk.kom_delka 
FROM 
(
WITH zb as
(
SELECT cid, kod_obec, st_buffer(z.zo_geom, 200) as geomb FROM zastavba_faze3_obce AS z
)
SELECT st_length(st_collect(st_intersection(k.geom, zb.geomb)))/1e3 as kom_delka, zb.kod_obec as kod_obec
FROM komunikace_faze3 AS k JOIN zb ON st_intersects(k.geom, zb.geomb)
GROUP BY zb.kod_obec
) as zk
WHERE obce_faze3.kod=zk.kod_obec;

--přidání vypočtených ukazatelů do tabulky obcí
ALTER TABLE obce_faze3 
ADD COLUMN zp_pocet INT,
ADD COLUMN zp_avg_vzd_km FLOAT;

----počet zastavěných částí v obci
UPDATE obce_faze3
SET zp_pocet=z.zp_pocet
FROM
(
SELECT COUNT(cid) as zp_pocet, kod_obec
FROM zastavba_faze3_obce
GROUP BY kod_obec
) as z
WHERE obce_faze3.kod=z.kod_obec;

----průměrná vzdálenost zastavěných částí v obci
UPDATE obce_faze3
SET zp_avg_vzd_km=z.zp_avg_vzd_km
FROM
(
SELECT AVG(vzd)/1e3 as zp_avg_vzd_km, kod1
FROM zastavba_faze3_vzd
GROUP BY kod1
) as z
WHERE obce_faze3.kod=z.kod1;

--3b/KATEGORIZACE OBCÍ
ALTER TABLE obce_faze3 
ADD COLUMN zp_pocet_kat INT,
ADD COLUMN zp_avg_vzd_km_kat INT,
ADD COLUMN kom_delka_km_kat INT,
ADD COLUMN pocet_obyv_kat INT;

--přidělení kategorie dle počtu zastavěných ploch v obci
UPDATE obce_faze3 SET zp_pocet_kat=1 WHERE zp_pocet < 10;
UPDATE obce_faze3 SET zp_pocet_kat=2 WHERE zp_pocet >= 10 AND zp_pocet < 20;
UPDATE obce_faze3 SET zp_pocet_kat=3 WHERE zp_pocet >=  20;

--přidělení kategorie dle průměrné vzdálenosti zastavěných ploch v obci
UPDATE obce_faze3 SET zp_avg_vzd_km_kat=1 WHERE zp_avg_vzd_km < 3 OR zp_avg_vzd_km IS NULL; --3 obce mají pouze 1 zastavěnou část, proto zp_avg_vzd_km IS NULL
UPDATE obce_faze3 SET zp_avg_vzd_km_kat=2 WHERE zp_avg_vzd_km >=  3 AND zp_avg_vzd_km < 6;
UPDATE obce_faze3 SET zp_avg_vzd_km_kat=3 WHERE zp_avg_vzd_km >=  6;

--přidělení kategorie dle délky komunikací v zastavěných plochách obce
UPDATE obce_faze3 SET kom_delka_km_kat=1 WHERE kom_delka_km < 50;
UPDATE obce_faze3 SET kom_delka_km_kat=2 WHERE kom_delka_km >= 50 AND kom_delka_km < 100;
UPDATE obce_faze3 SET kom_delka_km_kat=3 WHERE kom_delka_km >=  100;

--přidělení kategorie dle počtu obyvatel obce
UPDATE obce_faze3 SET pocet_obyv_kat=1 WHERE pocet_obyv < 5000;
UPDATE obce_faze3 SET pocet_obyv_kat=2 WHERE pocet_obyv >= 5000 AND pocet_obyv < 7500;
UPDATE obce_faze3 SET pocet_obyv_kat=3 WHERE pocet_obyv >=  7500;

--přidělení celkové kategorie obci
ALTER TABLE obce_faze3 
ADD COLUMN kat_celkem INT;

UPDATE obce_faze3 SET kat_celkem=((0.3*zp_pocet_kat)+(0.4*zp_avg_vzd_km_kat)+(0.2*kom_delka_km_kat)+(0.1*pocet_obyv_kat));

--určení časové náročnosti zpracování obce dle celkové kategorie
ALTER TABLE obce_faze3 
ADD COLUMN zpracovani_hod INT;

UPDATE obce_faze3 SET zpracovani_hod=8 WHERE kat_celkem=1;
UPDATE obce_faze3 SET zpracovani_hod=12 WHERE kat_celkem=2;
UPDATE obce_faze3 SET zpracovani_hod=16 WHERE kat_celkem=3;

--3c/ATRIBUTOVÉ A PROSTOROVÉ DOTAZY

--Kolik mapovaných obcí leží v okruhu 100 km od Domažlic a jaká je jejich časová náročnost zpracování přepočtena na počet pracovních dnů (pracovní den = 8 hodin)? 
SELECT COUNT (o.kod) AS obce_pocet, SUM(zpracovani_hod)/8 AS zpracovani_dny
FROM obce_faze3 AS o
JOIN obce_vyjezdy AS v
ON ST_DWithin(o.geom1, v.geom, 1e5)
WHERE v.nazev = 'Domažlice'

--Jaký je počet obcí v jednotlivých kategoriích?
SELECT COUNT(kod), kat_celkem
FROM obce_faze3
GROUP BY kat_celkem;

/*Jaký počet kilometrů komunikací musí projít mapovač z Prahy-Smíchova, který bude mapovat obce v okruhu 50 km? 
Jak dlouho by mu trvalo tyto komunikace projít, pokud budeme uvažovat průměrnou rychlost chůze 4 km/hod.?*/
SELECT SUM(o.kom_delka_km) as pocet_km, (SUM(o.kom_delka_km))/4 as pocet_hodin, ((SUM(o.kom_delka_km))/4)/8 as pocet_dni
FROM obce_faze3 AS o
JOIN obce_vyjezdy AS v
ON ST_DWithin(o.geom1, v.geom, 50000)
WHERE v.nazev = 'Praha-Smíchov';

--Pro porovnání: Jaká je předem stanovená časová náročnost zpracování obcí z předešlého dotazu?
SELECT COUNT (o.kod) AS obce_pocet, SUM(zpracovani_hod)/8 AS zpracovani_dny
FROM obce_faze3 AS o
JOIN obce_vyjezdy AS v
ON ST_DWithin(o.geom1, v.geom, 50000)
WHERE v.nazev = 'Praha-Smíchov';

--Kolik mapovaných obcí spolu sousedí?
SELECT COUNT(DISTINCT o.kod)
FROM obce_faze3 as o, obce_faze3 as o1
WHERE st_touches(o.geom1, o1.geom1)=true;

--V jaké obci bude muset mapovač navštívit nejvíce zastavěných částí - mapovač z které obce to bude mít nejblíže (kolik km to bude)? 
SELECT o.nazev, o.zp_pocet, v.nazev as obec_vyjezd, st_distance(o.geom1, v.geom)/1e3 as vzd
FROM obce_faze3 as o, obce_vyjezdy as v
ORDER BY zp_pocet DESC, vzd ASC
LIMIT 1;

--V jaké obci bude muset mapovač zmapovat nejvíce kilometrů komunikací - mapovač z které obce to bude mít nejblíže (kolik km to bude)? 
SELECT o.nazev, o.kom_delka_km, v.nazev as obec_vyjezd, st_distance(o.geom1, v.geom)/1e3 as vzd
FROM obce_faze3 as o, obce_vyjezdy as v
ORDER BY kom_delka_km DESC, vzd ASC
LIMIT 1;

--V jaké obci bude muset mapovač najezdit nejvíce kilometrů mezi zastavěnými částmi - mapovač z které obce to bude mít nejblíže (kolik km to bude)? 
SELECT o.nazev, o.zp_avg_vzd_km, v.nazev as obec_vyjezd, st_distance(o.geom1, v.geom)/1e3 as vzd
FROM obce_faze3 as o, obce_vyjezdy as v
WHERE zp_avg_vzd_km IS NOT NULL
ORDER BY zp_avg_vzd_km DESC, vzd ASC
LIMIT 1;




