--PŘEDMĚT: Úvod do zpracování prostorových dat
--SKUPINA: b11 (Barbora Faitová, Alžbeta Gardoňová, Anna Šubrtová)
--ČESKÉ VYSOKÉ UČENÍ TECHNICKÉ V PRAZE, 2010/2011
------------------------------------------------------------------
--NASTAVENÍ CESTY

SET SEARCH_PATH TO b11, public, osm, gis1;


-- Tvorba vrstiev:

--Vrstva dialnic

CREATE TABLE dialnice AS 
SELECT COUNT(osm_id) AS pocet_usekov, ref AS nazov , ST_Area(ST_Union(ST_Buffer(way, 100))) AS plocha, ST_Perimeter(ST_Union(ST_Buffer(way,100 ))) AS obvod, ST_Length(ST_Union(way)) AS dlzka, ST_Union(ST_Buffer(way, 100)) AS geom
FROM osm.czech_line 
WHERE highway = 'motorway' 
GROUP BY ref; 

ALTER TABLE dialnice ADD COLUMN gid serial;
ALTER TABLE dialnice ADD PRIMARY KEY(gid);
CREATE INDEX dialnice_geom ON dialnice USING gist (geom);
SELECT populate_geometry_columns('dialnice'::regclass);


--Vrstva ciest 1.triedy

CREATE TABLE silnice_1 AS 
SELECT COUNT(osm_id) AS pocet_usekov,  ref AS nazov , ST_Area(ST_Union(ST_Buffer(way, 50))) AS plocha, st_perimeter(ST_Union(ST_Buffer(way, 50))) AS obvod, ST_Length(ST_Union(way)) as dlzka, ST_Union(ST_Buffer(way, 50)) AS geom
FROM osm.czech_line
WHERE highway = 'primary'  
GROUP BY ref ;

ALTER TABLE silnice_1 ADD COLUMN gid serial;
ALTER TABLE silnice_1 ADD PRIMARY KEY(gid);
REATE INDEX silnice_1_geom ON silnice_1 USING gist (geom);
SELECT populate_geometry_columns('silnice_1'::regclass);


-- Vrstva ciest 2. a 3. triedy

CREATE TABLE silnice_2_3 AS 
SELECT COUNT(osm_id) AS pocet_usekov, ref AS nazov , ST_Area(ST_Union(ST_Buffer(way, 15))) AS plocha, ST_Perimeter(ST_Union(ST_Buffer(way, 15))) AS obvod, AS_Length(ST_Union(way)) AS dlzka, ST_Union(ST_Buffer(way, 15)) AS geom
FROM osm.czech_line 
WHERE highway = 'secondary' 
OR highway = 'tertiary' 
GROUP BY ref ;

ALTER TABLE silnice_2_3 ADD COLUMN gid serial;
ALTER TABLE silnice_2_3 ADD PRIMARY KEY(gid);
CREATE INDEX silnice_2_3_geom ON silnice_2_3 USING gist (geom);
SELECT populate_geometry_columns('silnice_2_3'::regclass);

--Vrstva runway:

-- tabulka zoradenych objektov podla atributu REF pre velke runway

CREATE TABLE zoradene2 AS
SELECT ref, COUNT(osm_id) AS pocet_usekov
FROM czech_line
WHERE aeroway = 'runway'
AND ST_Length(way) >= 1800
GROUP BY ref
ORDER BY pocet_usekov;

-- tabulka zoradenych objektov podla atributu REF pre male runway

CREATE TABLE zoradene AS
SELECT ref, COUNT(osm_id) AS pocet_usekov
FROM czech_line
WHERE aeroway = 'runway'
AND ST_Length(way) < 1800
GROUP BY ref
ORDER BY pocet_usekov;


--zakladna tabulka runway

CREATE TABLE runway AS
SELECT zoradene2.ref, COUNT(zoradene2.ref), ST_Area(ST_Union(ST_Buffer(way, 300))) AS plocha, ST_Union(ST_Buffer(way, 300)) AS geom 
FROM zoradene2  
RIGHT OUTER JOIN czech_line 
ON zoradene2.ref = czech_line.ref
WHERE pocet_usekov = 1 
AND aeroway = 'runway' 
AND ST_Length(way) >= 1800 
AND zoradene2.ref  NOT IN('13-31', '06-24', '04-22', '06/24', '09/27','03C/21C','03R/21L','03L/21R') 
AND osm_id NOT IN (28042379,28042372,25451388,25451389,89985986 )
GROUP BY zoradene2.ref


-- doplnovanie tabulky  runway podla zistenych  udajov

INSERT INTO runway(
SELECT COUNT(ref) AS  ref , COUNT(osm_id) AS count, ST_Area(ST_Union(ST_Buffer(way, 300))) AS plocha, ST_Union(ST_Buffer(way, 300)) AS geom 
FROM czech_line 
WHERE aeroway = 'runway' 
AND ST_Length(way) >= 1800 
AND ref IN ('13-31', '06-24', '04-22')
GROUP BY aeroway)

INSERT INTO runway(
SELECT COUNT(ref) AS  ref , COUNT(osm_id) AS count, ST_Area(ST_Union(ST_Buffer(way, 300))) AS plocha, ST_Union(ST_Buffer(way, 300)) AS geom 
FROM czech_line 
WHERE aeroby = 'runway' 
AND ST_Length(way) >= 1800 
AND ref IN ('03C/21C','03R/21L','03L/21R')
GROUP BY aeroway)

INSERT INTO runway(
SELECT COUNT(ref) AS  ref , COUNT(osm_id) AS count, ST_Area(ST_Union(ST_Buffer(way, 300))) AS plocha, ST_Union(ST_Buffer(way, 300)) AS geom 
FROM czech_line 
WHERE aeroby = 'runway' 
AND ST_Length(way) >= 1800 
AND osm_id IN (28042379 , 28042372)
GROUP BY aeroway)

INSERT INTO runway(
SELECT COUNT(ref) AS  ref , COUNT(osm_id) AS count, ST_Area(ST_Union(ST_Buffer(way, 300))) AS plocha, ST_Union(ST_Buffer(way, 300)) AS geom 
FROM czech_line 
WHERE aeroby = 'runway' 
AND ST_Length(way) >= 1800 
AND osm_id IN (25451388 , 25451389)
GROUP BY aeroway)

INSERT INTO runway(
SELECT count(ref) AS ref, COUNT(osm_id) AS count, ST_Area(ST_Union(ST_Buffer(way, 300))) AS plocha, ST_Union(ST_Buffer(way, 300)) AS geom 
FROM czech_line 
WHERE aeroby = 'runway' 
AND ST_Length(way) >= 1800 
AND ref IN ('09/27', '06/24')
GROUP BY osm_id)

INSERT INTO runway(
SELECT count(ref) AS ref, COUNT(osm_id) AS count, ST_Area(ST_Union(ST_Buffer(way, 300))) AS plocha, ST_Union(ST_Buffer(way, 300)) AS geom 
FROM czech_line 
WHERE aeroway = 'runway' 
AND ST_Length(way) >= 1800 
AND osm_id IN (102406127,25451625,48499285,48503047,53569376,23048716,19853714,19853713,75134835,16280387))
GROUP BY osm_id)



-- tabulka mala runway 

CREATE TABLE mala_runway AS 
SELECT zoradene.ref, COUNT(zoradene.ref),  ST_Area(ST_Union(ST_Buffer(way, 150))) AS plocha, ST_Union(ST_Buffer(way, 150)) AS geom 
FROM zoradene 
RIGHT OUTER JOIN czech_line
ON zoradene.ref = czech_line.ref 
WHERE  pocet_usekov = 1 
AND aeroway = 'runway'
AND ST_Length(way) < 1800 
AND  zoradene.ref  NOT IN ('18','36','10R/28L','10L/28R','05L/23R','05R/23L','09R/27L','16R/34L','07L/25R','07R/25L')
AND osm_id NOT IN (97169088, 97169087, 55985240, 55985239, 89366169, 89366159)
GROUP BY  zoradene.ref


--doplnovanie tabulky podla zistenych informacii

INSERT INTO mala_runway(
SELECT COUNT(ref) AS ref , COUNT(osm_id) AS count, ST_Area(ST_Union(ST_Buffer(way, 150))) AS plocha, ST_Union(ST_Buffer(way, 150)) AS geom 
FROM czech_line 
WHERE aeroway = 'runway' 
AND ST_Length(way) < 1800 
AND ref IN('10R/28L', '10L/28R')
GROUP BY aeroway)

INSERT INTO mala_runway(
SELECT COUNT(ref) AS ref , COUNT(osm_id) AS count, ST_Area(ST_Union(ST_Buffer(way, 150))) AS plocha, ST_Union(ST_Buffer(way, 150)) AS geom 
FROM czech_line 
WHERE aeroway = 'runway' 
AND ST_Length(way) < 1800 
AND ref IN('05R/23L', '05L/23R')
GROUP BY aeroway)

INSERT INTO mala_runway(
SELECT COUNT(ref) AS ref , COUNT(osm_id) AS count, ST_Area(ST_Union(ST_Buffer(way, 150))) AS plocha, ST_Union(ST_Buffer(way, 150)) AS geom 
FROM czech_line 
WHERE aeroway = 'runway' 
AND ST_Length(way) < 1800 
AND ref in('18', '36')
GROUP BY aeroway)

INSERT INTO mala_runway(
SELECT COUNT(ref) AS ref , COUNT(osm_id) AS count, ST_Area(ST_Union(ST_Buffer(way, 150))) AS plocha, ST_Union(ST_Buffer(way, 150)) AS geom 
FROM czech_line 
WHERE aeroway = 'runway' 
AND ST_Length(way) < 1800 
AND ref in('07R/25L','07L/25R')
GROUP BY aeroway)

INSERT INTO mala_runway(
SELECT COUNT(ref) AS ref , COUNT(osm_id) AS count, ST_Area(ST_Union(ST_Buffer(way, 150))) AS plocha, ST_Union(ST_Buffer(way, 150)) AS geom 
FROM czech_line 
WHERE aeroway = 'runway' 
AND ST_Length(way) < 1800 
AND osm_id IN('97169088', '97169087')
GROUP BY aeroway)

INSERT INTO mala_runway(
SELECT COUNT(ref) AS ref , COUNT(osm_id) AS count, ST_Area(ST_Union(ST_Buffer(way, 150))) AS plocha,  ST_Union(ST_Buffer(way, 150)) AS geom 
FROM czech_line 
WHERE aeroway = 'runway' 
AND ST_Length(way) < 1800 
AND osm_id IN('55985240', '55985239')
GROUP BY aeroway)

INSERT INTO mala_runway(
SELECT COUNT(ref) AS ref , COUNT(osm_id) AS count, ST_Area(ST_Union(ST_Buffer(way, 150))) AS plocha, ST_Union(ST_Buffer(way, 150)) AS geom 
FROM czech_line 
WHERE aeroway = 'runway' 
AND ST_Length(way) < 1800 
AND osm_id IN('89366169', '89366159')
GROUP BY aeroway)


-- pre jednoduchost  su  tabulky nasledovne spojene

INSERT INTO runway (
SELECT * 
FROM mala_runway)


ALTER TABLE runway ADD COLUMN gid serial;
ALTER TABLE runway ADD PRIMARY KEY(gid);
CREATE INDEX runway_geom ON runway USING gist (geom);
SELECT populace_geometry_columns('runway'::regclass);




--tabulka zdrojov podzemnej vody

CREATE TABLE podz_voda AS 
SELECT osm_id, ST_Area(ST_Buffer(way, 10)) AS plocha, (ST_Buffer(way, 10)) AS geom
FROM osm.czech_point 


ALTER TABLE podz_voda ADD PRIMARY KEY(osm_id);
CREATE INDEX podz_voda_geom ON podz_voda USING gist (geom;



--tabulka elektrostanic

CREATE TABLE el_stanice AS 
SELECT osm_id, ST_Area(ST_Buffer(way, 20)) AS plocha, (ST_Buffer(way, 20)) AS geom
FROM osm.czech_polygon 
WHERE power = 'station';

ALTER TABLE el_stanice ADD PRIMARY KEY(osm_id);
CREATE INDEX el_stanice_geom ON el_stanice USING gist (geom;
SELECT populate_geometry_columns('el_stanice'::regclass);
SELECT populate_geometry_columns('podz_voda'::regclass);
WHERE "natural" = 'spring';


--tabulka obci

CREATE TABLE obce AS 
SELECT * 
FROM gis1.obce;


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


ALTER TABLE obce ADD PRIMARY KEY(gid);
CREATE INDEX obce_geom ON obce USING gist (geom);
SELECT populate_geometry_columns('obce'::regclass);


--tabulka KLTM50

CREATE TABLE kltm50 AS 
SELECT * 
FROM gis1.kltm50

UPDATE kltm50 
SET geom = ST_Transform(geom, 900913);


ALTER TABLE kltm50 ADD PRIMARY KEY(gid);
CREATE INDEX kltm50_geom ON kltm50 USING gist (geom);
SELECT populate_geometry_columns('kltm50'::regclass);

-- tabulka CR 
CREATE TABLE cr AS
SELECT       ST_BuildArea(ST_Union(geom)) AS geom
FROM         obce
GROUP BY     pomoc


ALTER TABLE cr ADD PRIMARY KEY(gid);
CREATE INDEX cr_geom ON cr USING gist (geom);
SELECT populate_geometry_columns('cr'::regclass);



--DOTAZY

--1 Určete počet listů KLTM50, na kterých se nachází objekty tabulky runway a kolik objektů (runway) se nachází v ČR.
SELECT COUNT( DISTINCT kltm50.gid ) AS pocet_listov, COUNT(DISTINCT runway.gid) AS pocet_runway
  FROM kltm50
  JOIN runway
    ON ST_Intersects(runway.geom, kltm50.geom);

--Answer: ( 42 ; 38 ) 
--Total query runtime: 224 ms.


--2 Určete počet objektů tabulky runway, které alespoň částí svého ochranného území leží ve více obcích.

SELECT (SELECT COUNT(DISTINCT runway.gid) FROM runway) - COUNT(DISTINCT runway.gid) 
  FROM obce
  JOIN runway
    ON ST_Contains(obce.geom, runway.geom);

--Answer: ( 22 )
--Total query runtime: 21 ms.


--3 Určete plochu ochranných pásem všech objektů tabulky runway a průměrnou hodnotu plochy ochranných pásem runway (v km2).

SELECT ROUND(SUM(ST_Area(runway.geom))/1e6) AS plocha_letisk,  SUM(st_area(runway.geom))/(1e6*COUNT(gid))  AS priemer_procenta
  FROM runway;

--Answer: ( 77 ; 2.03)
--Total query runtime: 14 ms.


--4 Určete počet ochranných pásem runway, které se křižují s ochrannými pásmy silnic 2. a 3. třídy.

SELECT COUNT(DISTINCT runway.gid)
  FROM silnice_2_3
  JOIN runway
    ON ST_Intersects(runway.geom, silnice_2_3.geom);

--Answer: (10)
--Total query runtime: 2927 ms.


--5 Určete počet obcí, ve kterých se nachází ochranné pásmo silnic 1. třídy. 

SELECT COUNT(DISTINCT obce.gid)
  FROM silnice_1
  JOIN obce
    ON ST_Intersects(obce.geom, silnice_1.geom);

--Answer: ( 1518 )
--Total query runtime: 14772 ms.


--6 Určete počet křižování ochranného pásma dálnice D5 (nazov) s ochrannými pásmy silnic 1. třídy. 

SELECT COUNT(dialnice.gid)
  FROM silnice_1
  JOIN dialnice
    ON ST_Intersects(dialnice.geom, silnice_1.geom)
 WHERE dialnice.nazov='D5';

--Answer: (5)
--Total query runtime: 72 ms.


--7 Určete počet obcí, ve kterých se nachází ochranná pásma cest všech kategorií (dálnice, silnice 1., 2., 3. třídy).

SELECT COUNT(DISTINCT obce.gid)
  FROM silnice_1
  JOIN obce
    ON ST_Intersects(obce.geom, silnice_1.geom)
  JOIN dialnice
    ON ST_Intersects(obce.geom, dialnice.geom)
  JOIN  silnice_2_3
    ON ST_Intersects(obce.geom, silnice_2_3.geom);

--Answer: (52)
--Total query runtime: 14034 ms.


--8 Určete počet obcí, v kterých se nenachází ochranné pásmo silnic 2. a 3. třídy.

SELECT (SELECT COUNT(obce.gid) FROM obce)-COUNT(DISTINCT(obce.gid))
  FROM obce
  JOIN silnice_2_3
    ON ST_Intersects(obce.geom, silnice_2_3.geom);

--Answer: (42 )
--Total query runtime: 458708 ms.


--9 Určete vzdálenost nejvýchodnější a nejzápadnější runwaye (použijte centroidy, odpověď v km).

SELECT ROUND(ST_Distance((SELECT geom  
                            FROM runway 
                        ORDER BY ST_x(CENTROID(runway.geom))  
                      DESC LIMIT 1), 
                         (SELECT geom 
                            FROM runway 
                        ORDER BY st_x(CENTROID(runway.geom)) 
                       ASC LIMIT 1))/1e3);

--Answer: (635)
--Total query runtime: 14114  ms.


--10 Určete součet ploch ochranných pásem dálnice a 1. třídy a jejich procentuální vyjádření v rámci plochy ČR. (plochu v m2)

SELECT SUM(ST_Area(dialnice.geom)) + SUM(ST_Area(silnice_1.geom)), 
       (SUM(ST_Area(dialnice.geom)) + SUM(ST_Area(silnice_1.geom)))/SUM(ST_Area(cr.geom))*100
  FROM dialnice
  JOIN cr
    ON ST_Intersects(cr.geom, dialnice.geom)
  JOIN silnice_1
    ON ST_Intersects(cr.geom, silnice_1.geom);

--Answer: ( 37626463628.6449 ; 0.0179500656356264 )
--Total query runtime: 749 ms.


--11 Určete nejmenší vzdálenost mezi runway a elektrostanicí, gid runway a osm_id elektrostatice.

   SELECT ROUND( Max_Distance(runway.geom, el_stanice.geom)) AS vzd, runway.gid, El_stanice.osm_id
     FROM runway
     JOIN el_stanice 
       ON ST_Disjoint(runway.geom, el_stanice.geom);
 ORDER BY vzd 
ASC LIMIT 1;

--Answer: ( 3042 ; 14; 42939782 )
--Total query runtime: 389 ms.


--12 Najděte list KLTM50 (nazev), na kterém se nachází nevyšší počet elektrostanic, určete jejich počet.

    SELECT kltm50.nazev, COUNT(DISTINCT el_stanice.osm_id) AS pocet
      FROM kltm50
      JOIN el_stanice
        ON ST_Intersects(kltm50.geom, CENTROID(el_stanice.geom))
  GROUP BY nazev
  ORDER BY pocet 
DESC LIMIT 1;

--Answer: ("M-33-65-D";10)
--Total query runtime: 22 ms.


--13 Určete plochu ochranných pásem a počet elektrostanic nacházejících se na listě KLTM50 "M-33-65-D" (nazev).

SELECT COUNT(DISTINCT el_stanice.osm_id) AS pocet, SUM(ST_Area(el_stanice.geom))
  FROM kltm50
  JOIN el_stanice
    ON ST_Intersects(kltm50.geom, CENTROID(el_stanice.geom))
 WHERE nazev='M-33-65-D';

--Answer: (10;730512.443806648)
--Total query runtime: 14 ms.


--14 Určete počet ochranných pásem elektrostanic, které se nacházejí v ochranném pásu cest 1. třídy.

SELECT COUNT(DISTINCT el_stanice.osm_id) 
  FROM silnice_1
  JOIN el_stanice
    ON  ST_Intersects(el_stanice.geom, silnice_1.geom);

--Answer: (10)
--Total query runtime: 2528 ms.


--15 Určete název obce, ve které se nachází elektrostatice 28477054 (osm_id).

SELECT nazev 
  FROM  obce
  JOIN el_stanice
    ON ST_Intersects(el_stanice.geom, obce.geom)
 WHERE osm_id='28477054';

--Answer: "Dolní Lukavice"
--Total query runtime: 12 ms.


--16 Určete počet ochranných pásem zdroje podzemní vody, které se alespoň částečně překrývají s ochranným pásmem silnic 2. a 3. třídy. 
 
SELECT COUNT(DISTINCT podz_voda.osm_id)
  FROM  podz_voda
  JOIN silnice_2_3 
    ON ST_Intersects(podz_voda.geom, silnice_2_3.geom);

--Answer: (6 )
--Total query runtime: 40778 ms.


--17 Určete název KLTM50 nejjižnějšího ochranného pásma zdroje podzemní vody. 
 
SELECT DISTINCT(nazev)
  FROM  kltm50
  JOIN podz_voda 
    ON ST_Intersects((SELECT podz_voda.geom 
                        FROM podz_voda 
                    ORDER BY ST_y(CENTROID(podz_voda.geom)) 
                   ASC LIMIT 1), kltm50.geom);


--Answer: ("M-33-114-C")
--Total query runtime: 21 ms.


--18 Najděte název obce, kde se nachází ochranné pásmo runwaye s největším poměrem plochy k obvodu. Vypište název a hledaný poměr. 

    SELECT nazev, ST_Area(runway.geom)/st_perimeter(runway.geom) AS pomer
      FROM runway 
      JOIN obce 
        ON ST_Intersects(obce.geom, runway.geom) 
  ORDER BY pomer 
DESC LIMIT 1;

--Answer: ("Kunovice";339.195296803988)
--Total query runtime: 23 ms.


--19 Určete souřadnice zdroje podzemní vody v obci Horní Kozolupy. (použijte centroid)

SELECT st_astext(CENTROID(podz_voda.geom))
  FROM podz_voda
  JOIN obce
    ON ST_Intersects(CENTROID(podz_voda.geom), obce.geom) 
 WHERE nazev = 'Horní Kozolupy';

--Answer: ("POINT(1443969.56495223 6422518.29573358)")
--Total query runtime: 52 ms.


--20 Určete gid a vzdálenost nejbližší runway od obce Horní Kozolupy. (Pro obě vrstvy použijte centroid.)

   SELECT ref, st_distance(CENTROID(obce.geom), CENTROID(runway.geom)) AS vzd
     FROM runway
     JOIN obce
       ON ST_Disjoint(CENTROID( obce.geom), CENTROID(runway.geom)) 
    WHERE nazev = 'Horní Kozolupy'
 ORDER BY vzd 
ASC LIMIT 1;

--Answer: (22;45781.7490865062)
--Total query runtime: 12 ms.


--21 V jaké obci leží elektrostanice a letiště z otázky 11?


SELECT nazev

  FROM obce

  JOIN runway
    ON ST_CONTAINS(obce.geom,runway.geom)

  JOIN el_stanice

    ON ST_CONTAINS(obce.geom,el_stanice.geom)

 WHERE el_stanice.osm_id = (SELECT el_stanice.osm_id

			      FROM runway

			      JOIN el_stanice
 
				ON ST_DISJOINT(runway.geom, el_stanice.geom)

			  ORDER BY ROUND(MAX_DISTANCE(runway.geom, el_stanice.geom))
			     LIMIT 1)

   AND runway.gid = (SELECT runway.gid

	               FROM runway

		       JOIN el_stanice
 
		    	 ON ST_DISJOINT(runway.geom, el_stanice.geom)

		   ORDER BY ROUND(MAX_DISTANCE(runway.geom, el_stanice.geom))

		      LIMIT 1);

--Answer: ("Praha")
--Total query runtime: 786 ms.




--22 Kolik je v CR dálnic a jaká je jejich průměrná délka?



SELECT COUNT(*), SUM(dlzka)/(COUNT(*)*1e3) as delka

  FROM dialnice;


--Answer: (10; 229.869734399394)

--Total query runtime: 11 ms.
