Network In Oracle Spatial

 What is Networking in Oracle Spatial?

I suggest you watch the video below about this article.

A spatial network is a collection of connected nodes and edges:

• Nodes: Points representing intersections, stops, or terminals.
• Edges: Lines representing roads, pipelines, or pathways.

Networking in Oracle Spatial is used for:

• Finding shortest paths.
• Analyzing connectivity (e.g., "Can I travel between two points?").
• Calculating flow (e.g., traffic, water supply).

Key Components of Oracle Spatial Networking

1. Nodes Table: Represents points (e.g., road intersections).
2. Edges Table: Represents connections between nodes (e.g., roads).
3. Network Metadata: Defines the network structure and stores rules.
4. Network Analysis Functions: Built-in functions to analyze the network (e.g., shortest path).

Implement Networking

1. Prerequisites

• Ensure Oracle Spatial and Graph is installed and enabled in your database.
• Grant necessary privileges to your user:

  GRANT ALL ON NETWORK_ADMIN TO MY_USER;
  

2. Create a Network Workspace

The network workspace organizes all network-related data.

BEGIN
  SDO_NET.CREATE_NETWORK(
    'ROAD_NETWORK',    -- Name of the network
    'MY_SCHEMA',       -- Schema
    'DIRECTED NETWORK' -- Type (directed/undirected)
  );
END;

3. Create Tables for Nodes and Edges

Nodes Table:

CREATE TABLE ROAD_NODES (
  NODE_ID NUMBER PRIMARY KEY,  -- Unique ID for the node
  GEOMETRY SDO_GEOMETRY        -- Geometry of the node
);

-- Example: Insert nodes
INSERT INTO ROAD_NODES VALUES (1, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(10, 10, NULL), NULL, NULL));
INSERT INTO ROAD_NODES VALUES (2, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(20, 20, NULL), NULL, NULL));
INSERT INTO ROAD_NODES VALUES (3, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(30, 30, NULL), NULL, NULL));

Edges Table:

CREATE TABLE ROAD_EDGES (
  EDGE_ID NUMBER PRIMARY KEY,     -- Unique ID for the edge
  START_NODE_ID NUMBER,           -- Start node
  END_NODE_ID NUMBER,             -- End node
  GEOMETRY SDO_GEOMETRY,          -- Geometry of the edge
  COST NUMBER                     -- Cost of traversal (e.g., distance, time)
);

-- Example: Insert edges
INSERT INTO ROAD_EDGES VALUES (1, 1, 2, 
  SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(10, 10, 20, 20)), 
  10);

INSERT INTO ROAD_EDGES VALUES (2, 2, 3, 
  SDO_GEOMETRY(2002, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(20, 20, 30, 30)), 
  15);

4. Register Nodes and Edges to the Network

Register the nodes and edges in the network workspace.

Register Nodes:

BEGIN
  SDO_NET.REGISTER_NODE_TABLE(
    'ROAD_NETWORK',       -- Network name
    'ROAD_NODES',         -- Nodes table name
    'NODE_ID',            -- Node ID column
    'GEOMETRY'            -- Geometry column
  );
END;

Register Edges:

BEGIN
  SDO_NET.REGISTER_EDGE_TABLE(
    'ROAD_NETWORK',       -- Network name
    'ROAD_EDGES',         -- Edges table name
    'EDGE_ID',            -- Edge ID column
    'START_NODE_ID',      -- Start node ID
    'END_NODE_ID',        -- End node ID
    'GEOMETRY',           -- Geometry column
    'COST'                -- Cost column
  );
END;

5. Build the Network

The network is now defined, and you can build its topology.

BEGIN
  SDO_NET.BUILD_NETWORK(
    'ROAD_NETWORK'
  );
END;

6. Query the Network

Find the Shortest Path Between Two Nodes:

Use the SDO_NET.SHORTEST_PATH function.

SELECT * 
FROM TABLE(
  SDO_NET.SHORTEST_PATH(
    'ROAD_NETWORK',       -- Network name
    1,                    -- Start node ID
    3,                    -- End node ID
    'COST'                -- Cost attribute
  )
);

Find All Connected Nodes:

Use the SDO_NET.CONNECTED_NODES function.

SELECT * 
FROM TABLE(
  SDO_NET.CONNECTED_NODES(
    'ROAD_NETWORK',       -- Network name
    1                     -- Starting node ID
  )
);

Check If Two Nodes Are Connected:

Use the SDO_NET.CONNECTIVITY function.

SELECT SDO_NET.CONNECTIVITY(
  'ROAD_NETWORK',         -- Network name
  1,                      -- Start node ID
  3                       -- End node ID
) AS IS_CONNECTED
FROM DUAL;