Analýza možností SŘBD PostgreSQL / PostGIS pro vytvoření datového skladu v prostředí GIS

1. Analýza možností SŘBD PostgreSQL / PostGIS pro vytvoření datového skladu v prostředí GIS Jaromír Kamler Vedoucí: Antonín Orlík

2. ÚVOD • Trend ukládat geodata do relačních databází • Důvody: • Centralizace dat (řízení přístupu, aktuálnost) • Sjednocení grafických a popisných atributů • Definice souřadnicového systému • Možnost využití funkcí definovaných v databázi pro složité prostorové operace s daty • Možné prostředky:

3. Cíl práce • Testovat a zhodnotit možnosti SŘBD PostgreSQL/PostGIS pro práci s geodaty • Úkoly: • Seznámení s PostgreSQL/PostGIS • Testování importu / exportu geodat do PostgreSQL, komunikace s různými programovými prostředky • Možnosti správy dat, uživatelů a skupin, nastavování práv • Možnosti využití funkcí PostGIS • Indexace, porovnání časové náročnosti dotazů s použitím indexace a bez ní

4. PostgreSQL / PostGIS 1/2 • RDBMS PostgreSQL: • Open-source produkt • Dodržování standardů SQL92 a SQL99 • Možnost rozšiřování např. o nové: • Datové typy • Funkce (C, Perl, Python, PL/pgSQL) • Operátory • Tvorba triggerů • Možnost instalace na GNU/Linux i MS Windows

5. PostgreSQL / PostGIS 2/2 • PostGIS: • Rozšíření databázového systému PostgreSQL • Dodržování standardu OGC • Přidává nové funkce a datové typy pro ukládání prostorových dat • Přidává nové tabulky pro definování prostorových dat

6. Definice souřadnicového systému S-JTSK

7. Oprava definice S-JTSK • Oprava definice S-JTSK v knihovně proj4 • Aplikace patche na soubor PJ_krovak.c ve zdrojových kódech proj4 • Kompilace zdrojových kódů nebo tvorba balíčku

8. Transformace z S-JTSK do WGS-84 s opravou knihovny proj4

9. Import / export dat do PostgreSQL • ogr2ogr • Součást knihovny OGR • Schopnost práce s 25 datovými formáty bash-3.00$ ogr2ogr -f PostgreSQL PG:'dbname=kam038 user=kam038 password=***' silnice.shp • shp2pgsql / pgsql2shp bash-3.00$ shp2pgsql -s 102065 ulice.shp ulicePoruba kam038 | psql -d kam038 • SQL INSERT INTO MySpatialTable (nazev, the_geom) VALUES('polygon', GeometryFromText('POLYGON((0 0 0,4 0 0,4 4 0,0 4 0,0 00),(1 1 0,2 1 0,2 2 0,1 2 0,1 1 0))'));

11. Funkce pro prostorové dotazy • PostGIS rozšiřuje možnosti práce s daty o prostorové dotazy. • Funkce typu: • Boolean Touches(),Intersects(),Contains(), ... • Constructive Diference(),Union(),Buffer(), ...

12. Města ve vzdálenosti 15 km od Prahy CREATE TABLE near_city_p AS SELECT * FROM sidlacr_p WHERE distance(the_geom, praha.the_geom) < 15000;

13. Zjednodušení říční sítě kam038=# CREATE TABLE simplified_rivers ASSELECT simplify(the_geom,0.5) FROM rivers;

14. Indexace tabulek • B-tree - základní typ indexu • Nejvíce urychluje operace <, <=, =, >=, >, LIKE, … • Hash - nejpomalejší na vytvoření. • Rychlý při porovnávání řetězců (hashů), pouze pro operátor = • GiST (zobecněný vyhledávací strom) – • Jedná se o rozšiřitelnou strukturu, která sdružuje mezi jinými vlastnosti B-trees a R-trees • R-tree – index pro geometrická data. • Pro operátory: @, ~= , &&, …

15. Indexy • Vytváření CREATE [UNIQUE] INDEX jmeno ON tabulka [USING typ_indexu] (sloupec) • Rušení DROP INDEX jmeno • Nutnost použití VACUUM ANALYZE • Při vkládání velkého množství dat může vytížením reindexací dojít k přerušení transakce

16. Dotaz na geometrická data

17. Kombinovaný dotaz

18. Programové prostředky komunikující s PostgreSQL 1/2 • GRASS • Nutnost importu dat do nativní podoby GRASS GRASS 6.0.0 (cvicna):~ > v.in.ogr -o dsn='PG:host=postgis.vsb.cz user=kam038 password=*** dbname=kam038' output=nhpn_grass layer=nhpn • ArcGIS • Pomocí extense „data interoperability“ v ArcCatalog • Problém s PostGIS v. 1.x • Důvodem je změna interního ukládání dat v PostgreSQL z WKT na WKB • Odstranění problému pomocí instalace FME rozšíření ArcCatalogu

19. Programové prostředky komunikující s PostgreSQL 2/2 • Quantum GIS • Načítání vrstev z ESRI Shapefile a PostGIS, rastry • UMN Mapserver • Načítání vrstev z Oracle, MySQL, PostGIS, ESRI Shapefile, … • UDIG • Načítání vrstev z PostGIS, DB2, WFS, WMS, ESRI Shapefile a rastrové soubory

21. Přístupová práva • Definice přístupových práv: • Celé databázi (CREATE) • Tabulkám (SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER) • Jednotlivým sloupcům v tabulce • Funkcím (EXECUTE) • Jazykům

22. Tvorba funkcí v PL/pgSQL 1/2 • Nutnost definice jazyka v databázi: bash-3.00$ createlang plpgsql mojedb • Rychlost interních funkcí způsobená předchozím vytvořením prováděcího plánu • Využívání SQL a funkcí definovaných uživatelem

23. Tvorba funkcí v PL/pgSQL 2/2 CREATE OR REPLACE FUNCTION incid() RETURNS void AS $$ DECLARE a RECORD; b RECORD; c boolean; BEGIN FOR a IN SELECT id FROM public.line LOOP FOR b IN SELECT id FROM public.line WHERE id <> a.id LOOP SELECT INTO c touches((SELECT the_geom FROM line WHERE id=a.id), (SELECT the_geom FROM line WHERE id=b.id)); IF c = true THEN RAISE NOTICE 'linie % navazuje na %', a.id, b.id; END IF; END LOOP; END LOOP; END; $$ LANGUAGE PLPGSQL;

24. Tvorba spouštěčů (trigger) • Nástroj zajišťující automatické provedení funkce před nebo po vložení, zrušení nebo změně záznamu v tabulce • Trigger může volat libovolnou funkci vracející datový typ trigger • Definice triggeru: CREATE TRIGGER nazev { BEFORE | AFTER } { udalost [ OR ... ] } ON tabulka [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE nazev_funkce ( argumenty )

25. ZÁVĚREČNÉ ZHODNOCENÍ • relativně malá podpora komerčních systémů • +/- podpora ukládání rastrů – unstable PGCHIP • + vysoká rozšiřitelnost • + standardy OGC • + transparentnost • +zadarmo

26. Děkuji za pozornost