/*CREATE TABLE osm(
osm VARCHAR(50),
artifical INTEGER,
cropland INTEGER,
woodland INTEGER,
shrubland INTEGER,
grassland INTEGER,
bare INTEGER,
water INTEGER,
wetland INTEGER
);*/

\copy osm(osm, artifical, cropland, woodland, shrubland, grassland, bare, water, wetland) FROM 'C:/Users/Adriana/Desktop/osm.csv' DELIMITER ';' CSV HEADER encoding 'windows-1250';

ALTER TABLE osm
RENAME COLUMN field_9 TO wetland

ALTER TABLE osm
DROP COLUMN field_10 

--Výběr požadované dlaždice 
CREATE TABLE crml AS
SELECT geom FROM osm.sentinel2_tiles WHERE name = '33UVR'

--Transformace souřadnicového systému dlaždice do systému 3857, ve kterém jsou osm data
SELECT crml_trans AS(
CREATE TABLE ST_Transform(geom, 3857) as geom FROM crml
)

--Změna názvu geometrie dlaždice pro následné použití prostorové funkce (aby nebyly v jedné tabulce dva sloupečky geom)
ALTER TABLE crml_trans
RENAME COLUMN geom TO geom1

--Výběr osm prvků nacházejících se v zájmové oblasti
CREATE TABLE cz_line AS(
SELECT *
FROM osm.czech_line AS c
JOIN crml_trans AS p
ON st_within(c.geom, p.geom1)
)

CREATE TABLE cz_polygon as(
SELECT *
FROM osm.czech_polygon AS c
JOIN crml_trans AS p
ON st_within(c.geom, p.geom1)
)

CREATE TABLE cz_road as(
SELECT *
FROM osm.czech_roads AS c
JOIN crml_trans AS p
ON st_within(c.geom, p.geom1)
)


------spojenie liniovych prvkov do jednej tabulky

CREATE TABLE road_vybrane AS (
SELECT * FROM  cz_road WHERE highway IN 
((SELECT highway FROM highway)));
INSERT INTO road_vybrane 
SELECT * FROM  cz_road WHERE waterway IN 
((SELECT waterway FROM waterway));
INSERT INTO road_vybrane 
SELECT * FROM  cz_road WHERE aeroway IN 
((SELECT aeroway FROM aeroway));
INSERT INTO road_vybrane 
SELECT * FROM  cz_road WHERE railway IN 
((SELECT railway FROM railway));

---------------------------------------------------
CREATE TABLE line_vybrane AS (
SELECT * FROM  cz_line WHERE highway IN 
((SELECT highway FROM highway)));
INSERT INTO line_vybrane 
SELECT * FROM  cz_line WHERE waterway IN 
((SELECT waterway FROM waterway));
INSERT INTO line_vybrane 
SELECT * FROM  cz_line WHERE aeroway IN 
((SELECT aeroway FROM aeroway));
INSERT INTO line_vybrane
SELECT * FROM  cz_line WHERE railway IN 
((SELECT railway FROM railway));

--Výběr osm prvků, podle hodnoty tagu 
CREATE TABLE polygon_vybrane AS (
SELECT * FROM  cz_polygon WHERE landuse IN 
((SELECT landuse FROM landuse)));
INSERT INTO polygon_vybrane 
SELECT * FROM  cz_polygon WHERE aeroway IN
((SELECT aeroway FROM aeroway));
INSERT INTO polygon_vybrane 
SELECT * FROM  cz_polygon WHERE railway IN
((SELECT railway FROM railway));
INSERT INTO polygon_vybrane 
SELECT * FROM  cz_polygon WHERE building IN
((SELECT building FROM building));
INSERT INTO polygon_vybrane 
SELECT * FROM  cz_polygon WHERE waterway IN 
((SELECT waterway FROM waterway));
INSERT INTO polygon_vybrane 
SELECT * FROM  cz_polygon WHERE surface IN 
((SELECT surface FROM surface));
INSERT INTO polygon_vybrane 
SELECT * FROM  cz_polygon WHERE highway IN 
((SELECT highway FROM highway));
INSERT INTO polygon_vybrane 
SELECT * FROM  cz_polygon WHERE "natural" IN
((SELECT "natural"  FROM "natural" ));

----vytvoření tabulky pro polygony a buffery
CREATE TABLE pol_buf AS 
SELECT * FROM line_vybrane

----vytvoření bufferu širokého 3 m okolo liniových prvků tím, že se nehradil jeden sloupec s geometrií transformované dlaždice (samotný buffer nezachová původní atributy)
UPDATE pol_buf
SET geom1= st_buffer (geom, 3,42)
----odstranění původních linií
ALTER TABLE pol_buf DROP COLUMN geom

ALTER TABLE pol_buf
RENAME geom1 TO geom

ALTER TABLE polygon_vybrane DROP COLUMN geom1

--přidání do tabulky s buffery i tabulka s polygony 
INSERT INTO pol_buf
SELECT * FROM polygon_vybrane

--Propojení tabulek obsahující hodnoty 1,0 podle toho do jaké třídy lucas spadá daná hodnota tagu v osm s tabulkou obsahující vybrané osm prvky  
CREATE TABLE lucas AS
INSERT INTO lucas 
(SELECT * FROM pol_buf
INNER JOIN aeroway ON pol_buf.aeroway = aeroway.aeroway);

INSERT INTO lucas
(SELECT * FROM pol_buf 
INNER JOIN building ON pol_buf.building = building.building);

INSERT INTO lucas
(
SELECT * FROM pol_buf 
INNER JOIN highway ON pol_buf.highway = highway.highway
);

INSERT INTO lucas
(
SELECT * FROM pol_buf 
INNER JOIN waterway ON pol_buf.waterway= waterway.waterway
);

INSERT INTO lucas
(
SELECT * FROM pol_buf 
INNER JOIN surface ON pol_buf.surface = surface.surface
);

INSERT INTO lucas
(
SELECT * FROM pol_buf 
INNER JOIN railway ON pol_buf.railway = railway.railway
);

INSERT INTO lucas
(
SELECT * FROM pol_buf 
INNER JOIN landuse ON pol_buf.landuse = landuse.landuse
);

INSERT INTO lucas
(
SELECT * FROM pol_buf 
INNER JOIN "natural" ON pol_buf."natural" = "natural"."natural"
);

--Vložení názvu třídy lucas do sloupce lucas pro každý prvek 
UPDATE lucas SET lucas = 'bare' WHERE "Bare" ='1'
UPDATE lucas SET lucas = 'artificial' WHERE "Artificial" ='1'
UPDATE lucas SET lucas = 'cropland' WHERE "Cropland" ='1'
UPDATE lucas SET lucas = 'woodland' WHERE "Woodland" ='1'
UPDATE lucas SET lucas = 'shrubland' WHERE "Shrubland" ='1'
UPDATE lucas SET lucas = 'grassland' WHERE "Grassland" ='1'
UPDATE lucas SET lucas = 'water' WHERE "Water" ='1'
UPDATE lucas SET lucas = 'wetland' WHERE "Wetland" ='1'






