9
Partitioned Point Data Functions

Spatial Cartridge has undergone an architectural change, beginning with the 7.3.3 release. The empshasis 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

---