Skip Headers

Oracle® Spatial Topology and Network Data Models
10g Release 1 (10.1)
Part No. B10828-01
  Go To Table Of Contents
Contents
Go To Index
Index

Previous Next  

1 Topology Data Model Overview

The topology data model of Oracle Spatial lets you work with data about nodes, edges, and faces in a topology. For example, United States Census geographic data is provided in terms of nodes, chains, and polygons, and this data can be represented using the Spatial topology data model. You can store information about topological elements and geometry layers in Oracle Spatial tables and metadata views. You can then perform certain Spatial operations referencing the topological elements, for example, finding which chains (such as streets) have any spatial interaction with a specific polygon entity (such as a park).

This chapter describes the Spatial data structures and data types that support the topology data model, and what you need to do to populate and manipulate the structures. You can use this information to write a program to convert your topological data into formats usable with Spatial.

A demo procedure is provided that processes U.S. Census topological data for use with Spatial, although you must modify that procedure (or write your own) to process your own topological data for use with Spatial. For information about the demo files provided, see the files in the demos directory and its subdirectory hierarchy under your Spatial Topology Manager installation directory. For information about the spatial topology editor demo, see demos/Topology/Bulk-Load/README.


Note:

Although this chapter discusses some topology terms as they relate to Oracle Spatial, it assumes that you are familiar with basic topology concepts.

It also assumes that you are familiar with the main Spatial concepts, data types, and operations, as documented in Oracle Spatial User's Guide and Reference.


This chapter contains the following major sections:

1.1 Main Steps in Using Topology Data

This section summarizes the main steps for working with topological data in Oracle Spatial. It refers to important concepts, structures, and operations that are described in detail in other sections.

The main steps for working with topological data are as follows:

  1. Create the topology, using the SDO_TOPO.CREATE_TOPOLOGY procedure. This causes the <topology-name>_EDGE$, <topology-name>_NODE$, <topology-name>_FACE$, and <topology-name>_HISTORY$ tables to be created. (These tables are described in Section 1.5.1, Section 1.5.2, Section 1.5.3, and Section 1.5.5, respectively.)

  2. Load topology data into the node, edge, and face tables created in Step 1. This is typically done using a bulk-load utility, but it can be done using SQL INSERT statements.

  3. Create a feature table for each feature in the topology. For example, a city data topology might have separate feature tables for land parcels, streets, and traffic signs.

  4. Associate the feature tables with the topology, using the SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER procedure for each feature table. This causes the <topology-name>_RELATION$ table to be created. (This table is described in Section 1.5.4.)

  5. Initialize topology metadata, using the SDO_TOPO.INITIALIZE_METADATA procedure. (This procedure also creates spatial indexes on the <topology-name>_EDGE$, <topology-name>_NODE$, and <topology-name>_FACE$ tables, and B-tree indexes on the <topology-name>_EDGE$, <topology-name>_RELATION$, and <topology-name>_HISTORY$ tables.)

  6. Load the feature tables using the SDO_TOPO_GEOMETRY constructor. (This constructor is described in Section 1.6.2.)

  7. Query the topology data (for example, using SDO_ANYINTERACT operator).

  8. Optionally, edit topology data using the PL/SQL or Java application programming interfaces (APIs).

Section 1.11 contains a PL/SQL example that performs these main steps.

You can use the topology data model PL/SQL and Java APIs to update the topology (for example, to change the data about an edge, node, or face). The PL/SQL API for most editing operations is the SDO_TOPO_MAP package, which is documented in Chapter 4. The Java API is described in Section 1.8.1.

1.2 Topology Data Model Concepts

Topology is a branch of mathematics concerned with objects in space. Topological relationships include such relationships as contains, inside, covers, covered by, touch, and overlap with boundaries intersecting. Topological relationships remain constant when the coordinate space is deformed, such as by twisting or stretching. (Examples of relationships that are not topological include length of, distance between, and area of.)

The basic elements in a topology are its nodes, edges, and faces.

A node, represented by a point, can be isolated or it can be used to bound edges. Two or more edges meet at every non-isolated node. A node has a coordinate pair associated with it that describes the spatial location for that node. Examples of geographic entities that might be represented as nodes include start and end points of streets, places of historical interest, and airports (if the map scale is sufficiently large).

An edge is bounded by two nodes: the start (origin) node and the end (terminal) node. An edge has an associated geometric object, usually a coordinate string that describes the spatial representation of the edge. An edge may have several vertices making up a line string, circular arc string, or combination. Examples of geographic entities that might be represented as edges include segments of streets and rivers.

The order of the coordinates gives a direction to an edge, and direction is important in determining topological relationships. The positive direction agrees with the orientation of the underlying edge, and the negative direction reverses this orientation. Each orientation of an edge is referred to as a directed edge, and each directed edge is the mirror image of its other directed edge. The start node of the positive directed edge is the end node of the negative directed edge. An edge also lies between two faces and has references to both of them. Each directed edge contains a reference to the next edge in the contiguous perimeter of the face on its left side. A face, represented by a polygon, has a reference to one directed edge of its outer boundary. If any island nodes or island edges are present, it also has a reference to one directed edge on the boundary of each island. Examples of geographic entities that might be represented as faces include parks, lakes, counties, and states.

Figure 1-1 shows a simplified topology containing nodes, edges, and faces. The arrowheads on each edge indicate the positive direction of the edge (or, more precisely, the orientation of the underlying line string or curve geometry for positive direction of the edge).

Notes on Figure 1-1:

Figure 1-2 shows the same topology illustrated in Figure 1-1, but it adds a grid and unit numbers along the x-axis and y-axis. Figure 1-2 is useful for understanding the output of some of the examples in Chapter 3 and Chapter 4.

Figure 1-2 Simplified Topology, with Grid Lines and Unit Numbers

Description of topo_xy_axes.gif follows
Description of the illustration topo_xy_axes.gif

1.3 Topology Geometries and Layers

A topology geometry (also referred to as a feature) is a spatial representation of a real world object. For example, Main Street and Walden State Park might be the names of topology geometries. The geometry is stored as a set of topological elements (nodes, edges, and faces). Each topology geometry has a unique ID (assigned by Spatial when records are imported or loaded) associated with it.

A topology geometry layer is the collection of topology geometries of a specific type. For example, Streets might be the topology geometry layer that includes the Main Street topology geometry, and State Parks might be the topology geometry layer that includes the Walden State Park topology geometry. Each topology geometry layer has a unique ID (assigned by Spatial) associated with it. The data for each topology geometry layer is stored in a feature table. For example, a feature table named CITY_STREETS might contain information about all topology geometries (individual roads or streets) in the Streets topology geometry layer.

Each topology geometry (feature) is defined as an object of type SDO_TOPO_GEOMETRY (described in Section 1.6.1), which identifies the topology geometry type, topology geometry ID, topology geometry layer ID, and topology ID for the topology.

Topology metadata is automatically maintained by Spatial in the USER_SDO_TOPO_METADATA and ALL_SDO_TOPO_METADATA views, which are described in Section 1.7.2. The USER_SDO_TOPO_INFO and ALL_SDO_TOPO_INFO views (described in Section 1.7.1) contain a subset of this topology metadata.

1.3.1 Features and Topology Objects

Often, there are fewer features in a topology than there are nodes, edges, and faces. For example, a road feature may consist of many edges, an area feature such as a park may consist of many faces, and some nodes may not be associated with point features. Figure 1-3 shows point, line, and area features associated with the topology that was shown in Figure 1-1 in Section 1.2.

Figure 1-3 Features in a Topology

Description of topo_features.gif follows
Description of the illustration topo_features.gif

Figure 1-3 shows the following kinds of features in the topology:

  • Point features (traffic signs), shown as dark circles: S1, S2, S3, and S4

  • Linear features (roads or streets), shown as dashed lines: R1, R2, R3, and R4

  • Area features (land parcels), shown as rectangles: P1, P2, P3, P4, and P5

    Land parcel P5 does not include the shaded area within its area. (Specifically, P5 includes face F1 but not face F9. These faces are shown in Figure 1-1 in Section 1.2.)

Example 1-8 in Section 1.11 defines these features.

1.4 Topology Geometry Layer Hierarchy

In some topologies, the topology geometry layers (feature layers) have one or more parent-child relationships in a topology hierarchy. That is, the layer at the topmost level consists of features in its child layer at the next level down in the hierarchy; the child layer might consist of features in its child layer at the next layer farther down; and so on. For example, a land use topology might have the following topology geometry layers at different levels of hierarchy:

If the topology geometry layers in a topology have this hierarchical relationship, it is far more efficient if you model the layers as hierarchical than if you specify all topology geometry layers at a single level (that is, with no hierarchy). For example, it is more efficient to construct SDO_TOPO_GEOMETRY objects for counties by specifying only the tracts in the county than by specifying all land parcels in all block groups in all tracts in the county.

The lowest level (for the topology geometry layer containing the smallest kinds of features) in a hierarchy is level 0, and successive higher levels are numbered 1, 2, and so on. Topology geometry layers at adjacent levels of a hierarchy have a parent-child relationship. Each topology geometry layer at the higher level is the parent layer for one layer at the lower level, which is its child layer. A parent layer can have only one child layer, but a child layer can have one or more parent layers. Using the preceding example, the Counties layer can have only one child layer, Tracts; however, the Tracts layer could have parent layers named Counties and Water Districts (as long as each tract is in only one water district).


Note:

Topology geometry layer hierarchy is somewhat similar to network hierarchy, which is described in Section 6.5; however, there are significant differences, and you should not confuse the two. For example, the lowest topology geometry layer hierarchy level is 0, and the lowest network hierarchy level is 1; and in a topology geometry layer hierarchy each parent must have one child and each child can have many parents, while in a network hierarchy each parent can have many children and each child must have one parent.

Figure 1-4 shows the preceding example topology geometry layer hierarchy. Each level of the hierarchy shows the level number and the topology geometry layer in that level.

Figure 1-4 Topology Geometry Layer Hierarchy

Description of topo_hier.gif follows
Description of the illustration topo_hier.gif

To model topology geometry layers as hierarchical, specify the child layer in the child_layer_id parameter when you call the SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER procedure to add a parent topology geometry layer to the topology. Add the lowest-level (level 0) topology geometry layer first; then add the level 1 layer, specifying the level 0 layer as its child; then add the level 2 layer, specifying the level 1 layer as its child; and so on. Example 1-1 shows five topology geometry layers being added so that the 5-level hierarchy is established.

Example 1-1 Modeling a Topology Geometry Layer Hierarchy

-- Create the topology. (Null SRID in this example.)
EXECUTE SDO_TOPO.CREATE_TOPOLOGY('LAND_USE_HIER', 0.00005);
 
-- Create feature tables.
CREATE TABLE land_parcels ( -- Land parcels (selected faces)
  feature_name VARCHAR2(30) PRIMARY KEY,
  feature SDO_TOPO_GEOMETRY);
 
CREATE TABLE block_groups (
  feature_name VARCHAR2(30) PRIMARY KEY,
  feature SDO_TOPO_GEOMETRY);
 
CREATE TABLE tracts (
  feature_name VARCHAR2(30) PRIMARY KEY,
  feature SDO_TOPO_GEOMETRY);
 
CREATE TABLE counties (
  feature_name VARCHAR2(30) PRIMARY KEY,
  feature SDO_TOPO_GEOMETRY);
 
CREATE TABLE states (
  feature_name VARCHAR2(30) PRIMARY KEY,
  feature SDO_TOPO_GEOMETRY);
 
-- (Other steps not shown here, such as populating the feature tables
-- and initializing the metadata.)
   .
   .
   .
-- Associate feature tables with the topology; include hierarchy information.

DECLARE
  land_parcels_id NUMBER;
  block_groups_id NUMBER;
  tracts_id NUMBER;
  counties_id NUMBER;
BEGIN
SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('LAND_USE_HIER', 'LAND_PARCELS',
  'FEATURE','POLYGON');
SELECT tg_layer_id INTO land_parcels_id FROM user_sdo_topo_info 
  WHERE topology = 'LAND_USE_HIER' AND table_name = 'LAND_PARCELS';
SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('LAND_USE_HIER', 'BLOCK_GROUPS',
  'FEATURE','POLYGON', NULL, land_parcels_id);
SELECT tg_layer_id INTO block_groups_id FROM user_sdo_topo_info 
  WHERE topology = 'LAND_USE_HIER' AND table_name = 'BLOCK_GROUPS';
SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('LAND_USE_HIER', 'TRACTS',
  'FEATURE','POLYGON', NULL, block_groups_id);
SELECT tg_layer_id INTO tracts_id FROM user_sdo_topo_info 
  WHERE topology = 'LAND_USE_HIER' AND table_name = 'TRACTS';
SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('LAND_USE_HIER', 'COUNTIES',
  'FEATURE','POLYGON', NULL, tracts_id);
SELECT tg_layer_id INTO counties_id FROM user_sdo_topo_info 
  WHERE topology = 'LAND_USE_HIER' AND table_name = 'COUNTIES';
SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('LAND_USE_HIER', 'STATES',
  'FEATURE','POLYGON', NULL, counties_id);
END;/
 

To insert or update topology geometry objects in feature tables for parent levels in a hierarchy, use the forms of the SDO_TOPO_GEOMETRY constructor that include attributes of type SDO_TGL_OBJECT_ARRAY (as opposed to SDO_TOPO_OBJECT_ARRAY). Feature tables are described in Section 1.3, and SDO_TOPO_GEOMETRY constructors are described in Section 1.6.2.


Note:

The TOPO_ID and TOPO_TYPE attributes in the relationship information table have special meanings when applied to parent layers in a topology with a topology geometry layer hierarchy. See the explanations of these attributes in Table 1-5 in Section 1.5.4.

1.5 Topology Data Model Tables

To use the Spatial topology capabilities, you must first insert data into special edge, node, and face tables, which are created by Spatial when you create a topology. The edge, node, and face tables are described in Section 1.5.1, Section 1.5.2, and Section 1.5.3, respectively.

Spatial automatically maintains a relationship information (<topology-name>_RELATION$) table for each topology, which is created the first time that a feature table is associated with a topology (that is, at the first call to the SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER procedure that specifies the topology). The relationship information table is described in Section 1.5.4.

Figure 1-5 shows the role of the relationship information table in connecting information in a feature table with information in its associated node, edge, or face table.

Figure 1-5 Mapping Between Feature Tables and Topology Tables

Description of feat_topo_rel.gif follows
Description of the illustration feat_topo_rel.gif

As shown in Figure 1-5, the mapping between feature tables and the topology node, edge, and face tables occurs through the <topology-name>_RELATION$ table. In particular:

1.5.1 Edge Information Table

You must store information about the edges in a topology in the <topology-name>_EDGE$ table, where <topology-name> is the name of the topology as specified in the call to the SDO_TOPO.CREATE_TOPOLOGY procedure. Each edge information table has the columns shown in Table 1-1.

Table 1-1 Columns in the <topology-name>_EDGE$ Table

Column Name Data Type Description
EDGE_ID NUMBER Unique ID number for this edge.
START_NODE_ID NUMBER ID number of the start node for this edge.
END_NODE_ID NUMBER ID number of the end node for this edge.
NEXT_LEFT_EDGE_ID NUMBER ID number (signed) of the next left edge for this edge.
PREV_LEFT_EDGE_ID NUMBER ID number (signed) of the previous left edge for this edge.
NEXT_RIGHT_EDGE_ID NUMBER ID number (signed) of the next right edge for this edge.
PREV_RIGHT_EDGE_ID NUMBER ID number (signed) of the previous right edge for this edge.
LEFT_FACE_ID NUMBER ID number of the left face for this edge.
RIGHT_FACE_ID NUMBER ID number of the right face for this edge.
GEOMETRY SDO_GEOMETRY Geometry object (line string) representing this edge.

Figure 1-6 shows nodes, edges, and faces that illustrate the relationships among the various ID columns in the edge information table. (In Figure 1-6, thick lines show the edges, and thin lines with arrowheads show the direction of each edge.)

Figure 1-6 Nodes, Edges, and Faces

Description of edge_table.gif follows
Description of the illustration edge_table.gif

Table 1-2 shows the ID column values in the edge information table for edges E4 and E8 in Figure 1-6. (For clarity, Table 1-2 shows ID column values with alphabetical characters, such as E4 and N1; however, the ID columns actually contain numeric values only, specifically the numeric ID value associated with each named object.)

Table 1-2 Edge Table ID Column Values

EDGE_ID START_NODE_ID END_NODE_ID NEXT_LEFT_EDGE_ID PREV_LEFT_EDGE_ID NEXT_RIGHT_EDGE_ID PREV_RIGHT_EDGE_ID LEFT_FACE_ID RIGHT_FACE_ID
E4 N1 N2 -E5 E3 E2 -E6 F1 F2
E8 N4 N3 -E8 -E8 E8 E8 F2 F2

In Figure 1-6 and Table 1-2:

  • The start node and end node for edge E4 are N1 and N2, respectively. The next left edge for edge E4 is E5, but its direction is the opposite of edge E4, and therefore the next left edge for E4 is stored as -E5 (negative E5).

  • The previous left edge for edge E4 is E3, and because it has the same direction as edge E4, the previous left edge for E4 is stored as E3.

  • The next right face is determined using the negative directed edge of E4. This can be viewed as reversing the edge direction and taking the next left edge and previous left edge. In this case, the next right edge is E2 and the previous right edge is -E6 (the direction of edge E6 is opposite the negative direction of edge E4). For edge E4, the left face is F1 and the right face is F2.

  • Edges E1 and E7 are neither leftmost nor rightmost edges with respect to edge E4, and therefore they do not appear in the edge table row associated with edge E4.

1.5.2 Node Information Table

You must store information about the nodes in a topology in the <topology-name>_NODE$ table, where <topology-name> is the name of the topology as specified in the call to the SDO_TOPO.CREATE_TOPOLOGY procedure. Each node information table has the columns shown in Table 1-3.

Table 1-3 Columns in the <topology-name>_NODE$ Table

Column Name Data Type Description
NODE_ID NUMBER Unique ID number for this node.
EDGE_ID NUMBER ID number (signed) of the edge (if any) associated with this node.
FACE_ID NUMBER ID number of the face (if any) associated with this node.
GEOMETRY SDO_GEOMETRY Geometry object (point) representing this node.

1.5.3 Face Information Table

You must store information about the faces in a topology in the <topology-name>_FACE$ table, where <topology-name> is the name of the topology as specified in the call to the SDO_TOPO.CREATE_TOPOLOGY procedure. Each face information table has the columns shown in Table 1-4.

Table 1-4 Columns in the <topology-name>_FACE$ Table

Column Name Data Type Description
FACE_ID NUMBER Unique ID number for this face.
BOUNDARY_EDGE_ID NUMBER ID number of the boundary edge for this face. The sign of this number (which is ignored for use as a key) indicates which orientation is being used for this boundary component (positive numbers indicate the left of the edge, and negative numbers indicate the right of the edge).
ISLAND_EDGE_ID_LIST SDO_LIST_TYPE Island edges (if any) in this face. (The SDO_LIST_TYPE type is described in Section 1.6.4.)
ISLAND_NODE_ID_LIST SDO_LIST_TYPE Island nodes (if any) in this face. (The SDO_LIST_TYPE type is described in Section 1.6.4.)
MBR_GEOMETRY SDO_GEOMETRY Minimum bounding rectangle (MBR) that encloses this face. (This is not required. However, if the MBR is specified and if a spatial R-tree index is defined on this geometry, the face can be retrieved more efficiently.)

1.5.4 Relationship Information Table

As you work with topology objects, Spatial automatically maintains information about each object in <topology-name>_RELATION$ tables, where <topology-name> is the name of the topology and there is one such table for each topology. Each row in the table uniquely identifies a topology geometry with respect to its topology geometry layer and topology. Each relationship information table has the columns shown in Table 1-5.

Table 1-5 Columns in the <topology-name>_RELATION$ Table

Column Name Data Type Description
TG_LAYER_ID NUMBER ID number of the topology geometry layer to which the topology geometry belongs.
TG_ID NUMBER ID number of the topology geometry.
TOPO_ID NUMBER For a topology that does not have a topology geometry layer hierarchy or for the lowest level (level 0) in the hierarchy: ID number of a topological element in the topology geometry.

For a level higher than 0 in the hierarchy: level number in the hierarchy of the topology geometry layer.

TOPO_TYPE NUMBER For a topology that does not have a topology geometry layer hierarchy or for the lowest level (level 0) in the hierarchy: type of topology: 1 = node, 2 = edge, 3 = face.

For a level higher than 0 in the hierarchy: ID number of a topological element in the topology geometry.

TOPO_ATTRIBUTE VARCHAR2 Reserved for Oracle use.

1.5.5 History Information Table

When a topology editing operation causes an insert or delete operation on an edge or face information table, Spatial automatically maintains information about these operations in <topology-name>_HISTORY$ tables, where <topology-name> is the name of the topology and there is one such table for each topology. Each row in the table uniquely identifies an editing operation on a topology object. (Topology editing is discussed in Chapter 2.) Each history information table has the columns shown in Table 1-6.

Table 1-6 Columns in the <topology-name>_HISTORY$ Table

Column Name Data Type Description
TOPO_TX_ID NUMBER ID number of the transaction that was started by a call to the SDO_TOPO_MAP.LOAD_TOPO_MAP function or to the loadWindow or loadTopology Java method. Each transaction can consist of several editing operations.
TOPO_SEQUENCE NUMBER Sequence number assigned to an editing operation within the transaction.
TOPOLOGY VARCHAR2 Name of the topology containing the objects being edited.
TOPO_ID NUMBER ID number of a topological element in the topology geometry.
TOPO_TYPE NUMBER Type of topology: 1 = node, 2 = edge, 3 = face.
TOPO_OP VARCHAR2 Type of editing operation that was performed on the topology object: I for insert or D for delete.
PARENT_ID NUMBER For an insert operation, the ID of the parent topological element from which the current topological element is derived; for a delete operation, the ID of the resulting topological element.

Consider the following examples:

  • Adding a node to break edge E2, generating edge E3: The TOPO_ID value of the new edge is the ID of E3, the TOPO_TYPE value is 2, the PARENT_ID value is the ID of E2, and the TOPO_OP value is I.

  • Deleting a node to merge edges E6 and E7, resulting in E7: The TOPO_ID value is the ID of E6, the TOPO_TYPE value is 2, the PARENT_ID value is the ID of E7, and the TOPO_OP value is D.

1.6 Topology Data Types

The main data type associated with the topology data model is SDO_TOPO_GEOMETRY, which describes a topology geometry. The SDO_TOPO_GEOMETRY type has several constructors and one member function. This section describes the topology model types, constructors, and member functions.

1.6.1 SDO_TOPO_GEOMETRY Type

The description of a topology geometry is stored in a single row, in a single column of object type SDO_TOPO_GEOMETRY in a user-defined table. The object type SDO_TOPO_GEOMETRY is defined as:

CREATE TYPE sdo_topo_geometry AS OBJECT
  (tg_type      NUMBER,
   tg_id        NUMBER,
   tg_layer_id  NUMBER,
   topology_id  NUMBER);

The SDO_TOPO_GEOMETRY type has the attributes shown in Table 1-7.

Table 1-7 SDO_TOPO_GEOMETRY Type Attributes

Attribute Explanation
TG_TYPE Type of topology geometry: 1 = point, 2 = line string, 3 = polygon or multipolygon, 4 = heterogeneous collection. Note: Most real world topology geometries are one of the multi types.
TG_ID Unique ID number (generated by Spatial) for the topology geometry.
TG_LAYER_ID ID number for the topology geometry layer to which the topology geometry belongs. (This number is generated by Spatial, and it is unique within the topology geometry layer.)
TOPOLOGY_ID Unique ID number (generated by Spatial) for the topology.

Each topology geometry in a topology is uniquely identified by the combination of its TG_ID and TG_LAYER_ID values.

You can use an attribute name in a query on an object of SDO_TOPO_GEOMETRY. Example 1-2 shows SELECT statements that query each attribute of the FEATURE column of the CITY_STREETS table, which is defined in Example 1-8 in Section 1.11.

Example 1-2 SDO_TOPO_GEOMETRY Attributes in Queries

SELECT s.feature.tg_type FROM city_streets s;
SELECT s.feature.tg_id FROM city_streets s;
SELECT s.feature.tg_layer_id FROM city_streets s;
SELECT s.feature.topology_id FROM city_streets s;

1.6.2 SDO_TOPO_GEOMETRY Constructors

The SDO_TOPO_GEOMETRY type has constructors for inserting and updating topology geometry objects. The constructor format to use for either type of operation (insert or update) depends on whether or not the operation affects a parent level in a topology geometry layer hierarchy:

  • To insert and update topology geometry objects when the topology does not have a topology geometry layer hierarchy or when the operation affects the lowest level (level 0) in the hierarchy, use constructors that specify the lowest-level topology objects (nodes, edges, and faces). These constructors have at least one attribute of type SDO_TOPO_OBJECT_ARRAY and no attributes of type SDO_TGL_OBJECT_ARRAY. (Topology geometry layer hierarchy is explained in Section 1.4.)

  • To insert and update topology geometry layers when the topology has a topology geometry layer hierarchy and the operation affects a level other than the lowest in the hierarchy, use constructors that specify elements in the child level. These constructors have at least one attribute of type SDO_TGL_OBJECT_ARRAY and no attributes of type SDO_TOPO_OBJECT_ARRAY.

For specifying either lowest-level objects or child-level objects, there are two constructors for insert operations and two constructors for update operations. For each type of operation (insert or update), one constructor format specifies the topology geometry layer by its ID value and the other format specifies the layer by the combination of table name and column name.

This section describes the available SDO_TOPO_GEOMETRY constructors.

1.6.2.1 Constructors for Insert Operations into the Lowest Level

The SDO_TOPO_GEOMETRY type has the following constructors that you can use in INSERT statements to create new topology geometry objects when the topology does not have a topology geometry layer hierarchy or when the operation affects the lowest level (level 0) in the hierarchy:

SDO_TOPO_GEOMETRY (topology     VARCHAR2,
                   tg_type      NUMBER,
                   tg_layer_id  NUMBER,
                   topo_ids     SDO_TOPO_OBJECT_ARRAY)

SDO_TOPO_GEOMETRY (topology      VARCHAR2,
                   table_name    VARCHAR2,
                   column_name   VARCHAR2,
                   tg_type       NUMBER,
                   topo_ids      SDO_TOPO_OBJECT_ARRAY)

The SDO_TOPO_OBJECT_ARRAY type is defined as a VARRAY of SDO_TOPO_OBJECT objects.

The SDO_TOPO_OBJECT type has the following two attributes:

(topo_id NUMBER, topo_type NUMBER)

The TG_TYPE and TOPO_IDS attribute values must be within the range of values from the <topology-name>_RELATION$ table (described in Section 1.5.4) for the specified topology.

Example 1-3 shows two SDO_TOPO_GEOMETRY constructors, one in each format. Each constructor inserts a topology geometry into the LAND_PARCELS table, which is defined in Example 1-8 in Section 1.11.

Example 1-3 INSERT Using Constructor with SDO_TOPO_OBJECT_ARRAY

INSERT INTO land_parcels VALUES ('P1', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    3, -- Topology geometry type (polygon/multipolygon)
    1, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (3, 3), -- face_id = 3
      SDO_TOPO_OBJECT (6, 3))) -- face_id = 6
);

INSERT INTO land_parcels VALUES ('P1A', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    'LAND_PARCELS', -- Table name
    'FEATURE', -- Column name
    3, -- Topology geometry type (polygon/multipolygon)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (3, 3), -- face_id = 3
      SDO_TOPO_OBJECT (6, 3))) -- face_id = 6
);

1.6.2.2 Constructors for Insert Operations into a Parent Level

The SDO_TOPO_GEOMETRY type has the following constructors that you can use in INSERT statements into a feature table associated with a parent level in a topology that has a topology geometry layer hierarchy:

SDO_TOPO_GEOMETRY (topology     VARCHAR2,
                   tg_type      NUMBER,
                   tg_layer_id  NUMBER,
                   topo_ids     SDO_TGL_OBJECT_ARRAY)

SDO_TOPO_GEOMETRY (topology      VARCHAR2,
                   table_name    VARCHAR2,
                   column_name   VARCHAR2,
                   tg_type       NUMBER,
                   topo_ids      SDO_TGL_OBJECT_ARRAY)

The SDO_TGL_OBJECT_ARRAY type is defined as a VARRAY of SDO_TGL_OBJECT objects.

The SDO_TGL_OBJECT type has the following two attributes:

(tgl_id NUMBER, tg_id NUMBER)

Example 1-4 shows an SDO_TOPO_GEOMETRY constructor that inserts a row into the BLOCK_GROUPS table, which is the feature table for the Block Groups level in the topology geometry layer hierarchy. The Block Groups level is the parent of the Land Parcels level at the bottom of the hierarchy.

Example 1-4 INSERT Using Constructor with SDO_TGL_OBJECT_ARRAY

INSERT INTO block_groups VALUES ('BG1', -- Feature name
  SDO_TOPO_GEOMETRY('LAND_USE_HIER',
    3, -- Topology geometry type (polygon/multipolygon)
    2, -- TG_LAYER_ID for block groups (from ALL_SDO_TOPO_METADATA)
    SDO_TGL_OBJECT_ARRAY (
      SDO_TGL_OBJECT (1, 1), -- land parcel ID = 1
      SDO_TGL_OBJECT (12, 2))) -- land parcel ID = 2
);

1.6.2.3 Constructors for Update Operations into the Lowest Level

The SDO_TOPO_GEOMETRY type has the following constructors that you can use in UPDATE statements to modify existing topology geometry objects when the topology does not have a topology geometry layer hierarchy or when the operation affects the lowest level (level 0) in the hierarchy:

SDO_TOPO_GEOMETRY (topology         VARCHAR2,
                   tg_type          NUMBER,
                   tg_layer_id      NUMBER,
                   add_topo_ids     SDO_TOPO_OBJECT_ARRAY,
                   delete_topo_ids  SDO_TOPO_OBJECT_ARRAY)

SDO_TOPO_GEOMETRY (topology         VARCHAR2,
                   table_name       VARCHAR2,
                   column_name      VARCHAR2,
                   tg_type          NUMBER,
                   add_topo_ids     SDO_TOPO_OBJECT_ARRAY,
                   delete_topo_ids  SDO_TOPO_OBJECT_ARRAY)

For example, you could use one of these constructor formats to add an edge to a linear feature or to remove an obsolete edge from a feature.

The SDO_TOPO_OBJECT_ARRAY type definition and the requirements for the TG_TYPE and TOPO_IDS attribute values are as described in Section 1.6.2.1.

You can specify values for both the ADD_TOPO_IDS and DELETE_TOPO_IDS attributes, or you can specify values for one attribute and specify the other as null; however, you cannot specify null values for both ADD_TOPO_IDS and DELETE_TOPO_IDS.

Example 1-5 shows two SDO_TOPO_GEOMETRY constructors, one in each format. Each constructor removes two faces from the CITY_DATA topology in the LAND_PARCELS table, which is defined in Example 1-8 in Section 1.11.

Example 1-5 UPDATE Using Constructor with SDO_TOPO_OBJECT_ARRAY

UPDATE land_parcels l SET l.feature = SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    3, -- Topology geometry type (polygon/multipolygon)
    1, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    NULL, -- No topology objects to be added
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (3, 3), -- face_id = 3
      SDO_TOPO_OBJECT (6, 3))) -- face_id = 6
WHERE l.feature_name = 'P1';
 
UPDATE land_parcels l SET l.feature = SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    'LAND_PARCELS', -- Table name
    'FEATURE', -- Column name
    3, -- Topology geometry type (polygon/multipolygon)
    NULL, -- No topology objects to be added
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (3, 3), -- face_id = 3
      SDO_TOPO_OBJECT (6, 3))) -- face_id = 6
WHERE l.feature_name = 'P1A';

1.6.2.4 Constructors for Update Operations into a Parent Level

The SDO_TOPO_GEOMETRY type has the following constructors that you can use in UPDATE statements affecting a feature table associated with a parent level in a topology that has a topology geometry layer hierarchy:

SDO_TOPO_GEOMETRY (topology         VARCHAR2,
                   tg_type          NUMBER,
                   tg_layer_id      NUMBER,
                   add_topo_ids     SDO_TGL_OBJECT_ARRAY,
                   delete_topo_ids  SDO_TGL_OBJECT_ARRAY)

SDO_TOPO_GEOMETRY (topology         VARCHAR2,
                   table_name       VARCHAR2,
                   column_name      VARCHAR2,
                   tg_type          NUMBER,
                   add_topo_ids     SDO_TGL_OBJECT_ARRAY,
                   delete_topo_ids  SDO_TGL_OBJECT_ARRAY)

For example, you could use one of these constructor formats to add an edge to a linear feature or to remove an obsolete edge from a feature.

The SDO_TGL_OBJECT_ARRAY type definition and the requirements for its attribute values are as described in Section 1.6.2.2.

You can specify values for both the ADD_TOPO_IDS and DELETE_TOPO_IDS attributes, or you can specify values for one attribute and specify the other as null; however, you cannot specify null values for both ADD_TOPO_IDS and DELETE_TOPO_IDS.

Example 1-6 shows two SDO_TOPO_GEOMETRY constructors, one in each format. Each constructor deletes the land parcel with the ID value of 2 from two features (named BG1 and BG1A and that have the same definition) from the CITY_DATA topology in the BLOCK_GROUPS table, which is the feature table for the Block Groups level in the topology geometry layer hierarchy. The Block Groups level is the parent of the Land Parcels level at the bottom of the hierarchy.

Example 1-6 UPDATE Using Constructor with SDO_TGL_OBJECT_ARRAY

UPDATE block_groups b SET b.feature = SDO_TOPO_GEOMETRY(
  'LAND_USE_HIER',
  3, -- Topology geometry type (polygon/multipolygon)
  2, -- TG_LAYER_ID for block groups (from ALL_SDO_TOPO_METADATA)
  null, -- No IDs to add
  SDO_TGL_OBJECT_ARRAY (
    SDO_TGL_OBJECT (1, 2)) -- land parcel ID = 2
  )
WHERE b.feature_name = 'BG1';
 
UPDATE block_groups b SET b.feature = SDO_TOPO_GEOMETRY(
  'LAND_USE_HIER',
  'BLOCK_GROUPS', -- Feature table
  'FEATURE', -- Feature column
  3, -- Topology geometry type (polygon/multipolygon)
  null, -- No IDs to add
  SDO_TGL_OBJECT_ARRAY (
    SDO_TGL_OBJECT (1, 2)) -- land parcel ID = 2
  )
WHERE b.feature_name = 'BG1A';

1.6.3 GET_GEOMETRY Member Function

The SDO_TOPO_GEOMETRY type has a member function GET_GEOMETRY, which you can use to return the SDO_GEOMETRY object for the topology geometry object.

Example 1-7 uses the GET_GEOMETRY member function to return the SDO_GEOMETRY object for the topology geometry object associated with the land parcel named P1.

Example 1-7 GET_GEOMETRY Member Function

SELECT l.feature_name, l.feature.get_geometry()
  FROM land_parcels l WHERE l.feature_name = 'P1';
 
FEATURE_NAME                                                                    
------------------------------                                                  
L.FEATURE.GET_GEOMETRY()(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO,
--------------------------------------------------------------------------------
P1                                                                              
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 3, 1), SDO_ORDINATE_ARRAY(
21, 14, 21, 22, 9, 22, 9, 14, 9, 6, 21, 6, 21, 14)) 

1.6.4 SDO_LIST_TYPE Type

The SDO_LIST_TYPE type is used to store the EDGE_ID values of island edges and NODE_ID values of island nodes in a face. The SDO_LIST_TYPE type is defined as:

CREATE TYPE sdo_list_type as VARRAY(2147483647) OF NUMBER;

1.6.5 SDO_EDGE_ARRAY and SDO_NUMBER_ARRAY Types

The SDO_EDGE_ARRAY type is used to specify the coordinates of attached edges affected by a node move operation. The SDO_EDGE_ARRAY type is defined as:

CREATE TYPE sdo_edge_array as VARRAY(1000000) OF MDSYS.SDO_NUMBER_ARRAY;

The SDO_NUMBER_ARRAY type is a general-purpose type used by Spatial for arrays. The SDO_NUMBER_ARRAY type is defined as:

CREATE TYPE sdo_number_array as VARRAY(1048576) OF NUMBER;

1.7 Topology Metadata Views

There are two sets of topology metadata views for each schema (user): xxx_SDO_TOPO_INFO and xxx_SDO_TOPO_METADATA, where xxx can be USER or ALL. These views are read-only to users; they are created and maintained by Spatial.

The xxx_SDO_TOPO_METADATA views contain the most detailed information, and each xxx_SDO_TOPO_INFO view contains a subset of the information in its corresponding xxx_SDO_TOPO_METADATA view.

1.7.1 xxx_SDO_TOPO_INFO Views

The following views contain basic information about topologies:

  • USER_SDO_TOPO_INFO contains topology information for all tables owned by the user.

  • ALL_SDO_TOPO_INFO contains topology information for all tables on which the user has SELECT permission.

The USER_SDO_TOPO_INFO and ALL_SDO_TOPO_INFO views contain the same columns, as shown Table 1-8. (The columns are listed in their order in the view definition.)

Table 1-8 Columns in the xxx_SDO_TOPO_INFO Views

Column Name Data Type Purpose
OWNER VARCHAR2 Owner of the topology.
TOPOLOGY VARCHAR2 Name of the topology.
TOPOLOGY_TYPE VARCHAR2 Contains PLANAR if the topology can have nodes, edges, and faces. (No other values are supported for the current release.)
TOLERANCE NUMBER Tolerance value associated with topology geometries in the topology. (Tolerance is explained in Chapter 1 of the 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 NUMBER Coordinate system (spatial reference system) associated with all topology geometry layers in the topology. Is null if no coordinate system is associated; otherwise, it contains a value from the SRID column of the MDSYS.CS_SRS table (described in Oracle Spatial User's Guide and Reference).
TABLE_SCHEMA VARCHAR2 Name of the schema that owns the table containing the topology geometry layer column.
TABLE_NAME VARCHAR2 Name of the table containing the topology geometry layer column.
COLUMN_NAME VARCHAR2 Name of the column containing the topology geometry layer data.
TG_LAYER_ID NUMBER ID number of the topology geometry layer.
TG_LAYER_TYPE VARCHAR2 Contains one of the following: POINT, LINE, CURVE, POLYGON, or COLLECTION.
TG_LAYER_LEVEL NUMBER Hierarchy level number of this topology geometry layer. (Topology geometry layer hierarchy is explained in Section 1.4.)
CHILD_LAYER_ID NUMBER ID number of the topology geometry layer that is the child layer of this layer in the topology geometry layer hierarchy. Null if this layer has no child layer or if the topology does not have a topology geometry layer hierarchy. (Topology geometry layer hierarchy is explained in Section 1.4.)

1.7.2 xxx_SDO_TOPO_METADATA Views

The following views contain detailed information about topologies:

  • USER_SDO_TOPO_METADATA contains topology information for all tables owned by the user.

  • ALL_SDO_TOPO_METADATA contains topology information for all tables on which the user has SELECT permission.

The USER_SDO_TOPO_METADATA and ALL_SDO_TOPO_METADATA views contain the same columns, as shown Table 1-9. (The columns are listed in their order in the view definition.)

Table 1-9 Columns in the xxx_SDO_TOPO_METADATA Views

Column Name Data Type Purpose
OWNER VARCHAR2 Owner of the topology.
TOPOLOGY VARCHAR2 Name of the topology.
TOPOLOGY_TYPE VARCHAR2 Contains PLANAR if the topology can have nodes, edges, and faces. (No other values are supported for the current release.)
TOLERANCE NUMBER 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 NUMBER Coordinate system (spatial reference system) associated with all topology geometry layers in the topology. Is null if no coordinate system is associated; otherwise, contains a value from the SRID column of the MDSYS.CS_SRS table (described in Oracle Spatial User's Guide and Reference).
TABLE_SCHEMA VARCHAR2 Name of the schema that owns the table containing the topology geometry layer column.
TABLE_NAME VARCHAR2 Name of the table containing the topology geometry layer column.
COLUMN_NAME VARCHAR2 Name of the column containing the topology geometry layer data.
TG_LAYER_ID NUMBER ID number of the topology geometry layer.
TG_LAYER_TYPE VARCHAR2 Contains one of the following: POINT, LINE, CURVE, or POLYGON.
TG_LAYER_LEVEL NUMBER Hierarchy level number of this topology geometry layer. (Topology geometry layer hierarchy is explained in Section 1.4.)
CHILD_LAYER_ID NUMBER ID number of the topology geometry layer that is the child layer of this layer in the topology geometry layer hierarchy. Null if this layer has no child layer or if the topology does not have a geometry layer hierarchy. (Topology geometry layer hierarchy is explained in Section 1.4.)
NODE_SEQUENCE VARCHAR2 Name of the sequence containing the next available node ID number.
EDGE_SEQUENCE VARCHAR2 Name of the sequence containing the next available edge ID number.
FACE_SEQUENCE VARCHAR2 Name of the sequence containing the next available face ID number.
TG_SEQUENCE VARCHAR2 Name of the sequence containing the next available topology geometry ID number.

1.8 Topology Application Programming Interface

The topology data model application programming interface (API) consists of the following:

1.8.1 Topology Data Model Java Interface

The Java client interface for the topology data model consists of the following classes:

  • TopoMap: class that stores edges, nodes, and faces, and provides methods for adding and deleting elements while maintaining topological consistency both in the cache and in the underlying database tables

  • Edge: class for an edge

  • Face: class for a face

  • Node: class for a node

  • Point2DD: class for a point

  • IntArrayList: class for the int data type

  • InvalidTopoOperationException: class for the invalid topology operation exception

  • TopoValidationException: class for the topology validation failure exception

  • TopoEntityNotFoundException: class for the entity not found exception

For detailed reference information about the topology data model classes, as well as some usage information about the Java API, see the Javadoc-generated API documentation: open index.html in a directory that includes the path sdotopo/doc/javadoc.

1.9 Exporting and Importing Topology Data

To export topology data from one database and import it into another database, follow the steps in this section.

In the database with the topology data to be exported, export the topology data by exporting all topology tables, including the feature tables.

In the database into which to import the topology data:

  1. Import the tables from the .dmp file that you created when you exported the topology data.

  2. Call the SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER procedure to add each topology geometry layer to the topology.

  3. Call the SDO_TOPO.INITIALIZE_METADATA procedure to initialize the topology metadata.

1.10 Function-Based Indexes Not Supported

You cannot create a function-based index on a column of type SDO_TOPO_GEOMETRY. (Function-based indexes are explained in Oracle Database Application Developer's Guide - Fundamentals and Oracle Database Administrator's Guide.)

1.11 Topology Example (PL/SQL)

This section presents a simplified PL/SQL example that performs topology data model operations. It refers to concepts that are explained in this chapter. It uses SDO_TOPO functions and procedures, which are documented in Chapter 3, and the SDO_ANYINTERACT operator, which is documented in Chapter 5.

Example 1-8 uses the topology shown in Figure 1-1 in Section 1.2, and the features shown in Figure 1-3 in Section 1.3.1.

Example 1-8 Topology Example (PL/SQL)

------------------------------
-- Main steps for using the topology data model
------------------------------
-- 1. Create a topology.
-- 2. Load (normally bulk-load) topology data (node, edge, and face tables).
-- 3. Create feature tables.
-- 4. Associate feature tables with the topology.
-- 5. Initialize topology metadata.
-- 6. Load feature tables using the SDO_TOPO_GEOMETRY constructor.
-- 7. Query the data.
-- 8. Optionally, edit data using the Java API.
 
-- 1. Create the topology. (Null SRID in this example.)
EXECUTE SDO_TOPO.CREATE_TOPOLOGY('CITY_DATA', 0.00005);
 
-- 2. Load topology data (node, edge, and face tables).
--  Use INSERT statements here instead of a bulk-load utility.
 
-- 2A. Insert data into <topology_name>_EDGE$ table.
 
-- E1
INSERT INTO city_data_edge$ VALUES(1, 1, 1, 1, 1, -1, -1, 1, -1,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(8,30, 16,30, 16,38, 3,38, 3,30, 8,30)));
-- E2
INSERT INTO city_data_edge$ VALUES(2, 2, 2, 3, -3, -2, -2, 2, -1,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(25,30, 31,30, 31,40, 17,40, 17,30, 25,30)));
-- E3
INSERT INTO city_data_edge$ VALUES(3, 2, 3, -3, 2, 2, 3, 2, 2,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(25,30, 25,35)));
-- E4
INSERT INTO city_data_edge$ VALUES(4, 5, 6, -5, -4, 4, 5, -1, -1,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(36,38, 38,35, 41,34, 42,33, 45,32, 47,28, 50,28, 52,32,
57,33)));
-- E5
INSERT INTO city_data_edge$ VALUES(5, 7, 6, -4, -5, 5, 4, -1, -1,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(41,40, 45,40, 47,42, 62,41, 61,38, 59,39, 57,36,
57,33)));
-- E6
INSERT INTO city_data_edge$ VALUES(6, 16, 17, 7, 21, -21, 19, -1, 3,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(9,22, 21,22)));
-- E7
INSERT INTO city_data_edge$ VALUES(7, 17, 18, 8, 6, -19, 17, -1, 4,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(21,22, 35,22)));
-- E8
INSERT INTO city_data_edge$ VALUES(8, 18, 19, -15, 7, -17, 15, -1, 5,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(35,22, 47,22)));
-- E9
INSERT INTO city_data_edge$ VALUES(9, 15, 14, 19, -21, -22, 20, 3, 6,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(9,14, 21,14)));
-- E10
INSERT INTO city_data_edge$ VALUES(10, 13, 14, -20, 18, 17, -19, 7, 4,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(35,14, 21,14)));
-- E11
INSERT INTO city_data_edge$ VALUES(11, 13, 12, 15, -17, -18, 16, 5, 8,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(35,14, 47,14)));
-- E12
INSERT INTO city_data_edge$ VALUES(12, 8, 9, 20, -22, 22, -13, 6, -1,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(9,6, 21,6)));
-- E13
INSERT INTO city_data_edge$ VALUES(13, 9, 10, 18, -20, -12, -14, 7, -1,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(21,6, 35,6)));
-- E14
INSERT INTO city_data_edge$ VALUES(14, 10, 11, 16, -18, -13, -16, 8, -1,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(35,6, 47,6)));
-- E15
INSERT INTO city_data_edge$ VALUES(15, 12, 19, -8, 11, -16, 8, 5, -1,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(47,14, 47,22)));
-- E16
INSERT INTO city_data_edge$ VALUES(16, 11, 12, -11, 14, -14, -15, 8, -1,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(47,6, 47,14)));
-- E17
INSERT INTO city_data_edge$ VALUES(17, 13, 18, -7, -10, 11, -8, 4, 5,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(35,14, 35,22)));
-- E18
INSERT INTO city_data_edge$ VALUES(18, 10, 13, 10, 13, 14, -11, 7, 8,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(35,6, 35,14)));
-- E19
INSERT INTO city_data_edge$ VALUES(19, 14, 17, -6, 9, -10, -7, 3, 4,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(21,14, 21,22)));
-- E20
INSERT INTO city_data_edge$ VALUES(20, 9, 14, -9, 12, 13, 10, 6, 7,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(21,6, 21,14)));
-- E21
INSERT INTO city_data_edge$ VALUES(21, 15, 16, 6, 22, 9, -6, -1, 3,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(9,14, 9,22)));
-- E22
INSERT INTO city_data_edge$ VALUES(22, 8, 15, 21, -12, 12, -9, -1, 6,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(9,6, 9,14)));
-- E25
INSERT INTO city_data_edge$ VALUES(25, 21, 22, -25, -25, 25, 25, 1, 1,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(9,35, 13,35)));
-- E26
INSERT INTO city_data_edge$ VALUES(26, 20, 20, 26, 26, -26, -26, 9, 1,
  SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
    SDO_ORDINATE_ARRAY(4,31, 7,31, 7,34, 4,34, 4,31)));
 
-- 2B. Insert data into <topology_name>_NODE$ table.
 
-- N1
INSERT INTO city_data_node$ VALUES(1, 1, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(8,30,NULL), NULL, NULL));
-- N2
INSERT INTO city_data_node$ VALUES(2, 2, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(25,30,NULL), NULL, NULL));
-- N3
INSERT INTO city_data_node$ VALUES(3, -3, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(25,35,NULL), NULL, NULL));
-- N4
INSERT INTO city_data_node$ VALUES(4, NULL, 2,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(20,37,NULL), NULL, NULL));
-- N5
INSERT INTO city_data_node$ VALUES(5, 4, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(36,38,NULL), NULL, NULL));
-- N6
INSERT INTO city_data_node$ VALUES(6, -4, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(57,33,NULL), NULL, NULL));
-- N7
INSERT INTO city_data_node$ VALUES(7, 5, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(41,40,NULL), NULL, NULL));
-- N8
INSERT INTO city_data_node$ VALUES(8, 12, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(9,6,NULL), NULL, NULL));
-- N9
INSERT INTO city_data_node$ VALUES(9, 20, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(21,6,NULL), NULL, NULL));
-- N10
INSERT INTO city_data_node$ VALUES(10, 18, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(35,6,NULL), NULL, NULL));
-- N11
INSERT INTO city_data_node$ VALUES(11, -14, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(47,6,NULL), NULL, NULL));
-- N12
INSERT INTO city_data_node$ VALUES(12, 15, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(47,14,NULL), NULL, NULL));
-- N13
INSERT INTO city_data_node$ VALUES(13, 17, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(35,14,NULL), NULL, NULL));
-- N14
INSERT INTO city_data_node$ VALUES(14, 19, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(21,14,NULL), NULL, NULL));
-- N15
INSERT INTO city_data_node$ VALUES(15, 21, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(9,14,NULL), NULL, NULL));
-- N16
INSERT INTO city_data_node$ VALUES(16, 6, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(9,22,NULL), NULL, NULL));
-- N17
INSERT INTO city_data_node$ VALUES(17, 7, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(21,22,NULL), NULL, NULL));
-- N18
INSERT INTO city_data_node$ VALUES(18, 8, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(35,22,NULL), NULL, NULL));
-- N19
INSERT INTO city_data_node$ VALUES(19, -15, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(47,22,NULL), NULL, NULL));
-- N20
INSERT INTO city_data_node$ VALUES(20, 26, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(4,31,NULL), NULL, NULL));
-- N21
INSERT INTO city_data_node$ VALUES(21, 25, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(9,35,NULL), NULL, NULL));
-- N22
INSERT INTO city_data_node$ VALUES(22, -25, NULL,
  SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(13,35,NULL), NULL, NULL));
 
-- 2C. Insert data into <topology_name>_FACE$ table.
 
-- F0 (id = -1, not 0)
INSERT INTO city_data_face$ VALUES(-1, NULL, SDO_LIST_TYPE(-1, -2, 4, 6), 
                                                          SDO_LIST_TYPE(), NULL);
-- F1
INSERT INTO city_data_face$ VALUES(1, 1, SDO_LIST_TYPE(25), SDO_LIST_TYPE(),
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(3,30, 15,38)));
-- F2
INSERT INTO city_data_face$ VALUES(2, 2, SDO_LIST_TYPE(), SDO_LIST_TYPE(4),
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(17,30, 31,40)));
-- F3
INSERT INTO city_data_face$ VALUES(3, 19, SDO_LIST_TYPE(), SDO_LIST_TYPE(),
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(9,14, 21,22)));
-- F4
INSERT INTO city_data_face$ VALUES(4, 17, SDO_LIST_TYPE(), SDO_LIST_TYPE(),
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(21,14, 35,22)));
-- F5
INSERT INTO city_data_face$ VALUES(5, 15, SDO_LIST_TYPE(), SDO_LIST_TYPE(),
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(35,14, 47,22)));
-- F6
INSERT INTO city_data_face$ VALUES(6, 20, SDO_LIST_TYPE(), SDO_LIST_TYPE(),
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(9,6, 21,14)));
-- F7
INSERT INTO city_data_face$ VALUES(7, 10, SDO_LIST_TYPE(), SDO_LIST_TYPE(),
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(21,6, 35,14)));
-- F8
INSERT INTO city_data_face$ VALUES(8, 16, SDO_LIST_TYPE(), SDO_LIST_TYPE(),
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(35,6, 47,14)));
-- F9
INSERT INTO city_data_face$ VALUES(9,26,SDO_LIST_TYPE(), SDO_LIST_TYPE(),
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(4,31, 7,34)));
 
-- 3. Create feature tables.
 
CREATE TABLE land_parcels ( -- Land parcels (selected faces)
  feature_name VARCHAR2(30) PRIMARY KEY,
  feature SDO_TOPO_GEOMETRY);
 
CREATE TABLE city_streets ( -- City streets (selected edges)
  feature_name VARCHAR2(30) PRIMARY KEY,
  feature SDO_TOPO_GEOMETRY);
 
CREATE TABLE traffic_signs ( -- Traffic signs (selected nodes)
  feature_name VARCHAR2(30) PRIMARY KEY,
  feature SDO_TOPO_GEOMETRY);
 
-- 4. Associate feature tables with the topology.
--    Add the three topology geometry layers to the CITY_DATA topology.
--    Any order is OK.
 
EXECUTE SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('CITY_DATA', 'LAND_PARCELS','FEATURE', 'POLYGON');
EXECUTE SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('CITY_DATA', 'TRAFFIC_SIGNS','FEATURE', 'POINT');
EXECUTE SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER('CITY_DATA', 'CITY_STREETS', 'FEATURE','LINE');
 
--  As a result, Spatial generates a unique TG_LAYER_ID for each layer in 
--  the topology metadata (USER/ALL_SDO_TOPO_METADATA).
 
-- 5. Initialize topology metadata.
EXECUTE SDO_TOPO.INITIALIZE_METADATA('CITY_DATA');
 
-- 6. Load feature tables using the SDO_TOPO_GEOMETRY constructor.
 
-- Each topology feature can consist of one or more objects (face, edge, node)
-- of an appropriate type. For example, a land parcel can consist of one face,
-- or two or more faces, as specified in the SDO_TOPO_OBJECT_ARRAY.
 
-- There are typically fewer features than there are faces, nodes, and edges.
-- In this example, the only features are these:
-- Area features (land parcels): P1, P2, P3, P4, P5
-- Point features (traffic signs): S1, S2, S3, S4
-- Linear features (roads/streets): R1, R2, R3, R4

-- 6A. Load LAND_PARCELS table.
 
-- P1
INSERT INTO land_parcels VALUES ('P1', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    3, -- Topology geometry type (polygon/multipolygon)
    1, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (3, 3), -- face_id = 3
      SDO_TOPO_OBJECT (6, 3))) -- face_id = 6
);
-- P2
INSERT INTO land_parcels VALUES ('P2', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    3, -- Topology geometry type (polygon/multipolygon)
    1, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (4, 3), -- face_id = 4
      SDO_TOPO_OBJECT (7, 3))) -- face_id = 7
);
-- P3
INSERT INTO land_parcels VALUES ('P3', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    3, -- Topology geometry type (polygon/multipolygon)
    1, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (5, 3), -- face_id = 5
      SDO_TOPO_OBJECT (8, 3))) -- face_id = 8
);
-- P4
INSERT INTO land_parcels VALUES ('P4', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    3, -- Topology geometry type (polygon/multipolygon)
    1, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (2, 3))) -- face_id = 2
);
-- P5 (Includes F1, but not F9.)
INSERT INTO land_parcels VALUES ('P5', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    3, -- Topology geometry type (polygon/multipolygon)
    1, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (1, 3))) -- face_id = 1
);
 
-- 6B. Load TRAFFIC_SIGNS table.
 
-- S1
INSERT INTO traffic_signs VALUES ('S1', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    1, -- Topology geometry type (point)
    2, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (14, 1))) -- node_id = 14
);
-- S2
INSERT INTO traffic_signs VALUES ('S2', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    1, -- Topology geometry type (point)
    2, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (13, 1))) -- node_id = 13
);
-- S3
INSERT INTO traffic_signs VALUES ('S3', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    1, -- Topology geometry type (point)
    2, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (6, 1))) -- node_id = 6
);
-- S4
INSERT INTO traffic_signs VALUES ('S4', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    1, -- Topology geometry type (point)
    2, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (4, 1))) -- node_id = 4
);
 
-- 6C. Load CITY_STREETS table.
-- (Note: "R" in feature names is for "Road", because "S" is used for signs.)
 
-- R1
INSERT INTO city_streets VALUES ('R1', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    2, -- Topology geometry type (line string)
    3, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (9, 2),
      SDO_TOPO_OBJECT (-10, 2),
      SDO_TOPO_OBJECT (11, 2))) -- edge_ids = 9, -10, 11
);
-- R2
INSERT INTO city_streets VALUES ('R2', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    2, -- Topology geometry type (line string)
    3, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (4, 2),
      SDO_TOPO_OBJECT (-5, 2))) -- edge_ids = 4, -5
);
-- R3
INSERT INTO city_streets VALUES ('R3', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    2, -- Topology geometry type (line string)
    3, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (25, 2))) -- edge_id = 25
);
-- R4
INSERT INTO city_streets VALUES ('R4', -- Feature name
  SDO_TOPO_GEOMETRY(
    'CITY_DATA', -- Topology name
    2, -- Topology geometry type (line string)
    3, -- TG_LAYER_ID for this topology (from ALL_SDO_TOPO_METADATA)
    SDO_TOPO_OBJECT_ARRAY (
      SDO_TOPO_OBJECT (3, 2))) -- edge_id = 3
);
 
-- 7. Query the data.
 
SELECT a.feature_name, a.feature.tg_id, a.feature.get_geometry()
FROM land_parcels a;
 
/* Window is city_streets */
SELECT  a.feature_name, b.feature_name
  FROM city_streets b,
     land_parcels a
  WHERE  b.feature_name like 'R%' AND 
     sdo_anyinteract(a.feature, b.feature) = 'TRUE'
  ORDER BY b.feature_name, a.feature_name;
 
-- Find all streets that have any interaction with land parcel P3.
-- (Should return only R1.)
SELECT c.feature_name FROM city_streets c, land_parcels l 
  WHERE l.feature_name = 'P3' AND
   SDO_ANYINTERACT (c.feature, l.feature) = 'TRUE';
 
-- Find all land parcels that have any interaction with traffic sign S1.
-- (Should return P1 and P2.)
SELECT l.feature_name FROM land_parcels l, traffic_signs t 
  WHERE t.feature_name = 'S1' AND
   SDO_ANYINTERACT (l.feature, t.feature) = 'TRUE';
 
-- Get the geometry for land parcel P1.
SELECT l.feature_name, l.feature.get_geometry()
  FROM land_parcels l WHERE l.feature_name = 'P1';
 
-- Get the boundary of face with face_id 3.
SELECT SDO_TOPO.GET_FACE_BOUNDARY('CITY_DATA', 3) FROM DUAL;
 
-- Get the topology objects for land parcel P2.
-- CITY_DATA layer, land parcels (tg_ layer_id = 1), parcel P2 (tg_id = 2)
SELECT SDO_TOPO.GET_TOPO_OBJECTS('CITY_DATA', 1, 2) FROM DUAL;