3 SDO_TOPO Package Subprograms

The MDSYS.SDO_TOPO package contains subprograms (functions and procedures) that constitute part of the PL/SQL application programming interface (API) for the Spatial topology data model. This package mainly contains subprograms for creating and managing topologies.

To use the subprograms in this chapter, you must understand the conceptual information about topology in Chapter 1.

Another package, SDO_TOPO_MAP, mainly contains subprograms related to editing topologies. Reference information for the SDO_TOPO_MAP package is in Chapter 4.

The rest of this chapter provides reference information on the SDO_TOPO subprograms, listed in alphabetical order.

SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER

Format

SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER(

topology IN VARCHAR2,

table_name IN VARCHAR2,

column_name IN VARCHAR2,

topo_geometry_layer_type IN VARCHAR2,

relation_table_storage IN VARCHAR2 DEFAULT NULL,

child_layer_id IN NUMBER DEFAULT NULL);

Description

Adds a topology geometry layer to a topology.

Parameters

topology

Topology to which to add the topology geometry layer containing the topology geometries in the specified column. The topology must have been created using the SDO_TOPO.CREATE_TOPOLOGY procedure.

table_name

Name of the topology geometry layer table containing the column specified in column_name.

column_name

Name of the column (of type SDO_TOPO_GEOMETRY) containing the topology geometries in the topology geometry layer to be added to the topology.

topo_geometry_layer_type

Type of topology geometry layer: POINT, LINE, CURVE, or POLYGON.

relation_table_storage

Physical storage parameters used internally to create the <topology-name>_RELATION$ table (described in Section 1.5.4). Must be a valid string for use with the CREATE TABLE statement. For example: TABLESPACE tbs_3 STORAGE (INITIAL 100K NEXT 200K). If you do not specify this parameter, the default physical storage values are used.

child_layer_id

Layer ID number of the child topology geometry layer for this layer, if the topology has a topology geometry layer hierarchy. (Topology geometry layer hierarchy is explained in Section 1.4.) If you do not specify this parameter and if the topology has a topology geometry layer hierarchy, the topology geometry layer is added to the lowest level (level 0) of the hierarchy.

If the topology does not have a topology geometry layer hierarchy, do not specify this parameter when adding any of the topology geometry layers.

Usage Notes

The first call to this procedure for a given topology creates the <topology-name>_RELATION$ table, which is described in Section 1.5.4.

An exception is raised if topology, table_name, or column_name does not exist, or if topo_geometry_layer_type is not one of the supported values.

Examples

The following example adds a topology geometry layer to the CITY_DATA topology. The topology geometry layer consists of polygon geometries in the FEATURE column of the LAND_PARCELS table. (The example refers to definitions and data from Section 1.11.)

EXECUTE SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('CITY_DATA', 'LAND_PARCELS', 'FEATURE', 'POLYGON');

SDO_TOPO.CREATE_TOPOLOGY

Format

SDO_TOPO.CREATE_TOPOLOGY(

topology IN VARCHAR2,

tolerance IN NUMBER,

srid IN NUMBER DEFAULT NULL,

node_table_storage IN VARCHAR2 DEFAULT NULL,

edge_table_storage IN VARCHAR2 DEFAULT NULL,

face_table_storage IN VARCHAR2 DEFAULT NULL,

history_table_storage IN VARCHAR2 DEFAULT NULL);

Description

Creates a topology.

Parameters

topology: Name of the topology to be created. Must not exceed 20 characters.
tolerance: Tolerance value associated with topology geometries in the topology. (Tolerance is explained in Chapter 1 of Oracle Spatial User's Guide and Reference.) Oracle Spatial uses the tolerance value in building R-tree indexes on the node, edge, and face tables; the value is also used for any spatial queries that use these tables.
srid: Coordinate system (spatial reference system) associated with all topology geometry layers in the topology. The default is null: no coordinate system is associated; otherwise, it must be a value from the SRID column of the MDSYS.CS_SRS table (described in Oracle Spatial User's Guide and Reference).
node_table_storage: Physical storage parameters used internally to create the <topology-name>_NODE$ table (described in Section 1.5.2). Must be a valid string for use with the CREATE TABLE statement. For example: TABLESPACE tbs_3 STORAGE (INITIAL 100K NEXT 200K). If you do not specify this parameter, the default physical storage values are used.
edge_table_storage: Physical storage parameters used internally to create the <topology-name>_EDGE$ table (described in Section 1.5.1). Must be a valid string for use with the CREATE TABLE statement. For example: TABLESPACE tbs_3 STORAGE (INITIAL 100K NEXT 200K). If you do not specify this parameter, the default physical storage values are used.
face_table_storage: Physical storage parameters used internally to create the <topology-name>_FACE$ table (described in Section 1.5.3). Must be a valid string for use with the CREATE TABLE statement. For example: TABLESPACE tbs_3 STORAGE (INITIAL 100K NEXT 200K). If you do not specify this parameter, the default physical storage values are used.
history_table_storage: Physical storage parameters used internally to create the <topology-name>_HISTORY$ table (described in Section 1.5.5. Must be a valid string for use with the CREATE TABLE statement. For example: TABLESPACE tbs_3 STORAGE (INITIAL 100K NEXT 200K). If you do not specify this parameter, the default physical storage values are used.

Usage Notes

This procedure creates the <topology-name>_EDGE$, <topology-name>_NODE$, <topology-name>_FACE$, and <topology-name>_HISTORY$ tables, which are described in Section 1.5. This procedure also creates the metadata for the topology.

An exception is raised if the topology already exists.

Examples

The following example creates a topology named CITY_DATA. The spatial geometries in this topology have a tolerance value of 0.5 and use the WGS 84 coordinate system (longitude and latitude, SRID value 8307). (The example refers to definitions and data from Section 1.11.)

EXECUTE SDO_TOPO.CREATE_TOPOLOGY('CITY_DATA', 0.5, 8307);

SDO_TOPO.DELETE_TOPO_GEOMETRY_LAYER

Format

SDO_TOPO.DELETE_TOPO_GEOMETRY_LAYER(

topology IN VARCHAR2,

table_name IN VARCHAR2,

column_name IN VARCHAR2);

Description

Deletes a topology geometry layer from a topology.

Parameters

topology: Topology from which to delete the topology geometry layer containing the topology geometries in the specified column. The topology must have been created using the SDO_TOPO.CREATE_TOPOLOGY procedure.
table_name: Name of the table containing the column specified in column_name.
column_name: Name of the column containing the topology geometries in the topology geometry layer to be deleted from the topology.

Usage Notes

This procedure deletes data associated with the specified topology geometry layer from the <topology-name>_RELATION$ table (described in Section 1.5.4). If this procedure is deleting the only remaining topology geometry layer from the topology, it also deletes the <topology-name>_RELATION$ table.

Examples

The following example deletes the topology geometry layer that is based on the geometries in the FEATURE column of the LAND_PARCELS table from the topology named CITY_DATA. (The example refers to definitions and data from Section 1.11.)

EXECUTE SDO_TOPO.DELETE_TOPO_GEOMETRY_LAYER('CITY_DATA', 'LAND_PARCELS', 'FEATURE');

SDO_TOPO.DROP_TOPOLOGY

Format

SDO_TOPO.DROP_TOPOLOGY(

topology IN VARCHAR2);

Description

Deletes a topology.

Parameters

topology: Name of the topology to be deleted. The topology must have been created using the SDO_TOPO.CREATE_TOPOLOGY procedure.

Usage Notes

This procedure deletes the <topology-name>_EDGE$, <topology-name>_NODE$, <topology-name>_FACE$, and <topology-name>_HISTORY$ tables (described in Section 1.5).

An exception is raised if the topology contains any topology geometries from any topology geometry layers. If you encounter this exception, delete all topology geometry layers in the topology using the SDO_TOPO.DELETE_TOPO_GEOMETRY_LAYER procedure for each topology geometry layer, and then drop the topology.

Examples

The following example drops the topology named CITY_DATA. (The example refers to definitions and data from Section 1.11.)

EXECUTE SDO_TOPO.DROP_TOPOLOGY('CITY_DATA');

SDO_TOPO.GET_FACE_BOUNDARY

Format

SDO_TOPO.GET_FACE_BOUNDARY(

topology IN VARCHAR2,

face_id IN NUMBER,

all_edges IN VARCHAR2 DEFAULT 'FALSE'

) RETURN SDO_LIST_TYPE;

Description

Returns a list of the signed ID numbers of the edges for the specified face.

Parameters

topology: Name of the topology that contains the face. Must not exceed 20 characters.
face_id: Face ID value of the face.
all_edges: TRUE includes all edges in the face, including isolated edges and edges that intersect a point on an edge on the boundary of the face; FALSE (the default) includes only edges that constitute the boundary of the face. (See the examples for this function.)

Usage Notes

None.

Examples

The following examples return the ID numbers of the edges for the face whose face ID value is 1. The first example accepts the default value of 'FALSE' for the all_edges parameter. The second example specifies 'TRUE' for all_edges, and the list includes the ID numbers of the boundary edge and the two isolated edges on the face. (The examples refer to definitions and data from Section 1.11.)

-- Get the boundary of face with face_id 1.
SELECT SDO_TOPO.GET_FACE_BOUNDARY('CITY_DATA', 1) FROM DUAL;
 
SDO_TOPO.GET_FACE_BOUNDARY('CITY_DATA',1)                                       
--------------------------------------------------------------------------------
SDO_LIST_TYPE(1)                                                                
 
-- Specify 'TRUE' for the all_edges parameter.
SELECT SDO_TOPO.GET_FACE_BOUNDARY('CITY_DATA', 1, 'TRUE') FROM DUAL;
 
SDO_TOPO.GET_FACE_BOUNDARY('CITY_DATA',1,'TRUE')                                
--------------------------------------------------------------------------------
SDO_LIST_TYPE(1, -26, 25)

SDO_TOPO.GET_TOPO_OBJECTS

Format

SDO_TOPO.GET_TOPO_OBJECTS(

topology IN VARCHAR2,

geometry IN SDO_GEOMETRY

) RETURN SDO_TOPO_OBJECT_ARRAY;

SDO_TOPO.GET_TOPO_OBJECTS(

topology IN VARCHAR2,

topo_geometry_layer_id IN NUMBER,

topo_geometry_id IN NUMBER

) RETURN SDO_TOPO_OBJECT_ARRAY;

Description

Returns an array of SDO_TOPO_OBJECT objects that interact with a specified geometry object or topology geometry object.

Parameters

topology: Name of the topology that contains the face and the point. Must not exceed 20 characters.
geometry: Geometry object to be checked.
topo_geometry_layer_id: ID number of the topology geometry layer that contains the topology geometry object to be checked.
topo_geometry_id: ID number of the topology geometry object to be checked.

Usage Notes

The SDO_TOPO_OBJECT_ARRAY data type is described in Section 1.6.2.1.

Examples

The following example returns the topology geometry objects that interact with land parcel P2 in the CITY_DATA topology. (The example refers to definitions and data from Section 1.11.)

-- CITY_DATA layer, land parcels (topo_geometry_ layer_id = 1), 
-- parcel P2 (topo_geometry_id = 2)
SELECT SDO_TOPO.GET_TOPO_OBJECTS('CITY_DATA', 1, 2) FROM DUAL;
 
SDO_TOPO.GET_TOPO_OBJECTS('CITY_DATA',1,2)(TOPO_ID, TOPO_TYPE)                  
--------------------------------------------------------------------------------
SDO_TOPO_OBJECT_ARRAY(SDO_TOPO_OBJECT(9, 1), SDO_TOPO_OBJECT(10, 1), SDO_TOPO_OB
JECT(13, 1), SDO_TOPO_OBJECT(14, 1), SDO_TOPO_OBJECT(17, 1), SDO_TOPO_OBJECT(18,
 1), SDO_TOPO_OBJECT(6, 2), SDO_TOPO_OBJECT(7, 2), SDO_TOPO_OBJECT(8, 2), SDO_TO
PO_OBJECT(9, 2), SDO_TOPO_OBJECT(10, 2), SDO_TOPO_OBJECT(11, 2), SDO_TOPO_OBJECT
(12, 2), SDO_TOPO_OBJECT(13, 2), SDO_TOPO_OBJECT(14, 2), SDO_TOPO_OBJECT(17, 2),
 SDO_TOPO_OBJECT(18, 2), SDO_TOPO_OBJECT(19, 2), SDO_TOPO_OBJECT(20, 2), SDO_TOP
O_OBJECT(-6, 2), SDO_TOPO_OBJECT(-7, 2), SDO_TOPO_OBJECT(-8, 2), SDO_TOPO_OBJECT
(-9, 2), SDO_TOPO_OBJECT(-10, 2), SDO_TOPO_OBJECT(-11, 2), SDO_TOPO_OBJECT(-12, 
2), SDO_TOPO_OBJECT(-13, 2), SDO_TOPO_OBJECT(-14, 2), SDO_TOPO_OBJECT(-17, 2), S
DO_TOPO_OBJECT(-18, 2), SDO_TOPO_OBJECT(-19, 2), SDO_TOPO_OBJECT(-20, 2), SDO_TO
PO_OBJECT(-1, 3), SDO_TOPO_OBJECT(3, 3), SDO_TOPO_OBJECT(4, 3), SDO_TOPO_OBJECT(
5, 3), SDO_TOPO_OBJECT(6, 3), SDO_TOPO_OBJECT(7, 3), SDO_TOPO_OBJECT(8, 3))

SDO_TOPO.INITIALIZE_METADATA

Format

SDO_TOPO.INITIALIZE_METADATA(

topology IN VARCHAR2);

Description

Initializes the topology metadata: sets sequence information for the node, edge, and face tables, and creates (or re-creates) required indexes on these tables.

Parameters

topology: Name of the topology for which to initialize the sequences. The topology must have been created using the SDO_TOPO.CREATE_TOPOLOGY procedure.

Usage Notes

You should run this procedure after loading data into the node, edge, or face tables, to initialize the sequences for these tables with the highest ID values stored in those tables. This ensures that no attempt is made to reuse the unique ID values in these tables. (The node, edge, and face tables are described in Section 1.5.)

This procedure creates spatial indexes on the geometry or MBR geometry columns in the node, edge, and face tables. If the indexes were dropped before a bulk load operation, running this procedure after the bulk load will re-create these indexes.

Examples

The following example initializes the metadata for the topology named CITY_DATA. (The example refers to definitions and data from Section 1.11.)

EXECUTE SDO_TOPO.INITIALIZE_METADATA('CITY_DATA');