----- Úvod do zpracování prostorových dat -----
-----------------------------------------------
------------- Semestrální projekt -------------
-----------------------------------------------
------------------ Skupina E ------------------
--------------- Kateřina Hynková --------------
---------------- Martin Lžíčař ----------------
----------------- Tomáš Tichý -----------------
-----------------------------------------------
------------------- 8.6.2011 ------------------

-- nastavení cesty

-- vytvoření bodové vrstvy hradů a zámků
CREATE TABLE castle_b 
AS SELECT osm_id, way, name 
FROM czech_point
WHERE historic='castle';

-- vytvoření polygonové vrstvy hradů a zámků
CREATE TABLE castle_p 
AS SELECT osm_id, way, name 
FROM czech_polygon
WHERE historic='castle';

-- odstranění prvků s atributem name o hodnotě NULL
DELETE FROM castle_b
WHERE name IS NULL;

DELETE FROM castle_p
WHERE name IS NULL;

-- vytvoření bodové vrstvy hrady a zámky
CREATE TABLE hrady_zamky
AS SELECT osm_id, way, name
FROM castle_b;

-- nalezení objektů tvořených více polygony
SELECT name, COUNT(name) 
FROM castle_p
GROUP BY name 
ORDER BY count(name);

-- určení centroidů polygonů z polygonové vrstvy a jejich vložení do bodové vrstvy 
INSERT INTO hrady_zamky
SELECT osm_id, ST_Centroid(way) AS way, name 
FROM castle_p 
WHERE name NOT IN ('Hrad Střekov', 
'Vargač', 
'zámek Tloskov', 
'Bučovický zámek', 
'Zámek Hořín', 
'Nové Hrady', 
'Dačice', 
'Zámek Veltrusy', 
'Český Šternberk', 
'Nový zámek', 
'Bítov', 
'Zámek Benešov nad Ploučnicí', 
'Hrad Kunětická Hora', 
'Zámek Dobříš', 
'zámek Velké Meziříčí');

-- určení centroidů sjednocených polygonů pro objekty tvořené více polygony 
CREATE TABLE docasna 
AS SELECT ST_Centroid(ST_Union(way)) AS way, name 
FROM castle_p 
WHERE name IN ('Hrad Střekov', 
'Vargač', 
'zámek Tloskov', 
'Bučovický zámek', 
'Zámek Hořín', 
'Nové Hrady', 
'Dačice', 
'Zámek Veltrusy', 
'Český Šternberk', 
'Nový zámek', 
'Bítov', 
'Zámek Benešov nad Ploučnicí', 
'Hrad Kunětická Hora', 
'Zámek Dobříš', 
'zámek Velké Meziříčí') 
GROUP BY name;

-- generování id centroidů sjednocených polygonů pro objekty tvořené více polygony 
ALTER TABLE docasna ADD COLUMN gid serial;

-- vložení centroidů sjednocených polygonů pro objekty tvořené více polygony do bodové vrstvy
INSERT INTO hrady_zamky
SELECT gid, way, name
FROM docasna;

-- vyhledání objektů, které definují 2 body podle názvu
SELECT name, COUNT(name) 
FROM hrady_zamky
GROUP BY name 
ORDER BY count(name);

-- odstranění jednoho z bodů v případě, kdy 1 objekt definovaly 2 body
DELETE FROM hrady_zamky 
WHERE osm_id='842666679'; --Benesov n Ploucnici

DELETE FROM hrady_zamky 
WHERE osm_id IN ('4','12','3','13','7','8','11','6');

-- vyhledání objektů, které definují 2 body podle určením bodů původní bodové vrstvy, které leží uvnitř polygonů původní polygonové vrstvy
CREATE TABLE kontrola 
AS SELECT ST_Intersection(h.way,c.way) 
FROM castle_b AS h 
JOIN castle_p AS c 
ON ST_Intersects(h.way,c.way);

--odstranění jednoho z bodů v případě, kdy 1 objekt definovaly 2 body
DELETE FROM hrady_zamky WHERE osm_id=30857094;

DELETE FROM hrady_zamky WHERE osm_id=84529738;

-- odstranění pracovních tabulek
SELECT DROPGEOMETRYTABLE ('e11','docasna');

SELECT DROPGEOMETRYTABLE ('e11','kontrola');

SELECT DROPGEOMETRYTABLE ('e11','castle_p');

SELECT DROPGEOMETRYTABLE ('e11','castle_b');

-- vytvoření prostorového indexu
CREATE INDEX hrady_zamky_geom 
ON hrady_zamky USING gist (way);

-- definování primárního klíče
ALTER TABLE hrady_zamky 
ADD PRIMARY KEY(osm_id);

-- aktualizace metadatové tabulky
SELECT populate_geometry_columns('e11.hrady_zamky'::regclass);

-- vytvoření bodové vrstvy koupališť
CREATE TABLE koupani_b 
AS SELECT osm_id, way, name 
FROM czech_point
WHERE leisure IN ('water_park','swimming_pool');

-- vytvoření polygonové vrstvy koupališť
CREATE TABLE koupani_p 
AS SELECT osm_id, way, name 
FROM czech_polygon
WHERE leisure IN ('water_park','swimming_pool');

-- vytvoření bodové vrstvy koupání
CREATE TABLE koupani
AS SELECT osm_id, way, name
FROM koupani_b;

-- nalezení objektů tvořených více polygony
SELECT name, COUNT(name) 
FROM koupani_p
GROUP BY name 
ORDER BY count(name);

-- určení centroidů polygonů z polygonové vrstvy a jejich vložení do bodové vrstvy 
INSERT INTO koupani
SELECT osm_id, ST_Centroid(way) AS way, name 
FROM koupani_p 
WHERE name NOT IN ('SPORTRELAX JH s.r.o','Kraví hora') OR name IS NULL;

-- určení centroidů sjednocených polygonů pro objekty tvořené více polygony 
CREATE TABLE docasna 
AS SELECT ST_Centroid(ST_Union(way)) AS way, name 
FROM koupani_p 
WHERE name IN ('SPORTRELAX JH s.r.o','Kraví hora')
GROUP BY name;

-- generování id centroidů sjednocených polygonů pro objekty tvořené více polygony 
ALTER TABLE docasna ADD COLUMN gid serial;

-- vložení centroidů sjednocených polygonů pro objekty tvořené více polygony do bodové vrstvy
INSERT INTO koupani
SELECT gid, way, name
FROM docasna;

-- odstranění pracovních tabulek
SELECT DROPGEOMETRYTABLE ('e11','docasna');

SELECT DROPGEOMETRYTABLE ('e11','koupani_p');

SELECT DROPGEOMETRYTABLE ('e11','koupani_b');

-- odstranění jednoho z bodů v případě, kdy 1 objekt definovaly 2 body 
DELETE FROM koupani WHERE osm_id=48002574;

UPDATE koupani SET osm_id=59753494 WHERE osm_id=1;

-- vytvoření prostorového indexu
CREATE INDEX koupani_geom 
ON koupani USING gist (way);

-- definování primárního klíče
ALTER TABLE koupani 
ADD PRIMARY KEY(osm_id);

-- aktualizace metadatové tabulky
SELECT populate_geometry_columns('e11.koupani'::regclass);

-- vytvoření bodové vrstvy stravovacích zařízení
CREATE TABLE stravovani_b 
AS SELECT osm_id, way, name 
FROM czech_point
WHERE amenity IN ('restaurant', 'food_court', 'fast_food', 'pub', 'biergarten');

-- vytvoření polygonové vrstvy stravovacích zařízení
CREATE TABLE stravovani_p 
AS SELECT osm_id, way, name 
FROM czech_polygon
WHERE amenity IN ('restaurant', 'food_court', 'fast_food', 'pub', 'biergarten');

-- vytvoření bodové vrstvy stravování
CREATE TABLE stravovani
AS SELECT osm_id, way, name
FROM stravovani_b;

-- vložení centroidů polygonů z polygonové vrstvy do bodové vrstvy
INSERT INTO stravovani
SELECT osm_id, ST_Centroid(way) AS way, name 
FROM stravovani_p; 

-- vyhledání objektů, které definují 2 body podle určením bodů původní bodové vrstvy, které leží uvnitř polygonů původní polygonové vrstvy
CREATE TABLE kontrola 
AS SELECT ST_Intersection(h.way,c.way) 
FROM stravovani_b AS h 
JOIN stravovani_p AS c 
ON ST_Intersects(h.way,c.way);

-- odstranění jednoho z bodů v případě, kdy 1 objekt definovaly 2 body 
DELETE FROM stravovani 
WHERE osm_id IN (1041981379,830023026,1171787818,417968090,769445444,597835997,19923483,451845893,31971828,363825679,32085053,31800236,441945410,1013733983,1013733925,611688198,1155482381,1155511574,34350114,31512367,258529835,255653013,256764153,250529104,365684304,930684637,32862081,986808392,869991226,487093033,316244795,475420172,31064709,1096688584,581300682,25401465,25401471);

DELETE FROM stravovani 
WHERE osm_id IN (61266811,61266810,61266808,61266823);

-- odstranění pracovních tabulek
SELECT DROPGEOMETRYTABLE ('e11','kontrola');

SELECT DROPGEOMETRYTABLE ('e11','stravovani_p');

SELECT DROPGEOMETRYTABLE ('e11','stravovani_b');

-- vytvoření prostorového indexu
CREATE INDEX stravovani_geom 
ON stravovani USING gist (way);

-- definování primárního klíče
ALTER TABLE stravovani 
ADD PRIMARY KEY(osm_id);

-- aktualizace metadatové tabulky
SELECT populate_geometry_columns('e11.stravovani'::regclass);

-- vytvoření liniové vrstvy komunikací
CREATE TABLE silnice 
AS SELECT osm_id, way, highway AS typ 
FROM czech_line 
WHERE highway IN ('motorway', 'motorway_link','trunk', 'trunk_link', 'primary','primary_link','secondary', 'secondary_link', 'tertiary', 'tertiary_link');

-- vytvoření prostorového indexu
CREATE INDEX silnice_geom 
ON silnice USING gist (way);

-- definování primárního klíče
ALTER TABLE silnice 
ADD PRIMARY KEY(osm_id);

-- kontrola validity funkcí ST_IsValid
SELECT osm_id 
FROM silnice 
WHERE not ST_Isvalid(way);

-- aktualizace metadatové tabulky
SELECT populate_geometry_columns('e11.silnice'::regclass);

-- vytvoření polygonové vrstvy obcí
CREATE TABLE obce 
AS SELECT * 
FROM gis1.obce;

-- transformace do souřadnicového systému Google Mercator
UPDATE obce SET geom = ST_Transform(geom, 900913);

-- definování primárního klíče
ALTER TABLE obce 
ADD PRIMARY KEY(gid);

-- aktualizace metadatové tabulky
SELECT populate_geometry_columns('e11.obce'::regclass);

-- vytvoření prostorového indexu
CREATE INDEX obce_geom 
ON obce USING gist (geom);


----- Atributové dotazy -----

-- 1. Kolik je hradů a zámků, jejichž název končí na 'ice'? 

SELECT COUNT(name) 
FROM hrady_zamky 
WHERE name LIKE '%ice';

-- count 
---------
-- 47
-- (1 row)
-- Celková doba provádění dotazu: 11 ms.
-- Získán 1 řádek.

-- 2. Kolik obyvatel ČR připadá průměrně na 1 koupaliště? 

SELECT 
(SELECT SUM(obyv02) FROM obce)/COUNT(osm_id) 
FROM koupani;

-- ?column? 
-- ----------------------
-- 95357.654205607476636
-- (1 row)
-- Celková doba provádění dotazu: 12 ms.
-- Získán 1 řádek.

-- 3. Jaká je hustota hradů na území ČR? 

SELECT 
COUNT(osm_id)/(SELECT SUM(area)/1e6 FROM obce) 
FROM hrady_zamky;

-- ?column? 
-- ------------------------
-- 0.00446052726133204921
-- (1 row)
-- Celková doba provádění dotazu: 23 ms.
-- Získán 1 řádek.


----- Prostorové dotazy -----

-- 1. Které hrady a zámky se nachází na území obce Lednice?

SELECT h.name 
FROM hrady_zamky AS h 
JOIN obce AS c 
ON ST_Intersects(h.way,c.geom) 
WHERE c.nazev='Lednice';

--      name      
-- --------------
--  Janův hrad
--  Zámek Lednice
-- (2 rows)
-- Celková doba provádění dotazu: 12 ms.
-- Získány 2 řádky.

-- 2. Na území které obce je nejvíce hradů a zámků?

SELECT c.nazev 
FROM hrady_zamky AS h 
JOIN obce AS c 
ON ST_Intersects(h.way, c.geom) 
GROUP BY nazev 
ORDER BY count(*) DESC 
LIMIT 1;

-- nazev 
-- -------
-- Praha
-- (1 row)
-- Celková doba provádění dotazu: 754 ms.
-- Získán 1 řádek.

-- 3. Které hrady a zámky leží do 200 m od nejbližší silnice na území obce Praha?

SELECT DISTINCT(name) 
FROM hrady_zamky AS h 
JOIN silnice AS s 
ON ST_Dwithin(h.way, s.way, 200) 
JOIN obce AS o 
ON ST_Intersects(h.way, o.geom) 
WHERE o.nazev='Praha';

-- name 
-- ------------------
-- Libeňský zámeček
-- Chodovská tvrz
-- (2 rows)
-- Celková doba provádění dotazu: 23 ms.
-- Získány 2 řádky.

-- 4. Kolik stravovacích zařízení leží do 1 km od hradu Státní hrad a zámek Český Krumlov?

SELECT COUNT(s.name) 
FROM hrady_zamky AS h 
JOIN stravovani AS s 
ON ST_Dwithin(h.way, s.way, 1000) 
WHERE h.name='Státní hrad a zámek Český Krumlov';

-- count 
-- -------
-- 8
-- (1 row)
-- Celková doba provádění dotazu: 13 ms.
-- Získán 1 řádek.

-- 5. Kolik koupališť leží do 10 km od hradu Znojemský hrad?

SELECT COUNT(k.name) 
FROM hrady_zamky AS h 
JOIN koupani AS k 
ON ST_Dwithin(h.way, k.way, 10000) 
WHERE h.name='Znojemský hrad';

-- count 
-- -------
-- 3
-- (1 row)
-- Celková doba provádění dotazu: 24 ms.
-- Získán 1 řádek.

-- 6. Kolik stravovacích zařízení leží do 0,5 km od koupaliště Aquacentrum Bospor?

SELECT COUNT(s.name) 
FROM stravovani AS s 
JOIN koupani AS k 
ON ST_Dwithin(s.way, k.way, 500) 
WHERE k.name='Aquacentrum Bospor';

-- count 
-- -------
-- 4
-- (1 row)
-- Celková doba provádění dotazu: 11 ms.
-- Získán 1 řádek.

-- 7. Který hrad leží v největší vzdálenosti od silnice?

SELECT h.name 
FROM hrady_zamky AS h 
CROSS JOIN silnice AS s 
GROUP BY h.name 
ORDER BY MIN(ST_Distance(h.way, s.way)) DESC 
LIMIT 1;

-- name 
-- ------
-- Lány
-- (1 row)
-- Celková doba provádění dotazu: 43085 ms.
-- Získán 1 řádek.

-- 8. Který hrad leží v nejmenší vzdálenosti od silnice?

SELECT h.name 
FROM hrady_zamky AS h 
CROSS JOIN silnice AS s 
ORDER BY ST_Distance(h.way, s.way) ASC 
LIMIT 1;

-- name 
-- ---------------------
-- Moravské Budějovice
-- (1 row)
-- Celková doba provádění dotazu: 42753 ms.
-- Získán 1 řádek.

-- 9. Které stravovací zařízení leží nejblíže hradu Svojanov?

SELECT s.name 
FROM stravovani AS s 
CROSS JOIN hrady_zamky AS h 
WHERE h.name='Svojanov' 
ORDER BY ST_Distance(s.way, h.way) 
LIMIT 1;

-- name 
-- -------------
-- Na Hrdé Vsi
-- (1 row)
-- Celková doba provádění dotazu: 22 ms.
-- Získán 1 řádek.

-- 10. Které koupaliště leží nejblíže hradu Zámek Opočno?

SELECT k.name 
FROM koupani AS k 
CROSS JOIN hrady_zamky AS h 
WHERE h.name='Zámek Opočno' 
ORDER BY ST_Distance(k.way, h.way) 
LIMIT 1;

-- name 
-- -----------------
-- Dobrušský bazén
-- (1 row)
-- Celková doba provádění dotazu: 12 ms.
-- Získán 1 řádek.

-- 11. Na území které obce (nazev, gid) je největší hustota hradů (nejvíce hradů na km$^{2}$)?

SELECT nazev, gid 
FROM hrady_zamky 
JOIN obce 
ON ST_Intersects(geom, way) 
GROUP BY area, nazev, gid 
ORDER BY COUNT(osm_id)/area DESC 
LIMIT 1;

-- nazev | gid 
-- -------------+-----
-- Josefův Důl | 758
-- (1 row)
-- Celková doba provádění dotazu: 73 ms.
-- Získán 1 řádek.

-- 12. V které obci je nejvíce stravovacích zařízení na obyvatele? 

SELECT nazev 
FROM stravovani 
JOIN obce 
ON ST_Intersects(geom, way) 
GROUP BY obyv02, nazev 
ORDER BY COUNT(osm_id)/obyv02 DESC 
LIMIT 1;

-- nazev 
-- ---------
-- Hřensko
-- (1 row)
-- Celková doba provádění dotazu: 285 ms.
-- Získán 1 řádek.

-- 13. Kolik stravovacích zařízení leží do 100 m od dálnice?

SELECT COUNT(h.name) 
FROM stravovani AS h 
JOIN silnice AS s 
ON ST_Dwithin(h.way, s.way, 100) 
WHERE typ='motorway';

-- count 
-- -------
-- 14
-- (1 row)
-- Celková doba provádění dotazu: 52 ms.
-- Získán 1 řádek.

-- 14. Která pizzerie (stravovací zařízení, jehož název obsahuje řetězec izz) leží nejblíže kterého hradu?

SELECT s.name, h.name 
FROM stravovani AS s 
CROSS JOIN hrady_zamky AS h 
WHERE s.name LIKE '%izz%' 
ORDER BY ST_Distance(s.way, h.way) 
LIMIT 1;

-- name | name 
-- ------------------+------------------
-- Pizzerie Renardo | Vrchlabský zámek
-- (1 row)
-- Celková doba provádění dotazu: 71 ms.
-- Získán 1 řádek.

-- 15. V kolika obcích nejsou žádná stravovací zařízení? 

SELECT COUNT(DISTINCT(gid)) 
FROM obce w
WHERE gid NOT IN
(SELECT DISTINCT(gid) 
FROM stravovani 
JOIN obce 
ON ST_Intersects(way, geom));

-- count 
-- -------
-- 5270
-- (1 row)
-- Celková doba provádění dotazu: 276 ms.
-- Získán 1 řádek.

-- 16. Které 2 hrady jsou od sebe nejvzdálenější?

SELECT h.name, z.name 
FROM hrady_zamky AS h 
CROSS JOIN hrady_zamky AS z 
ORDER BY ST_Distance(h.way, z.way) DESC 
LIMIT 1;

-- name | name 
-- -----------------------------+----------------
-- Rothschildův empirový zámek | Burg Hohenberg
-- (1 row)
-- Celková doba provádění dotazu: 173 ms.
-- Získán 1 řádek.

-- 17. Který hrad leží nejblíže hranicím ČR?

-- DROP TABLE hranice;

CREATE TABLE hranice 
AS SELECT ST_Boundary(ST_Union(geom)) AS way 
FROM obce;

SELECT h.name 
FROM hranice AS b 
JOIN hrady_zamky AS h 
ON b.way && h.way 
ORDER BY ST_Distance(b.way, h.way) 
LIMIT 1;

-- name 
-- ----------------
-- Burg Hohenberg
-- (1 row)
-- Celková doba provádění dotazu: 73 ms.
-- Získán 1 řádek.







