Difference between revisions of "Points"
m (→Příprava tabulek CUSTOMERS a STORES) |
m (→Vkládání dat s bodovou geometrií) |
||
| Line 63: | Line 63: | ||
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. | 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. | ||
| − | + | Vkládání dat o zákaznících: | |
| − | INSERT INTO customers VALUES | + | INSERT INTO customers VALUES |
(1001,'Nichols', 'Alexandra', | (1001,'Nichols', 'Alexandra', | ||
'17 Maple Drive', 'Nashua', 'NH','03062', | '17 Maple Drive', 'Nashua', 'NH','03062', | ||
| Line 71: | Line 71: | ||
SDO_POINT_TYPE (-71.48923,42.72347,NULL), NULL, NULL)); | SDO_POINT_TYPE (-71.48923,42.72347,NULL), NULL, NULL)); | ||
| − | INSERT INTO customers VALUES | + | INSERT INTO customers VALUES |
(1002,'Harris', 'Melvin', | (1002,'Harris', 'Melvin', | ||
'5543 Harrison Blvd', 'Reston', 'VA', '20190', | '5543 Harrison Blvd', 'Reston', 'VA', '20190', | ||
| Line 77: | Line 77: | ||
SDO_POINT_TYPE(-70.120133,44.795766,NULL), NULL, NULL)); | SDO_POINT_TYPE(-70.120133,44.795766,NULL), NULL, NULL)); | ||
| − | INSERT INTO customers VALUES | + | INSERT INTO customers VALUES |
(1003,'Chang', 'Marian', | (1003,'Chang', 'Marian', | ||
'294 Main St', 'Concord', 'MA','01742', | '294 Main St', 'Concord', 'MA','01742', | ||
| Line 83: | Line 83: | ||
SDO_POINT_TYPE (-71.351,42.4598,NULL), NULL, NULL)); | SDO_POINT_TYPE (-71.351,42.4598,NULL), NULL, NULL)); | ||
| − | INSERT INTO customers VALUES | + | INSERT INTO customers VALUES |
(1004,'Williams', 'Thomas', | (1004,'Williams', 'Thomas', | ||
'84 Hayward Rd', 'Acton', 'MA','01720', | '84 Hayward Rd', 'Acton', 'MA','01720', | ||
| Line 89: | Line 89: | ||
SDO_POINT_TYPE (-71.4559,42.4748,NULL), NULL, NULL)); | SDO_POINT_TYPE (-71.4559,42.4748,NULL), NULL, NULL)); | ||
| − | INSERT INTO customers VALUES | + | INSERT INTO customers VALUES |
(1005,'Rodriguez', 'Carla', | (1005,'Rodriguez', 'Carla', | ||
'9876 Pine Lane', 'Sudbury', 'MA','01776', | '9876 Pine Lane', 'Sudbury', 'MA','01776', | ||
| Line 95: | Line 95: | ||
SDO_POINT_TYPE (-71.4242,42.3826,NULL), NULL, NULL)); | SDO_POINT_TYPE (-71.4242,42.3826,NULL), NULL, NULL)); | ||
| − | INSERT INTO customers VALUES | + | INSERT INTO customers VALUES |
(1006,'Adnani', 'Ramesh', | (1006,'Adnani', 'Ramesh', | ||
'1357 Appletree Ct', 'Falls Church', 'VA','22042 ', | '1357 Appletree Ct', 'Falls Church', 'VA','22042 ', | ||
| Line 101: | Line 101: | ||
SDO_POINT_TYPE (-77.1745,38.88505,NULL),NULL,NULL)); | SDO_POINT_TYPE (-77.1745,38.88505,NULL),NULL,NULL)); | ||
| − | + | Vkládání dat o obchodech. | |
INSERT INTO stores VALUES | INSERT INTO stores VALUES | ||
Revision as of 13:36, 2 August 2014
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.
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));
-- 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;
