--Vypište jména obcí, které mají v názvu Březno.
SELECT name 
 FROM a10.obce 
  WHERE name like '%Březno%';

--Vypište hrady a zámky, které mají v názvu "štejn".
SELECT name 
 FROM a10.pamatky 
  WHERE historic = 'castle' AND name LIKE '%štejn%';

--Vypište jména (name) a druh (amenity) restaurací se zahrádkou (biergarten) a seřaď podle abecedy.
SELECT name, amenity 
 FROM a10.obcerstveni 
  WHERE amenity = 'biergarten' 
 ORDER BY name;

--Kolik je typů památek?
SELECT distinct historic FROM a10.pamatky;

--Kolik je hradů v památkách?
SELECT count(*) FROM a10.pamatky WHERE historic = 'castle';

--Vypište všechny pizzerie (v názvu mají řetězec 'pizz').
SELECT name 
 FROM a10.obcerstveni 
  WHERE amenity='restaurant' AND name LIKE '%pizz%';

--Kolik km komunikací leží v lese?
SELECT SUM(st_length(st_intersection(lesy.way,kom.way))/1e3) 
 FROM a10.lesy AS lesy 
  JOIN a10.komunikace AS kom 
  ON st_intersects(lesy.way, kom.way);

--Kolik památek leží do 5 km od silnice a současně do 5 km od železnice?
SELECT count(distinct pamatky.name) AS pocet_pamatek 
FROM a10.pamatky AS pamatky  JOIN a10.komunikace AS kom 
 ON st_dwithin(pamatky.way, kom.way, 5000) JOIN a10.zeleznice AS zelez 
 ON st_dwithin(pamatky.way, zelez.way, 5000);

--Jaká je celková výměra vodních ploch v km2?
SELECT ROUND(SUM(st_area(way)/1e6)) AS area_km2 FROM a10.voda;

--Kolik vodních ploch leží dál než 5km od obcí?
SELECT count(voda.osm_id) FROM a10.voda AS voda 
LEFT JOIN a10.obce AS obce ON st_dwithin(voda.way, obce.way, 5000) 
WHERE obce.osm_id is null; 

--Kolik památek je v lese? 
SELECT count(*) AS pocet_pamatek FROM a10.pamatky AS p 
 JOIN a10.lesy AS l ON st_within(p.way, l.way);

--Kolik občerstvení je u silnice (tj. do 100 m) ?
SELECT count(distinct ob.name) AS obcerstveni_u_silnice 
FROM a10.obcerstveni AS ob 
JOIN a10.komunikace AS kom ON st_dwithin(ob.way, kom.way, 100);

--Na kolika místech se kříží železnice a silnice ?
SELECT count(*) AS pocet_krizeni FROM a10.komunikace AS kom 
 JOIN a10.zeleznice AS zelez ON st_crosses(kom.way, zelez.way);

--Jaká je výměra lesů, které leží do 10 km od Prahy, Brna a Ostravy?
CREATE VIEW a10.obce_buffer AS SELECT st_buffer(way,1e4) AS way 
FROM a10.obce 
WHERE name = 'Praha' or name = 'Brno' or name = 'Ostrava';

SELECT SUM(st_area(st_intersection(obce_buff.way,lesy.way))/1e6) 
FROM a10.lesy AS lesy JOIN a10.obce_buffer AS obce_buff 
ON st_intersects(obce_buff.way, lesy.way);

--Kolik vodních ploch a s jakou výměrou leží zcela v lese nebo se lesa dotýkají?
SELECT COUNT(voda.osm_id), SUM(st_area(voda.way)/1e6) 
 FROM a10.voda AS voda JOIN a10.lesy AS lesy 
 ON st_touches(voda.way,lesy.way) or st_within(voda.way, lesy.way);

--Kolik % území, které je do 20 km od Ostravy, zaujimá les?
CREATE VIEW a10.ostr_buff AS SELECT st_buffer(way,2e4) AS way 
 FROM a10.obce WHERE name = 'Ostrava';

SELECT ROUND((lesy_area/ostr_area)*100)
 FROM (SELECT SUM(st_area(st_intersection(ostrava.way, lesy.way)))
 AS lesy_area, st_area(ostrava.way) AS ostr_area 
 FROM a10.ostr_buff AS ostrava 
 JOIN a10.lesy AS lesy 
 ON st_intersects(ostrava.way,lesy.way) 
 GROUP BY ostr_area) AS lesy_ostr;

--Jaký je poměr celkové délky komunikace/železnice?
SELECT (SELECT (SUM(st_length(kom.way))) FROM a10.komunikace AS kom)/
 (SELECT (SUM(st_length(zelez.way))) FROM a10.zeleznice AS zelez);

--Kolik km dálnic je v ČR?
SELECT ROUND(SUM(st_length(kom.way)/1e3)) AS delka_dalnic 
FROM a10.komunikace AS kom WHERE highway = 'motorway';

--Vypište 3 největší úseky železnice, které jsou v lese.
--Uveďte id železnice, id lesa a délku.
SELECT z.osm_id AS zeleznice, l.osm_id AS les, 
 st_length(st_intersection(z.way, l.way)) 
 FROM a10.zeleznice AS z join a10.lesy AS l 
 ON st_crosses(z.way, l.way) 
 ORDER BY st_length DESC LIMIT 3;

--Vyberte nejbližší nevěstinec od hradu Kozel v km.
SELECT o.name, st_distance(p.way,o.way)/1e3 AS v 
 FROM a10.pamatky AS p JOIN a10.obcerstveni AS o
 ON o.amenity = 'brothel' AND p.name = 'Kozel' 
 ORDER BY v DESC LIMIT 1;

--Vypište počet občerstvení ležící u lesa (do 100 m).
SELECT COUNT(DISTINCT(o.name)) FROM a10.obcerstveni AS o 
JOIN a10.lesy AS l ON st_dwithin(o.way,l.way,100);

--Kolik % plochy ČR zabírají lesy, když plocha republiky je 78866 km2?
SELECT ((SELECT SUM(st_area(lesy.way))/1e6 FROM a10.lesy)/78866);

--Jaká je nejmenší vodní plocha a jeji výměra (její osm_id a vymera v m2)?
SELECT osm_id, st_area(way) AS area 
FROM a10.voda ORDER BY st_area(way) ASC LIMIT 1;

--Obvod nejmenšího lesa?
SELECT osm_id, st_perimeter(lesy.way) AS obvod 
FROM a10.lesy ORDER BY obvod ASC LIMIT 1;

--Kolik obcí leží v lese?
SELECT COUNT(*) FROM a10.obce AS o 
JOIN a10.lesy AS l ON st_contains(l.way,o.way);

--Najděte nejbližší obec u Prahy a její vzdálenost.
SELECT DISTINCT ON(o1.name) o1.name, o2.name, st_distance(o1.way,o2.way) 
 FROM a10.obce AS o1 
 JOIN a10.obce AS o2 ON(o1.name <> o2.name and o1.name ='Praha') 
 ORDER BY o1.name, st_distance ASC;

--V okruhu 5 km od obcí najděte pro každou obec nejbližší památku. 
--Vypište prvních pět obcí, jméno památky a jejich vzdálenost.
SELECT DISTINCT ON(o1.name) o1.name , o2.name , 
st_distance(o1.way,o2.way) 
FROM a10.obce AS o1 JOIN  a10.pamatky AS o2  
ON st_dwithin(o1.way, o2.way, 5000) 
ORDER BY o1.name, st_distance(o1.way, o2.way) LIMIT 5;

