Skip Headers
Oracle® Spatial Topology and Network Data Models
10g Release 2 (10.2)

Part Number B14256-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

5 Network Data Model Overview

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:

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:

5.1 Introduction to Network Modeling

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:

In additional to logical network information, spatial information such as node locations and link geometries can be associated with the 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 Spatial network data model can be used can be used for large, complex networks. For example, Figure 5-1 shows New York City nodes and links, which have been defined using the network data model, displayed using the Network Editor demo tool (described in Section 5.12).

Figure 5-1 New York City Nodes and Links

Description of Figure 5-1 follows
Description of "Figure 5-1 New York City Nodes and Links"

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 and SDO_NET_MEM packages) for creating, managing, editing, and analyzing networks in the database, and a middle-tier (or client-side) Java API for network editing and analysis.

5.2 Main Steps in Using the Network Data Model

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:

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 5.10.)

5.2.1 Letting Spatial Perform Most Operations

To create a network by letting Spatial perform most of the necessary operations, follow these steps:

  1. 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:

    Each of these procedures creates the necessary network data model tables (described in Section 5.8) and inserts a row with the appropriate network metadata information into the xxx_SDO_NETWORK_METADATA views (described in Section 5.9.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.

  2. 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 5.8.)

  3. Validate the network, using the SDO_NET.VALIDATE_NETWORK procedure.

  4. 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.

    If you plan to use a view as a node, link, or path table, you must specify the view name for the TABLE_NAME column value when you insert information about the node, link, or path table in the USER_SDO_GEOM_METADATA view.

5.2.2 Performing the Operations Yourself

To create a network by performing the necessary operations yourself, follow these steps:

  1. Create the node table, using the SDO_NET.CREATE_NODE_TABLE procedure. (The node table is described in Section 5.8.1.)

  2. Insert data into the node table.

  3. Create the link table, using the SDO_NET.CREATE_LINK_TABLE procedure. (The link table is described in Section 5.8.2).

  4. Insert data into the link table.

  5. Optionally, create the path table, using the SDO_NET.CREATE_PATH_TABLE procedure. (The path table is described in Section 5.8.3).

  6. 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 5.8.4).

  7. If you created the path table and if you want to create paths, insert data into the table.

  8. If you inserted data into the path table, insert the appropriate rows into the path-link table.

  9. 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 5.9.1.)

    If you plan to use a view as a node, link, path, or path-link table, you must specify the view name for the relevant columns when you insert information about the network in the USER_SDO_NETWORK_METADATA view.

  10. 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.

    If you plan to use a view as a node, link, or path table, you must specify the view name for the TABLE_NAME column value when you insert information about the node, link, or path table in the USER_SDO_GEOM_METADATA view.

  11. Validate the network, using the SDO_NET.VALIDATE_NETWORK function.

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.

5.3 Network Data Model Concepts

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 (that is, by default, the start and end nodes determine link direction) or undirected (that is, links can be traversed in either direction).

The following are some key terms related to the network data model:

5.4 Network Applications

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.

5.4.1 Road Network Example

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.

5.4.2 Train (Subway) Network Example

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.

5.4.3 Utility Network Example

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.

5.4.4 Biochemical Network Example

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.

5.5 Network Hierarchy

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 enables 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. Sibling nodes are nodes that have the same parent node.

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. Sibling links are links that have the same parent link.

Figure 5-2 shows a simple hierarchical network, in which there are two levels.

Figure 5-2 Network Hierarchy

Description of Figure 5-2 follows
Description of "Figure 5-2 Network Hierarchy"

As shown in Figure 5-2:

Although it is not shown in Figure 5-2, 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.

5.6 Network Constraints

Network constraints are restrictions defined on network analysis computations. For example, a network constraint might list a series of prohibited turns in a roads network due to one-way streets and "No Left Turn" signs, with each prohibited turn represented as a pair of links (a start link and an end link onto which a turn cannot be made from the start link). As another example, a network constraint might require that driving routes must not include toll roads or must not include expressways.

To create a network constraint, you must create a Java class that implements the constraint, and you must register the constraint by inserting a row for it in the USER_SDO_NETWORK_CONSTRAINTS view (described in Section 5.9.2). To apply a network constraint to a network analysis operation, specify the constraint using the constraint parameter with the appropriate SDO_NET_MEM subprogram.

Examples of Java classes to implement network constraints are provided in the network data model demo files, which are described in Section 5.12. For example, the ProhibitedTurns.java file creates a network constraint that defines a series of prohibited turns, and it then returns the shortest path between two nodes, first without applying the constraint and then applying the constraint.

5.7 Network Editing and Analysis Using a Network Memory Object

This section describes how to perform network editing and analysis operations using a network memory object, which is a cache in virtual memory. You can load a network or a hierarchy level in a network into a network memory object, perform operations on network objects in the memory object, and then either discard any changes or write the changes to the network in the database.

Multiple network memory objects can exist at a time for a specified network, but only one can be updatable; any others must be read-only. For better performance, if you plan to use the network memory object only to retrieve information or to perform network analysis operations, make the network memory object read-only (that is, specify allow_updates=>'FALSE' with the SDO_NET_MEM.NETWORK_MANAGER.READ_NETWORK procedure).

To work with a network memory object, you can use either the PL/SQL API (specifically, the SDO_NET_MEM package) or the Java API. Both APIs are introduced in Section 5.10.

In the network data model PL/SQL API, the subprograms in the SDO_NET package operate on the network in the database, and the subprograms in the SDO_NET_MEM package operate on the network memory object in the cache. For some network editing operations (such as adding a node, link, or path), you can use either an SDO_NET or SDO_NET_MEM procedure; however, if you are performing a large number of editing operations, using the cache (SDO_NET_MEM procedures) offers better performance. Most network operations, though, can be performed only by a subprogram in the SDO_NET or SDO_NET_MEM package, and in these cases your decision about whether to use a network memory object depends on your specific needs.

Example 5-1 uses a network memory object to add a new node and a new link to an existing network, perform a shortest path analysis, print the analysis results, and save the changes and analysis results in the database. These steps assume that a logical network named XYZ_NETWORK has already been created and populated using the statements shown in Example 5-5 in Section 5.11.4.

Example 5-1 Using a Network Memory Object for Editing and Analysis (PL/SQL)

DECLARE
  path_id     NUMBER;
  res_numeric NUMBER;
  res_array   SDO_NUMBER_ARRAY;
  indx        NUMBER;
 
BEGIN 
-- Create a network memory object in the user session for the 
-- logical network named XYZ_NETWORK. This creates a network 
-- object and reads all metadata, nodes, links, and paths in 
-- the network, and it allows for updates to be performed.
sdo_net_mem.network_manager.read_network(net_mem=>'XYZ_NETWORK', 
  allow_updates=>'TRUE');
 
-- Add a node with ID=901, and set its name to N901 and cost to 5.
sdo_net_mem.network.add_node(net_mem=>'XYZ_NETWORK', node_id=>901,
  node_name=>'N901', external_network_id=>0, external_node_id=>0);
sdo_net_mem.node.set_cost(net_mem=>'XYZ_NETWORK', node_id=>901, cost=>5);
 
-- Add a link with ID=9901, name=N901N1, cost=20 from node N901 to node N1.
sdo_net_mem.network.add_link(net_mem=>'XYZ_NETWORK', link_id=>9901, 
  link_name=>'N901N1', start_node_id=>901, end_node_id=>101, cost=>20);
 
-- Perform a shortest path analysis from node N1 to node N5.
path_id := sdo_net_mem.network_manager.shortest_path('XYZ_NETWORK', 101, 105);
DBMS_OUTPUT.PUT_LINE('The ID of the shortest path from N1 to N5 is: ' || path_id);
 
-- List the properties of the path: cost, nodes, and links.
res_numeric := sdo_net_mem.path.get_cost('XYZ_NETWORK', path_id);
DBMS_OUTPUT.PUT_LINE('The cost of this path is: ' || res_numeric);
res_array:= sdo_net_mem.path.get_node_ids('XYZ_NETWORK', path_id);
DBMS_OUTPUT.PUT('This path has the following nodes: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
res_array:= sdo_net_mem.path.get_link_ids('XYZ_NETWORK', path_id);
DBMS_OUTPUT.PUT('This path has the following links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- Add the path to the network memory object.
sdo_net_mem.network.add_path(net_mem=>'XYZ_NETWORK', path_id=>path_id);
 
-- Write changes to the database and commit changes.
sdo_net_mem.network_manager.write_network(net_mem=>'XYZ_NETWORK');
 
-- Drop the network memory object.
sdo_net_mem.network_manager.drop_network(net_mem=>'XYZ_NETWORK');
 
END;
/
The ID of the shortest path from N1 to N5 is: 1
The cost of this path is: 50
This path has the following nodes: 101 103 104 105
This path has the following links: 1102 1104 1105

5.8 Network Data Model Tables

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. Thus, 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.

The following considerations apply to schema, table, and column names that are stored in any Oracle Spatial metadata views. For example, these considerations apply to the names of node, link, path, and path-link tables, and to the names of any columns in these tables that are stored in the network metadata views described in Section 5.9.

5.8.1 Node Table

Each network has a node table that can contain the columns described in Table 5-1. (The specific columns depend on the network type and whether the network is hierarchical or not.)

Table 5-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, the SDO_GEOMETRY object associated with the node

For a spatial topology network, the SDO_TOPO_GEOMETRY object associated with the node

For a spatial LRS network, GEOM_ID and MEASURE column values (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 geom_column parameter value in the call to the SDO_NET.CREATE_NODE_TABLE procedure.

<node_cost_column>

NUMBER

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 5.5 explains network hierarchy.)

PARENT_NODE_ID

NUMBER

For hierarchical networks only: node ID of the parent node of this node. (Section 5.5 explains network hierarchy.)


5.8.2 Link Table

Each network has a link table that contains the columns described in Table 5-2.

Table 5-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_MEASURE

SDO_GEOMETRY, or SDO_TOPO_GEOMETRY, or NUMBER

For a spatial (SDO, non-LRS) network, the SDO_GEOMETRY object associated with the link

For a spatial topology network, the SDO_TOPO_GEOMETRY object associated with the link

For a spatial LRS network, GEOM_ID, START_MEASURE, and END_MEASURE column values (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 geom_column parameter value in the call to the SDO_NET.CREATE_LINK_TABLE procedure.

<link_cost_column>

NUMBER

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 5.5 explains parent-child relationships in a network hierarchy.)

BIDIRECTED

VARCHAR2(1)

For directed networks only: contains Y if the link is bidirected (that is, can be traversed either from the start node to the end node or from the end node to the start node), or N if the link is unidirected (in one direction only, from the start node to the end node).


5.8.3 Path Table

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 5.8.4). Each path table contains the columns described in Table 5-3.

Table 5-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

PATH_TYPE

VARCHAR2(24)

User-defined string to identify the path type

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

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, 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.


5.8.4 Path-Link Table

For each path table (described in Section 5.8.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; that is, each combination of PATH_ID, LINK_ID, and SEQ_NO values must be unique within the network. The order of rows in the path-link table is not significant. Each path-link table contains the columns described in Table 5-4.

Table 5-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

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.


5.9 Network Data Model Metadata Views

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.

5.9.1 xxx_SDO_NETWORK_METADATA Views

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 5-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 5-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(24)

Name of the network

NETWORK_ID

NUMBER

ID number of the network; assigned by Spatial

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. A value of LOGICAL causes the network data model PL/SQL and Java APIs to ignore any spatial attributes of nodes, links, and paths.

GEOMETRY_TYPE

VARCHAR2(24)

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 5.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(32)

If GEOMETRY_TYPE is SDO_GEOMETRY, contains the name of the table containing geometries associated with nodes.

LRS_GEOM_COLUMN

VARCHAR2(32)

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 5.8.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(1024)

If NODE_TABLE_NAME contains a table name, identifies the cost column in that table, or a PL/SQL function to compute the cost value.

NODE_PARTITION_COLUMN

VARCHAR2(32)

Reserved for future use.

NODE_DURATION_COLUMN

VARCHAR2(32)

If NODE_TABLE_NAME contains a table name, identifies the optional duration column in that table. This column can contain a numeric value that has any user-defined significance, such as a number of minutes associated with the node.

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 5.8.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(1024)

If LINK_TABLE_NAME contains a table name, identifies the optional numeric column containing a cost value for each link, or a PL/SQL function to compute the cost value.

LINK_PARTITION_COLUMN

VARCHAR2(32)

Reserved for future use.

LINK_DURATION_COLUMN

VARCHAR2(32)

If LINK_TABLE_NAME contains a table name, identifies the optional duration column in that table. This column can contain a numeric value that has any user-defined significance, such as a number of minutes associated with the link.

PATH_TABLE_NAME

VARCHAR2(32)

Contains the name of an optional table containing information about paths. (The path table is described in Section 5.8.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 5.8.4.)

PARTITION_TABLE_NAME

VARCHAR2(32)

Reserved for future use.

TOPOLOGY

VARCHAR2(32)

For a spatial network containing SDO_TOPO_GEOMETRY objects (creating using the SDO_NET.CREATE_TOPO_NETWORK procedure), contains the name of the topology.


5.9.2 xxx_SDO_NETWORK_CONSTRAINTS Views

The following views contain information about network constraints (described in Section 5.6):

  • USER_SDO_NETWORK_CONSTRAINTS contains information about all network constraints owned by the user.

  • ALL_SDO_NETWORK_CONSTRAINTS contains information about all network constraints on which the user has SELECT permission.

To enable a network constraint to be specified with the constraint parameter in an SDO_NET_MEM subprogram, you must insert information about the network constraint into the USER_SDO_NETWORK_CONSTRAINTS view.

The USER_SDO_NETWORK_CONSTRAINTS and ALL_SDO_NETWORK_CONSTRAINTS views contain the same columns, as shown Table 5-6, except that the USER_SDO_NETWORK_CONSTRAINTS view does not contain the OWNER column. (The columns are listed in their order in the view definition.)

Table 5-6 Columns in the xxx_SDO_NETWORK_CONSTRAINTS Views

Column Name Data Type Purpose

OWNER

VARCHAR2(32)

Owner of the network constraint (ALL_SDO_NETWORK_CONSTRAINTS view only)

CONSTRAINT

VARCHAR2(32)

Name of the network constraint

DESCRIPTION

VARCHAR2(200)

Descriptive information about the network constraint, such as its purpose and any usage notes

CLASS_NAME

VARCHAR2(32)

Name of the Java class that implements the network constraint

CLASS

BINARY FILE LOB

The Java class that implements the network constraint


5.10 Network Data Model Application Programming Interface

The Oracle Spatial network data model includes two client application programming interfaces (APIs): a PL/SQL interface provided by the SDO_NET and SDO_NET_MEM packages and a Java interface. Both interfaces let you create and update network data, and perform network analysis. It is recommended that you use only PL/SQL or SQL to populate network tables and to create indexes, and that you use either PL/SQL or Java for application development.

The following performance considerations apply to the PL/SQL and Java APIs:

5.10.1 Network Data Model PL/SQL Interface

The SDO_NET package provides subprograms for creating, accessing, and managing networks on a database server. The SDO_NET_MEM package, which implements capabilities available through the Java API, provides subprograms for editing network objects and performing network analysis using a cache object called a network memory object. Example 5-4 in Section 5.11 shows the use of SDO_NET functions and procedures. Section 5.7 explains how to use a network memory object, and it contains Example 5-1, which uses SDO_NET_MEM functions and procedures.

The SDO_NET subprograms can be grouped into the following logical categories:

For reference information about each SDO_NET function and procedure, see Chapter 6.

The SDO_NET_MEM subprograms are grouped according to their associated object-related class in the oracle.spatial.network interface or class. You must specify a prefix after SDO_NET_MEM for each program, depending on its associated class (for example, SDO_NET_MEM.NETWORK_MANAGER.CREATE_LOGICAL_NETWORK, SDO_NET_MEM.NETWORK.ADD_NODE, and SDO_NET_MEM.NODE.GET_COST).

Note:

Although this manual refers to "the SDO_NET_MEM package," the subprograms are actually implemented as methods of several object types. Thus, they are not listed by the statement DESCRIBE SDO_NET_MEM.

The SDO_NET_MEM subprogram groupings are as follows:

  • SDO_NET_MEM.NETWORK_MANAGER subprograms are related to the oracle.spatial.network.NetworkManager Java class. They enable you to create and drop network memory objects and to perform network analysis.

  • SDO_NET_MEM.NETWORK subprograms are related to the oracle.spatial.network.Network Java interface. They enable you to add and delete nodes, links, and paths.

  • SDO_NET_MEM.NODE subprograms are related to the oracle.spatial.network.Node Java interface. They enable you to get and set attributes for nodes.

  • SDO_NET_MEM.LINK subprograms are related to the oracle.spatial.network.Link Java interface. They enable you to get and set attributes for links.

  • SDO_NET_MEM.PATH subprograms are related to the oracle.spatial.network.Path Java interface. They enable you to get and set attributes for paths.

The associations between SDO_NET_MEM subprograms and methods of the Java API are not necessarily exact. In some cases, a PL/SQL subprogram may combine operations and options from several methods. In addition, some Java methods do not have PL/SQL counterparts. Thus, the Usage Notes for subprograms state only that the function or procedure is analogous to a specific Java method, to indicate a logical relationship between the two. For detailed information about a specific Java method and others that may be related, see the Javadoc-generated API documentation (briefly explained in Section 5.10.2).

For reference information about each SDO_NET_MEM function and procedure, see Chapter 7.

5.10.2 Network Data Model Java Interface

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 5-3 is a Unified Modeling Language (UML) diagram that shows the relationship between the main classes and interfaces.

Figure 5-3 Java Classes and Interfaces for Network Data Model

Description of Figure 5-3 follows
Description of "Figure 5-3 Java Classes and Interfaces for Network Data Model"

Section 5.10.2.3 lists the major interfaces and their methods. 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.

5.10.2.1 Network Metadata and Data Management

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

5.10.2.2 Network Analysis

You can use the oracle.spatial.network.NetworkManager class 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" (TSP) analysis: Given a set of nodes, find the most efficient (lowest-cost or shortest distance) path that visits all nodes, and optionally require that the start and end nodes be the same.

5.10.2.3 Major Interfaces and Methods in the Network Java API

This section lists methods included in the major interfaces of the network data model Java API. For detailed reference information, see the Javadoc-generated API documentation: open index.html in a directory that includes the path sdonm/doc/javadoc.

The oracle.spatial.network.NetworkManager class includes the following methods:

allPairsShortestPath
allPaths
createNetworkPartitionTable
createRandomGraph
createRandomTree
createRefConstraints
disableRefConstraints
dropNetwork
dropRefConstraints
enableRefConstraints
findConnectedComponents
findExternalLinks
findInternalLinks
findMBR
findReachableNodes
findReachingNodes
getMaxFlow
getNetworkIDFromName
getNetworkIDs
getNetworkNameFromID
getNetworkNames
getNodePartitionID
getPartitionIDArray
getPartitionLog
getVersion
initNodeLocation
insertGeomMetadata
isNetworkPartitioned
isPartitionedByNode
isReachable
layout
makeLogical
makeSpatial
mcst
mcstLinkArray
nearestNeighbors
networkExists
partitionNetwork
readBlobNetwork
readNetwork
readNetworkByLinkPartition
readNetworkByNodePartition
readNetworkFromXML
readNetworkMetadata
readNetworkPartition
shortestPath
shortestPathAStar
shortestPathDijkstra
shortestPaths
spatialCluster
tspPath
validateNetworkSchema
withinCost
writeNetwork
writeNetworkMetadata
writeNetworkPartition
writeNetworkToXML

The oracle.spatial.network.Network interface includes the following methods:

addTemporaryNode
addLink
addLinks
addNetwork
addNode
addNodes
addPath
addPaths
clear
clearTemporaryCache
clone
containsLink
containsNode
containsPath
deleteTemporaryElements
deleteLink
deleteNetwork
deleteNode
deletePath
getTemporaryLinkArray
getTemporaryNodeArray
getExternalLinks
getExternalNetworkIDs
getExternalNodes
getLink
getLinkArray
getLinkCostColumn
getLinkDurationColumn
getLinkGeomColumn
getLinkHierarchyLevelArray
getLinkPartitionColumn
getLinks
getLinksByPartitionID
getLinkTableName
getLRSGeomColumn
getLRSTableName
getMaxLinkID
getMaxNodeID
getMaxPathID
getMBR
getMetadata
getMetadataViewName
getName
getNetworkAt
getNetworkCache
getNetworkID
getNode
getNodeArray
getNodeCostColumn
getNodeDurationColumn
getNodeGeomColumn
getNodeHierarchyLevelArray
getNodePartitionColumn
getNodes
getNodesByPartitionID
getNodeTableName
getNoOfHierarchyLevels
getNoOfLinks
getNoOfNodes
getNoOfPartitions
getNoOfPaths
getPartitionID
getPartitionTableName
getPath
getPathArray
getPathGeomColumn
getPathLinkTableName
getPaths
getPathsByPartitionID
getPathTableName
getSubNetwork
getUserData
hasNodeCost
hasStringID
intersectNetwork
isBidirected
isConnected
isDirected
isHierarchical
isLogical
isLRSGeometry
isModified
isPartition
isPartitioned
isPersistent
isReadOnly
isSDOGeometry
isSimple
isSpatial
isTopoGeometry
isTree
isUndirected
partitionedByLink
partitionedByNode
resetComponentNo
setCategory
setLinkDurationColumn
setLinkPartitionColumn
setNodeDurationColumn
setNodePartitionColumn
setNoOfHierarchyLevels
setPartitionTableName
setStringIDIndex
setTemporaryCaching
setUserData

The oracle.spatial.network.Link interface includes the following methods:

clone
getChildLinkArray
getChildLinks
getCoLinks
getCost
getDuration
getEndMeasure
getEndNode
getFlow
getGeometry
getGeomID
getID
getLinkLevel
getName
getNetwork
getParentLink
getPartitionID
getSiblingLinkArray
getSiblings
getStartMeasure
getStartNode
getState
getType
getUserData
isActive
isBidirected
isTemporary
isExternalLink
isLogical
isUnidirected
makeTemporary
otherNode
setBidirected
setCost
setDuration
setEndNode
setFlow
setGeometry
setGeomID
setLinkLevel
setMeasure
setName
setParentLink
setPartitionID
setStartNode
setState
setType
setUserData

The oracle.spatial.network.Node interface includes the following methods:

clone
findLinks
getAdjacentNodeArray
getAdjacentNodes
getChildNodeArray
getChildNodes
getComponentNo
getCost
getDuration
getExternalNetworkID
getExternalNetworkName
getExternalNodeID
getGeometry
getGeomID
getHierarchyLevel
getID
getIncidentLinks
getInLinks
getMDPoint
getMeasure
getName
getNetwork
getOutLinks
getParentNode
getPartitionID
getSiblingNodeArray
getSiblings
getState
getType
getUserData
isActive
isTemporary
isExternalNode
isLogical
isMarked
linkExists
makeTemporary
setComponentNo
setCost
setDuration
setExternalNetworkID
setExternalNodeID
setGeometry
setGeomID
setHierarchyLevel
setMDPoint
setMeasure
setName
setParentNode
setPartitionID
setState
setType
setUserData

The oracle.spatial.network.Path interface includes the following methods:

clone
computeGeometry
contains(Link l)
contains(Node n)
contains(Path path)
getCost
getDuration
getEndNode
getGeometry
getID
getLinkArray
getLinkAt
getLinks
getName
getNetwork
getNodeArray
getNodeAt
getNodes
getNoOfLinks
getStartNode
getState
getType
getUserData
isActive
isClosed
isConnected
isLogical
isSimple
isTemporary
setGeometry
setID
setName
setType
setUserData
size

5.11 Network Examples (PL/SQL)

This section presents simplified examples that use the network data model PL/SQL API. 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 6.

5.11.1 Simple Spatial (SDO) Network Example

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 5-4.

Figure 5-4 Simple Spatial (SDO) Network

Description of Figure 5-4 follows
Description of "Figure 5-4 Simple Spatial (SDO) Network"

As shown in Figure 5-4, 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 5-2 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 5-2 does not show the use of many SDO_NET functions and procedures; these are included in Example 5-4 in Section 5.11.3.

Example 5-2 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$ (node_id, node_name, active, geometry, cost)
  VALUES(1, 'N1', 'Y',
    SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(1,1,NULL), NULL, NULL),
    5);
-- N2
INSERT INTO sdo_net1_node$ (node_id, node_name, active, geometry, cost)
  VALUES(2, 'N2', 'Y',
    SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(15,1,NULL), NULL, NULL),
    8);
-- N3
INSERT INTO sdo_net1_node$ (node_id, node_name, active, geometry, cost)
  VALUES(3, 'N3', 'Y',
    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$ (link_id, link_name, start_node_id, end_node_id,
     active, geometry, cost, bidirected)
  VALUES(1, 'L1', 1, 2, 'Y',
    SDO_GEOMETRY(2002, NULL, NULL, 
      SDO_ELEM_INFO_ARRAY(1,2,1), 
        SDO_ORDINATE_ARRAY(1,1, 15,1)),
    14, 'Y');
-- L2
INSERT INTO sdo_net1_link$ (link_id, link_name, start_node_id, end_node_id,
     active, geometry, cost, bidirected)
   VALUES(2, 'L2', 2, 3, 'Y',
    SDO_GEOMETRY(2002, NULL, NULL, 
      SDO_ELEM_INFO_ARRAY(1,2,1), 
        SDO_ORDINATE_ARRAY(15,1, 9,4)),
    10, 'Y');
-- L3
INSERT INTO sdo_net1_link$ (link_id, link_name, start_node_id, end_node_id,
     active, geometry, cost, bidirected)
  VALUES(3, 'L3', 3, 1, 'Y',
    SDO_GEOMETRY(2002, NULL, NULL, 
      SDO_ELEM_INFO_ARRAY(1,2,1), 
        SDO_ORDINATE_ARRAY(9,4, 1,1)),
    10, 'Y');
 
-- 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
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  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
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  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;

5.11.2 Simple Logical Network Example

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 5-5.

Figure 5-5 Simple Logical Network

Description of Figure 5-5 follows
Description of "Figure 5-5 Simple Logical Network"

As shown in Figure 5-5, 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 5-3 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 5-3 does not show the use of many SDO_NET functions and procedures; these are included in the logical hierarchical network example (Example 5-5) in Section 5.11.4.

Example 5-3 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.

5.11.3 Spatial (LRS) Network Example

This section presents an example of a spatial (LRS) network that uses the roads (routes) illustrated in Figure 5-6. Each road is built from individual line segments (associated with links) taken from one or more road segment geometries, which are also shown in the figure.

Figure 5-6 Roads and Road Segments for Spatial (LRS) Network Example

Description of Figure 5-6 follows
Description of "Figure 5-6 Roads and Road Segments for Spatial (LRS) Network Example"

As shown in Figure 5-6:

  • 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.

  • The four road segment geometries are shown individually on the right side of the figure. (The points on each segment are labeled with their associated node names, to clarify how each segment geometry fits into the illustration on the left side.)

Example 5-4 does the following:

  • Creates a table to hold the road segment geometries.

  • Inserts four road segment geometries into the table.

  • Inserts the spatial metadata into the USER_SDO_GEOM_METADATA view.

  • Creates a spatial index on the geometry column in the ROAD_SEGMENTS 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 and SDO_NET_MEM functions and procedures.

Example 5-4 Spatial (LRS) Network Example (PL/SQL)

---------------------------------------------------------------------------
-- CREATE AND POPULATE TABLE --
---------------------------------------------------------------------------
-- Create a table for road segments. Use LRS.
CREATE TABLE road_segments (
  segment_id  NUMBER PRIMARY KEY,
  segment_name  VARCHAR2(32),
  segment_geom  SDO_GEOMETRY, 
  geom_id NUMBER);
 
-- Populate the table with road segments.
INSERT INTO road_segments VALUES(
  1,
  'Segment1',
  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. 
  ), 1001
);
 
INSERT INTO road_segments VALUES(
  2,
  'Segment2',
  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 road_segments VALUES(
  3,
  'Segment3',
  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,4,0,     -- Node4; 0 is measure from start.
      12,10,6,    -- Node5; 6 is measure from start. 
      8,13,11,    -- Node8; 11 is measure from start. 
      5,14,14.16) -- Ending point - Node7; 14.16 is measure from start.
  ), 1003
);
 
INSERT INTO road_segments VALUES(
  4,
  'Segment4',
  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,10,4,  -- Node6; 4 is measure from start.  
      5,14,9)  -- Ending point - Node7; 9 is measure from start.
  ), 1004
);
 
---------------------------------------------------------------------------
-- 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: road_segment and segment_geom).
INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  'ROAD_SEGMENTS',
  'SEGMENT_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 road_segments_idx ON road_segments(segment_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 paths and links 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.
-- 6. Use SDO_NET_MEM functions and procedures for analysis and editing.
 
-- 1. Create and populate the node table.
EXECUTE SDO_NET.CREATE_NODE_TABLE('ROADS_NODES', 'LRS_GEOMETRY', 'NODE_GEOMETRY', 'COST', 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', 1004, 0);
 
-- N6
INSERT INTO roads_nodes (node_id, node_name, active, geom_id, measure)
  VALUES (6, 'N6', 'Y', 1002, 6);
 
-- N7
INSERT INTO roads_nodes (node_id, node_name, active, geom_id, measure)
  VALUES (7, 'N7', 'Y', 1004, 9);
 
-- 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, 1003, 0, 6);
 
-- 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, 1004, 0, 4);
 
-- 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, 1004, 4, 9);
 
-- 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, 6, 11);
 
-- 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, 11, 14.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 
    (NETWORK,
     NETWORK_CATEGORY,
     GEOMETRY_TYPE,
     NETWORK_TYPE,
     NO_OF_HIERARCHY_LEVELS,
     NO_OF_PARTITIONS,
     LRS_TABLE_NAME,
     LRS_GEOM_COLUMN,
     NODE_TABLE_NAME,
     NODE_GEOM_COLUMN,
     NODE_COST_COLUMN,
     LINK_TABLE_NAME,
     LINK_GEOM_COLUMN,
     LINK_DIRECTION,
     LINK_COST_COLUMN,
     PATH_TABLE_NAME,
     PATH_GEOM_COLUMN,
     PATH_LINK_TABLE_NAME)
  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
    'ROAD_SEGMENTS',   -- LRS table name
    'SEGMENT_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'  -- Paths and links 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_INVALID_LINKS('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_INVALID_NODES('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_INVALID_PATHS('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_ISOLATED_NODES('ROADS_NETWORK') 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_CATEGORY('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NETWORK_ID('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NETWORK_NAME(3) 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_NODE_COST_COLUMN('ROADS_NETWORK') FROM DUAL;
SELECT SDO_NET.GET_NODE_HIERARCHY_LEVEL('ROADS_NETWORK', 3) 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_COMPLEX('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_SIMPLE('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');
 
-- Create a trigger.
EXECUTE SDO_NET.CREATE_DELETE_TRIGGER('ROADS_NETWORK');
 
-- 6. Use SDO_NET_MEM functions and procedures for analysis and editing.
 
-- Network analysis and other operations (SDO_NET_MEM.NETWORK_MANAGER)
 
DECLARE
  net_mem    VARCHAR2(100);
  res_string VARCHAR2(1000);
 
  cost        NUMBER;
  res_numeric NUMBER;
  res_array   SDO_NUMBER_ARRAY;
  indx        NUMBER;
 
  indx1        NUMBER;
  var1_numeric NUMBER;
  var1_array   SDO_NUMBER_ARRAY;
 
BEGIN
 
net_mem := 'ROADS_NETWORK';
  
-- Read in the network.
SDO_NET_MEM.NETWORK_MANAGER.READ_NETWORK(net_mem, 'TRUE');
 
-- Validate the network.
res_string := SDO_NET_MEM.NETWORK_MANAGER.VALIDATE_NETWORK_SCHEMA(net_mem);
DBMS_OUTPUT.PUT_LINE('Is network ' || net_mem || ' valid? ' || res_string);
  
res_string := SDO_NET_MEM.NETWORK_MANAGER.LIST_NETWORKS;
DBMS_OUTPUT.PUT_LINE('The current in-memory network(s) is/are: ' || res_string);
 
res_numeric := SDO_NET_MEM.NETWORK_MANAGER.FIND_CONNECTED_COMPONENTS(net_mem);
DBMS_OUTPUT.PUT_LINE('The number of connected components is: ' || res_numeric);
 
res_array := SDO_NET_MEM.NETWORK_MANAGER.MCST_LINK(net_mem);
DBMS_OUTPUT.PUT('Network ' || net_mem || ' has the following MCST links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
res_array := SDO_NET_MEM.NETWORK_MANAGER.FIND_REACHABLE_NODES(net_mem,1);
DBMS_OUTPUT.PUT_LINE('Reachable nodes from 1: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  res_numeric := res_array(indx);
  DBMS_OUTPUT.PUT(res_numeric || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
res_array := SDO_NET_MEM.NETWORK_MANAGER.NEAREST_NEIGHBORS(net_mem,6,3);
DBMS_OUTPUT.PUT_LINE('Path IDs to the nearest 3 neighbors of node 6 are: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  res_numeric := res_array(indx);
  DBMS_OUTPUT.PUT(res_numeric || ', which contains links: ');
  var1_array := SDO_NET_MEM.PATH.GET_LINK_IDS(net_mem, res_numeric);
    FOR indx1 IN var1_array.FIRST..var1_array.LAST
    LOOP
      var1_numeric := var1_array(indx1);
      DBMS_OUTPUT.PUT(var1_numeric || ' ');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' '); 
 
res_array := SDO_NET_MEM.NETWORK_MANAGER.NEAREST_NEIGHBORS(net_mem,6,3);
DBMS_OUTPUT.PUT_LINE('Path IDs to the nearest 3 neighbors of node 6 are: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  res_numeric := res_array(indx);
  DBMS_OUTPUT.PUT(res_numeric || ', whose end node is: ');
  var1_numeric := SDO_NET_MEM.PATH.GET_END_NODE_ID(net_mem, res_numeric);
  DBMS_OUTPUT.PUT(var1_numeric);
  DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' '); 
  
res_string := SDO_NET_MEM.NETWORK_MANAGER.IS_REACHABLE(net_mem,1,5);
DBMS_OUTPUT.PUT_LINE('Can node 1 reach node 5? ' || res_string);
  
res_array := SDO_NET_MEM.NETWORK_MANAGER.ALL_PATHS(net_mem,1,5,10,200,5);
DBMS_OUTPUT.PUT_LINE('For each path from node 1 to node 5: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  res_numeric := res_array(indx);
  DBMS_OUTPUT.PUT_LINE('Path ' || res_numeric ||
                       ' has the following properties: ');
  cost := SDO_NET_MEM.PATH.GET_COST(net_mem, res_numeric);
  DBMS_OUTPUT.PUT_LINE('Path ' || res_numeric || ' cost: ' || cost);
  res_string := SDO_NET_MEM.PATH.IS_CLOSED(net_mem, res_array(indx));  
  DBMS_OUTPUT.PUT_LINE('Is path ' || res_numeric || ' closed? ' || res_string);       
END LOOP;
   
DBMS_OUTPUT.PUT_LINE(' ');
res_numeric := SDO_NET_MEM.NETWORK_MANAGER.SHORTEST_PATH(net_mem,1,5);
DBMS_OUTPUT.PUT_LINE('The shortest path from node 1 to node 5 is path ID: ' || res_numeric);
 
DBMS_OUTPUT.PUT_LINE('The following are characteristics of this shortest path: ');
cost := SDO_NET_MEM.PATH.GET_COST(net_mem, res_numeric);
DBMS_OUTPUT.PUT_LINE('Path ' || res_numeric || ' has cost: ' || cost);
res_string := SDO_NET_MEM.PATH.IS_CLOSED(net_mem, res_numeric);  
DBMS_OUTPUT.PUT_LINE('Is path ' || res_numeric || ' closed? ' || res_string);  
 
DBMS_OUTPUT.PUT_LINE(' ');
res_numeric := SDO_NET_MEM.NETWORK_MANAGER.SHORTEST_PATH_DIJKSTRA(net_mem,1,5);
DBMS_OUTPUT.PUT_LINE('The shortest Dijkstra path from node 1 to node 5 is ' || res_numeric);
 
DBMS_OUTPUT.PUT_LINE('The following are characteristics of this shortest path: ');
cost := SDO_NET_MEM.PATH.GET_COST(net_mem, res_numeric);
DBMS_OUTPUT.PUT_LINE('Path ' || res_numeric || ' cost: ' || cost);
res_string := SDO_NET_MEM.PATH.IS_CLOSED(net_mem, res_numeric);  
DBMS_OUTPUT.PUT_LINE('Is path ' || res_numeric || ' closed? ' || res_string);  
    
res_array := SDO_NET_MEM.PATH.GET_LINK_IDS(net_mem, res_numeric);   
DBMS_OUTPUT.PUT('Path ' || res_numeric || ' has links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
  
res_array := SDO_NET_MEM.PATH.GET_NODE_IDS(net_mem, res_numeric);   
DBMS_OUTPUT.PUT('Path ' || res_numeric || ' has nodes: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
res_array := SDO_NET_MEM.NETWORK_MANAGER.WITHIN_COST(net_mem,2,20);
DBMS_OUTPUT.PUT('Path IDs to nodes within cost of 40 from node 2: ');
DBMS_OUTPUT.PUT_LINE(' ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  res_numeric := res_array(indx);
  DBMS_OUTPUT.PUT(res_numeric || ', whose end node is: ');
  var1_numeric := SDO_NET_MEM.PATH.GET_END_NODE_ID(net_mem, res_numeric);
  DBMS_OUTPUT.PUT(var1_numeric);
  DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' '); 
 
END;  
/
 
-- Link editing (SDO_NET_MEM.LINK)
 
DECLARE
  net_mem     VARCHAR2(32);
  res_string  VARCHAR2(100);
  res_numeric NUMBER;
  res_geom    SDO_GEOMETRY;
  res_array   SDO_NUMBER_ARRAY;
  indx        NUMBER;
 
BEGIN
 
net_mem := 'ROADS_NETWORK';
  
-- GET_COST
res_numeric := SDO_NET_MEM.LINK.GET_COST(net_mem, 104);
DBMS_OUTPUT.PUT_LINE('The cost of link 104 is: ' || res_numeric);
 
-- GET_END_MEASURE
res_numeric := SDO_NET_MEM.LINK.GET_END_MEASURE(net_mem, 104);
DBMS_OUTPUT.PUT_LINE('The end measure of link 104 is: ' || res_numeric);
 
-- GET_END_NODE_ID
res_numeric := SDO_NET_MEM.LINK.GET_END_NODE_ID(net_mem, 104);
DBMS_OUTPUT.PUT_LINE('The end node of link 104 is: ' || res_numeric);
 
-- GET_GEOM_ID
res_numeric := SDO_NET_MEM.LINK.GET_GEOM_ID(net_mem, 104);
DBMS_OUTPUT.PUT_LINE('The geometry ID of link 104 is: ' || res_numeric);
 
-- GET_GEOMETRY
res_geom := SDO_NET_MEM.LINK.GET_GEOMETRY(net_mem, 104);
 
-- GET_NAME
res_string := SDO_NET_MEM.LINK.GET_NAME(net_mem, 104);
DBMS_OUTPUT.PUT_LINE('The name of link 104 is: ' || res_string);
 
-- GET_START_MEASURE
res_numeric := SDO_NET_MEM.LINK.GET_START_MEASURE(net_mem, 104);
DBMS_OUTPUT.PUT_LINE('The start measure of link 104 is: ' || res_numeric);
 
-- GET_START_NODE_ID
res_numeric := SDO_NET_MEM.LINK.GET_START_NODE_ID(net_mem, 104);
DBMS_OUTPUT.PUT_LINE('The start node of link 104 is: ' || res_numeric);
 
-- GET_STATE
res_string := SDO_NET_MEM.LINK.GET_STATE(net_mem, 104);
DBMS_OUTPUT.PUT_LINE('The state of link 104 is: ' || res_string);
 
-- IS_ACTIVE
res_string := SDO_NET_MEM.LINK.IS_ACTIVE(net_mem, 104);
DBMS_OUTPUT.PUT_LINE('Is link 104 active?: ' || res_string);
 
-- IS_EXTERNAL_LINK
res_string := SDO_NET_MEM.LINK.IS_EXTERNAL_LINK(net_mem, 104);
DBMS_OUTPUT.PUT_LINE('Is link 104 an external link?: ' || res_string);
 
-- IS_LOGICAL
res_string := SDO_NET_MEM.LINK.IS_LOGICAL(net_mem, 104);
DBMS_OUTPUT.PUT_LINE('Is link 104 a logical link?: ' || res_string);
 
-- IS_TEMPORARY
res_string := SDO_NET_MEM.LINK.IS_TEMPORARY(net_mem, 104);
DBMS_OUTPUT.PUT_LINE('Is link 104 temporary?: ' || res_string);
 
-- SET_COST
-- Set the cost of link 302 to 6.
SDO_NET_MEM.LINK.SET_COST(net_mem, 302, 6);
 
-- SET_MEASURE
-- Set the measure value of link 302 as from 111 to 114.16.
SDO_NET_MEM.LINK.SET_MEASURE(net_mem, 302, 111, 114.16);
 
-- SET_NAME
-- Set the name of link 302 to 'My favorite link'.
SDO_NET_MEM.LINK.SET_NAME(net_mem, 302, 'My favorite link');
 
-- SET_STATE
-- Set the state of link 302 to 'INACTIVE'.
SDO_NET_MEM.LINK.SET_STATE(net_mem, 302, 'INACTIVE');
-- GET_STATE
res_string := SDO_NET_MEM.LINK.GET_STATE(net_mem, 302);
DBMS_OUTPUT.PUT_LINE('The state of link 302 is: ' || res_string);
 
-- SET_TYPE
-- Set the type of link 302 to 'Normal street'.
SDO_NET_MEM.LINK.SET_TYPE(net_mem, 302, 'Normal street');
-- GET_TYPE
res_string := SDO_NET_MEM.LINK.GET_TYPE(net_mem, 302);
DBMS_OUTPUT.PUT_LINE('The type of link 302 is: ' || res_string);
 
END;  
/
 
-- Node editing (SDO_NET_MEM.NODE)
 
DECLARE
  net_mem     VARCHAR2(32);
  res_string  VARCHAR2(100);
  res_numeric NUMBER;
  res_geom    SDO_GEOMETRY;
  res_array   SDO_NUMBER_ARRAY;
  indx        NUMBER;
 
BEGIN
 
net_mem := 'ROADS_NETWORK';
  
-- GET_COMPONENT_NO
res_numeric := SDO_NET_MEM.NODE.GET_COMPONENT_NO(net_mem, 3);
DBMS_OUTPUT.PUT_LINE('The component number of node 3 is: ' || res_numeric);
 
-- GET_COST
res_numeric := SDO_NET_MEM.NODE.GET_COST(net_mem, 3);
DBMS_OUTPUT.PUT_LINE('The cost of node 3 is: ' || res_numeric);
 
-- GET_EXTERNAL_NETWORK_ID
res_numeric := SDO_NET_MEM.NODE.GET_EXTERNAL_NETWORK_ID(net_mem, 3);
DBMS_OUTPUT.PUT_LINE('The external network ID of node 3 is: ' || res_numeric);
 
-- GET_EXTERNAL_NETWORK_NAME
res_string := SDO_NET_MEM.NODE.GET_EXTERNAL_NETWORK_NAME(net_mem, 3);
DBMS_OUTPUT.PUT_LINE('The external network name of node 3 is: ' || res_numeric);
 
-- GET_EXTERNAL_NODE_ID
res_numeric := SDO_NET_MEM.NODE.GET_EXTERNAL_NODE_ID(net_mem, 3);
DBMS_OUTPUT.PUT_LINE('The external node ID of node 3 is: ' || res_numeric);
 
-- GET_GEOM_ID
res_numeric := SDO_NET_MEM.NODE.GET_GEOM_ID(net_mem, 3);
DBMS_OUTPUT.PUT_LINE('The geometry ID of node 3 is: ' || res_numeric);
 
-- GET_GEOMETRY
res_geom := SDO_NET_MEM.NODE.GET_GEOMETRY(net_mem, 3);
 
-- GET_IN_LINK_IDS
res_array := SDO_NET_MEM.NODE.GET_IN_LINK_IDS(net_mem, 3);
DBMS_OUTPUT.PUT('Node 3 has the following inbound links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- GET_INCIDENT_LINK_IDS
res_array := SDO_NET_MEM.NODE.GET_INCIDENT_LINK_IDS(net_mem, 3);
DBMS_OUTPUT.PUT('Node 3 has the following incident links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- GET_MEASURE
res_numeric := SDO_NET_MEM.NODE.GET_MEASURE(net_mem, 3);
DBMS_OUTPUT.PUT_LINE('The measure value of node 3 is: ' || res_numeric);
 
-- GET_NAME
res_string := SDO_NET_MEM.NODE.GET_NAME(net_mem, 3);
DBMS_OUTPUT.PUT_LINE('The name of node 3 is: ' || res_string);
 
-- GET_OUT_LINK_IDS
res_array := SDO_NET_MEM.NODE.GET_OUT_LINK_IDS(net_mem, 3);
DBMS_OUTPUT.PUT('Node 3 has the following outbound links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- GET_PARTITION_ID
res_numeric := SDO_NET_MEM.NODE.GET_PARTITION_ID(net_mem, 3);
DBMS_OUTPUT.PUT_LINE('The partition for node 3 is: ' || res_numeric);
 
-- GET_STATE
res_string := SDO_NET_MEM.NODE.GET_STATE(net_mem, 3);
DBMS_OUTPUT.PUT_LINE('The state of node 3 is: ' || res_string);
 
-- IS_ACTIVE
res_string := SDO_NET_MEM.NODE.IS_ACTIVE(net_mem, 3);
DBMS_OUTPUT.PUT_LINE('Is node 3 active?: ' || res_string);
 
-- IS_EXTERNAL_NODE
res_string := SDO_NET_MEM.NODE.IS_EXTERNAL_NODE(net_mem, 3);
DBMS_OUTPUT.PUT_LINE('Is node 3 an external node?: ' || res_string);
 
-- IS_LOGICAL
res_string := SDO_NET_MEM.NODE.IS_LOGICAL(net_mem, 3);
DBMS_OUTPUT.PUT_LINE('Is node 3 a logical node?: ' || res_string);
 
-- IS_TEMPORARY
res_string := SDO_NET_MEM.NODE.IS_TEMPORARY(net_mem, 3);
DBMS_OUTPUT.PUT_LINE('Is node 3 temporary?: ' || res_string);
 
-- LINK_EXISTS
res_string := SDO_NET_MEM.NODE.LINK_EXISTS(net_mem, 3, 4);
DBMS_OUTPUT.PUT_LINE('Does a link exist between nodes 3 and 4?: ' || res_string);
 
-- MAKE_TEMPORARY
-- Make node 7 temporary.
SDO_NET_MEM.NODE.MAKE_TEMPORARY(net_mem, 7);
 
-- SET_COMPONENT_NO
-- Set the component number of node 7 to 987.
SDO_NET_MEM.NODE.SET_COMPONENT_NO(net_mem, 7, 987);
 
-- SET_COST
-- Set the cost of node 7 to 40.
SDO_NET_MEM.NODE.SET_COST(net_mem, 7, 40);
 
-- SET_EXTERNAL_NETWORK_ID
-- Set the external network ID of node 7 to 1000.
SDO_NET_MEM.NODE.SET_EXTERNAL_NETWORK_ID(net_mem, 7, 1000);
 
-- SET_EXTERNAL_NODE_ID
-- Set the external node ID of node 7 to 1014.
SDO_NET_MEM.NODE.SET_EXTERNAL_NODE_ID(net_mem, 7, 1014);
 
-- SET_GEOM_ID
-- Set the geometry ID of node 7 to 99.
SDO_NET_MEM.NODE.SET_GEOM_ID(net_mem, 7, 99);
 
-- SET_MEASURE
-- Set the measure value of node 7 to 30.
SDO_NET_MEM.NODE.SET_MEASURE(net_mem, 7, 30);
 
-- SET_NAME
-- Set the name of node 7 to 'My favorite node'.
SDO_NET_MEM.NODE.SET_NAME(net_mem, 7, 'My favorite node');
-- GET_NAME
res_string := SDO_NET_MEM.NODE.GET_NAME(net_mem, 7);
DBMS_OUTPUT.PUT_LINE('The name of node 7 is: ' || res_string);
 
-- SET_STATE
-- Set the state of node 7 to 'INACTIVE'.
SDO_NET_MEM.NODE.SET_STATE(net_mem, 7, 'INACTIVE');
-- GET_STATE
res_string := SDO_NET_MEM.NODE.GET_STATE(net_mem, 7);
DBMS_OUTPUT.PUT_LINE('The state of node 7 is: ' || res_string);
 
-- SET_TYPE
-- Set the type of node 7 to 'Historic site'.
SDO_NET_MEM.NODE.SET_TYPE(net_mem, 7, 'Historic site');
-- GET_TYPE
res_string := SDO_NET_MEM.NODE.GET_TYPE(net_mem, 7);
DBMS_OUTPUT.PUT_LINE('The type of node 7 is: ' || res_string);
 
END;  
/
 
-- Path editing (SDO_NET_MEM.PATH)
 
DECLARE
  net_mem     VARCHAR2(32);
  res_string  VARCHAR2(100);
  res_numeric NUMBER;
  res_geom    SDO_GEOMETRY;
  path_id     NUMBER;
  res_array   SDO_NUMBER_ARRAY;
  indx        NUMBER;
 
BEGIN
 
net_mem := 'ROADS_NETWORK';
  
-- Create a path for use with subsequent statements. Here, it is
-- the shortest path between nodes 1 (N1) and 5 (N5).
path_id := SDO_NET_MEM.NETWORK_MANAGER.SHORTEST_PATH(net_mem,1,5);
DBMS_OUTPUT.PUT_LINE('The shortest path between nodes 1 and 5 is: ' || path_id);
 
-- GET_LINK_IDS
res_array := SDO_NET_MEM.PATH.GET_LINK_IDS(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Path ' || path_id || ' has the following links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- GET_COST
res_numeric := SDO_NET_MEM.PATH.GET_COST(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('The cost of path ' || path_id || ' is: ' || res_numeric);
 
-- GET_END_NODE_ID
res_numeric := SDO_NET_MEM.PATH.GET_END_NODE_ID(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('The end node ID of path ' || path_id || ' is: ' || res_numeric);
 
-- GET_GEOMETRY
res_geom := SDO_NET_MEM.PATH.GET_GEOMETRY(net_mem, path_id);
-- doesn't work DBMS_OUTPUT.PUT_LINE('The geometry of path ' || path_id || ' is: ' || res_geom);
 
-- GET_LINK_IDS
res_array := SDO_NET_MEM.PATH.GET_LINK_IDS(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Path ' || path_id || ' has the following links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- GET_NAME
res_string := SDO_NET_MEM.PATH.GET_NAME(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('The name of path ' || path_id || ' is: ' || res_string);
 
-- GET_NO_OF_LINKS
res_numeric := SDO_NET_MEM.PATH.GET_NO_OF_LINKS(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('The number of links in path ' || path_id || ' is: ' || res_numeric);
 
-- GET_NODE_IDS
res_array := SDO_NET_MEM.PATH.GET_NODE_IDS(net_mem, path_id);
DBMS_OUTPUT.PUT('Path ' || path_id || ' has the following nodes: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- GET_START_NODE_ID
res_numeric := SDO_NET_MEM.PATH.GET_START_NODE_ID(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('The start node ID of path ' || path_id || ' is: ' || res_numeric);
 
-- IS_ACTIVE
res_string := SDO_NET_MEM.PATH.IS_ACTIVE(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Is path ' || path_id || ' active?: ' || res_string);
 
-- IS_CLOSED
res_string := SDO_NET_MEM.PATH.IS_CLOSED(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Is path ' || path_id || ' closed?: ' || res_string);
 
-- IS_CONNECTED
res_string := SDO_NET_MEM.PATH.IS_CONNECTED(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Is path ' || path_id || ' connected?: ' || res_string);
 
-- IS_LOGICAL
res_string := SDO_NET_MEM.PATH.IS_LOGICAL(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Is path ' || path_id || ' a logical path?: ' || res_string);
 
-- IS_SIMPLE
res_string := SDO_NET_MEM.PATH.IS_SIMPLE(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Is path ' || path_id || ' a simple path?: ' || res_string);
 
-- IS_TEMPORARY
res_string := SDO_NET_MEM.PATH.IS_TEMPORARY(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Is path ' || path_id || ' temporary?: ' || res_string);
 
-- SET_NAME
-- Set the name of path to 'My favorite path'.
SDO_NET_MEM.PATH.SET_NAME(net_mem, path_id, 'My favorite path');
-- GET_NAME
res_string := SDO_NET_MEM.PATH.GET_NAME(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('The name of path ' || path_id || ' is: ' || res_string);
 
-- SET_TYPE
-- Set the type of the path to 'Scenic'.
SDO_NET_MEM.PATH.SET_TYPE(net_mem, path_id, 'Scenic');
-- GET_TYPE
res_string := SDO_NET_MEM.PATH.GET_TYPE(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('The type of path ' || path_id || ' is: ' || res_string);
 
-- SET_PATH_ID
-- Set (change) the path ID of the path to 6789.
SDO_NET_MEM.PATH.SET_PATH_ID(net_mem, path_id, 6789);
 
END;  
/

5.11.4 Logical Hierarchical Network Example

This section presents an example of a logical network that contains the nodes and links illustrated in Figure 5-7. Because it is a logical network, there are no spatial geometries associated with it. (Figure 5-7 is essentially the same as Figure 5-2 in Section 5.5, but with the nodes and links labeled.)

Figure 5-7 Nodes and Links for Logical Network Example

Description of Figure 5-7 follows
Description of "Figure 5-7 Nodes and Links for Logical Network Example"

As shown in Figure 5-7:

  • 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 5-5 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.

  • Uses SDO_NET_MEM functions and procedures for analysis and editing.

Example 5-5 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 paths and links 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.
-- 6. Use SDO_NET_MEM functions and procedures for analysis and editing.
 
-- 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, 103, '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, 108, '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);
 
-- N7N8
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active, 
     link_level, cost)
  VALUES (1110, 'N7N8', 107, 108, 'Y', 1, 10);
 
-- N7N9
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active, 
     link_level, cost)
  VALUES (1111, 'N7N9', 107, 109, 'Y', 1, 20);
 
-- N8N9
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active, 
     link_level, cost)
  VALUES (1112, 'N8N9', 108, 109, 'Y', 1, 30);
 
-- N9N10
INSERT INTO xyz_links (link_id, link_name, start_node_id, end_node_id, active, 
     link_level, cost)
  VALUES (1113, '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 (1114, '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 (1115, '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 (1116, '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 (1117, '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 (1118, '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 (1119, '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 
    (NETWORK,
     NETWORK_CATEGORY,
     NO_OF_HIERARCHY_LEVELS,
     NO_OF_PARTITIONS,
     NODE_TABLE_NAME,
     LINK_TABLE_NAME,
     LINK_DIRECTION,
     LINK_COST_COLUMN,
     PATH_TABLE_NAME,
     PATH_LINK_TABLE_NAME)
  VALUES (
    'XYZ_NETWORK',  -- Network name
    'LOGICAL',   -- Network category
    2,  -- No. of levels in hierarchy
    1,  -- No. of partitions
    'XYZ_NODES',  -- Node table name
    'XYZ_LINKS',  -- Link table name
    'BIDIRECTED',  -- Link direction
    'COST',  -- Link cost column
    'XYZ_PATHS',  -- Path table name
    'XYZ_PATHS_LINKS'  -- Path-link table name
  );
 
-- 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_NO_OF_PARTITIONS('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.NETWORK_EXISTS('XYZ_NETWORK') FROM DUAL;
 
-- Copy a network.
EXECUTE SDO_NET.COPY_NETWORK('XYZ_NETWORK', 'XYZ_NETWORK2');
 
-- Create a trigger.
EXECUTE SDO_NET.CREATE_DELETE_TRIGGER('XYZ_NETWORK');
 
-- 6. Use SDO_NET_MEM functions and procedures for analysis and editing.
 
-- Network analysis and other operations (SDO_NET_MEM.NETWORK_MANAGER)
 
DECLARE
  net_mem    VARCHAR2(100);
  res_string VARCHAR2(1000);
 
  cost        NUMBER;
  res_numeric NUMBER;
  res_array   SDO_NUMBER_ARRAY;
  indx        NUMBER;
 
  indx1        NUMBER;
  var1_numeric NUMBER;
  var1_array   SDO_NUMBER_ARRAY;
 
BEGIN
 
net_mem := 'XYZ_NETWORK';
  
-- Read in the network.
SDO_NET_MEM.NETWORK_MANAGER.READ_NETWORK(net_mem, 'TRUE');
 
-- Validate the network.
res_string := SDO_NET_MEM.NETWORK_MANAGER.VALIDATE_NETWORK_SCHEMA(net_mem);
DBMS_OUTPUT.PUT_LINE('Is network ' || net_mem || ' valid? ' || res_string);
  
res_string := SDO_NET_MEM.NETWORK_MANAGER.LIST_NETWORKS;
DBMS_OUTPUT.PUT_LINE('The current in-memory network(s) is/are: ' || res_string);
 
res_numeric := SDO_NET_MEM.NETWORK_MANAGER.FIND_CONNECTED_COMPONENTS(net_mem);
DBMS_OUTPUT.PUT_LINE('The number of connected components is: ' || res_numeric);
 
res_array := SDO_NET_MEM.NETWORK_MANAGER.MCST_LINK(net_mem);
DBMS_OUTPUT.PUT('Network ' || net_mem || ' has the following MCST links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
res_array := SDO_NET_MEM.NETWORK_MANAGER.FIND_REACHABLE_NODES(net_mem,101);
DBMS_OUTPUT.PUT_LINE('Reachable nodes from 101: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  res_numeric := res_array(indx);
  DBMS_OUTPUT.PUT(res_numeric || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
res_array := SDO_NET_MEM.NETWORK_MANAGER.FIND_REACHING_NODES(net_mem,101);
DBMS_OUTPUT.PUT_LINE('Nodes from which 101 can be reached: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  res_numeric := res_array(indx);
  DBMS_OUTPUT.PUT(res_numeric || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
  
res_array := SDO_NET_MEM.NETWORK_MANAGER.NEAREST_NEIGHBORS(net_mem,101,3);
DBMS_OUTPUT.PUT_LINE('Path IDs to the nearest 3 neighbors of node 101 are: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  res_numeric := res_array(indx);
  DBMS_OUTPUT.PUT(res_numeric || ', which contains links: ');
  var1_array := SDO_NET_MEM.PATH.GET_LINK_IDS(net_mem, res_numeric);
    FOR indx1 IN var1_array.FIRST..var1_array.LAST
    LOOP
      var1_numeric := var1_array(indx1);
      DBMS_OUTPUT.PUT(var1_numeric || ' ');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' '); 
 
res_array := SDO_NET_MEM.NETWORK_MANAGER.NEAREST_NEIGHBORS(net_mem,101,3);
DBMS_OUTPUT.PUT_LINE('Path IDs to the nearest 3 neighbors of node 101 are: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  res_numeric := res_array(indx);
  DBMS_OUTPUT.PUT(res_numeric || ', whose end node is: ');
  var1_numeric := SDO_NET_MEM.PATH.GET_END_NODE_ID(net_mem, res_numeric);
  DBMS_OUTPUT.PUT(var1_numeric);
  DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' '); 
  
res_string := SDO_NET_MEM.NETWORK_MANAGER.IS_REACHABLE(net_mem,101,105);
DBMS_OUTPUT.PUT_LINE('Can node 101 reach node 105? ' || res_string);
  
res_array := SDO_NET_MEM.NETWORK_MANAGER.ALL_PATHS(net_mem,101,105,10,200,5);
DBMS_OUTPUT.PUT_LINE('For each path from node 101 to node 105: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  res_numeric := res_array(indx);
  DBMS_OUTPUT.PUT_LINE('Path ' || res_numeric ||
                       ' has the following properties: ');
  cost := SDO_NET_MEM.PATH.GET_COST(net_mem, res_numeric);
  DBMS_OUTPUT.PUT_LINE('Path ' || res_numeric || ' cost: ' || cost);
  res_string := SDO_NET_MEM.PATH.IS_CLOSED(net_mem, res_array(indx));  
  DBMS_OUTPUT.PUT_LINE('Is path ' || res_numeric || ' closed? ' || res_string);       
END LOOP;
   
DBMS_OUTPUT.PUT_LINE(' ');
res_numeric := SDO_NET_MEM.NETWORK_MANAGER.SHORTEST_PATH(net_mem,101,105);
DBMS_OUTPUT.PUT_LINE('The shortest path from node 101 to node 105 is path ID: ' || res_numeric);
 
DBMS_OUTPUT.PUT_LINE('The following are characteristics of this shortest path: ');
cost := SDO_NET_MEM.PATH.GET_COST(net_mem, res_numeric);
DBMS_OUTPUT.PUT_LINE('Path ' || res_numeric || ' has cost: ' || cost);
res_string := SDO_NET_MEM.PATH.IS_CLOSED(net_mem, res_numeric);  
DBMS_OUTPUT.PUT_LINE('Is path ' || res_numeric || ' closed? ' || res_string);  
 
res_array := SDO_NET_MEM.PATH.GET_LINK_IDS(net_mem, res_numeric);   
DBMS_OUTPUT.PUT('Path ' || res_numeric || ' has links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
DBMS_OUTPUT.PUT_LINE(' ');
res_numeric := SDO_NET_MEM.NETWORK_MANAGER.SHORTEST_PATH_DIJKSTRA(net_mem,101,105);
DBMS_OUTPUT.PUT_LINE('The shortest Dijkstra path from node 101 to node 105 is ' || res_numeric);
 
DBMS_OUTPUT.PUT_LINE('The following are characteristics of this shortest path: ');
cost := SDO_NET_MEM.PATH.GET_COST(net_mem, res_numeric);
DBMS_OUTPUT.PUT_LINE('Path ' || res_numeric || ' cost: ' || cost);
res_string := SDO_NET_MEM.PATH.IS_CLOSED(net_mem, res_numeric);  
DBMS_OUTPUT.PUT_LINE('Is path ' || res_numeric || ' closed? ' || res_string);  
 
res_array := SDO_NET_MEM.PATH.GET_LINK_IDS(net_mem, res_numeric);   
DBMS_OUTPUT.PUT('Path ' || res_numeric || ' has links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
  
res_array := SDO_NET_MEM.PATH.GET_NODE_IDS(net_mem, res_numeric);   
DBMS_OUTPUT.PUT('Path ' || res_numeric || ' has nodes: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
res_array := SDO_NET_MEM.NETWORK_MANAGER.WITHIN_COST(net_mem,102,100);
DBMS_OUTPUT.PUT('Shortest path IDs to nodes within cost of 100 from node 102: ');
DBMS_OUTPUT.PUT_LINE(' ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  res_numeric := res_array(indx);
  DBMS_OUTPUT.PUT(res_numeric || ', whose end node is: ');
  var1_numeric := SDO_NET_MEM.PATH.GET_END_NODE_ID(net_mem, res_numeric);
  DBMS_OUTPUT.PUT(var1_numeric);
  DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' '); 
 
END;  
/
 
-- Link editing (SDO_NET_MEM.LINK)
 
DECLARE
  net_mem     VARCHAR2(32);
  res_string  VARCHAR2(100);
  res_numeric NUMBER;
  res_array   SDO_NUMBER_ARRAY;
  indx        NUMBER;
 
BEGIN
 
net_mem := 'XYZ_NETWORK';
  
-- Read in the network.
-- SDO_NET_MEM.NETWORK_MANAGER.READ_NETWORK(net_mem, 'TRUE');
 
-- GET_CHILD_LINKS
res_array := SDO_NET_MEM.LINK.GET_CHILD_LINKS(net_mem, 1001);
DBMS_OUTPUT.PUT('Link 1001 has the following child links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- GET_COST
res_numeric := SDO_NET_MEM.LINK.GET_COST(net_mem, 1104);
DBMS_OUTPUT.PUT_LINE('The cost of link 1104 is: ' || res_numeric);
 
-- GET_END_NODE_ID
res_numeric := SDO_NET_MEM.LINK.GET_END_NODE_ID(net_mem, 1104);
DBMS_OUTPUT.PUT_LINE('The end node of link 1104 is: ' || res_numeric);
 
-- GET_LEVEL
res_numeric := SDO_NET_MEM.LINK.GET_LEVEL(net_mem, 1001);
DBMS_OUTPUT.PUT_LINE('The hierarchy level of link 1001 is: ' || res_numeric);
 
-- GET_NAME
res_string := SDO_NET_MEM.LINK.GET_NAME(net_mem, 1104);
DBMS_OUTPUT.PUT_LINE('The name of link 1104 is: ' || res_string);
 
-- GET_PARENT_LINK_ID
res_numeric := SDO_NET_MEM.LINK.GET_PARENT_LINK_ID(net_mem, 1108);
DBMS_OUTPUT.PUT_LINE('The parent link of link 1108 is: ' || res_numeric);
 
-- GET_SIBLING_LINK_IDS
res_array := SDO_NET_MEM.LINK.GET_SIBLING_LINK_IDS(net_mem, 1108);
DBMS_OUTPUT.PUT('Link 1108 has the following sibling links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');

-- GET_START_NODE_ID
res_numeric := SDO_NET_MEM.LINK.GET_START_NODE_ID(net_mem, 1104);
DBMS_OUTPUT.PUT_LINE('The start node of link 1104 is: ' || res_numeric);
 
-- GET_STATE
res_string := SDO_NET_MEM.LINK.GET_STATE(net_mem, 1104);
DBMS_OUTPUT.PUT_LINE('The state of link 1104 is: ' || res_string);
 
-- IS_ACTIVE
res_string := SDO_NET_MEM.LINK.IS_ACTIVE(net_mem, 1104);
DBMS_OUTPUT.PUT_LINE('Is link 1104 active?: ' || res_string);
 
-- IS_EXTERNAL_LINK
res_string := SDO_NET_MEM.LINK.IS_EXTERNAL_LINK(net_mem, 1104);
DBMS_OUTPUT.PUT_LINE('Is link 1104 an external link?: ' || res_string);
 
-- IS_LOGICAL
res_string := SDO_NET_MEM.LINK.IS_LOGICAL(net_mem, 1104);
DBMS_OUTPUT.PUT_LINE('Is link 1104 a logical link?: ' || res_string);
 
-- IS_TEMPORARY
res_string := SDO_NET_MEM.LINK.IS_TEMPORARY(net_mem, 1104);
DBMS_OUTPUT.PUT_LINE('Is link 1104 temporary?: ' || res_string);
 
-- SET_COST
-- Set the cost of link 1119 to 40.
SDO_NET_MEM.LINK.SET_COST(net_mem, 1119, 40);
 
-- SET_END_NODE
-- Set the end node of link 1119 to 109 (N9).
SDO_NET_MEM.LINK.SET_END_NODE(net_mem, 1119, 109);
 
-- SET_LEVEL
-- Set the hierarchy level of link 1119 to 2.
SDO_NET_MEM.LINK.SET_LEVEL(net_mem, 1119, 2);
 
-- SET_NAME
-- Set the name of link 1119 to 'My favorite link'.
SDO_NET_MEM.LINK.SET_NAME(net_mem, 1119, 'My favorite link');
 
-- SET_PARENT_LINK
-- Make link 1001 the parent of link 1119.
SDO_NET_MEM.LINK.SET_PARENT_LINK(net_mem, 1119, 1001);
 
-- SET_START_NODE
-- Set the start node of link 1119 to 110 (N10).
SDO_NET_MEM.LINK.SET_START_NODE(net_mem, 1119, 110);
 
-- SET_STATE
-- Set the state of link 1119 to 'INACTIVE'.
SDO_NET_MEM.LINK.SET_STATE(net_mem, 1119, 'INACTIVE');
-- GET_STATE
res_string := SDO_NET_MEM.LINK.GET_STATE(net_mem, 1119);
DBMS_OUTPUT.PUT_LINE('The state of link 1119 is: ' || res_string);
 
-- SET_TYPE
-- Set the type of link 1119 to 'Associative'.
SDO_NET_MEM.LINK.SET_TYPE(net_mem, 1119, 'Associative');
-- GET_TYPE
res_string := SDO_NET_MEM.LINK.GET_TYPE(net_mem, 1119);
DBMS_OUTPUT.PUT_LINE('The type of link 1119 is: ' || res_string);
 
END;  
/
 
-- Node editing (SDO_NET_MEM.NODE)
 
DECLARE
  net_mem     VARCHAR2(32);
  res_string  VARCHAR2(100);
  res_numeric NUMBER;
  res_array   SDO_NUMBER_ARRAY;
  indx        NUMBER;
 
BEGIN
 
net_mem := 'XYZ_NETWORK';
  
-- GET_ADJACENT_NODE_IDS
res_array := SDO_NET_MEM.NODE.GET_ADJACENT_NODE_IDS(net_mem, 103);
DBMS_OUTPUT.PUT('Node 103 has the following adjacent nodes: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');

-- GET_CHILD_NODE_IDS
res_array := SDO_NET_MEM.NODE.GET_CHILD_NODE_IDS(net_mem, 1);
DBMS_OUTPUT.PUT('Node 1 has the following child nodes: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- GET_COMPONENT_NO
res_numeric := SDO_NET_MEM.NODE.GET_COMPONENT_NO(net_mem, 103);
DBMS_OUTPUT.PUT_LINE('The component number of node 103 is: ' || res_numeric);
 
-- GET_COST
res_numeric := SDO_NET_MEM.NODE.GET_COST(net_mem, 103);
DBMS_OUTPUT.PUT_LINE('The cost of node 103 is: ' || res_numeric);
 
-- GET_EXTERNAL_NETWORK_ID
res_numeric := SDO_NET_MEM.NODE.GET_EXTERNAL_NETWORK_ID(net_mem, 103);
DBMS_OUTPUT.PUT_LINE('The external network ID for node 103 is: ' || res_numeric);
 
-- GET_EXTERNAL_NETWORK_NAME
res_string := SDO_NET_MEM.NODE.GET_EXTERNAL_NETWORK_NAME(net_mem, 103);
DBMS_OUTPUT.PUT_LINE('The external network name for node 103 is: ' || res_numeric);
 
-- GET_EXTERNAL_NODE_ID
res_numeric := SDO_NET_MEM.NODE.GET_EXTERNAL_NODE_ID(net_mem, 103);
DBMS_OUTPUT.PUT_LINE('The external node ID of node 103 is: ' || res_numeric);
 
-- GET_HIERARCHY_LEVEL
res_numeric := SDO_NET_MEM.NODE.GET_HIERARCHY_LEVEL(net_mem, 1);
DBMS_OUTPUT.PUT_LINE('The hierarchy level of node 1 is: ' || res_numeric);
 
-- GET_IN_LINK_IDS
res_array := SDO_NET_MEM.NODE.GET_IN_LINK_IDS(net_mem, 103);
DBMS_OUTPUT.PUT('Node 103 has the following inbound links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- GET_INCIDENT_LINK_IDS
res_array := SDO_NET_MEM.NODE.GET_INCIDENT_LINK_IDS(net_mem, 103);
DBMS_OUTPUT.PUT('Node 103 has the following incident links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- GET_NAME
res_string := SDO_NET_MEM.NODE.GET_NAME(net_mem, 103);
DBMS_OUTPUT.PUT_LINE('The name of node 103 is: ' || res_string);
 
-- GET_OUT_LINK_IDS
res_array := SDO_NET_MEM.NODE.GET_OUT_LINK_IDS(net_mem, 103);
DBMS_OUTPUT.PUT('Node 103 has the following outbound links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- GET_PARENT_NODE_ID
res_numeric := SDO_NET_MEM.NODE.GET_PARENT_NODE_ID(net_mem, 103);
DBMS_OUTPUT.PUT_LINE('The parent node of node 103 is: ' || res_numeric);
 
-- GET_PARTITION_ID
res_numeric := SDO_NET_MEM.NODE.GET_PARTITION_ID(net_mem, 103);
DBMS_OUTPUT.PUT_LINE('The partition for node 103 is: ' || res_numeric);
 
-- GET_SIBLING_NODE_IDS
res_array := SDO_NET_MEM.NODE.GET_SIBLING_NODE_IDS(net_mem, 103);
DBMS_OUTPUT.PUT('Node 103 has the following sibling nodes: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');

-- GET_STATE
res_string := SDO_NET_MEM.NODE.GET_STATE(net_mem, 103);
DBMS_OUTPUT.PUT_LINE('The state of node 103 is: ' || res_string);
 
-- IS_ACTIVE
res_string := SDO_NET_MEM.NODE.IS_ACTIVE(net_mem, 103);
DBMS_OUTPUT.PUT_LINE('Is node 103 active?: ' || res_string);
 
-- IS_EXTERNAL_NODE
res_string := SDO_NET_MEM.NODE.IS_EXTERNAL_NODE(net_mem, 103);
DBMS_OUTPUT.PUT_LINE('Is node 103 an external node?: ' || res_string);
 
-- IS_LOGICAL
res_string := SDO_NET_MEM.NODE.IS_LOGICAL(net_mem, 103);
DBMS_OUTPUT.PUT_LINE('Is node 103 a logical node?: ' || res_string);
 
-- IS_TEMPORARY
res_string := SDO_NET_MEM.NODE.IS_TEMPORARY(net_mem, 103);
DBMS_OUTPUT.PUT_LINE('Is node 103 temporary?: ' || res_string);
 
-- LINK_EXISTS
res_string := SDO_NET_MEM.NODE.LINK_EXISTS(net_mem, 103, 104);
DBMS_OUTPUT.PUT_LINE('Does a link exist between nodes 103 and 104?: ' || res_string);
 
-- MAKE_TEMPORARY
-- Make node 114 temporary.
SDO_NET_MEM.NODE.MAKE_TEMPORARY(net_mem, 114);
 
-- SET_COMPONENT_NO
-- Set the component number of node 114 to 987.
SDO_NET_MEM.NODE.SET_COMPONENT_NO(net_mem, 114, 987);
 
-- SET_COST
-- Set the cost of node 114 to 40.
SDO_NET_MEM.NODE.SET_COST(net_mem, 114, 40);
 
-- SET_EXTERNAL_NETWORK_ID
-- Set the external network ID of node 114 to 1000.
SDO_NET_MEM.NODE.SET_EXTERNAL_NETWORK_ID(net_mem, 114, 1000);
 
-- SET_EXTERNAL_NODE_ID
-- Set the external node ID of node 114 to 1014.
SDO_NET_MEM.NODE.SET_EXTERNAL_NODE_ID(net_mem, 114, 1014);
 
-- SET_HIERARCHY_LEVEL
-- Set the hierarchy level of node 1 to 2.
SDO_NET_MEM.NODE.SET_HIERARCHY_LEVEL(net_mem, 1, 2);
 
-- SET_NAME
-- Set the name of node 114 to 'My favorite node'.
SDO_NET_MEM.NODE.SET_NAME(net_mem, 114, 'My favorite node');
-- GET_NAME
res_string := SDO_NET_MEM.NODE.GET_NAME(net_mem, 114);
DBMS_OUTPUT.PUT_LINE('The name of node 114 is: ' || res_string);
 
-- SET_PARENT_NODE
-- Make node 1 the parent of node 114.
SDO_NET_MEM.NODE.SET_PARENT_NODE(net_mem, 114, 1);
 
-- SET_STATE
-- Set the state of node 111 to 'INACTIVE'.
SDO_NET_MEM.NODE.SET_STATE(net_mem, 111, 'INACTIVE');
-- GET_STATE
res_string := SDO_NET_MEM.NODE.GET_STATE(net_mem, 111);
DBMS_OUTPUT.PUT_LINE('The state of node 111 is: ' || res_string);
 
-- SET_TYPE
-- Set the type of node 114 to 'Research'.
SDO_NET_MEM.NODE.SET_TYPE(net_mem, 114, 'Research');
-- GET_TYPE
res_string := SDO_NET_MEM.NODE.GET_TYPE(net_mem, 114);
DBMS_OUTPUT.PUT_LINE('The type of node 114 is: ' || res_string);
 
END;  
/
 
-- Path editing (SDO_NET_MEM.PATH)
 
DECLARE
  net_mem     VARCHAR2(32);
  res_string  VARCHAR2(100);
  res_numeric NUMBER;
  path_id     NUMBER;
  res_array   SDO_NUMBER_ARRAY;
  indx        NUMBER;
 
BEGIN
 
net_mem := 'XYZ_NETWORK';
  
-- Create a path for use with subsequent statements. Here, it is
-- the shortest path between nodes 101 (N1) and 105 (N5).
path_id := SDO_NET_MEM.NETWORK_MANAGER.SHORTEST_PATH(net_mem,101,105);
DBMS_OUTPUT.PUT_LINE('The shortest path between nodes 101 and 105 is: ' || path_id);
 
-- GET_LINK_IDS
res_array := SDO_NET_MEM.PATH.GET_LINK_IDS(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Path ' || path_id || ' has the following links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- GET_COST
res_numeric := SDO_NET_MEM.PATH.GET_COST(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('The cost of path ' || path_id || ' is: ' || res_numeric);
 
-- GET_END_NODE_ID
res_numeric := SDO_NET_MEM.PATH.GET_END_NODE_ID(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('The end node ID of path ' || path_id || ' is: ' || res_numeric);
 
-- GET_LINK_IDS
res_array := SDO_NET_MEM.PATH.GET_LINK_IDS(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Path ' || path_id || ' has the following links: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- GET_NO_OF_LINKS
res_numeric := SDO_NET_MEM.PATH.GET_NO_OF_LINKS(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('The number of links in path ' || path_id || ' is: ' || res_numeric);
 
-- GET_NODE_IDS
res_array := SDO_NET_MEM.PATH.GET_NODE_IDS(net_mem, path_id);
DBMS_OUTPUT.PUT('Path ' || path_id || ' has the following nodes: ');
FOR indx IN res_array.FIRST..res_array.LAST
LOOP
  DBMS_OUTPUT.PUT(res_array(indx) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
 
-- GET_START_NODE_ID
res_numeric := SDO_NET_MEM.PATH.GET_START_NODE_ID(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('The start node ID of path ' || path_id || ' is: ' || res_numeric);
 
-- IS_ACTIVE
res_string := SDO_NET_MEM.PATH.IS_ACTIVE(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Is path ' || path_id || ' active?: ' || res_string);
 
-- IS_CLOSED
res_string := SDO_NET_MEM.PATH.IS_CLOSED(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Is path ' || path_id || ' closed?: ' || res_string);
 
-- IS_CONNECTED
res_string := SDO_NET_MEM.PATH.IS_CONNECTED(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Is path ' || path_id || ' connected?: ' || res_string);
 
-- IS_LOGICAL
res_string := SDO_NET_MEM.PATH.IS_LOGICAL(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Is path ' || path_id || ' a logical path?: ' || res_string);
 
-- IS_SIMPLE
res_string := SDO_NET_MEM.PATH.IS_SIMPLE(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Is path ' || path_id || ' a simple path?: ' || res_string);
 
-- IS_TEMPORARY
res_string := SDO_NET_MEM.PATH.IS_TEMPORARY(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('Is path ' || path_id || ' temporary?: ' || res_string);
 
-- SET_NAME
-- Set the name of path to 'My favorite path'.
SDO_NET_MEM.PATH.SET_NAME(net_mem, path_id, 'My favorite path');
-- GET_NAME
res_string := SDO_NET_MEM.PATH.GET_NAME(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('The name of path ' || path_id || ' is: ' || res_string);
 
-- SET_TYPE
-- Set the type of the path to 'Logical connections'.
SDO_NET_MEM.PATH.SET_TYPE(net_mem, path_id, 'Logical connections');
-- GET_TYPE
res_string := SDO_NET_MEM.PATH.GET_TYPE(net_mem, path_id);
DBMS_OUTPUT.PUT_LINE('The type of path ' || path_id || ' is: ' || res_string);
 
-- SET_PATH_ID
-- Set (change) the path ID of the path to 6789.
SDO_NET_MEM.PATH.SET_PATH_ID(net_mem, path_id, 6789);
 
END;  
/

5.12 Network Editor and Other Demo Files

Oracle Spatial provides several network data model demo files that you can use to reinforce your learning and to create models for coding certain operations. Some of these files are for the Network Editor, an unsupported tool that enables you to visualize and edit network data. (The Network Editor interface is shown in Figure 5-1 in Section 5.1.) If you installed the demo files from the Companion CD, those related to the network data model are under the following directory:

$ORACLE_HOME/md/demos/network

The files for the Network Editor are in the following directory:

$ORACLE_HOME/md/demos/network/editor

Read the README.txt file in that directory for information about this tool. Sample data for the network data model is under the following directory:

$ORACLE_HOME/md/demos/network/editor_download_kit

For example, New York City sample network data is in the following directory:

$ORACLE_HOME/md/demos/network/editor_download_kit/nyc_sample_data

Read the data_load_instructions.txt file in that directory for information about loading the New York City sample network data into the database.

5.13 README File for Spatial and Related Features

A README.txt file supplements the information in the following manuals: Oracle Spatial User's Guide and Reference, Oracle Spatial GeoRaster, and Oracle Spatial Topology and Network Data Models (this manual). This file is located at:

$ORACLE_HOME/md/doc/README.txt