PostgreSQL (Postgis)

Prostorové databázové systémy 2

Jan Caha

2024-03-04

Připojení a práce s DB

  • buď přes specializovaného klienta PGAdmin
  • nebo přes obecné klienty k databázím (např. DBeaver a řada dalších)
  • přes programovací jazyky - Python (knihovna Psycopg), R(knihovny RPostgres a dbplyr)
  • některé IDE mají extenze pro DB
  • jednoúčelové nástroje - např. ogr2ogr z GDAL/OGR
  • příkazová řádka

SQL

  • odlišný od programovacích jazyků
  • popisujeme výsledek, ne process jak se k němu dostat
  • založený na výrazech (SELECT, INSERT, UPDATE, DROP, ALTER…)
  • variace pro různé druhy - MySQL (MariaDB), PostgreSQL, SQLite atd.

Warning

Dokumentace !!!

PostgreSQL

  • v oblasti “větších” databází aktuálně standard - u lehkých databází převládá MySQL nebo SQLite
  • řada variací a forků - HadoopDB, Postgres Pro Enterprise, Red Hat Database, Redshift, Yahoo! Everest
  • extenze - funkcionalita a nové typy dat (např. geodata - PostGIS)
  • vhodné sledovat vývoj, v poslední době dost výrazné změny zejména v rychlosti

Import Export dat

  • buď SQL soubory
  • nebo tvorba tabulky a import CSV (pozor na práva, ne všude může databáze číst - využíváme případně adresář /tmp)
  • nebo přes programovací jazyky či komplexnější nástroje - Python, R, DB software, GDAL/OGR

Příklady - import data z CSV, pomocí GDAL/OGR

Konstrukce WITH

  • dočasné tabulky pro dotazy
  • lze je i zanořovat do sebe
WITH nazev_tabulky AS (
  SELECT ...
)
  • tabulku pak dále použijeme např. v JOIN

Příklad - JOIN s dočasnou tabulkou

VIEW

  • pohled na data
  • reálně neexistující tabulka, která se vytváří na požádání z dat
  • updatuje se v podstatě v reálném čase
  • užitečné proti duplikacím dat
  • může být náročné na čas

Příklad - VIEW prostorvých dat s transformací souřadnicového systému

Připojení a práce přes QGIS

  • možnost připojit data přímo do QGIS
  • včetně editace dat
  • ukládání do DB

Příklad - připojení dat s PostgreSQL do QGIS

PostGIS

  • extenze PostgreSQL pro prostorová data
  • stojí na knihovnách PROJ, GDAL/OGR, GEOS a dalších
  • geometrické typy geometry (častější) a geography (novější)
  • funkce ST_*, spatio-temporal
  • informace o tabulkách s geometriemi ve Views
  • manuál

Data

  • obvykle vektorová
  • lze i rastry, ale to má relativně malé přínosy a výrazně to zvětšuje DB
  • rastry se často ukládají mimo a z DB se pouze referencují, tím se nezvětšuje velikost DB ale lze používat funkce
  • funkce pro oba typy data existují

Seznamy funkce

Souřadnicové systémy

  • ukládají se tabulky spatial_ref_sys ve schématu public při instalaci extenze do DB
  • bez této tabulky nemusí PostGIS fungovat korektně
  • do tabulky nezasahovat, leda skrze funkce PostGIS, jinak je možné způsobit nefunkčnost extenze
  • integerový identfikátor srid obvykle odpovídá EPSG kódu, EPSG:5514 - 5514

Dotazy ???