OBE Home > 10gR2 VMware > Data Management > Performing Location-Based Analysis

Performing Location-Based Analysis

Purpose

This module shows you how to use Oracle Locator or Oracle Spatial, and Oracle Workspace Manager for location-based analysis on current and proposed data.

Time to Complete

Approximately 45 minutes.

Topics

This tutorial covers the following topics:

	Overview
	Scenario
	Loading New Customers and Their Locations
	Creating a Spatial Index on a Geometry Column
	Performing Location-Based Queries
	Creating and Using a Function-Based Index
	Analyzing Current and Proposed Location Data Using Workspace Manager
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so, depending on your Internet connection, may result in a slow response time.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Oracle Locator and Oracle Workspace Manager are features of Oracle10g Database, Standard and Enterprise Editions. Oracle Locator provides an integrated set of functions and procedures to efficiently store, manage, query and analyze spatial data in an Oracle database, using standard SQL. Oracle Workspace Manager allows current, proposed and historical values of the data to be managed in the same database.

Oracle Spatial, an option for Oracle10g Database, Enterprise Edition, augments Oracle Locator with additional high-end spatial functionality including: functions such as buffer generation, spatial aggregates, area calculations, and more; linear referencing; coordinate systems transformations; topology data model; and support for geo-referenced raster data.

Back to Topic List

Scenario

MyCompany has several major warehouses. It needs to locate its customers who are near a given warehouse, to inform them of new advertising promotions. To locate its customers and perform location-based analysis, MyCompany must store location data for both its customers and warehouses.

This module uses the CUSTOMERS and WAREHOUSES tables in the OE schema.

The CUSTOMERS table has the following fields:

The WAREHOUSES table has the following fields:

Oracle's Data Types

Oracle's data types include:

	Numbers (NUMBER)
	Characters (VARCHAR2)
	Dates (DATE)
	Spatial data (MDSYS.SDO_GEOMETRY). A location can be stored as a point in an SDO_GEOMETRY column of a table. The customer's location is associated with longitude and latitude values on the Earth's surface—for example, -63.13631, 52.485426.

Back to Topic List

Loading New Customers and Their Locations

In this module it is assumed that you have already loaded the Order Entry (OE) schema, which contains the CUSTOMERS and WAREHOUSES tables. Perform the following tasks:

	Load the Location Data
	Add New Customers and Their Locations to the CUSTOMERS Table
	Add Meta Data to the USER_SDO_GEOM_METADATA View

Back to Topic List

Load the Location Data

Several customers and warehouses in the Order Entry schema have location values of NULL. To supply locations for those customers and warehouses, perform the following step

sqlplus oe/oe
@loc_updates

Back to Topic

Adding New Customers and Their Locations to the CUSTOMERS Table

A transactional insert is used to add new customers and their locations to the CUSTOMERS table. A customer's location can be stored as a point in an SDO_GEOMETRY column in a table. The customer's location is associated with longitude and latitude values on the Earth's surface (for example, -63.136, 52.4854). Oracle Locator and Oracle Spatial require that you place the longitude value before the latitude value. In the INSERT statement below, an SDO_GEOMETRY constructor is used to insert the point location.

To add a new customer and his or her location to the CUSTOMERS table, perform the following step:

@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('+1 603 897 4104'),
   'us','AMERICA','100','Dennis.Green@Oracle.com',
   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('+1 703 364 4111'),
   'us','AMERICA','100','John.Smith@Oracle.com',
   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;

Description of SDO_GEOMETRY Constructor

Below is a brief description of the SDO_GEOMETRY constructor that is populated in this exercise:

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

The elements of the syntax have the following meanings:

Back to Topic

Adding Meta Data to the USER_SDO_GEOM_METADATA View

Before creating a spatial index, you must add metadata for the CUSTOMERS and WARESHOUSES tables to the USER_SDO_GEOM_METADATA view.

Note:

	Add one row for every SDO_GEOMETRY column
	The SDO_GEOMETRY column for CUSTOMERS is cust_geo_location.
	The SDO_GEOMETRY column for WAREHOUSES is wh_geo_location.

To add metadata for customers and warehouses, perform the following step:

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;

Here is a description of the information that was inserted:

Back to Topic

Creating a Spatial Index on a Geometry Column

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

Back to Topic List

Performing Location-Based Queries

You will learn how to perform the following types of location-based queries:

	Using the Spatial Index to Find the Five Nearest Neighbors to a Warehouse, no Additional Constraints
	Using the Spatial Index to Find the Five Nearest Neighbors in a Location, with Additional Constraints
	Using the Spatial Index to Identify the Set of Locations that are within some Specified Distance of another Location

Back to Topic List

Using the Spatial Index to Find the Five Nearest Neighbors to a Warehouse, No Additional Constraints

Query 1: Find the five customers closest to the warehouse whose warehouse ID is 2.

Perform the following:

@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';

Here is a description of the information that is selected:

	The /*+ordered*/ hint is a hint to the optimizer, which ensures that the WAREHOUSES table is searched first.
	The SDO_NN operator returns the SDO_NUM_RES value of the customers from the CUSTOMERS table who are closest to warehouse 2. The first argument to SDO_NN (c.cust_geo_location in the example above) is the column to search. The second argument to SDO_NN (w.wh_geo_location in the example above) is the location you want to find the neighbors nearest to. No assumptions should be made about the order of the returned results. For example, the first row returned is not guaranteed to be the customer closest to warehouse 2. If two or more customers are an equal distance from the warehouse, then either of the customers may be returned on subsequent calls to SDO_NN.
	When using the SDO_NUM_RES parameter, no other constraints are used in the WHERE clause. SDO_NUM_RES takes only proximity into account. For example, if you added a criterion to the WHERE clause because you wanted the five closest customers that resided in NY, and four of the five closest customers resided in NJ, the query above would return one row. This behavior is specific to the SDO_NUM_RES parameter, and its results may not be what you are looking for. You will learn how to find the five closest customers who reside in NY in the discussion of query 3.

Query 2: Find the five customers closest to warehouse 2 and put the results in order of distance

To return the actual distances for the five closest customers, you can use the SDO_NN_DISTANCE operator. Perform the following:

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

Here is a description of the information that is selected:.

	The SDO_NN_DISTANCE operator is an ancillary operator to the SDO_NN operator; it can only be used within the SDO_NN operator. The argument for this operator is a number that matches the number specified as the last argument of SDO_NN; in this example it is 1. There is no hidden meaning to this argument, it is simply a tag. If SDO_NN_DISTANCE() is specified, you can order the results by distance and guarantee that the first row returned is the closest. If the data you are querying is stored as longitude and latitude, the default unit for SDO_NN_DISTANCE is meters.
	The SDO_NN operator also has a UNIT parameter that determines the unit of measure returned by SDO_NN_DISTANCE. However, it is not used in this example.
	The ORDER BY DISTANCE clause ensures that the distances are returned in order, with the shortest distance first.

Back to Topic

Using the Spatial Index to Find the Five Nearest Neighbors in a Location, with Additional Constraints

Query 3: Find the five customers closest to warehouse 3 who reside in NY state, put the results in order of distance, and give the distance in miles

Perform the following:

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

Here is a description of the information that was selected:

	SDO_BATCH_SIZE is a tunable parameter that may affect your query's performance. SDO_NN internally calculates that number of distances at a time. The initial batch of rows returned may not satisfy the constraints in the WHERE clause, so the number of rows specified by SDO_BATCH_SIZE is continuously returned until all the constraints in the WHERE clause are satisfied. You should choose a SDO_BATCH_SIZE that initially returns the number of rows likely to satisfy the constraints in your WHERE clause.
	The UNIT parameter used within the SDO_NN operator specifies the unit of measure of the SDO_NN_DISTANCE parameter. The default unit is the unit of measure associated with the data. For longitude and latitude data, the default is meters.
	c.cust_address.state_province = 'NY' and rownum < 6 are the additional constraints in the WHERE clause. The rownum < 6 clause is necessary to limit the number of results returned to fewer than 6.
	The ORDER BY DISTANCE_IN_MILES clause ensures that the distances are returned in order, with the shortest distance first and the distances measured in miles.

Back to Topic

Using the Spatial Index to Identify the Set of Locations that are within some Specified Distance of another Location

Query 4: Find all the customers within 100 miles of warehouse 3

Perform the following:

@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';

Here is a description of the information that was selected:

	The SDO_WITHIN_DISTANCE operator returns the customers from the customers table that are within 100 miles of warehouse 3. The first argument to SDO_WITHIN_DISTANCE (c.cust_geo_location in the example above) is the column to search. The second argument to SDO_WITHIN_DISTANCE (w.wh_geo_location in the example above) is the location you want to determine the distances from. No assumptions should be made about the order of the returned results. For example, the first row returned is not guaranteed to be the customer closest to warehouse 3.
	The DISTANCE parameter used within the SDO_WITHIN_DISTANCE operator specifies the distance value; in this example it is 100.
	The UNIT parameter used within the SDO_WITHIN_DISTANCE operator specifies the unit of measure of the DISTANCE parameter. The default unit is the unit of measure associated with the data. For longitude and latitude data, the default is meters; in this example, it is miles.

Query 5: Find all the customers within 100 miles of warehouse 3, put the results in order of distance, and give the distance in miles

Perform the following:

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

Here is a description of the information that was selected:

	The SDO_GEOM.SDO_DISTANCE function computes the exact distance between the customer's location and warehouse 3. The first argument to SDO_GEOM.SDO_DISTANCE (c.cust_geo_location in the example above) contains the customer's location whose distance from warehouse 3 is to be computed. The second argument to SDO_WITHIN_DISTANCE (w.wh_geo_location in the example above) is the location of warehouse 3, whose distance from the customer's location is to be computed.
	The third argument to SDO_GEOM.SDO_DISTANCE (0.005) is the tolerance value. The tolerance is a round-off error value used by Oracle Spatial. The tolerance is in meters for longitude and latitude data. In this example, the tolerance is 5 mm.
	The UNIT parameter used within the SDO_GEOM.SDO_DISTANCE parameter specifies the unit of measure of the distance computed by the SDO_GEOM.SDO_DISTANCE function. The default unit is the unit of measure associated with the data. For longitude and latitude data, the default is meters. In this example it is miles.
	The ORDER BY DISTANCE_IN_MILES clause ensures that the distances are returned in order, with the shortest distance first and the distances measured in miles.

Back to Topic

Creating and Using a Function-Based Index

A function-based index allows indexes to be built on the results of a function that returns a SDO_GEOMETRY object. It is a powerful mechanism which enables location-based functionality without requiring a SDO_GEOMETRY column in a table. The function-based index is intended for use on tables with columns that store longitude and latitude data.

How to Create and Use a Function-Based Index

The steps to create and use a function-based index are as follows:

Back to Topic List

Setup: Updating the WAREHOUSES Table

Before you continue, you need to run the wh_updates.sql script. This script adds the wh_longitude and wh_latitude columns to the WAREHOUSES table. wh_longitude and wh_latitude are columns of type NUMBER that will be spatially indexed using the function-based index.

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

Back to Topic

1. Creating a Function that Returns a SDO_GEOMETRY Object

To create a function that returns a SDO_GEOMETRY object, perform the following steps:

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

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

Description of the Executed SQL Query

The function must be declared as DETERMINISTIC, otherwise the optimizer may not use the most optimal plan. This is true for any function that returns an object. The GET_GEOM function takes two arguments of type NUMBER and returns a MDSYS.SDO_GEOMETRY object

Back to Topic

2. Adding Metadata to the user_sdo_geom_metadata View for a Function

Before creating a spatial index, you must add metadata to the user_sdo_geom_metadata view. One row is added for a function that returns the SDO_GEOMETRY object.

To add metadata for a function that returns a SDO_GEOMETRY object, perform the following:

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

Description of the Executed SQL Query

The GET_GEOM function is loaded into the COLUMN_NAME column of user_sdo_geom_metadata. The owner of the function (OE) is included with the function call. The arguments to the GET_GEOM function (wh_longitude, wh_latitude) are the longitude and latitude columns in the WAREHOUSES table.

Back to Topic

3. Creating a Function-Based Spatial Index

To create a function-based index, a user must have Query Rewrite privileges. Because you have already granted these privileges to the OE user, you can go ahead and create the index. To create a function-based index, perform the following steps:

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

Description of the Executed SQL Query

The GET_GEOM function is called as the COLUMN_NAME argument of the CREATE_INDEX statement. The arguments to the GET_GEOM function (wh_longitude, wh_latitude) are the longitude and latitude columns in the WAREHOUSES table.

Back to Topic

4. Using the Function-Based Spatial Index

Perform the following steps:

1.

To perform queries with function-based indexes, session privileges are required. QUERY_REWRITE_ENABLED must be set to True and QUERY_REWRITE_INTEGRITY must be set to Trusted for the session. Now rerun query 3. However, this time, use the function-based spatial index on the second argument to the SDO_NN operator instead of an SDO_GEOMETRY column.

2.

From your SQL*Plus session, logged on to the OE schema, run the following script: @query3_wfi This script contains the following statements: 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;

Description of the Executed SQL Query

The GET_GEOM function is called as the second argument to the SDO_NN operator. The arguments to the GET_GEOM function (wh_longitude, wh_latitude) are the longitude and latitude columns in the WAREHOUSES table.

Back to Topic

Analyzing Current and Proposed Location Data Using Workspace Manager

MyCompany is planning to build another warehouse to provide better service to its customers. Two potential sites are under consideration. MyCompany wants to analyze these prospective sites using existing SQL applications. To do this, data for both prospective sites must be entered into the production warehouse table. However, it is important to isolate the prospective site data so it does not impact the work of employees who are not part of the site selection teams. Isolating prospective site data will also allow the two site selection teams to work concurrently.

Oracle Workspace Manager enables current, proposed and historical row versions to exist in the same table. It requires no changes to application SQL (DML). It improves concurrency by allowing production users to access current data while other users go to a workspace to create and access proposed and historical data values. Changes made from a workspace can merged into current data as a group. Workspace Manager allows users to see a complete picture of the database in the context of the changes made from a workspace. It frees developers from writing custom code and DBAs from copying and synchronizing multiple copies of database tables or adding application specific metadata to track row versions.

In this section, you will perform the following:

Back to Topic List

1. Prepare for Warehouse Site Analysis

When a table is version-enabled, all rows in the table can support multiple versions of the data. Versioned rows are stored in the same table as the original rows. A workspace is a virtual environment that one or more users can share to make changes to the data in the database. There can be a hierarchy of workspaces in the database. By default, when a workspace is created, it is created from the topmost database workspace, which is always called "LIVE".

You will begin by version-enabling the warehouse table and creating two workspaces, SITE1 and SITE2, off of LIVE, one for each site selection team.

Perform the following:

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

Back to Topic

2. Add Two Proposed Warehouse Locations in a Workspace

Users enter a workspace to make changes to data. A workspace logically groups collections of row versions from one or more version-enabled tables, and isolates these versions until they are explicitly merged with production data.

Go to workspace SITE1 and add the first proposed warehouse location. Go to workspace SITE2 and add the second proposed warehouse location with same attributes as SITE1 except a different location. Go to workspace LIVE and select all from the warehouse table, the prospective sites do not appear. Users in SITE1 can not see changes made in SITE2 and visa versa. Perform the following:

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

Back to Topic

3. Determine which Location is Nearest to the Largest Number of Customers

The versioning infrastructure is not visible to the users of the database, and application SQL data manipulation statements (DML) to select, insert, modify, and delete data continue to work in the usual way with version-enabled tables. Users in a workspace automatically see the correct version of the record in which they are interested, that is, the user does not have to keep track of version chains and specify the version of interest.

You will go to each workspace in turn and use existing application SQL to perform a location-based query that reports how many customers are within 100 miles of each site. As it turns out, there are three customers within 100 miles of SITE1 and 25 customers within 100 miles of SITE2. Perform the following steps

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

Back to Topic

4. Make Chosen Location Data Available to other Users and Clean-up

Workspaces can be merged, refreshed or rolled back. Merging a workspace involves applying changes made in a child workspace to its parent workspace. Refreshing a workspace involves applying changes made in the parent workspace to a child workspace. Rolling back a workspace involves deleting changes in the workspace. Users can either delete all changes made since the workspace was created or only changes made after a savepoint. If a row is changed in both the child and parent workspace, a data conflict is created. Conflicts can be checked and resolved at any time. They are automatically detected when a merge or refresh operation is requested.

Since SITE2 has more customers within 100 miles of its location, merge it into workspace LIVE to make SITE2's data accessible to all users. Go to workspace LIVE and "select all from wrehouse" table, SITE2's data now appears. Rollback data for the less desirable SITE1 to remove it, delete the workspace and disable versioning on the warehouse table. Perform the following steps:

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

Back to Topic

Summary

In this lesson, you learned how to use Oracle Locator or Oracle Spatial, and Oracle Workspace Manager for location-based analysis on current and proposed data.

	Create a spatial index.
	Perform location-based queries.
	Create and use a function-based index.
	Analyzing Current and Proposed Location Data Using Workspace Manager.

Back to Topic List

Place the cursor over this icon to hide all screenshots.