´ Uvod do zpracov´an´ı prostorov´ych dat
Semestr´ aln´ı projekt
DOKUMENTACE
Kateˇrina Hynkov´a Martin Lˇz´ıˇcaˇr Tom´aˇs Tich´y
ˇ ´ VYSOKE ´ UCEN ˇ ´I TECHNICKE ´ V PRAZE CESK E Fakulta stavebn´ı Katedra mapov´an´ı a kartografie 11. 6. 2011
Obsah ´ 1 Uvod 1.1 Zad´an´ı . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3 3
2 Zdroje dat 2.1 Sch´ema osm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.2 Sch´ema gis1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3 3 3
3 Tvorba tematick´ ych vrstev a jejich validace 3.1 Bodov´e vrstvy . . . . . . . . . . . . . . . . . 3.1.1 Hrady a z´amky . . . . . . . . . . . . 3.1.2 Koup´an´ı . . . . . . . . . . . . . . . . 3.1.3 Stravov´an´ı . . . . . . . . . . . . . . . 3.2 Liniov´e vrstvy . . . . . . . . . . . . . . . . . 3.2.1 Silnice . . . . . . . . . . . . . . . . . 3.3 Polygonov´e vrstvy . . . . . . . . . . . . . . 3.3.1 Obce . . . . . . . . . . . . . . . . . .
4 4 4 7 8 8 8 9 9
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
. . . . . . . .
4 Dotazy 9 4.1 Atributov´e dotazy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 4.2 Prostorov´e dotazy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 5 Pouˇ zit´ y software
21
6 Z´ avˇ er
21
7 Seznam pˇ r´ıloh
21
2
´ Uvod
1
Tato dokumentace si klade za c´ıl sezn´amit ˇcten´aˇre s pr˚ ubˇehem vzniku semestr´aln´ıho ´ projektu pro pˇredmˇet Uvod do zpracov´an´ı prostorov´ ych dat (153UZPD). Tento pˇredmˇet je vypisov´an Katedrou mapov´ani a kartografie pro studenty 3. roˇcn´ıku ˇ eho vysok´eho uˇcen´ı technick´eho oboru Geoinformatika na Fakultˇe stavebn´ı Cesk´ v Praze. Z poskytnut´ ych dat byly vytvoˇreny tematick´e vrstvy a po kontrole jejich validity nad nimi byly provedeny atributov´e a prostorov´e dotazy.
1.1
Zad´ an´ı
• Navrhnˇete a vytvoˇrte tematick´e vrstvy na z´akladˇe dat OpenStreetMap (OSM) • Aplikujte testy datov´e integrity a odstraˇ nte pˇr´ıpadn´e nekonzistence v datech • Vytvoˇrte tutori´al pro v´ yuku PostGIS - tj. sadu atributov´ ych a prostorov´ ych dotaz˚ u nad datab´az´ı pgis_uzpd
2
Zdroje dat
Data byla z´ısk´ana z cviˇcn´e datab´aze PostGIS pgis_student1 um´ıstˇen´e na serveru geo.1022 .
2.1
Sch´ ema osm
ˇ e republiky Sch´ema osm obsahuje data z projektu OpenStreetMap3 pro u ´zem´ı Cesk´ ze dne 9. 3. 2011. OpenStreetMap pouˇz´ıv´a souˇradnicov´ y syst´em Google Mercator (SRID 900913).
2.2
Sch´ ema gis1
Sch´ema gis1 obsahuje data ze cviˇcen´ı k pˇredmˇetu GIS1 (153GIS1). Tato data jsou v souˇradnicov´em syst´emu S-JTSK Krovak East North (SRID 102067). 1
http://geo101.fsv.cvut.cz/gwiki/Cviˇcn´a datab´aze PostGIS#gis1 http://geo102.fsv.cvut.cz/ 3 www.openstreetmap.org 2
3
3
Tvorba tematick´ ych vrstev a jejich validace
Pro prov´adˇen´ı anal´ yz jsme se rozhodli vytvoˇrit 5 tematick´ ych vrstev. Volbu jejich obsahu jsme podm´ınili urˇcitou praktickou vyuˇzitelnost´ı. Vrstvy jsme koncipovali tak, aby je ve v´ ysledku bylo moˇzn´e pouˇz´ıt k vyhled´av´an´ı turistick´ ych c´ıl˚ u (hrad˚ ua ’ z´amk˚ u) a d´ale koupaliˇst ˇci stravovac´ıch zaˇr´ızen´ı v jejich okol´ı. Pro zjednoduˇsen´ı pˇr´ıkaz˚ u a dotaz˚ u byla nastavena cesta ke sch´emat˚ um: SET SEARCH_PATH TO e11, public, osm Pro kaˇzdou vytvoˇrenou tematickou vrstvu byl definov´an prim´arn´ı kl´ıˇc, vytvoˇren prostorov´ y index a aktualizov´ana metadatov´a tabulka geometry_columns. Vˇsechny konkr´etn´ı pˇr´ıkazy a dotazy, kter´e vedly k vytvoˇren´ı validn´ıch tematick´ ych vrstev, se nach´az´ı v pˇriloˇzen´em SQL d´avkov´em souboru.
3.1
Bodov´ e vrstvy
3.1.1
Hrady a z´ amky
Do vrstvy hrad˚ u a z´amk˚ u byly vybr´any ze sch´ematu osm prvky s atributem castle (hrady a z´amky) ze sekce historic (historick´a m´ısta). Tyto atributy maj´ı jak polygonov´e, tak bodov´e prvky, proto byly v prvn´ı f´azi vytvoˇreny 2 vrstvy: • castle_p (polygonov´a vrstva) CREATE TABLE e11.castle_p AS SELECT osm_id, way, name FROM czech_polygon WHERE historic=’castle’ • castle_b (bodov´a vrstva) CREATE TABLE e11.castle_b AS SELECT osm_id, way, name FROM czech_point WHERE historic=’castle’ Abychom mohli prov´est kontroly, zda se ve v´ ysledn´e vrstvˇe nenach´az´ı pro 1 objekt 2 body, odstranili jsme z nich prvky, jejichˇz atribut name mˇel hodnotu NULL. Pro u ´ˇcely zam´ yˇslen´ ych anal´ yz plnˇe dostaˇcuj´ı data ve formˇe bod˚ u. Polygonov´a vrstva castle_p byla tedy pomoc´ı funkce ST_CENTROID, kter´a vrac´ı tˇeˇziˇstˇe prvku, 4
pˇrevedena na vrstvu bodovou. Nˇekter´e objekty ovˇsem tvoˇr´ı v´ıce polygon˚ u a v tomto pˇr´ıpadˇe funkce ST_CENTROID vrac´ı tˇeˇziˇstˇe jednotliv´ ych prvk˚ u.
Obr.1: Aplikace funkce ST_Centroid pro objekt tvoˇren´y v´ıce polygony
Z tohoto d˚ uvodu jsme tyto polygony nejprve spojili funkc´ı ST_UNION a teprve pot´e pouˇzili funkci ST_CENTROID.
Obr.2: Aplikace funkce ST_Centroid na slouˇcen´y polygon
5
Body z´ıskan´e t´ımto zp˚ usobem jsme vloˇzili do bodov´e vrstvy hrad˚ u a z´amk˚ u hrady_zamky. Prvk˚ um, kter´e vznikly sjednocen´ım v´ıce polygon˚ u, bylo vygenerov´ano id. • Vloˇzen´ı bod˚ u vznikl´ ych z 1 polygonu INSERT INTO hrady_zamky SELECT osm_id, ST_CENTROID(way) AS way, name FROM castle_p WHERE name NOT IN (’Hrad Stˇ rekov’, ’Vargaˇ c’, ’z´ amek Tloskov’, ’Buˇ covick´ y z´ amek’, ’Z´ amek Hoˇ r´ ın’, ’Nov´ e Hrady’, ’Daˇ cice’, ’Z´ amek Veltrusy’, ˇ ’Cesk´ y ˇ Sternberk’, ’Nov´ y z´ amek’, ’B´ ıtov’, ’Z´ amek Beneˇ sov nad Plouˇ cnic´ ı’, ’Hrad Kunˇ etick´ a Hora’, ’Z´ amek Dobˇ r´ ıs ˇ’, ’z´ amek Velk´ e Meziˇ rı ´ˇ cı ´’) • Vloˇzen´ı bod˚ u vznikl´ ych z v´ıce polygon˚ u CREATE TABLE docasna AS SELECT ST_CENTROID(ST_UNION(way)) AS way, name FROM castle_p WHERE name IN (’Hrad Stˇ rekov’, ’Vargaˇ c’, ’z´ amek Tloskov’, ’Buˇ covick´ y z´ amek’, ’Z´ amek Hoˇ r´ ın’, ’Nov´ e Hrady’, ’Daˇ cice’, ’Z´ amek Veltrusy’, ’ˇ Cesk´ y ˇ Sternberk’, ’Nov´ y z´ amek’, ’B´ ıtov’, ’Z´ amek Beneˇ sov nad Plouˇ cnic´ ı’, ’Hrad Kunˇ etick´ a Hora’, ’Z´ amek Dobˇ r´ ıs ˇ’, 6
’z´ amek Velk´ e Meziˇ rı ´ˇ cı ´’) GROUP BY name ALTER TABLE docasna ADD COLUMN gid serial; INSERT INTO hrady_zamky SELECT gid, way, name FROM docasna Pot´e jsme provedli kontrolu, zda se vrstvˇe nenach´az´ı pro jeden objekt 2 body. Nejprve jsme vyhledali body se stejn´ ym atributem name.
SELECT name, COUNT(name) FROM hrady_zamky GROUP BY name ORDER BY count(name) D´ale jsme vybrali body z p˚ uvodn´ı bodov´e vrstvy castle_b, kter´e leˇz´ı uvnitˇr polygon˚ u p˚ uvodn´ı polygonov´e vrstvy castle_p a vizu´alnˇe zkontrolovali, zda pro jeden objekt neexistuj´ı 2 body s r˚ uzn´ ym atributem name.
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) Pokud byly ve vrstvˇe hrady_zamky nalezeny 2 body definuj´ıc´ı 1 objekt, byl jeden z bod˚ u odstranˇen.
3.1.2
Koup´ an´ı
Do vrstvy koupaliˇst’ byly ze sch´ematu osm vloˇzeny prvky s atributem leisure (voln´ y ˇcas) o hodnotˇe water_park (aquapark) nebo swimming_pool (koupaliˇstˇe). Tyto objekty se v datab´azi stejnˇe jako hrady a z´amky objevuj´ı ve formˇe bod˚ ui polygon˚ u, proto jsme je stejn´ ym postupem jako v pˇredchoz´ım pˇr´ıpadˇe pˇrevedli na v´ yhradnˇe bodov´e prvky. Ovˇeˇren´ı, zda se ve vrstvˇe nenach´az´ı 2 body definuj´ıc´ı 1 objekt, bylo provedeno na z´akladˇe stejn´ ych n´azv˚ u. Vˇetˇsina prvk˚ u v datab´azi ovˇsem nem´a vyplnˇen atribut name, proto nebyla kontrola u ´pln´a. Vrstva je pˇresto validn´ı.
7
3.1.3
Stravov´ an´ı
Do vrstvy stravovac´ıch zaˇr´ızen´ı byly ze sch´ematu osm vloˇzeny prvky s atributem amenity (obˇcansk´a vybavenost) o hodnotˇe restaurant (restaurace), food_court (m´ısto s v´ıce stravovac´ımi zaˇr´ızen´ımi), fast_food (fast food), pub (hospoda) nebo biergarten (venkovn´ı posezen´ı). Tyto objekty se v datab´azi stejnˇe jako hrady a z´amky objevuj´ı ve formˇe bod˚ ui polygon˚ u, proto jsme je stejn´ ym postupem jako v pˇredchoz´ım pˇr´ıpadˇe pˇrevedli na v´ yhradnˇe bodov´e prvky. Ovˇeˇren´ı, zda se vrstvˇe nenach´az´ı 2 body definuj´ıc´ı 1 objekt, byla provedena u ˇc´asti prvk˚ u vizu´alnˇe na z´akladˇe pr˚ uniku p˚ uvodn´ı polygonov´e vrstvy s p˚ uvodn´ı bodovou vrstvou. Protoˇze vrstva obsahuje velk´e mnoˇzstv´ı prvk˚ u, nav´ıc ˇcasto s nevyplnˇen´ ym atributem name, a tak´e je obt´ıˇzn´e rozhodnout, jestli se v dan´em m´ıstˇe nenach´az´ı nˇekolik objekt˚ u v tˇesn´e bl´ızkosti, nebyla kontrola u ´pln´a. Vrstva je pˇresto validn´ı.
3.2 3.2.1
Liniov´ e vrstvy Silnice
Do vrstvy komunikac´ı byly ze sch´ematu osm vybr´any prvky s atributem highway (pozemn´ı komunikace) o hodnotˇe motorway (d´alnice), motorway_link (n´ajezdy a sjezdy k d´alnic´ım), trunk (rychlostn´ı komunikace), trunk_link (n´ajezdy a sjezdy k rychlostn´ı komunikaci), primary (silnice 1. tˇr´ıdy), primary_link (pˇr´ıpojn´e rampy ke komunikaci 1. tˇr´ıdy), secondary (silnice 2. tˇr´ıdy), secondary_link (pˇr´ıpojn´e rampy ke komunikaci 2. tˇr´ıdy), tertiary (silnice 3. tˇr´ıdy), tertiary_link (pˇr´ıpojn´e rampy ke komunikaci 3. tˇr´ıdy). Validita vrstvy byla ovˇeˇrena pomoc´ı funkce ST_IsValid4 , kter´a pro liniov´e prvky kontroluje, zda jsou jednoduch´e, ˇcili zda ˇretˇezec neproch´az´ı 1 bodem 2kr´at, s v´ yjimkou koncov´eho bodu. Funkce nenalezla ˇza´dn´e invalidn´ı prvky.
SELECT * FROM silnice WHERE NOT ST_IsValid(way) 4
http://postgis.refractions.net/docs/ST IsValid.html
8
3.3 3.3.1
Polygonov´ e vrstvy Obce
ˇ byla pˇrevzata ze sch´ematu gis1 a pomoc´ı pˇr´ıkazu pˇrevzat´eho ze Vrstva obc´ı CR str´anek pˇredmˇetu pˇretransformov´ana ze souˇradnicov´eho syst´emu S-JTSK Krovak East North do souˇradnicov´eho syst´emu Google Mercator.
4
Dotazy
Dotazy jsme se snaˇzili formulovat tak, abychom uk´azali urˇcitou praktickou vyuˇzitelnost datab´az´ı prostorov´ ych dat a z´aroveˇ n pouˇzili r˚ uzn´e funkce a postupy. U kaˇzd´eho dotazu uv´ad´ıme slovn´ı zad´an´ı, SQL pˇr´ıkaz a pˇribliˇznou dobu vyhodnocen´ı v programu pgAdmin v1.12.3. Nˇekter´e prostorov´e dotazy jsou doplnˇeny vizualizac´ı ˇreˇsen´ı v programu Quantum GIS 1.6.0
4.1
Atributov´ e dotazy
1. Kolik je hrad˚ u a z´amk˚ u, jejichˇz n´azev konˇc´ı na ’ice’ ? SELECT COUNT(name) FROM hrady_zamky WHERE name LIKE ’%ice’ count ------47 (1 row) Celkov´ a doba prov´ adˇ en´ ı dotazu: 11 ms. Z´ ısk´ an 1 r ˇ´ adek. ˇ pˇripad´a pr˚ 2. Kolik obyvatel CR umˇernˇe na 1 koupaliˇstˇe? SELECT (SELECT SUM(obyv02) FROM obce)/COUNT(osm_id) FROM koupani ?column? ----------------------95357.654205607476636 (1 row) 9
Celkov´ a doba prov´ adˇ en´ ı dotazu: 12 ms. Z´ ısk´ an 1 r ˇ´ adek. ˇ 3. Jak´a je hustota hrad˚ u na u ´zem´ı CR? SELECT COUNT(osm_id)/(SELECT SUM(area)/1e6 FROM obce) FROM hrady_zamky ?column? -----------------------0.00446052726133204921 (1 row) Celkov´ a doba prov´ adˇ en´ ı dotazu: 23 ms. Z´ ısk´ an 1 r ˇ´ adek.
4.2
Prostorov´ e dotazy
1. Kter´e hrady a z´amky se nach´az´ı na u ´zem´ı obce Lednice?
Obr.3: 1. Kter´e hrady a z´amky se nach´az´ı na u ´zem´ı obce Lednice?
SELECT h.name FROM hrady_zamky AS h JOIN obce AS c ON ST_Intersects(h.way, c.geom) 10
WHERE c.nazev=’Lednice’ name --------------Jan˚ uv hrad Z´ amek Lednice (2 rows) Celkov´ a doba prov´ adˇ en´ ı dotazu: 12 ms. Z´ ısk´ any 2 r ˇ´ adky.
2. Na u ´zem´ı kter´e obce je nejv´ıce hrad˚ u a z´amk˚ u? 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´ a doba prov´ adˇ en´ ı dotazu: 754 ms. Z´ ısk´ an 1 r ˇ´ adek.
3. Kter´e hrady a z´amky leˇz´ı do 200 m od nejbliˇzˇs´ı silnice na u ´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ˇ nsk´ y z´ ameˇ cek Chodovsk´ a tvrz (2 rows) 11
Obr.4: 3. Kter´e hrady a z´amky leˇz´ı do 200 m od nejbliˇzˇs´ı silnice na u ´zem´ı obce Praha?
Celkov´ a doba prov´ adˇ en´ ı dotazu: 23 ms. Z´ ısk´ any 2 r ˇ´ adky. ˇ y 4. Kolik stravovac´ıch zaˇr´ızen´ı leˇz´ı do 1 km od hradu St´atn´ı hrad a z´amek Cesk´ Krumlov?
Obr.5: 4. Kolik stravovac´ıch zaˇr´ızen´ı leˇz´ı do 1 km od hradu St´atn´ı hrad a z´amek ˇ y Krumlov? Cesk´
12
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´ atn´ ı hrad a z´ amek ˇ Cesk´ y Krumlov’ count ------8 (1 row) Celkov´ a doba prov´ adˇ en´ ı dotazu: 13 ms. Z´ ısk´ an 1 r ˇ´ adek.
5. Kolik koupaliˇst’ leˇz´ı do 10 km od hradu Znojemsk´ y hrad?
Obr.6: 5. Kolik koupaliˇst’ leˇz´ı do 10 km od hradu Znojemsk´y 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´ y hrad’ count ------3 (1 row) 13
Celkov´ a doba prov´ adˇ en´ ı dotazu: 24 ms. Z´ ısk´ an 1 r ˇ´ adek.
6. Kolik stravovac´ıch zaˇr´ızen´ı leˇz´ı do 0,5 km od koupaliˇstˇe Aquacentrum Bospor?
Obr.7: 6. Kolik stravovac´ıch zaˇr´ızen´ı leˇz´ı do 0,5 km od koupaliˇstˇe Aquacentrum Bospor?
SELCT 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´ a doba prov´ adˇ en´ ı dotazu: 11 ms. Z´ ısk´ an 1 r ˇ´ adek.
7. Kter´ y hrad leˇz´ı v nejvˇetˇs´ı vzd´alenosti od silnice? SELECT h.name FROM hrady_zamky AS h 14
CROSS GROUP ORDER LIMIT
JOIN silnice AS s BY h.name BY MIN(ST_Distance(h.way, s.way)) DESC 1
name -----L´ any (1 row) Celkov´ a doba prov´ adˇ en´ ı dotazu: 43085 ms. Z´ ısk´ an 1 r ˇ´ adek.
8. Kter´ y hrad leˇz´ı v nejmenˇs´ı vzd´alenosti 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´ e Budˇ ejovice (1 row) Celkov´ a doba prov´ adˇ en´ ı dotazu: 42753 ms. Z´ ısk´ an 1 r ˇ´ adek.
9. Kter´e stravovac´ı zaˇr´ızen´ı leˇz´ı nejbl´ıˇze 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´ e Vsi (1 row) Celkov´ a doba prov´ adˇ en´ ı dotazu: 22 ms. Z´ ısk´ an 1 r ˇ´ adek.
15
Obr.8: 9. Kter´e stravovac´ı zaˇr´ızen´ı leˇz´ı nejbl´ıˇze hradu Svojanov?
10. Kter´e koupaliˇstˇe leˇz´ı nejbl´ıˇze hradu Z´amek Opoˇcno?
Obr.9: 10. Kter´e koupaliˇstˇe leˇz´ı nejbl´ıˇze hradu Z´amek Opoˇcno?
SELECT k.name FROM koupani AS k CROSS JOIN hrady_zamky AS h WHERE h.name=’Z´ amek Opoˇ cno’ ORDER BY ST_Distance(k.way, h.way) 16
LIMIT 1 name ----------------Dobruˇ ssk´ y baz´ en (1 row) Celkov´ a doba prov´ adˇ en´ ı dotazu: 12 ms. Z´ ısk´ an 1 r ˇ´ adek.
11. Na u ´zem´ı kter´e obce (nazev, gid) je nejvˇetˇs´ı hustota hrad˚ u (nejv´ıce hrad˚ u na 2 km )? 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˚ uv D˚ ul | 758 (1 row) Celkov´ a doba prov´ adˇ en´ ı dotazu: 73 ms. Z´ ısk´ an 1 r ˇ´ adek.
12. V kter´e obci je nejv´ıce stravovac´ıch zaˇr´ı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ˇ rensko (1 row) Celkov´ a doba prov´ adˇ en´ ı dotazu: 285 ms. 17
Obr.10: 12. V kter´e obci je nejv´ıce stravovac´ıch zaˇr´ızen´ı na obyvatele?
Z´ ısk´ an 1 r ˇ´ adek.
13. Kolik stravovac´ıch zaˇr´ızen´ı leˇz´ı do 100 m od d´alnice? 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´ a doba prov´ adˇ en´ ı dotazu: 52 ms. Z´ ısk´ an 1 r ˇ´ adek.
14. Kter´a pizzerie (stravovac´ı zaˇr´ızen´ı, jehoˇz n´azev obsahuje ˇretˇezec izz) leˇz´ı nejbl´ıˇze kter´eho hradu? SELECT s.name, h.name FROM stravovani AS s CROSS JOIN hrady_zamky AS h WHERE s.name LIKE ’%izz%’ 18
ORDER BY ST_Distance(s.way, h.way) LIMIT 1 name | name ------------------+-----------------Pizzerie Renardo | Vrchlabsk´ y z´ amek (1 row) Celkov´ a doba prov´ adˇ en´ ı dotazu: 71 ms. Z´ ısk´ an 1 r ˇ´ adek.
15. V kolika obc´ıch nejsou ˇza´dn´a stravovac´ı zaˇr´ı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´ a doba prov´ adˇ en´ ı dotazu: 276 ms. Z´ ısk´ an 1 r ˇ´ adek.
16. Kter´e 2 hrady jsou od sebe nejvzd´alenˇejˇs´ı? 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˚ uv empirov´ y z´ amek | Burg Hohenberg (1 row) Celkov´ a doba prov´ adˇ en´ ı dotazu: 173 ms. Z´ ısk´ an 1 r ˇ´ adek.
19
Obr.11: 16. Kter´e 2 hrady jsou od sebe nejvzd´alenˇejˇs´ı?
ˇ Obr.12: 17. Kter´y hrad leˇz´ı nejbl´ıˇze hranic´ım CR?
ˇ 17. Kter´ y hrad leˇz´ı nejbl´ıˇze hranic´ım CR? DROP TABLE hranice; CREATE TABLE hranice AS SELECT ST_Boundary(ST_Union(geom)) AS way FROM obce;
20
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´ a doba prov´ adˇ en´ ı dotazu: 73 ms. Z´ ısk´ an 1 r ˇ´ adek.
5
Pouˇ zit´ y software • n´astroje pro administraci a spr´avu PostgreSQL pgAdmin v1.10.5 • svobodn´ y a multiplatformn´ı geografick´ y informaˇcn´ı syst´em Quantum GIS 1.4.0 • s´azec´ı program LATEX a kompil´ator MiKTeX 2.8
6
Z´ avˇ er
´ V´ ysledkem naˇseho semestr´aln´ıho projektu v r´amci pˇredmˇetu Uvod do zpracov´an´ı prostorov´ ych dat je 5 validn´ıch tematick´ ych vrstev (hrady_zamky, koupani, stravovani, silnice, obce) ve sch´ematu e11 v datab´azi pgis_uzpd a sada atributov´ ych a prostorov´ ych dotaz˚ u, kter´e byly nad tˇemito vrstvami provedeny. Jak moc jejich v´ ysledky odpov´ıdaj´ı skuteˇcnosti, z´avis´ı pˇredevˇs´ım na kvalitˇe vstupn´ıch dat z projektu Openˇ e republiky liˇs´ı. StreetMap, jeˇz se pro r˚ uzn´e ˇca´sti u ´zem´ı Cesk´
7
Seznam pˇ r´ıloh • SQL d´avkov´ y soubor • Prezentace
Reference ´ [1] 153UZPD Uvod do zpracov´an´ı prostorov´ ych dat http://geo101.fsv.cvut.cz/gwiki/153UZPD
21
[2] PostGIS 1.5.2 Manual http://postgis.refractions.net/docs/ [3] projekt OpenStreetMap http://www.openstreetmap.org/ [4] software pgAdmin http://www.pgadmin.org/ [5] software Quantum GIS http://www.qgis.org/
22
