--===================
--NASTAVENI CESTY
--===================
--===================
--OBCE ZE SCHEMATU GIS1
--===================
CREATE  TABLE d11.obce
    AS SELECT gid, area, perimeter, nazev, obyv02, nuts3, nuts4, kodorp, nazorp, nk AS nazev_kraje, pomoc, geom
	 FROM gis1.obce;

--PREVOD DO SOURADNICOVEHO SYSTEMU DAT Z OSM
UPDATE d11.obce SET geom = ST_Transform(geom, 900913);

--===================
--LEZENI
--===================
CREATE TABLE d11.lezeni
    AS SELECT osm_id,way,name AS nazev, leisure AS TYP
         FROM czech_point
        WHERE sport='climbing';

--kontrola, zda nejaky polygon uz neni obsazen ve vrstve bodu lezeni
SELECT *
  FROM d11.lezeni AS cz_pt
  JOIN osm.czech_polygon AS cz_p
    ON ST_contains(cz_p.way,cz_pt.way)
 WHERE cz_p.sport='climbing';

--nahrani polygonu lezeni a prevod na centroid
CREATE  TABLE d11.lezeni_pol
    AS SELECT osm_id,way,name AS nazev, leisure AS TYP
         FROM czech_polygon
        WHERE sport='climbing';

UPDATE lezeni_pol SET way = ST_centroid(way);

--vlozeni dat z lezeni_pol do tabulky lezeni
INSERT INTO lezeni(osm_id,way,nazev,typ) SELECT * FROM lezeni_pol;

SELECT dropgeometrytable('d11','lezeni_pol');

--UPRAVA DAT (doplneni typu podle nazvu centra)
UPDATE lezeni SET typ='ledy' WHERE nazev LIKE '%Led%';
UPDATE lezeni SET typ='umela_stena' WHERE typ = 'sports_centre';
UPDATE lezeni SET typ='lanove_centrum' WHERE nazev like'Lan%';
UPDATE lezeni SET typ='umela_stena' WHERE nazev like '%ezeck%';
UPDATE lezeni SET typ='skala' WHERE nazev IN ('Skály u Pakosty','Županovice','domoradice rock #7');
UPDATE lezeni SET typ='umela_stena' WHERE nazev ='Mammut';

--===================
--UBYTOVANI
--===================
CREATE  TABLE d11.ubytovani
    AS SELECT osm_id, way, name AS nazev, tourism AS typ
         FROM czech_point
        WHERE tourism
           IN ('camp_site','caravan_site','chalet','hostel','hotel','motel');

--tvorba bodove vrstvy z polygonu
CREATE  TABLE d11.ubytovani_pol
    AS SELECT cp.osm_id, cp.way, cp.name AS nazev, cp.tourism AS typ
         FROM czech_polygon AS cp
        WHERE cp.tourism
           IN ('camp_site','caravan_site','chalet','hostel','hotel','motel')
	  AND cp.osm_id NOT IN (SELECT cz_p.osm_id --kontrola, zda nejaky polygon uz neni obsazen ve vrstve bodu ubytovani
	                          FROM d11.ubytovani AS ub
	                          JOIN osm.czech_polygon AS cz_p
	                            ON ST_contains(cz_p.way,ub.way)
	                         WHERE cz_p.tourism IN ('camp_site','caravan_site','chalet','hostel','hotel','motel'));

UPDATE ubytovani_pol SET way = ST_centroid(way);
--vlozeni do bodove vrstvy
INSERT INTO ubytovani(osm_id,way,nazev,typ) SELECT * FROM ubytovani_pol;

--uprava dat ubytovani
UPDATE ubytovani SET typ = 'kemp' WHERE typ = 'camp_site';
UPDATE ubytovani SET typ = 'autokemp' WHERE typ = 'caravan_site';
UPDATE ubytovani SET typ = 'horska_chata' WHERE typ = 'chalet';

SELECT dropgeometrytable('d11','ubytovani_pol');

--===================
--MICOVE HRY (opakovalo se osm_id, nahrazeno za gid)
--===================
CREATE  TABLE d11.micove_hry
    AS SELECT osm_id,way, name AS nazev, sport AS hra
         FROM czech_point
        WHERE sport IN ('tennis','soccer','volleyball');

--polygonova vrstva -> prevod na body
CREATE  TABLE d11.micove_hry_pol
    AS SELECT cp.osm_id, cp.way, cp.name AS nazev, cp.sport AS hra
         FROM czech_polygon AS cp
        WHERE cp.sport IN ('tennis','soccer','volleyball')
          AND cp.osm_id NOT IN (SELECT cz_p.osm_id   --kontrola
				  FROM d11.micove_hry AS mh
				  JOIN osm.czech_polygon AS cz_p
				    ON ST_contains(cz_p.way,mh.way)
				 WHERE cz_p.sport IN ('tennis','soccer','volleyball'));

UPDATE micove_hry_pol SET way = ST_centroid(way);

INSERT INTO micove_hry(osm_id,way,nazev,hra) SELECT * FROM micove_hry_pol;

--uprava dat micove_hry
UPDATE micove_hry SET hra = 'tenis' WHERE hra = 'tennis';
UPDATE micove_hry SET hra = 'fotbal' WHERE hra = 'soccer';
UPDATE micove_hry SET hra = 'volejbal' WHERE hra = 'volleyball';

SELECT dropgeometrytable('d11','micove_hry_pol');

ALTER TABLE micove_hry ADD COLUMN gid serial;
ALTER TABLE micove_hry ADD PRIMARY KEY(gid);
ALTER TABLE micove_hry DROP COLUMN osm_id;

--===================
--nahrazeni osm_id za gid (duvody: pokud se doplni novy zaznam, ziska nejvyssi cislo ->
-- ->snadne dohledani zmen; neni problem urcit id objektu, nazev sloupce vsude stejny)
--===================
ALTER TABLE d11.obce ADD PRIMARY KEY(gid);

ALTER TABLE lezeni ADD COLUMN gid serial;
ALTER TABLE lezeni ADD PRIMARY KEY(gid);
ALTER TABLE lezeni DROP COLUMN osm_id;

ALTER TABLE ubytovani ADD COLUMN gid serial;
ALTER TABLE ubytovani ADD PRIMARY KEY(gid);
ALTER TABLE ubytovani DROP COLUMN osm_id;


--===================
--ZIMNI SPORTY
--===================
CREATE  TABLE d11.zimni_sporty 
    AS SELECT osm_id,way,name AS nazev,sport
         FROM czech_point
        WHERE sport IN ('hockey' , 'skiing');

CREATE  TABLE d11.zimni_sporty_pol
    AS SELECT cp.osm_id, cp.way, cp.name AS nazev, cp.sport AS sport
         FROM czech_polygon AS cp
        WHERE cp.sport IN ('hockey','skiing')
          AND cp.osm_id NOT IN (SELECT cz_p.osm_id --kontrola
				  FROM d11.zimni_sporty AS zs
				  JOIN osm.czech_polygon AS cz_p
				    ON ST_contains(cz_p.way,zs.way)
				 WHERE cz_p.sport IN ('hockey','skiing'));

UPDATE zimni_sporty_pol SET way = ST_centroid(way);

INSERT INTO zimni_sporty(osm_id,way,nazev,sport) SELECT * FROM zimni_sporty_pol;
--uprava dat zimni_sporty
UPDATE zimni_sporty SET sport = 'hokej' WHERE sport = 'hockey';
UPDATE zimni_sporty SET sport = 'lyzovani' WHERE sport = 'skiing';

SELECT dropgeometrytable('d11','zimni_sporty_pol');

ALTER TABLE zimni_sporty ADD COLUMN gid serial;
ALTER TABLE zimni_sporty ADD PRIMARY KEY(gid);
ALTER TABLE zimni_sporty DROP COLUMN osm_id;

--===================
--PARKY, POLYGONOVA VRSTVA
--===================
CREATE  TABLE d11.parky
    AS SELECT osm_id,name AS nazev,way_area AS rozloha, way
         FROM czech_polygon
        WHERE leisure='park';

ALTER TABLE parky ADD COLUMN gid serial;
ALTER TABLE parky ADD PRIMARY KEY(gid);
ALTER TABLE parky DROP COLUMN osm_id;

--===================
--TVORBA PROSTOROVEHO INDEXU
--===================
CREATE INDEX obce_geom ON obce USING gist (geom);
CREATE INDEX lezeni_geom ON lezeni USING gist (way);
CREATE INDEX ubytovani_geom ON ubytovani USING gist (way);
CREATE INDEX micove_hry_geom ON micove_hry USING gist (way);
CREATE INDEX zimni_sporty_geom ON zimni_sporty USING gist (way);
CREATE INDEX parky_geom ON parky USING gist (way);
--===================
--AKTUALIZACE METADATOVE TABULKY
--===================
SELECT populate_geometry_columns('d11.obce'::regclass);
SELECT populate_geometry_columns('d11.lezeni'::regclass);
SELECT populate_geometry_columns('d11.ubytovani'::regclass);
SELECT populate_geometry_columns('d11.micove_hry'::regclass);
SELECT populate_geometry_columns('d11.zimni_sporty'::regclass);
SELECT populate_geometry_columns('d11.parky'::regclass);


--===================
--DOPLNENI DAT DO TABULKY LEZENI
--===================
--uprava dat podle gid
UPDATE lezeni SET nazev = 'TJ Lokomotiva Plzeň', typ = 'umela_stena' WHERE gid = 1;
UPDATE lezeni SET nazev = 'Skály nad Jezeřím', typ = 'skala' WHERE gid = 2;
UPDATE lezeni SET nazev = 'Stěna Ruzyně', typ = 'umela_stena' WHERE gid = 3;
UPDATE lezeni SET nazev = 'HUDY SmichOFF', typ = 'umela_stena' WHERE gid = 4;
UPDATE lezeni SET nazev = 'Sportovní areál H-Centrum' WHERE gid = 7;
UPDATE lezeni SET nazev = 'Městská hala - Ostrava' WHERE gid = 10;