Dotazy

1 Atributové dotazy

1.1  Určete nejmenší sportoviště(osm_id)  v ČR podle rozlohy:

SELECT osm_id FROM e10.sportoviste ORDER BY way_area LIMIT 1;
             
Odpověď:osm_id = 43896824


1.2   Kolik je celkem sportovišť v ČR:

SELECT COUNT(*) FROM e10.sportoviste;

Odpověď:count = 1497


1.3  Kolik je v ČR hřišť na  fotbal?

SELECT COUNT(*) FROM e10.sportoviste WHERE sport LIKE '%football%';

Odpověď: count = 111


1.4 Kolik je obcí začínajících na A nebo Z?

SELECT count(*) FROM e10.obce_body WHERE name LIKE 'A%' OR name LIKE 'Z%';

Odpověď: count = 341 obci


1.5 Kolik je v ČR Lhot a Lhotek (např. Červená Lhota, Lhotka u Radnic...)?

SELECT count(osm_id) FROM e10.obce_body WHERE name LIKE '%Lhota%' OR name LIKE '%Lhotka%';

Odpověď: count = 133  Lhot a Lhotek


1.6 Kolik je měst s více než 100 000 obyvateli?

SELECT count(*) FROM e10.obce_body WHERE place = 'city';

Odpověď : count = 10



2 Prostorové dotazy

2.1   Kolik sportovišt v ČR (bodová vrstva) je dál než 8 km od nejbližší železnice?

SELECT COUNT(DISTINCT sportoviste_body.osm_id) FROM e10.sportoviste_body AS sportoviste_body LEFT JOIN e10.zeleznice AS zeleznice ON ST_DWithin(sportoviste_body.way, zeleznice.way, 8e3) WHERE zeleznice.gid IS NULL;

Odpověď:  count = 16


2.2   Kolik vodních toků v ČR protíná železnice?

SELECT COUNT(DISTINCT vodni_toky.osm_id) FROM e10.vodni_toky AS vodni_toky JOIN e10.zeleznice AS zeleznice ON ST_Intersects(zeleznice.way, vodni_toky.way);

Odpověď : count = 813


2.3  Kolik vodních toků v ČR protéká lesem?

SELECT COUNT(DISTINCT vodni_toky.osm_id) FROM e10.vodni_toky AS vodni_toky JOIN f10.lesni_porosty AS porosty ON ST_Intersects(porosty.way, vodni_toky.way);

Odpověď: count = 2047


2.4 Které vojenské prostory jsou větší než 10  km2

SELECT COUNT(osm_id), name FROM f10.military_plg WHERE ST_Area(f10.military_plg.way)» 1e7;

Odpověď:
name
-------------------------
Vojenský újezd Hradiště
Vojenský újezd Brdy
Vojenský újezd Boletice
Vojenský újezd Březina
Vojenský újezd Libavá
(5 rows)



2.5  Kolik obcí  v ČR je v lese?

SELECT COUNT(*) FROM e10.obce_body AS obce JOIN f10.lesni_porosty AS lesy ON ST_Within(obce.way, lesy.way);

Odpověď: count = 180

  
2.6 Kolik je lezeckých centrer, která jsou do 2 km od železnice a 1 km od vodního toku?

CREATE VIEW e10.lezeni AS SELECT osm_id, name, leisure, sport, way FROM e10.sportoviste_body WHERE sport='climbing';

SELECT COUNT(DISTINCT lez.osm_id) FROM e10.lezeni AS lez JOIN e10.zeleznice AS zel ON ST_DWithin(lez.way, zel.way, 2e3) JOIN e10.vodni_toky AS toky ON ST_DWithin(lez.way, toky.way, 1e3);

Odpověď: count=7

2.7 Určete délku Labe (sečtěte délku úseku označených jako Labe).

SELECT (SUM(ST_Length(way))/1000)::int AS labe_km FROM e10.vodni_toky WHERE name = ’Labe’ GROUP BY name;

Odpověď:  labe_km = 575


2.8.  Vyberte všechna sportoviště - body, které se nacházejí ve vzdálenosti do 10 km od nejdelšího úseku řeky Labe a určete jejich souřadnice.

SELECT name, ST_AsText(way) FROM e10.sportoviste_body WHERE ST_Distance((SELECT way FROM e10.vodni_toky WHERE name = 'Labe' ORDER BY ST_Length(way) DESC LIMIT 1), way) « 10000;

Odpověď:
name | st_astext
-----------------+------------------------------------------
Slavín | POINT(1586516.74439325 6518503.00524588)
           | POINT(1586758.66391064 6520367.95378401)
           | POINT(1586852.62869282 6520236.95582516)
Roudnický bazén | POINT(1587397.20364178 6521302.15848799)
           | POINT(1608811.73408568 6517934.35485988)
BB Bar     | POINT(1615552.04019762 6490900.63872238)
           | POINT(1616392.76951989 6491244.43557373)
           | POINT(1616502.95355188 6491460.41936927)

count = 8


2.9  Najděte nejzápadnější obci v ČR.

SELECT nazev, ST_X(obce_body.way) AS xx FROM e10.obce_body AS points ORDER BY xx ASC LIMIT 1;

Odpověď:
name     | xx
-------- +------------------
Trojmezí | 1352110.55792121


2.10  Vypište vzdálenost (v km) mezi zájmovými místy Ostrava a Liberec.

SELECT (ST_Distance((SELECT way FROM e10.obce_body WHERE name = 'Ostrava'), (SELECT way FROM e10.obce_body WHERE name = 'Liberec'))/1e3)::int FROM e10.obce_body WHERE name = 'Ostrava';

Odpověď:
int4
------
394


2.11  Vyhledejte lezecké oblasti, které jsou do 20 km od některého z 5 největších vojenských prostorů.

CREATE VIEW e10.velky_vojensky AS SELECT osm_id,ST_Area(f10.military_plg.way) AS plocha, way FROM f10.military_plg ORDER BY plocha DESC LIMIT 5;

CREATE VIEW e10.lezeni AS SELECT osm_id, name, leisure, sport, way FROM e10.sportoviste_body WHERE sport='climbing';

SELECT lez.name FROM e10.lezeni AS lez JOIN e10.velky_vojensky AS voj ON ST_DWithin(lez.way, voj.way, 20e3);


Odpověď:
name
--------------------
domoradice rock #7


2.12  Jak daleko je lezecká stěna Mammut od nejmenšího tenisového hřiště?

SELECT (ST_Distance((SELECT way FROM e10.sportoviste_body WHERE name = 'Mammut'), (SELECT way FROM e10.sportoviste ORDER BY ST_Area(way) LIMIT 1 ))/1e3)::int ;

Odpověď:
int4
------
118
(1 row)


2.13  Jak velkou plochu dohromady zabírá 10 největších lesních ploch (v km2)?

SELECT sum(plocha) FROM (SELECT ST_Area(f10.lesni_porosty.way)/1e6 AS plocha FROM f10.lesni_porosty ORDER BY plocha DESC LIMIT 10) AS plochy;

Odpověď: 4391 km2


2.14 Kolik vodních toků v ČR protíná sportoviště?

SELECT COUNT(DISTINCT vodni_toky.osm_id) FROM e10.vodni_toky AS vodni_toky JOIN e10.sportoviste AS sportoviste ON ST_Intersects(sportoviste.way, vodni_toky.way);

Odpověď: count = 12