Prostorové databáze - cvičení

From Wikivyuka
Revision as of 07:33, 2 August 2014 by Dur30 (talk | contribs) (→‎Cvičení 7 - Práce s bodovými geoobjekty)

Jump to: navigation, search

Obsah a cíle cvičení z předmětu Prostorové databáze

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

Použití příkazu SELECT nad jednou tabulkou je popsáno na stránce Dotazy nad jednou tabulkou.

Agregační funkce

Po zopakování základního použití příkazu SELECT jej rozšíříme o Agregační funkce.

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

Definice SDO_GEOMETRY

Princip práce s prostorovými daty v prostředí SŘBD Oracle je postaven na objektovém datovém typu SDO_GEOMETRY. Jeho definice a význam struktury je popsán na stránce SDO_GEOMETRY.

Cvičení 6 - Tvorba a ukládání v ORACLE definovaných geometrií

Ukládání geometrií

Specifické kódy, které používá Spatial Oracle pro ukládání různých tvarů geometrií vyzkoušíme tvorbou tabulky jednoduchou strukturou a předpřipravenými ukázkami na stránce Ukázky geometrií. Pro vyjasnění kódů používaných v Spatial Oracle pro různé tvary geometrií použijeme příklad z dokumentace Oracle a prvky zobrazíme v prostředí Prohlížeče.

Samostatný úkol

V prostředí Oracle napište příkaz insert, který do vytvořené tabulky T1 vloží Vaše vlastní geometrie. Fantazii se meze nekladou. Výsledky své práce ukažte pedagogovi.

Cvičení 7 - Práce s bodovými geoobjekty

The CUSTOMERS table has the following definition. (An actual customers table would have more information, but the definition is simplified for this scenario.)

CREATE TABLE customers ( 

 customer_id NUMBER,
 last_name VARCHAR2(30),
 first_name VARCHAR2(30),
 street_address VARCHAR2(40),
 city VARCHAR2(30),
 state_province_code VARCHAR2(2),
 postal_code VARCHAR2(9),
 cust_geo_location SDO_GEOMETRY);

The STORES table has the following definition:

CREATE TABLE stores ( 

 store_id NUMBER,
 description VARCHAR2(100),
 street_address VARCHAR2(40),
 city VARCHAR2(30),
 state_province_code VARCHAR2(2),
 postal_code VARCHAR2(9),
 store_geo_location SDO_GEOMETRY);

-- Clean up from any previous running of this procedure.

DROP TABLE customers; DROP TABLE stores; DROP INDEX customers_sidx; DROP INDEX stores_sidx; DELETE FROM USER_SDO_GEOM_METADATA 

  WHERE TABLE_NAME = 'CUSTOMERS' AND COLUMN_NAME = 'CUST_GEO_LOCATION';

DELETE FROM USER_SDO_GEOM_METADATA 

  WHERE TABLE_NAME = 'STORES' AND COLUMN_NAME = 'STORE_GEO_LOCATION';

-- Create table for customer information.

CREATE TABLE customers ( 

 customer_id NUMBER,
 last_name VARCHAR2(30),
 first_name VARCHAR2(30),
 street_address VARCHAR2(40),
 city VARCHAR2(30),
 state_province_code VARCHAR2(2),
 postal_code VARCHAR2(9),
 cust_geo_location SDO_GEOMETRY);

-- Create table for store information.

CREATE TABLE stores ( 

 store_id NUMBER,
 description VARCHAR2(100),
 street_address VARCHAR2(40),
 city VARCHAR2(30),
 state_province_code VARCHAR2(2),
 postal_code VARCHAR2(9),
 store_geo_location SDO_GEOMETRY);

-- Insert customer data.

INSERT INTO customers VALUES 

 (1001,'Nichols', 'Alexandra', 
 '17 Maple Drive', 'Nashua', 'NH','03062',
  SDO_GEOMETRY(2001, 8307, 
    SDO_POINT_TYPE (-71.48923,42.72347,NULL), NULL, NULL));

INSERT INTO customers VALUES 

 (1002,'Harris', 'Melvin', 
 '5543 Harrison Blvd', 'Reston', 'VA', '20190',
 SDO_GEOMETRY(2001, 8307, 
   SDO_POINT_TYPE(-70.120133,44.795766,NULL), NULL, NULL));

INSERT INTO customers VALUES 

 (1003,'Chang', 'Marian', 
 '294 Main St', 'Concord', 'MA','01742',
  SDO_GEOMETRY(2001, 8307, 
    SDO_POINT_TYPE (-71.351,42.4598,NULL), NULL, NULL));

INSERT INTO customers VALUES 

 (1004,'Williams', 'Thomas', 
 '84 Hayward Rd', 'Acton', 'MA','01720',
  SDO_GEOMETRY(2001, 8307, 
    SDO_POINT_TYPE (-71.4559,42.4748,NULL), NULL, NULL));

INSERT INTO customers VALUES 

 (1005,'Rodriguez', 'Carla', 
 '9876 Pine Lane', 'Sudbury', 'MA','01776',
  SDO_GEOMETRY(2001, 8307, 
    SDO_POINT_TYPE (-71.4242,42.3826,NULL), NULL, NULL));

INSERT INTO customers VALUES 

 (1006,'Adnani', 'Ramesh', 
 '1357 Appletree Ct', 'Falls Church', 'VA','22042 ',
  SDO_GEOMETRY(2001, 8307, 
    SDO_POINT_TYPE (-77.1745,38.88505,NULL),NULL,NULL));

-- Insert stores data.

INSERT INTO stores VALUES 

 (101,'Nashua megastore', 
 '123 Commercial Way', 'Nashua', 'NH','03062',
  SDO_GEOMETRY(2001, 8307, 
    SDO_POINT_TYPE (-71.49074,42.7229,NULL),NULL,NULL));

INSERT INTO stores VALUES 

 (102,'Reston store', 
 '99 Main Blvd', 'Reston', 'VA','22070',
  SDO_GEOMETRY(2001, 8307, 
    SDO_POINT_TYPE (-77.34511,38.9521,NULL),NULL,NULL));

-- Add metadata to spatial view USER_SDO_GEOM_METADATA.

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) 

  VALUES ('CUSTOMERS', 'CUST_GEO_LOCATION', 
  SDO_DIM_ARRAY 
    (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5), 
    SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)), 
  8307);

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) 

  VALUES ('STORES', 'STORE_GEO_LOCATION', 
  SDO_DIM_ARRAY 
    (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5), 
    SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)), 
  8307);

-- Create spatial indexes.

CREATE INDEX customers_sidx ON customers(cust_geo_location) 

 INDEXTYPE IS mdsys.spatial_index;

CREATE INDEX stores_sidx ON stores(store_geo_location) 

 INDEXTYPE IS mdsys.spatial_index;

-- Perform location-based queries.

-- Find the 3 closest customers to store_id = 101.

SELECT /*+ordered*/ 

  c.customer_id, 
  c.first_name, 
  c.last_name

FROM stores s, 

  customers c

WHERE s.store_id = 101 AND sdo_nn (c.cust_geo_location, s.store_geo_location, 'sdo_num_res=3') 

  = 'TRUE';

-- Find the 3 closest customers to store_id = 101, and -- order the results by distance.

SELECT /*+ordered*/ 

  c.customer_id, 
  c.first_name, 
  c.last_name,
  sdo_nn_distance (1) distance

FROM stores s, 

  customers c

WHERE s.store_id = 101 AND sdo_nn 

 (c.cust_geo_location, s.store_geo_location, 'sdo_num_res=3', 1)
   = 'TRUE'

ORDER BY distance;

-- Find all the customers within 100 miles of store_id = 101

SELECT /*+ordered*/ 

  c.customer_id,
  c.first_name, 
  c.last_name

FROM stores s, 

  customers c

WHERE s.store_id = 101 AND sdo_within_distance (c.cust_geo_location, 

  s.store_geo_location,
  'distance = 100 unit=MILE') = 'TRUE';

Example 1-2 Inserting Customer and Store Records

-- Insert customer data.

INSERT INTO customers VALUES 

 (1001,'Nichols', 'Alexandra', 
 '17 Maple Drive', 'Nashua', 'NH','03062',
  SDO_GEOMETRY(2001, 8307, 
    SDO_POINT_TYPE (-71.48923,42.72347,NULL), NULL, NULL));

INSERT INTO customers VALUES 

 (1002,'Harris', 'Melvin', 
 '5543 Harrison Blvd', 'Reston', 'VA', '20190',
 SDO_GEOMETRY(2001, 8307, 
   SDO_POINT_TYPE(-70.120133,44.795766,NULL), NULL, NULL));

INSERT INTO customers VALUES 

 (1003,'Chang', 'Marian', 
 '294 Main St', 'Concord', 'MA','01742',
  SDO_GEOMETRY(2001, 8307, 
    SDO_POINT_TYPE (-71.351,42.4598,NULL), NULL, NULL));

INSERT INTO customers VALUES 

 (1004,'Williams', 'Thomas', 
 '84 Hayward Rd', 'Acton', 'MA','01720',
  SDO_GEOMETRY(2001, 8307, 
    SDO_POINT_TYPE (-71.4559,42.4748,NULL), NULL, NULL));

INSERT INTO customers VALUES 

 (1005,'Rodriguez', 'Carla', 
 '9876 Pine Lane', 'Sudbury', 'MA','01776',
  SDO_GEOMETRY(2001, 8307, 
    SDO_POINT_TYPE (-71.4242,42.3826,NULL), NULL, NULL));

INSERT INTO customers VALUES 

 (1006,'Adnani', 'Ramesh', 
 '1357 Appletree Ct', 'Falls Church', 'VA','22042 ',
  SDO_GEOMETRY(2001, 8307, 
    SDO_POINT_TYPE (-77.1745,38.88505,NULL),NULL,NULL));

-- Insert stores data.

INSERT INTO stores VALUES 

 (101,'Nashua megastore', 
 '123 Commercial Way', 'Nashua', 'NH','03062',
  SDO_GEOMETRY(2001, 8307, 
    SDO_POINT_TYPE (-71.49074, 42.7229,NULL),NULL,NULL));

INSERT INTO stores VALUES 

 (102,'Reston store', 
 '99 Main Blvd', 'Reston', 'VA','22070',
  SDO_GEOMETRY(2001, 8307, 
    SDO_POINT_TYPE (-77.34511, 38.9521,NULL),NULL,NULL));

Updating the Spatial Metadata

For each spatial column (type SDO_GEOMETRY), you must insert an appropriate row into the USER_SDO_GEOM_METADATA view to reflect the dimensional information for the area in which the data is located. You must do this before creating spatial indexes (see "Creating Spatial Indexes") on the spatial columns.

The USER_SDO_GEOM_METADATA view has the following definition:

( 

 TABLE_NAME   VARCHAR2(32),
 COLUMN_NAME  VARCHAR2(32),
 DIMINFO      SDO_DIM_ARRAY,
 SRID         NUMBER

);

Cvičení x - Prostorové dotazy

Cvičení 8 - Import dat s prostorovým kontextem

Cvičení 9 - Vizualizace dat s prostorovým kontextem

Cvičení 10 -

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.

Retrieved from "http://gisak.vsb.cz/wikivyuka/index.php?title=Prostorové_databáze_-_cvičení&oldid=3103"