| Oracle® Spatial Topology and Network Data Models 10g Release 1 (10.1) Part Number B10828-01 |
|
|
View PDF |
| Oracle® Spatial Topology and Network Data Models 10g Release 1 (10.1) Part Number B10828-01 |
|
|
View PDF |
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:
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:
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.)
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.
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.
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.)
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.)
Load the feature tables using the SDO_TOPO_GEOMETRY constructor. (This constructor is described in Section 1.6.2.)
Query the topology data (for example, using SDO_ANYINTERACT operator).
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.
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:
E elements (E1, E2, and so on) are edges, F elements (F0, F1, and so on) are faces, and N elements (N1, N2, and so on) are nodes.
F0 (face zero) is created for every topology. It is the universal face containing everything else in the topology. There is no geometry associated with the universal face. F0 has the face ID value of -1 (negative 1).
There is a node created for every point geometry and for every start and end node of an edge. For example, face F1 has only one edge (a closed edge), E1. The edge has the same node as the start and end nodes (N1).
An island node is a node that is isolated in a face. For example, node N4 is an island node in face F2.
An island edge is an edge that is isolated in a face. For example, edge E25 is an island edge in face F1.
An edge cannot have an island node on it. The edge can broken up into two edges by adding a node on the edge. For example, if there was originally a single edge between nodes N16 and N18, adding node N17 resulted in two edges: E6 and E7.
Information about the topological relationships is stored in special edge, face, and node information tables. For example, the edge information table contains the following information about edges E9 and E10. (Note the direction of the arrowheads for each edge.) The next and previous edges are based on the left and right faces of the edge.
For edge E9, the start node is N15 and the end node is N14, the next left edge is E19 and the previous left edge is -E21, the next right edge is -E22 and the previous right edge is E20, the left face is F3 and the right face is F6.
For edge E10, the start node is N13 and the end node is N14, the next left edge is -E20 and the previous left edge is E18, the next right edge is E17 and the previous right edge is -E19, the left face is F7 and the right face is F4.
For additional examples of edge-related data, including an illustration and explanations, see Section 1.5.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
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.
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 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.
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:
States at the highest level, which consists of features from its child layer, Counties
Counties at the next level down, which consists of features from its child layer, Tracts
Tracts at the next level down, which consists of features from its child layer, Block Groups
Block Groups at the next level down, which consists of features from its child layer, Land Parcels
Land Parcels at the lowest level of the 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.
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. |
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
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:
Each feature table includes a column of type SDO_TOPO_GEOMETRY. This type includes a TG_LAYER_ID attribute (the unique ID assigned by Oracle Spatial Topology Manager when the layer is created), as well as a TG_ID attribute (the unique ID assigned to each feature in a layer). The values in these two columns have corresponding values in the TG_LAYER_ID and TG_ID columns in the <topology-name>_RELATION$ table.
Each feature has one or more rows in the <topology-name>_RELATION$ table.
Given the TG_LAYER_ID and TG_ID values for a feature, the set of nodes, faces, and edges associated with the feature can be determined by matching the TOPO_ID value (the node, edge, or face ID) in the <topology-name>_RELATION$ table with the corresponding ID value in the <topology-name>_NODE$, <topology-name>_EDGE$, or <topology-name>_FACE$ 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.)
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.
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. |
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.) |
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. |
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.
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.
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
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.
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.
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
);
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
);
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';
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';
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))
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;
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;
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.
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.) |
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. |
The topology data model application programming interface (API) consists of the following:
Subprograms (PL/SQL functions and procedures) in the SDO_TOPO package (described in Chapter 3) and the SDO_TOPO_MAP package (described in Chapter 4)
SDO_ANYINTERACT operator (described in Chapter 5)
Java API (described in Section 1.8.1)
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.
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:
Import the tables from the .dmp file that you created when you exported the topology data.
Call the SDO_TOPO.ADD_TOPO_GEOMETRY_LAYER procedure to add each topology geometry layer to the topology.
Call the SDO_TOPO.INITIALIZE_METADATA procedure to initialize the topology metadata.
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.)
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;
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|