1 of 31

Databáze v geoinformatice VII.

Mgr. Michal Jakl

ZS 2025

2 of 31

Obsah lekce

  1. PostGIS - procvičení funkcí
  2. Prostorové indexy
  3. Pomoc s tvorbou semestrální práce
  4. Hodnocení semestrální práce
  5. pgexercises.com
  6. SpatiaLite

​

​

​

​

Praktické cvičení:

Procvičování PostGISu a propojení s QGISem

3 of 31

0. Datové typy II. – geometry()

Zdroj: https://towardsdatascience.com/spatial-data-science-with-postgresql-geometries-c00387755700

CREATE TABLE test (id serial PRIMARY KEY, geom geometry(POINT,4326) );

4 of 31

0. Načtení do QGISu II.

5 of 31

1. Příprava a úprava databáze

  1. Upravte v programu QGIS výběhy tak, aby spolu alespoň některé prostorově sousedily (i více než 1:1)
  2. Vytvořte novou tabulku “stroje” (ID, TYP, SPRAVCE, GEOM) a do ní přidejte pomocí QGISu alespoň 15 strojů, které rozmístíte do jednotlivých výběhů i mimo ně

6 of 31

1. PostGIS – ST_Intersects

Vyberte všechny stroje, které se nacházejí na území výběhu X

7 of 31

1. PostGIS – ST_Intersects

SELECT "ID",geom FROM "stroje" as s

WHERE ST_Intersects(s.geom, (SELECT geom FROM "vybehy_area" WHERE ID=3)) = true;

Vyberte všechny stroje, které se nacházejí na území výběhu X

8 of 31

1. PostGIS – ST_Touches

Vyberte všechny výběhy, které mají společnou hranici s výběhem X a načtěte je do QGISu jako novou vrstvu

9 of 31

1. PostGIS – ST_Touches

Vyberte všechny výběhy, které mají společnou hranici s výběhem X a načtěte je do QGISu jako novou vrstvu

SELECT "ID", geom FROM "vybehy_area" as v

WHERE ST_Touches(v.geom, (SELECT geom FROM "vybehy_area" WHERE ID=1)) = true;

10 of 31

1. PostGIS – Buffer

Zjistěte které stroje se nacházejí v okruhu X km od spojnice výběhů Y a Z

11 of 31

1. PostGIS – Buffer

Zjistěte které stroje se nacházejí v okruhu X km od spojnice výběhů Y a Z

SELECT "ID", geom FROM "stroje"

WHERE

ST_Intersects(

ST_SetSRID(

ST_Buffer(

ST_Transform(ST_MakeLine(

(SELECT geom FROM "vybehy" WHERE "ID" = 1),

(SELECT geom FROM "vybehy" WHERE "ID" =4)),5514),

100,'endcap=round join=round'),

5514),ST_Transform(geom,5514)

);

12 of 31

1. PostGIS – ST_Intersects II.

Zjistěte délku spojnice výběhů X – Y a vyberte do mapy výběhy, přes něž tato spojnice přechází.

PAMATUJ -> CIBULE

13 of 31

1. PostGIS – ST_Intersects II.

Zjistěte délku spojnice výběhů X – Y a vyberte do mapy výběhy, přes něž tato spojnice přechází.

SELECT Nazev, geom as spojnice FROM "vybehy_area" WHERE

ST_Intersects(

ST_MakeLine(

(SELECT geom FROM "vybehy" WHERE "ID”=1),

(SELECT geom FROM "vybehy" WHERE "ID" =2)

) ,ST_Transform(geom,4326)

);

14 of 31

2. Prostorové indexy – indexy obecně

  • Indexy jsou databázovými objekty, které vytvářejí pomocné datové struktury pro rychlý a efektivní přístup k záznam ˚um tabulek. Bez indexů by tabulkové dotazy degenerovaly na sekvenční prohledávání všech záznamů tabulky. Dotazy, ve které by v klauzuli WHERE byly neindexované atributy, by byly stejně neefektivní jako hledání v nesetříděném telefonním seznamu.
  • Pokud je v tabulce definován primární klíč, je zároveň vždy automaticky vytvořen i index.
  • Indexy ale můžeme vytvářet i nad neklíčovými atributy, které přitom mohou obsahovat duplicitní hodnoty a hodnoty NULL.
  • Nad tabulkou může být definováno více indexů. V mnoha případech indexy zabírají vetší objem diskového prostoru než vlastní data. Správa indexů vždy představuje nezanedbatelnou režii, je třeba proto definovat je uváženě.

​

15 of 31

2. Prostorové indexy – indexy obecně

CREATE TEMPORARY TABLE demo (key int, val int);

​

INSERT INTO demo SELECT *,cast(random()*1000 AS int)FROM generate_series(1,10000000);

​

SELECT count(*) FROM demo WHERE val=123;

CREATE INDEX valind ON demo (val);

SELECT count(*) FROM demo WHERE val=123;

16 of 31

2. Prostorové indexy – BSP-tree

  • BSP-stromy: založené na rekurzivní dělení prostoru na dva poloprostory.
  • Binary Space Partitioning

17 of 31

2. Prostorové indexy – Quadtree

  • Kvadrátove stromy (Quadtree): založené na postupném dělení prostoru na kvadranty
  • Quad strom je stromová datová struktura, ve které se každý vnitřní uzel rekurzivně rozděluje vždy na čtyři potomky.

18 of 31

2. Prostorové indexy – R-tree

  • R-stromy: dekompozicí prostoru formou minimálních ohraničujících obdélníků, přičemž jednotlivé obdélníky se mohou i vzájemně překrývat.
  • R-strom využívá stejně jako v PostgreSQL systému rozdělení prostoru do obdélníků a podobdélníků.
  • Tento způsob už umožňuje indexovat GIS data v prostorových databázích, ale nepoužívá se ve velké míře, protože není tak robustním indexem oproti GiST v PostGIS.

19 of 31

2. Prostorové indexy – GIST index

  • GIST (Generalized Search Tree): R-strom
  • GiST není klasickým indexem, ale spíše infrastrukturou. Díky prostorovým datovým typům v rozšíření PostGIS může GiST pracovat i s více než dvoudimenzionálními daty. Infrastruktura GiST je používána ve spojení s R-stromem.
  • GiST může indexovat velký rozsah datových typů obsahující GIS data. Kromě požadavku na rychlé vyhledávání v tabulkách o tisících řádcích, je i požadavek na rychlé vytvoření indexu nad takto rozsáhlými sloupci tabulek.
  • V první fázi se vyhledávají všechny objekty na základě jejich minimálního ohraničujícího obdélníku – viz. Operátor překryti “&&”. V druhé fázi se provede přesnější dotazovaní pouze na podmnožině objektu vybraných v první fázi.

Zdroj: https://www.vutbr.cz/www_base/zav_prace_soubor_verejne.php?file_id=132396

20 of 31

2. Prostorové indexy - VACUUM

  • Slouží k vyčištění databáze od nepotřebných dat, které vznikají jejím použitím a k uvolnění místa, která tato data zabírá.
  • Ve zkratce, příkaz VACUUM uklidí datové soubory a odstraní z nich staré řádky po potvrzených transakcích, řádky smazané příkazem DELETE apod. Příkaz VACUUM FULL také zmenší velikost datových souborů.

Zdroj: https://www.heronovo.cz/optimalizace-databaze-postgresql-logy-uklid/

21 of 31

2. Prostorové indexy - ANALYZE

  • Po zadané SQL dotazu se dotaz nejprve analyzuje parserem, který výsledek předá planneru. Planner se pokusí sestavit nejlepší exekuční plán (query plan), podle kterého se dotaz provede (předá jej exekutorovi).
  • Při sestavení optimálního exekučního plánu potřebujeme znát rozložení dat v tabulkách (statistiky). Tyto informace je nutno pravidelně aktualizovat příkazem ANALYZE.
  • V případě, že planner nemá aktuální statistiky nebo statistiky neexistují, nemá planner možnost sestavit optimální plán.
  • Pro velké tabulky příkaz ANALYZE analyzuje pouze náhodný vzorek dat (statistiky se mohou i výrazně lišit).
  • Po velké změně dat v tabulkách je nutné vždy spouštět příkaz ANALYZE.

Zdroj: http://geo102.fsv.cvut.cz/user/cepek/git2/git2.pdf

22 of 31

2. Prostorové indexy

Zjistěte vzdálenosti mezi centroidy všech výběhů bez využití prostorového indexu a s prostorovým indexem

SELECT b1."ID", b2."ID",

ST_Distance(ST_Centroid(b1.geom),

ST_Centroid(b2.geom))/1000 AS distance

FROM "vybehy_area" AS b1, "vybehy_area" AS b2;

Bez použití indexu: 3,7 vteřiny

S použitím indexu: 3,4 vteřiny

23 of 31

3. Pomoc s tvorbou semestrální práce

  1. Výběr vhodného (GIS) tématu
  2. Promyšlení obsahu a rozsahu tabulek v databázi
  3. Návrh jednotlivých tabulek, atributů, datových typů, klíčů a integritních omezení (viz návrh na další stránce)
  4. Optimalizace navržených tabulek (normálové formy, indexy, SRID…)
  5. Ověření funkčnosti a správného nastavení potřebného softwaru (QGIS, pgAdmin, PostgreSQL, PostGIS)
  6. Vytvoření fyzických tabulek v PostGIS databázi a naplnění tabulky daty
  7. Tvorba základních SELECTů, UPDATů a DELETů k jednotlivým tabulkám = základní funkčnost „programu“
  8. Tvorba komplikovanějších dotazů s využitím JOIN, WHERE, GROUP BY, ORDER BY apod. dle požadované funkčnosti „programu“
  9. Tvorba specifických dotazů (i prostorových) dle konkrétních úloh a funkčnosti „programu“
  10. Průběžná tvorba a doplňování „technické dokumentace“
  11. Vytvoření průvodní zprávy a „uživatelského manuálu“
  12. Doplnění zpráv o obrázky, tabulky, grafy, mapy apod.
  13. Kontrola práce dle dostupných hodnotících kritérií
  14. Osobní otestování skriptů a srozumitelnosti dokumentace (ideálně na jiném stroji)
  15. Otestování skriptů a srozumitelnosti dokumentace na kolegovi (osoba znalá problematiky)
  16. Otestování skriptů a srozumitelnosti dokumentace na nezaujaté osobě

​

24 of 31

3. Pomoc s tvorbou semestrální práce

MALUJTE SI TO! ☺

25 of 31

4. Hodnocení semestrální práce

Jsou všechny SQL skripty napsané korektně, efektivně a funkčně?

Skripty obsahují i WHERE, ORDER BY, DISTINCT, GROUP BY, JOIN a LIMIT

SQL skripty jsou psané přehledně a jsou příjemně strukturované

26 of 31

4. Hodnocení semestrální práce

Jsou definovány vhodné datové typy pro jednotlivé atributy?

Splňuje návrh řešení 1. NF?

Splňuje návrh řešení 2. NF?

Splňuje návrh řešení 3. NF?

Databáze má přiměřenou složitost a komplexitu, tj. neobsahuje jen jedinou tabulku o pár atributech.

Jsou vhodně definovány indexy?

27 of 31

4. Hodnocení semestrální práce

Je uživatelský návod srozumitelný a kompletní?

Obsahuje odevzdané řešení uživatelský návod?

Obsahuje odevzdané řešení průvodní (technickou) zprávu?

Je průvodní (technická) zpráva srozumitelná a kompletní?

Obsahuje odevzdané řešení zkušební data v dostatečném rozsahu?

Odevzdané texty jsou vhodně doplněny obrázky

28 of 31

4. Hodnocení semestrální práce

Obsahují skripty nějakou „ST_“ funkci z PostGISu?

Implementuje databáze prostorové rozšíření Post GIS a datový typ geometry pro některý z atributů?

Prostorová složka dat v databázi má patřičný smysl a opodstatnění (tzn. není tam vměstnána nesmyslně a násilím)

Jsou pro data definovány SRID?

Obsahují skripty nějakou kombinovanou „ST_“ funkci z PostGISu?

29 of 31

5. pgexercises.com

https://pgexercises.com/

Úkol do příště: Kdo se dostane nejdál?

30 of 31

6. SpatiaLite

    • Prostorové rozšíření SQLite
    • “Low-end” k PostGIS
    • Pro přenosné aplikace: např. Android

https://www.gaia-gis.it/fossil/libspatialite/index

31 of 31

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

​