-- SET search_path TO uzpd18_g, dibavod, ruian, public;


CREATE TABLE uzpd18_g.staty AS 
SELECT kod AS gid, nazev, geom
FROM ruian.staty;

ALTER TABLE uzpd18_g.staty ADD PRIMARY KEY (gid);

CREATE INDEX staty_geom_id ON uzpd18_g.staty USING gist(geom);

SELECT gid, ST_IsValidReason(geom) FROM uzpd18_g.staty WHERE ST_IsValid(geom) = FALSE;

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

CREATE TABLE uzpd18_g.vusc AS 
SELECT ogc_fid AS gid, nazev, geom
FROM ruian.vusc;

ALTER TABLE uzpd18_g.vusc ADD PRIMARY KEY (gid);

ALTER TABLE uzpd18_g.vusc ADD COLUMN stat_id INT;

UPDATE uzpd18_g.vusc 
SET stat_id = 1;

CREATE INDEX vusc_geom_id ON uzpd18_g.vusc USING gist(geom);

SELECT gid, ST_IsValidReason(geom) FROM uzpd18_g.vusc WHERE ST_IsValid(geom) = FALSE;

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

CREATE TABLE uzpd18_g.okresy AS 
SELECT ogc_fid AS gid, nazev, geom
FROM ruian.okresy;

ALTER TABLE uzpd18_g.okresy ADD PRIMARY KEY (gid);

ALTER TABLE uzpd18_g.okresy ADD COLUMN vusc_id INT;

CREATE INDEX okresy_geom_id ON uzpd18_g.okresy USING gist(geom);

SELECT gid, ST_IsValidReason(geom) FROM uzpd18_g.okresy WHERE ST_IsValid(geom) = FALSE;


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

CREATE TABLE uzpd18_g.obce AS 
SELECT ogc_fid AS gid, nazev, geom
FROM ruian.obce;

ALTER TABLE uzpd18_g.obce ADD PRIMARY KEY (gid);

ALTER TABLE uzpd18_g.obce ADD COLUMN okres_id INT;

CREATE INDEX obce_geom_id ON uzpd18_g.obce USING gist(geom);

SELECT gid, ST_IsValidReason(geom) FROM uzpd18_g.obce WHERE ST_IsValid(geom) = FALSE;

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

CREATE TABLE uzpd18_g.toky AS 
SELECT gid AS gid, naz_tok AS nazev, geom
FROM  dibavod.vodni_toky;

ALTER TABLE uzpd18_g.toky ADD PRIMARY KEY (gid);

CREATE INDEX toky_geom_id ON uzpd18_g.toky USING gist(geom);

SELECT gid, ST_IsValidReason(geom) FROM uzpd18_g.toky WHERE ST_IsValid(geom) = FALSE;

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

shp2pgsql -s 4326 Velkoplosna_zvlaste_chranena_uzemi/Velkoplosna_zvlaste_chranena_uzemi.shp uzpd18_g.velkoplosna_uzemi | psql -h geo102.fsv.cvut.cz -d pgis_uzpd -U uzpd18_g

ALTER TABLE velkoplosna_uzemi
ALTER COLUMN geom TYPE geometry(MultiPolygon,5514) 
USING ST_Transform(geom,5514);

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

shp2pgsql -s 4326 Maloplosna_zvlaste_chranena_uzemi/Maloplosna_zvlaste_chranena_uzemi.shp uzpd18_g.maloplosna_uzemi | psql -h geo102.fsv.cvut.cz -d pgis_uzpd -U uzpd18_g

ALTER TABLE maloplosna_uzemi
ALTER COLUMN geom TYPE geometry(MultiPolygon,5514) 
USING ST_Transform(geom,5514);
------------------------------------------------------------------------------------

shp2pgsql -s 4326 Evropsky_vyznamne_lokality/Evropsky_vyznamne_lokality.shp uzpd18_g.natura2000 | psql -h geo102.fsv.cvut.cz -d pgis_uzpd -U uzpd18_g

ALTER TABLE natura2000
ALTER COLUMN geom TYPE geometry(MultiPolygon,5514) 
USING ST_Transform(geom,5514);

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

shp2pgsql -s 4326 Ptaci_oblasti/Ptaci_oblasti.shp uzpd18_g.ptaci_oblasti | psql -h geo102.fsv.cvut.cz -d pgis_uzpd -U uzpd18_g

ALTER TABLE ptaci_oblasti
ALTER COLUMN geom TYPE geometry(MultiPolygon,5514) 
USING ST_Transform(geom,5514);


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

shp2pgsql -s 4326 Pamatne_stromy/Pamatne_stromy.shp uzpd18_g.pamatne_stromy | psql -h geo102.fsv.cvut.cz -d pgis_uzpd -U uzpd18_g

ALTER TABLE pamatne_stromy
ALTER COLUMN geom TYPE geometry(MultiPolygon,5514) 
USING ST_Transform(geom,5514);


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

ALTER TABLE velkoplosna_uzemi DROP COLUMN objectid, DROP COLUMN kod, DROP COLUMN rozl, DROP COLUMN zmena_g, DROP COLUMN zmena_t, DROP COLUMN prekryv
ALTER TABLE maloplosna_uzemi DROP COLUMN objectid, DROP COLUMN kod, DROP COLUMN rozl, DROP COLUMN op_typ, DROP COLUMN iucn, DROP COLUMN zmena_g, DROP COLUMN zmena_t, DROP COLUMN prekryv;
ALTER TABLE natura2000 DROP COLUMN objectid, DROP COLUMN sitecode , DROP COLUMN kod ,  DROP COLUMN zmena_g, DROP COLUMN zmena_t;
ALTER TABLE pamatne_stromy DROP COLUMN objectid, DROP COLUMN p_stro_ , DROP COLUMN p_stro_id , DROP COLUMN kod , DROP COLUMN typ , DROP COLUMN pocet , DROP COLUMN op_typ , DROP COLUMN op_vel , DROP COLUMN urceno , DROP COLUMN puv_xy , DROP COLUMN podklad , DROP COLUMN dat_pkd, DROP COLUMN zpracoval, DROP COLUMN zmena_g, DROP COLUMN zmena_t, DROP COLUMN chyba_s;
ALTER TABLE ptaci_oblasti DROP COLUMN objectid, DROP COLUMN sitecode, DROP COLUMN kod,  DROP COLUMN zmena_g, DROP COLUMN zmena_t;

CREATE INDEX maloplosna_uzemi_geom_id ON maloplosna_uzemi USING gist(geom);
CREATE INDEX velkoplosna_uzemi_geom_id ON velkoplosna_uzemi USING gist(geom);
CREATE INDEX natura2000_geom_id ON natura2000 USING gist(geom);
CREATE INDEX pamatne_stromy_geom_id ON pamatne_stromy USING gist(geom);
CREATE INDEX ptaci_oblasti_geom_id ON ptaci_oblasti USING gist(geom);

SELECT gid, ST_IsValidReason(geom) FROM uzpd18_g.maloplosna_uzemi WHERE ST_IsValid(geom) = FALSE;
SELECT gid, ST_IsValidReason(geom) FROM uzpd18_g.velkoplosna_uzemi WHERE ST_IsValid(geom) = FALSE;
SELECT gid, ST_IsValidReason(geom) FROM uzpd18_g.natura2000 WHERE ST_IsValid(geom) = FALSE;
SELECT gid, ST_IsValidReason(geom) FROM uzpd18_g.pamatne_stromy WHERE ST_IsValid(geom) = FALSE;
SELECT gid, ST_IsValidReason(geom) FROM uzpd18_g.ptaci_oblasti WHERE ST_IsValid(geom) = FALSE;
---------------------------------------------------------------------------------------
--PROSTOROVE DOTAZY
-- Jaká je v ČR největší ptačí oblast?

select po.nazev, st_area(geom)/1000000 rozloha
from ptaci_oblasti po
order by st_area(geom) desc limit 1;

-- Šumava - 975 m2

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

SELECT DISTINCT a.nazev AS  PamatnyStrom FROM pamatne_stromy AS a JOIN (SELECT * FROM okresy WHERE nazev = 'Prachatice') AS b
ON st_intersects(a.geom, b.geom) ORDER BY a.nazev ASC;

-- Alej Kratochvíle, Alej u Buku …

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

WITH cte AS (
SELECT a.nazev,count(*) AS count FROM vusc AS a JOIN pamatne_stromy AS b ON st_intersects(a.geom, b.geom)
GROUP BY a.nazev ORDER BY count DESC
) SELECT nazev,count FROM cte WHERE count = (SELECT max(count) FROM cte);

-- Středočeský kraj – 2181

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

--ATRIBUTOVE DOTAZY

-- Kolik je v ČR přírodních památek
SELECT count(*) FROM maloplosna_uzemi WHERE  kat = 'PP';

-- 1560
