| |
Using Oracle Spatial and Oracle Workspace Manager to Perform Location-Based
Analysis
Module Objectives
Purpose
This module focuses on how to use Oracle Spatial to perform location-based
analysis. Oracle Spatial is an integrated set of functions and procedures
designed to enable you to efficiently store, query, and perform analysis
on spatial data in Oracle.
This module also shows how Oracle Workspace Manager can be used to select
an optimal location for a new warehouse. Workspace Manager is a feature
of the Oracle database. It enables developers and DBAs to isolate collections
of changes to relational data, make multiple copies of relational data
for what-if analysis, and/or keep a persistent history of changes to the
data. In essence, it gives a user, or group of users, a private version
of the entire database.
Objectives
After completing this module, you should be able to:
 |
Load new customers and their locations |
|
Index spatial data |
|
Perform location-based queries |
|
Create and use function-based indexes |
|
Enable and disable versioning on a database table |
|
Create and delete workspaces |
|
Enter a workspace |
|
Rollback unwanted changes to data |
|
Merge changes made in a workspace into production |
Prerequisites
Before starting this module, you should have:
Reference Material
The following is a list of useful reference material if you want additional
information about the topics in this module:
Spatial Overview
MyCompany has several major warehouses. They need to locate their customers
who are near a given warehouse, to inform them of new advertising promotions.
Spatial data includes information about an object's location. To locate
their customers and perform location-based analysis, MyCompany must store
spatial data for both their customers and the warehouses.
This module uses the customers
and warehouses tables in
the OE schema.
The customers table has
the following fields:
| Column |
Data Type |
| 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_language |
VARCHAR2(3) |
| nls_territory |
VARCHAR2(30) |
| credit_limit |
NUMBER(9,2) |
| cust_email |
VARCHAR2(30) |
| account_mgr_id |
NUMBER(6) |
| cust_geo_location |
MDSYS.SDO_GEOMETRY
|
The warehouses table has
the following fields:
| Column |
Data Type |
| warehouse_id |
NUMBER(3) |
| warehouse_spec |
SYS.XMLTYPE |
| warehouse_name |
VARCHAR2(35) |
| location_id |
NUMBER(4) |
| wh_geo_location |
MDSYS.SDO_GEOMETRY |
Oracle Data Types
Oracle's native data types include:
Load New Customers and Their Locations
This module already assimes you have the Order Entry (OE) schema loaded
and the CUSTOMERS and WAREHOUSES tables have been created. You will perform
the following tasks:
| 1. |
Load the Location Data |
| 2. |
Add New Customers and Their Locations to the CUSTOMERS Table |
| 3. |
Add metadata to the USER_SDO_GEOM_METADATA
view |
1. Loading the Location Data
This module assumes that you have the Order Entry (OE)
schema loaded, and that the customers
and warehouses tables have
already been created. 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 steps:
| 1. |
Open a SQL*Plus session from your working directory d:\wkdir
and execute the following commands:
connect oe/oe@orcl.world
@loc_updates
|
| 2. |
The results are as follows:
|
2. 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).
For Oracle Spatial, it is important to 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 steps:
| 1. |
From a SQL*Plus session logged on to the OE schema, run
@insert_customers.sql.
|
| 2. |
The results are as follows:
|
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)
where the elements of the syntax have the following meanings:
| 2001 |
This is the SDO_GTYPE
attribute and it is set to 2001 when storing a two-dimensional single
point such as a customer's location. |
| 8307 |
This is the spatial reference system ID (SRID): a foreign
key to an Oracle dictionary table (MDSYS.CS_SRS)
that contains all the supported coordinate systems. It is important
to associate your customer's location to a coordinate system. In this
example, 8307 corresponds to "Longitude / Latitude (WGS 84)." |
| MDSYS.SDO_POINT_TYPE |
This is where you store your longitude and latitude
values within the SDO_GEOMETRY
constructor. Note that you can store a third value also, but for these
modules, all the customer data is two-dimensional. |
| NULL,
NULL |
The last two null values are beyond the scope of this
module. You can construct very powerful location-based queries without
understanding the last two fields of the SDO_GEOMETRY
constructor. For more information on all the fields of the SDO_GEOMETRY
object, please refer to the Oracle Spatial User's Guide and Reference.
For now, these last two fields should be set to NULL. |
3. Add metadata to the USER_SDO_GEOM_METADATA
view
Before creating a spatial index, you must add metadata for the customers
and warehouses 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 steps:
| 1. |
From a SQL*Plus session logged on to the OE schema, run
@add_metadata.sql.
|
| 2. |
The results are as follows:
|
Here is a description of the information that was inserted:
| TABLE_NAME |
This is the name of the table which contains the spatial
data. |
| COLUMN_NAME |
This is the name of the SDO_GEOMETRY
column which stores the spatial data |
| MDSYS.SDO_DIM_ARRAY |
This is a constructor which holds the MDSYS.SDO_DIM_ELEMENT
object, which in turn stores the extents of the spatial data
in each dimension (-180.0, 180.0), and a tolerance value (0.005).
The tolerance is a round-off error value used by Oracle Spatial, and
is in meters for longitude and latitude data. In this example, the
tolerance is 5 mm. |
| 8307 |
This is the spatial reference system id (SRID): a foreign
key to an Oracle dictionary table (MDSYS.CS_SRS)
that contains all the supported coordinate systems. It is important
to associate your customer's location to a coordinate system. In this
example, 8307 corresponds to "Longitude / Latitude (WGS 84)." |
Creating a Spatial Index on a Geometry Column
You are now ready to create spatial indexes for CUSTOMERS and WAREHOUSES:
| 1. |
From a SQL*Plus session logged on to the OE schema, run @create_indexes.sql
to create the indexes.
|
| 2. |
The results are as follows:
|
| LAYER_GTYPE |
This parameter works both as a constraint and as a
hint to the optimizer. If the parameter LAYER_GTYPE =POINT is used,
checks are made to ensure that all geometries are points, as well
as the parameter ensures optimized processing of point data. CUSTOMERS
and WAREHOUSES both contain point geometries only. |
Performing Location-Based Queries
In that pages that follow, you will learn how to perform the following
location-based queries:
| 1. |
Find the five customers closest to the warehouse whose warehouse
ID is 2. |
| 2. |
Find the five customers closest to warehouse 2 and put the results
in order of distance. |
| 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. |
| 4. |
Find all the customers within 100 miles of warehouse 3. |
| 5. |
Find all the customers within 100 miles of warehouse 3, put the
results in order of distance, and give the distance in miles. |
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 steps:
| 1. |
From a SQL*Plus session logged on to the OE schema, run
@query1.sql
to execute the following query:
|
| 2. |
The results are as follows:
|
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 steps:
| 1. |
From a SQL*Plus session logged on to the OE schema, run
@query2.sql
to execute the following query:
|
| 2. |
The results are as follows:
|
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. |
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 steps:
| 1. |
From a SQL*Plus session logged on to the OE schema, run
@query3.sql
to execute the following query:
|
| 2. |
The results are as follows:
|
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. |
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 steps:
| 1. |
From a SQL*Plus session logged on to the OE schema, run
@query4.sql
to execute the following query:
|
| 2. |
The results are as follows:
|
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 steps:
| 1. |
From a SQL*Plus session logged on to the OE schema, run
@query5.sql
to execute the following query:
|
| 2. |
The results are as follows:
|
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. |
Creating and Using a Function-Based Index
Overview
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 Oracle Spatial 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:
| 1. |
Create a function that returns a SDO_GEOMETRY
object.
|
| 2. |
Add metadata into the USER_SDO_GEOM_METADATA
view for a function.
|
| 3. |
Create a function-based spatial index . |
| 4. |
Use the function-based spatial index. |
Updating the warehouses
Table
Before you continue, you must 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.
| 1. |
From a SQL*Plus session logged on to the OE schema, run
@wh_updates.sql
to load the data.
|
| 2. |
The results are as follows:
|
1. Creating a Function that Returns a SDO_GEOMETRY
Object
To create a function that returns a SDO_GEOMETRY
object, perform the following steps:
| 1. |
Before you can create the function, you need to make sure that
you have the privilege to do so. Specify the following commands:
connect / as sysdba;
Grant query rewrite to oe;
|
| 2. |
Connect again to your instance as oe/oe.
|
| 3. |
From a SQL*Plus session logged on to the OE schema, run
@create_function.sql
to execute the following query:
|
| 4. |
The results are as follows:
|
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.
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 steps:
| 1. |
From a SQL*Plus session logged on to the OE schema, run
@add_metadata_fi.sql
to execute the following query:
|
| 2. |
The results are as follows:
|
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.
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:
| 1. |
From a SQL*Plus session logged on to the OE schema, run
@create_function_index.sql
to execute the following query:
|
| 2. |
The results are as follows:
|
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.
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 a SQL*Plus session logged on to the OE schema, run
@query3_wfi.sql.
The results are as follows:
|
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.
Oracle Workspace Manager Overview
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 versions database rows and groups collections
of row versions in Oracle9i workspaces. It requires no changes to application
SQL (DML), and is easily managed from Oracle Enterprise Manager. Workspace
Manager provides a rich concurrency and security model, a complete set
of workspace semantics and efficient storage of versioned data.
Workspace Manager improves concurrency. It allows users to continue to
access production data while data changes are collected in isolation over
a period of time. This collection of changes can be rolled into production
as a group. Workspace Manager allows users to see a complete picture of
the database in the context of the changes made in a workspace. It enables
multiple versions of database rows to exist in the same database, obviating
the need to copy and synchronize multiple copies of database tables. And,
it frees developers from writing custom code and DBAs from adding application
specific metadata to track versions
In this section, you will perform the following:
| 1. |
Prepare for warehouse site analysis
|
| 2. |
Add two proposed warehouse locations in a workspace |
| 3. |
Determine which location is nearest to the largest number of customers |
| 4. |
Make chosen location data available to other users and clean-up |
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 steps:
| 1. |
From a SQL*Plus session logged on to the OE schema, run @init_workspaces.sql.
The results are as follows:
|
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.
Goto workspace SITE1 and add the first proposed warehouse location. Goto
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 steps:
| 1. |
From a SQL*Plus session logged on to the OE schema, run @add_workspace_data.sql.
The results are as follows:
|
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:
| 1. |
From a SQL*Plus session logged on to the OE schema, run @goto_workspace_and_query.sql.
The results are as follows:
Scroll down to see the rest of the data.
|
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. User 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 is closeest to the greatest number of customers, merge it
into workspace LIVE to make SITE2 data accessible to all users. Go to
workspace LIVE and select all from warehouse table, SITE2 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:
| 1. |
From a SQL*Plus session logged on to the OE schema, run @merge_workspace_cleanup.sql.
The results are as follows:
|
Module Summary
In this module, you should have learned how to:
|
Load new customers and their locations |
|
Index spatial data |
|
Perform location-based queries |
|
Create and use function-based indexes |
|
Enable and disable versioning on a database table |
|
Create and delete workspaces |
|
Enter a workspace |
|
Rollback unwanted changes to data |
|
Merge changes made in a workspace into production |
Copyright © 2002 Oracle Corporation. All Rights Reserved.
Close Window
|
Bookmark
