/*
153UZPD - Semestralni projekt
skupina: a13
clenove skupiny: Cimburova, Bambula, Dlesk
semestr: letni 2013
*/

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

/*
TVORBA JEDNOTLIVYCH VRSTEV
==========================
*/

-- tvorba tabulky cyklostezek

CREATE TABLE cyklostezky AS
SELECT line.* FROM osm.czech_line AS line JOIN
(SELECT DISTINCT unnest(parts) AS osm_id FROM osm.czech_rels WHERE 'bicycle' = ANY(tags)) AS foo
ON line.osm_id = foo.osm_id;
DELETE FROM cyklostezky WHERE bicycle = 'no';

-- nahrazeni hodnot ve sloupci 'highway' za 'cycleway'

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

-- tvorba tabulky silnic prvni, druhe a treti tridy

CREATE TABLE silnice AS
SELECT * FROM czech_line
WHERE highway IN ('primary','secondary', 'tertiary') AND (bicycle IN ('no') OR bicycle IS NULL);
------------------------------------------------------------------------------------

-- tvorba tabulky komunikaci 

CREATE TABLE komunikace AS(SELECT osm_id, highway, geom FROM cyklostezky 
 UNION SELECT osm_id, highway, geom FROM silnice);
   
ALTER TABLE komunikace ADD COLUMN gid serial;
ALTER TABLE komunikace ADD PRIMARY KEY (gid);
CREATE INDEX komunikace_geom_gist ON komunikace USING gist (geom);
------------------------------------------------------------------------------------

-- tvorba tabulky pamatek

CREATE TABLE  pamatky AS 
SELECT * FROM czech_point WHERE historic IS NOT NULL;

ALTER TABLE pamatky ADD COLUMN gid serial;
ALTER TABLE pamatky ADD PRIMARY KEY (gid);
CREATE INDEX pamatky_geom_gist1 ON pamatky USING gist (geom);
------------------------------------------------------------------------------------

-- tvorba tabulky lesu

CREATE TABLE lesy AS 
SELECT * FROM czech_polygON WHERE landuse = 'forest';

ALTER TABLE lesy ADD COLUMN gid SERIAL;
ALTER TABLE lesy ADD PRIMARY KEY (gid);
CREATE INDEX lesy_geom_gist1 ON pamatky USING gist (geom);
------------------------------------------------------------------------------------

-- tabulka obci

CREATE TABLE obce AS SELECT * FROM gis1.obce;

-- transformace tabulky obci z S-JTSK do Google Mercator 
ALTER TABLE a13.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');

ALTER TABLE obce ADD COLUMN gid serial; 
ALTER TABLE obce ADD PRIMARY KEY (gid); 
CREATE INDEX obce_geom_gist ON obce USING GIST (geom);
------------------------------------------------------------------------------------

-- obce bodova vrstva

CREATE TABLE obce_body AS SELECT * FROM gis1.obce_b;

-- transformace bodove vrstvy obci z S-JTSK do Google Mercator 
ALTER TABLE a13.obce_body RENAME COLUMN geom TO geom1;
SELECT AddGeometryColumn('obce_body', 'geom', 900913, 'point', 2);
UPDATE obce_body SET geom = ST_Transform(geom1, 900913);
SELECT DropGeometryColumn('obce_body', 'geom1');

ALTER TABLE obce_body ADD COLUMN gid serial; 
ALTER TABLE obce_body ADD PRIMARY KEY (gid); 
CREATE INDEX obce_body_geom_gist ON obce_body USING GIST (geom);
------------------------------------------------------------------------------------

-- tabulka vodnich_ploch

CREATE TABLE vodni_plochy AS 
SELECT * FROM czech_polygon WHERE lANDuse = 'reservoir';
 
ALTER TABLE vodni_plochy ADD COLUMN gid serial; 
ALTER TABLE vodni_plochy ADD PRIMARY KEY (gid); 
CREATE INDEX vodni_plochy_geom_gist ON vodni_plochy USING GIST (geom); 
------------------------------------------------------------------------------------

-- tabulka rek

CREATE TABLE reky AS
SELECT * FROM czech_line  WHERE waterway = 'river'
ALTER TABLE reky ADD COLUMN gid serial;
ALTER TABLE reky ADD PRIMARY KEY (gid);
CREATE INDEX reky_geom_gist ON reky USING gist (geom);
------------------------------------------------------------------------------------

-- potoky, pouze pojmenovane kvuli prehlednosti

CREATE TABLE potoky AS SELECT * FROM czech_line WHERE waterway = 'stream' AND name IS NOT NULL;
ALTER TABLE potoky ADD COLUMN gid serial;
ALTER TABLE potoky ADD PRIMARY KEY (gid);
CREATE INDEX potoky_geom_gist ON potoky USING gist (geom);
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

/*
overeni validity dat
====================
*/  

SELECT ogc_fid, ST_IsValidReason(geom) AS duvod FROM obce WHERE ST_IsValid(geom) = FALSE;       --chyba Ring Self-intersection at or near point 1670500.0546917408 6553612.614894894
SELECT ogc_fid, ST_IsValidReason(geom) AS duvod FROM obce_body WHERE ST_IsValid(geom) = FALSE;  --OK
SELECT osm_id, ST_IsValidReason(geom) AS duvod FROM cyklostezky WHERE ST_IsValid(geom) = FALSE; --OK
SELECT osm_id, ST_IsValidReason(geom) AS duvod FROM lesy WHERE ST_IsValid(geom) = FALSE;        --OK          
SELECT osm_id, ST_IsValidReason(geom) AS duvod FROM pamatky WHERE ST_IsValid(geom) = FALSE;     --OK
SELECT osm_id, ST_IsValidReason(geom) AS duvod FROM potoky WHERE ST_IsValid(geom) = FALSE;      --OK
SELECT osm_id, ST_IsValidReason(geom) AS duvod FROM reky WHERE ST_IsValid(geom) = FALSE;        --OK
SELECT osm_id, ST_IsValidReason(geom) AS duvod FROM vodni_plochy WHERE ST_IsValid(geom) = FALSE;--OK
SELECT osm_id, ST_IsValidReason(geom) AS duvod FROM silnice WHERE ST_IsValid(geom) = FALSE;     --OK
SELECT osm_id, ST_IsValidReason(geom) AS duvod FROM komunikace WHERE ST_IsValid(geom) = FALSE;  --OK
SELECT osm_id, ST_IsValidReason(geom) AS duvod FROM komunikace WHERE ST_IsValid(geom) = FALSE;  --OK
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

/*
priprava na pg routing
======================
*/ 
ALTER TABLE komunikace ADD COLUMN source INTEGER;
ALTER TABLE komunikace ADD COLUMN target INTEGER;
ALTER TABLE komunikace ADD COLUMN length FLOAT;

CREATE INDEX source_idx ON komunikace(source);
CREATE INDEX target_idx ON komunikace(target);

SELECT assign_vertex_id('a13', 'komunikace', 1,'geom', 'gid');
UPDATE komunikace SET length = ST_Length(geom);

ALTER TABLE komunikace ADD COLUMN x1 DOUBLE PRECISION;
ALTER TABLE komunikace ADD COLUMN y1 DOUBLE PRECISION;
ALTER TABLE komunikace ADD COLUMN x2 DOUBLE PRECISION;
ALTER TABLE komunikace ADD COLUMN y2 DOUBLE PRECISION;

UPDATE komunikace SET x1 = ST_x(ST_startpoint(geom));
UPDATE komunikace SET y1 = ST_y(ST_startpoint(geom));
UPDATE komunikace SET x2 = ST_x(ST_endpoint(geom));
UPDATE komunikace SET y2 = ST_y(ST_endpoint(geom));

ALTER TABLE komunikace ADD COLUMN to_cost DOUBLE PRECISION;
ALTER TABLE komunikace ADD COLUMN rule text;

-- ohodnoceni vrstvy komunikace
ALTER TABLE komunikace ADD COLUMN cost INTEGER; 
UPDATE komunikace SET cost=1 ;
UPDATE komunikace SET cost=2 WHERE highway IN ('tertiary');
UPDATE komunikace SET cost=3 WHERE highway IN ('secondary');	
UPDATE komunikace SET cost=4 WHERE highway IN ('primary');
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

/*
zdrojovy kod funkci pro hledani nejblizsich prvku
================================================
*/
 
CREATE TYPE link_point AS (id integer, name varchar);

-------------------------------------------------------------------
-- This function finds nearest link to a given node
-- point - text representation of point
-- distance - function will search for a link within this distance
-- tbl - table name
-------------------------------------------------------------------
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 getsrid(geom) as srid from '||tbl||' where gid = (select min(gid) from '||tbl||')' LOOP
    END LOOP;
	srid:= row.srid;
    
    -- Getting x and y of the point
    
    FOR row in EXECUTE 'select x(GeometryFromText('''||point||''', '||srid||')) as x' LOOP
    END LOOP;
	x:=row.x;

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

    -- Searching for a link within the distance

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

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

    RETURN row.gid;

END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;


-------------------------------------------------------------------
-- This function finds nearest node as a source or target of the
-- nearest link
-- point - text representation of point
-- distance - function will search for a link within this distance
-- tbl - table name
-------------------------------------------------------------------

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 getsrid(geom) as srid from '||tbl||' where gid = (select min(gid) 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 distance((select StartPoint(geom) from '||tbl||' where gid='||link||'), GeometryFromText('''||point||''', '||srid||')) as dist'
    LOOP
    END LOOP;
    d1:=row.dist;

    FOR row in EXECUTE 'select distance((select EndPoint(geom) from '||tbl||' where gid='||link||'), 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 gid='||link
    LOOP
    END LOOP;
        
    res.id:=row.id;
    res.name:=row.f;
    
    RETURN res;


END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;


/*
atributove dotazy
=================
*/
-- Jaka je celkove delka všech cyklostezek [km] ?
SELECT round(sum(length)/1000) AS delka_km FROM komunikace WHERE highway = 'cycleway';
	
-- Jak pocetne jsou jednotlive typy pamatek? Seradte od nejvyššiho.
SELECT distinct historic AS typ, count (*) AS pocet FROM pamatky GROUP BY historic ORDER BY pocet desc;
  	
-- Ktera je rozlohou nejvetši obec, ktera ma v nazvu 'Tyn'? Vypište nazev a rozlohu v ha.
SELECT nazev, round(round(area)/10000) AS rozloha FROM obce WHERE nazev like '%Tyn%' ORDER BY rozloha desc limit 1;

-- Z kolika useki se sklada Vltava?
SELECT distinct name, count (*) AS pocet FROM reky WHERE name = 'Vltava' GROUP BY name;

-- Ktere jsou 3 nejsevernejši obce v cR?
SELECT nazob AS nazev  FROM obce_body ORDER BY sxob asc limit 3;
     	

 /*-------
prostorove dotazy
-----*/
-- Kolik % obci na svem uzemi nema cyklostezku?

 SELECT COUNT(distinct(obce.name))*100./(select count(*) from obce) AS maji 
 FROM obce AS obce
 JOIN cyklostezky AS cyklo ON
 ST_Intersects(obce.geom, cyklo.geom);
 
-- Kolik % delky cyklostezek prochazi lesem?

SELECT sum(ST_Length(ST_Intersection(cyklo.geom,lesy.geom)))*100./(
SELECT sum(ST_Length(cyklostezky.geom)) FROM cyklostezky)
FROM cyklostezky AS cyklo
JOIN lesy AS lesy ON
ST_Intersects(cyklo.geom,lesy.geom);
 
-- Ktere cyklostezky lezi do 5 km od reky?

CREATE TABLE reky_buffer AS SELECT ST_Buffer(reky.geom, 5000) AS geom1 FROM reky;

CREATE TABLE dotaz_cykloreky AS 
SELECT ST_Intersection(reky_buffer.geom1, cyklostezky.geom)
FROM   cyklostezky 
JOIN   reky_buffer
ON     ST_Intersects(reky_buffer.geom1, cyklostezky.geom);

-- Kolik % Jihoceskeho kraje zaujimaji vodni plochy?

SELECT sum(ST_Area(vodni_plochy.geom))*100/(
SELECT sum(ST_Area(obce.geom))
FROM   obce
WHERE  nk = 'JC') from vodni_plochy;

-- Kolik cyklostezek projizdeji pres most? 

 SELECT COUNT(*) FROM cyklostezky AS cyklo
 JOIN reky AS reky ON
 ST_Intersects(reky.geom, cyklo.geom);
 
  /*-------
pg routing
-----*/

-- Kterymi obcemi se projizdi z Jihlavy do Prahy?

SELECT distinct nazev FROM
	(SELECT * FROM shortest_path_shooting_star('SELECT gid AS id, source, 
		target, length*cost AS cost, x1, y1, x2, y2, rule, to_cost 
		FROM komunikace', 1456, 1581, false, false) AS cesta
JOIN komunikace ON cesta.edge_id=komunikace.gid) as a
JOIN obce ON ST_Intersects(a.geom,obce.geom);

-- Kolik hradi lezi 1 km od trasy z Jihlavy do Prahy a ktere? 

SELECT distinct p.osm_id, name
FROM (SELECT ST_Buffer(geom,1000) AS buf
	FROM shortest_path_astar
        ('SELECT gid AS id, source, target, length*cost AS cost, x1, y1, x2, y2 
        FROM komunikace', 79907, 24486, false, false) AS cesta
	JOIN komunikace as k ON cesta.edge_id=k.gid) as a
	JOIN pamatky AS p ON ST_Intersects(a.buf, p.geom) 
WHERE historic='castle'; 

-- Porovnani metod z hlediska delky
        -- shooting star
SELECT round(sum(length)/1000) FROM shortest_path_shooting_star
	('SELECT gid AS id,source, target, length*cost AS cost, x1, y1, x2, y2, rule, to_cost 
	FROM komunikace', 1456, 1581, false, false) AS cesta
JOIN komunikace AS kom ON cesta.edge_id = kom.gid
        
        -- A Star
SELECT round(sum(length)/1000) FROM shortest_path_astar
       	('SELECT gid AS id, source, target, length*cost AS cost, x1, y1, x2, y2 
         FROM komunikace', 79907, 24486, false, false) AS cesta
JOIN komunikace AS kom ON cesta.edge_id = kom.gid

        -- Dijkstruv algoritmus
SELECT round(sum(length)/1000) FROM shortest_path
        ('SELECT gid AS id, source, target, length as cost 
	FROM komunikace' , 79907, 24486, false, false) AS cesta
JOIN komunikace AS kom ON cesta.edge_id = kom.gid



-- Rozdil mezi cestou z Vichove nad Jizerou do zelezneho Brodu na kole a autem (bez cyklostezek). (Dijkstruv algoritmus)
SELECT 
(
	(SELECT SUM(kolo.cost) FROM
	(SELECT * FROM shortest_path('SELECT gid AS id, source, target, length*cost as cost 
		FROM komunikace where highway != ''cycleway''' , 46860, 16708, false, false)) AS kolo) -
	
	(SELECT SUM(auto.cost) FROM 
	(SELECT * FROM shortest_path('SELECT gid AS id, source, target, length*cost as cost 
		FROM komunikace' , 46860, 16708, false, false)) AS auto) 	
)/1000 AS rozdil_km;
