Oracle8 Spatial Cartridge Oracle8 Spatial Cartridge User's Guide and Reference Release 8.0.5 A53264-03 |
| Library | Product | Contents

# 9
Partitioned Point Data Functions

Spatial Cartridge has undergone an architectural change, beginning with the 7.3.3 release. The emphasis on partitioned tables has been replaced by the improved spatial indexing features.

The functions described in this chapter are not required for creating or maintaining a spatial database, however, they are provided for convenience in working with legacy data in partitioned point data tables. They are used with SQL SELECT, INSERT, UPDATE, and DELETE statements to perform the following:

- Generate dimensions from bounded, hierarchical, or date data values

- Encode and decode dimensions

- Retrieve bounded, hierarchical, or date data values from dimensions

When using these functions in basic SQL statements, use the form:
SDO_<function>. When using the functions inside a PL/SQL block, use a period (.) instead of the underscore.

This chapter contains descriptions of the spatial functions listed in Table 9-1.

##### Table 9-1 Partitioned Point Data Functions

Function | Purpose
--- | ---
SDO_BVALUETODIM | Creates a dimension from bounded data values.
SDO_COMPARE | Evaluates the relationship between two objects described by HHCODEs.
SDO_DATETODIM | Creates a dimension from an Oracle DATE data type.
SDO_DECODE | Extracts a single dimension from an HHCODE.
SDO_ENCODE | Creates an HHCODE by combining dimensions to describe an area or point.
SDO_TO_BVALUE | Extracts a bounded data value from a dimension.
SDO_TO_DATE | Extracts an Oracle DATE data type from a dimension.

Additional functions that support partitioned point data can be found in Chapter 5, "Administrative Procedures" and Appendix A, "Sample SQL Scripts and Tuning Tips".

## SDO_BVALUETODIM

### Purpose

This function creates a dimension from a bounded value, which is a value contained in a set of values expressed as a lower boundary and an upper boundary.

### Syntax

SDO_BVALUETODIM (value, lower_boundary, upper_boundary, decimal_scale)

### Keywords and Parameters

#####

value | Specifies the value for the particular dimension. Data type is NUMBER.
lower_boundary | Specifies the lower boundary of the dimension range. Data type is NUMBER.
upper_boundary | Specifies the upper boundary of the dimension range. Data type is NUMBER.
decimal_scale | Specifies the number of digits to the right of the decimal point. Data type is NUMBER.

### Returns

This function returns a dimension. The data type is RAW.

### Usage Notes

Example 9-1 shows the SDO_BVALUETODIM() function.

#### Example 9-1

    SQL> INSERT INTO sourcetable1(SAMPLENAME,DATA_PT)

    2> VALUES ('SAMPLE1',SDO_ENCODE(SDO_BVALUETODIM(10,-100,100,7),

    3> SDO_BVALUETODIM(20,-100,100,7));

### Related Topics

- SDO_ENCODE() function

- SDO_TO_BVALUE() function

## SDO_COMPARE

### Purpose

This function evaluates the relationship between an area or point described by an HHCODE and another HHCODE, or a range of HHCODEs expressed as an upper bound and lower bound.

### Syntax

SDO_COMPARE (hhcode_expression, {hhcode_expression | lower_bound_HHCODE,upper_bound_HHCODE})

### Keywords and Parameters

#####

hhcode_expression | Specifies an expression that evaluates to an HHCODE. Data type is RAW.
lower_bound_HHCODE | Specifies the lower bound HHCODE expression. Data type is RAW.
upper_bound_HHCODE | Specifies the upper bound HHCODE expression. Data type is RAW.

### Returns

This function returns one of the following keywords:

- ENCLOSES

- EQUAL

- INSIDE

- OUTSIDE

- OVERLAP

The data type is VARCHAR2.

### Usage Notes

Example 9-2 selects all points that fall within the given multidimensional range.

#### Example 9-2

    SQL> SELECT SDO_GID FROM layer1_SDOINDEX WHERE

    2> SDO_COMPARE(SDO_MAXCODE,

    3> SDO_ENCODE(5,5),

    4> SDO_ENCODE(25,25))='INSIDE';

Example 9-3 selects GIDs based on interaction between their spatial index tiles.

#### Example 9-3

    SQL> SELECT SDO_GID FROM layer1_SDOINDEX A, layer2_SDOINDEX B

    2> WHERE SDO_COMPARE(A.SDO_CODE,B.SDO_CODE) != 'OUTSIDE';

### Related Topics

- SDO_GEOM.INTERACT() function

- SDO_GEOM.RELATE() function

## SDO_DATETODIM

### Purpose

This function creates a dimension from an Oracle DATE data type. The component number determines the level of resolution of the date in the dimension.

### Syntax

SDO_DATETODIM (date [, component])

### Keywords and Parameters

#####

date | Specifies the calendar date. Data type is DATE.
component | Specifies the level of resolution. The component number values are defined as follows: 1 accurate to year 2 accurate to month 3 accurate to day 4 accurate to hour 5 accurate to minute 6 accurate to second The default value is 6. Data type is INTEGER.

### Returns

This function returns a dimension. The data type is RAW.

### Usage Notes

You must use a valid Oracle date format string.

Example 9-4 shows the SDO_DATETODIM() function.

#### Example 9-4

    SQL> INSERT INTO sourcetable1(SAMPLENAME,DATA_PT)

    2> VAUES('SAMPLE1',SDO_ENCODE(SDO_DATETODIM(TO_DATE('19-Jul-96'),

    3> SDO_BVALUETODIM(100,-1000,1000,7)));

### Related Topics

- SDO_ENCODE() function

- SDO_TO_DATE() function

## SDO_DECODE

### Purpose

This function extracts a single dimension from an HHCODE.

### Syntax

SDO_DECODE (hhcode_expression, dimension_number)

### Keywords and Parameters

#####

hhcode_expression | Specifies an expression that evaluates to an HHCODE. Data type is RAW.
dimension_number | Specifies the dimension number to extract. Data type is INTEGER.

### Returns

This function returns a dimension. The data type is RAW.

### Usage Notes

The SDO_DECODE() function is called once for each dimension to be decoded.

Example 9-5 shows the SDO_DECODE() function.

#### Example 9-5

    SQL> SELECT

    2> SDO_TO_BVALUE(SDO_DECODE(DATA_PT,1),1,6),

    3> SDO_TO_BVALUE(SDO_DECODE(DATA_PT,2),-100,100),

    4> SDO_TO_DATE(SDO_DECODE(DATA_PT,3))

    5> FROM sourcetable1 WHERE SAMPLENAME='SAMPLE1';

### Related Topics

- SDO_TO_BVALUE() function

- SDO_TO_DATE() function

## SDO_ENCODE

### Purpose

This function combines dimensions to create the HHCODE that describes an area or point.

### Syntax

SDO_ENCODE (dimension1[,dimension2 ...])

### Keywords and Parameters

#####

dimension | Specifies an expression created by the SDO_BVALUETODIM or SDO_DATETODIM functions. Data type is RAW.

### Returns

This function returns an HHCODE. The data type is RAW.

### Usage Notes

Consider the following when using this function:

- When encoding dimensions, the order of the dimensions in the parameter list must be consistent for all rows within the table.

- This function can encode up to 32 dimensions.

Example 9-6 shows the SDO_ENCODE() function.

#### Example 9-6

    SQL> INSERT INTO sourcetable1(SAMPLENAME,DATA_PT)

    2> VALUES ('SAMPLE1',SDO_ENCODE(SDO_BVALUETODIM(50,-100, 100, 10),

    3> SDO_BVALUETODIM(30,-100,100,10),

    4> SDO_DATETODIM(TO_DATE('05-Jul-96'),3)));

### Related Topics

- SDO_BVALUETODIM() function

- SDO_DATETODIM() function

## SDO_TO_BVALUE

### Purpose

This function returns the original bounded data value of a dimension.

### Syntax

SDO_TO_BVALUE (dimension, lower_boundary, upper_boundary)

### Keywords and Parameters

#####

dimension | Specifies the dimension. Data type is RAW.
lower_boundary | Specifies the lower boundary of the dimension range. Data type is NUMBER.
upper_boundary | Specifies the upper boundary of the dimension range. Data type is NUMBER.

### Returns

This function returns a bounded data value. The data type is NUMBER.

### Usage Notes

This function returns a number that is the value for a dimension within the specified range. This is not necessarily the range for which the dimension was originally created.

Example 9-7 shows the SDO_TO_BVALUE() function.

#### Example 9-7

    SQL> SELECT (SDO_TO_BVALUE(SDO_DECODE(DATA_PT,2),-100,100)

    2> FROM sourcetable1 WHERE SAMPLENAME='SAMPLE1';

### Related Topics

- SDO_DECODE() function

- SDO_BVALUETODIM() function

## SDO_TO_DATE

### Purpose

This function returns the original date value of a dimension.

### Syntax

SDO_TO_DATE (dimension)

### Keywords and Parameters

#####

dimension | Specifies the dimension. Data type is RAW.

### Returns

This function returns an Oracle DATE data type.

### Usage Notes

Example 9-8 shows the SDO_TO_DATE() function.

#### Example 9-8

    SQL> SELECT SDO_TO_DATE(SDO_DECODE(DATA_PT,3))

    2> FROM sourcetable1 WHERE SAMPLENAME='SAMPLE1';

### Related Topics

- SDO_DATETODIM() function

- SDO_DECODE() function

Prev | Next
| Copyright © 1998 Oracle Corporation. All Rights Reserved. |
| Library | Product | Contents