set search_path to c11,public,osm;


-------------------- VYTVOŘENÍ VRSTEV -------------------------------



-- vytvoření transformované tabulky Obce
------------------------------------------------
CREATE TABLE obce AS SELECT * FROM gis1.obce;
UPDATE obce SET geom = ST_Transform(geom, 900913);

CREATE INDEX obce_geom_gist
  ON c11.obce
  USING gist(geom);

SELECT populate_geometry_columns('c11.obce'::regclass);


-- vymazání sloupců,které nepotrebujem
-------------------------------------------------
ALTER TABLE obce DROP COLUMN nazev_eng;
ALTER TABLE obce DROP COLUMN kodorp;
ALTER TABLE obce DROP COLUMN nk;
ALTER TABLE obce DROP COLUMN iczuj;
ALTER TABLE obce DROP COLUMN obyv91;
ALTER TABLE obce DROP COLUMN kraj1960;
ALTER TABLE obce DROP COLUMN kodok;
ALTER TABLE obce DROP COLUMN kodob;
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 obyv01;
ALTER TABLE obce DROP COLUMN cispou;
ALTER TABLE obce DROP COLUMN nazorp_eng;



-- přejmenování sloupců
--------------------------------------------------
ALTER TABLE obce RENAME COLUMN area TO vymera;
ALTER TABLE obce RENAME COLUMN perimeter TO obvod;
ALTER TABLE obce RENAME COLUMN nuts4 TO okres;
ALTER TABLE obce RENAME COLUMN nuts3 TO kraj;
ALTER TABLE obce RENAME COLUMN nazev TO obec;
ALTER TABLE obce RENAME COLUMN obyv02 TO obyvatel;
ALTER TABLE obce RENAME COLUMN syob TO y;
ALTER TABLE obce RENAME COLUMN sxob TO x;
ALTER TABLE obce RENAME COLUMN nazorp TO orp;


-- přejmenování kodu krajů na název
-------------------------------------------------
UPDATE obce SET kraj='hl.m.Praha' WHERE kraj='CZ011';
UPDATE obce SET kraj='Ústecký' WHERE kraj='CZ042';
UPDATE obce SET kraj='Moravskoslezský' WHERE kraj='CZ081';
UPDATE obce SET kraj='Pardubický' WHERE kraj='CZ053';
UPDATE obce SET kraj='Plzeňský' WHERE kraj='CZ032';
UPDATE obce SET kraj='Karlovarský' WHERE kraj='CZ041';
UPDATE obce SET kraj='Vysočina' WHERE kraj='CZ061';
UPDATE obce SET kraj='Olomoucký' WHERE kraj='CZ071';
UPDATE obce SET kraj='Liberecký' WHERE kraj='CZ051';
UPDATE obce SET kraj='Jihomoravský' WHERE kraj='CZ062';
UPDATE obce SET kraj='Středočeský' WHERE kraj='CZ021';
UPDATE obce SET kraj='Jihočeský' WHERE kraj='CZ031';
UPDATE obce SET kraj='Zlínský' WHERE kraj='CZ072';
UPDATE obce SET kraj='Královéhradecký' WHERE kraj='CZ052';



--  přejemenování kodu okresů na název
-------------------------------------------
UPDATE obce SET okres='Praha' WHERE okres='CZ0110';
UPDATE obce SET okres='Benešov' WHERE okres='CZ0211';
UPDATE obce SET okres='Beroun' WHERE okres='CZ0212';
UPDATE obce SET okres='Kladno' WHERE okres='CZ0213';
UPDATE obce SET okres='Kolín' WHERE okres='CZ0214';
UPDATE obce SET okres='Kutná Hora' WHERE okres='CZ0215';
UPDATE obce SET okres='Mělník' WHERE okres='CZ0216';
UPDATE obce SET okres='Mladá Boleslav' WHERE okres='CZ0217';
UPDATE obce SET okres='Nymburk' WHERE okres='CZ0218';
UPDATE obce SET okres='Praha-východ' WHERE okres='CZ0219';
UPDATE obce SET okres='Praha-západ' WHERE okres='CZ021A';
UPDATE obce SET okres='Příbram' WHERE okres='CZ021B';
UPDATE obce SET okres='Rakovník' WHERE okres='CZ021C';

UPDATE obce SET okres='České Budějovice' WHERE okres ='CZ0311';
UPDATE obce SET okres='Český Krumlov' WHERE okres ='CZ0312';
UPDATE obce SET okres='Jindřichův Hradec' WHERE okres ='CZ0313';
UPDATE obce SET okres='Písek' WHERE okres ='CZ0314';
UPDATE obce SET okres='Prachatice' WHERE okres ='CZ0315';
UPDATE obce SET okres='Strakonice' WHERE okres ='CZ0316';
UPDATE obce SET okres='Tábor' WHERE okres ='CZ0317';
UPDATE obce SET okres='Domažlice' WHERE okres ='CZ0321';
UPDATE obce SET okres='Klatovy' WHERE okres ='CZ0322';
UPDATE obce SET okres='Plzeň-město' WHERE okres ='CZ0323';
UPDATE obce SET okres='Plzeň-jih' WHERE okres ='CZ0324';
UPDATE obce SET okres='Plzeň-sever' WHERE okres ='CZ0325';
UPDATE obce SET okres='Rokycany' WHERE okres ='CZ0326';
UPDATE obce SET okres='Tachov' WHERE okres ='CZ0327';

UPDATE obce SET okres='Cheb' WHERE okres ='CZ0411';
UPDATE obce SET okres='Karlovy Vary' WHERE okres ='CZ0412';
UPDATE obce SET okres='Sokolov' WHERE okres ='CZ0413';
UPDATE obce SET okres='Děčín' WHERE okres ='CZ0421';
UPDATE obce SET okres='Chomutov' WHERE okres ='CZ0422';
UPDATE obce SET okres='Litoměřice' WHERE okres ='CZ0423';
UPDATE obce SET okres='Louny' WHERE okres ='CZ0424';
UPDATE obce SET okres='Most' WHERE okres ='CZ0425';
UPDATE obce SET okres='Teplice' WHERE okres ='CZ0426';
UPDATE obce SET okres='Ústí nad Labem' WHERE okres ='CZ0427';

UPDATE obce SET okres='Česká Lípa' WHERE okres ='CZ0511';
UPDATE obce SET okres='Jablonec nad Nisou' WHERE okres ='CZ0512';
UPDATE obce SET okres='Liberec' WHERE okres ='CZ0513';
UPDATE obce SET okres='Semily' WHERE okres ='CZ0514';
UPDATE obce SET okres='Hradec Králové' WHERE okres ='CZ0521';
UPDATE obce SET okres='Jičín' WHERE okres ='CZ0522';
UPDATE obce SET okres='Náchod' WHERE okres ='CZ0523';
UPDATE obce SET okres='Rychnov nad Kněžnou' WHERE okres ='CZ0524';
UPDATE obce SET okres='Trutnov' WHERE okres ='CZ0525';
UPDATE obce SET okres='Chrudim' WHERE okres ='CZ0531';
UPDATE obce SET okres='Pardubice' WHERE okres ='CZ0532';
UPDATE obce SET okres='Svitavy' WHERE okres ='CZ0533';
UPDATE obce SET okres='Ústí nad Orlicí' WHERE okres ='CZ0534';

UPDATE obce SET okres='Havlíčkův Brod' WHERE okres ='CZ0611';
UPDATE obce SET okres='Jihlava' WHERE okres ='CZ0612';
UPDATE obce SET okres='Pelhřimov' WHERE okres ='CZ0613';
UPDATE obce SET okres='Třebíč' WHERE okres ='CZ0614';
UPDATE obce SET okres='Žďár nad Sázavou' WHERE okres ='CZ0615';
UPDATE obce SET okres='Blansko' WHERE okres ='CZ0621';
UPDATE obce SET okres='Brno-město' WHERE okres ='CZ0622';
UPDATE obce SET okres='Brno-venkov' WHERE okres ='CZ0623';
UPDATE obce SET okres='Břeclav' WHERE okres ='CZ0624';
UPDATE obce SET okres='Hodonín' WHERE okres ='CZ0625';
UPDATE obce SET okres='Vyškov' WHERE okres ='CZ0626';
UPDATE obce SET okres='Znojmo' WHERE okres ='CZ0627';

UPDATE obce SET okres='Jeseník' WHERE okres ='CZ0711';
UPDATE obce SET okres='Olomouc' WHERE okres ='CZ0712';
UPDATE obce SET okres='Prostějov' WHERE okres ='CZ0713';
UPDATE obce SET okres='Přerov' WHERE okres ='CZ0714';
UPDATE obce SET okres='Šumperk' WHERE okres ='CZ0715';
UPDATE obce SET okres='Kroměříž' WHERE okres ='CZ0721';
UPDATE obce SET okres='Uherské Hradiště' WHERE okres ='CZ0722';
UPDATE obce SET okres='Vsetín' WHERE okres ='CZ0723';
UPDATE obce SET okres='Zlín' WHERE okres ='CZ0724';

UPDATE obce SET okres='Bruntál' WHERE okres ='CZ0811';
UPDATE obce SET okres='Frýdek-Místek' WHERE okres ='CZ0812';
UPDATE obce SET okres='Karviná' WHERE okres ='CZ0813';
UPDATE obce SET okres='Nový Jičín' WHERE okres ='CZ0814';
UPDATE obce SET okres='Opava' WHERE okres ='CZ0815';
UPDATE obce SET okres='Ostrava-město' WHERE okres ='CZ0816';



-- vrstva Kraje
---------------------
CREATE TABLE kraje AS
SELECT       ST_Union(geom) AS geom, kraj 
FROM         obce
GROUP BY     kraj;

ALTER TABLE  kraje
ADD COLUMN   gid SERIAL;

ALTER TABLE kraje 
ADD PRIMARY KEY(gid);

CREATE INDEX kraje_geom_gist
ON           kraje
USING        GIST (geom);

SELECT populate_geometry_columns('c11.kraje'::regclass);


-- vytvoření vrstev integrovaného záchranného systému IZS
-----------------------------------------------------------
CREATE TABLE IZS_bod
AS SELECT osm_id, way AS geom, amenity AS druh ,czech_point.name AS popis 
FROM czech_point 
WHERE amenity = 'fire_station' OR amenity = 'police' OR amenity = 'hospital';

CREATE TABLE IZS_poly 
AS SELECT osm_id, way AS geom, amenity AS druh ,czech_polygon.name AS popis 
FROM czech_polygon 
WHERE amenity = 'fire_station' OR amenity = 'police' OR amenity = 'hospital';

ALTER TABLE IZS_poly ADD COLUMN gid serial;
ALTER TABLE IZS_poly ADD PRIMARY KEY(gid); 


-- test validity polygonové vrstvy
----------------------------------------
SELECT COUNT(*) 
FROM IZS_poly 
WHERE ST_IsValid(IZS_poly.geom)=FALSE;


-- odstranění duplicit z tabulky IZS_bod
------------------------------------------
DELETE FROM IZS_bod
WHERE  IZS_bod.geom IN
	(SELECT IZS_bod.geom  
	FROM IZS_bod
	JOIN IZS_poly
	ON ST_WITHIN(IZS_bod.geom,IZS_poly.geom));



-- ostranění zahraničních IZS
-------------------------------------------------------------
DELETE FROM IZS_poly
WHERE IZS_poly.geom NOT IN 
	( SELECT izs_poly.geom 
	FROM IZS_poly 
	JOIN kraje 
	ON ST_Within(IZS_poly.geom,kraje.geom));


DELETE FROM IZS_bod
WHERE izs_bod.geom NOT IN 
	( SELECT izs_bod.geom 
	FROM izs_bod 
	JOIN kraje 
	ON ST_Within(izs_bod.geom,kraje.geom));



-- spojení do jedné bodové vrstvy IZS_bod
-------------------------------------------
CREATE TABLE centr 
AS SELECT osm_id,
	ST_Centroid(izs_poly.geom) as geom,druh,popis,gid 
FROM izs_poly;

INSERT INTO izs_bod (osm_id,geom,druh,popis) 
SELECT osm_id,geom,druh,popis 
FROM centr; 


-- překlad názvů druhů IZS do češtiny
---------------------------------------------------
UPDATE IZS_bod SET druh = 'policie' WHERE druh='police';
UPDATE IZS_bod SET druh = 'nemocnice' WHERE druh='hospital';
UPDATE IZS_bod SET druh = 'požární stanice' WHERE druh='fire_station';


-- zvětšeni sloupců
----------------------
ALTER TABLE IZS_bod ALTER COLUMN druh type varchar(100);
ALTER TABLE IZS_bod alter COLUMN popis type varchar(100);



-- definice primárního klíče
-------------------------------------------------
ALTER TABLE IZS_bod ADD COLUMN gid serial;
ALTER TABLE IZS_bod ADD PRIMARY KEY(gid); 

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

-- aktualizace metadatové tabulky
------------------------------------
SELECT populate_geometry_columns('c11.IZS_bod'::regclass);


CREATE TABLE  zahrady AS 
SELECT osm_id, ST_Multi(way) AS geom  
FROM czech_polygon WHERE leisure='garden';
SELECT populate_geometry_columns('c11.zahrady'::regclass);


ALTER TABLE zahrady ADD COLUMN gid serial;
ALTER TABLE zahrady ADD PRIMARY KEY(gid);


----------------VALIDACE POLYGONU,vrstva zahrady ----------------


CREATE TABLE validni_polygony AS 
SELECT (ST_Dump(ST_Polygonize(ST_Union(geom, ST_Startpoint(geom))))).geom AS geom, gid, path[1] AS path
FROM 
(
	SELECT (ST_ExteriorRing((ST_DumpRings((ST_Dump(geom)).geom)).geom)) AS geom,(ST_DumpRings((ST_Dump(geom)).geom)).path AS path , gid AS gid
	FROM zahrady
	WHERE ST_IsValid(geom)= FALSE
) as ext_linestring
GROUP BY geom, gid, path;

ALTER TABLE validni_polygony 
ADD COLUMN gid_unique serial;

UPDATE validni_polygony  
SET geom = with_holes.geom
FROM  
(
	SELECT  st_difference(shells.geom,holes.geom)as geom, shells.gid_unique 
	FROM validni_polygony as shells
	JOIN 
	(
		SELECT  st_union(geom) as geom, gid 
		FROM validni_polygony
		WHERE path!=0
		GROUP BY gid
	) as holes
	ON holes.gid=shells.gid 
	AND shells.path=0
)
AS with_holes 
WHERE path=0 AND validni_polygony.gid_unique= with_holes.gid_unique;

DELETE FROM  validni_polygony
WHERE path!=0;

DELETE FROM  validni_polygony 
WHERE gid_unique IN 
(
	SELECT   holes.gid_unique 
	FROM validni_polygony AS shells
	JOIN validni_polygony AS holes
	ON shells.gid = holes.gid 
	AND shells.gid_unique != holes.gid_unique 
	AND st_relate(holes.geom,shells.geom,'FF2F1F212')
);

CREATE TABLE validni_polygony_collect AS 
SELECT ST_Collect(geom) AS geom, gid FROM
(
	SELECT (ST_Dump(geom)).geom as geom, gid 
	FROM validni_polygony
) 
as val_p GROUP BY gid ;  

UPDATE validni_polygony_collect  
SET geom = with_holes.geom 
FROM 
(
	SELECT ST_BuildArea(geom) as geom, gid 
	FROM validni_polygony_collect 
	WHERE st_isvalid(geom)=False  
) as with_holes
WHERE  validni_polygony_collect.gid= with_holes.gid;  
 

UPDATE zahrady AS updated_table 
SET geom = ST_Multi(validni_polygony_collect.geom)
FROM validni_polygony_collect
WHERE  updated_table.gid= validni_polygony_collect.gid;


CREATE INDEX zahrady_gist
ON zahrady
USING GIST(geom);


---------------------- ATRIBUTOVE A PROSTOROVE DOTAZY --------------

--1. Kolik zahrad je do 1 km od obce Jihlava?
CREATE TABLE pokusna_zajcev AS SELECT ST_Buffer(geom, 1e3) AS geom FROM obce WHERE obec = 'Jihlava';

SELECT COUNT(*)
FROM   zahrady
JOIN   pokusna_zajcev
ON     ST_Intersects(pokusna_zajcev.geom, zahrady.geom);

--2. Kolik a jaké obce začínají na písmeno A? Výpis obcí v abecedním pořadí
SELECT obec 
FROM obce 
WHERE obec LIKE 'A%'
ORDER BY obec;

--3. Jaký je nejmenší obvod obce?
SELECT obec,obvod
FROM obce
ORDER BY obvod
LIMIT 1;

--4. V kterém kraji a obci ordinuje MuDR. Konrádová?
SELECT kraj,obec
FROM obce
JOIN izs_bod
ON ST_Within(izs_bod.geom, obce.geom)
WHERE izs_bod.popis = 'MuDr. Konrádová';

--5. Kolik městských policejních stanic ('M(m)ěstská policie')je ve vzdálenosti do 100 km od Prahy?
CREATE TABLE praha100 AS
SELECT ST_Buffer(geom, 1e5) AS geom
FROM   obce
WHERE  obec = 'Praha';

SELECT count(*)
FROM   izs_bod
JOIN   praha100
ON     ST_Intersects(praha100.geom, izs_bod.geom)
WHERE  popis LIKE '%ěstská policie%';


--6 Jaký je celkový počet evidovaných zahrad na území ČR a jaká je jejich celková výměra v hektarech?
-----------------------------------------------------------------------------------------
SELECT COUNT(gid) AS počet, ROUND(SUM(ST_AREA(geom))/1e4) AS vymera 
FROM zahrady;


--7 kolik procent byvalých okresních měst je dnes ORP
------------------------------------------------------------------------------------
SELECT ROUND((stejne.cislo/(vse.cislo/100))) 
FROM
	(SELECT COUNT(*) ::NUMERIC AS cislo 
	FROM obce 
	WHERE okres=orp AND orp=obec) AS stejne
  CROSS JOIN 
	(SELECT COUNT(distinct okres) ::NUMERIC AS cislo 
	FROM obce) AS vse ;


--8 Kolik nemocnic, hasičských stanic a policejních stanic se nachází na území Prahy?
--------------------------------------------------------------------------------------
SELECT COUNT(*) AS počet,druh 
FROM izs_bod 
JOIN obce
ON st_within(izs_bod.geom,obce.geom) 
WHERE obec='Praha' 
GROUP BY druh;


--9 Kde se nachází Hamzova léčebna. Uveďte obec,okres a kraj.
--------------------------------------------------------------------
SELECT obec,okres,kraj,popis 
FROM izs_bod
JOIN obce 
ON st_within(izs_bod.geom,obce.geom)
WHERE popis LIKE 'Hamzova léčebna';


-- 10 V jaké vzdálenosti se nachází 5 nejbližších požárních stanic od obce Chropyně? 
-- Vzdálenost uveďte v kilometrech.
------------------------------------------------------------------------------------
SELECT popis,ROUND(ST_DISTANCE(obce.geom,izs_bod.geom)/1e3) AS vzdalenost 
FROM obce,izs_bod
WHERE obec ='Chropyně' AND druh='požární stanice'
ORDER BY vzdalenost 
LIMIT 5;


-- 11 Z jaké nemocnice je nejkratši časová vzdálenost (v minutách) při půměrné rychlosti sanitky 50 km/h do obce Dlouhopolsko  ?
-----------------------------------------------------------------------
SELECT popis,ROUND(((ST_DISTANCE(obce.geom,izs_bod.geom))/(70/3.6))/60) AS čas 
FROM obce,izs_bod
WHERE obec ='Dlouhopolsko' AND druh='nemocnice'
ORDER BY čas 
LIMIT 1;


-- 12 Jakou výměru má největší zahrada Zlínského kraje? 
-- Plochu uvedte v hektarech s přesností na 3 desetinná místa.
----------------------------------------------------------------------------------
SELECT ROUND(MAX(ST_AREA(zahrady.geom)/1e4) :: NUMERIC ,3)  
FROM zahrady
JOIN kraje 
ON ST_Within(zahrady.geom,kraje.geom)  
WHERE kraj = 'Zlínský';

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