/*
SEMESTRALNI PROJEKT UZPD
skupina b12
Fiedlerova, Spicka, Klimunda
letni semestr 2011/2012
*/

-- nastaveni cesty
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------

/*
VYTVORENI VRSTEV
*/


-- VYTVORENI LINIOVE VRSTVY SILNICE

CREATE TABLE silnice AS
 SELECT osm_id, name AS nazev, highway AS popis, geom FROM osm.czech_line 
 WHERE highway IN ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link');

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

-- nastaveni prostoroveho klice
CREATE INDEX silnice_geom ON silnice USING gist (geom);
-------------------------------------------------------------------------------------

-- VYTVORENI BODOVE VRSTVY BENZINKY

-- bodova vrstva
CREATE TABLE benzinky AS 
 SELECT osm_id, name AS nazev, amenity AS popis, geom FROM czech_point 
 WHERE amenity = 'fuel';

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

-- polygonova vrstva  
CREATE TABLE benzinky_pol AS
 SELECT osm_id, name AS nazev, amenity AS popis, geom FROM czech_polygon 
 WHERE amenity = 'fuel';

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

-- nastaveni prostoroveho klice
CREATE INDEX benzinky_geom ON benzinky USING gist (geom);

-- kontrola validity polygon. vrstvy - OK
SELECT ST_IsValidReason(geom) FROM benzinky_pol 
 WHERE ST_IsValid(geom) = false;

-- smazani duplicitnich hodnot
DELETE FROM benzinky_pol WHERE osm_id IN  
(SELECT b.osm_id FROM benzinky AS a 
 JOIN benzinky_pol AS b
 ON ST_Contains(b.geom, a.geom));
 
 -- prevedeni polygonove vrstvy na bodovou a vlozeni do bodove tabulky
INSERT INTO benzinky 
(SELECT osm_id, nazev, popis, ST_PointOnSurface(geom) AS geom FROM benzinky_pol);

-- smazani tabulky s polygony
-------------------------------------------------------------------------------------

-- VYTVORENI BODOVE VRSTVY MOTORISMUS

-- bodova vrstva
CREATE TABLE motorismus AS 
 SELECT osm_id, name AS nazev, amenity AS popis, geom FROM czech_point 
 WHERE amenity ='car_wash';

-- vlozeni dalsich hodnot  
INSERT INTO motorismus (osm_id, nazev, popis, geom)   
  SELECT osm_id, name AS nazev, shop AS popis, geom FROM czech_point 
  WHERE shop IN ('car_parts','car_repair') AND (amenity != 'car_wash' OR amenity IS NULL);

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

-- nastaveni prostoroveho klice
CREATE INDEX motorismus_geom ON motorismus USING gist (geom);

-- polygonova vrstva  
CREATE TABLE motorismus_pol AS
 SELECT osm_id, name AS nazev, amenity AS popis, geom FROM czech_polygon 
 WHERE amenity ='car_wash';

-- vlozeni dalsich hodnot  
INSERT INTO motorismus_pol (osm_id, nazev, popis, geom)   
  SELECT osm_id, name AS nazev, shop AS popis, geom FROM czech_polygon 
  WHERE shop IN ('car_parts','car_repair') AND (amenity != 'car_wash' OR amenity IS NULL);

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

-- kontrola validity - OK
SELECT ST_IsValidReason(geom) FROM motorismus_pol 
 WHERE ST_IsValid(geom) = false;

-- smazani duplicitnich prvku
DELETE FROM motorismus WHERE osm_id IN  
(SELECT a.osm_id FROM motorismus AS a 
 JOIN motorismus_pol AS b
 ON ST_Contains(b.geom, a.geom));

-- prevedeni polygonove vrstvy na bodovou a vlozeni do bodove tabulky
INSERT INTO motorismus
(SELECT osm_id, nazev, popis, ST_PointOnSurface(geom) AS geom FROM motorismus_pol);

-- smazani tabulky s polygony
-------------------------------------------------------------------------------------

-- VYTVORENI BODOVE VRSTVY PARKOVISTE

-- bodova vrstva
CREATE TABLE parkoviste AS 
 SELECT osm_id, name AS nazev, amenity AS popis, geom FROM czech_point 
 WHERE amenity = 'parking';

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

-- polygonova vrstva  
CREATE TABLE parkoviste_pol AS
 SELECT osm_id, name AS nazev, amenity AS popis, geom FROM czech_polygon 
 WHERE amenity = 'parking';

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

-- nastaveni prostoroveho klice
CREATE INDEX parkoviste_geom ON parkoviste USING gist (geom);

-- kontrola validity polygon. vrstvy - 1 chyba
SELECT ST_IsValidReason(geom) FROM parkoviste_pol 
 WHERE ST_IsValid(geom) = false;

-- oprava
ALTER TABLE parkoviste_pol RENAME COLUMN geom TO geom1;
SELECT AddGeometryColumn('parkoviste_pol','geom',900913,'multipolygon',2,true);
UPDATE parkoviste_pol SET geom = ST_Multi(geom1);
UPDATE parkoviste_pol SET geom = ST_MakeValid(geom) WHERE ST_IsValid(geom) = FALSE;
SELECT DropGeometryColumn('parkoviste_pol', 'geom1');

-- smazani duplicitnich prvku
DELETE FROM parkoviste WHERE osm_id IN  
(SELECT a.osm_id FROM parkoviste AS a 
 JOIN parkoviste_pol AS b
 ON ST_Contains(b.geom, a.geom));

-- prevedeni polygonove vrstvy na bodovou a vlozeni do bodove tabulky
INSERT INTO parkoviste 
(SELECT osm_id, nazev, popis, ST_PointOnSurface(geom) AS geom FROM parkoviste_pol);

-- smazani tabulky s polygony
-------------------------------------------------------------------------------------

-- VYTVORENI POLYGONOVE VRSTVY TRAVA

CREATE TABLE trava AS 
 SELECT osm_id, name AS nazev, landuse AS popis, geom FROM czech_polygon 
 WHERE landuse IN ('meadow', 'grass');

-- vlozeni dalsich hodnot 
INSERT INTO trava (osm_id, nazev, popis, geom) 
 SELECT osm_id, name AS nazev, p.natural AS popis, geom FROM czech_polygon AS p 
 WHERE p.natural = 'grassland';

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

-- vytvoreni prostor. indexu
CREATE INDEX trava_geom_gist ON trava USING gist (geom);
-------------------------------------------------------------------------------------

-- VYTVORENI POLYGONOVE VRSTVY PRUM_ZONY
CREATE TABLE prum_zony AS 
 SELECT osm_id, name AS nazev, landuse AS popis, geom FROM czech_polygon 
 WHERE landuse = 'industrial';

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

-- vytvoreni prostor. indexu
CREATE INDEX prum_zony_geom_gist ON prum_zony USING gist (geom);
-------------------------------------------------------------------------------------

-- VYTVORENI POLYGONOVE VRSTVY OBCE

-- vytvoreni docasne tabulky s multipolygony
CREATE TABLE obce_tmp1 AS SELECT kodob,ST_Union(geom) AS geom FROM obce GROUP BY kodob;

-- zkopirovani tabulky obce a priprava sloupecku multipolygonu
CREATE TABLE obce_tmp2 AS SELECT * FROM obce;
DELETE FROM obce_tmp2 WHERE ctid NOT IN (SELECT MAX(ctid) FROM obce GROUP BY kodob);
SELECT DropGeometryColumn('obce_tmp2', 'geom');
SELECT AddGeometryColumn('obce_tmp2', 'geom', 2065, 'MultiPolygon', 2);

-- prekopirovani geometrie z tabulky tmp1
UPDATE obce_tmp2 AS a SET geom = ST_Multi(b.geom) FROM obce_tmp1 AS b WHERE a.kodob = b.kodob;

-- smazani tabulky tmp1
DROP TABLE obce_tmp1;
ALTER TABLE obce_tmp2 RENAME TO obce;

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

-- transformace
ALTER TABLE obce RENAME COLUMN geom TO geom1; 
SELECT AddGeometryColumn('obce', 'geom', 900913, 'multipolygon', 2);
UPDATE obce SET geom = ST_Transform(geom1, 900913);
SELECT DropGeometryColumn('obce','geom1');

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

-- vymazani nepotrebnych sloupcu
ALTER TABLE obce DROP COLUMN nazev;
ALTER TABLE obce DROP COLUMN kodorp;
ALTER TABLE obce DROP COLUMN iczuj;
ALTER TABLE obce DROP COLUMN ob91;
ALTER TABLE obce DROP COLUMN kraj1960;
ALTER TABLE obce DROP COLUMN kodok;
ALTER TABLE obce DROP COLUMN ko;
ALTER TABLE obce DROP COLUMN icobce;
ALTER TABLE obce DROP COLUMN om;
ALTER TABLE obce DROP COLUMN ur;
ALTER TABLE obce DROP COLUMN sm;
ALTER TABLE obce DROP COLUMN kodst;
ALTER TABLE obce DROP COLUMN kodfi;
ALTER TABLE obce DROP COLUMN kodma;
ALTER TABLE obce DROP COLUMN syob1;
ALTER TABLE obce DROP COLUMN sxob1;
ALTER TABLE obce DROP COLUMN mapa;
ALTER TABLE obce DROP COLUMN okpo;
ALTER TABLE obce DROP COLUMN kodpo;
ALTER TABLE obce DROP COLUMN nazpo;
ALTER TABLE obce DROP COLUMN cisorp;
ALTER TABLE obce DROP COLUMN okorp;
ALTER TABLE obce DROP COLUMN nazpo_eng;
ALTER TABLE obce DROP COLUMN pomoc;
ALTER TABLE obce DROP COLUMN nuts5;
ALTER TABLE obce DROP COLUMN nuts2;
ALTER TABLE obce DROP COLUMN ob01;
ALTER TABLE obce DROP COLUMN cispou;
ALTER TABLE obce DROP COLUMN nazorp;
ALTER TABLE obce DROP COLUMN psc;
ALTER TABLE obce DROP COLUMN perimeter;
ALTER TABLE obce DROP COLUMN area;
ALTER TABLE obce DROP COLUMN cat;
ALTER TABLE obce DROP COLUMN cat_;
ALTER TABLE obce DROP COLUMN nuts3;
ALTER TABLE obce DROP COLUMN nuts4;
-------------------------------------------------------------------------------------

-- VYTVORENI BODOVE VRSTVY OBCE_BOD

-- bodova vrstva
CREATE TABLE obce_bod AS 
 SELECT ogc_fid, kodob, nazob_eng, nazorp_eng, nk, geom FROM gis1.obce_b;

-- nastaveni primarniho klice
 ALTER TABLE obce_bod ADD PRIMARY KEY (ogc_fid);
 
 -- transformace
ALTER TABLE obce_bod RENAME COLUMN geom TO geom1; 
SELECT AddGeometryColumn('obce_bod', 'geom', 900913, 'point', 2);
UPDATE obce_bod SET geom = ST_Transform(geom1, 900913);
SELECT DropGeometryColumn('obce_bod','geom1');

-- vytvoreni prostoroveho indexu
CREATE INDEX obce_bod_geom ON obce_bod USING gist (geom);

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

/*
KONTROLA VALIDACE JEDNOTLIVYCH VRSTEV
*/

-- SILNICE - OK
SELECT osm_id, ST_IsValidReason(geom) FROM silnice WHERE ST_IsValid(geom) = FALSE;

-- MOTORISMUS - OK
SELECT osm_id, ST_IsValidReason(geom) FROM motorismus WHERE ST_IsValid(geom) = FALSE;

-- BENZINKY - OK
SELECT osm_id, ST_IsValidReason(geom) FROM benzinky WHERE ST_IsValid(geom) = FALSE;

-- PARKOVISTE - OK
SELECT osm_id, ST_IsValidReason(geom) FROM parkoviste WHERE ST_IsValid(geom) = FALSE;

-- PRUM_ZONY - 1 chyba
SELECT osm_id, ST_IsValidReason(geom) FROM prum_zony WHERE ST_IsValid(geom) = FALSE;

-- prevod na multipolygony a provedeni opravy
ALTER TABLE prum_zony RENAME COLUMN geom TO geom1;
SELECT AddGeometryColumn('prum_zony','geom',900913,'multipolygon',2,true);
UPDATE prum_zony SET geom = ST_Multi(geom1);
UPDATE prum_zony SET geom = ST_MakeValid(geom) WHERE ST_IsValid(geom) = FALSE;
SELECT DropGeometryColumn('prum_zony', 'geom1'); 

-- TRAVA - 36 chyb
SELECT osm_id, ST_IsValidReason(geom) FROM trava WHERE ST_IsValid(geom) = FALSE;

-- prevod na multipolygony a provedeni opravy
ALTER TABLE trava RENAME COLUMN geom TO geom1;
SELECT AddGeometryColumn('trava','geom',900913,'multipolygon',2,true);
UPDATE trava SET geom = ST_Multi(geom1);
-- odstraneni jinych prvku nez polygonu
UPDATE trava SET geom = ST_CollectionExtract(ST_MakeValid(geom),3) 
 WHERE ST_IsValid(geom) = false AND ST_GeometryType(ST_MakeValid(geom)) = 'ST_GeometryCollection';
UPDATE trava SET geom = ST_MakeValid(geom) WHERE ST_IsValid(geom) = FALSE;
SELECT DropGeometryColumn('trava', 'geom1');

-- OBCE - 1 chyba
SELECT ogc_fid, ST_IsValidReason(geom) FROM obce WHERE ST_IsValid(geom) = FALSE;

-- oprava
SELECT ogc_fid, ST_AsText(ST_MakeValid(geom)) FROM obce WHERE ST_IsValid(geom) = FALSE;
UPDATE obce SET geom = ST_MakeValid(geom) WHERE ST_IsValid(geom) = FALSE;

-- OBCE_BOD - OK
SELECT ogc_fid, ST_IsValidReason(geom) FROM obce_bod WHERE ST_IsValid(geom) = FALSE;

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

/*
PGROUTING
*/

-- nezbytne sloupce
ALTER TABLE silnice ADD COLUMN source INTEGER;
ALTER TABLE silnice ADD COLUMN target INTEGER;
ALTER TABLE silnice ADD COLUMN length FLOAT;

-- pro a-star
ALTER TABLE silnice ADD COLUMN x1 DOUBLE PRECISION;
ALTER TABLE silnice ADD COLUMN y1 DOUBLE PRECISION;
ALTER TABLE silnice ADD COLUMN x2 DOUBLE PRECISION;
ALTER TABLE silnice ADD COLUMN y2 DOUBLE PRECISION;
 
UPDATE silnice SET x1 = ST_X(ST_StartPoint(geom));
UPDATE silnice SET y1 = ST_Y(ST_StartPoint(geom));
UPDATE silnice SET x2 = ST_X(ST_EndPoint(geom));
UPDATE silnice SET y2 = ST_Y(ST_EndPoint(geom));

-- pro shooting star
ALTER TABLE silnice ADD COLUMN reverse_cost DOUBLE PRECISION;
UPDATE silnice SET reverse_cost = LENGTH;
 
ALTER TABLE silnice ADD COLUMN to_cost DOUBLE PRECISION;
ALTER TABLE silnice ADD COLUMN rule text;

-- indexy
CREATE INDEX source_idx ON silnice(source);
CREATE INDEX target_idx ON silnice(target);

-- topologie - doplneni pocatecnich a koncovych uzlu
SELECT assign_vertex_id('b12', 'silnice', 1, 'geom', 'osm_id');

-- doplneni delky useku
UPDATE silnice SET length = ST_Length(geom);

-- topologie - doplneni pocatecnich a koncovych uzlu
SELECT assign_vertex_id('b12', 'silnice', 1, 'geom', 'osm_id');

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

/* 
ZDROJOVE KODY FUNKCI POUZITYCH V DOTAZECH 
*/

CREATE TYPE link_point AS (id integer, name varchar);

-- zdrojovy kod funkce find_nearest_link_within_distance
CREATE OR REPLACE FUNCTION find_nearest_link_within_distance(point varchar,
distance double precision, tbl varchar)
RETURNS INT AS
$$
DECLARE
    row record;
    x float8;
    y float8;
    
    srid integer;
    
BEGIN

    FOR row IN EXECUTE 'select ST_srid(geom) as srid from '||tbl||' where osm_id = (select min(osm_id) from '||tbl||')' LOOP
    END LOOP;
srid:= row.srid;
    
    -- Getting x and y of the point
    
    FOR row in EXECUTE 'select ST_x(ST_GeometryFromText('''||point||''', '||srid||')) as x' LOOP
    END LOOP;
x:=row.x;

    FOR row in EXECUTE 'select ST_y(ST_GeometryFromText('''||point||''', '||srid||')) as y' LOOP
    END LOOP;
y:=row.y;

    -- Searching for a link within the distance

    FOR row in EXECUTE 'select osm_id, ST_distance(geom, ST_GeometryFromText('''||point||''', '||srid||')) as dist from '||tbl||
' where ST_setsrid(''BOX3D('||x-distance||' '||y-distance||', '||x+distance||' '||y+distance||')''::BOX3D, '||srid||')&&geom order by dist asc limit 1'
    LOOP
    END LOOP;

    IF row.osm_id IS NULL THEN
--RAISE EXCEPTION 'Data cannot be matched';
RETURN NULL;
    END IF;

    RETURN row.osm_id;

END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;

-- zdrojovy kod funkce find_node_by_nearest_link_within_distance
CREATE OR REPLACE FUNCTION find_node_by_nearest_link_within_distance(point varchar,
distance double precision, tbl varchar)
RETURNS link_point AS
$$
DECLARE
    row record;
    link integer;
    d1 double precision;
    d2 double precision;
    field varchar;
    res link_point;
    
    srid integer;
BEGIN

    FOR row IN EXECUTE 'select ST_srid(geom) as srid from '||tbl||' where osm_id = (select min(osm_id) from '||tbl||')' LOOP
    END LOOP;
srid:= row.srid;


    -- Searching for a nearest link
    
    FOR row in EXECUTE 'select id from find_neareST_link_within_distance('''||point||''', '||distance||', '''||tbl||''') as id'
    LOOP
    END LOOP;
    IF row.id is null THEN
        res.id = -1;
        RETURN res;
    END IF;
    link:=row.id;

    -- Check what is nearer - source or target
    
    FOR row in EXECUTE 'select ST_distance((select ST_StartPoint(geom) from '||tbl||' where osm_id='||link||'), ST_geometryfromtext('''||point||''', '||srid||')) as dist'
    LOOP
    END LOOP;
    d1:=row.dist;

    FOR row in EXECUTE 'select ST_distance((select ST_EndPoint(geom) from '||tbl||' where osm_id='||link||'), ST_geometryfromtext('''||point||''', '||srid||')) as dist'
    LOOP
    END LOOP;
    d2:=row.dist;

    IF d1<d2 THEN
field:='source';
    ELSE
field:='target';
    END IF;
    
    FOR row in EXECUTE 'select '||field||' as id, '''||field||''' as f from '||tbl||' where osm_id='||link
    LOOP
    END LOOP;
        
    res.id:=row.id;
    res.name:=row.f;
    
    RETURN res;

END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;

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

/*
 DOTAZY
*/

-- ATRIBUTOVE

-- 1.1 Jaky je nejdelsi usek silnic? Vypiste nazev, popis a delku.
   SELECT nazev,popis, length FROM silnice 
   ORDER BY length DESC LIMIT 1;

-- 1.2 Kolik automycek ma v databazi ulozen nazev?
   SELECT COUNT(*) FROM motorismus 
   WHERE popis = 'car_wash' AND nazev != '';

-- 1.3 Ktere obce v Olomouckem kraji nezacinaji na 'D' a konci na 'ov'? Vypiste id a nazev.
   SELECT ogc_fid, nazev_eng FROM obce 
   WHERE nazev_eng LIKE '%ov' AND nk='OL' AND nazev_eng NOT LIKE 'D%';

-- 1.4 Kolik km dalnic je v CR?
   SELECT SUM(length)/1000 AS delka FROM silnice
   WHERE popis IN ('motorway', 'motorway_link');
   
   
-- PROSTOROVE

-- 2.1 Vypiste jmeno a geometrii kazde benzinky, ktera ma nejdale 50 m vzdalene parkoviste.
   SELECT benzinky.nazev, ST_AsText(benzinky.geom) FROM benzinky 
   JOIN parkoviste ON ST_DWithin(benzinky.geom, parkoviste.geom, 50);

-- 2.2 Kolik benzinek je na uzemi Prahy?
   SELECT COUNT(*) FROM benzinky 
   JOIN (SELECT geom FROM obce
   WHERE nazev_eng = 'Praha') AS Praha 
   ON ST_Intersects(benzinky.geom, Praha.geom);

-- 2.3 Jaka je nejjiznejsi benzinka v Olomouckem kraji a jaka je jeji Y souradnice?
   SELECT benzinky.nazev, ST_Y(benzinky.geom) AS y FROM benzinky 
   JOIN obce ON ST_Intersects(benzinky.geom, obce.geom) 
   WHERE obce.nk = 'OL' ORDER BY y ASC LIMIT 1;

-- 2.4 Vypiste nazev a obvod vsech prumyslovych zon ktere jsou vzdalene do 5 km od benzinove pumpy.
   SELECT DISTINCT prum_zony.nazev, ST_Perimeter(prum_zony.geom) AS obvod 
   FROM prum_zony 
   JOIN benzinky ON ST_DWithin(prum_zony.geom, benzinky.geom, 5000);

-- 2.5 Vypiste prumyslove zony, ktere maji v okoli 500 m vice nez 2 ha travnatych ploch a vymeru techto ploch v ha.
   SELECT DISTINCT prum_zony.nazev, SUM(ST_Area(trava.geom))/1e4 AS area 
   FROM prum_zony 
   JOIN trava ON ST_DWithin(prum_zony.geom,trava.geom,500) 
   WHERE trava.popis = 'grass' group by prum_zony.geom, prum_zony.nazev 
   HAVING SUM(ST_Area(trava.geom))/1e4 > 2 ORDER BY area DESC;

-- 2.6 Jaka je prumerna rozloha (ha) a pocet prumyslovych zon, ktere jsou jizneji nez Praha a severneji nez ceske Budejovice?
   SELECT DISTINCT (SUM(ST_Area(prum_zony.geom))/1e4)/COUNT(*) AS prumer, COUNT(*) AS pocet 
   FROM prum_zony 
   JOIN obce_bod AS ob1 ON (ST_y(ST_centroid(prum_zony.geom)) < ST_y(ob1.geom)) 
   JOIN obce_bod AS ob2 ON ((ST_y(ST_centroid(prum_zony.geom)) > ST_y(ob2.geom))) 
   WHERE ob1.nazob_eng='Praha' and ob2.nazob_eng='Ceske Budejovice';

-- 2.7  Vypiste nazev, popis a geometrii benzinek, ktere jsou v okruhu 5 km od centra Prahy (bodova vrstva).
   SELECT nazev, popis, ST_Astext(geom) FROM benzinky 
   WHERE ST_Intersects(geom,ST_Buffer((SELECT geom FROM obce_bod WHERE nazob_eng = 'Praha'),5000));

-- 2.8 Ktera prumyslova plocha s vymerou vetsi nez 10 ha lezi nejblize centru Ostravy (bod), jaka je jeji rozloha a vzdalenost od centra Ostravy?
   SELECT nazev, ST_Area(prum_zony.geom)/1e4 AS vymera, ST_Distance(prum_zony.geom, obce_bod.geom) AS vzdalenost 
   FROM prum_zony 
   JOIN obce_bod ON (obce_bod.nazob_eng='Ostrava') 
   AND ST_Area(prum_zony.geom)/1e4 > 10 ORDER BY vzdalenost ASC LIMIT 1;

-- 2.9  Jaka je rozloha vsech travnatych ploch v ha ve Znojme?
   SELECT SUM(St_Area(St_Intersection(t.geom, o.geom)))/1e4 AS plocha_ha FROM trava AS t
   JOIN obce AS o ON St_Intersects(t.geom, o.geom)
   WHERE nazev_eng = 'Znojmo'; 
   
-- 2.10 Jaka je prima vzdalenost v km z Karlovych Varu do Prahy?
   SELECT ST_Distance(a.geom, b.geom)/1000 AS delka_km FROM obce_bod AS a, obce_bod AS b
   WHERE a.nazob_eng = 'Karlovy Vary' AND b.nazob_eng = 'Praha';

-- 2.11 Vypiste kraj s nejmensim pomerem plochy travy ku plose prumyslovych zon.
  SELECT ST_Area(trava.geom)/ST_Area(prum_zony.geom) AS pomer, obce.nk FROM obce 
  JOIN trava ON ST_Intersects(obce.geom,trava.geom) 
  JOIN prum_zony ON ST_Intersects(obce.geom,prum_zony.geom) 
  GROUP BY obce.nk, trava.geom, prum_zony.geom ORDER BY pomer ASC LIMIT 1;
  
   
-- SITOVE

-- 3.1 Ktere benzinky jsou vzdalene do 500 metru od nejkratsi trasy mezi dvema uzlovymi body 1099,8316?
   SELECT DISTINCT benzinky.osm_id, benzinky.nazev, benzinky.popis 
   FROM (SELECT ST_buffer(geom,500) AS buffer,silnice.osm_id AS osm_id 
   FROM shortest_path('SELECT osm_id AS id,source,target,length AS cost 
                       FROM b12.silnice',1099,8316,false,false) AS path 
   JOIN silnice ON path.edge_id=silnice.osm_id) AS A	
   JOIN benzinky ON ST_intersects(A.buffer,benzinky.geom);

-- 3.2 Kterymi obcemi se projizdi na nejkratsi trase mezi temito dvema body?
  SELECT DISTINCT nazev_eng
  FROM (SELECT geom, silnice.osm_id AS osm_id 
  FROM shortest_path_astar('SELECT osm_id AS id,source,target,length AS cost,x1,y1,x2,y2 
                            FROM silnice', 1099,8316, false, false) AS path 
  JOIN silnice ON path.edge_id=silnice.osm_id) AS A
  JOIN obce ON ST_intersects(A.geom,obce.geom) order by nazev_eng;

-- 3.3  Jaka je nejkratsi cesta z Karlovych Varu do Opavy? reste postupne pomoci algoritmu Djistkra, A-Star a Shooting Star.
 -- Djistkra 
  SELECT * FROM shortest_path(
  'SELECT osm_id AS id,source,target,length AS cost FROM b12.silnice',
  (SELECT id FROM find_node_by_nearest_link_within_distance(
  (SELECT ST_AsText(geom) FROM obce_bod WHERE nazob_eng = 'Karlovy Vary'),5000,'silnice')),
  (SELECT id FROM find_node_by_nearest_link_within_distance(
  (SELECT ST_AsText(geom) FROM obce_bod WHERE nazob_eng = 'Opava'),5000,'silnice')),false,false);

-- A-Star
  SELECT * FROM shortest_path_astar(
  'SELECT osm_id as id,source,target,length as cost,x1,y1,x2,y2 from silnice',
  (SELECT id from find_node_by_nearest_link_within_distance(
  (SELECT st_astext(geom) FROM obce_bod where nazob_eng = 'Karlovy Vary'),5000,'silnice')),
  (SELECT id from find_node_by_nearest_link_within_distance(
  (SELECT st_astext(geom) FROM obce_bod where nazob_eng = 'Opava'),5000,'silnice')),false, false);

-- Shooting Star
  SELECT * FROM shortest_path_shooting_star(
  'SELECT osm_id as id,source,target,length as cost,x1,y1,x2,y2,rule,to_cost from silnice',
  (SELECT * from find_nearest_link_within_distance(
  (SELECT st_astext(geom) FROM obce_bod WHERE nazob_eng = 'Karlovy Vary'),5000,'silnice')),
  (SELECT * from find_nearest_link_within_distance(
  (SELECT st_astext(geom) FROM obce_bod WHERE nazob_eng = 'Opava'),5000,'silnice')),false,false);

-- 3.4  O kolik delsi bude cesta z bodu 5626 do 12073, jestlize nepojedeme po dalnici? 
  SELECT (b.delka-a.delka)/1000 AS rozdil_km FROM
  (SELECT SUM(length) AS delka
   FROM shortest_path('SELECT osm_id AS id, source, target, length AS cost 
   FROM silnice', 5626, 12073, FALSE, FALSE) AS cesta2
  JOIN silnice ON cesta2.edge_id = silnice.osm_id) AS a
  JOIN
  (SELECT SUM(length) AS delka
   FROM shortest_path('(SELECT osm_id AS id, source, target, length AS cost FROM silnice 
   WHERE popis NOT IN (''motorway'', ''motorway_link''))', 5626, 12073, FALSE, FALSE) AS cesta
  JOIN silnice ON cesta.edge_id = silnice.osm_id) AS b
  ON a.delka != b.delka;

-- 3.5 Jakym smerem se vydate, pokud pojedete po ceste z prumyslove zony Papcel do Opavy?
  SELECT ST_Azimuth(
 (SELECT ST_Point(ST_X(vertices_tmp.the_geom),ST_Y(vertices_tmp.the_geom)) FROM silnice 
   JOIN vertices_tmp ON silnice.source=vertices_tmp.id 
   WHERE osm_id IN 
   (SELECT edge_id FROM shortest_path('SELECT osm_id AS id,source,target,length AS cost FROM silnice',
   (SELECT id FROM find_node_by_nearest_link_within_distance(
   (SELECT st_astext(st_centroid(geom)) FROM prum_zony where nazev = 'Papcel'),5000,'silnice')),
   (SELECT id FROM find_node_by_nearest_link_within_distance(
   (SELECT st_astext(geom) FROM obce_bod where nazob_eng = 'Opava'),5000,'silnice')), false,false) limit 1)),
  (SELECT ST_Point(ST_X(vertices_tmp.the_geom),ST_Y(vertices_tmp.the_geom)) FROM silnice 
   JOIN vertices_tmp ON silnice.target=vertices_tmp.id 
   WHERE osm_id in 
   (SELECT edge_id FROM shortest_path('SELECT osm_id AS id,source,target,length AS cost FROM silnice',
   (SELECT id FROM find_node_by_nearest_link_within_distance(
   (SELECT st_astext(st_centroid(geom)) FROM prum_zony where nazev = 'Papcel'),5000,'silnice')),
   (SELECT id FROM find_node_by_nearest_link_within_distance(
   (SELECT st_astext(geom) FROM obce_bod where nazob_eng = 'Opava'),5000,'silnice')),false,false) limit 1)))
  /(2*pi())*360 AS azimut;
