5.17 Network Examples
This topic presents several Network Data Model examples.
Most are simplified examples. All examples use the PL/SQL API, and some also use other APIs.
The examples refer to concepts that are explained in this chapter, and they use PL/SQL functions and procedures documented in SDO_NET Package Subprograms.
- Simple Spatial (SDO) Network Example (PL/SQL)
- Simple Logical Network Example (PL/SQL)
- Spatial (LRS) Network Example (PL/SQL)
- Logical Hierarchical Network Example (PL/SQL)
- Partitioning and Load on Demand Analysis Examples (PL/SQL, XML, and Java)
- User-Defined Data Examples (PL/SQL and Java)
Parent topic: Network Data Model Overview
5.17.1 Simple Spatial (SDO) Network Example (PL/SQL)
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.
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-7 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-7 does not show the use of many SDO_NET functions and procedures; these are included in Example 5-9 in Spatial (LRS) Network Example (PL/SQL).
Example 5-7 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;
Parent topic: Network Examples
5.17.2 Simple Logical Network Example (PL/SQL)
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.
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-8 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-8 does not show the use of many SDO_NET functions and procedures; these are included in the logical hierarchical network example (Example 5-10) in Logical Hierarchical Network Example (PL/SQL).
Example 5-8 Simple Logical 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.
Parent topic: Network Examples
5.17.3 Spatial (LRS) Network Example (PL/SQL)
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 follows Description of Figure 5-7 follows](img/net_roads.gif)
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
, andN7
. It has the following links:R1L1
,R1L2
,R1L3
,R1L4
,R1L5
, andR1L6
. -
Route2
starts at point 8,4 and ends at point 8,13. It has the following nodes:N3
,N6
, andN8
. It has the following links:R2L1
andR2L2
. -
Route3
starts at point 12,10 and ends at point 5,14. It has the following nodes:N5
,N8
, andN7
. It has the following links:R3L1
andR3L2
. -
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-9 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.
Example 5-9 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. -- 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');
Parent topic: Network Examples
5.17.4 Logical Hierarchical Network Example (PL/SQL)
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 follows Description of Figure 5-8 follows](img/net_hier2.gif)
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
andHN2
), 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
, andN6
. NodeHN2
has the following child nodes:N7
,N8
,N9
,N10
,N11
,N12
,N13
, andN14
. -
One link (
HN1HN2
) links nodesHN1
andHN2
, and two links (N5N8
andN6N7
) are child links of parent linkHN1HN2
. Note, however, that links are not associated with a specific network hierarchy level.
Example 5-10 does the following:
-
Creates and populates the node table.
-
Creates and populates the link table.
-
Creates and populates the path table and path-link table, for possible future use. (Before an application can use paths, you must populate these two tables.)
-
Inserts network metadata into the USER_SDO_NETWORK_METADATA view.
-
Uses various SDO_NET functions and procedures.
Example 5-10 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. -- 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');
Parent topic: Network Examples
5.17.5 Partitioning and Load on Demand Analysis Examples (PL/SQL, XML, and Java)
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.
Example 5-11 Partitioning a Spatial Network
Example 5-11 partitions a spatial network named NYC_NET
. (Assume that this network already exists and its metadata, node, and link tables are populated.)
Example 5-11 and Example 5-12 generate the necessary partition tables for the NYC_NET
network. After executing these examples, you can check the .log file for the current status or any errors encountered during partitioning or BLOB generation.
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
Example 5-12 Generating Partition BLOBs
Example 5-12 generates partition BLOBs for the network.
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
Example 5-13 Configuring the Load on Demand Environment, Including Partition Cache
Example 5-13 shows the XML for configuring the load on demand environment, including the partition cache.
<?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>
Example 5-14 Reloading the Load on Demand Configuration (Java API)
Example 5-14 and Example 5-15 show the Java and PL/SQL APIs, respectively, for reloading the load on demand configuration.
InputStream config = ClassLoader.getSystemResourceAsStream( "netlodcfg.xml"); LODNetworkManager.getConfigManager().loadConfig(config);
Example 5-15 Reloading the Load on Demand Configuration (PL/SQL API)
EXECUTE SDO_NET.LOAD_CONFIG('WORK_DIR', 'netlodcfg.xml');
Example 5-16 Getting Estimated Partition Size
Example 5-16 returns the estimated size in bytes for a specified network partition.
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;
Example 5-17 Network Analysis: Shortest Path (LOD Java API)
Example 5-17 uses the load on demand Java API (oracle.spatial.network.lod) to issue a shortest-path query on a network.
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); . . .
Example 5-18 Network Analysis: Shortest Path (XML API)
Example 5-18 uses the XML API (oracle.spatial.network.xml) to issue a shortest-path query on a network. It includes the request and the response.
<?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>
Parent topic: Network Examples
5.17.6 User-Defined Data Examples (PL/SQL and Java)
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).
Example 5-19 Inserting User-Defined Data into Network Metadata
Example 5-19 uses the PL/SQL API to insert 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();
Example 5-20 Implementation of writeUserData method of LODUserDataIO Interface
Example 5-20 uses the Java API for a custom user data I/O implementation (non-default implementation) of the LODUserDataIO
interface. User data associated to links is written to BLOBs (one BLOB per partition) and read from BLOBs during analysis. In this example 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
-
The user-defined data BLOB table name is MULTIMODAL_USER_DATA
//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();
Example 5-21 Implementation of readUserData method of LODUserDataIO Interface
In Example 5-21, the user-defined data is accessed through the getCategorizedUserData
and setCategorizedUserData
methods for the Node
, Link
, Path
, and SubPath
interfaces and the getUserData
and setUserData
methods of the CategorizedUserData
interface.
//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); } }
The following example shows how to read the user-defined data, namely the route ID associated with a link during analysis:
//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);
The implementation of the MultimodalLinkUserData
interface is as follows:
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); } }
Parent topic: Network Examples