18
Window Functions and Procedures for Relational Model

If a query window does not already exist in the database, you must first insert it and create an index for it. The SDO_WINDOW functions and procedures are used to create temporary geometry objects to be used in comparisons with stored geometries. You can create query windows with any number of coordinates.

Because not all Oracle users may have insert privileges, the SDO_WINDOW package is not automatically installed when you install Spatial. This allows a DBA to control the schema under which these functions and procedures operate. Choose an Oracle user who has insert privilege and compile the SDO_WINDOW package under that user. For example, you could choose the MDSYS Oracle user:

% sqlplus mdsys/password

SQL> @$ORACLE_HOME/md/admin/sdowin.sql 
SQL> @$ORACLE_HOME/md/admin/prvtwin.plb

This chapter contains descriptions of the window functions and procedures listed in Table 18-1. This chapter refers to the relational Spatial model only.

Table 18-1 Window Functions and Procedures

Function/Procedures	Description
SDO_WINDOW.BUILD_WINDOW	Builds a query window.
SDO_WINDOW.BUILD_WINDOW_FIXED	Builds a query window using fixed-size tiles.
SDO_WINDOW.CLEAN_WINDOW	Removes the tables used for a query window.
SDO_WINDOW.CLEANUP_GID	Removes the query window without removing the tables.
SDO_WINDOW.CREATE_WINDOW_LAYER	Creates the tables needed for a query window layer.

---

SDO_WINDOW.BUILD_WINDOW

Format

SDO_WINDOW.BUILD_WINDOW(comp_name, layername, SDO_ETYPE, SDO_NUMTILES,
X1, Y1, [...Xn, Yn])

Description

Builds the window for the query and returns an SDO_GID that serves as a handle. The window is tessellated into hybrid tiles. Hybrid indexing is not recommended for the relational Spatial model.

Keywords and Parameters

comp_name	Specifies the name of the user who compiled this package. This user must have appropriate privileges to read and write into the database. Data type is VARCHAR2.
layername	Specifies the name of the window layer into which the coordinates will be inserted. Data type is VARCHAR2.
SDO_ETYPE	Specifies the type of geometry objects. Data type is INTEGER, corresponding to the following constants: 1 or SDO_GEOM.POINT_TYPE 2 or SDO_GEOM.LINESTRING_TYPE 3 or SDO_GEOM.POLYGON_TYPE
SDO_NUMTILES	Value must be NULL for Spatial release 8.0.4 and later. Data type is NUMBER.
X ordinateN, Y ordinateN	Specifies the X and Y values of a vertex (coordinate pair) in a geometry. Up to 125 pairs may be added in a single call. Data type is NUMBER.

Returns

This function returns the SDO_GID of the new geometry.

Data type is NUMBER.

Usage Notes

This function inserts the coordinates into the <layername>_SDOGEOM table, tessellates the geometry (creates the index), and returns a unique SDO_GID corresponding to the geometry.

You do not need special privileges to execute this function. However, the user who compiles it does need appropriate privileges to read and write into the database.

When working with Spatial release 8.0.3 tables, the SDO_NUMTILES parameter indicates the number of tiles into which the window should be tessellated. For release 8.0.4 or later, the function reads that information automatically from the <layername>_SDOLAYER table.

Related Topics

SDO_WINDOW.BUILD_WINDOW_FIXED

---

SDO_WINDOW.BUILD_WINDOW_FIXED

Format

SDO_WINDOW.BUILD_WINDOW_FIXED (comp_name, layername, SDO_ETYPE, SDO_TILESIZE,
X1, Y1, [...Xn, Yn])

Description

Builds the window for the query and returns an SDO_GID that serves as a handle. The window is tessellated into fixed-size tiles.

Keywords and Parameters

comp_name	Specifies the name of the user who compiled this package. This user must have appropriate privileges to read and write into the database. Data type is VARCHAR2.
layername	Specifies the name of the window layer into which the coordinates will be inserted. Data type is VARCHAR2.
SDO_ETYPE	Specifies the type of geometry element. Data type is INTEGER, corresponding to the following constants: 1 or SDO_GEOM.POINT_TYPE 2 or SDO_GEOM.LINESTRING_TYPE 3 or SDO_GEOM.POLYGON_TYPE
SDO_TILESIZE	Specifies the number of tessellations required to achieve the desired fixed-size tiles. Data type is NUMBER.
X ordinateN, Y ordinateN	Specifies the X and Y values of a vertex (coordinate pair) in a geometry. Up to 125 pairs may be added in a single call. Data type is NUMBER.

Returns

This function returns the SDO_GID of the new geometry.

Data type is NUMBER.

Usage Notes

This function inserts the coordinates into the <layername>_SDOGEOM table, tessellates the geometry (creates the index), and returns a unique SDO_GID corresponding to the geometry.

You do not need special privileges to execute this function. However, the user who compiles it does need appropriate privileges to read and write into the database.

Query SDO_LEVEL from the <layername>_SDOLAYER table to pass the correct SDO_TILE_SIZE value to this function.

Related Topics

None.

---

SDO_WINDOW.CLEAN_WINDOW

Format

SDO_WINDOW.CLEAN_WINDOW (layername);

Description

Removes the four tables created in the layer for the query window.

Keywords and Parameters

layername

Specifies the name of the window layer that must be removed. Data type is VARCHAR2.

Usage Notes

Typically, you would build a layer once, and then build multiple windows and perform multiple queries using that layer. After finishing all queries, you can execute the SDO_WINDOW.CLEAN_WINDOW procedure to remove the tables.

Related Topics

SDO_WINDOW.CLEANUP_GID

---

SDO_WINDOW.CLEANUP_GID

Format

SDO_WINDOW.CLEANUP_GID (gid, layer, do_commit);

Description

Removes the query window from the layer tables.

Keywords and Parameters

gid	Specifies the geometric object identifier of the query window. Data type is NUMBER.
layer	Specifies the name of the window layer associated with the query window. Data type is VARCHAR2.
do_commit	Specifies whether a commit operation is performed (TRUE) or is not performed (FALSE, the default) after the cleanup. Data type is BOOLEAN.

Usage Notes

Typically, you would create a query layer once, and then build multiple query windows and perform multiple queries using that layer. The SDO_WINDOW.CLEANUP_GID procedure removes a single query window from the layer. Use this procedure to avoid the overhead of removing and re-creating the tables repeatedly.

After finishing all queries, you can execute the SDO_WINDOW.CLEAN_WINDOW procedure to remove the tables.

Related Topics

SDO_WINDOW.CLEAN_WINDOW

---

SDO_WINDOW.CREATE_WINDOW_LAYER

Format

SDO_WINDOW.CREATE_WINDOW_LAYER (layername, SDO_LEVEL, SDO_NUMTILES,
SDO_DIMNUM1, SDO_LB1, SDO_UB1, SDO_TOLERANCE1, SDO_DIMNAME1,
SDO_DIMNUM2, SDO_LB2, SDO_UB2, SDO_TOLERANCE2, SDO_DIMNAME2)

Description

Creates the necessary tables that constitute a layer used for defining a query window.

Keywords and Parameters

layername	Specifies the name of the window layer to be created. The layer name is used to construct the four tables associated with the layer. Data type is VARCHAR2.
SDO_LEVEL	Specifies the number of times the layer should be tessellated during the indexing phase. Data type is INTEGER.
SDO_NUMTILES	Specifies the number of tiles to generate during indexing. Data type is INTEGER.
SDO_DIMNUM1, SDO_DIMNUM2	Specifies the number of the dimension, starting with 1. Data type is NUMBER.
SDO_LB1, SDO_UB1, SDO_LB2, SDO_UB2	Specifies the lower and upper bounds of this dimension. Data type is NUMBER.
SDO_TOLERANCE1, SDO_TOLERANCE2	Specifies the allowable variance of ordinate values within each dimension. Data type is NUMBER.
SDO_DIMNAME1, SDO_DIMNAME2	Specifies the name of the dimension. Data type is VARCHAR2.

Usage Notes

Because the <layername>_SDODIM table is initialized with the dimension and the bound information, only those queries that are in the same dimension should be queried against this layer. If you wish to issue a query with respect to a different dimension, you must create a new layer.

Related Topics

None.