Prostorové databáze - cvičení

Požadavky na zápočet

Zápočet (33 bodů) je rozdělen následujícím způsobem:

1. Písemný test - 9 bodů - v průběhu semestru
2. Semestrální projekt - úlohy s prostorovými operátory - 24 bodů

Zadání zápočtového projektu je uvedeno na stránce s popisem úkolů.

Cvičení 1 - Instalace prostorové databáze a vizualizačního nástroje

Instalace image v prostředí Virtual PC, XE server, SQLDeveloper, GeoRaptor

Cvičení 2 - Přístupy k databázi, prostředí klienta

"Zprovoznění" uživatele HR, vyzkoušení přístupů k databázi - cmd, APEX, SQLDeveloper

Tutoriál SQLDeveloper

Tvorba tabulky

Příkazem jazyka DDL pro vytvoření nové tabulky je CREATE TABLE. V příkazu pojmenujeme tabulku (dle významu obashu dat, předchozí analýzy), její sloupce (atributy), ke kterým nadefinujeme datové typy.

Syntaxe příkazu CREATE TABLE:

CREATE TABLE [nazev_databaze.]nazev_tabulky
   (<nazev sloupce> <datovy typ>
      [DEFAULT <konstantni vyraz>]
      [NULL | NOT NULL]
      [<omezeni pro sloupec>]
      |[<omezeni pro tabulku>]
      [,...n]
   )

V [] jsou uvedeny nepovinné údaje - například [nazev_databaze.]. Za () je uvedena povinná část <nazev sloupce> <datovy typ> s možným opakováním až n-krát [,...n].

Datové typy

Kompletní popis datových typů je možno nalézt v dokumentaci Oracle.

Cvičení 3 - SQL I.

Dotazování nad jednou tabulkou

Structured Query Language (SQL) představuje sadu příkazů, které umožňují pracovat s databází. Základem jazyka SQL je příkaz SELECT. Popis jeho plné notace je uveden na stránce Vyhledávání.

Opakování budeme provádět nad databází HR, ke které jsme získali přístup aktivací uživatele hr. Schema databáze je uvedeno na obrázku Schema HR.

Nejjednodušší forma dotazu - získáme obsah celé tabulky Zamestnanci:

SELECT * FROM employees;

V ORACLE se nerozlišuje psaní malými a velkými písmeny, takže název tabulky i klíčová slova lze psát různým způsobem - employees x EMPLOYEES, select x SELECT.

Výběr některých sloupců dosáhneme použitím projekce, tj. uvedením názvů sloupců za klíčové slovo SELECT. Zde je uveden dotaz, jehož výsledkem je jmenný seznam zaměstnanců:

SELECT FIRST_NAME, LAST_NAME FROM employees; 

V případě, že bychom chtěli pouze seznam příjmení všech zaměstanců bez opakování stejných jmen, použijeme klíčové slovo DISTINCT, které eliminuje výstup - bude bez opakování stejných řádků:

SELECT DISTINCT LAST_NAME FROM employees;

Budeme-li chtít ve výsledku změnit číselné hodnoty nějakého sloupce, lze je přímo dopočítat a na výstupu sloupec také přejmenovat pomocí tzv. alias, za slovem AS:

SELECT LAST_NAME, SALARY + 100 AS "Vyplata s odmenou" FROM employees;

Pro výběr záznamů z tabulky, které vyhovují určité podmínce, použijeme operaci selekce. Zapisujeme ji za klíčové slovo WHERE a podle datového typu atributu volíme zápis podmínky. Pro jmenný seznam zaměstnanců z oddělení 80 pak příkaz vypadá následně:

SELECT FIRST_NAME, LAST_NAME FROM employees WHERE DEPARTMENT_ID=80;

V podmínce je možno použít operátory:

=   rovná se                        AND       a současně
<   je menší než                    OR        nebo
>   je větší než                    NOT       negace
<=  je menší nebo rovno             BETWEEN   pro intervalové hodnoty
>=  je větší nebo rovno             IS (NOT)  ne/shoda s hodnotou NULL
<>  není rovno (dle standardu ISO)  LIKE      shoda s maskou v řetězci

Například vyhledání všech zaměstanců, jejichž jméno začíná na K využije operátor LIKE pro porovnání s maskou řetězce a navíc zástupný znak % za jakékoli jiné znaky (i žádný znak).

SELECT LAST_NAME AS "Zamestnanci s příjemním na 'K'" FROM employees WHERE last_name LIKE 'K%' ;

Pro vyhledání zaměstnanců, kteří nejsou přiděleni k žádnému oddělení, použijeme podmínku, kde zjistíme hodnotu NULL u atributu DEPARTMENT_ID.

SELECT FIRST_NAME, LAST_NAME FROM employees WHERE DEPARTMENT_ID IS NULL;

Budeme-li chtít získat jmenný seznam zaměstnanců oddělení 80, využijeme možnosti setřídít výsledek podle příjmení, případně i podle dvou atributů současně, nejprve dle příjmení a pak křestního jména:

SELECT FIRST_NAME, LAST_NAME FROM employees WHERE DEPARTMENT_ID=80 ORDER BY LAST_NAME;

SELECT FIRST_NAME, LAST_NAME FROM employees WHERE DEPARTMENT_ID=80 ORDER BY LAST_NAME, FIRST_NAME;

Agregační funkce

Agregační funkce představují možnost sloučit záznamy či hodnoty do skupin a nad nimi provézt aritmetickou či statistickou operaci. Výsledkem jsou například počty záznamů (COUNT), součet hodnot (SUM), maximální hodnota (MAX) ze sady numerických hodnot apod. Seznam a význam agregačních funkcí:

COUNT(*) navrací počet počet řádků ve výsledku dotazu
COUNT(DISTINCT)  počet jedinečných výskytů hodnot 
SUM()            součet numerických hodnot ze zadaného sloupce/pole hodnot
AVG()            aritmetický průměr z numerických platných hodnot 
MIN()            minimální hodnotu z numerických platných hodnot 
MAX()            maximální hodnota z numerických platných hodnot
STD()            směrodatnou odchylku
GROUP BY sloučí záznamy se stejnými hodnotami určeného atributu a vytvoří souhrnnou hodnotu

Chceme-li zjistit počet všech zaměstnanců, použijeme operaci COUNT:

SELECT COUNT(*) FROM employees;

Chceme-li zjistit kolik zaměstnanců dostává provizi, použijeme operaci COUNT s názvem sloupce:

SELECT COUNT(commission_pct) FROM employees;

Počet je jiný, protože ve sloupci commission_pct jsou i hodnoty NULL, které funkce nezapočítává.

Jaký je nejvyšší plat v oddělení 80? SELECT MAX(salary) FROM employees WHERE DEPARTMENT_ID=80;

Agregační funkce nelze použít v podmínce za WHERE, ale je možné je použít při seskupování za klauzulí GROUP BY. Chceme-li zjistit minimální platy v každém oddělení, seskupíme palty podle oddělení a v každé skupině je pak zjištěno minimum:

SELECT MIN(salary), department_id FROM employees GROUP BY department_id;

Samostatný úkol

V prostředí SQLDevelopera sestavte dotazy, kterými si vyzkoušíte své znalosti. Zadání dotazů je uvedeno na Opakování SQL. Výsledky své práce ukažte pedagogovi.

Cvičení 4 - SQL II

Dotazování nad více tabulkami

Cvičení 5 - Principy práce s prostorovými daty

Princip práce s prostorovými daty v prostředí SŘBD Oracle.

SDO_Geometry

Databázový server Oracle je rozšířen o objektový prostorový datový typ s názvem SDO_GEOMETRY. Prefix SDO pochází z doby, kdy Oracle spolupracoval s Canadian Hydrographic Service a tehdejší změny v jádru Oracle vedly k označení "Spatial Data Option" (SDO). Jedním z atributů tohoto objektu je geometrie prostorového prvku, která může být tvořena základním geometrickým elementem - bodem, úsečkou, křivkou, kruhem nebo polygonem, případně jejich kombinacemi. V dané tabulce je možné mít neomezený počet sloupců s prostorovými daty.

Oracle Spatial definuje objektový typ SDO_GEOMETRY s následujícími atributy:

CREATE TYPE sdo_geometry AS OBJECT (
 SDO_GTYPE NUMBER, 
 SDO_SRID NUMBER,
 SDO_POINT SDO_POINT_TYPE,
 SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
 SDO_ORDINATES SDO_ORDINATE_ARRAY);

Struktura a základní metody u objektu SDO_Geometry je vidět na třídním diagramu .

Je nutné vnímat "skladbu" tohoto objektu. Při zadávání dat potřebujeme znát význam atributů, které určují geometrii či koordinační (souřadnicový) systém. Dále jsou definovány datové typy SDO_POINT_TYPE, SDO_ELEM_INFO_ARRAY, SDO_ORDINATE_ARRAY použité v definici SDO_GEOMETRY:

CREATE TYPE sdo_point_type AS OBJECT (
  X NUMBER,
  Y NUMBER,
  Z NUMBER);

CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER;

CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) of NUMBER;

Sloupec, který je definován pomocí datového typu SDO_GEOMETRY lze vnímat jako vrstvu, v níž jsou obsaženy prostorové prvky nesoucí informace o své geometrii, mohou být vztažené k určitému souřadnicovému systému a jsou určeny souřadnicemi, kterými je prvek lokalizován.

Význam a možnosti použití jednotlivých atributů objektového typu SDO_GEOMETRY objasní jejich popis.

Atribut SDO_GTYPE

SDO_GTYPE je datového typu NUMBER. Atribut je tvořen 4 číselným kódem, kterým je určen geometrický typ prostorového prvku. Typ geometrie koresponduje se specifikací modelu "Geometry Object Model" definovanou organizací OGC jako OGIS - Simple Features for SQL specification, blíže [1]. Kód je označován jako dltt, kde

d – určuje počet dimenzí (2, 3 nebo 4),
l – vykazuje spojení na LRS (lineární referenční systém), pro 3 nebo 4 dimenzionální hodnoty. V případě geometri bez LRS nebo při akceptaci nastavení Spatial je ponechána hodnota 0,
tt – lze chápat jako číselník geometrií Oracle, kde jsou definovány hodnoty 00 – 09, 10 a jsou rezervovány pro další rozšíření.

Seznam útvarů je vypsán v tabulce Geometrické typy.

Podrobně viz [2].

Například:

2001 dvoudimenzionální bod
3001 třídimenzionální bod
2002 dvoudimenzionální linie nebo křivka
2003 dvoudimenzionální polygon nebo povrch
2004 kolekce (Geometry is a heterogeneous collection of elements. COLLECTION is a superset that includes all other types.)
2005 multipoint (Geometry has one or more points. (MULTIPOINT is a superset of POINT.)) 

Pro zjištění obsažených informací je možné použít metody pro navrácení těchto hodnot: Get_Dims, Get_LRS_Dim, Get_Gtype xxxxxxxxxxxxxxxxx

Příklad vytvoření tabulky s prostorovými daty, včetně tvorby prostorového indexu: příklad Parcely

Cvičení 10 - Zpracování vlastního projektu

---

Výuková podpora pro cvičení z předmětu Objektově orientované technologie vznikla za podpory projektu Inovace bakalářských a magisterských studijních oborů na Hornicko-geologické fakultě VŠB-TUO pod číslem CZ.1.07/2.2.00/28.0308. Tento projekt je realizován za spoluúčasti EU.