1 of 25

Databáze v geoinformatice VII.

Mgr. Michal Jakl

ZS 2025

2 of 25

Obsah lekce

  1. PostGIS - procvičení funkcí
  2. Prostorové indexy
  3. pgexercises.com
  4. SpatiaLite

​

​

​

​

Praktické cvičení:

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

3 of 25

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 25

0. Načtení do QGISu II.

5 of 25

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 25

1. PostGIS – ST_Intersects

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

7 of 25

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 25

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 25

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 25

1. PostGIS – Buffer

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

11 of 25

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 25

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 25

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 25

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 25

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 25

2. Prostorové indexy – BSP-tree

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

17 of 25

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 25

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 25

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 25

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 25

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 25

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 25

5. pgexercises.com

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

24 of 25

6. SpatiaLite

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

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

25 of 25

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

​