Points
Příklad využívá podklady z Oracle dokumentace zaměřené na práci s modulem Locator.
Contents
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 (WGS 84 coordinate system pro starší verze Oracle 8, 9 or 10gR1, později už EPSG 4326):
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;
Teprve po vytvoření prostorového indexu můžeme využít prostředí GeoRaptoru pro zobrazení geoobjektů. Rozmístění obchodů a lokalizace zákazníků jsou patrny na obrázku Obchody&Zakaznici.
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 - SDO_NN. 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';
Pokud budeme chtít výsledek upravit tak, abychom dostali seznam nejbližších zákazníků setříděný podle vzdálenosti, kterou má zákazník k obchodu, pak lze využít operátoru SDO_NN_DISTANCE a podle jeho výsledků třídít. Podobně jako u operátoru SDO_NN je i tento operátor popsán v dokumentaci kapitola 19 Spatial Operators - SDO_NN_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;
Kdo bydlí nejblíže obchodu 101 lze zjistit příkazem
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=1') = 'TRUE';
Detailnější rozmístění obchodů a zákazníků ukazuje obrázek Lokalizace detailněji.
Dalším operátorem, jehož působení si ukážeme je SDO_WITHIN_DISTANCE (stránka dokumentace Oracle v kapitole 19 Spatial Operators - SDO_WITHIN_DISTANCE). Operátor na základě prostorového indexu identifikuje sadu prostorových objektů, které jsou do určité specifikované vzdálenosti od daného objektu, jako je například oblast zájmu nebo bod zájmu. Formát operátoru:
SDO_WITHIN_DISTANCE (geometry1, aGeom, params);
kde
geometry1 Určuje geometrie sloupce v tabulce. Polohu sady geometrických objektů operátor porovná s požadovanou vzdáleností od daného objektu (aGeom).
Sloupec musí být prostorově indexován. Datový typ je SDO_GEOMETRY.
aGeom Určuje objekt, který má být posouzen na vzdálenost od geometrických objektů v geometry1.
Zadává se buď geometrie z tabulky (pomocí proměnné vazby) nebo přechodná instance geometrie (pomocí SDO_GEOMETRY konstruktoru).
Datový typ je SDO_GEOMETRY.
params Udává v uvozovkách zadané řetězce obsahující jeden nebo více klíčových slov (s hodnotami), které určují chování operátoru.
Mohou to být položky (distance, max_resolution, min_resolution, querytype, a unit)
(vzdálenost, max_rozliseni, min_rozliseni, typDotazu, a jednotka) a jsou chápána jako potenciální klíčová slova pro parametr params.
Datový typ těchto hodnot je VARCHAR2.
Parametry operandu params:
distance Určuje hodnotu vzdálenosti. Je-li souřadný systém spojený s geometrií, vzdálenost používá jednotky spojené se souřadnicovým systémem.
Toto je povinný parametr. Datový typ je NUMBER.
max_resolution Zahrnuje pouze geometrie, u nichž alespoň u jedné strany z geometrie MBR (minimálního ohraničujícícho obdélníka)
je vzdálenost menší než nebo rovna zadané hodnotě. Například, max_resolution = 10 zahrnuje pouze geometrie,
pro které je šířka nebo výška (nebo oba) z geometrie MBR menší než nebo rovna 10.
min_resolution Zahrnuje pouze takové geometrie, pro které alespoň jedna strana geometrie MBR je rovna nebo větší než zadaná hodnota.
Například, min_resolution = 10 zahrnuje pouze geometrie, pro které je šířka nebo výška (nebo oba) z geometrie MBR nejméně 10.
querytype Nastavení 'querytype = FILTER' vede k prováděn9 pouze primárního filtru.
Není-li uveden querytype, pak se v operaci provedou oba filtry - jak primární, tak sekundární (výchozí nastavení).
Datový typ je VARCHAR2.
unit Určuje jednotku měření: 'unit= ', kde je hodnota SDO_UNIT převzata z tabulky MDSYS.SDO_DIST_UNITS (například 'unit = KM').
Datový typ je NUMBER. Výchozí jednotka měření je spojena s daty, u geodetických dat je výchozí hodnota metr.
Chceme-li tedy vyhledat zákazníky do vzdálenosti 50 mil od obchodu se store_id = 101, použijeme v podmínce za klíčovým slovem WHERE operátor SDO_WITHIN_DISTANCE s parametrem unit pro změnu jednotek na míle.
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 = 50 unit=MILE') = 'TRUE';
Ve výsledku by to měli být tito 4 zákazníci:
1005 Carla Rodriguez 1004 Thomas Williams 1003 Marian Chang 1001 Alexandra Nichols
