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:

   • SDO_NET.CREATE_SDO_NETWORK for a spatial network with non-LRS SDO_GEOMETRY objects

   • SDO_NET.CREATE_LRS_NETWORK for a spatial network with LRS SDO_GEOMETRY objects

   • SDO_NET.CREATE_TOPO_NETWORK for a spatial network with topology geometry (SDO_TOPO_GEOMETRY) objects

   • SDO_NET.CREATE_LOGICAL_NETWORK for a logical network that does not contain spatial information

   Each of these procedures creates the necessary Network Data Model tables (described in Network Data Model Tables) and inserts a row with the appropriate network metadata information into the xxx_SDO_NETWORK_METADATA views (described in xxx_SDO_NETWORK_METADATA Views).

   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 Network Data Model Tables.)
3. Validate the network, using the SDO_NET.VALIDATE_NETWORK function.
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.

1. Create the node table, using the SDO_NET.CREATE_NODE_TABLE procedure.
2. Insert data into the node table.
3. Create the link table, using the SDO_NET.CREATE_LINK_TABLE procedure.
4. Insert data into the link table.
5. Optionally, create the path table, using the SDO_NET.CREATE_PATH_TABLE procedure.
6. If you created the path table, create the path-link table, using the SDO_NET.CREATE_PATH_LINK_TABLE procedure.
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 xxx_SDO_NETWORK_METADATA Views.)

   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.

A subpath is a partial path along a path, created either as a result of a network analysis operation or explicitly by a user. The start and end points of a subpath are defined as link indexes and the percentage of the distance from the previous node in the path, as shown in Figure 5-2.

Figure 5-2 Path and Subpaths

Description of "Figure 5-2 Path and Subpaths"

A subpath refers to an existing path (the reference path) using the following parameters:

• Reference path ID: the path ID of the reference path.

• Start link index: the start link index on the reference path. (Link index 0 refers to the link between the first and second nodes on the path.) In Figure 5-2, link index 0 is the start link index.

• Start percentage: the percentage of the distance along the start link for the start node of the subpath. In Figure 5-2, the subpath starts at 65 percent of the distance between the start and end of link index 0.

• End link index: the end link index on the reference path. In Figure 5-2, link index 6 is the end link index.

• End percentage: the percentage of the distance along the end link for the end node of the subpath. In Figure 5-2, the subpath ends at 50 percent of the distance between the start and end of link index 6.

A feature is an object of interest in a network application that is associated with a node or link. For example, in a transportation network, features include exits and intersections (mapped to nodes), and highways and streets (mapped to links).

A feature consists of one or more feature elements. A feature element is a point or line along the network. If it is a point, it can lie on a node or along a line; if it is a line, it can be a full link or a partial link.

Depending on the types of feature elements in the feature, a feature can have any of the feature types shown in Table 5-1.

A feature layer corresponds to a table containing features that have the same set of attributes. For example, in a roads network, there may be separate feature layers for restaurants and hotels (and perhaps other feature layers for other kinds of things of interest to travelers).

Depending on the types of features in the feature layer, a feature layer can have any of the feature layer types shown in Table 5-2, which maps each feature layer type to the associated feature type or types from Table 5-1.

A parent feature consists of features from one or more feature layers. For example, in an electrical network, substation is a parent feature for the feature layers for all its associated parts, such as joints, switches, and cables.

Features and feature layers can be edited and displayed using the NFE API. This API makes it easier to model certain types of networks, such as electrical, gas, and water utilities.

In a typical road network, the intersections of roads are nodes and the road segments between two intersections are links. The spatial representation of a road is not inherently related to the nodes and links in the network. For example, a shape point in the spatial representation of a road (reflecting a sharp turn in the road) is not a node in the network if that shape point is not associated with an intersection; and a single spatial object may make up several links in a network (such as a straight segment intersected by three crossing roads). An important operation with a road network is to find the path from a start point to an end point, minimizing either the travel time or distance. There may be additional constraints on the path computation, such as having the path go through a particular landmark or avoid a particular intersection.

The subway network of any major city is probably best modeled as a logical network, assuming that precise spatial representation of the stops and track lines is unimportant. In such a network, all stops on the system constitute the nodes of the network, and a link is the connection between two stops if a train travels directly between these two stops. Important operations with a train network include finding all stations that can be reached from a specified station, finding the number of stops between two specified stations, and finding the travel time between two stations.

Multimodal networks are networks that consist of multiple modes, such as a network consisting of driving and walking routes. They are usually modeled as individual networks (of the specific mode) and are treated as an aggregate network so that routes of single mode as well as multiple modes can be represented and computed. In general, multimodal networks are "connected" by schedules of different modes, and in such cases they are also temporal networks. An example is to compute an itinerary with walking to nearest bus stop, taking the fastest bus route, getting off at the stop that is closest to the destination, then walking to your destination. You could also add modes like driving or flight to be taken into consideration.

Temporal modeling and analysis adds a temporal (time) dimension to network modeling and analysis. The time factor provides cost and/or constraints on top of static (non-temporal) networks. An example is to consider traffic patterns (time-dependent travel time costs) instead of static travel-time costs.

Many metropolitan transportation networks consist of multiple modes such as buses, subways, and commuter rail lines, where transfers across modes are possible (for example, from a bus to the subway). Each transportation mode has a component network within the larger transportation network. The component networks can be modeled using nodes and links, and the transfers across modes can be modeled as links that connect the stops where transfers are possible.

An important feature of such multimodal transportation networks is their schedule-based operation. When performing common network operations such as computing the fastest route from a start point to an end point, the schedule information and possible transfers across modes must be considered. The schedule information at stops can be represented as user-defined data at the nodes representing these stops. Examples of operations that use schedule information in a multimodal network are (A) finding the fastest route (minimum travel time) from a start point to an end point for a specified start time, and (B) finding the latest departure time at a start point to reach an end point by a specified arrival time.

Utility networks, such as power line or cable networks, must often be configured to minimize the cost. An important operation with a utility network is to determine the connections among nodes, using minimum cost spanning tree algorithms, to provide the required quality of service at the minimum cost. Another important operation is reachability analysis, so that, for example, if a station in a water network is shut down, you know which areas will be affected.

Biochemical processes can be modeled as biochemical networks to represent reactions and regulations in living organisms. For example, metabolic pathways are networks involved in enzymatic reactions, while regulatory pathways represent protein-protein interactions. In this example, a pathway is a network; genes, proteins, and chemical compounds are nodes; and reactions among nodes are links. Important operations for a biochemical network include computing paths and the degrees of nodes.

This section presents an example of using network user-defined data, which is the information (not related to connectivity) that users want to associate with a network representation. The USER_SDO_NETWORK_USER_DATA and ALL_SDO_ NETWORK_USER_DATA metadata views (described in xxx_SDO_NETWORK_USER_DATA Views) contain information about user-defined data.

To use user-defined data, you must set the USER_DEFINED_DATA column value to Y in the appropriate xxx_SDO_NETWORK_METADATA views (described in Section xxx_SDO_NETWORK_METADATA Views).

Example 5-1 inserts link-related user-defined data into the network metadata.

-- Insert link user data named 'interaction' of
-- type varchar2 (50) in network 'bi_test'.
--'interaction' is a column of type varchar2(50) in the link table of network 'bi_
test'.
insert into user_sdo_network_user_data (network, table_type, data_name, data_type, data_length, category_id) 
                  values ('bi_test', 'LINK', 'interaction', 'VARCHAR2', 50, 0) ;
-- insert link user data named 'PROB' of type Number.
--'PROB' is a column of type NUMBER in the link table of network 'bi_test'.
insert into user_sdo_network_user_data (network, table_type, data_name, data_type, category_id)
                  values ('bi_test','LINK','PROB','NUMBER', 0) ;
 
After a network or network partition is loaded, user-defined data is available in Java representations. You can access user-defined data through the getCategorizedUserData and setCategorizedUserData methods for the Node, Link, Path, and SubPath interfaces. For example:
 
//The user data index is the sequence number of a user data within a category sorted by data name.
 
int interactionUserDataIndex = 0;
int probUserDataIndex = 1;
 
String interaction = (String)link.getCategorizedUserData().getUserData(0).
                                get(interactionUseDataIndex);
 
double prob = ((Double)link.getCategorizedUserData().getUserData(0).
                       get(probUserdataIndex)).doubleValue();

This section presents an example of a custom user data I/O implementation (nondefault implementation) of the LODUserDataIO interface. In Example 5-2, user data associated with links is written to BLOBs (one BLOB per partition) and read from BLOBs during analysis. It is assumed that the user-defined data BLOB for multimodal data for each partition has the partition ID and number of links associated with the partition, followed by <Link ID, link route ID> for each link.

//Method getLinksInPartition(partitionId) computes a vector that
// consists of  the ID and the route ID of each link associated with a partition
// with ID = partitionId
LinkVector = getLinksInPartition(partitionId);

ObjectOutputStream dout = null;

//Insert an empty blob for the partition with ID = partition_id
String insertStr = "INSERT INTO " + MULTIMODAL_USER_DATA +
                             " (partition_id, blob) " + " VALUES " + " (?, EMPTY_LOB())" ;

PreparedStatement stmt = conn.prepareStatement(insertStr);
stmt.setInt(1,partitionId);
int n = stmt.executeUpdate();
stmt.close();

//lock the row for blob update
 String lockRowStr = "SELECT blob FROM " + MULTIMODAL_USER_DATA +
                                  " WHERE partition_id = ? " + " FOR UPDATE";
 stmt = conn.prepareStatement(lockRowStr);
 stmt.setInt(1,partitionId);
 ResultSet rs = stmt.executeQuery();

 rs.next();
oracle.sql.BLOB userDataBlob = (oracle.sql.BLOB) rs.getBlob(1);
stmt.close();

 OutputStream blobOut = ((oracle.sql.BLOB) userDataBlob).setBinaryStream(1);
 dout = new ObjectOutputStream(blobOut);

 //write partition ID
 dout.writeInt(partitionId);
 int numLinks = linkVector.size()

  for (int i=0; i<linkVector.size(); i++) {
        //MultimodalLink is a class with variables link ID and route ID
        MultimodalLink link = (MultimodalLink) linkVector.elementAt(i);
         //write link ID 
        dout.writeLong(link.getLinkId());

        // write route ID into file
        dout.writeInt(link.getRouteId());
   }
   dout.close();
    blobOut.close();
    rs.close();

• Implementation of writeUserData method of LODUserDataIO
  
• Implementation of readUserData method of LODUserDataIO
  

This section describes the following PL/SQL data types that are used for parameters and return values of some SDO_NET package subprograms related to feature modeling:

• SDO_NET_FEAT_ELEM_ARRAY

• SDO_NET_FEAT_ELEM

• SDO_NET_LAYER_FEAT_ARRAY

• SDO_NET_LAYER_FEAT

• SDO_NETWORK_NVP_TAB

• SDO_NETWORK_NVP

SDO_NET_FEAT_ELEM_ARRAY is defined as VARRAY(1024) OF MDSYS.SDO_NET_FEAT_ELEM.

SDO_NET_FEAT_ELEM is defined as:

FEAT_ELEM_TYPE    NUMBER
NET_ELEM_ID       NUMBER
START_PERCENTAGE  NUMBER
END_PERCENTAGE    NUMBER

SDO_NET_LAYER_FEAT_ARRAY is defined as VARRAY(1024) OF MDSYS.SDO_NET_LAYER_FEAT.

SDO_NET_LAYER_FEAT is defined as:

FEATURE_LAYER_ID  NUMBER
FEATURE_ID        NUMBER

SDO_NETWORK_NVP_TAB is defined as TABLE OF MDSYS.SDO_NETWORK_NVP.

SDO_NETWORK_NVP is defined as:

NAME   VARCHAR2(128)
VALUE  VARCHAR2(1024)

You can create a network feature editing (NFE) model in one of two possible modes: from scratch and over an existing network model.

• From scratch: In this mode, a network is generated automatically when you create the NFE the model, and the network is assign to the model. You will work only with features, and the underlying network elements will be hidden from you. This mode supports rules whose statements specify what kind of features (feature classes) can be connected with each other kinds of features. One typical use of this mode is to model utility networks, such as electricity or water networks, where you want to restrict the connections among certain components in the network.

• Over an existing network model: In this mode, an existing network is used when you create the NFE model. The network is visible to you, and you can add features over the network elements. Rules are not supported in this mode.

The feature class relationship table contains information about the relationship between features and feature classes, that is, which feature belongs to which feature class.

A feature class describes a group of features based on attributes values, shape, and style. Each feature class belongs only to one feature layer, so the group of features in the feature class also belongs to the same feature layer.

For example, an electrical network might include two feature layers: Transformers and Conductors. Each of these feature layers might include two feature classes:

• The Transformers feature layer might include the HT Transformers (high tension transformers) and LT Transformers (low tension transformers), where transformers within each feature type have specified input and output voltages, and tension types. Both feature types are associated with points (their shape). Each feature type has a different associated icon for use in diagrams.

• The Conductors feature layer might include the HT Conductors (high tension conductors) and LT Conductors (low tension conductors), where conductors within each feature type have specified attribute values. Both feature types are associated with lines (their shape). Each feature type has a different associated icon for use in diagrams.

The following table lists the shapes that can be associated with a feature class, and requirements and restrictions depending on the creation mode used for the NFE model..

Rules in NFE models are constraints related to feature connections. Rules are available only for models created from scratch, that is, not for models created over an existing network. Rules can be customized, and can support any kind of connection constraint.

Rules can generally be classified into cardinality rules and connectivity rules:

• Cardinality rules are used over point features that have specific characteristics (that is, belong to the same feature class), to specify the maximum and minimum number of incoming and outgoing connections that the point feature can accept.

• Connectivity rules are used to indicate whether two features can be connected if they have some specific characteristics (feature layer, feature class, and attributes) and a specific interaction. A connectivity rule will allow the connection with a point feature as long as the point feature has available space to support the corresponding incoming and outgoing connections needed, as dictated by its related cardinality rule.

  Connectivity rules are the only way to connect features in NFE, and they are always positive; that is, they allow connections, but cannot deny them. The absence of connectivity rules between elements indicates that elements cannot be connected.

This section describes the following PL/SQL data types that are used for parameters and return values of some SDO_NFE package subprograms related to network feature editing (NFE):

• SDO_INTERACT_POINT_FEAT_ARRAY

• SDO_INTERACT_POINT_FEAT

• SDO_INTERACT_LINE_FEAT_ARRAY

• SDO_INTERACT_LINE_FEAT

• SDO_INTERACTION_ARRAY

• SDO_INTERACTION

SDO_INTERACT_POINT_FEAT_ARRAY is defined as VARRAY(1024) OF MDSYS.SDO_INTERACT_POINT_FEAT.

SDO_INTERACT_POINT_FEAT is defined as:

FEATURE_LAYER_ID   	NUMBER 
FEATURE_ID         	NUMBER 
FEATURE_CLASS_ID   	NUMBER
NODE_ID            	NUMBER 
NODE_GEOM          	SDO_GEOMETRY
AVAILABLE_IN_CONN  	SDO_NUMBER_ARRAYSET
AVAILABLE_OUT_CONN	SDO_NUMBER_ARRAYSET
RUNTIME_CREATED 		CHAR(1)

SDO_INTERACT_LINE_FEAT_ARRAY is defined as VARRAY(1024) OF MDSYS.SDO_INTERACT_LINE_FEAT.

SDO_NET_LAYER_FEAT is defined as:

FEATURE_LAYER_ID  	NUMBER
FEATURE_ID        	NUMBER
FEATURE_CLASS_ID  	NUMBER
LINK_ID           	NUMBER
LINK_GEOM         	SDO_GEOMETRY
START_NODE        	SDO_INTERACT_POINT_FEAT
END_NODE          	SDO_INTERACT_POINT_FEAT
BIDIRECTED        	CHAR(1)
INTERSECTION_LOCATION 	NUMBER
RULE_SIDE         	CHAR(1)

SDO_INTERACTION_ARRAY is defined as VARRAY (1048576) OF MDSYS.SDO_INTERACTION.

SDO_INTERACTION is defined as:

LINES             SDO_INTERACT_LINE_FEAT_ARRAY
POINTS            SDO_INTERACT_POINT_FEAT_ARRAY
INTERSECT_PT_GEOM SDO_GEOMETRY

You can partition a network using the SDO_NET.SPATIAL_PARTITION procedure, specifying the maximum number of nodes in each partition. The partition result is stored in a partition table, which is automatically generated, and partition metadata information is inserted into the network metadata. (As an alternative to using the procedure, you can partition a network by creating and populating a partition table.) You can use other SDO_NET subprograms to query the partitioning metadata.

A good partition strategy is to minimize the number of links between partitions, which reduces the number of partitions that need to be loaded and the probable number of times that the same partitions need to be reloaded. Moreover, partitions that are too small require excessive loading and unloading of partitions during analysis.

The recommended maximum number of nodes per partition, assuming 1 GB of memory, is between 5,000 and 10,000. You can tune the number and see what is best for your applications, considering the available memory, type of analysis, and network size. You should also consider configuring the partition caching size.

To enhance the performance of network loading, you can optionally store partitions as BLOBs in a network partition BLOB table. This information needs to be stored in the network metadata view in order to take advantage of faster partition loading time. Note that if a network or partition information is updated, the partition BLOBs need to be regenerated as well.

A partition BLOB is a binary stream of data containing the network partition information, such as number of nodes, number of links, properties of each node, properties of each link, and so on. If a partition BLOB exists, Spatial uses it to read information during the load operation, rather than performing time-consuming database queries.

To generate partition BLOBs, use the SDO_NET.GENERATE_PARTITION_BLOBS procedure. The partition BLOBs and their metadata are stored in the Partition BLOB Table.

Before you perform network analysis, you can configure the network partition cache to optimize performance, by modifying an XML configuration file to override the default configuration. You can specify the following:

• Cache size: the maximum number of nodes in partition cache

• Partitions source: from network tables or partition BLOBs

• Resident partitions: IDs of partitions that will not be flushed out of the cache, but will stay in memory once they are loaded

• Cache flushing policy: class name of the CachingHandler implementation

  The default caching policy is LeastRecentlyUsed, which flushes out the oldest partition out of memory when the cache is full. You can specify other caching policies by implementing the CachingHandler interface.

A copy of the default load on demand configuration file is included in the supplementary documentation, described in Network Data Model Tutorial and Other Resources.

After you have created and partitioned the network, and optionally configured the partition cache, you can issue analysis queries. Analysis results are returned in Java representation or XML responses, depending on whether you used the Java or XML API. For details, see the load on demand (LOD) Javadoc and XML schemas (the latter described in Network Data Model Tutorial and Other Resources).

You can write the analysis results to the database using the load on demand Java API.

Although the load on demand approach reduces the effect of memory limitations in analyzing large networks, analysis operations still can sometimes be very slow. For example, shortest path analysis of two nodes diagonally across the entire network is likely to require traversing almost every link in the network, and this will take a significant amount of time for a network with more than, for example, two million nodes.

To further reduce network analysis time, you can perform analysis on different link levels. Link level is a positive integer assigned to a link indicating the level of preference of this link. The higher the link level, the higher the preference. For example, a road network may consist of two link levels, level 1 for local roads and level 2 for highways. During network analysis, highways are preferred to local roads, and the minimum link level is 1. (If no link level is assigned to a link, the default link level of 1 is used for the link.)

Link levels have an implicit inheritance property, which means that a network at higher link levels must be a subnetwork of a network at a lower link level. That is, link level 2 is a subnetwork of link level 1, link level 3 is a subnetwork of link level 2, and so on.

You can specify a link level when you load a network or a partition, which causes links at that level and higher levels to be loaded. Using the road network example, with link level 1 for local roads and link level 2 for highways, specifying link level 1 on a load operation loads links at link levels 1 and 2 (that is, local roads and highways), but specifying link level 2 on a load operation loads only the highways links. If you wanted to perform analysis using only highways links, you could optimize the performance by specifying link level 2 for the load operation.

Some analysis operations, such as connected component analysis, can be time consuming. To improve runtime performance, you can call the SDO_NET.FIND_CONNECTED_COMPONENTS procedure, which computes the connected components in the network and stores the results in the Connected Component Table.

At runtime, before calling shortest path analysis or reachability analysis, you can check whether the nodes of interest belong to the same connected component by querying the connected component table. If precomputed component information does not exist, it may take a long time for shortest path and reachability analysis to discover that two nodes are, in fact, not connected.

The metadata tables in this section are not related to feature modeling.

• Node Table
  
• Link Table
  
• Path Table
  
• Path-Link Table
  
• Subpath Table
  
• Partition Table
  
• Partition BLOB Table
  
• Connected Component Table
  
• Node Hierarchy Table (Optional)
  
• Node Level Table (Optional)
  

The tables in this section are related to feature modeling (see Feature Modeling). These tables are used to describe each registered feature layer.

In most applications, the tables containing feature entity information, feature to network relationships, or feature hierarchy relationships already exist, although the table schema may be different from that of the NDM tables. In such cases, you can create views to map the existing table schema to the NDM table schema.

• Feature Table
  
• Feature Element Relationships Table
  
• Feature Hierarchy Table
  

The tables in this section are related to feature manipulation using Network Feature Editing (NFE) (see Feature Modeling Using Network Feature Editing (NFE)). These tables are used to describe each NFE model.

In most cases, these tables are created or updated automatically when a new NFE Model is created using the PL/SQL function SDO_NFE.CREATE_MODEL_STRUCTURE or the Java API; but you can also manually create the tables or views and register them to the desired model.

• Automatically Created Points Default Attributes Table
  The automatically created points default attributes table contains information about the default values for the attributes of automatically created points as the result of the execution of a connectivity line-line rule.
• Connectivity Line-Line Rules Table
  The connectivity line-line rules table contains the information about the definition of connectivity line-line rules applicable to features involved in an NFE model.
• Connectivity Line-Point Rules Table
  The connectivity line-point rules table contains the information about the definition of connectivity line-point rules applicable to features involved in an NFE model.
• Feature Class Table
  A feature class is related to a feature. A feature class table contains the records of all feature classes from a feature layer, and must contain the columns described in the following table.
• Feature Class Attributes Constraints Table
  The feature class attributes constraints table contains information about user restrictions over feature class attributes, which are the same attributes defined for the feature layer that the feature class belongs to.
• Feature Class Default Predefined Connected Points Table
  The feature class default predefined connected points table contains the information about such default connected point features along a line feature described by a feature class.
• Feature Class Relationship Table
  The feature class relationship table contains information about the relationship between features and feature classes, that is, which feature belongs to which feature class.
• Feature Rule Relationship Table
  The feature rule relationship table contains information that relates each feature element involved in a rule, with the rule that caused its generation or connection.
• Feature User Data Table
  The feature user data table contains he information of feature class attributes of a catalog type.
• Feature User Data Catalog Table
  You can assign a value to a feature class attribute from a catalog; this is, that the attribute type from a feature class can be a catalog type. The feature user data catalog table keeps a register of the catalogs that can be used for that purpose.
• Feature User Data Catalog Values Table
  The feature user data catalog values table contains the list of values held by catalogs defined in feature user data catalog table.
• Point Cardinality Rules Table
  The point cardinality rules table contains the configuration of the maximum and minimum inbound and outbound connections that a specific point feature must support in an NFE model.
• Rule Decision Handlers Table
  The rule decision handlers table contains information about the names of the Java class and/or PL/SQL procedures to be executed as decision handlers when a connectivity rule (line-line or line-point) is executed.
• Rule Instance Table
  The rule instance table contains information about rule instances generated by the application of either line-line or line-point connectivity rules in an NFE model.

The following views contain information about network user-defined data, which is the information (not related to connectivity) that users want to associate with a network representation:

• USER_SDO_NETWORK_USER_DATA contains information about all network user-defined data owned by the user.

• ALL_SDO_NETWORK_USER_DATA contains information about all network user-defined data on which the user has SELECT permission.

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

To use user-defined data, you must set the USER_DEFINED_DATA column value to Y in the appropriate xxx_SDO_NETWORK_METADATA views (described in xxx_SDO_NETWORK_METADATA Views).

For an example of using user-defined data, see User-Defined Data Examples (PL/SQL and Java).

For user data defined through the xxx_SDO_NETWORK_USER_DATA views, the default user data I/O implementation (LODUserDataIOSDO) is used to access the user data during network analysis. However, some user data is not included in the node or link table, and thus cannot be registered through xxx_SDO_NETWORK_USER_DATA views. For such user data, you must provide your own implementation of the user data I/O interface. A typical way of implementing a custom user data I/O interface is to generate BLOBs corresponding to node and link user data , one BLOB for each partition, and then retrieve user data information from the BLOBs during network analysis.

You can also associate multiple categories of user-defined data (categorized user data) with a single network. For example, in a multimodal network (described in Multimodal Network and Temporal Examples), if you must associate driving-related attributes (such as speed limit) with a link in addition to the link's multimodal attributes, you can organize user-defined data in two categories: one for driving-related attributes and the other for multimodal attributes.

The following views contain information about network feature layers (described in Features and Feature Layers):

• USER_SDO_NETWORK_FEATURE contains information about all network feature layers owned by the user.

• ALL_SDO_NETWORK_FEATURE contains information about all network feature layers on which the user has SELECT permission.

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

To use user-defined data, you must set the USER_DEFINED_DATA column value to Y in the appropriate xxx_SDO_NETWORK_METADATA views (described in xxx_SDO_NETWORK_METADATA Views).

For an example of using user-defined data, see User-Defined Data Examples (PL/SQL and Java).

For user data defined through the xxx_SDO_NETWORK_USER_DATA views, the default user data I/O implementation (LODUserDataIOSDO) is used to access the user data during network analysis. However, some user data is not included in the node or link table, and thus cannot be registered through xxx_SDO_NETWORK_USER_DATA views. For such user data, you must provide your own implementation of the user data I/O interface. A typical way of implementing a custom user data I/O interface is to generate BLOBs corresponding to node and link user data , one BLOB for each partition, and then retrieve user data information from the BLOBs during network analysis.

You can also associate multiple categories of user-defined data (categorized user data) with a single network. For example, in a multimodal network (described in Multimodal Network and Temporal Examples), if you must associate driving-related attributes (such as speed limit) with a link in addition to the link's multimodal attributes, you can organize user-defined data in two categories: one for driving-related attributes and the other for multimodal attributes.

The following views contain information about network feature layers related to NFE models. (This topic assumes you are familiar with the concepts explained in Feature Modeling Using Network Feature Editing (NFE).)

• USER_SDO_NFE_MODEL_FTLAYER_REL contains information about feature layers that are related to all NFE models that are owned by the user.

• ALL_SDO_NFE_MODEL_FTLAYER_REL contains information about feature layers that are related to NFE models on which the user has SELECT permission.

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

The following views contain information about NFE models. (This topic assumes you are familiar with the concepts explained in Feature Modeling Using Network Feature Editing (NFE).)

• USER_SDO_NFE_MODEL_METADATA contains information about NFE models that are owned by the user.

• ALL_SDO_NFE_MODEL_METADATA contains information about NFE models on which the user has SELECT permission.

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

The following views contain information about workspaces related to NFE models. (This topic assumes you are familiar with the concepts explained in Feature Modeling Using Network Feature Editing (NFE).)

• USER_SDO_NFE_MODEL_WORKSPACE contains information about workspaces that are related to all NFE models that are owned by the user.

• ALL_SDO_NFE_MODEL_WORKSPACE contains information about workspaces that are related to NFE models on which the user has SELECT permission.

These views are automatically maintained by Spatial using the NFE Java and PL/SQL interfaces for creating and deleting Workspace Manager workspaces. You should never directly modify the content of these views.

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

The SDO_NET package provides subprograms for creating, accessing, and managing networks on a database server. Example 5-5 in Network Examples shows the use of SDO_NET functions and procedures.

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

• Creating networks:

  SDO_NET.CREATE_SDO_NETWORK

  SDO_NET.CREATE_LRS_NETWORK

  SDO_NET.CREATE_TOPO_NETWORK

  SDO_NET.CREATE_LOGICAL_NETWORK

• Copying and deleting networks:

  SDO_NET.COPY_NETWORK

  SDO_NET.DROP_NETWORK

• Creating network tables:

  SDO_NET.CREATE_NODE_TABLE

  SDO_NET.CREATE_LINK_TABLE

  SDO_NET.CREATE_PATH_TABLE

  SDO_NET.CREATE_PATH_LINK_TABLE

  SDO_NET.CREATE_LRS_TABLE

• Validating network objects:

  SDO_NET.VALIDATE_NETWORK

  SDO_NET.VALIDATE_NODE_SCHEMA

  SDO_NET.VALIDATE_LINK_SCHEMA

  SDO_NET.VALIDATE_PATH_SCHEMA

  SDO_NET.VALIDATE_LRS_SCHEMA

• Retrieving information (getting information about the network, checking for a characteristic):

  SDO_NET.GET_CHILD_LINKS

  SDO_NET.GET_CHILD_NODES

  SDO_NET.GET_GEOMETRY_TYPE

  SDO_NET.GET_IN_LINKS

  SDO_NET.GET_LINK_COST_COLUMN

  SDO_NET.GET_LINK_DIRECTION

  SDO_NET.GET_LINK_GEOM_COLUMN

  SDO_NET.GET_LINK_GEOMETRY

  SDO_NET.GET_LINK_TABLE_NAME

  SDO_NET.GET_LRS_GEOM_COLUMN

  SDO_NET.GET_LRS_LINK_GEOMETRY

  SDO_NET.GET_LRS_NODE_GEOMETRY

  SDO_NET.GET_LRS_TABLE_NAME

  SDO_NET.GET_NETWORK_TYPE

  SDO_NET.GET_NO_OF_HIERARCHY_LEVELS

  SDO_NET.GET_NO_OF_LINKS

  SDO_NET.GET_NO_OF_NODES

  SDO_NET.GET_NODE_DEGREE

  SDO_NET.GET_NODE_GEOM_COLUMN

  SDO_NET.GET_NODE_GEOMETRY

  SDO_NET.GET_NODE_IN_DEGREE

  SDO_NET.GET_NODE_OUT_DEGREE

  SDO_NET.GET_NODE_TABLE_NAME

  SDO_NET.GET_OUT_LINKS

  SDO_NET.GET_PATH_GEOM_COLUMN

  SDO_NET.GET_PATH_TABLE_NAME

  SDO_NET.IS_HIERARCHICAL

  SDO_NET.IS_LOGICAL

  SDO_NET.IS_SPATIAL

  SDO_NET.LRS_GEOMETRY_NETWORK

  SDO_NET.NETWORK_EXISTS

  SDO_NET.SDO_GEOMETRY_NETWORK

  SDO_NET.TOPO_GEOMETRY_NETWORK

For reference information about each SDO_NET function and procedure, see SDO_NET Package Subprograms.

The Network Data Model feature includes the load on demand Java interface. Complete reference information about this interface is provided in Oracle Spatial Java API Reference. The classes of the load on demand Java interface are in the oracle.spatial.network.lod package and its subpackages.

The Spatial Java class libraries are in .jar files under the <ORACLE_HOME>/md/jlib/ directory.

• Network Metadata and Data Management
  
• Network Analysis Using the Load on Demand Approach
  

You can use the Network Data Model XML API to perform network analysis. Web service requests are supported through Oracle Spatial web services, which are described in Oracle Spatial Developer's Guide.

HTTP requests can be sent to the web service from Java, PLSQL, or .NET programs or simply from a HTML form. The SDO_NET.POST_XML function (described in SDO_NET Package Subprograms) enables PL/SQL users to call the web service.

The XML schema of the Network Data Model XML API is described in the following: $ORACLE_HOME/md/doc/sdondmxml.zip

• User-Specified Implementations
  

To enable a non-owner user (with suitable privileges) to access a network, you can specify the network owner in the network metadata. For each non-owner user that will be permitted to access the network, follow these steps:

1. Ensure that the user has SELECT or READ privilege access to the necessary Network Data Model tables. If the user does not have this access, connect as the network owner and grant it. For example, connect as User1 and execute the following statements:

   GRANT select ON network1_node$ TO user2;
   GRANT select ON network1_link$ TO user2;
   GRANT select ON network1_path$ TO user2;
   GRANT select ON network1_plink$ TO user2;
   

2. Connect as the non-owner user. For example, connect as User2.

3. Use the schema name of the network owner to qualify the Network Data Model tables for the network in the USER_SDO_NETWORK_METADATA view (explained in xxx_SDO_NETWORK_METADATA Views). For example, if the network is not already defined in this view, enter the following while connected as User2:

   INSERT INTO user_sdo_network_metadata 
     (network, network_category, geometry_type, 
      node_table_name,node_geom_column,
      link_table_name, link_geom_column, link_direction,
      path_table_name, path_geom_column,
      path_link_table_name)
   VALUES
     ('NETWORK1','SPATIAL', 'SDO_GEOMETRY',
      'USER1.NETWORK1_NODE$', 'GEOMETRY',
      'USER1.NETWORK1_LINK$', 'GEOMETRY', 'DIRECTED',
      'USER1.NETWORK1_PATH$', 'GEOMETRY',
      'USER1.NETWORK1_PLINK$');
   

   If the network is already defined in this view, update the definition to qualify each table name with the schema name. For example:

   UPDATE USER_SDO_NETWORK_METADATA
     SET node_table_name = 'USER1.NETWORK1_NODE$',
         link_table_name = 'USER1.NETWORK1_LINK$',
         path_table_name = 'USER1.NETWORK1_PATH$',
         path_link_table_name = 'USER1.NETWORK1_PLINK$'
     WHERE network = 'NETWORK1';
   

In this scenario, User2 can now read NETWORK1 into memory.

To enable a non-owner user (with suitable privileges) to access a network, or specific parts of a network, you can create views. For each non-owner user that will be permitted to access the network, follow these steps:

1. Ensure that the user has SELECT or READ privilege access to the necessary Network Data Model tables. If the user does not have this access, connect as the network owner and grant it. For example, connect as User1 and execute the following statements:

   GRANT select ON network1_node$ TO user2;
   GRANT select ON network1_link$ TO user2;
   GRANT select ON network1_path$ TO user2;
   GRANT select ON network1_plink$ TO user2;
   

2. Connect as the non-owner user. For example, connect as User2.

3. Create a view on each of the necessary Network Data Model nodes, with each view selecting all columns in the associated table. Qualify the table name with the schema name of the network owner. For example, while connected as User2:

   CREATE VIEW network1_node$ AS select * from user1.network1_node$;
   CREATE VIEW network1_link$ AS select * from user1.network1_link$;
   CREATE VIEW network1_path$ AS select * from user1.network1_path$;
   CREATE VIEW network1_plink$ AS select * from user1.network1_plink$;

   Note:

   Although this example shows views that include all data in the underlying tables, you can restrict the parts of the network that are available by using a WHERE clause in each view definition (for example, WHERE STATE_CODE='NY').

4. Add a row specifying the newly created views to the USER_SDO_NETWORK_METADATA view (explained in xxx_SDO_NETWORK_METADATA Views). For example, while connected as User2:

   INSERT INTO user_sdo_network_metadata 
     (network, network_category, geometry_type, 
      node_table_name,node_geom_column,
      link_table_name, link_geom_column, link_direction,
      path_table_name, path_geom_column,
      path_link_table_name)
   VALUES
     ('NETWORK1','SPATIAL', 'SDO_GEOMETRY',
      'NETWORK1_NODE$', 'GEOMETRY',
      'NETWORK1_LINK$', 'GEOMETRY', 'DIRECTED',
      'NETWORK1_PATH$', 'GEOMETRY',
      'NETWORK1_PLINK$');
   

In this scenario, User2 can now read into memory those parts of NETWORK1 that are available through the views that were created.

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

Figure 5-5 Simple Spatial (SDO) Network

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

As shown in Figure 5-5, 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-3 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-3 does not show the use of many SDO_NET functions and procedures; these are included in Example 5-5 in Spatial (LRS) 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;

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

Figure 5-6 Simple Logical Network

Description of "Figure 5-6 Simple Logical Network"

As shown in Figure 5-6, 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-4 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-4 does not show the use of many SDO_NET functions and procedures; these are included in the logical hierarchical network example (Example 5-6) in Logical Hierarchical Network Example (PL/SQL).

-- Creates the LOG_NET1 directed logical network. Also creates the
-- LOG_NET1_NODE$, LOG_NET1_LINK$, LOG_NET1_PATH$,
-- and LOG_NET1_PLINK$ tables, and updates USER_SDO_NETWORK_METADATA.
-- Both the node and link tables contain a cost column named COST. 
EXECUTE SDO_NET.CREATE_LOGICAL_NETWORK('LOG_NET1', 1, TRUE, TRUE);
 
-- Populate the LOG_NET1_NODE$ table.
-- N1
INSERT INTO log_net1_node$ (node_id, node_name, active, cost)
  VALUES (1, 'N1', 'Y', 2);
-- N2
INSERT INTO log_net1_node$ (node_id, node_name, active, cost)
  VALUES (2, 'N2', 'Y', 3);
-- N3
INSERT INTO log_net1_node$ (node_id, node_name, active, cost)
  VALUES (3, 'N3', 'Y', 2);
 
-- Populate the LOG_NET1_LINK$ table.
-- L1
INSERT INTO log_net1_link$ (link_id, link_name, start_node_id, end_node_id,
     active, link_level, cost)
  VALUES (1, 'L1', 1, 2, 'Y', 1, 10);
-- L2
INSERT INTO log_net1_link$ (link_id, link_name, start_node_id, end_node_id,
     active, link_level, cost)
  VALUES (2, 'L2', 2, 3, 'Y', 1, 7);
-- L3
INSERT INTO log_net1_link$ (link_id, link_name, start_node_id, end_node_id,
     active, link_level, cost)
  VALUES (3, 'L3', 3, 1, 'Y', 1, 8);
 
-- Do not populate the LOG_NET1_PATH$ and LOG_NET1_PLINK$ tables now.
-- Do this only when you need to create any paths.

This section presents an example of a spatial (LRS) network that uses the roads (routes) illustrated in Figure 5-7. 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-7 Roads and Road Segments for Spatial (LRS) Network Example

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

As shown in Figure 5-7:

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

---------------------------------------------------------------------------
-- 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.
 
-- 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_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_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');

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

Figure 5-8 Nodes and Links for Logical Network Example

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

As shown in Figure 5-8:

• The network is hierarchical, with two levels. The top level (level 2) consists of two nodes (HN1 and HN2), and 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.

• One link (HN1HN2) links nodes HN1 and HN2, and two links (N5N8 and N6N7) are child links of parent link HN1HN2. Note, however, that links are not associated with a specific network hierarchy level.

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

-- 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.
 
-- 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_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');

This section presents examples of partitioning a network, including related operations, and performing load on demand network analysis. The examples illustrate concepts and techniques explained in Network Analysis Using Load on Demand.

Additional examples of using load on demand analysis with partitioned networks are included in the demo files, described in Network Data Model Tutorial and Other Resources.

exec sdo_net.spatial_partition(
  network->'NYC_NET', -- network name
  partition_table_name->'NYC_PART$', -- partition table name
  max_num_nodes->5000, -- max. number of nodes per partition
  log_loc->'MDDIR', -- partition log directory
  log_file->'nyc_part.log', --partition log file name
  open_mode->'w', -- partition log file open mode
  link_level->1); -- link level

exec sdo_net.generate_partition_blobs(
  network->'NYC_NET', ,-- network name
  link_level ->1, -- link level
  partition_blob_table_name->'NYC_PBLOB$', -- partition blob table name
  includeUserdata->FALSE, -- include user data in partition blobs?
  log_loc->'MYDIR',  -- partition log directory
  log_file->'nyc_part.log', --partition log file name
  open_mode->'a'); -- partition log file open mode

<?xml version="1.0" encoding="UTF-8" ?>
<LODConfigs xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xmlns ="http://xmlns.oracle.com/spatial/network"
            version = "12.1">      
<!--The new xml configuration schema takes the version number. If the version attribute is missing, then we assume it is 11.2 or lower. -->
<!-- default configuration for networks not configured -->
  <LODConfig globalNetworkName="$DEFAULT$" networkName="$DEFAULT$">
    <networkIO>
      <geometryTolerance>0.000001</geometryTolerance>
      <readPartitionFromBlob>false</readPartitionFromBlob>
      <partitionBlobTranslator>
        <className>oracle.spatial.network.lod.PartitionBlobTranslator11gR2</className>
        <parameters></parameters>
      </partitionBlobTranslator>
      <userDataIO categoryId="0">
        <className>oracle.spatial.network.lod.LODUserDataIOSDO</className>
        <parameters></parameters>
      </userDataIO>
      <cachingPolicy linkLevel="1">
        <maxNodes>500000</maxNodes>
        <residentPartitions></residentPartitions>
        <flushRule>
          <className>oracle.spatial.network.lod.LRUCachingHandler</className>
          <parameters></parameters>
        </flushRule>
      </cachingPolicy>
    </networkIO>
    <networkAnalysis>
      <linkLevelSelector>
        <className>oracle.spatial.network.lod.DummyLinkLevelSelector</className>
        <parameters></parameters>
      </linkLevelSelector>
      <withinCostPolygonTolerance>0.05</withinCostPolygonTolerance>
    </networkAnalysis>
  </LODConfig>
  <LODConfig globalNetworkName="SAMPLE_NETWORK" networkName="SAMPLE_NETWORK">
    <networkIO>
      <geometryTolerance>0.000001</geometryTolerance>
      <readPartitionFromBlob>true</readPartitionFromBlob>
      <partitionBlobTranslator>
        <className>oracle.spatial.router.ndm.RouterPartitionBlobTranslator11gR2</className>
        <parameters></parameters>
      </partitionBlobTranslator>
      <userDataIO categoryId="0">
        <className>oracle.spatial.network.lod.LODUserDataIOSDO</className>
        <parameters></parameters>
      </userDataIO>
      <userDataIO categoryId="1">
        <className>oracle.spatial.router.ndm.RouterUserDataIO</className>
        <parameters></parameters>
      </userDataIO>
      <cachingPolicy linkLevel="1">
        <maxNodes>200000</maxNodes>
        <residentPartitions></residentPartitions>
        <flushRule>
          <className>oracle.spatial.network.lod.LRUCachingHandler</className>
          <parameters></parameters>
        </flushRule>
      </cachingPolicy>
      <cachingPolicy linkLevel="2">
        <maxNodes>800000</maxNodes>
        <residentPartitions>0</residentPartitions>
        <flushRule>
          <className>oracle.spatial.network.lod.LRUCachingHandler</className>
          <parameters></parameters>
        </flushRule>
      </cachingPolicy>
    </networkIO>
    <networkAnalysis>
    </networkAnalysis>
  </LODConfig>
</LODConfigs>

InputStream config = ClassLoader.getSystemResourceAsStream(
                     "netlodcfg.xml");
LODNetworkManager.getConfigManager().loadConfig(config);

EXECUTE SDO_NET.LOAD_CONFIG('WORK_DIR', 'netlodcfg.xml');

SELECT SDO_NET.GET_PARTITION_SIZE (
  NETWORK->'NYC_NET',
  PARTITION_ID->1,
  LINK_LEVEL ->1,
  INCLUDE_USER_DATA->'FALSE',
  INCLUDE_SPATIAL_DATA->'TRUE') FROM DUAL;

Connection conn = LODNetworkManager.getConnection(dbUrl, dbUser, dbPassword);
// get LOD network IO Adapter
String networkName = "NYC_NET";
NetworkIO reader = LODNetworkManager.getCachedNetworkIO(conn, networkName, networkName, null);
// get analysis module
NetworkAnalyst analyst = LODNetworkManager.getNetworkAnalyst(reader);
// compute the shortest path
LogicalSubPath path = analyst.shortestPathDijkstra(new PointOnNet(startNodeId),
      new PointOnNet(endNodeId), null);
// print path result
PrintUtility.print(System.out, path, false, 0, 0);
. . .

<?xml version="1.0" encoding="UTF-8"?>
<ndm:networkAnalysisRequest
    xmlns:ndm="http://xmlns.oracle.com/spatial/network"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:gml="http://www.opengis.net/gml">
  <ndm:networkName>NYC_NET</ndm:networkName>
  <ndm:shortestPath>
    <ndm:startPoint>
      <ndm:nodeID>65</ndm:nodeID>
    </ndm:startPoint>
    <ndm:endPoint>
      <ndm:nodeID>115</ndm:nodeID>
    </ndm:endPoint>
    <ndm:subPathRequestParameter>
      <ndm:isFullPath> true </ndm:isFullPath>
      <ndm:startLinkIndex> true </ndm:startLinkIndex>
      <ndm:startPercentage> true </ndm:startPercentage>
      <ndm:endLinkIndex> true </ndm:endLinkIndex>
      <ndm:endPercentage> true </ndm:endPercentage>
      <ndm:geometry>false</ndm:geometry>
    <ndm:pathRequestParameter>
      <ndm:cost> true </ndm:cost>
      <ndm:isSimple> true </ndm:isSimple>
      <ndm:startNodeID>true</ndm:startNodeID>
      <ndm:endNodeID>true</ndm:endNodeID>
      <ndm:noOfLinks>true</ndm:noOfLinks>
      <ndm:linksRequestParameter>
        <ndm:onlyLinkID>true</ndm:onlyLinkID>
      </ndm:linksRequestParameter>
      <ndm:nodesRequestParameter>
        <ndm:onlyNodeID>true</ndm:onlyNodeID>
      </ndm:nodesRequestParameter>
      <ndm:geometry>true</ndm:geometry>
    </ndm:pathRequestParameter>
    </ndm:subPathRequestParameter>
  </ndm:shortestPath>
</ndm:networkAnalysisRequest>
 
<?xml version = '1.0' encoding = 'UTF-8'?>
<ndm:networkAnalysisResponse xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ndm="http://xmlns.oracle.com/spatial/network" xmlns:gml="http://www.opengis.net/gml">
  <ndm:networkName>NYC_NET</ndm:networkName>
  <ndm:shortestPath>
    <ndm:subPathResponse>
      <ndm:isFullPath>true</ndm:isFullPath>
      <ndm:startLinkIndex>0</ndm:startLinkIndex>
      <ndm:startPercentage>0.0</ndm:startPercentage>
      <ndm:endLinkIndex>17</ndm:endLinkIndex>
      <ndm:endPercentage>1.0</ndm:endPercentage>
      <ndm:pathResponse>
        <ndm:cost>6173.212694405703</ndm:cost>
        <ndm:isSimple>true</ndm:isSimple>
        <ndm:startNodeID>65</ndm:startNodeID>
        <ndm:endNodeID>115</ndm:endNodeID>
        <ndm:noOfLinks>18</ndm:noOfLinks>
        <ndm:linkIDs>145477046 145477044 145477042 145477039 145476926 145476930 145480892 145480891 145476873 145476871 145477023 145489019 145489020 145476851 145488986 145488987 145476913 145476905         
        </ndm:linkIDs>
        <ndm:nodeIDs>65 64 60 57 58 61 71 70 73 87 97 95 91 101 102 104 117 120 115 
        </ndm:nodeIDs>
        <ndm:geometry>
          <gml:LineString>
            <gml:coordinates>-71.707462,43.555262 -71.707521,43.555601…
            </gml:coordinates>
          </gml:LineString>
        </ndm:geometry>
      </ndm:pathResponse>
    </ndm:subPathResponse>
  </ndm:shortestPath>
</ndm:networkAnalysisResponse>

This section presents examples of using network user-defined data, which is the information (not related to connectivity) that users want to associate with a network representation. The USER_SDO_NETWORK_USER_DATA and ALL_SDO_NETWORK_USER_DATA metadata views (described in xxx_SDO_NETWORK_USER_DATA Views) contain information about user-defined data.

To use user-defined data, you must set the USER_DEFINED_DATA column value to Y in the appropriate xxx_SDO_NETWORK_METADATA views (described in xxx_SDO_NETWORK_METADATA Views).

-- Insert link user data named 'interaction' of
-- type varchar2 (50) in network 'bi_test'.
--'interaction' is a column of type varchar2(50) in the link table of network 'bi_
test'.
insert into user_sdo_network_user_data 
         (network,table_type, data_name, data_type, data_length, category_id) 
          values ('bi_test', 'LINK', 'interaction', 'VARCHAR2', 50, 0) ;
-- insert link user data named 'PROB' of type Number.
--'PROB' is a column of type NUMBER in the link table of network 'bi_test'.
insert into user_sdo_network_user_data 
         (network,table_type,data_name,data_type, category_id)
          values ('bi_test','LINK','PROB','NUMBER', 0) ;

// The user data index is the sequence number of a user data within a category
// sorted by data name.
 
int interactionUserDataIndex = 0;
int probUserDataIndex = 1;
 
String interaction = (String)link.getCategorizedUserData().getUserData(0).
                     get(interactionUseDataIndex);
 
double prob = ((Double)link.getCategorizedUserData().getUserData(0).
              get(probUserdataIndex)).doubleValue();

 //Method getLinksInPartition(partitionId) computes a vector that
 // consists of  the ID and the route ID of each link associated with a partition
 // with ID = partitionId
 LinkVector = getLinksInPartition(partitionId);
 
 ObjectOutputStream dout = null;
 
 //Insert an empty blob for the partition with ID = partition_id
 String insertStr = "INSERT INTO " + MULTIMODAL_USER_DATA +
                              " (partition_id, blob) " + " VALUES " + " (?, EMPTY_BLOB())" ;
 
 PreparedStatement stmt = conn.prepareStatement(insertStr);
 stmt.setInt(1,partitionId);
 int n = stmt.executeUpdate();
 stmt.close();
 
 //lock the row for blob update
  String lockRowStr = "SELECT blob FROM " + MULTIMODAL_USER_DATA +
                                   " WHERE partition_id = ? " + " FOR UPDATE";
  stmt = conn.prepareStatement(lockRowStr);
  stmt.setInt(1,partitionId);
  ResultSet rs = stmt.executeQuery();
 
  rs.next();
 oracle.sql.BLOB userDataBlob = (oracle.sql.BLOB) rs.getBlob(1);
 stmt.close();
 
  OutputStream blobOut = ((oracle.sql.BLOB) userDataBlob).setBinaryStream(1);
  dout = new ObjectOutputStream(blobOut);
 
  //write partition ID
  dout.writeInt(partitionId);
  int numLinks = linkVector.size()
 
   for (int i=0; i<linkVector.size(); i++) {
         //MultimodalLink is a class with variables link ID and route ID
         MultimodalLink link = (MultimodalLink) linkVector.elementAt(i);
          //write link ID 
         dout.writeLong(link.getLinkId());
 
         // write route ID into file
         dout.writeInt(link.getRouteId());
    }
    dout.close();
     blobOut.close();
     rs.close();

//Read the blob for the required partition from the user data blob table
// In this example,
// MULTIMODAL_USER_DATA  is the name of user –defined data blob table
BLOB multimodalBlob = null;
String queryStr = "SELECT blob FROM " + MULTIMODAL_USER_DATA                                
                             " WHERE partition_id = ?";
PreparedStatement stmt = conn.prepareStatement(queryStr);
stmt.setInt(1,partitionId);
ResultSet rs = stmt.executeQuery();
if (rs.next())   {
     multimodalBlob = (oracle.sql.BLOB)rs.getBlob(1);
}
 
// Materialize the blob value as an input stream        
InputStream is = multimodalBlob.getBinaryStream();
 
//Create an ObjectInputStream that reads from the InputStream is
ObjectInputStream ois = new ObjectInputStream(is);
 
//Read the values of partition ID and number of links from the blob
int partitionId = ois.readInt();
int numLinks = ois.readInt();
 
for (int i=0; i<numLinks; i++)  {
 
    //Read link ID and route ID for each link
     long linkId = ois.readLong();
     int routeId = ois.readInt();
 
     //MultimodalLinkUserData is an implementation of NDM LOD UserData interface
     //Implementation is provided at the end of the example 
     linkUserData = new MultimodalLinkUserData(routeId);
 
     //Get the link object corresponding to the link ID
     LogicalNetLink link = partition.getLink(linkId);
 
     //Get the (categorized) user data associated with the link. 
     CategorizedUserData cud = link.getCategorizedUserData();
    
     // If the link does not have categorized user data associated with it,
     // initialize it to linkUserData
     // Else, set the user data for category USER_DATA_MULTIMODAL 
     // to linkUserData 
     if (cud == null) {
            UserData [] userDataArray = {linkUserData};
            cud = new CategorizedUserDataImpl(userDataArray);
             link.setCategorizedUserData(cud);
     }
     else {                   
            cud.setUserData(USER_DATA_MULTIMODAL,linkUserData);
     }
}

//info is an instance of LODAnalysisInfo 
LogicalLink currentLink = info.getCurrentLink();
 
//Read the user-defined data (in this case, route ID) 
int linkRouteId   = (Integer)currentLink.getCategorizedUserData().
                           getUserData(USER_DATA_MULTIMODAL).                                      
                           get(INDEX_LINK_ROUTEID);

class MultimodalLinkUserData implements UserData
{
    private int routeId;
 
    protected MultimodalLinkUserData(int routeId)
   {
        this.routeId = routeId;
   }
 
  public Object get(int index)
  {
    switch(index)
    {
         case INDEX_LINK_ROUTEID:
              return routeId;
    }
    return null;
  }
 
  public void set(int index, Object userData)
  {
    switch(index)
    {
         case INDEX_LINK_ROUTEID:
            this.routeId = (Integer) userData;
    }
  }
 
  public int getNumberOfUserData()
  {
       return 1;
  }
 
  public Object clone()
{
    return new MultimodalLinkUserData(routeId);
  }
}