1 of 10

Databáze v geoinformatice VIII.

Mgr. Michal Jakl

ZS 2025

2 of 10

Obsah lekce

  1. Samostatná práce na příkladu z praxe

​

​

​

​

​

​

Praktické cvičení:

Praktické procvičení dosud získaných poznatků

3 of 10

Úvod

Dnes máte příležitost ponořit se do praktického světa databází a prostorových dat s rozšířením PostGIS. Vaším úkolem bude postupovat podle pokynů v této prezentaci, vytvořit požadované tabulky, naplnit je daty a odpovědět na sadu dotazů pomocí SQL. Tento úkol je navržen tak, aby vám umožnil procvičit si:

  • návrh databázových tabulek,
  • práci s datovými typy a referenčními vazbami,
  • manipulaci s daty (vkládání, aktualizaci a mazání),
  • pokročilé SQL dotazy včetně práce s datem, agregacemi a prostorovými funkcemi.

4 of 10

Téma

V rámci tohoto úkolu si zahrajete na analytiky kriminality a budete pracovat s databází zaměřenou na kriminálníky, jejich činy a jejich klasifikaci. Cílem je nejen procvičit si práci s databázemi, ale také lépe porozumět tomu, jak data mohou být užitečná například pro potřeby policie či jiných bezpečnostních složek.

​

Jak databáze pomáhají policii?

  • Databázové systémy hrají klíčovou roli v každodenní práci policie. Umožňují:
    1. Sledování trestné činnosti: Ukládání a analýzu dat o trestných činech – kdo je spáchal, kdy a kde.
    2. Geografickou analýzu: PostGIS pomáhá vizualizovat data na mapách, což umožňuje lokalizovat oblasti s vyšší kriminalitou a nasměrovat tam policejní činnost.
    3. Predikci a prevence: Na základě analýzy historických dat mohou být určeny trendy a vzorce kriminality, což pomáhá předcházet budoucím zločinům.

5 of 10

Co Vás čeká?

Návrh databázového modelu:

  • Vytvoříte tabulky reprezentující kriminálníky, jejich činy a klasifikaci trestných činů. Budete přemýšlet o správných datových typech a relacích mezi tabulkami.

Práce s daty:

  • Naplníte tabulky konkrétními daty podle zadání. Zaměříte se na přesné zadání formátů a na využití prostorových bodů pro místní údaje.

Řešení dotazů:

  • Budete odpovídat na otázky pomocí SQL příkazů. Dotazy zahrnují nejen základní výběry, ale i agregace, filtry a prostorové analýzy.

Manipulace s daty:

  • Vyzkoušíte si mazání a aktualizaci dat podle specifických požadavků.

6 of 10

1) Tvorba tabulek

Vytvořte ve své PostgreSQL databázi “policie” s PostGIS extenzí a v ní ve schématu “public” následující tabulky a vhodně pro ně definujte datové typy atributů:

Trestné činy – tabulka „ciny“

    • ID činu
    • Třída činu
    • Pachatel
    • Datum a čas spáchaní
    • Místo spáchání
    • Vyšetřeno?

Kriminalistické třídy - „krim_tridy“

    • ID třídy
    • Název třídy
    • Závažnost (= „váha“ daného činu)

Kriminálníci – tabulka „kriminalnici“

    • ID
    • Jméno
    • Příjmení
    • Datum narozeni
    • Pohlaví

7 of 10

2) Naplnění daty

Do tabulky „krim_tridy“ vložte následující data

Naplňte (pomocí QGISu) tabulku „ciny“ alespoň 15 záznamy kriminálních činů, které jednotliví kriminálníci spáchali. Jednotlivé činy umisťujte do okresu Ústí nad Labem.

Naplňte tabulku „kriminalnici“ záznamy alespoň pěti kriminálníků

8 of 10

3) Dotazy

Odpovězte na následující dotazy vždy jedním SQL příkazem:

​

  1. Zobrazte činy spáchané v letním období (červen - srpen)
  2. Zobrazte pouze data spáchání jednotlivých činů a text „muž“, pokud jej spáchal muž či „žena“ pokud čin spáchala žena
  3. Kolik je unikátních prodejců drog?
  4. Zjistěte průměrný věk pachatelů vražd.
  5. Zjistěte průměrný počet činů na kriminálníka
  6. Seřaďte kriminálníky dle nebezpečnosti (nejnebezpečnější je ten, jehož souhrnná závažnost spáchaných činů je nejvyšší)
  7. Kolik procent zaznamenaných činů ještě nebylo vyšetřeno?
  8. Kolik dopravních nehod mají na svědomí ženy?
  9. Kterých 5 činů bylo spácháno vzdušnou čarou nejdále od místa se souřadnicemi 50.6598636, 14.0360378?

Poté smažte všechny záznamy o loupežích a aktualizujte závažnost vraždy na hodnotu 15 (opět si zapište SQL dotazy pro provedení těchto úkonů)

9 of 10

4) Závěr

Na konci této aktivity pochopíte nejen techniky práce s databázemi, ale také způsob, jakým mohou být data využívána v reálných scénářích – například pro zlepšení veřejné bezpečnosti nebo podporu při rozhodování policejních složek.

​

Pracujte systematicky, pozorně čtěte zadání a zamyslete se nad tím, jak by takové databázové systémy mohly být vylepšeny pro skutečné použití. Kromě technických dovedností si tak rozšíříte i svůj pohled na praktické využití IT ve světě kolem nás.

SVÉ SQL SKRIPTY NAHRAJTE DO MOODLU KE CVIČENÍ Z DANÉ HODINY!

10 of 10

Děkuji za pozornost a těším se příště!��Prostor pro zpětnou vazbu

mail@mjakl.cz

Tel. 608 544 839

Web: mjakl.cz

​