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

Teprve nyní můžeme přistoupit k tvorbě prostorového indexu příkazem CREATE INDEX. Název indexu je složen z názvu tabulky, ke které je tvořen a zkratky sidx (spatial index). Bude vytvořen na sloupci s typem SDO_GEOMETRY a je to prostorový index(mdsys.spatial_index).

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

Creating Spatial Indexes

Spatial indexes are required for many queries that use Locator operators, and are important for performance for most spatial queries. Before you use spatial data for analysis or queries, create a spatial index on each spatial column. To create a spatial index, use the CREATE INDEX statement, and specify the INDEXTYPE IS MDSYS.SPATIAL_INDEX clause.

To create a spatial index, the database user must have the CREATE TABLE privilege.

Example 1-4 creates spatial indexes on the CUSTOMERS.CUST_GEO_LOCATION and STORES.STORE_GEO_LOCATION columns.

Example 1-4 Creating the 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;