Points

Příklad využívá podklady z Oracle dokumentace zaměřené na práci s modulem Locator.

Příprava tabulek CUSTOMERS a STORES

Uvedený příklad vychází z předpokladu, kdy obchodní společnost má několik maloobchodních prodejen. Aby prodejny mohly zákazníky, kteří jsou v blízkosti daného obchodu, informovat o nových reklamních akcích, je nutné uložit a uchovávat lokalizační údaje jak pro zákazníky, tak obchody. Pak je teprve možné najít odpovídající zákazníky a provádět analýzy využívající znalost polohy obchodu i zákazníků.

Data o Zákaznících budou uloženy v tabulce CUSTOMERS, která má takto definovanou strukturu:

 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)

Data o obchodech budou mít strukturu definovánou v tabulce STORES:

 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)

Než tabulky vytvoříme, je vhodné "vyčistit" databázi pro případ, že by tam už tabulky stejného jména existovaly. (Zejména v případě, že budeme akce opakovat:-)). Příkaz DROP vymaže z databáze tabulku nebo index daného jména, příkaz DELETE vymaže záznam, který je dán podmínkou za klíčovým slovem WHERE.

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';

Nyní je možné vytvořit pomocí příkazů CREATE obě tabulky:

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 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);

Vkládání dat s bodovou geometrií

Pomocí příkazů INSERT začneme vkládat data do tabulek. Hodnoty vkládané do atributů cust_geo_location a store_geo_location jsou typu POINT (hodnota 2001 na pozici SDO_GTYPE), jsou vztaženy k souřadnicovému systému s kódem 8307 (atribut SDO_SRID), dále jsou uváděny souřadnice bodu (atribut SDO_POINT), zbývající atibuty SDO_ELEM_INFO a SDO_ORDINATES mají hodnoty NULL. Při vkládání souřadnic je nutné dát pozor na následující pravidlo: Oracle Locator vyžaduje, aby hodnota hodnotu zeměpisné délky byla umístěna před hodnotou zeměpisné šířky.

Vkládání dat o zákaznících:

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));

Vkládání dat o obchodech.

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));

Vytvoření prostorového indexu

Tvorba prostorového indexu je složena ze dvou kroků, jejichž pořadí je nutné dodržet. Nejprve je nutné pro tabulku s prostorovými informacemi vložit záznam s příslušnými informacemi do systémové tabulky s metadaty. Příkaz INSERT je povolen i běžnému uživateli, ale vkládání záznamu se děje přes pohled USER_SDO_GEOM_METADATA. Ten umožňuje uživateli uložit hodnoty následujících sloupců:

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

kde TABLE_NAME představuje název tabulky s prostorovými daty, COLUMN_NAME je název sloupce, který je typu SDO_GEOMETRY. DIMINFO je pole proměnlivou délkou (VARRAY(4)) obsahujícíc prvky typu SDO_DIM_ELEMENT. Atribut SRID obsahuje buď kód souřadnicového systému, ve kterém jsou lokalizovány geometrie prostorových objektů, nebo má zadánu hodnotu NULL, nejsou-li prvky spojeny s žádným konkrétním souřadnicovým systémem.

Definice SDO_DIM_ARRAY:

Create Type SDO_DIM_ARRAY as VARRAY(4) of SDO_DIM_ELEMENT; 

kde SDO_DIM_ELEMENT je definován:

Create Type SDO_DIM_ELEMENT as OBJECT (
 SDO_DIMNAME VARCHAR2(64),
 SDO_LB NUMBER,
 SDO_UB NUMBER,
 SDO_TOLERANCE NUMBER);

Příklady hodnot DIMINFO:

• SS s kódem 8307:

SDO_DIM_ARRAY (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5), 
               SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)
              ) 

• Bez konkrétního SS (plocha o velikosti 100x100 s tolerancí 0.05):

SDO_DIM_ARRAY (SDO_DIM_ELEMENT('X', 0, 100, 0.05), 
               SDO_DIM_ELEMENT('Y', 0, 100, 0.05)
              ) 

Pole SDO_DIM_ARRAY má počet prvků n, odpovídající n dimenzím. Zde se DIMINFO skládá ze 2 instancí SDO_DIM_ELEMENT pro dvou-dimenzionální geometrie. Mohly by být 3 instance pro 3-dimenzionální geometrie nebo 4 pro 4-dimenzionální geometrie. Každá instance SDO_DIM_ELEMENT v poli musí být validní (nesmí mít NULL hodnotu) pro atributy SDO_LB (dolní mez), SDO_UB (horní mez) zájmového prostoru a SDO_TOLERANCE - toleranci, se kterou jsou dva body vnímány jako totožné. Tolerance představuje přesnost pro prostorová data a musí být větší než nula.

Pro náš příklad budeme vkládat záznamy s metainformacemi o obou tabulkách přes pohled 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); 

Uvedené meze pro dimenze longitude (-180.0, 180.0) a latitude (-90, 90) souvisí geodetickým datem, který používá souřadnicový systém WGS84. Hodnota tolerance 0.5 znamená, že jakékoli dva body ležící od sebe méně než půl metru, jsou považovány za stejný bod v případě funkcí nebo operací pracujícími s určováním polohy.

Teprve nyní můžeme přistoupit k tvorbě prostorového indexu. Prostorové indexy jsou nutné pro mnoho dotazů, které používají operátory modulu Locator, a pro výkon většiny prostorových dotazů jsou důležité, neboť jinak se vyhodnocování vztahů dopočítává teprve při vykonávání dotazu. Před použitím prostorových dat v analýzách či dotazování vytváříme prostorový index pro každý prostorový sloupec. Chcete-li vytvořit prostorové index, použijeme příkaz CREATE INDEX a v klauzuli pro INDEXTYPE použijeme typ MDSYS.SPATIAL_INDEX. Příkaz CREATE INDEX pak má v z8kladn9m tvaru následující složení(může obsahovat řadu parametrů, viz [1]):

CREATE INDEX [schema.]index ON [schema.]table (column)
    INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Název indexu je doporučeno poskládat z názvu tabulky, ke které je index tvořen a zkratky sidx (spatial index). Bude vytvořen na sloupci s typem SDO_GEOMETRY a je to prostorový index(mdsys.spatial_index).

Příkazy pro naše dvě tabulky pak vypadají takto:

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;

Prostorové dotazy nad bodovými daty

Teprve poté, kdy máme vytvořené a naplněné tabulky s prostorovými daty, aktualizovaná prostorová metadata a vytvořené prostorové indexy, můžeme začít používat operátory a funkce Oracle Locator v dotazech, kde práce s prostorem je vázána na definovanou lokalitu.

Chceme vyhledat tři nejbližší zákazníky pro obchod se store_id = 101. V příkazu SQL použijeme operátor SDO_NN ("nearest neighbor - nejbližší soused") ve formátu

SDO_NN (geometry1, geometry2, param [, číslo]);

který používá prostorový index k identifikaci nejbližších sousedů ke geometrii.

Význam operandů:

geometry1   Určuje geometrii sloupce v tabulce. Sloupec musí být prostorově zaindexován. Datovým typem je SDO_GEOMETRY.
geometry2   Určuje buď geometrii z tabulky nebo přechodnou geometrickou instanci. 
            Nejbližší soused nebo sousedé ke geometry2 se vrací od geometry1. Datový typ je SDO_GEOMETRY.
param       Určuje chování operátoru. K dispozici jsou klíčová slova  v tabulce 19-4. Pokud tento parametr nezadáte, 
            operátor vrátí všechny řádky v pořadí podle rostoucí vzdálenosti od geometry2. Datový typ je VARCHAR2.
number      Pokud SDO_NN volá operátor SDO_NN_DISTANCE, který vrací nejmenší vzdálenost mezi geometriemi. Datový typ je NUMBER.

Operátor SDO_NN vrací počet objektů geometry1, které jsou nejblíže k objektům geometry2. Při určování toho, jak blízko sebe jsou dva geometrické objekty, se použije nejkratší vzdálenost mezi dvěma body na obvodu/povrchu každého objektu.

Operand param může zahrnovat některý z následujících parametrů:

distance    Určuje počet jednotek vzdálenosti, po kterém se přestane hledat nejbližší soused. 
            Není-li zadána parametr unit, použije se unit(jednotka měření) stejná, jako mají data. 
            Datový typ je NUMBER. Například: "distance = 10 unit = míle"
sdo_batch_size   Určuje počet řádků, které mají být hodnoceny v době, kdy provádění výrazu SDO_NN se vyhodnocuje vícekrát, 
            aby se vrátil požadovaný počet výsledků, které vyhovují klauzuli WHERE. 
            K dispozici je pouze tehdy, je-li použit index R-tree. Pokud zadáte sdo_batch_size = 0 
            (nebo je-li parametr param zcela vynechán), Spatial modul vypočítá velikost dávky, která je vhodná pro velikost výsledné sady. 
            Datový typ je NUMBER. Například: 'sdo_batch_size = 10'
sdo_num_res    Není-li uveden sdo_batch_size, určuje kolik výsledků (nejbližších sousedů) se má vrátit. 
            Pokud je zadán sdo_batch_size, je toto klíčové slovo ignorováno.
            Datový typ je NUMBER. Například: 'sdo_num_res = 5'
unit        Je-li užito klíčové slovo distance nebo je použito volání SDO_NN_DISTANCE, určuje jednotky měření. 
            Hodnoty jednotek SDO_UNIT jsou uvedeny v tabulce MDSYS.SDO_DIST_UNITS. 
            Datový typ je VARCHAR2. Výchozí = unit (jednotka měření) spojená s daty. 
            U geodetických dat je výchozí hodnotou  metr. Například: 'jednotka = KM'

Podrobnosti k operátoru SDO_NN najdete v dokumentaci Oracle kapitola 19 Spatial Operators. V příkazu pro vyhledání tří nejblíže bydlících zákazníků u obchodu s id 101, bude použití SDO_NN vypadat takto"

SELECT 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';

Chceme tři nejbližší zákazníky pro obchod se store_id = 101 a ještě setříděné podle vzdálenosti, kterou má zákazník k obchodu.

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;

Chceme vyhledat zákazníky do vzdálenosti 100 mil od obchodu se 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';