Points

From Wikivyuka
Revision as of 13:19, 2 August 2014 by Dur30 (talk | contribs)

Jump to: navigation, search

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

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á již má strukturu definovanou a je možné ji vytvořit pomocí následujícícho příkazu CREATE: 

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

Data o obchodech jsou definovány a vytvořeny podobně, v tabulce STORES:

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

);

The DIMINFO column is a varying length array of an object type, ordered by dimension, and has one entry for each dimension. The SDO_DIM_ARRAY type is defined as follows:

Create Type SDO_DIM_ARRAY as VARRAY(4) of SDO_DIM_ELEMENT;

The SDO_DIM_ELEMENT type is defined as:

Create Type SDO_DIM_ELEMENT as OBJECT ( 

 SDO_DIMNAME VARCHAR2(64),
 SDO_LB NUMBER,
 SDO_UB NUMBER,
 SDO_TOLERANCE NUMBER);

The SDO_DIM_ARRAY instance is of size n if there are n dimensions. That is, DIMINFO contains 2 SDO_DIM_ELEMENT instances for two-dimensional geometries, 3 instances for three-dimensional geometries, and 4 instances for four-dimensional geometries. Each SDO_DIM_ELEMENT instance in the array must have valid (not null) values for the SDO_LB (lower bound), SDO_UB (upper bound), and SDO_TOLERANCE (tolerance) attributes.

Tolerance reflects the distance that two points can be apart and still be considered the same (for example, to accommodate rounding errors), and thus reflects the precision of the spatial data. The tolerance value must be a positive number greater than zero.

Example 1-3 inserts rows into the USER_SDO_GEOM_METADATA view, with dimensional information for each spatial column. In both cases, the dimensional range is the entire Earth, and the coordinate system is the widely used WGS84 (longitude/latitude) system (spatial reference ID = 8307).

Example 1-3 Updating the Spatial 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);

In Example 1-3, the longitude dimension of -180.0,180.0 and latitude dimension of -90.90 are required for geodetic data using the WGS84 coordinate system. The tolerance value of 0.5 means that any points less than one-half meter apart are considered to be the same point by any location-based operators or functions.

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;
Retrieved from "http://gisak.vsb.cz/wikivyuka/index.php?title=Points&oldid=3111"