执行基于位置的分析

http://www.oracle.com/technology/global/cn/obe/10gr2_db_single/datamgmt/spatial/spatial_otn.htm?_template=/ocom/print
执行基于位置的分析

目的

本教程介绍了如何对当前和建议的数据进行基于位置的分析。

所需时间

大约 45 分钟。

主题

本教程包括下列主题：

概述
情景
前提条件
加载新客户及其位置

在几何列上创建空间索引

执行基于位置的查询
创建并使用基于函数的索引

使用 Workspace Manager 分析当前和建议的位置数据

总结
查看屏幕截图

将鼠标置于此图标上以加载和查看本教程的所有屏幕截图。（警告：因为此操作会同时加载所有屏幕截图，所以网速较慢时，响应时间可能会比较长。）

注意：此外，您还可以在下列步骤中将鼠标放在每个单独的图标上，从而仅加载和查看与该步骤相关的屏幕截图。可以通过单击各个屏幕截图来将其隐藏。

概述

Oracle Locator 和 Oracle Workspace Manager 是 Oracle 数据库 10g 标准版和企业版的功能。Oracle Locator 提供了一组集成的功能和过程，可以使用标准 SQL 在 Oracle 数据库中高效地存储、管理、查询和分析空间数据。Oracle Workspace Manager 允许您在同一数据库中对当前数据、建议的数据以及历史数据进行管理。

Oracle Spatial 是 Oracle 数据库 10g 企业版的一个选件，它是 Oracle Locator 的补充，可以提供更多高端空间功能，包括：缓冲区生成、空间聚合、面积计算等功能；线性参照；坐标系转换；拓扑数据模型；以及对地理参照栅格数据的支持。

返回主题列表

案例

MyCompany 拥有几个主要仓库。它需要找到靠近给定仓库的客户，告知他们新的促销息。为了确定客户的位置并执行基于位置的分析，MyCompany 必须存储仓库和客户的位置数据。

本教程将使用 OE 模式中的 CUSTOMERS 和 WAREHOUSES 表。

CUSTOMERS 表具有以下字段：

列 数据类型
CUSTOMER_ID NUMBER(6)
CUST_FIRST_NAME VARCHAR2(20)
CUST_LAST_NAME VARCHAR2(20)
CUST_ADDRESS CUST_ADDRESS_TYP
PHONE_NUMBERS PHONE_LIST_TYP
NLS_LANGAUGE VARCHAR2(3)
NLS_TERRITORY VARCHAR2(30)
CREDIT_LIMIT NUMBER(9.2)
CUST_EMAIL VARCHAR2(20)
ACCOUNT_MGR_ID NUMBER(6)
CUST_GEO_LOCATION MDSYS.SDO_GEOMETRY
WAREHOUSES 表具有以下字段：

列 数据类型
WAREHOUSE_ID NUMBER(3)
WAREHOUSE_SPEC SYS.XMLTYPE
WAREHOUSE_NAME VARCHAR2(35)
LOCATION_ID NUMBER(4)
WH_GEO_LOCATION MDSYS.SDO_GEOMETRY
Oracle 数据类型

Oracle 数据类型包括：

数字 (NUMBER)
字符 (VARCHAR2)
日期 (DATE)
空间数据 (MDSYS.SDO_GEOMETRY)。可以将位置存储为表的 SDO_GEOMETRY 列中的一个点。客户的位置与地球表面的经度值和纬度值相关联，例如，-63.13631，52.485426。
返回主题列表

前提条件

开始本教程之前，您应该：

1.
完成了教程在 Windows 上安装 Oracle 数据库 10g。

2.
下载 spatial.zip 文件并将其解压缩到您的工作目录 (c:\wkdir) 中。

返回主题列表

加载新客户及其位置

在本教程中，假设您已经加载了 Order Entry (OE) 模式 — 该模式包含 CUSTOMERS 和 WAREHOUSES 表。执行下列任务：

加载位置数据
将新客户及其位置添加到 CUSTOMERS 表
将元数据添加到 USER_SDO_GEOM_METADATA 视图
返回主题列表

加载位置数据

Order Entry 模式中的几个客户和仓库的位置值均为 NULL。要为这些客户和仓库提供位置，执行以下命令：

cd \wkdir
sqlplus oe/oe
@loc_updates


返回主题

将新客户及其位置添加到 CUSTOMERS 表

使用事务插入将新客户及其位置添加到 CUSTOMERS 表。可以将客户的位置存储为表的 SDO_GEOMETRY 列中的一个点。客户的位置与地球表面的经度值和纬度值相关联（例如，-63.136，52.4854）。Oracle Locator 和 Oracle Spatial 要求您将经度值置于纬度值之前。在下面的 INSERT 语句中，将使用 SDO_GEOMETRY 构造函数插入点位置。

要将新客户及其位置添加到 CUSTOMERS 表，执行以下步骤：

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@insert_customers
该脚本包含以下语句：

REM script name: insert_customers.sql
REM Inserts values for CUSTOMERS table in the OE schema

REM
REM CUSTOMERS
REM


DELETE FROM customers WHERE
           customer_id = 1001;


INSERT INTO customers VALUES
   (1001,'Dennis','Green',
   cust_address_typ('1 Oracle Drive','03062','Nashua','NH','US'),
   PHONE_LIST_TYP('[phone]'),
   'us','AMERICA','100','[e-mail]',
   149,
   MDSYS.SDO_GEOMETRY(2001, 8307,
   MDSYS.SDO_POINT_TYPE (-63.13631, 52.485424,NULL),NULL,NULL),
   '01-JAN-60','married', 'M', '110,000 - 129,999');
       
DELETE FROM customers WHERE
           customer_id = 1002;
          
INSERT INTO customers VALUES
   (1002,'John','Smith',
   cust_address_typ('1910 Oracle Way','20190','Reston','VA','US'),
   PHONE_LIST_TYP('[phone]'),
   'us','AMERICA','100','[e-mail]',
   149,
   MDSYS.SDO_GEOMETRY(2001, 8307,
   MDSYS.SDO_POINT_TYPE(-70.120133, 44.795766,NULL),NULL,NULL),
   '02-MAY-70', 'single', 'M', '70,000 - 89,999');


commit;




SDO_GEOMETRY 构造函数说明

以下是本练习中要填充的 SDO_GEOMETRY 构造函数的简要说明：

MDSYS.SDO_GEOMETRY (2001,8307,
MDSYS.SDO_POINT_TYPE(-63.13631,52.485424,NULL),NULL,NULL)

该语法中各元素的含义分别如下：

2001 这是 SDO_GTYPE 属性，在存储二维单点（如客户位置）时将其设为 2001。
8307 这是空间参照系 ID (SRID)：Oracle 词典表 (MDSYS.CS_SRS) 的外键，它包含了所有受支持的坐标系。将客户的位置与坐标系相关联至关重要。本例中，8307 对应于“经度/纬度 (WGS 84)”。
MDSYS.SDO_POINT_TYPE 这是在 SDO_GEOMETRY 构造函数内存储经度值和纬度值的地方。请注意，您还可以存储第三个值，但是对于这些教程而言，所有客户数据都是二维的。
NULL, NULL 最后两个空值超出了本教程的讨论范围。无需了解 SDO_GEOMETRY 构造函数的最后两个字段，您就可以构造基于位置的强大查询了。有关 SDO_GEOMETRY 对象中所有字段的详细信息，请参考 Oracle Spatial 用户指南和参考。现在，应将最后这两个字段都设为 NULL。
返回主题

将元数据添加到 USER_SDO_GEOM_METADATA 视图

在创建空间索引前，必须将 CUSTOMERS 和 WARESHOUSES 表的元数据添加到 USER_SDO_GEOM_METADATA 视图。

注意：

为每个 SDO_GEOMETRY 列添加一行
CUSTOMERS 的 SDO_GEOMETRY 列是 cust_geo_location。
WAREHOUSES 的 SDO_GEOMETRY 列是 wh_geo_location。
要为客户和仓库添加元数据，执行以下步骤：

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@add_metadata

该脚本包含以下语句：

REM
REM USER_SDO_GEOM_METADATA, CUSTOMERS and WAREHOUSES
REM
-- inserting data into the user_sdo_geom_metadata view
DELETE FROM USER_SDO_GEOM_METADATA
   WHERE TABLE_NAME = 'WAREHOUSES' AND COLUMN_NAME = 'WH_GEO_LOCATION' ;
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
   VALUES ('WAREHOUSES', 'WH_GEO_LOCATION',
   MDSYS.SDO_DIM_ARRAY
   (MDSYS.SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.005),
   MDSYS.SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.005)
   ),
   8307);
   COMMIT;
DELETE FROM USER_SDO_GEOM_METADATA
   WHERE TABLE_NAME = 'CUSTOMERS' AND COLUMN_NAME = 'CUST_GEO_LOCATION' ;
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
   VALUES ('CUSTOMERS', 'CUST_GEO_LOCATION',
   MDSYS.SDO_DIM_ARRAY
   (MDSYS.SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.005),
   MDSYS.SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.005)
   ),
   8307);

COMMIT;


以下是所插入信息的说明：

TABLE_NAME 这是包含空间数据的表的名称。
COLUMN_NAME 这是存储空间数据的 SDO_GEOMETRY 列的名称。
MDSYS.SDO_DIM_ARRAY 这是保存 MDSYS.SDO_DIM_ELEMENT 对象的构造函数，该函数依次将空间数据的范围存储在每个维中 (-180.0, 180.0)，公差值为 (0.005)。这个公差是 Oracle Spatial 使用的舍入误差值，经度和纬度数据以米为单位。在本例中，该公差值为 5 mm。
8307 这是空间参照系 id (SRID)：Oracle 典表 (MDSYS.CS_SRS) 的外键，它包含了所有受支持的坐标系。将客户的位置与坐标系相关联至关重要。本例中，8307 对应于“经度/纬度 (WGS 84)”。
返回主题

在几何列上创建空间索引

现在，可以为 CUSTOMERS 和 WAREHOUSES 创建空间索引了：

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@create_indexes

该脚本包含以下语句：

-- creating spatial indexes
-- **NOTE** storage parameters should be modified for large tables
-- if the tablespace is not locally managed.
DROP INDEX warehouses_sidx;
CREATE INDEX warehouses_sidx ON warehouses(WH_GEO_LOCATION)
           indextype is mdsys.spatial_index;
DROP INDEX customers_sidx;
CREATE INDEX customers_sidx ON customers(CUST_GEO_LOCATION)
           indextype is mdsys.spatial_index;






LAYER_GTYPE 该参数既充当约束又充当优化程序的提示。如果使用参数 LAYER_GTYPE =POINT，则进行检查以确保所有几何对象都是点，还要检查参数以确保对点数据进行优化处理。customers 和 warehouses 都只包含点几何对象。
返回主题列表

执行基于位置的查询

您将了解如何执行以下类型的基于位置的查询：

使用空间索引查找距离某仓库最近的五个邻居（无附加约束）
使用空间索引在某个位置上查找五个距离最近的邻居（有附加约束）
使用空间索引标识距另一位置指定距的位置集
返回主题列表

使用空间索引查找距离某仓库最近的五个邻居（无附加约束）

查询 1：查找距离 ID 为 2 的仓库最近的五个客户。

执行以下步骤：

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@query1
该脚本包含以下语句：

-- Finds the 5 closest customers to warehouse_id = 2
SELECT /*+ordered*/
   c.customer_id,
   c.cust_first_name,
   c.cust_last_name
FROM warehouses w,
   customers c
WHERE w.warehouse_id = 2
AND sdo_nn (c.cust_geo_location, w.wh_geo_location, 'sdo_num_res=5') = 'TRUE';




以下是对 select 参数的说明：

/*+ordered*/ 提示是优化程序的提示，它可确保首先搜索 WAREHOUSES 表。
SDO_NN 操作符从 CUSTOMERS 表返回距离仓库 2 最近的客户的 SDO_NUM_RES 值。SDO_NN 的第一个参数（上例中的 c.cust_geo_location）是要搜索的列。SDO_NN 的第二个参数（上例中的 w.wh_geo_location）是所要查找最近邻居的位置。不应该对返回结果的顺序进行假设。例如，返回的第一行不能确保是距离仓库 2 最近的客户。如果两个或多个客户距离仓库的距离相等，则可能在对 SDO_NN 的后续调用中返回其中一个。
在使用 SDO_NUM_RES 参数时，WHERE 子句中没有使用其他约束。SDO_NUM_RES 只考虑近似值。例如，如果您希望找出位于纽约的五个最近客户，而其中四个客户位于新泽西，因而将某个条件添加到 WHERE 子句，那么上述查询将返回一行。该行为特定于 SDO_NUM_RES 参数，而其结果可能不是您想要的。在查询 3 中，您将了解到如何查找位于纽约的 5 个最近的客户。
查询 2：查找距离仓库 2 最近的 5 个客户，并按距离顺序显示结果

要返回 5 个最近客户的实际距离，您可以使用 SDO_NN_DISTANCE 操作符。执行以下步骤：

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@query2
该脚本包含以下语句：

-- Finds the 5 closest customers to warehouse_id = 2
-- and orders the results by distance
SELECT /*+ordered*/
   c.customer_id,
   c.cust_first_name,
   c.cust_last_name,
   sdo_nn_distance (1) distance
FROM warehouses w,
   customers c
WHERE w.warehouse_id = 2
AND sdo_nn
(c.cust_geo_location, w.wh_geo_location, 'sdo_num_res=5', 1) = 'TRUE'
ORDER BY distance;




以下是对 select 参数的说明：

SDO_NN_DISTANCE 操作符是 SDO_NN 操作符的辅助操作符；它只能在 SDO_NN 操作符内使用。该操作符的参数是一个与被指定为 SDO_NN 最后一个参数的数字相匹配的数字；在本例中为 1。这个参数没有隐含意义，它只是一个标记。如果指定了 SDO_NN_DISTANCE()，则可以按距离对结果进行排序，并确保返回的第一行是距离仓库最近的客户。如果要查询的数据是以经度和纬度形式存储的，则 SDO_NN_DISTANCE 的默认单位是米。
SDO_NN 操作符还带有 UNIT 参数，它决定 SDO_NN_DISTANCE 返回的度量单位。但本例中并不使用该参数。
ORDER BY DISTANCE 子句可确保按顺序返回距离 — 最短的距离位于最前面。
返回主题

使用空间索引在某个位置上查找五个距离最近的邻居（有附加约束）

查询 3：查找 5 个居住在纽约州且距离仓库 3 最近的客户，并以英里为单位按距离顺序返回结果

执行以下步骤：

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@query3
该脚本包含以下语句：

REM Finds the 5 closest customers to warehouse_id = 3
REM who reside in NY state, and return the distance in miles,
REM and order the results by distance
REM
REM CUSTOMERS and WAREHOUSES
REM


set lines 132
set pages 1000


SELECT /*+ordered*/
   c.customer_id,
   c.cust_address.state_province state,
   sdo_nn_distance(1) distance_in_miles
FROM warehouses w,
   customers c
WHERE w.warehouse_id = 3
AND sdo_nn (c.cust_geo_location, w.wh_geo_location,
   'sdo_batch_size =5 unit=mile', 1) = 'TRUE'
AND c.cust_address.state_province = 'NY'
AND rownum < 6
ORDER BY distance_in_miles;




以下对 select 参数的说明：

SDO_BATCH_SIZE 是一个可调整的参数，它可以影响查询的性能。SDO_NN 同时在内部计算距离的数值。最初返回的一批行可能无法满足 WHERE 子句中的约束，因此继续返回由 SDO_BATCH_SIZE 指定的行数，直至满足 WHERE 子句中的所有约束。您应当选择一个 SDO_BATCH_SIZE，以使最初返回的行数满足 WHERE 子句中的约束。
SDO_NN 操作符中使用的 UNIT 参数指定了 SDO_NN_DISTANCE 参数的度量单位。默认单位是与数据相关联的度量单位。对于经度和纬度数据，默认单位是米。
c.cust_address.state_province = 'NY' 和 rownum < 6 是 WHERE 子句中的附加约束rownum < 6 子句对于将返回的结果数限制为少于 6 必不可少。
ORDER BY DISTANCE_IN_MILES 子句可确保按顺序返回距离，最短的距离位于最前面，其度量单位是英里。
返回主题

使用空间索引标识距另一位置指定距离的位置集

查询 4：查找距离仓库 3 一百英里以内的所有客户

执行以下步骤：

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@query4
该脚本包含以下语句：

REM Finds all the customers within 100 miles of warehouse_id=3
REM
REM CUSTOMERS and WAREHOUSES
REM
set lines 132
set pages 1000
-- To find all the customers within 100 miles of warehouse_id = 3
SELECT /*+ordered*/
   c.customer_id,
   c.cust_address.state_province state
FROM warehouses w,
   customers c
WHERE w.warehouse_id = 3
AND sdo_within_distance (c.cust_geo_location,
   w.wh_geo_location,
   'distance = 100 unit=MILE') = 'TRUE';




以下是对 select 参数的说明：

SDO_WITHIN_DISTANCE 操作符从 customers 表返回距离仓库 3 一百英里以内的客户。SDO_WITHIN_DISTANCE 的第一个参数（上例中的 c.cust_geo_location）是要搜索的列。SDO_WITHIN_DISTANCE 的第二个参数（上例中的 w.wh_geo_location）是要确定距离远近的位置。不应该对返回结果的顺序进行假设。例如，返回的第一行并不保证就是距离仓库 3 最近的客户。
SDO_WITHIN_DISTANCE 操作符中使用的 DISTANCE 参数指定距离值；在本例，该值为 100。
SDO_WITHIN_DISTANCE 操作符中的 UNIT 参数指定 DISTANCE 参数的度量单位。默认单位是与数据相关联的度量单位。对于经度和纬度数据而言，默认单位是米；在本例中为英里。
查询 5：查找距离仓库 3 一百英里以内的所有客户，并以英里为单位按距离顺序返回结果

执行以下步骤：

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@query5
该脚本包含以下语句：

-- To find all the customers within 100 miles of warehouse_id = 3
-- and return the distance in miles, and order the results by distance
SELECT /*+ordered*/
   c.customer_id,
   c.cust_address.state_province state,
   sdo_geom.sdo_distance (c.cust_geo_location,
   w.wh_geo_location,
   .005, 'unit=MILE') distance_in_miles
FROM warehouses w,
   customers c
WHERE w.warehouse_id = 3
AND sdo_within_distance (c.cust_geo_location,
   w.wh_geo_location,
   'distance = 100 unit=MILE') = 'TRUE'
ORDER BY distance_in_miles;




以下是对 select 参数的说明：

SDO_GEOM.SDO_DISTANCE 函数计算客户所处位置与仓库 3 之间的精确距离。SDO_GEOM.SDO_DISTANCE 的第一个参数（上例中的 c.cust_geo_location）包含了要计算与仓库 3 之间距离的客户所处的位置。SDO_WITHIN_DISTANCE 的第二个参数（上例中的 w.wh_geo_location）是仓库 3 的位置，函数将要计算其与客户所处位置之间的距离。
SDO_GEOM.SDO_DISTANCE 的第三个参数 (0.005) 是公差值。该公差值是由 Oracle Spatial 使用的舍入误差值。对于经度和纬度数据，该公差值以米为单位。在本例中，该公差值为 5 mm。
SDO_GEOM.SDO_DISTANCE 参数中使用的 UNIT 参数指定由 SDO_GEOM.SDO_DISTANCE 函数所计算距离的度量单位。默认单位是与数据相关联的度量单位。对于经度和纬度数据，默认单位是米。在本例中，单位是英里。
ORDER BY DISTANCE_IN_MILES 子句可确保按顺序返回距离，最短的距离位于最前面，其度量单位是英里。
返回主题

创建并使用基于函数的索引

基于函数的索引使得可以基于返回 SDO_GEOMETRY 对象的函数结果构建索引。这种强大的机制可以实现基于位置的功能，而无需表中具有 SDO_GEOMETRY 列。基于函数的索引主要用于具有存储经度和纬度数据的列的表上。

如何创建并使用基于函数的索引

要创建并使用基于函数的索引，执行以下步骤：


设置：更新 WAREHOUSES 表

1.
创建返回 SDO_GEOMETRY 对象的函数

2.
将元数据添加到函数的 USER_SDO_GEOM_METADATA 视图

3. 创建基于函数的空间索引
4. 使用基于函数的空间索引
返回主题列表

设置：更新 WAREHOUSES 表

在继续之前，您需要运行 wh_updates.sql 脚本。该脚本会将 wh_longitude 和 wh_latitude 列添加到 WAREHOUSES 表。wh_longitude 和 wh_latitude 都是 NUMBER 类型的列，我们将使用基于函数的索引按空间对它们进行索引。

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@wh_updates
该脚本包含以下语句：

REM script name: wh_updates.sql
REM Function-based indexes
REM adding wh_longitude/wh_latitude columns and populating them
REM
REM WAREHOUSES
REM


ALTER TABLE warehouses
            DROP COLUMN wh_longitude;
ALTER TABLE warehouses
            DROP COLUMN wh_latitude;
ALTER TABLE warehouses
            ADD (wh_longitude number, wh_latitude number);
UPDATE warehouses
            SET wh_longitude = -103.00195, wh_latitude = 36.500374
            WHERE warehouse_id = 1;
UPDATE warehouses
            SET wh_longitude = -124.21014, wh_latitude = 41.998016
            WHERE warehouse_id = 2;
UPDATE warehouses
            SET wh_longitude = -74.695305, wh_latitude = 41.35733
            WHERE warehouse_id = 3;
UPDATE warehouses
            SET wh_longitude = -123.61526, wh_latitude = 46.257458
            WHERE warehouse_id = 4;
UPDATE warehouses
            SET wh_longitude = -79.4167, wh_latitude = 43.6667
            WHERE warehouse_id = 5;
UPDATE warehouses
            SET wh_longitude = 151.2000, wh_latitude = -33.8833
            WHERE warehouse_id = 6;
UPDATE warehouses
            SET wh_longitude = -106.0500, wh_latitude = 24.3833
            WHERE warehouse_id = 7;
UPDATE warehouses
            SET wh_longitude = 123.8839, wh_latitude = 39.8667
            WHERE warehouse_id = 8;
UPDATE warehouses
            SET wh_longitude = 72.8333, wh_latitude = 18.9667
            WHERE warehouse_id = 9;
COMMIT;




返回主题

1. 创建返回 SDO_GEOMETRY 对象的函数

要创建返回 SDO_GEOMETRY 对象的函数，执行以下步骤：

1.
在创建函数之前，需要确保 OE 具有进行此项工作的适当权限。同时，授予 OE 权限，允许 Oracle 优化程序使用基于函数的索引。指定以下命令：

connect system/oracle
grant create procedure to oe;  
grant query rewrite to oe;        




2.
同样，以 oe/oe 连接实例。





3.
在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@create_function

该脚本包含以下语句：

-- creating a function that returns a SDO_GEOMETRY object
CREATE OR REPLACE FUNCTION get_geom (
   longitude IN NUMBER,
   latitude IN NUMBER)
RETURN mdsys.sdo_geometry
   DETERMINISTIC IS
BEGIN
   RETURN mdsys.sdo_geometry (2001, 8307,
     mdsys.sdo_point_type (longitude,latitude, NULL),
     NULL, NULL);
END;
/





所执行 SQL 查询的说明

必须将该函数声明为 DETERMINISTIC，否则优化程序可能无法使用最优的计划。对于返回对象的函数来说都是如此。GET_GEOM 函数接受两个 NUMBER 型参数，返回一个 MDSYS.SDO_GEOMETRY 对象

返回主题

2. 将元数据添加到函数的 user_sdo_geom_metadata 视图

在创建空间索引前，必须将元数据添加到 user_sdo_geom_metadata 视图。为返回 SDO_GEOMETRY 对象的函数增加了一行。

要将元数据添加到返回 SDO_GEOMETRY 对象的函数，执行以下步骤：

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@add_metadata_fi

该脚本包含以下语句：

-- adding metadata into USER_SDO_GEOM_METADATA view
DELETE FROM USER_SDO_GEOM_METADATA
WHERE TABLE_NAME = 'WAREHOUSES'
AND COLUMN_NAME = 'OE.GET_GEOM(WH_LONGITUDE,WH_LATITUDE)' ;
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES ('WAREHOUSES', 'OE.GET_GEOM(WH_LONGITUDE,WH_LATITUDE)',
   MDSYS.SDO_DIM_ARRAY
   (MDSYS.SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.005),
   MDSYS.SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.005)
   ),
   8307);

COMMIT;




所执行 SQL 查询的说明

将 GET_GEOM 函数加载到 user_sdo_geom_metadata 的 COLUMN_NAME 列中。将函数的所有者 (OE) 包含在函数调用中。GET_GEOM 函数的参数（wh_longitude、wh_latitude）就是 WAREHOUSES 表的经度列和纬度列。

返回主题

3. 创建基于函数的空间索引

要创建基于函数的索引，用户必须具有查询重写权限。因为您已经将这些权限授予了 OE 用户，所以可以继续该步骤来创建索引。要创建基于函数的索引，执行以下步骤：

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@create_function_index

该脚本包含以下语句：

-- creating the function-based spatial index
DROP INDEX warehouses_sidx;
CREATE INDEX warehouses_sidx on warehouses
  (get_geom(WH_LONGITUDE,WH_LATITUDE))
  INDEXTYPE IS mdsys.spatial_index
  PARAMETERS('layer_gtype=point initial=1K next=1K pctincrease=0');




所执行 SQL 查询的说明

GET_GEOM 函数以 CREATE_INDEX 语句的 COLUMN_NAME 参数的形式调用。GET_GEOM 函数的参数（wh_longitude、wh_latitude）就是 WAREHOUSES 表的经度列和纬度列。

返回主题

4. 使用基于函数的空间索引

执行以下步骤：

1.
要使用基于函数的索引执行查询，需要具有会话权限。必须针对该会话将 QUERY_REWRITE_ENABLED 设为 True，将 QUERY_REWRITE_INTEGRITY 设为 Trusted。现在返回查询 3。但是，此时应根据 SDO_NN 操作符的第二个参数而不是 SDO_GEOMETRY 列使用基于函数的空间查询。

?

2.
在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@query3_wfi
该脚本包含以下语句：

REM Function-based index
REM
REM Uses the function-based index to find the 5 closest customers to warehouse_id = 3
REM who reside in NY state, and return the distance in miles,
REM and order the results by distance
REM
REM CUSTOMERS and WAREHOUSES
REM
set lines 132
            set pages 1000
-- setting session privileges
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;


SELECT /*+ordered*/
   c.customer_id,
   c.cust_address.state_province state,
   sdo_nn_distance(1) distance_in_miles
FROM warehouses w,
   customers c
WHERE w.warehouse_id = 3
AND sdo_nn (c.cust_geo_location, get_geom(wh_longitude,wh_latitude),
   'sdo_batch_size =5 unit=mile', 1) = 'TRUE'
AND c.cust_address.state_province = 'NY'
AND rownum < 6
ORDER BY distance_in_miles;



所执行 SQL 查询的说明

GET_GEOM 函数以 SDO_NN 操作符的第二个参数的形式调用。GET_GEOM 函数的参数（wh_longitude、wh_latitude）就是 WAREHOUSES 表的经度列和纬度列。

返回主题

使用 Workspace Manager 分析当前和建议的位置数据

MyCompany 计划再建造一个仓库，从而为客户提供更好的服务。目前有两个地点可供选择。MyCompany 希望使用 SQL 应用程序来对这些候选地点进行分析。为此，必须将两个候选地点的数据输入生产仓库表。然而，将候选地点数据隔离以使其不会影响到非地点选择小组成员员工的工作，这一点非常重要。隔离候选地点数据还将使两个地点选择小组能够同时展开工作。

Oracle Workspace Manager 能够将当前行、建议行以及历史行保存在同一个表中。它不要求更改应用程序 SQL (DML)。通过允许生产用户访问当前数据，同时其他用户进入工作区以创建并访问建议数据值和历史数据值，员工之间的协同工作性得以提升。工作区中进行的更改可以合并至当前数据中形成一个整体。Workspace Manager 使用户能够在工作区中做出更改的情况下对数据库有一个全面的了解。通过它，开发人员不用再编写自定义代码，DBA 也不用再复制和同步多个数据库副本或添加应用程序特定的元数据来跟踪行版本了。

在本部分中，您将执行以下步骤：

1.
准备进行仓库地点分析

2.
在工作区中添加两个候选仓库位置

3.
确定哪个位置距离最大客户群最近

4.
将选定的位置数据提供给其他用户并进行清理

返回主题列表

1. 准备进行仓库地点分析

当表支版本控制时，表中的所有行都可支持多个版本的数据。版本控制的行作为原始行存储在同一表格中。工作区是一种虚拟环境，由一个或多个用户共享来对数据库中的数据进行更改。在该数据库中有一个工作区层次结构。默认情况下，在创建工作区时，从最上层始终称为“LIVE”的数据库工作区进行创建。

首先，使仓库表支持版本控制，并在 LIVE 之外创建两个工作区（SITE1 和 SITE2），每个选择小组使用一个。



执行以下步骤：

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@init_workspaces
该脚本包含以下语句：

ALTER TABLE Warehouses MODIFY (wh_longitude not null, wh_latitude not null);


exec dbms_wm.EnableVersioning('Inventories') ;
exec dbms_wm.EnableVersioning('Warehouses') ;


DELETE FROM warehouses
WHERE warehouse_id = 10 ;


COMMIT;


exec dbms_wm.CreateWorkspace('SITE1') ;
exec dbms_wm.CreateWorkspace('SITE2') ;




返回主题

2. 在工作区中添加两个候选仓库位置

用户须进入工作区才能对数据进行更改。工作区按逻辑将一个或多个支持版本控制的表中的行版本集合进行分组，并在将其显式地合并至生产数据前隔离这些版本。

转至工作区 SITE1，添加第一个建议的仓库位置。转至工作区 SITE2，添加第二个建议的仓库位置，其属性与 SITE1 的相同，只是位置不同。转至工作区 LIVE 和“select all from wrehouse”表，候选地点没有显示。SITE1 中的用户无法看到 SITE2 中所做的更改，反之亦然。执行以下步骤：

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@add_workspace_data
该脚本包含以下语句：

exec dbms_wm.GotoWorkspace('SITE1') ;


INSERT INTO warehouses values
   (10, null, 'Munich', 2700,
    mdsys.sdo_geometry
   (2001,8307, mdsys.sdo_point_type (11.5424,48.2231, null),null, null),
    11.5424, 48.2231) ;

COMMIT;


exec dbms_wm.gotoworkspace('SITE2') ;


INSERT INTO warehouses values
   (10, null, 'Roma', 1000,
    mdsys.sdo_geometry (2001,8307,
    mdsys.sdo_point_type (12.4833,41.9601, null),null, null),
   12.4833, 41.9601) ;

COMMIT;


exec dbms_wm.GotoWorkspace('LIVE') ;


SELECT warehouse_id, warehouse_name, location_id
FROM warehouses
WHERE warehouse_id=10 ;




返回主题

3. 确定哪个位置距离最大客户群最近

数据库用户不会看到版本控制基础架构，并且执行选择、插入、修改以及删除数据等操作的应用程序 SQL 数据处理语句 (DML) 继续在支持版本控制的表中正常运行。工作区中的用户可自动查看感兴趣的正确版本的记录，即用户无需跟踪版本链并指定所感兴趣的版本。

依次进入每个工作区并使用现有的应用程序 SQL 执行基于位置的查询，以报告每个地点在 100 英里以内具多少个客户。最终结果是，在 100 英里内，SITE1 有 3 个客户，而 SITE2 有 25 个客户。执行以下步骤

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@goto_workspace_and_query
该脚本包含以下语句：

exec dbms_wm.GotoWorkspace('SITE1') ;


SELECT /*+ordered*/
   c.customer_id, c.cust_address.city,
   sdo_geom.sdo_distance (c.cust_geo_location,
   w.wh_geo_location,
   .005, 'unit=MILE') distance_in_miles
FROM warehouses w, customers c
WHERE w.warehouse_id = 10 and
   sdo_within_distance (c.cust_geo_location,
   w.wh_geo_location,
   'distance = 100 unit=MILE') = 'TRUE'
ORDER BY distance_in_miles;exec dbms_wm.gotoworkspace('SITE2') ;


SELECT /*+ordered*/
   c.customer_id, c.cust_address.city,
   sdo_geom.sdo_distance (c.cust_geo_location,
   w.wh_geo_location,
   .005, 'unit=MILE') distance_in_miles
FROM warehouses w, customers c
WHERE w.warehouse_id = 10 and
   sdo_within_distance (c.cust_geo_location,
   w.wh_geo_location,
   'distance = 100 unit=MILE') = 'TRUE'
ORDER BY distance_in_miles;


返回主题

4. 将选定的位置数据提供给其他用户并进行清理

工作区可以被合并、刷新或回滚。合并工作区需要将子工作区中所做的更改应用到其父工作区。刷新工作区需要将父工作区中的更改应用到子工作区。回滚工作区需要删除工作区中所做的更改。用户可以删除所有自工作区创建以来所做的更改，或只删除保存点之后的更改。如果某行在子工作区和父工作区中均进行了更改，则会导致数据冲突。冲突可随时得到检查和解决。请求合并或刷新操作时，会自动检测冲突。

因为 SITE2 在 100 英里以内有更多的客户，所以将其合并至工作区 LIVE 以使所有用户都可访问 SITE2 的数据。转至工作区 LIVE 和“select all from warehouse”表，SITE2 的数据已显示在其中。回滚不需要的 SITE1 数据将其删除，删除该工作区并禁用该工作区表上的版本控制。执行以下步骤：

1.

在登录 OE 模式的 SQL*Plus 会话中，运行以下脚本：

@merge_workspace_cleanup
该脚本包含以下语句：

exec dbms_wm.RollbackWorkspace('SITE1') ;
exec dbms_wm.RemoveWorkspace('SITE1') ;


exec dbms_wm.GotoWorkspace('LIVE') ;
exec dbms_wm.MergeWorkspace('SITE2', remove_workspace=>TRUE) ;

SELECT warehouse_id, warehouse_name, location_id
FROM warehouses
WHERE warehouse_id=10 ;

exec dbms_wm.DisableVersioning('warehouses') ;
exec dbms_wm.DisableVersioning('inventories') ;

ALTER TABLE Warehouses
MODIFY (wh_longitude null, wh_latitude null);




返回主题

总结

在本教程中，您学习了如何：

加载新客户及其位置

在几何列上创建空间索引

执行基于位置的查询
创建并使用基于函数的索引

使用 Workspace Manager 分析当前和建议的位置数据