-- CVUT PRAHA
-- UVOD DO ZPRACOVANI PROSTOROVYCH DAT (UZPD)
-- 6. semestr (2012/2013)
-- SKUPINA c13 - Jana Antlova, Michaela Sipkova

-- nastaveni cesty 

-- I. TVORBA VRSTEV

-- vrstva OBCE

CREATE TABLE obce AS SELECT * FROM gis1.obce;

SELECT AddGeometryColumn('obce', 'geom1', 900913, 'multipolygon', 2);
UPDATE obce SET geom1 = ST_Transform(geom, 900913); 
SELECT DropGeometryColumn('obce', 'geom');
ALTER TABLE obce RENAME COLUMN geom1 TO geom;

-- primarni klic
ALTER TABLE obce ADD PRIMARY KEY (ogc_fid);
-- prostorovy index
CREATE INDEX obce_geom
ON c13.obce USING gist(geom);

-- prejmenovani sloupcu
ALTER TABLE obce RENAME COLUMN area TO vymera;
ALTER TABLE obce RENAME COLUMN nuts3 TO kraj;

-- prejmenovani kraju
UPDATE obce SET kraj='hl.m.Praha' WHERE kraj='CZ011';
UPDATE obce SET kraj='Ustecky' WHERE kraj='CZ042';
UPDATE obce SET kraj='Moravskoslezsky' WHERE kraj='CZ081';
UPDATE obce SET kraj='Pardubicky' WHERE kraj='CZ053';
UPDATE obce SET kraj='Plzensky' WHERE kraj='CZ032';
UPDATE obce SET kraj='Karlovarsky' WHERE kraj='CZ041';
UPDATE obce SET kraj='Vysocina' WHERE kraj='CZ061';
UPDATE obce SET kraj='Olomoucky' WHERE kraj='CZ071';
UPDATE obce SET kraj='Liberecky' WHERE kraj='CZ051';
UPDATE obce SET kraj='Jihomoravsky' WHERE kraj='CZ062';
UPDATE obce SET kraj='Stredocesky' WHERE kraj='CZ021';
UPDATE obce SET kraj='Jihocesky' WHERE kraj='CZ031';
UPDATE obce SET kraj='Zlinsky' WHERE kraj='CZ072';
UPDATE obce SET kraj='Kralovehradecky' WHERE kraj='CZ052';


-- vrstva KRAJE

CREATE TABLE kraje AS 
SELECT ST_Union(geom) AS geom, kraj 
FROM c13.obce 
GROUP BY kraj;

-- pridani primarniho klice
ALTER TABLE kraje 
ADD COLUMN gid serial;
ALTER TABLE kraje
ADD PRIMARY KEY (gid);

-- pridani prostoroveho indexu
CREATE INDEX kraje_geom ON kraje
USING GIST (geom);

-- vrstva KINO

CREATE TABLE kino AS
SELECT osm_id, geom, amenity, name AS nazev
FROM czech_point WHERE amenity='cinema';

DELETE FROM kino WHERE nazev IS NULL;

CREATE TABLE kino_pol AS
SELECT osm_id, geom, amenity, name AS nazev
FROM czech_polygon WHERE amenity='cinema';

DELETE FROM kino_pol WHERE nazev IS NULL;

-- odstaneni duplicit
DELETE FROM kino WHERE
kino.geom IN 
(SELECT kino.geom FROM kino
JOIN kino_pol 
ON ST_Within(kino.geom, kino_pol.geom));

CREATE TABLE centr AS 
SELECT osm_id, ST_Centroid(kino_pol.geom) AS geom,
amenity, nazev FROM kino_pol;
INSERT INTO kino(osm_id, geom, amenity, nazev)
SELECT osm_id, geom, amenity, nazev FROM centr;

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

ALTER TABLE kino ADD PRIMARY KEY (osm_id);
CREATE INDEX kino_geom ON kino
USING GIST (geom);

DROP TABLE kino_pol;
DROP TABLE centr;

-- vrstva BANKOMATY

CREATE TABLE bankomaty AS
SELECT osm_id, operator, geom
FROM czech_point
WHERE amenity='atm';

-- pridani primarniho klice
ALTER TABLE bankomaty ADD PRIMARY KEY (osm_id);

-- pridani prostoroveho indexu
CREATE INDEX bankomaty_geom ON bankomaty
USING GIST(geom);

-- odstraneni zahranicnich atm
DELETE FROM bankomaty
WHERE bankomaty.geom NOT IN
(SELECT bankomaty.geom FROM bankomaty 
JOIN kraje ON ST_Within(bankomaty.geom, kraje.geom));

-- vrstva SILNICE

CREATE TABLE silnice AS
SELECT osm_id, name AS nazev, highway AS popis, geom FROM osm.czech_line 
 WHERE highway IN ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link', 'tertialy', 'tertialy_link');

--primarni klic
ALTER TABLE silnice
ADD COLUMN gid serial;
ALTER TABLE silnice
ADD PRIMARY KEY (gid);

-- prostorovy index
CREATE INDEX silnice_geom ON silnice
USING GIST (geom);	

-- vrstva HRANICE - pomocna polygonova vrstva pro orezani silnic
CREATE TABLE hranice AS 
SELECT ST_Union(geom) AS geom 
FROM c13.obce;

-- orezani silnic, ktere presahovaly
DELETE FROM silnice
WHERE silnice.geom NOT IN
(SELECT silnice.geom FROM silnice 
JOIN hranice ON ST_Within(silnice.geom,hranice.geom));

-- vytvoreni vrstvy DALNICE
CREATE TABLE dalnice AS
SELECT osm_id, nazev, popis, geom FROM silnice
WHERE popis = 'motorway';

-- pridani nazvu dalnic 
--* tyto prikazy byly pouzity i pro vrstvu silnice
UPDATE dalnice SET nazev= 'D5'
where osm_id IN
(
'217891524','217891521','217891522','217891523','24525621','27568574','5609790','199647461','199639929','26531580','199639924','199639925','199639930','199647464','100515385','199639926','199639927','9792394','199639928','9792390','9792386','9792384','9792388','9792382','9792380','5050072','9792381','32692028','4051104','32692027','32692029','81349895','32692030','81349892','81349890','5050030','81349893','5051489','4051153','4051256','4051569','5051490','4630736','22818632','22818631','22818629','22818630','22818469','22818472','22818470','5051491','22818471','75899547','4672243','75899546','75899549','75899545','75899550','75899544','75899542','75899543','75413971','75413970','75413976','5051492','75413975','159220447','48535035','33511542','33511540','33511543','33511547','50438177','33511546','50438181','50438180','100515384','50438178','27568025','27515342','100515388','100515397','131670258','131670257','40688934','131670256','131670255','40688935','40688933','50537918','40688928','50537917','50537923','27568256','50537924','27515343','27515437','40701785','27515335','40701784','40701786','85391591','40701787','85391590','85391589','159269738','85391593','23292119','159220226','23292118','23292115','34253182','23292117','34253179','34253181','22748795','34253180','22748794','22748793','22748799','22748792','22748796','22748798','28912062','22748797','28912061','28912059','121988933','28912060','121989002','121988938','93287872','121988983','93287871','93287880','93287881','93287861','93287879','93287868','165809691','93287877','165809698','165809707','93287870','165809696','93287863','93287866','93287875','93287867','93287869','93287878','23322730','93287862','23322732','23322729','23322731','165809718','165809715','165809700','23296232','165809695','23296228','23296231','23296229','45739752','45739753','45739755','45739754','4880523','23296208','4880476','23296207','23296209','23296085','23296210','23296160','23296084','23296086','23296083','165809692','165809714','165809703','165809720','165809719','165809716','165809699','4772289','165809694','165809721','165809697','165809701','43530031','165809690','4357105','125544373','43530028','125544378','125544376','43530029','125544368','30876550','43530026','30876553','30876551','30876554','30876552','30876556','30876555','13142028','30876557','13141962','13141958','4357106','13141959','129749614','4076739','129835839','129749615','129835840','105480329','105480332','105480340','105480328','105480327','105480335','105480331','81605044','105480338','81605033','81605043','13117927','81605037','123265398','13117926','13117923','30957479','13117924','30957478','30957476','165809693','30957477','159536777','165809711','165809705','32823915','165809717','32823914','32823916','8682537','32823918','8682539','30952738','26167614','32863449'
); 

UPDATE dalnice SET nazev= 'D3'
where osm_id IN
(
'-1753349','51526882','39379554','102413204','102413205','102413203','83891729','102413206','39379553','39379552','102413207','51526934','51526933','51526926','51448854','115670155','51526925','51448853','51448749','83891731','83891730','83891732','171049579','39379549','171049583','39379548','39379547','39379546','58920105','171049580','58920111','58920116','58920122','58933305','171047170','32598181','119909466','171047176'
);

UPDATE dalnice SET nazev= 'D11'
where osm_id IN
(
'-1753316','30821125','27637381','31929452','31929449','31929451','184132681','31929450','184132680','184132679','4889257','184132682','184146325','184146305','184146306','184146324','22774288','22774287','22774285','26628134','22774286','26628135','26628133','26628136','184146323','184146315','184146304','184146320','184146319','184146308','184146314','184146318','184146316','184146313','184146303','184146330','184146329','184146311','184146307','184146299','184146298','184146302','184146300','184146321','171912149','171912136','171912142','171912147','171912143','171912132','171912141','22745516','171912128','22745517','22745515','4212595','22745518','22745514','22745513','171912144','22768456','171912138','171912129','171912148','171912145','171912140','171912130','171912127','184146327','184146309','184146301','184146326','113686802','113686807','113686806','113687455','113686796','113687456','113687457','184146328','113687454','184146312','184146310','184146297','172364698','172364695','172364696','172364697','50417475','50417476','50417478','30074100','50417479','30074098','30074101','184150455','30074099','184150456','184150457','184150461','184150462','184150458','184150459','184150460','30036809','30036811','30036810','70742032','26727329','70741835','70741981','70742090','70742056','70741983','70741866','70741814','184154029','184154009','184153996','184154025','184154018','184154020','184154015','26727327','184153997','30036893','30036896','28403115','30036894','28403114','28403116','28403121','28403122','28403113','28403124','28403123','28403109','28403125','28403112','28403110','28403111','184154031','184154012','184154002','184153993','184153991','184154000','184154005','118626273','184153986','118626295','118626204','118626253','184157260','184157267','184157268','184157266','184157265','184157271','184157269','184157274','184157275','184157270','184157272','184157261','49210108','49210110','49210109','49210111','28067059','28067064','28067060','49113449','28067062','49113450','49113445','30817378','49113443','80298150','28637735','80298154','100515395','28637733','30821126','30821129','30821130','30821132','45874908','30821131','184157273','45874905','45874906','45874907'
);

UPDATE dalnice SET nazev= 'D8'
where osm_id IN 
(
'217338956','30148678','30148681','32478550','32478546','26095383','25627287','30149098','34242310','34242311','34242309','34242308','132020749','132020754','11936764','132020705','132020695','132020747','132020751','132020732','30148682','185578656','185578660','31717581','9399450','185578659','185578663','31717583','31717580','186677750','31717582','186677739','186677753','23719731','132020718','28144251','132020711','140855006','180457920','186677705','186677708','186677692','31716911','140855001','140854997','27752785','140855008','27752784','27752782','186677714','186677725','27752789','27752783','186677677','186677680','31716910','31716909','31716908','30998203','30998202','34785953','186677701','186677720','132020743','186677732','27752787','27752788','34785954','34785952','186677695','30148679','38187131','27752786','34785951','30998205','31714925','38187130','31714923','38187132','38187133','31714922','30998204','31714924','186677764','186677761','186677773','32478549','32478734','142263527','126827669','126827671','126827670','126827672','132008947','186677683','32478731','32478543','132008935','132008944','132008987','89654825','89654827','89654826','89654824','118610733','118610729','118610732','118610727','137638734','126828369','126828367','126828366','126828368','15999997','40691982','16000000','15999999','16000001','16000002','16000003','16000005','16000004','62125723','62125710','62125696','62125729','132008966','132008927','132008940','132008970','40353598','15998476','15998478','40353596','132008963','132008954','132008926','132008979','15996963','15996956','15996961','15996958','28056799','28056795','28056797','28056796','132008984','132008938','132008952','132008959','15993569','15993570','15906490','15993572','15903770','42085050','42085049','42085047','42085048','42085051','42085052','42085054','42085053','15898606','15898604','15898608','15898609','25810012','25810011','25810009','15897238','25810010','15897235','15897236','15897237','13446479','13354539','130010752','132008918','132008974'
);

UPDATE dalnice SET nazev= 'D2'
where osm_id IN 
(
'4228028','5375467','49679963','4083860','40214503','40214502','22732132','49679964','44673187','22732129','22732131','22732130','44674712','49686012','4083862','5375466','38687507','4083863','38687509','112321104','112321087','112321122','112321074','159638851','159638849','159638850','159638848','26253314','26253317','26253315','4882104','22946222','22946224','22946223','22946225','51182590','51182592','51182591','23587136','4083864','25410080','26253108','23587133','25410079','4551460','4210840','23587138','23587139','44675120','44675122','44675121','51181595','51181596','51181593','51181594','44675119','51181598','108675289','51181597','51181592','108675231','108675353','108675246','108675282','108675346','108675263','108675350','108675325','44674710','108675323','31712210','31712208','4210837','31712253','31712209','108675294','4551461','108675394'
);

UPDATE dalnice SET nazev= 'D1'
where osm_id IN
(
'217429471','217429469','217429468','217429470','210376150','210376149','210376148','210376151','217181286','217181288','219020762','27462089','12191431','12191467','100515391','32489712','51551834','100515393','51551835','51551836','51551832','171993315','171993314','67790516','171993316','51518003','45054930','119766572','51518005','51518006','51518002','32489715','119766569','32065252','120610514','51519378','51519381','51519382','51519375','120610513','64250370','64250368','64250342','64250377','64250350','64250345','64250374','64250347','26474365','26474366','26474364','4608306','120827884','12837922','12840263','12840264','12840262','12839852','31401907','31401917','31401909','31401912','39815325','39815323','39815324','39815322','39815332','39815330','39815331','39815329','119763488','26784178','119763495','26784183','26784177','26784182','119763490','193766357','119763491','193766360','193766361','193766364','193766363','193766359','193766362','193766358','12838432','119763493','5931788','26786281','26786278','26786280','26786279','93542370','93542367','93542366','93542373','26786264','26786265','26786266','26786263','22868197','22868199','22868200','22868196','119759949','119759944','119759946','119759948','4662504','6109867','6109868','4608309','119745466','119746296','119746297','119745461','119745460','119745467','119745462','119745459','119745463','119745465','51685602','119745464','119745443','4608311','51685602','119745464','119745443','51685606','51685604','51685605','53155776','53155778','53155777','53092527','53155779','53092524','53092521','53092517','121296543','121296544','115609352','115609324','115609344','115609329','115609342','121296542','115609347','115609325','115609355','121296545','121296541','5131490','4608310','45556210','45556207','45556209','45556208','45556166','45556168','45556164','45556169','121183365','26328967','26328888','26328955','26328915','4610857','26328875','26329128','87672556','26329104','26329085','37095713','26329140','10869982','37095638','10869983','43790208','43790205','43790207','43790206','4078522','4078523','22747198','22747197','22747195','22747196','53659351','53659367','53659366','22747164','53659285','22747165','22747167','22747166','42225569','42225566','42225568','42225567','53761416','53761535','53761536','53761534','53761533','53761542','53761540','53761543','4610858','22747169','4610859','22747168','22747170','22747171','75070490','75070506','75070482','75070473','25096903','25096900','25096902','25096901','22747144','22747141','22747143','22747136','22747142','22747139','22747140','22747138','22747093','22747096','22747094','22747095','4078524','48204431','12876555','48204429','48204430','48204412','22747986','22747985','22747983','22747984','12876543','12876552','38891871','38891873','38891872','38891874','12876520','12876541','22747982','22747979','22747981','22747980','38892037','38892034','38892036','38892035','4610860','4078526','201149709','201149708','201149707','201149706','51179612','51179620','51179621','51179614','51179616','51179619','51179617','51179613','34780545','34780548','34780544','34780549','4610861','4610862','22747631','22747630','22747632','129704217','22747633','129704216','129704214','22731681','22731678','22731680','22731679','4078527','4742968','39812445','39812444','22731517','22731518','34245623','34245621','34245624','34245622','22731516','22731519','4078528','5775473','5587919','4381525','5775472','4381482','5465378','5465491','39812447','39812446','40236397','40236398','22732379','5465492','22732376','22732378','22732377','5465490','4433408','32348072','4742975','32348071','32348073','40236400','32348074','40236399','4078529','4078546','117222366','117222369','117222367','4742979','117222368','22733706','22733709','22733714','7962135','7962135','4083866','32355903','32355906','32355904','27470737','32355905','4742980','27470738','12842104','4742981','48126585','28131597','12842105','28131596','28131593','12842103','28131595','4078545','15091059','27296187','15091060','27296188','27296190','27297735','27296189','142516338','142516336','27297736','27297733','27297734','22733936','22733937','22733935','22733938','61322014','23306712','23306715','23306713','26168617','23306714','23306729','26168607','23306730','23306732','23306717','23306731','23306718','23306716','149171093','23306719','8134156','27298695','4078530','27298696','27298698','91576613','27298697','91576594','91576611','91576598','27327219','142516339','142516337','27327218','27327220','27405163','27327221','27405160','27405162','44860462','27405161','44860456','44860461','24859840','44860457','22875243','24859839','142516335','22875244','22875242','8134155','22875245','4052013','102616787','102616747','102616807','69714574','102616813','69714594','69714601','69714585','69714596','69714597','69714599','69610678','69714586','69610695','69610708','4538687','69610692','24859654','203022942','203022934','203022929','203022938','203022939','203022923','203022926','203022944','203022945','203022928','203022925','203022935','203022937','203022927','203022924','203022941','203022943','203022932','203022936','116586247','203022940','116586246','116586245','69610686','116586248','69610710','69610714','69610703','27614010','27614009','69602361','69602371','69602385','69602350','69602356','69602369','69602377','69602339','27614014','27614013','27614016','39682189','27614015','39682190','39682193','172921525','69602338','172921522','172921523','27614003','172921524','24859655','154985933','154985837','87492475','154985835','154985833','154985834','154985838','154985839','154985832','154985836','171876008','154985831','171875998','171876002','171876006','171876005','171876003','171876001','85265723','171876007','87524621','186746677','186746675','186746671','186746674','171875997','85265719','85265701','87492476','87492477','171876000','171876004','171875999','186746670','186746676','186746673','186746672','78337782','28928757','42683949','28928756','28928754','100443783','28928755','100443753','100443771','100443762','100443748','100443767','100443785','140019121','100443756','140019120','100443775','100443777','100443782','100443764','100443774','100443754','100443778','100504569','38634690','38634689','38634687','100548416','38634688','100548405','100548430','100548407','100548394','100548424','100548399','100548432','100548418','100548417','100548409','100548389','100548426','100548393','100548390','28928902','100548425','100548428','28786840','45025953','45039868','77928392','100548427','100548406','46777814','45039867','45039869','170732805','45039870','170732806','170732803','123484703','170732804','123484696','123484700','45039880','123484701','45039877','45039879','45039855','45039878','170731877','45039856','170731876','45039858','45039884','45039857','45039860','45039883','45039881','170731239','45039882','170731242','170731245','170731247','170731235','170731234','170731236','170731233','170731244','170731232','170731237','170731240','170731241','170731246','170731238','45039122','170731231','45039121','45039119','170679178','45039120','170679173','170679172','45039113','170679174','77928395','45039114','45039111','170679175','45039112','45039117','45039115','45045652','170679180','45045653','45045651','170711926','45045654','170711922','170711930','45045660','170711928','45045659','45045657','170711929','45045658','170711921','170711920','45045673','170711927','45045672','45045674','109860598','45045671','109860601','109860605','45045676','109860604','45045678','45045675','170711077','45045677','170711075','170711073','170711074','45046031','45046030','45046028','45046033','45046029','45046035','45046032','32761702','45046034','170709523','170709530','46953920','77011100','170709527','170709531','46953921','46953922','46953923','28929258','28929263','28929257','170709529','28929260','170709524','170709526','32518052','170709525','32518053','32518056','32518055','166995482','166995481','32513190','32513186','32513189','52710826','52710825','52710830','52710829','52710729','52710732','52710725','31989806','52710721','26390647','31989810','91782022','28931673','45357943','91781805','115701035','45357945','45357944','115701034','45357947','47249140','45357933','45357946','45357948','45357934','47249142','47249141','52077563','47249123','40664798','52077561','40664797','40664800','48217364','40664799','48217365','48217366','77010678','48217362','77010686','77010683','41329267','77010681','41329269','41329257','41329293','41329263','30472159','90618949','29001575','30472156','30472157','30472158','90618948','29001576','90618955','42235204','29001577','90618952','41329290','41329285','29001578','41329286','96130828','42235203','42235205','28931674','30983737','28931675','188965099','119431265','139758510','188965097','188965098','139758506','188965100','193668757','22733720','22733722','27462089','12191431','12191467','100515391','32489712','51551834','100515393','51551835','51551836','51551832','171993315','171993314','67790516','171993316','51518003','45054930','119766572','51518005','51518006','51518002','32489715','119766569','32065252','120610514','51519378','51519381','51519382','51519375','120610513','64250370','64250368','64250342','64250377','64250350','64250345','64250374','64250347','26474365','26474366','26474364','4608306','120827884','12837922','12840263','12840264','12840262','12839852','31401907','31401917','31401909','31401912','39815325','39815323','39815324','39815322','39815332','39815330','39815331','39815329','119763488','26784178','119763495','26784183','26784177','26784182','119763490','193766357','119763491','193766360','193766361','193766364','193766363','193766359','193766362','193766358','12838432','119763493','5931788','26786281','26786278','26786280','26786279','93542370','93542367','93542366','93542373','26786264','26786265','26786266','26786263','22868197','22868199','22868200','22868196','119759949','119759944','119759946','119759948','4662504','6109867','6109868','4608309','119745466','119746296','119746297','119745461','119745460','119745467','119745462','119745459','119745463','119745465','51685602','119745464','119745443','22733723','22733723','82405954','22733721','8060412','8060406','8060411','8060413','8060408','8060414','8060409','8060407','82405957','82405954','4767288','4767287','7962136','22733705','22733708','22733710','22733715','22733713'
);

-- sjednoceni dalnic
CREATE TABLE dalniceu AS
SELECT nazev, ST_Union(geom) AS geom
FROM dalnice
GROUP BY nazev;

-- Nastaveni primarnich klicu a prostorovych indexu na vrstvy
ALTER TABLE dalnice
ADD COLUMN gid serial;
ALTER TABLE dalnice
ADD PRIMARY KEY (gid);

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

CREATE INDEX dalnice_geom ON dalnice
USING GIST (geom);
CREATE INDEX dalniceu_geom ON dalniceu
USING GIST (geom);

-- vrstva NAKUPNI CENTRA 

CREATE TABLE nakupni_centra AS
SELECT osm_id, geom, way_area, shop AS druh, name AS nazev
FROM czech_polygon
WHERE shop IN ('mall', 'supermarket', 'department_store');

--prejmenovani sloupce way_area to plocha
ALTER TABLE nakupni_centra RENAME COLUMN way_area TO plocha;

-- pridani primarniho klice
ALTER TABLE nakupni_centra
ADD COLUMN gid serial;
ALTER TABLE nakupni_centra 
ADD PRIMARY KEY (gid); 

-- pridani prostoroveho indexu
CREATE INDEX nc_geom
ON nakupni_centra 
USING GIST(geom);

-- vymazani nazvu s hodnotou NULL
DELETE FROM nakupni_centra 
WHERE nazev is NULL;

-- odstraneni NC mimo Cr
DELETE FROM nakupni_centra
WHERE nakupni_centra.geom NOT IN
(SELECT nakupni_centra.geom FROM nakupni_centra 
JOIN kraje ON ST_Within(nakupni_centra.geom, kraje.geom));
 
-- II. VALIDACE

SELECT osm_id 
FROM nakupni_centra
WHERE not ST_IsValid(geom);

SELECT osm_id 
FROM silnice
WHERE not ST_IsValid(geom);

SELECT osm_id FROM dalnice
WHERE NOT ST_IsSimple(geom);


-- III. DOTAZY

-- Atributove 

--1 Ktere obce Stredoceskeho kraje maji pocet obyvatel mezi 15000-20000?
SELECT nazev, obyv02, kraj FROM obce
WHERE obyv02 > 14999 AND obyv02 < 20001 AND kraj='Stredocesky'
ORDER BY obyv02 ASC;

--2 Kolik je v CR obchodnich domu Prior?
SELECT count(nazev) FROM nakupni_centra
WHERE nazev like '%rior%';

--3 Jaka je prumerna rozloha (v m2) jednoho nakupniho centra v CR?
SELECT ROUND(AVG(plocha)) FROM nakupni_centra;

--4 Najdete nejvetsi obchodni centrum v CR?
SELECT osm_id, nazev, plocha, druh 
FROM nakupni_centra AS nc
ORDER BY plocha DESC limit 1;

-- Prostorove 

--1 Kolik bankomatu je na uzemi Prahy?
SELECT COUNT(*) FROM bankomaty AS b
JOIN (SELECT geom FROM obce
WHERE nazev_eng = 'Praha') AS p
ON ST_Within(b.geom, p.geom);

--2 Jaky kraj ma nejvice obchodnich center?
SELECT count(nc.nazev), kraj FROM kraje 
JOIN nakupni_centra AS nc ON
ST_Intersects(nc.geom, kraje.geom)
GROUP BY kraj ORDER BY count(nazev) 
DESC limit 1;

--3 Kolik procent uzemi zaujimaji nakupni centra?
SELECT ROUND(CAST((nc_plocha/plocha)*100 AS numeric),4)
FROM
(
SELECT SUM(ST_Area(ST_Intersection(nc.geom, obce.geom))) AS nc_plocha,
SUM(ST_Area(obce.geom)) AS plocha
FROM obce
JOIN nakupni_centra AS nc
ON ST_Intersects(nc.geom, obce.geom)
) AS nc_cr;

--4 Uvedte 3 nejvetsi nakupni centra a napiste v jakem meste se nachazeji?
SELECT druh, nc.nazev, o.nazev, plocha 
FROM nakupni_centra AS nc
JOIN obce AS o
ON ST_Within(nc.geom, o.geom)
ORDER BY plocha DESC limit 3;

--5 Jake je nejzapadnejsi kino v CR a v jakem meste se nachazi? Uvedte jeho souradnice.
SELECT kino.nazev, ROUND(ST_Y(kino.geom)) AS y, ROUND(ST_X(kino.geom)) AS x,
obce.nazev FROM kino
JOIN obce ON ST_Intersects(kino.geom,obce.geom)
ORDER BY x ASC limit 1;

--6 Kolik kin je primo v nakupnich centrech?
SELECT count(nc.nazev) FROM nakupni_centra AS nc
JOIN kino AS k 
ON ST_Intersects(nc.geom, k.geom);

--7 Jak daleko od nejmensi obce v kraji Vysocina jsou nejblizsi 3 nc? Vypiste nazev nc, nazev obce a vzdalenost.
SELECT obce.nazev, nc.nazev, ROUND(ST_Distance(obce.geom,nc.geom)/1e3) 
AS delka_km
FROM obce, nakupni_centra AS nc
WHERE obce.nazev= (SELECT nazev FROM obce 
WHERE kraj = 'Vysocina' ORDER BY vymera LIMIT 1)
ORDER BY delka_km
LIMIT 3 ;

--8 Kolik bankomatu je ve vzdalenosti 7 km od obce Sedlice?
SELECT count(b.operator)
FROM obce AS o
JOIN bankomaty AS b
ON ST_Dwithin(o.geom, b.geom, 7000)
WHERE o.nazev = 'Sedlice';

--9 V kterych obcich najdeme kino CineStar? Vypiste nazev a kraj.
SELECT DISTINCT(obce.nazev), obce.kraj FROM obce
JOIN kino AS k ON ST_Intersects(k.geom,obce.geom)
WHERE k.nazev like '%ine_tar%';

--10 Kde se nachazi kino "CRYSTAL"? Vypiste jeho souradnice na cele metry.
SELECT obce.nazev, kraj, ROUND(ST_X(k.geom)), ROUND(ST_Y(k.geom))
FROM kino AS k
JOIN obce
ON ST_Intersects(obce.geom, k.geom)
WHERE k.nazev = 'Crystal';

--11 Urcete souradnice nakupniho centra Plzen Plaza?
SELECT ROUND(ST_X(ST_Centroid(nc.geom))), ROUND(ST_Y(ST_Centroid(nc.geom))),
nazev
FROM nakupni_centra AS nc
WHERE nazev= 'Plzeň Plaza';

--12 Kolik km silnic II. tridy je v Zline?
SELECT ROUND(SUM(ST_Length(s.geom))/1e3) 
FROM silnice AS s
JOIN obce 
ON ST_Within(s.geom,obce.geom)
WHERE obce.nazev_eng='Zlin'
AND s.popis = 'secondary';

--13 Kolik bankomatu je v okolí Vítězného náměstí v Praze (do 250m)?
SELECT count((b.operator))
FROM bankomaty AS b
JOIN silnice AS s
ON ST_Dwithin(b.geom, s.geom, 250)
WHERE s.nazev= 'Vítězné náměstí';

--14 Ktere kino leží nejblíže silnici? Napište i vzdálenost v km.
SELECT k.nazev, ROUND(ST_Distance(k.geom,s.geom))
FROM kino AS k 
JOIN silnice AS s
ON k.geom && s.geom
ORDER BY ST_Distance(k.geom, s.geom)
LIMIT 1;

--15 Ktera nc lezi do 100m od dalnice D5?
SELECT nc.nazev
FROM  nakupni_centra AS nc 
JOIN dalnice AS s 
ON ST_Dwithin(nc.geom, s.geom, 100) 
WHERE popis='motorway' AND s.nazev = 'D5';

--16 Ktere nakupni centrum lezi nejblize hranicim CR?
--DROP TABLE hranice2; //pokud je již vrstva vytvořena
CREATE TABLE hranice2 
AS SELECT ST_Boundary(ST_Union(geom)) AS geom 
FROM obce;

SELECT nc.nazev, ROUND(ST_Distance(nc.geom, h.geom))
FROM nakupni_centra AS nc
JOIN hranice2 AS h
ON nc.geom && h.geom
ORDER BY ST_Distance(nc.geom, h.geom)
LIMIT 1;

--17 Kterymi obcemi prochazi dalnice D1?
SELECT o.nazev, o.geom
FROM obce AS o
JOIN dalniceu AS d
ON ST_Intersects(d.geom,o.geom)
WHERE d.nazev = 'D1'; 