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

Chceme vyhledat tři nejbližší zákazníky pro obchod 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_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';