|
|
| Line 75: |
Line 75: |
| | | | |
| | [[Points]] | | [[Points]] |
| − |
| |
| − | The CUSTOMERS table has the following definition. (An actual customers table would have more information, but the definition is simplified for this scenario.)
| |
| − |
| |
| − | 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);
| |
| − |
| |
| − | The STORES table has the following definition:
| |
| − |
| |
| − | 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;
| |
| | | | |
| | ==Cvičení x - Prostorové dotazy== | | ==Cvičení x - Prostorové dotazy== |
"Zprovoznění" uživatele HR, vyzkoušení přístupů k databázi - cmd, APEX, SQLDeveloper
Příkazem jazyka DDL pro vytvoření nové tabulky je CREATE TABLE. V příkazu pojmenujeme tabulku (dle významu obashu dat, předchozí analýzy), její sloupce (atributy), ke kterým nadefinujeme datové typy.
V [] jsou uvedeny nepovinné údaje - například [nazev_databaze.]. Za () je uvedena povinná část <nazev sloupce> <datovy typ> s možným opakováním až n-krát [,...n].
V prostředí SQLDevelopera sestavte dotazy, kterými si vyzkoušíte své znalosti. Zadání dotazů je uvedeno na Opakování SQL. Výsledky své práce ukažte pedagogovi.
Princip práce s prostorovými daty v prostředí SŘBD Oracle je postaven na objektovém datovém typu SDO_GEOMETRY. Jeho definice a význam struktury je popsán na stránce SDO_GEOMETRY.
Specifické kódy, které používá Spatial Oracle pro ukládání různých tvarů geometrií vyzkoušíme tvorbou tabulky jednoduchou strukturou a předpřipravenými ukázkami na stránce
Ukázky geometrií. Pro vyjasnění kódů používaných v Spatial Oracle pro různé tvary geometrií použijeme příklad z dokumentace Oracle a prvky zobrazíme v prostředí Prohlížeče.
V prostředí Oracle napište příkaz insert, který do vytvořené tabulky T1 vloží Vaše vlastní geometrie. Fantazii se meze nekladou. Výsledky své práce ukažte pedagogovi.
Výuková podpora pro cvičení z předmětu Objektově orientované technologie vznikla za podpory projektu Inovace bakalářských a magisterských studijních oborů na Hornicko-geologické fakultě VŠB-TUO pod číslem CZ.1.07/2.2.00/28.0308. Tento projekt je realizován za spoluúčasti EU.
