Using Spatial with the Power Demand Cartridge

This section describes how the Oracle Spatial can be used with the Power Demand cartridge. With Spatial, you can perform geospatial queries against the power demand regions to select power demand data from areas of interest.

This example defines some regions with rectangular coordinates, and it also defines a rectangular area of interest that partially overlaps region 1 but does not touch regions 2 or 3. This area of interest might reflect the extent of some natural phenomenon, such as a mass of cold air or the path of a tornado (although an actual representation would require more than a simple rectangle). Figure 11-9 shows four regions, each represented as a square on a grid, and the area of interest represented as a gray rectangle. The figure uses a very simple (and arbitrary) coordinate system.

The queries select power demand data from regions that are overlapped in any way by the area of interest. In this example, the queries return data only from region 1. Note that this simple example considers the entire region; it does not provide coordinates for individual cells (neighborhoods), and it does not check which specific cells within region 1 are inside the area of interest.

This section assumes that you are familiar with the concepts and basic usage information for Spatial. For detailed conceptual and usage information, see the Oracle Spatial User's Guide and Reference.

To use Spatial with the power demand data, you must perform the following steps:

1. Create a Spatial layer consisting of four tables, with the geometry table related to the PowerDemand_tab table by region.

2. Create coordinates (x,y) for the power demand regions.

3. Populate the power demand regions index.

4. Create coordinates (x,y) for the area of interest to be used in queries against the power demand regions.

5. Populate the windows index.

6. Perform spatial queries.

Creating the Spatial Layer (PowerDemandRegions)

To use Spatial with power demand data, create a Spatial layer (called PowerDemandRegions in this example) consisting of four tables, with the geometry table (PowerDemandRegions_SDOGEOM) related to the PowerDemand_tab table by the region attribute.

The following statements define this layer.

-- Simple scenario for integrating Spatial.
-- Regions have spatial extent (e.g., states of the U.S.). 
-- A person wanting to select data may either want data for 
-- predefined regions or data for those units (such as
-- states) that intersect a dynamically created 
-- area-of-interest (such as one you would draw when choosing 
-- a zoom area from a Web-based mapping service).  
 
-- Create a Spatial layer called PowerDemandRegions. This 
-- consists of four tables, and the geometry table is related to 
-- the PowerDemand_Tab table by Region. 
set serveroutput on
set echo on
 
CREATE TABLE PowerDemandRegions_SDODIM ( 
  sdo_dimnum number, 
  sdo_dimname varchar2(32), 
  sdo_lb number, 
  sdo_ub number, 
  sdo_tolerance number ); 
 
CREATE TABLE PowerDemandRegions_SDOLAYER ( 
  sdo_ordcnt number, 
  sdo_level number,
  sdo_numtiles number ); 
 
-- In the following definition of PowerDemandRegions_SDOGEOM,
-- sdo_gid equates to Region in PowerDemand_Tab. 
CREATE TABLE PowerDemandRegions_SDOGEOM ( 
  sdo_gid number, 
  sdo_eseq number, 
  sdo_etype number,  
  sdo_seq number, 
  sdo_x1 number, 
  sdo_y1 number, 
  sdo_x2 number, 
  sdo_y2 number, 
  sdo_x3 number, 
  sdo_y3 number, 
  sdo_x4 number, 
  sdo_y4 number, 
  sdo_x5 number, 
  sdo_y5 number ) ; 
CREATE TABLE PowerDemandRegions_SDOINDEX ( 
  sdo_gid number, 
  sdo_code raw(255),
  sdo_meta raw(255)); 
 
-- Create some coordinates for the example and show how an 
-- index is created etc. We will also assume that some windows of 
-- interest have been pre-defined and stored in a Window_layer. 
 
CREATE TABLE Windows_SDODIM ( 
  sdo_dimnum number, 
  sdo_dimname varchar2(32), 
  sdo_lb number, 
  sdo_ub number, 
  sdo_tolerance number ); 
 
CREATE TABLE Windows_SDOLAYER ( 
  sdo_ordcnt number, 
  sdo_level number,
  sdo_numtiles number ); 
 
CREATE TABLE Windows_SDOGEOM ( 
  sdo_gid number, 
  sdo_eseq number, 
  sdo_etype number,  
  sdo_seq number, 
  sdo_x1 number, 
  sdo_y1 number, 
  sdo_x2 number, 
  sdo_y2 number, 
  sdo_x3 number, 
  sdo_y3 number, 
  sdo_x4 number, 
  sdo_y4 number, 
  sdo_x5 number, 
  sdo_y5 number ); 
 
CREATE TABLE Windows_SDOINDEX ( 
  sdo_gid number, 
  sdo_code raw(255),
  sdo_meta raw(255)); 

Creating Coordinates for the Power Demand Regions

To enable Spatial to recognize and work with the power demand regions, populate the Spatial layer tables with the necessary data. The following statements provide data, including x,y coordinates for region boundary corners, for three regions.

-- Populate the tables for power regions.
INSERT INTO PowerDemandRegions_SDODIM VALUES(
 1,
 'x-axis',
 0,
 100,
 0.0005);
INSERT INTO PowerDemandRegions_SDODIM VALUES(
 2,
 'y-axis',
 0,
 100,
 0.0005);
INSERT INTO PowerDemandRegions_SDOLAYER VALUES(
 10,
 5,
 NULL);
INSERT INTO PowerDemandRegions_SDOGEOM VALUES(
 1,
 0,
 3,
 1,
 2,2,
 4,2,
 4,4,
 2,4,
 2,2);
INSERT INTO PowerDemandRegions_SDOGEOM VALUES(
 2,
 0,
 3,
 1,
 4,2,
 6,2,
 6,4,
 4,4,
 4,2);
INSERT INTO PowerDemandRegions_SDOGEOM VALUES(
 3,
 0,
 3,
 1,
 6,2,
 8,2,
 8,4,
 6,4,
 6,2);

Populating the Power Demand Regions Index

The following statement populates the index for the Spatial layer PowerDemandRegions.

-- Populate the index for Spatial layer PowerDemandRegions 
EXECUTE sdo_admin.populate_index('POWERDEMANDREGIONS');

Creating Coordinates for the Area of Interest

To enable Spatial to recognize and work with the area of interest, populate the Windows layer tables with the necessary data. The following statements provide data, including x,y coordinates for the boundary corners, for the area of interest.

-- Populate the tables for region of interest.
INSERT INTO Windows_SDODIM VALUES(
 1,
 'x-axis',
 0,
 100,
 0.0005);
INSERT INTO Windows_SDODIM VALUES(
 2,
 'y-axis',
 0,
 100,
 0.0005);
INSERT INTO Windows_SDOLAYER VALUES(
 10,
 5,
 NULL);

-- The next INSERT creates a small rectangle that
-- partially overlaps region 1 but does not touch
-- regions 2 or 3.

INSERT INTO Windows_SDOGEOM VALUES(
 1,
 0,
 3,
 1,
 2,3,
 3,3,
 3,5,
 2,5,
 2,3);

Populating the Windows Index

The following statement populates the index for the Windows layer.

-- Populate the index for the WINDOWS layer (Windows)
EXECUTE sdo_admin.populate_index('WINDOWS');

Performing Spatial Queries

The following queries use the SDOGEOM.Relate function to retrieve data from regions that are within or overlapping the area of interest. Because only region 1 overlaps the area of interest, only rows from region 1 are considered. Each row returned reflects power demand data for a particular hourly timestamp (for example, aggregate data for region 1 at 5 AM on 01-Feb-1998).

The first query returns only rows for which the third cell has a power demand reading of 12. Given the actual sample data (see the INSERT statements in "Creating and Populating the Power Demand Table"), only one row meets this criterion.

Each of the following statements performs this query, but the second one uses the spatial index for primary filtering (for performance reasons).

-- Now some queries.
-- Query 1:  
-- Find Regions Within_Or_Overlapping the Area-of-Interest 1 
-- whose 3rd Cell has a power demand value of 12.
 
Select P.Region, P.Sample.TotGridDemand, P.Sample.MaxCellDemand, 
P.Sample.MinCellDemand 
FROM PowerDemand_Tab P 
WHERE Power_Equals(P.Sample, 3, 12) = 1  
AND P.Region IN ( 
Select S.sdo_gid 
from powerdemandregions_sdogeom S, windows_sdogeom 
where  
sdo_geom.Relate('PowerDemandRegions', S.sdo_gid, 'ANYINTERACT', 'WINDOWS', 1) = 
'TRUE' ); 

-- Use the spatial index for primary filtering (for performance reasons)

Select P.Region, P.Sample.TotGridDemand, P.Sample.MaxCellDemand, 
P.Sample.MinCellDemand 
FROM PowerDemand_Tab P 
WHERE Power_Equals(P.Sample, 3, 12) = 1  
AND P.Region IN ( 
Select sdo_gid gid1 
from (select distinct s.sdo_gid from powerdemandregions_sdoindex S, 
      windows_sdoindex w
      where s.sdo_code = w.sdo_code and w.sdo_gid = 1)
where  
sdo_geom.Relate('PowerDemandRegions', sdo_gid, 'ANYINTERACT', 'WINDOWS', 1) = 
'TRUE' ); 

Both queries return the following result:

REGION     SAMPLE.TOT SAMPLE.MAX SAMPLE.MIN
---------- ---------- ---------- ----------
         1         86         54          3
1 row selected.

The second query returns only rows for which any cell has a power demand reading of 9. Given the actual sample data (see the INSERT statements in "Creating and Populating the Power Demand Table"), five rows meets this criterion.

-- Query 2: Same thing for PowerEqualsAny() - in this case where
-- any cell has a power demand value of 9.
 
Select P.Region, P.Sample.TotGridDemand, P.Sample.MaxCellDemand, 
P.Sample.MinCellDemand 
FROM PowerDemand_Tab P
WHERE Power_EqualsAny(P.Sample, 9) = 1  
AND P.Region IN ( 
Select S.sdo_gid 
from powerdemandregions_sdogeom S, windows_sdogeom 
where  
sdo_geom.Relate('PowerDemandRegions', S.sdo_gid, 'ANYINTERACT', 'WINDOWS', 1) = 
'TRUE' ); 

This query returns the following result:

REGION     SAMPLE.TOT SAMPLE.MAX SAMPLE.MIN
---------- ---------- ---------- ----------
         1         90         55          5
         1         89         56          3
         1         88         55          3
         1         87         54          3
         1         86         54          3
5 rows selected.