| 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 |
This chapter explains the concepts and operations related to the Oracle Spatial network data model. It assumes that you are familiar with the following information:
The main topology concepts explained in Chapter 1, especially those related to nodes and links
The main Oracle Spatial concepts, data types, and operations, as documented in Oracle Spatial User's Guide and Reference
Although this chapter discusses some network-related terms as they relate to Oracle Spatial, it assumes that you are familiar with basic network data modeling concepts.
This chapter contains the following major sections:
In many applications, capabilities or objects are modeled as nodes and links in a network. The network model contains logical information such as connectivity relationships among nodes and links, directions of links, and costs of nodes and links. With logical network information, you can analyze a network and answer questions, many of them related to path computing and tracing. For example, for a biochemical pathway, you can find all possible reaction paths between two chemical compounds; or for a road network, you can find the following information:
What is the shortest (distance) or fastest (travel time) path between two cities?
What is the closest hotel to a specific airport, and how can I get there?
In additional to logical network information, spatial information such as node locations and link geometries can be associated with the logical network. This information can help you to model the logical information (such as the cost of a route, because its physical length can be directly computed from its spatial representation).
The generic data model and network analysis capability can model and analyze many kinds of network applications in addition to traditional geographical information systems (GIS). For example, in biochemistry, applications may need to model reaction pathway networks for living organisms; and in the pharmaceutical industry, applications that model the drug discovery process may need to model protein-protein interaction.
The network modeling capabilities of Spatial include schema objects and an application programming interface (API). The schema objects include metadata and network tables. The API includes a server-side PL/SQL API (the SDO_NET package) for creating, managing, and analyzing networks in the database, and a middle-tier (or client-side) Java API for network analysis.
This section summarizes the main steps for working with the network data model in Oracle Spatial. It refers to important concepts, structures, and operations that are described in detail in other sections.
There are two basic approaches to creating a network:
Let Spatial perform most operations, using procedures with names in the form CREATE_<network-type>_NETWORK. (See Section 6.2.1.)
Perform the operations yourself: create the necessary network tables and update the network metadata. (See Section 6.2.2.)
With each approach, you must insert the network data into the network tables. You can then use the network data model PL/SQL and Java application programming interfaces (APIs) to update the network and perform other operations. (The PL/SQL and Java APIs are described in Section 6.8.)
To create a network by letting Spatial perform most of the necessary operations, follow these steps:
Create the network using a procedure with a name in the form CREATE_<network-type>_NETWORK, where <network-type> reflects the type of network that you want to create:
SDO_NET.CREATE_SDO_NETWORK for a spatial network with non-LRS SDO_GEOMETRY objects
SDO_NET.CREATE_LRS_NETWORK for a spatial network with LRS SDO_GEOMETRY objects
SDO_NET.CREATE_TOPO_NETWORK for a spatial network with topology geometry (SDO_TOPO_GEOMETRY) objects
SDO_NET.CREATE_LOGICAL_NETWORK for a logical network
Each of these procedures creates the necessary network data model tables (described in Section 6.6) and inserts a row with the appropriate network metadata information into the xxx_SDO_NETWORK_METADATA views (described in Section 6.7.1).
Each procedure has two formats: one format creates all network data model tables using default names for the tables and certain columns, and other format lets you specify names for the tables and certain columns. The default names for the network data model tables are <network-name>_NODE$, <network-name>_LINK$, <network-name>_PATH$, and <network-name>_PLINK$. The default name for cost columns in the network data model tables is COST, and the default name for geometry columns is GEOMETRY.
Insert data into the node and link tables, and if necessary into the path and path-link tables. (The node, link, path, and path-link tables are described in Section 6.6.)
Validate the network, using the SDO_NET.VALIDATE_NETWORK procedure.
For a spatial (SDO or LRS) network, insert the appropriate information into the USER_SDO_GEOM_METADATA view, and create spatial indexes on the geometry columns.
To create a network by performing the necessary operations yourself, follow these steps:
Create the node table, using the SDO_NET.CREATE_NODE_TABLE procedure. (The node table is described in Section 6.6.1.)
Insert data into the node table.
Create the link table, using the SDO_NET.CREATE_LINK_TABLE procedure. (The link table is described in Section 6.6.2).
Insert data into the link table.
Optionally, create the path table, using the SDO_NET.CREATE_PATH_TABLE procedure. (The path table is described in Section 6.6.3).
If you created the path table, create the path-link table, using the SDO_NET.CREATE_PATH_LINK_TABLE procedure. (The path-link table is described in Section 6.6.4).
If you created the path table and if you want to create paths, insert data into the table.
If you inserted data into the path table, insert the appropriate rows into the path-link table.
Insert a row into the USER_SDO_NETWORK_METADATA view with information about the network. (The USER_SDO_NETWORK_METADATA view is described in Section 6.7.1.)
For a spatial (SDO or LRS) network, insert the appropriate information into the USER_SDO_GEOM_METADATA view, and create spatial indexes on the geometry columns.
Validate the network, using the SDO_NET.VALIDATE_NETWORK procedure.
You can change the sequence of some of these steps. For example, you can create both the node and link tables first, and then insert data into each one; and you can insert the row into the USER_SDO_NETWORK_METADATA view before you create the node and link tables.
A network is a type of mathematical graph that captures relationships between objects using connectivity. The connectivity may or may not be based on spatial proximity. For example, if two towns are on opposite sides of a lake, the shortest path based on spatial proximity (a straight line across the middle of the lake) is not relevant if you want to drive from one town to the other. Instead, to find the shortest driving distance, you need connectivity information about roads and intersections and about the "cost" of individual links.
A network consists of a set of nodes and links. Each link (sometimes also called an edge or a segment) specifies two nodes. A network can be directed or undirected, although links and paths typically have direction.
The following are some key terms related to the network data model:
A link represents a relationship between two nodes. A link may be directed (that is, have a direction) or undirected (that is, not have a direction).
A path is an alternating sequence of nodes and links, beginning and ending with nodes, and usually with no nodes and links appearing more than once. (Repeating nodes and links within a path are permitted, but are rare in most network applications.)
A network is a set of nodes and links. A network is directed if the links that is contains are directed, and a network is undirected if the links that it contains are undirected.
A logical network contains connectivity information but no geometric information. This is the model used for network analysis. A logical network can be treated as a directed graph or undirected graph, depending on the application.
A spatial network contains both connectivity information and geometric information. In a spatial network, the nodes and links are SDO_GEOMETRY geometry objects without LRS information (an SDO network) or with LRS information (an LRS network), or SDO_TOPO_GEOMETRY objects (a topology geometry network).
In an LRS network, each node includes a geometry ID value and a measure value, and each link includes a geometry ID value and start and end measure values; and the geometry ID value in each case refers to an SDO_GEOMETRY object with LRS information. A spatial network can be directed or undirected, depending on the application.
A feature is an object of interest in a network application that is associated with a node or link. For example, in a transportation network, features include exits and intersections (mapped to nodes), and highways and streets (mapped to links).
Cost is a non-negative numeric attribute that can be associated with links or nodes for computing the minimum cost path, which is the path that has the minimum total cost from a start node to an end node. You can specify a single cost factor, such as driving time or driving distance for links, in the network metadata.
Reachable nodes are all nodes that can be reached from a given node. Reaching nodes are all nodes that can reach a given node.
The degree of a node is the number of links to (that is, incident upon) the node. The in-degree is the number of inbound links, and the out-degree is the number of outbound links.
Network constraints are restrictions defined on network analysis computations (for example, that driving routes must consist of expressways and major highways).
A spanning tree of a connected graph is a tree (that is, a graph with no cycles) that connects all nodes of the graph. (The directions of links are ignored in a spanning tree.) The minimum cost spanning tree is the spanning tree that connects all nodes and has the minimum total cost.
Networks are used in applications to find how different objects are connected to each other. The connectivity is often expressed in terms of adjacency and path relationships. Two nodes are adjacent if they are connected by a link. There are often several paths between any two given nodes, and you may want to find the path with the minimum cost.
This section describes some typical examples of different kinds of network applications.
In a typical road network, the intersections of roads are nodes and the road segments between two intersections are links. The spatial representation of a road is not inherently related to the nodes and links in the network. For example, a shape point in the spatial representation of a road (reflecting a sharp turn in the road) is not a node in the network if that shape point is not associated with an intersection; and a single spatial object may make up several links in a network (such as a straight segment intersected by three crossing roads). An important operation with a road network is to find the path from a start point to an end point, minimizing either the travel time or distance. There may be additional constraints on the path computation, such as having the path go through a particular landmark or avoid a particular intersection.
The subway network of any major city is probably best modeled as a logical network, assuming that precise spatial representation of the stops and track lines is unimportant. In such a network, all stops on the system constitute the nodes of the network, and a link is the connection between two stops if a train travels directly between these two stops. Important operations with a train network include finding all stations that can be reached from a specified station, finding the number of stops between two specified stations, and finding the travel time between two stations.
Utility networks, such as power line or cable networks, must often be configured to minimize the cost. An important operation with a utility network is to determine the connections among nodes, using minimum cost spanning tree algorithms, to provide the required quality of service at the minimum cost. Another important operation is reachability analysis, so that, for example, if a station in a water network is shut down, you know which areas will be affected.
Biochemical processes can be modeled as biochemical networks to represent reactions and regulations in living organisms. For example, metabolic pathways are networks involved in enzymatic reactions, while regulatory pathways represent protein-protein interactions. In this example, a pathway is a network; genes, proteins, and chemical compounds are nodes; and reactions among nodes are links. Important operations for a biochemical network include computing paths and the degrees of nodes.
Some network applications require representations at different levels of abstraction. For example, two major processes might be represented as nodes with a link between them at the highest level of abstraction, and each major process might have several subordinate processes that are represented as nodes and links at the next level down.
A network hierarchy allows you to represent a network with multiple levels of abstraction by assigning a hierarchy level to each node. (Links are not assigned a hierarchy level, and links can be between nodes in the same hierarchy level or in different levels.) The lowest (most detailed) level in the hierarchy is level 1, and successive higher levels are numbered 2, 3, and so on.
Nodes at adjacent levels of a network hierarchy have parent-child relationships. Each node at the higher level can be the parent node for one or more nodes at the lower level. Each node at the lower level can be a child node of one node at the higher level.
Links can also have parent-child relationships. However, because links are not assigned to a hierarchy level, there is not necessarily a relationship between link parent-child relationships and network hierarchy levels.
Figure 6-1 shows a simple hierarchical network, in which there are two levels.
As shown in Figure 6-1:
The top level (level 2) contains two nodes. Each node is the parent node of several nodes in the bottom level. The link between the nodes in the top level is the parent link of two links between nodes in the bottom level.
The bottom level (level 1) shows the nodes that make up each node in the top level. It also shows the links between nodes that are child nodes of each parent node in the top level, and two links between nodes that have different parent nodes.
The links between nodes in the bottom level that have different parent nodes are shown with dark connecting lines. These links are child links of the single link between the nodes in the top level in the hierarchy. (However, these two links in the bottom level could also be defined as not being child links of any parent link between nodes in a higher level.)
The parent-child relationships between each parent node and link and its child nodes and links are shown with dashed lines with arrowheads at both ends.
Although it is not shown in Figure 6-1, links can cross hierarchy levels. For example, a link could be defined between a node in the top level and any node in the bottom level. In this case, there would not be a parent-child relationship between the links.
The connectivity information for a spatial network is stored in two tables: a node table and a link table. In addition, path information can be stored in a path table and a path-link table. You can have Spatial create these tables automatically when you create the network using a CREATE_<network-type>_NETWORK procedure; or you can create these tables using the SDO_NET.CREATE_NODE_TABLE, SDO_NET.CREATE_LINK_TABLE, SDO_NET.CREATE_PATH_TABLE, and SDO_NET.CREATE_PATH_LINK_TABLE procedures.
These tables contain columns with predefined names, and you must not change any of the predefined column names; however, you can add columns to the tables by using the ALTER TABLE statement with the ADD COLUMN clause. For example, although each link and path table is created with a single COST column, you can create additional columns and associate them with other comparable attributes. For example, if you wanted to assign a driving time, scenic appeal rating, and a danger rating to each link, you could use the COST column for driving time, add columns for SCENIC_APPEAL and DANGER to the link table, and populate all three columns with values to be interpreted by applications.
Each network has a node table that can contain the columns described in Table 6-1. (The specific columns depend on the network type and whether the network is hierarchical or not.)
Table 6-1 Node Table Columns
| Column Name | Data Type | Description |
|---|---|---|
| NODE_ID | NUMBER | ID number that uniquely identifies this node within the network. |
| NODE_NAME | VARCHAR2(32) | Name of the node. |
| NODE_TYPE | VARCHAR2(24) | User-defined string to identify the node type. |
| ACTIVE | VARCHAR2(1) | Contains Y if the node is active (visible in the network), or N if the node is not active. |
| PARTITION_ID | NUMBER | Reserved for future use. |
| <node_geometry_column>, or GEOM_ID and MEASURE | SDO_GEOMETRY, or SDO_TOPO_GEOMETRY, or NUMBER | For a spatial (SDO, non-LRS) network, name of the SDO_GEOMETRY column containing the geometry objects associated with the node.
For a spatial topology network, name of the SDO_TOPO_GEOMETRY column containing the topology geometry objects associated with the node. For a spatial LRS network, GEOM_ID and MEASURE columns (both of type NUMBER) for the geometry objects associated with the node. For a logical network, this column is not used. For a spatial SDO or topology network, the actual column name is either a default name or what you specified as the |
| <node_cost_column> | NUMBER | Name of the column containing the cost value to be associated with the node, for use by applications that use the network. The actual column name is either a default name or what you specified as the cost_column parameter value in the call to the SDO_NET.CREATE_NODE_TABLE procedure. The cost value can represent anything you want, for example, the toll to be paid at a toll booth. |
| HIERARCHY_LEVEL | NUMBER | For hierarchical networks only: number indicating the level in the network hierarchy for this node. (Section 6.5 explains network hierarchy.) |
| PARENT_NODE_ID | NUMBER | For hierarchical networks only: node ID of the parent node of this node. (Section 6.5 explains network hierarchy.) |
Each network has a link table that contains the columns described in Table 6-2.
Table 6-2 Link Table Columns
| Column Name | Data Type | Description |
|---|---|---|
| LINK_ID | NUMBER | ID number that uniquely identifies this link within the network. |
| LINK_NAME | VARCHAR2(32) | Name of the link. |
| START_NODE_ID | NUMBER | Node ID of the node that starts the link. |
| END_NODE_ID | NUMBER | Node ID of the node that ends the link. |
| LINK_TYPE | VARCHAR2(24) | User-defined string to identify the link type. |
| ACTIVE | VARCHAR2(1) | Contains Y if the link is active (visible in the network), or N if the link is not active. |
| LINK_LEVEL | NUMBER | Priority level for the link; used for hierarchical modeling, so that links with higher priority levels can be considered first in computing a path. |
| <link_geometry_column>; or GEOM_ID, START_MEASURE, and END_MESURE | SDO_GEOMETRY, or SDO_TOPO_GEOMETRY, or NUMBER | For a spatial (SDO, non-LRS) network, name of the SDO_GEOMETRY column containing the geometry objects associated with the link.
For a spatial topology network, name of the SDO_TOPO_GEOMETRY column containing the topology geometry objects associated with the link. For a spatial LRS network, GEOM_ID, START_MEASURE, and END_MEASURE columns (all of type NUMBER) for the geometry objects associated with the link. For a logical network, this column is not used. For a spatial SDO or topology network, the actual column name is either a default name or what you specified as the |
| <link_cost_column> | NUMBER | Name of the column containing the cost value to be associated with the link, for use by applications that use the network. The actual column name is either a default name or what you specified as the cost_column parameter value in the call to the SDO_NET.CREATE_LINK_TABLE procedure. The cost value can represent anything you want, for example, the estimated driving time for the link. |
| PARENT_LINK_ID | NUMBER | For hierarchical networks only: link ID of the parent link of this link. (Section 6.5 explains parent-child relationships in a network hierarchy.) |
Each network can have a path table. A path is an ordered sequence of links, and is usually created as a result of network analysis. A path table provides a way to store the result of this analysis. For each path table, you must create an associated path-link table (described in Section 6.6.4). Each path table contains the columns described in Table 6-3.
Table 6-3 Path Table Columns
| Column Name | Data Type | Description |
|---|---|---|
| PATH_ID | NUMBER | ID number that uniquely identifies this path within the network. |
| PATH_NAME | VARCHAR2(32) | Name of the path. |
| START_NODE_ID | NUMBER | Node ID of the node that starts the first link in the path. |
| END_NODE_ID | NUMBER | Node ID of the node that ends the last link in the path. |
| PATH_TYPE | VARCHAR2(24) | User-defined string to identify the path type. |
| COST | NUMBER | Cost value to be associated with the path, for use by applications that use the network. The cost value can represent anything you want, for example, the estimated driving time for the path. |
| SIMPLE | VARCHAR2(1) | Contains Y if the path is a simple path, or N if the path is a complex path. In a simple path, the links form an ordered list that can be traversed from the start node to the end node with each link visited once. In a complex path, there are multiple options for going from the start node to the end node. |
| <path_geometry_column> | SDO_GEOMETRY | For all network types except logical, name of the column containing the geometry object associated with the path. The actual column name is either a default name or what you specified as the geom_column parameter value in the call to the SDO_NET.CREATE_PATH_TABLE procedure.
For a logical network, this column is not used. |
For each path table (described in Section 6.6.3), you must create a path-link table. Each row in the path-link table uniquely identifies a link within a path in a network. The order of rows in the path-link table is not significant. Each path-link table contains the columns described in Table 6-4.
Table 6-4 Path-Link Table Columns
| Column Name | Data Type | Description |
|---|---|---|
| PATH_ID | NUMBER | ID number of the path in the network. |
| LINK_ID | NUMBER | ID number of the link in the network. Each combination of PATH_ID and LINK_ID must be unique within the network. |
| SEQ_NO | NUMBER | Unique sequence number of the link in the path. (The sequence numbers start at 1.) Sequence numbers allow paths to contain repeating nodes and links. |
There is a set of network metadata views for each schema (user): xxx_SDO_NETWORK_METADATA, where xxx can be USER or ALL. These views are created by Spatial.
The following views contain information about networks:
USER_SDO_NETWORK_METADATA contains information about all networks owned by the user.
ALL_SDO_NETWORK_METADATA contains information about all networks on which the user has SELECT permission.
If you create a network using one of the CREATE_<network-type>_NETWORK procedures, the information in these views is automatically updated to reflect the new network; otherwise, you must insert information about the network into the USER_SDO_NETWORK_METADATA view.
The USER_SDO_NETWORK_METADATA and ALL_SDO_NETWORK_METADATA views contain the same columns, as shown Table 6-5, except that the USER_SDO_NETWORK_METADATA view does not contain the OWNER column. (The columns are listed in their order in the view definition.)
Table 6-5 Columns in the xxx_SDO_NETWORK_METADATA Views
| Column Name | Data Type | Purpose |
|---|---|---|
| OWNER | VARCHAR2(32) | Owner of the network. (ALL_SDO_NETWORK_METADATA view only.) |
| NETWORK | VARCHAR2(32) | Name of the network. |
| NETWORK_CATEGORY | VARCHAR2(12) | Contains SPATIAL if the network nodes and links are associated with spatial geometries; contains LOGICAL if the network nodes and links are not associated with spatial geometries. |
| GEOMETRY_TYPE | VARCHAR2(20) | If NETWORK_CATEGORY is SPATIAL, contains a value indicating the geometry type of nodes and links: SDO_GEOMETRY for non-LRS SDO_GEOMETRY objects, LRS_GEOMETRY for LRS SDO_GEOMETRY objects, TOPO_GEOMETRY for SDO_TOPO_GEOMETRY objects. |
| NETWORK_TYPE | VARCHAR2(24) | User-defined string to identify the network type. |
| NO_OF_HIERARCHY_LEVELS | NUMBER | Number of levels in the network hierarchy. Contains 1 if there is no hierarchy. (See Section 6.5 for information about network hierarchy.) |
| NO_OF_PARTITIONS | NUMBER | (Must be 1 for the current release. Other values may be supported in future releases.) |
| LRS_TABLE_NAME | VARCHAR2(12) | If GEOMETRY_TYPE is SDO_GEOMETRY, contains the name of the table containing geometries associated with nodes. |
| LRS_GEOM_COLUMN | VARCHAR2(12) | If LRS_TABLE_NAME contains a table name, identifies the geometry column in that table. |
| NODE_TABLE_NAME | VARCHAR2(32) | If GEOMETRY_TYPE is SDO_GEOMETRY, contains the name of the table containing geometries associated with nodes. (The node table is described in Section 6.6.1.) |
| NODE_GEOM_COLUMN | VARCHAR2(32) | If NODE_TABLE_NAME contains a table name, identifies the geometry column in that table. |
| NODE_COST_COLUMN | VARCHAR2(32) | If NODE_TABLE_NAME contains a table name, identifies the cost column in that table. |
| LINK_TABLE_NAME | VARCHAR2(32) | If GEOMETRY_TYPE is SDO_GEOMETRY, contains the name of the table containing geometries associated with links. (The link table is described in Section 6.6.2.) |
| LINK_GEOM_COLUMN | VARCHAR2(32) | If LINK_TABLE_NAME contains a table name, identifies the geometry column in that table. |
| LINK_DIRECTION | VARCHAR2(12) | Contains a value indicating the type for all links in the network: UNDIRECTED or DIRECTED. |
| LINK_COST_COLUMN | VARCHAR2(32) | If LINK_TABLE_NAME contains a table name, identifies the optional numeric column containing a cost value for each link. |
| PATH_TABLE_NAME | VARCHAR2(32) | Contains the name of an optional table containing information about paths. (The path table is described in Section 6.6.3.) |
| PATH_GEOM_COLUMN | VARCHAR2(32) | If PATH_TABLE_NAME is associated with a spatial network, identifies the geometry column in that table. |
| PATH_LINK_TABLE_NAME | VARCHAR2(32) | Contains the name of an optional table containing information about links for each path. (The path-link table is described in Section 6.6.4.) |
| PARTITION_TABLE_NAME | VARCHAR2(32) | Reserved for future use. |
The Oracle Spatial network data model includes two client application programming interfaces (APIs): a PL/SQL interface provided by the SDO_NET package and a Java interface. Both interfaces let you create and update network data, and the Java interface provides network analysis capabilities. It is recommended that you use only PL/SQL or SQL to populate network tables and to create indexes, and that you use the Java interface for application development.
The SDO_NET package provides PL/SQL functions and procedures for creating, accessing, and managing networks on a database server. Example 6-3 in Section 6.9 shows the use of SDO_NET functions and procedures.
The SDO_NET functions and procedures can be grouped into the following logical categories:
Creating networks:
Copying and deleting networks:
Creating network tables:
Validating network objects:
Retrieving information (getting information about the network, checking for a characteristic):
For reference information about each SDO_NET function and procedure, see Chapter 7.
The Java client interface for the network data model consists of the following classes and interfaces:
NetworkManager: class to load and store network data and metadata, and to perform network analysis
NetworkFactory: class to create elements related to the network
NetworkConstraint: class to create network constraints
Network: interface for a network
NetworkMetadata: interface for network metadata
GeometryMetadata: class for geometry metadata
Node: interface for a network node
Link: interface for a network link
Path: interface for a network path
MDPoint: interface for a multiple-dimension point
MBR: interface for a multiple-dimension minimum bounding rectangle
JGeometry: class for Oracle Java SDO_GEOMETRY
NetworkDataException: class for exceptions of network manager
Figure 6-2 is a Unified Modeling Language (UML) diagram that shows the relationship between the main classes and interfaces.
Figure 6-2 Java Classes and Interfaces for Network Data Model
For detailed reference information about the network data model classes, see the Javadoc-generated API documentation: open index.html in a directory that includes the path sdonm/doc/javadoc.
You can use the Java API to perform network metadata and data management operations such as the following:
Insert, delete, and modify node and link data
Load a network from a database
Store a network in a database
Store network metadata in a database
Modify network metadata attributes
You can use the Java API to perform network analysis operations such as the following:
Shortest path (for directed and undirected networks): typical transitive closure problems in graph theory. Given a start and an end node, find the shortest path.
Minimum cost spanning tree (for undirected networks): Given an undirected graph, find the minimum cost tree that connects all nodes.
Reachability: Given a node, find all nodes that can reach that node, or find all nodes that can be reached by that node.
Within-cost analysis (for directed and undirected networks): Given a target node and a cost, find all nodes that can be reached by the target node within the given cost.
Nearest-neighbors analysis (for directed and undirected networks): Given a target node and number of neighbors, find the neighbor nodes and their costs to go to the given target node.
All paths between two nodes: Given two nodes, find all possible paths between them.
"Traveling salesman problem" analysis: Given a set of nodes, find the lowest-cost path that visits all nodes and in which the start and end nodes are the same.
This section presents simplified examples that use SDO_NET functions and procedures. It includes the following sections:
The examples refer to concepts that are explained in this chapter, and they use functions and procedures documented in Chapter 7.
This section presents an example of a very simple spatial (SDO, not LRS) network that contains three nodes and a link between each node. The network is illustrated in Figure 6-3.
As shown in Figure 6-3, node N1 is at point 1,1, node N2 is at point 15,1, and node N3 is at point 9,4. Link L1 is a straight line connecting nodes N1 and N2, link L2 is a straight line connecting nodes N2 and N3, and link L3 is a straight line connecting nodes N3 and N1. There are no other nodes or shape points on any of the links.
Example 6-1 does the following:
In a call to the SDO_NET.CREATE_SDO_NETWORK procedure, creates the SDO_NET1 directed network; creates the SDO_NET1_NODE$, SDO_NET1_LINK$, SDO_NET1_PATH$, and SDO_NET1_PLINK$ tables; and updates the xxx_SDO_NETWORK_METADATA views. All geometry columns are named GEOMETRY. Both the node and link tables contain a cost column named COST.
Populates the node, link, path, and path-link tables. It inserts three rows into the node table, three rows into the link table, two rows into the path table, and four rows into the path-link table.
Updates the Oracle Spatial metadata, and creates spatial indexes on the GEOMETRY columns of the node and link tables. (These actions are not specifically related to network management, but that are necessary if applications are to benefit from spatial indexing on these geometry columns.)
Example 6-1 does not show the use of many SDO_NET functions and procedures; these are included in Example 6-3 in Section 6.9.3.
Example 6-1 Simple Spatial (SDO) Network Example (PL/SQL)
-- Create the SDO_NET1 directed network. Also creates the SDO_NET1_NODE$,
-- SDO_NET1_LINK$, SDO_NET1_PATH$, SDO_NET1_PLINK$ tables, and updates
-- USER_SDO_NETWORK_METADATA. All geometry columns are named GEOMETRY.
-- Both the node and link tables contain a cost column named COST.
EXECUTE SDO_NET.CREATE_SDO_NETWORK('SDO_NET1', 1, TRUE, TRUE);
-- Populate the SDO_NET1_NODE$ table.
-- N1
INSERT INTO sdo_net1_node$ VALUES(1, 'N1', NULL, 'Y', 1,
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(1,1,NULL), NULL, NULL),
5);
-- N2
INSERT INTO sdo_net1_node$ VALUES(2, 'N2', NULL, 'Y', 1,
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(15,1,NULL), NULL, NULL),
8);
-- N3
INSERT INTO sdo_net1_node$ VALUES(3, 'N3', NULL, 'Y', 1,
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(9,4,NULL), NULL, NULL),
4);
-- Populate the SDO_NET1_LINK$ table.
-- L1
INSERT INTO sdo_net1_link$ VALUES(1, 'L1', 1, 2, NULL, 'Y', 1,
SDO_GEOMETRY(2002, NULL, NULL,
SDO_ELEM_INFO_ARRAY(1,2,1),
SDO_ORDINATE_ARRAY(1,1, 15,1)),
14);
-- L2
INSERT INTO sdo_net1_link$ VALUES(2, 'L2', 1, 3, NULL, 'Y', 1,
SDO_GEOMETRY(2002, NULL, NULL,
SDO_ELEM_INFO_ARRAY(1,2,1),
SDO_ORDINATE_ARRAY(1,1, 9,4)),
10);
-- L3
INSERT INTO sdo_net1_link$ VALUES(3, 'L3', 2, 3, NULL, 'Y', 1,
SDO_GEOMETRY(2002, NULL, NULL,
SDO_ELEM_INFO_ARRAY(1,2,1),
SDO_ORDINATE_ARRAY(9, 4, 1,1)),
10);
-- Do not populate the SDO_NET1_PATH$ and SDO_NET1_PLINK$ tables now.
-- Do this only when you need to create any paths.
---------------------------------------------------------------------------
-- REMAINING STEPS NEEDED TO USE SPATIAL INDEXES --
---------------------------------------------------------------------------
-- Update the USER_SDO_GEOM_METADATA view. This is required before the
-- spatial index can be created. Do this only once for each layer
-- (that is, table-column combination).
INSERT INTO USER_SDO_GEOM_METADATA
VALUES (
'SDO_NET1_NODE$',
'GEOMETRY',
SDO_DIM_ARRAY( -- 20X20 grid
SDO_DIM_ELEMENT('X', 0, 20, 0.005),
SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
),
NULL -- SRID (spatial reference system, also called coordinate system)
);
INSERT INTO USER_SDO_GEOM_METADATA
VALUES (
'SDO_NET1_LINK$',
'GEOMETRY',
SDO_DIM_ARRAY( -- 20X20 grid
SDO_DIM_ELEMENT('X', 0, 20, 0.005),
SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
),
NULL -- SRID (spatial reference system, also called coordinate system)
);
-- Create the spatial indexes
CREATE INDEX sdo_net1_nodes_idx ON sdo_net1_node$(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
CREATE INDEX sdo_net1_links_idx ON sdo_net1_link$(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
This section presents an example of a very simple logical network that contains three nodes and a link between the nodes. The network is illustrated in Figure 6-4.
As shown in Figure 6-4, link L1 is a straight line connecting nodes N1 and N2, link L2 is a straight line connecting nodes N2 and N3, and link L3 is a straight line connecting nodes N3 and N1. There are no other nodes on any of the links.
Example 6-2 calls the SDO_NET.CREATE_LOGICAL_NETWORK procedure, which does the following: creates the LOG_NET1 directed network; creates the LOG_NET1_NODE$, LOG_NET1_LINK$, LOG_NET1_PATH$, and LOG_NET1_PLINK$ tables; and updates the xxx_SDO_NETWORK_METADATA views. Both the node and link tables contain a cost column named COST. (Because this is a logical network, there are no geometry columns.) The example also populates the node and link tables.
Example 6-2 does not show the use of many SDO_NET functions and procedures; these are included in the logical hierarchical network example (Example 6-4) in Section 6.9.4.
Example 6-2 Simple Logical Network Example (PL/SQL)
-- Create the LOG_NET1 directed logical network. Also creates the
-- LOG_NET1_NODE$, LOG_NET1_LINK$, LOG_NET1_PATH$,
-- and LOG_NET1_PLINK$ tables, and updates USER_SDO_NETWORK_METADATA.
-- Both the node and link tables contain a cost column named COST.
EXECUTE SDO_NET.CREATE_LOGICAL_NETWORK('LOG_NET1', 1, TRUE, TRUE);
-- Populate the LOG_NET1_NODE$ table.
-- N1
INSERT INTO log_net1_node$ (node_id, node_name, active, cost)
VALUES (1, 'N1', 'Y', 2);
-- N2
INSERT INTO log_net1_node$ (node_id, node_name, active, cost)
VALUES (2, 'N2', 'Y', 3);
-- N3
INSERT INTO log_net1_node$ (node_id, node_name, active, cost)
VALUES (3, 'N3', 'Y', 2);
-- Populate the LOG_NET1_LINK$ table.
-- L1
INSERT INTO log_net1_link$ (link_id, link_name, start_node_id, end_node_id,
active, link_level, cost)
VALUES (1, 'L1', 1, 2, 'Y', 1, 10);
-- L2
INSERT INTO log_net1_link$ (link_id, link_name, start_node_id, end_node_id,
active, link_level, cost)
VALUES (2, 'L2', 2, 3, 'Y', 1, 7);
-- L3
INSERT INTO log_net1_link$ (link_id, link_name, start_node_id, end_node_id,
active, link_level, cost)
VALUES (3, 'L3', 3, 1, 'Y', 1, 8);
-- Do not populate the LOG_NET1_PATH$ and LOG_NET1_PLINK$ tables now.
-- Do this only when you need to create any paths.
This section presents an example of a spatial (LRS) network that uses the roads illustrated in Figure 6-5. This illustration is similar to the one used for the LRS example in Oracle Spatial User's Guide and Reference, but it adds two highways (Route2 and Route3).
As shown in Figure 6-5:
Route1 starts at point 2,2 and ends at point 5,14. It has the following nodes: N1, N2, N3, N4, N5, N6, and N7. It has the following links: R1L1, R1L2, R1L3, R1L4, R1L5, and R1L6.
Route2 starts at point 8,4 and ends at point 8,13. It has the following nodes: N3, N6, and N8. It has the following links: R2L1 and R2L2.
Route3 starts at point 12,10 and ends at point 5,14. It has the following nodes: N5, N8, and N7. It has the following links: R3L1 and R3L2.
Example 6-3 does the following:
Creates a table to hold the roads data.
Inserts the definition of three roads into the table.
Inserts the spatial metadata into the USER_SDO_GEOM_METADATA view.
Creates a spatial index on the geometry column in the ROADS table.
Creates and populates the node table.
Creates and populates the link table.
Creates and populates the path table and path-link table, for possible future use. (Before an application can use paths, you must populate these two tables.)
Inserts network metadata into the USER_SDO_NETWORK_METADATA view.
Uses various SDO_NET functions and procedures.
Example 6-3 Spatial (LRS) Network Example (PL/SQL)
---------------------------------------------------------------------------
-- CREATE AND POPULATE TABLE --
---------------------------------------------------------------------------
-- Create a table for roads. Use LRS.
CREATE TABLE roads (
road_id NUMBER PRIMARY KEY,
road_name VARCHAR2(32),
road_geom SDO_GEOMETRY,
geom_id NUMBER);
-- Populate the table with roads (Route1, Route2, Route3).
INSERT INTO roads VALUES(
1,
'Route1',
SDO_GEOMETRY(
3302, -- line string, 3 dimensions (X,Y,M), 3rd is measure dimension
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,2,1), -- one line string, straight segments
SDO_ORDINATE_ARRAY(
2,2,0, -- Starting point - Node1; 0 is measure from start.
2,4,2, -- Node2; 2 is measure from start.
8,4,8, -- Node3; 8 is measure from start.
12,4,12, -- Node4; 12 is measure from start.
12,10,NULL, -- Node5; measure automatically calculated and filled.
8,10,22, -- Node6; 22 is measure from start.
5,14,27) -- Ending point - Node7; 27 is measure from start.
), 1001
);
INSERT INTO roads VALUES(
2,
'Route2',
SDO_GEOMETRY(
3302, -- line string, 3 dimensions (X,Y,M), 3rd is measure dimension
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,2,1), -- one line string, straight segments
SDO_ORDINATE_ARRAY(
8,4,0, -- Node3; 0 is measure from start.
8,10,6, -- Node6; 6 is measure from start.
8,13,9) -- Ending point - Node8; 9 is measure from start.
), 1002
);
INSERT INTO roads VALUES(
3,
'Route3',
SDO_GEOMETRY(
3302, -- line string, 3 dimensions (X,Y,M), 3rd is measure dimension
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,2,1), -- one line string, straight segments
SDO_ORDINATE_ARRAY(
12,10,0, -- Node5; 0 is measure from start.
8,13,5, -- Node8; 5 is measure from start.
5,14,3.16) -- Ending point - Node7; 8.16 is measure from start.
), 1003
);
---------------------------------------------------------------------------
-- UPDATE THE SPATIAL METADATA --
---------------------------------------------------------------------------
-- Update the USER_SDO_GEOM_METADATA view. This is required before the
-- spatial index can be created. Do this only once for each layer
-- (that is, table-column combination; here: roads and road_geom).
INSERT INTO USER_SDO_GEOM_METADATA
VALUES (
'ROADS',
'ROAD_GEOM',
SDO_DIM_ARRAY( -- 20X20 grid
SDO_DIM_ELEMENT('X', 0, 20, 0.005),
SDO_DIM_ELEMENT('Y', 0, 20, 0.005),
SDO_DIM_ELEMENT('M', 0, 20, 0.005) -- Measure dimension
),
NULL -- SRID (spatial reference system, also called coordinate system)
);
-------------------------------------------------------------------
-- CREATE THE SPATIAL INDEX --
-------------------------------------------------------------------
CREATE INDEX roads_idx ON roads(road_geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
--------------------------------
-- USE SDO_NET SUBPROGRAMS
--------------------------------
-- This procedure does not use the CREATE_LRS_NETWORK procedure. Instead,
-- the user creates the network tables and populates the network metadata view.
-- Basic steps:
-- 1. Create and populate the node table.
-- 2. Create and populate the link table.
-- 3. Create the path table and path-link table (for possible
-- future use, before which they will need to be populated).
-- 4. Populate the network metadata (USER_SDO_NETWORK_METADATA).
-- Note: Can be done before or after Steps 1-3.
-- 5. Use various SDO_NET functions and procedures.
-- 1. Create and populate the node table.
EXECUTE SDO_NET.CREATE_NODE_TABLE('ROADS_NODES', 'LRS_GEOMETRY', 'NODE_GEOMETRY', NULL, 1);
-- Populate the node table.
-- N1
INSERT INTO roads_nodes (node_id, node_name, active, geom_id, measure)
VALUES (1, 'N1', 'Y', 1001, 0);
-- N2
INSERT INTO roads_nodes (node_id, node_name, active, geom_id, measure)
VALUES (2, 'N2', 'Y', 1001, 2);
-- N3
INSERT INTO roads_nodes (node_id, node_name, active, geom_id, measure)
VALUES (3, 'N3', 'Y', 1001, 8);
-- N4
INSERT INTO roads_nodes (node_id, node_name, active, geom_id, measure)
VALUES (4, 'N4', 'Y', 1001, 12);
-- N5
INSERT INTO roads_nodes (node_id, node_name, active, geom_id, measure)
VALUES (5, 'N5', 'Y', 1001, 18);
-- N6
INSERT INTO roads_nodes (node_id, node_name, active, geom_id, measure)
VALUES (6, 'N6', 'Y', 1001, 22);
-- N7
INSERT INTO roads_nodes (node_id, node_name, active, geom_id, measure)
VALUES (7, 'N7', 'Y', 1001, 27);
-- N8
INSERT INTO roads_nodes (node_id, node_name, active, geom_id, measure)
VALUES (8, 'N8', 'Y', 1002, 9);
-- 2. Create and populate the link table.
EXECUTE SDO_NET.CREATE_LINK_TABLE('ROADS_LINKS', 'LRS_GEOMETRY', 'LINK_GEOMETRY', 'COST', 1);
-- Populate the link table.
-- Route1, Link1
INSERT INTO roads_links (link_id, link_name, start_node_id, end_node_id, active,
cost, geom_id, start_measure, end_measure)
VALUES (101, 'R1L1', 1, 2, 'Y', 3, 1001, 0, 2);
-- Route1, Link2
INSERT INTO roads_links (link_id, link_name, start_node_id, end_node_id, active,
cost, geom_id, start_measure, end_measure)
VALUES (102, 'R1L2', 2, 3, 'Y', 15, 1001, 2, 8);
-- Route1, Link3
INSERT INTO roads_links (link_id, link_name, start_node_id, end_node_id, active,
cost, geom_id, start_measure, end_measure)
VALUES (103, 'R1L3', 3, 4, 'Y', 10, 1001, 8, 12);
-- Route1, Link4
INSERT INTO roads_links (link_id, link_name, start_node_id, end_node_id, active,
cost, geom_id, start_measure, end_measure)
VALUES (104, 'R1L4', 4, 5, 'Y', 15, 1001, 12, 18);
-- Route1, Link5
INSERT INTO roads_links (link_id, link_name, start_node_id, end_node_id, active,
cost, geom_id, start_measure, end_measure)
VALUES (105, 'R1L5', 5, 6, 'Y', 10, 1001, 18, 22);
-- Route1, Link6
INSERT INTO roads_links (link_id, link_name, start_node_id, end_node_id, active,
cost, geom_id, start_measure, end_measure)
VALUES (106, 'R1L6', 6, 7, 'Y', 7, 1001, 22, 27);
-- Route2, Link1 (cost = 30, a slow drive)
INSERT INTO roads_links (link_id, link_name, start_node_id, end_node_id, active,
cost, geom_id, start_measure, end_measure)
VALUES (201, 'R2L1', 3, 6, 'Y', 30, 1002, 0, 6);
-- Route2, Link2
INSERT INTO roads_links (link_id, link_name, start_node_id, end_node_id, active,
cost, geom_id, start_measure, end_measure)
VALUES (202, 'R2L2', 6, 8, 'Y', 5, 1002, 6, 9);
-- Route3, Link1
INSERT INTO roads_links (link_id, link_name, start_node_id, end_node_id, active,
cost, geom_id, start_measure, end_measure)
VALUES (301, 'R3L1', 5, 8, 'Y', 5, 1003, 0, 5);
-- Route3, Link2
INSERT INTO roads_links (link_id, link_name, start_node_id, end_node_id, active,
cost, geom_id, start_measure, end_measure)
VALUES (302, 'R3L2', 8, 7, 'Y', 5, 1003, 5, 8.16);
-- 3. Create the path table (to store created paths) and the path-link
-- table (to store links for each path) for possible future use,
-- before which they will need to be populated.
EXECUTE SDO_NET.CREATE_PATH_TABLE('ROADS_PATHS', 'PATH_GEOMETRY');
EXECUTE SDO_NET.CREATE_PATH_LINK_TABLE('ROADS_PATHS_LINKS');
-- 4. Populate the network metadata (USER_SDO_NETWORK_METADATA).
INSERT INTO user_sdo_network_metadata VALUES (
'ROADS_NETWORK', -- Network name
'SPATIAL', -- Network category
'LRS_GEOMETRY', -- Geometry type
'Roadways', -- Network type (user-defined)
1, -- No. of levels in hierarchy
1, -- No. of partitions
'ROADS', -- LRS table name
'ROAD_GEOM' , -- LRS geometry column
'ROADS_NODES', -- Node table name
'NODE_GEOMETRY', -- Node geometry column
'COST', -- Node cost column
'ROADS_LINKS', -- Link table name
'LINK_GEOMETRY', -- Link geometry column
'DIRECTED', -- Link direction
'COST', -- Link cost column
'ROADS_PATHS', -- Path table name
'PATH_GEOMETRY', -- Path geometry column
'ROADS_PATHS_LINKS', -- Path-link table
NULL -- No partition table
);
-- 5. Use various SDO_NET functions and procedures.
-- Validate the network.
SELECT SDO_NET.VALIDATE_NETWORK('ROADS_NETWORK') FROM DUAL;
-- Validate parts or aspects of the network.
SELECT SDO_NET.VALIDATE_LINK_SCHEMA('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.VALIDATE_LRS_SCHEMA('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.VALIDATE_NODE_SCHEMA('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.VALIDATE_PATH_SCHEMA('ROADS_NETWORK') FROM DUAL;
-- Retrieve various information (GET_xxx and some other functions).
SELECT SDO_NET.GET_CHILD_LINKS('ROADS_NETWORK', 101) FROM DUAL;
SELECT SDO_NET.GET_CHILD_NODES('ROADS_NETWORK', 1) FROM DUAL;
SELECT SDO_NET.GET_GEOMETRY_TYPE('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_IN_LINKS('ROADS_NETWORK', 3) FROM DUAL;
SELECT SDO_NET.GET_LINK_COST_COLUMN('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_LINK_DIRECTION('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_LINK_GEOM_COLUMN('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_LINK_GEOMETRY('ROADS_NETWORK', 103) FROM DUAL;
SELECT SDO_NET.GET_LINK_TABLE_NAME('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_LRS_GEOM_COLUMN('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_LRS_LINK_GEOMETRY('ROADS_NETWORK', 103) FROM DUAL;
SELECT SDO_NET.GET_LRS_NODE_GEOMETRY('ROADS_NETWORK', 3) FROM DUAL;
SELECT SDO_NET.GET_LRS_TABLE_NAME('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NETWORK_TYPE('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NO_OF_HIERARCHY_LEVELS('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NO_OF_LINKS('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NO_OF_NODES('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NO_OF_PARTITIONS('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NODE_DEGREE('ROADS_NETWORK', 3) FROM DUAL;
SELECT SDO_NET.GET_NODE_GEOM_COLUMN('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NODE_GEOMETRY('ROADS_NETWORK', 3) FROM DUAL;
SELECT SDO_NET.GET_NODE_IN_DEGREE('ROADS_NETWORK', 3) FROM DUAL;
SELECT SDO_NET.GET_NODE_OUT_DEGREE('ROADS_NETWORK', 3) FROM DUAL;
SELECT SDO_NET.GET_NODE_TABLE_NAME('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_OUT_LINKS('ROADS_NETWORK', 3) FROM DUAL;
SELECT SDO_NET.GET_PARTITION_TABLE_NAME('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_PATH_GEOM_COLUMN('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_PATH_TABLE_NAME('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.IS_HIERARCHICAL('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.IS_LOGICAL('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.IS_SPATIAL('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.LRS_GEOMETRY_NETWORK('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.NETWORK_EXISTS('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.SDO_GEOMETRY_NETWORK('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.TOPO_GEOMETRY_NETWORK('ROADS_NETWORK') FROM DUAL;
-- Copy a network.
EXECUTE SDO_NET.COPY_NETWORK('ROADS_NETWORK', 'ROADS_NETWORK2');
This section presents an example of a logical network that contains the nodes and links illustrated in Figure 6-6. Because it is a logical network, there are no spatial geometries associated with it. (Figure 6-6 is essentially the same as Figure 6-1 in Section 6.5, but with the nodes and links labeled.)
Figure 6-6 Nodes and Links for Logical Network Example
As shown in Figure 6-6:
The network is hierarchical, with two levels. The top level (level 2) consists of two nodes (HN1 and HN2) and one link (HN1HN2) that links these nodes. The remaining nodes and links are in the bottom level (level 1) of the hierarchy.
Each node in level 1 is a child node of one of the nodes in level 2. Node HN1 has the following child nodes: N1, N2, N3, N4, N5, and N6. Node HN2 has the following child nodes: N7, N8, N9, N10, N11, N12, N13, and N14.
Two links (N5N8 and N6N7) in level 1 are child links of the link HN1HN2 in level 2.
Example 6-4 does the following:
Creates and populates the node table.
Creates and populates the link table.
Creates and populates the path table and path-link table, for possible future use. (Before an application can use paths, you must populate these two tables.)
Inserts network metadata into the USER_SDO_NETWORK_METADATA view.
Uses various SDO_NET functions and procedures.
Example 6-4 Logical Network Example (PL/SQL)
-- Basic steps:
-- 1. Create and populate the node table.
-- 2. Create and populate the link table.
-- 3. Create the path table and path-link table (for possible
-- future use, before which they will need to be populated).
-- 4. Populate the network metadata (USER_SDO_NETWORK_METADATA).
-- Note: Can be done before or after Steps 1-3.
-- 5. Use various SDO_NET functions and procedures.
-- 1. Create and populate the node table.
EXECUTE SDO_NET.CREATE_NODE_TABLE('XYZ_NODES', NULL, NULL, NULL, 2);
-- Populate the node table, starting with the highest level in the hierarchy.
-- HN1 (Hierarchy level=2, highest in this network)
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level)
VALUES (1, 'HN1', 'Y', 2);
-- HN2 (Hierarchy level=2, highest in this network)
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level)
VALUES (2, 'HN2', 'Y', 2);
-- N1 (Hierarchy level 1, parent node ID = 1 for N1 through N6)
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level,
parent_node_id)
VALUES (101, 'N1', 'Y', 1, 1);
-- N2
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level,
parent_node_id)
VALUES (102, 'N2', 'Y', 1, 1);
-- N3
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level,
parent_node_id)
VALUES (103, 'N3', 'Y', 1, 1);
-- N4
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level,
parent_node_id)
VALUES (104, 'N4', 'Y', 1, 1);
-- N5
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level,
parent_node_id)
VALUES (105, 'N5', 'Y', 1, 1);
-- N6
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level,
parent_node_id)
VALUES (106, 'N6', 'Y', 1, 1);
-- N7 (Hierarchy level 1, parent node ID = 2 for N7 through N14)
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level,
parent_node_id)
VALUES (107, 'N7', 'Y', 1, 2);
-- N8
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level,
parent_node_id)
VALUES (108, 'N8', 'Y', 1, 2);
-- N9
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level,
parent_node_id)
VALUES (109, 'N9', 'Y', 1, 2);
-- N10
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level,
parent_node_id)
VALUES (110, 'N10', 'Y', 1, 2);
-- N11
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level,
parent_node_id)
VALUES (111, 'N11', 'Y', 1, 2);
-- N12
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level,
parent_node_id)
VALUES (112, 'N12', 'Y', 1, 2);
-- N13
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level,
parent_node_id)
VALUES (113, 'N13', 'Y', 1, 2);
-- N14
INSERT INTO xyz_nodes (node_id, node_name, active, hierarchy_level,
parent_node_id)
VALUES (114, 'N14', 'Y', 1, 2);
-- 2. Create and populate the link table.
EXECUTE SDO_NET.CREATE_LINK_TABLE('XYZ_LINKS', NULL, NULL, 'COST', 2);
-- Populate the link table.
-- HN1HN2 (single link in highest hierarchy level: link level = 2)
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level)
VALUES (1001, 'HN1HN2', 1, 2, 'Y', 2);
-- For remaining links, link level = 1 and cost (10, 20, or 30) varies among links.
-- N1N2
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1101, 'N1N2', 101, 102, 'Y', 1, 10);
-- N1N3
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1102, 'N1N3', 101, 102, 'Y', 1, 20);
-- N2N3
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1103, 'N2N3', 102, 103, 'Y', 1, 30);
-- N3N4
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1104, 'N3N4', 103, 104, 'Y', 1, 10);
-- N4N5
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1105, 'N4N5', 104, 105, 'Y', 1, 20);
-- N4N6
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1106, 'N4N6', 104, 106, 'Y', 1, 30);
-- N5N6
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1107, 'N5N6', 105, 106, 'Y', 1, 10);
-- N5N8 (child of the higher-level link: parent ID = 1001)
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost, parent_link_id)
VALUES (1108, 'N5N8', 105, 106, 'Y', 1, 20, 1001);
-- N6N7 (child of the higher-level link: parent ID = 1001)
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost, parent_link_id)
VALUES (1109, 'N6N7', 106, 107, 'Y', 1, 30, 1001);
-- N7N9
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1110, 'N7N9', 107, 109, 'Y', 1, 10);
-- N8N9
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1111, 'N8N9', 108, 109, 'Y', 1, 20);
-- N9N10
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1112, 'N9N10', 109, 110, 'Y', 1, 30);
-- N9N13
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1113, 'N9N13', 109, 113, 'Y', 1, 10);
-- N10N11
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1114, 'N10N11', 110, 111, 'Y', 1, 20);
-- N11N12
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1115, 'N11N12', 111, 112, 'Y', 1, 30);
-- N12N13
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1116, 'N12N13', 112, 113, 'Y', 1, 10);
-- N12N14
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1117, 'N12N14', 112, 114, 'Y', 1, 20);
-- N13N14
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active,
link_level, cost)
VALUES (1118, 'N13N14', 113, 114, 'Y', 1, 30);
-- 3. Create the path table (to store created paths) and the path-link
-- table (to store links for each path) for possible future use,
-- before which they will need to be populated.
EXECUTE SDO_NET.CREATE_PATH_TABLE('XYZ_PATHS', NULL);
EXECUTE SDO_NET.CREATE_PATH_LINK_TABLE('XYZ_PATHS_LINKS');
-- 4. Populate the network metadata (USER_SDO_NETWORK_METADATA).
INSERT INTO user_sdo_network_metadata VALUES (
'XYZ_NETWORK', -- Network name
'LOGICAL', -- Network category
NULL, -- Null geom type because not a spatial network
NULL, -- No user-specified network type string
2, -- No. of levels in hierarchy
1, -- No. of partitions
NULL, -- No LRS table name
NULL, -- No LRS geometry column
'XYZ_NODES', -- Node table name
NULL, -- No node geometry column
NULL, -- No node cost column
'XYZ_LINKS', -- Link table name
NULL, -- No link geometry column
'DIRECTED', -- Link direction
'COST', -- Link cost column
'XYZ_PATHS', -- Path table name
NULL, -- No path geometry column
NULL, -- No path-link table
NULL -- No partition table
);
-- 5. Use various SDO_NET functions and procedures.
-- Validate the network.
SELECT SDO_NET.VALIDATE_NETWORK('XYZ_NETWORK') FROM DUAL;
-- Validate parts or aspects of the network.
SELECT SDO_NET.VALIDATE_LINK_SCHEMA('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.VALIDATE_LRS_SCHEMA('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.VALIDATE_NODE_SCHEMA('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.VALIDATE_PATH_SCHEMA('XYZ_NETWORK') FROM DUAL;
-- Retrieve various information (GET_xxx and some other functions).
SELECT SDO_NET.GET_CHILD_LINKS('XYZ_NETWORK', 1001) FROM DUAL;
SELECT SDO_NET.GET_CHILD_NODES('XYZ_NETWORK', 1) FROM DUAL;
SELECT SDO_NET.GET_CHILD_NODES('XYZ_NETWORK', 2) FROM DUAL;
SELECT SDO_NET.GET_IN_LINKS('XYZ_NETWORK', 104) FROM DUAL;
SELECT SDO_NET.GET_LINK_COST_COLUMN('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_LINK_DIRECTION('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_LINK_TABLE_NAME('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NETWORK_TYPE('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NO_OF_HIERARCHY_LEVELS('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NO_OF_LINKS('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NO_OF_NODES('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NODE_DEGREE('XYZ_NETWORK', 104) FROM DUAL;
SELECT SDO_NET.GET_NODE_IN_DEGREE('XYZ_NETWORK', 104) FROM DUAL;
SELECT SDO_NET.GET_NODE_OUT_DEGREE('XYZ_NETWORK', 104) FROM DUAL;
SELECT SDO_NET.GET_OUT_LINKS('XYZ_NETWORK', 104) FROM DUAL;
SELECT SDO_NET.GET_PATH_TABLE_NAME('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.IS_HIERARCHICAL('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.IS_LOGICAL('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.IS_SPATIAL('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.LRS_GEOMETRY_NETWORK('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.NETWORK_EXISTS('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.SDO_GEOMETRY_NETWORK('XYZ_NETWORK') FROM DUAL;
SELECT SDO_NET.TOPO_GEOMETRY_NETWORK('XYZ_NETWORK') FROM DUAL;
-- Copy a network.
EXECUTE SDO_NET.COPY_NETWORK('XYZ_NETWORK', 'XYZ_NETWORK2');
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|