Rem Rem $Header: sdo/admin/sdortprt.sql /main/15 2009/03/18 11:15:05 cfreiwal Exp $ Rem Rem sdortprt.sql Rem Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem sdortprt.sql - SDO Router Partition Rem Rem DESCRIPTION Rem Partitions the road network data for the Oracle Router Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem sravada 02/11/09 - change to SYS.DBMS_ASSERT Rem cfreiwal 01/05/09 - Adjust VM for partition validation Rem cfreiwal 12/05/08 - Add trucking data Rem cfreiwal 10/29/08 - Add views and metadata for NDM Rem cfreiwal 02/12/08 - Fix bug 6762742, Router versioning Rem cfreiwal 08/16/07 - add partition validation Rem cfreiwal 07/19/07 - Bug 6159004 Rem cfreiwal 05/11/07 - Fix bug 5856048 Rem cfreiwal 05/09/07 - fix bug 5940583 Rem cfreiwal 04/25/07 - Bug 5989050: fix driving directions Rem cfreiwal 02/05/07 - Add turn restrictions Rem jcwang 09/05/06 - add table_type to hand tables and views Rem jcwang 09/04/06 - allow initial partition table to be a view Rem jcwang 08/30/06 - add set_log_info and log_message without date information Rem cfreiwal 10/08/05 - reconcile with router_partition_pkg.sql Rem cfreiwal 04/18/05 - Improve get geometry performance Rem sravada 07/30/04 - add create/replace Rem sravada 06/17/04 - sravada_fix_route_partitioning Rem sravada 06/15/04 - fix route partitioning (rename, create syn, Rem and grant public privs and install under mdsys. Rem cfreiwal 06/10/04 - cfreiwal_router_partition_package Rem cfreiwal 06/02/04 - create partition table Rem cfreiwal 05/25/04 - Created Rem -- Type to represent a Vector 2D -- CREATE or REPLACE TYPE vector_2d as OBJECT ( x NUMBER, y NUMBER); / grant execute on vector_2d to public; CREATE or REPLACE TYPE NUM_ARRAY as VARRAY(100) of NUMBER; / grant execute on NUM_ARRAY to public; CREATE or REPLACE TYPE string_array AS VARRAY(1048576) OF VARCHAR2(256); / grant execute on string_array to public; SHOW ERRORS; -- Wrapper to expand the memory available in the Java user heap -- CREATE OR REPLACE PROCEDURE ElocationSetJVMHeapSize(sz NUMBER) AS LANGUAGE JAVA NAME 'oracle.aurora.vm.OracleRuntime.setMaxMemorySize(long)'; . / SHOW ERRORS; -- Drop and recreate the temporary tables needed for partitioning -- DROP TABLE final_partition; DROP TABLE partition_tmp_2; DROP TABLE partition_tmp_3; CREATE TABLE final_partition(vertex_id NUMBER, p_id NUMBER, x NUMBER, y NUMBER); CREATE TABLE partition_tmp_2(vertex_id NUMBER, p_id NUMBER, x NUMBER, y NUMBER, m NUMBER); CREATE TABLE partition_tmp_3(vertex_id NUMBER, p_id NUMBER, x NUMBER, y NUMBER, m NUMBER); -- Create the partitioning package -- CREATE OR REPLACE PACKAGE SDO_ROUTER_PARTITION AUTHID current_user AS FUNCTION adjust_m(start_m IN NUMBER, end_m IN NUMBER, m IN NUMBER) RETURN NUMBER; FUNCTION get_pid(m IN NUMBER, pid IN NUMBER) RETURN NUMBER; FUNCTION min_eigenvector(sum_x2 IN NUMBER, sum_y2 IN NUMBER, sum_xy IN NUMBER) RETURN mdsys.vector_2d; FUNCTION node_inedges(node_id in NUMBER, edge_part_tab in VARCHAR2) RETURN mdsys.num_array; FUNCTION node_outedges(node_id IN NUMBER, edge_part_tab IN VARCHAR2) RETURN mdsys.num_array; PROCEDURE cleanup_router(all_tables IN BOOLEAN DEFAULT TRUE); PROCEDURE partition_router(log_file_name IN VARCHAR2 := 'sdo_router_partition.log', max_v_no IN NUMBER DEFAULT 10000, driving_side IN VARCHAR2 := 'R', network_name IN VARCHAR := 'NDM_US', cleanup IN BOOLEAN DEFAULT TRUE); PROCEDURE create_trucking_user_data( log_file_name IN VARCHAR2 := 'sdo_router_partition.log'); PROCEDURE dump_partitions(log_file_name IN VARCHAR2 := 'sdo_router_partition.log', start_pid IN NUMBER DEFAULT 0, end_pid IN NUMBER DEFAULT -1, verbose IN BOOLEAN DEFAULT FALSE); PROCEDURE validate_partitions(log_file_name IN VARCHAR2 := 'sdo_router_partition.log', start_pid IN NUMBER DEFAULT 0, end_pid IN NUMBER DEFAULT -1, verbose IN BOOLEAN DEFAULT FALSE); PROCEDURE get_version(log_file_name IN VARCHAR2 := 'sdo_router_partition.log'); PROCEDURE create_router_network(log_file_name IN VARCHAR2 := 'sdo_router_partition.log', network_name IN VARCHAR2 := 'NDM_US'); PROCEDURE delete_router_network(log_file_name IN VARCHAR2 := 'sdo_router_partition.log', network_name IN VARCHAR2 := 'NDM_US'); PROCEDURE elocation_partition_router (logfile_name in VARCHAR2) AS LANGUAGE java NAME 'oracle.spatial.router.partitioning.ElocationPartition.partition_router( java.lang.String)'; PROCEDURE elocation_trucking_user_data( logfile_name in VARCHAR2) AS LANGUAGE java NAME 'oracle.spatial.router.partitioning.ElocationPartition.create_trucking_user_data( java.lang.String)'; PROCEDURE elocation_dump_partition (logfile_name in VARCHAR2, start_pid in NUMBER, end_pid in NUMBER, verbose in BOOLEAN, is10g in BOOLEAN) AS LANGUAGE java NAME 'oracle.spatial.router.partitioning.ElocationPartition.dumpPartition( java.lang.String, int, int, boolean, boolean)'; PROCEDURE elocation_validate_partition (logfile_name in VARCHAR2, start_pid in NUMBER, end_pid in NUMBER, verbose in BOOLEAN, is10g in BOOLEAN) AS LANGUAGE java NAME 'oracle.spatial.router.partitioning.ElocationPartition.validatePartition( java.lang.String, int, int, boolean, boolean)'; PROCEDURE build_turn_restrictions (logdir in VARCHAR2, drivingside in VARCHAR2) AS LANGUAGE java NAME 'oracle.spatial.router.partitioning.TurnRestriction.buildTurnRestrictions(java.lang.String, java.lang.String)'; FUNCTION get_edge_info(edge_ids IN sdo_list_type, to_edge_ids OUT sdo_list_type, rets OUT mdsys.string_array, angle_segments OUT sdo_list_type) RETURN mdsys.string_array ; FUNCTION get_geometry_info(edge_ids IN sdo_list_type, merged_coords OUT sdo_list_type) RETURN NUMBER; END SDO_ROUTER_PARTITION; / --# CREATE OR REPLACE PACKAGE BODY SDO_ROUTER_PARTITION AS -- Partitioning log file part_log_file utl_file.file_type := NULL; JAVA_ERROR EXCEPTION; PARAMETER_ERROR EXCEPTION; -- -- log a message to the partitioning log file -- PROCEDURE log_message(message IN VARCHAR2, show_time IN BOOLEAN DEFAULT TRUE) IS BEGIN if ( utl_file.is_open(part_log_file) = FALSE ) then return; end if; IF ( show_time ) THEN utl_file.put_line (part_log_file, to_char(sysdate,'Dy fmMon DD HH24:MI:SS YYYY')); END IF; utl_file.put_line (part_log_file, message); utl_file.fflush(part_log_file); EXCEPTION WHEN OTHERS THEN raise_application_error(-20002, 'Error Writing Log File'); END log_message; -- -- check if the given index exists -- FUNCTION index_exists(index_name IN VARCHAR2) RETURN VARCHAR2 IS stmt VARCHAR2(256); no NUMBER := 0; BEGIN stmt := 'SELECT COUNT(*) FROM IND WHERE INDEX_NAME = :name'; EXECUTE IMMEDIATE stmt into no using UPPER(index_name); IF (no = 1) THEN RETURN 'TRUE'; ELSE RETURN 'FALSE'; END IF; END index_exists; -- -- check if the given table exists -- FUNCTION table_exists(tab_name IN VARCHAR2) RETURN VARCHAR2 IS stmt VARCHAR2(256); no NUMBER := 0; BEGIN stmt := 'SELECT COUNT(*) FROM TAB WHERE TNAME = :name'; EXECUTE IMMEDIATE stmt into no using UPPER(tab_name); IF (no = 1) THEN RETURN 'TRUE'; ELSE RETURN 'FALSE'; END IF; END table_exists; -- -- disk based graph partition functions/procedures -- based on moment of inertia appraoch -- FUNCTION min_eigenvector(sum_x2 IN NUMBER, sum_y2 IN NUMBER, sum_xy IN NUMBER) RETURN mdsys.vector_2d IS lamda NUMBER := 0; tmp_sum NUMBER := 0; k NUMBER := 0; eigenvector_1 NUMBER := 0; eigenvector_2 NUMBER := 0; BEGIN tmp_sum := sum_x2 + sum_y2; lamda := (tmp_sum - sqrt(tmp_sum*tmp_sum -4.0*(sum_x2*sum_y2-sum_xy*sum_xy)))/2; IF (sum_xy = 0) THEN IF (sum_x2 > sum_y2) THEN eigenvector_1 := 0; eigenvector_2 := 1.0; ELSE eigenvector_1 := 1.0; eigenvector_2 := 0; END IF; ELSE k := -sum_xy/(sum_x2-lamda); eigenvector_2 := 1.0/sqrt(k*k+1.0); eigenvector_1 := k*eigenvector_2; END IF; RETURN mdsys.vector_2d(eigenvector_1,eigenvector_2); END min_eigenvector; -- -- Return a negative value if the value falls in the given range (start_m,end_m) -- FUNCTION adjust_m(start_m IN NUMBER, end_m IN NUMBER, m IN NUMBER) RETURN NUMBER IS BEGIN IF (m >= start_m AND m <= end_m) THEN RETURN -m; ELSE RETURN m; END IF; END adjust_m; -- -- return the p_id based on the given m value (m > 0) ? pid : (pid+1) -- FUNCTION get_pid(m IN NUMBER, pid IN NUMBER) RETURN NUMBER IS BEGIN IF ( m < 0 ) THEN RETURN pid; ELSE RETURN pid+1; END IF; END get_pid; -- -- -- PROCEDURE adjust_final_pid(p_tab_name IN VARCHAR2) IS min_pid NUMBER; stmt VARCHAR2(256); BEGIN EXECUTE IMMEDIATE 'truncate table ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(p_tab_name); stmt := 'SELECT MIN(P_ID) FROM final_partition'; EXECUTE IMMEDIATE stmt into min_pid ; stmt := 'INSERT /*+ APPEND */ into ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(p_tab_name) || ' (vertex_id,p_id,x,y) ' || ' select vertex_id, (p_id-:min_pid+1),x,y from final_partition'; EXECUTE IMMEDIATE stmt USING min_pid; COMMIT; END adjust_final_pid; -- -- move vertices along the principal axes to make two partition with equal size -- PROCEDURE make_partition_equal(tab_name IN VARCHAR2, pid IN NUMBER, v_no IN NUMBER, part_counter IN NUMBER) IS no INTEGER; vno1 NUMBER; vno2 NUMBER; stmt VARCHAR2(256); part_m NUMBER; BEGIN part_m := 0; -- two partitions for partition pid is based on the m sign (<0 or >= 0 ) stmt := 'SELECT COUNT(*) FROM ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(tab_name) || ' WHERE m < 0 '; EXECUTE IMMEDIATE stmt into vno1; vno2 := v_no - vno1; IF (vno1 > vno2) THEN -- move n vertices from set (m < 0) to set (m>= 0) -- by flipping its sign of m no := (vno1-vno2)/2; stmt := 'SELECT min(m) FROM (SELECT m FROM ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(tab_name) || ' WHERE m < 0 ORDER BY m DESC) ' || ' WHERE rownum <= :no '; EXECUTE IMMEDIATE stmt into part_m USING no; INSERT /*+ APPEND */ INTO partition_tmp_3 (vertex_id,p_id,x,y,m) SELECT vertex_id, mdsys.SDO_ROUTER_PARTITION.get_pid( mdsys.SDO_ROUTER_PARTITION.adjust_m(part_m,0,m),part_counter), x,y,mdsys.SDO_ROUTER_PARTITION.adjust_m(part_m,0,m) FROM partition_tmp_2; ELSE -- move n vertices from set (m >= 0) to set ( m < 0) by -- updating pid in vertex table no := (vno2-vno1)/2; stmt := 'SELECT max(m) FROM (SELECT m FROM ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(tab_name) || ' WHERE m >= 0 ORDER BY m) ' || ' WHERE rownum <= :no ' ; EXECUTE IMMEDIATE stmt into part_m USING no; INSERT /*+ APPEND */ INTO partition_tmp_3 (vertex_id,p_id,x,y,m) SELECT vertex_id, mdsys.SDO_ROUTER_PARTITION.get_pid( mdsys.SDO_ROUTER_PARTITION.adjust_m(0,part_m,m),part_counter), x,y,mdsys.SDO_ROUTER_PARTITION.adjust_m(0,part_m,m) FROM partition_tmp_2; END IF; COMMIT; END make_partition_equal; -- -- partition procedure -- PROCEDURE new_partition_proc(p_tab_name IN VARCHAR2, max_v_no IN NUMBER, partition_id IN NUMBER, make_equal IN BOOLEAN, part_counter IN OUT NUMBER) IS m_mean NUMBER; v_no NUMBER; x_mean NUMBER; y_mean NUMBER; eigenvec vector_2d; partition_tmp_1 VARCHAR2(256); stmt VARCHAR2(256); table_name VARCHAR2(256); BEGIN -- -- terminal condition for bisecting -- if vertex no. smaller than max_v_no ,stops IF (partition_id = 0) THEN partition_tmp_1 := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(p_tab_name); ELSE partition_tmp_1 := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(p_tab_name) || '_' || partition_id; END IF; stmt := 'SELECT COUNT(*) FROM ' || partition_tmp_1; EXECUTE IMMEDIATE stmt into v_no; IF (v_no = 0) THEN RETURN; END IF; -- -- prepare data for eigenvalue/eigenvector calculation -- stmt := 'SELECT AVG(x), AVG(y) from ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(partition_tmp_1); EXECUTE IMMEDIATE stmt into x_mean, y_mean; stmt := 'SELECT mdsys.SDO_ROUTER_PARTITION.min_eigenvector( sum(power(x-:1,2)), sum(power(y-:2,2)), sum((x-:3)*(y-:4))) FROM ' || partition_tmp_1; EXECUTE IMMEDIATE stmt INTO eigenvec USING x_mean, y_mean, x_mean, y_mean; stmt := 'SELECT AVG(:1*(x - :2) + :3*(y-:4)) FROM ' || partition_tmp_1; EXECUTE IMMEDIATE stmt INTO m_mean USING -eigenvec.y, x_mean, eigenvec.x, y_mean; stmt := 'INSERT /*+ APPEND */ into partition_tmp_2 (vertex_id,p_id,x,y,m) SELECT vertex_id,p_id,x,y, (:1*(x - :2) + :3*(y-:4) - :5) FROM ' || partition_tmp_1; EXECUTE IMMEDIATE stmt USING -eigenvec.y, x_mean, eigenvec.x, y_mean, m_mean; COMMIT; -- -- make equal size if required -- IF (make_equal) THEN make_partition_equal('partition_tmp_2',partition_id,v_no,part_counter); ELSE INSERT /*+ APPEND */ INTO partition_tmp_3 (vertex_id,p_id,x,y,m) SELECT vertex_id, mdsys.SDO_ROUTER_PARTITION.get_pid(m, part_counter), x,y,m FROM partition_tmp_2; COMMIT; END IF; IF (partition_id = 0) THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(p_tab_name); ELSE table_name := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(p_tab_name) || '_' || partition_id; EXECUTE IMMEDIATE 'DROP TABLE ' || table_name; END IF; IF (v_no/2 > max_v_no) THEN table_name := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(p_tab_name) || '_' || part_counter; IF ( table_exists(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(table_name)) = 'TRUE') THEN execute immediate 'DROP TABLE ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(table_name); END IF; stmt := 'CREATE TABLE ' || table_name || ' STORAGE (maxextents unlimited), NOLOGGING as SELECT * FROM partition_tmp_3 WHERE p_id=' || part_counter; EXECUTE IMMEDIATE stmt; table_name := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(p_tab_name) || '_' || to_char(part_counter+1); IF ( table_exists(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(table_name)) = 'TRUE') THEN execute immediate 'DROP TABLE ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(table_name); END IF; stmt := 'CREATE TABLE ' || table_name || ' STORAGE (maxextents unlimited), NOLOGGING as SELECT * FROM partition_tmp_3 WHERE p_id=' || to_char(part_counter+1); EXECUTE IMMEDIATE stmt; COMMIT; ELSE INSERT /*+ APPEND */ INTO final_partition (vertex_id,p_id,x,y) SELECT vertex_id,p_id,x,y FROM partition_tmp_3; COMMIT; END IF; part_counter := part_counter+2; EXECUTE IMMEDIATE 'TRUNCATE TABLE partition_tmp_2'; EXECUTE IMMEDIATE 'TRUNCATE TABLE partition_tmp_3'; END new_partition_proc; -- -- main pl/sql procedure to partition a graph with coordinate information -- PROCEDURE graph_partition(p_tab_name IN VARCHAR2, max_v_no IN NUMBER, make_equal IN BOOLEAN) IS p_level INTEGER; stmt VARCHAR2(256); v_no NUMBER; min_pid NUMBER; max_pid NUMBER; pid NUMBER; p_counter NUMBER; p_date date; show_time BOOLEAN := FALSE; BEGIN IF (table_exists(p_tab_name) = 'FALSE' ) THEN log_message('ERROR: ' || p_tab_name || ' table not found'); RETURN ; END IF; stmt := 'SELECT MIN(p_id), MAX(p_id) FROM ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(p_tab_name); EXECUTE IMMEDIATE stmt INTO min_pid, max_pid; stmt := 'SELECT COUNT(*) FROM ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(p_tab_name) || ' WHERE p_id = :min_pid'; EXECUTE IMMEDIATE stmt INTO v_no USING min_pid; p_level := floor(LN(v_no/max_v_no)/LN(2.0)); -- issue warning if the no of nodes in the table is smaller than max_v_no if ( p_level < 0 ) THEN log_message('WARNING: no. of nodes: ' || v_no || ' in table: ' || p_tab_name || ' is smaller than the given max_v_no: ' || max_v_no); RETURN; end if; p_counter := max_pid+1; -- starting partition counter pid := min_pid; log_message('INFO: begin partitioning of '|| p_tab_name || ' partition level: ' || p_level || ' min(partition id): ' || min_pid || ' max(partition id): ' || max_pid); log_message('INFO: generating ' || power(2,p_level+1) || ' partitions from level:0 to level: ' || p_level ||' ...', show_time); FOR k IN min_pid..max_pid LOOP FOR i IN 0..p_level LOOP p_date := sysdate; FOR j IN 1..power(2,i) LOOP new_partition_proc(SYS.DBMS_ASSERT.NOOP(p_tab_name), max_v_no, pid, make_equal, p_counter); pid := pid +1; END LOOP; log_message('INFO: partitioning '|| p_tab_name || ' level: ' || i || ' partition id: ' || pid); -- add computation time form each level log_message('INFO: partitioning level: ' || i || ' with ' || power(2,i+1) || ' partitions '|| ' took ' || to_char((sysdate-p_date)*24*60,'99999.999') || ' min.',show_time) ; END LOOP; END LOOP; -- Copy the result back to original table and ajust the pids adjust_final_pid(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(p_tab_name)); log_message('INFO: completed partitioning of '|| p_tab_name); EXCEPTION WHEN OTHERS THEN log_message('Exception processing partition '|| pid || ' of the ' || p_tab_name || ' table'); log_message(SQLERRM); raise_application_error(-20009, 'Error Graphing Partitions'); END graph_partition; -- -- drop all temporary tables for partitioning -- PROCEDURE clean_tables (cleanup IN BOOLEAN DEFAULT TRUE) IS BEGIN log_message('INFO: cleaning up partitioning temporary tables'); -- Tables that should always be cleaned up -- IF (table_exists('final_partition') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE final_partition'; END IF; IF (table_exists('partition_tmp_2') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE partition_tmp_2'; END IF; IF (table_exists('partition_tmp_3') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE partition_tmp_3'; END IF; -- Tables we may want to keep for debugging purposes. -- IF (cleanup) THEN IF (table_exists('edge_part') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE edge_part'; END IF; IF (table_exists('node_part') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE node_part'; END IF; IF (table_exists('restricted_nodes')= 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE restricted_nodes'; END IF; IF (table_exists('restricted_edges')= 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE restricted_edges'; END IF; IF (table_exists('super_edge_ids') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE super_edge_ids'; END IF; IF (table_exists('super_node_ids') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE super_node_ids'; END IF; IF (table_exists('router_partitioned_truck_data') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE router_partitioned_truck_data'; END IF; END IF; END; -- -- setup all temporary tables for partitioning -- PROCEDURE setup_tables IS BEGIN log_message('INFO: setting up partitioning temporary tables'); IF (table_exists('partition_tmp_2') = 'TRUE') THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE partition_tmp_2'; ELSE EXECUTE IMMEDIATE 'CREATE TABLE partition_tmp_2 (vertex_id NUMBER, p_id NUMBER, x NUMBER, y NUMBER,m NUMBER) STORAGE (maxextents unlimited), NOLOGGING'; END IF; IF (table_exists('partition_tmp_3') = 'TRUE') THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE partition_tmp_3'; ELSE EXECUTE IMMEDIATE 'CREATE TABLE partition_tmp_3 (vertex_id NUMBER, p_id NUMBER, x NUMBER, y NUMBER,m NUMBER) STORAGE (maxextents unlimited), NOLOGGING'; END IF; IF (table_exists('final_partition')= 'TRUE') THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE final_partition'; ELSE EXECUTE IMMEDIATE 'CREATE TABLE final_partition (vertex_id NUMBER, p_id NUMBER, x NUMBER, y NUMBER) STORAGE (maxextents unlimited), NOLOGGING'; END IF; IF (table_exists('edge_part') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE edge_part'; END IF; IF (table_exists('node_part') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE node_part'; END IF; IF (table_exists('restricted_nodes')= 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE restricted_nodes'; END IF; IF (table_exists('restricted_edges')= 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE restricted_edges'; END IF; IF (table_exists('super_edge_ids') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE super_edge_ids'; END IF; IF (table_exists('super_node_ids') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE super_node_ids'; END IF; END setup_tables; -- -- Create the node_part table needed for partitioning -- PROCEDURE create_node_part IS BEGIN IF (table_exists('node_part') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE node_part'; END IF; log_message('INFO: create and load node_part table'); EXECUTE IMMEDIATE 'CREATE TABLE node_part(vertex_id NUMBER, x NUMBER, y NUMBER, p_id NUMBER) STORAGE (maxextents unlimited), NOLOGGING'; EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ into node_part SELECT n.node_id, n.geometry.sdo_point.x, n.geometry.sdo_point.y, 0 FROM node n'; COMMIT; END create_node_part; -- -- Create and load the restricted_edges table. -- PROCEDURE create_restricted_edges IS TYPE CURSOR_TYPE IS REF CURSOR; coords MDSYS.SDO_ORDINATE_ARRAY; divider VARCHAR2(1); edge_id NUMBER; ins_stmt VARCHAR(256); p_cursor CURSOR_TYPE; BEGIN IF (table_exists('restricted_edges') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE restricted_edges'; END IF; log_message('INFO: Create and load the restricted_edges table'); EXECUTE IMMEDIATE 'CREATE TABLE restricted_edges(edge_id NUMBER, divider VARCHAR2(1), startx1 NUMBER, starty1 NUMBER, startx2 NUMBER, starty2 NUMBER, endx1 NUMBER, endy1 NUMBER, endx2 NUMBER, endy2 NUMBER) STORAGE (maxextents unlimited), NOLOGGING'; ins_stmt := 'INSERT INTO restricted_edges VALUES ' || '(:eid, :div, :sx1, :sy1, :sx2, :sy2,:ex1, :ey1, :ex2, :ey2)'; -- Find all edges attached to nodes that have restricted edges either inbound -- or outbound from the node. OPEN p_cursor FOR 'SELECT t.edge_id, t.divider, t.geometry.sdo_ordinates' || ' FROM edge t WHERE t.edge_id in ' || '(SELECT edge_id FROM EDGE WHERE start_node_id IN (SELECT node_id FROM restricted_nodes) UNION' || ' SELECT edge_id FROM EDGE WHERE end_node_id IN (SELECT node_id FROM restricted_nodes))'; LOOP FETCH p_cursor INTO edge_id, divider, coords; EXIT WHEN p_cursor%NOTFOUND; -- Find and store the edges first and last segments EXECUTE IMMEDIATE ins_stmt USING edge_id, divider, coords(1), coords(2), coords(3), coords(4), coords(coords.count-3), coords(coords.count-2), coords(coords.count-1), coords(coords.count); END LOOP; CLOSE p_cursor; COMMIT; EXECUTE IMMEDIATE 'CREATE INDEX restricted_edges_idx on restricted_edges(edge_id)'; END create_restricted_edges; -- -- Create the restricted_nodes table needed for turn restriction generation -- PROCEDURE create_restricted_nodes IS BEGIN IF (table_exists('restricted_nodes') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE restricted_nodes'; END IF; log_message('INFO: Create and load the restricted_nodes table'); EXECUTE IMMEDIATE 'CREATE TABLE restricted_nodes(node_id NUMBER, inedges mdsys.num_array, outedges mdsys.num_array) STORAGE (maxextents unlimited), NOLOGGING'; -- Find all nodes that are the start or end point of a restricted edge. EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO restricted_nodes SELECT vertex_id, inedges, outedges FROM NODE_PART where vertex_id IN (SELECT source_id FROM edge_part WHERE divider IN (''1'', ''A'') union SELECT target_id FROM edge_part WHERE divider IN (''2'', ''A''))'; EXECUTE IMMEDIATE 'ALTER TABLE restricted_nodes PARALLEL (DEGREE 4)'; COMMIT; END create_restricted_nodes; -- -- find the inedges for a node in the node_part table -- FUNCTION node_inedges(node_id in NUMBER, edge_part_tab in VARCHAR2) RETURN mdsys.num_array IS TYPE CURSOR_TYPE IS REF CURSOR; e_cursor CURSOR_TYPE; e_id NUMBER; e_array mdsys.num_array; BEGIN e_array := mdsys.num_array(); OPEN e_cursor for 'select edge_id from ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(edge_part_tab) || ' where target_id = :id ' using node_id; LOOP FETCH e_cursor INTO e_id; EXIT WHEN e_cursor%NOTFOUND; e_array.extend(1); e_array(e_array.count) := e_id; END LOOP; CLOSE e_cursor; RETURN e_array; END node_inedges; -- -- find the outedges for a node in the node_part table -- FUNCTION node_outedges(node_id IN NUMBER, edge_part_tab IN VARCHAR2) RETURN mdsys.num_array IS TYPE CURSOR_TYPE IS REF CURSOR; e_cursor CURSOR_TYPE; e_id NUMBER; e_array mdsys.num_array; BEGIN e_array := mdsys.num_array(); OPEN e_cursor FOR 'SELECT edge_id FROM ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(edge_part_tab) || ' WHERE source_id = :id ORDER BY edge_id' using node_id; LOOP FETCH e_cursor INTO e_id; EXIT WHEN e_cursor%NOTFOUND; e_array.extend(1); e_array(e_array.count) := e_id; END LOOP; CLOSE e_cursor; RETURN e_array; END node_outedges; -- -- Create the node_part table needed for partitioning -- PROCEDURE create_super_tables IS stmt VARCHAR2(256); BEGIN -- Create an index on the node_part table -- log_message('INFO: create index np_v_idx on node_part'); EXECUTE IMMEDIATE 'CREATE INDEX np_v_idx on node_part(vertex_id)'; -- Rename the node table so we can use direct insert which is -- faster than updating -- IF (index_exists('node_id_index') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP INDEX node_id_index'; END IF; IF (index_exists('node_partition_index') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP INDEX node_partition_index'; END IF; EXECUTE IMMEDIATE 'RENAME node to node_tmp'; -- Create and populate the edge_part table. -- IF (table_exists('edge_part') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE edge_part'; END IF; log_message('INFO: create and load edge_part'); EXECUTE IMMEDIATE 'CREATE TABLE edge_part(edge_id NUMBER, source_id NUMBER, target_id NUMBER, source_p_id NUMBER, target_p_id NUMBER, func_class NUMBER, length NUMBER, speed_limit NUMBER, divider VARCHAR2(1), turn_restrictions mdsys.num_array) STORAGE (maxextents unlimited), NOLOGGING'; EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO edge_part SELECT edge_id, start_node_id, end_node_id, (SELECT p_id FROM node_part WHERE vertex_id = start_node_id), (SELECT p_id FROM node_part WHERE vertex_id = end_node_id), func_class, length, speed_limit, divider, null FROM edge'; COMMIT; -- Create useful indices on edge_part. -- log_message('INFO: create index edge_part_e_idx on edge_part'); EXECUTE IMMEDIATE 'CREATE INDEX edge_part_e_idx on edge_part(edge_id)'; log_message('INFO: create index edge_part_s_idx on edge_part'); EXECUTE IMMEDIATE 'CREATE INDEX edge_part_s_idx on edge_part(source_id)'; log_message('INFO: create index edge_part_t_idx on edge_part'); EXECUTE IMMEDIATE 'CREATE INDEX edge_part_t_idx on edge_part(target_id)'; log_message('INFO: create index edge_part_st_p_idx on edge_part'); EXECUTE IMMEDIATE 'CREATE INDEX edge_part_st_p_idx on edge_part(source_p_id, target_p_id)'; -- Add and populate the inedges and outedges fields to the node_part table. -- log_message('INFO: create and load outedge and inedge columns in node_part table'); EXECUTE IMMEDIATE 'ALTER TABLE node_part add (outedges mdsys.num_array)'; EXECUTE IMMEDIATE 'ALTER TABLE node_part add (inedges mdsys.num_array)'; stmt := 'UPDATE /*+ PARALLEL (node_part) */ node_part SET outedges = mdsys.sdo_router_partition.node_outedges(vertex_id, :e_part), inedges = mdsys.sdo_router_partition.node_inedges(vertex_id, :e_part)'; EXECUTE IMMEDIATE stmt USING 'edge_part', 'edge_part'; COMMIT; -- Add an partition id index to the node_part table. -- log_message('INFO: create index node_part_p_idx on node_part'); EXECUTE IMMEDIATE 'CREATE INDEX node_part_p_idx on node_part(p_id)'; -- Recreate the node table and load it from the node_tmp table. -- log_message('INFO: recreating node table with partitioning information'); EXECUTE IMMEDIATE 'CREATE TABLE node storage (maxextents unlimited), nologging AS SELECT * FROM node_tmp WHERE rownum < 0'; EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ into node SELECT node_id, geometry, (SELECT p_id FROM node_part WHERE vertex_id = node_id) FROM node_tmp'; COMMIT; EXECUTE IMMEDIATE 'DROP TABLE node_tmp'; -- Create an index on the node and partition id fields in the NODE table. -- log_message('INFO: create index node_id_index on node'); EXECUTE IMMEDIATE 'CREATE INDEX node_id_index on node(node_id)'; log_message('INFO: create index node_partition_index on node'); EXECUTE IMMEDIATE 'CREATE INDEX node_partition_index on node(partition_id)'; -- Set the partition ids in the edge table. -- EXECUTE IMMEDIATE 'ALTER TABLE edge nologging'; IF (index_exists('edge_partition_index') = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP INDEX edge_partition_index'; END IF; log_message('INFO: updating edge table with partitioning information'); EXECUTE IMMEDIATE 'UPDATE /*+ PARALLEL (edge) */ edge SET partition_id = (SELECT p_id from node_part WHERE vertex_id = start_node_id)'; COMMIT; -- Create an index on the partition id field in the EDGE table. -- log_message('INFO: create index edge_partition_index on edge'); EXECUTE IMMEDIATE 'CREATE INDEX edge_partition_index on edge(partition_id)'; -- Create and populate the super_node and super_edge tables. -- log_message('INFO: creating and loading super_node_ids table'); IF (table_exists('super_node_ids') = 'TRUE') THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE super_node_ids'; ELSE EXECUTE IMMEDIATE 'CREATE TABLE super_node_ids (node_id number)'; END IF; EXECUTE IMMEDIATE 'INSERT /*+APPEND */ into super_node_ids (SELECT source_id FROM edge_part WHERE func_class = 1 or func_class=2 UNION SELECT target_id FROM edge_part WHERE func_class = 1 or func_class=2)'; log_message('INFO: creating and loading super_edge_ids table'); IF (table_exists('super_edge_ids') = 'TRUE') THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE super_edge_ids'; ELSE EXECUTE IMMEDIATE 'CREATE TABLE super_edge_ids (edge_id number)'; END IF; EXECUTE IMMEDIATE 'INSERT /*+APPEND */ into super_edge_ids SELECT edge_id FROM edge_part WHERE func_class =1 or func_class = 2'; COMMIT; END create_super_tables; --- --- --- FUNCTION get_edge_info(edge_ids IN sdo_list_type, to_edge_ids OUT sdo_list_type, rets OUT mdsys.string_array, angle_segments OUT sdo_list_type) RETURN mdsys.string_array AS n INTEGER; i INTEGER; k INTEGER; base INTEGER := 1; to_edge_id INTEGER; coords mdsys.sdo_ordinate_array; names mdsys.string_array; name_query VARCHAR2(2000); sign_query VARCHAR2(2000); ret VARCHAR2(200); TYPE cursor_type IS REF CURSOR; sign_cursor cursor_type; BEGIN IF (edge_ids IS NULL) THEN RETURN NULL; END IF; -- Initialize varrays n := edge_ids.count; names := mdsys.string_array(); to_edge_ids := sdo_list_type(); rets := mdsys.string_array(); angle_segments := sdo_list_type(); names.extend(n); to_edge_ids.extend(n); rets.extend(n); -- Need 4 points to describe a start and end segment for each edge id angle_segments.extend(n*8); -- Initialize name query name_query := 'SELECT t.name,t.geometry.sdo_ordinates FROM edge t WHERE t.edge_id = :1'; -- Initialize sign query sign_query := 'SELECT to_edge_id, ' || 'ramp || '':'' || exit || '':'' || toward '|| 'FROM sign_post ' || 'WHERE from_edge_id = :1'; -- Iterate through route edge_ids and find info for each. FOR i IN 1..n LOOP EXECUTE IMMEDIATE name_query INTO names(i), coords USING edge_ids(i); IF (names(i) IS NULL) THEN names(i) := 'RAMP'; END IF; -- Get sign information, if any. to_edge_ids(i) := 0; rets(i) := NULL; -- Get the coordinates for the start and end segments of the edge angle_segments(base) := coords(1); angle_segments(base+1) := coords(2); angle_segments(base+2) := coords(3); angle_segments(base+3) := coords(4); angle_segments(base+4) := coords(coords.count-3); angle_segments(base+5) := coords(coords.count-2); angle_segments(base+6) := coords(coords.count-1); angle_segments(base+7) := coords(coords.count); base := base + 8; -- We have to use ABS(edge_ids(i)) since sign_post -- table contains only NAVSTREETS edge ids -- (positive only) not routeserver edge ids (which -- can be negative). OPEN sign_cursor FOR sign_query USING ABS(edge_ids(i)); LOOP FETCH sign_cursor INTO to_edge_id, ret; EXIT WHEN sign_cursor%NOTFOUND; FOR k IN i+1..n LOOP IF (to_edge_id = edge_ids(k) OR (-1*to_edge_id) = edge_ids(k)) THEN -- Make sure we assign router edge id: -- (negative or positive)!!! to_edge_ids(i) := edge_ids(k); rets(i) := ret; EXIT; END IF; END LOOP; IF (to_edge_ids(i) <> 0) THEN EXIT; END IF; END LOOP; CLOSE sign_cursor; END LOOP; RETURN names; END get_edge_info; --- --- --- FUNCTION get_geometry_info (edge_ids IN sdo_list_type, merged_coords OUT sdo_list_type) RETURN NUMBER AS coords MDSYS.SDO_ORDINATE_ARRAY; j NUMBER; k NUMBER; BEGIN IF (edge_ids IS NULL) THEN RETURN 0; END IF; k := 1; merged_coords := sdo_list_type(); -- For each input edge id, get the list of coordinates for the edge and -- build a list of all coordinates for the edges. FOR i in 1 .. edge_ids.count LOOP EXECUTE IMMEDIATE 'select t.geometry.sdo_ordinates from edge t ' || 'where edge_id=:i' INTO coords USING edge_ids(i); j := 1; merged_coords.extend(coords.count + 1); merged_coords(k) := coords.count; k := k + 1; WHILE j <= coords.count LOOP merged_coords(k) := coords(j); merged_coords(k+1) := coords(j+1); j := j + 2; k := k + 2; END LOOP; END LOOP; RETURN merged_coords.count; END get_geometry_info; --- --- Find and validate the Routeservers data version --- FUNCTION get_version_info RETURN VARCHAR2 IS data_version VARCHAR2(32) := '10.2.0.4.0'; major_version VARCHAR2(16); stmt VARCHAR2(256); v_count NUMBER; BEGIN IF (table_exists('SDO_ROUTER_DATA_VERSION')= 'TRUE') THEN stmt := 'SELECT COUNT(*) FROM SDO_ROUTER_DATA_VERSION'; EXECUTE IMMEDIATE stmt INTO v_count; IF (v_count != 1) THEN log_message('ERROR: Routeserver data version table corrupted, ' || 'multiple versions found'); raise_application_error(-20005, 'Error getting data version, multiple versions found'); END IF; stmt := 'SELECT data_version FROM SDO_ROUTER_DATA_VERSION'; EXECUTE IMMEDIATE stmt INTO data_version; major_version := SUBSTR(data_version, 0, INSTR(data_version, '.')-1); IF (INSTR(data_version, '.', 1, 4) = 0 OR (major_version!='10' AND major_version!='11')) THEN log_message('ERROR: Routeserver data version table corrupted, ' || 'unsupported data version ' || data_version); raise_application_error(-20005, 'Error getting data version, unsupported data version ' || data_version); END IF; END IF; RETURN data_version; END get_version_info; --- --- --- FUNCTION is_10g(version IN VARCHAR2) RETURN BOOLEAN IS BEGIN RETURN (SUBSTR(version, 0, INSTR(version, '.')-1) = '10'); END; --- --- --- FUNCTION open_log_file(log_file_name IN VARCHAR2) RETURN VARCHAR2 AS full_file_name VARCHAR2(256); stmt VARCHAR2(256); BEGIN BEGIN -- Open the routers partition log file part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A'); stmt := 'SELECT directory_path from all_directories where directory_name=''SDO_ROUTER_LOG_DIR'''; EXECUTE IMMEDIATE stmt into full_file_name; full_file_name := full_file_name || '/' || log_file_name; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001, 'Error Opening Log File'); END; RETURN full_file_name; END open_log_file; -- -- Entry point to used to create a network on the Router data -- PROCEDURE create_router_network(log_file_name IN VARCHAR2 := 'sdo_router_partition.log', network_name IN VARCHAR2 := 'NDM_US') IS full_file_name VARCHAR2(256); l_network_name VARCHAR2(256); stmt VARCHAR2(512); BEGIN full_file_name := open_log_file(log_file_name); l_network_name := UPPER(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(network_name)); log_message('INFO: creating the Routeserver network: ' || l_network_name); -- cleanup metadata stmt := 'delete from USER_SDO_NETWORK_METADATA where NETWORK = :name'; EXECUTE IMMEDIATE stmt using l_network_name; stmt := 'delete from USER_SDO_NETWORK_USER_DATA where NETWORK = :name'; EXECUTE IMMEDIATE stmt using l_network_name; log_message(' creating indexes', FALSE); -- create index for edge func_class IF (index_exists('EDGE_FUNC_CLASS_IDX') = 'TRUE') THEN EXECUTE IMMEDIATE 'drop index EDGE_FUNC_CLASS_IDX'; END IF; EXECUTE IMMEDIATE 'create index EDGE_FUNC_CLASS_IDX on EDGE (FUNC_CLASS)'; -- create a function based index for link level IF (index_exists('EDGE_LEVEL_IDX') = 'TRUE') THEN EXECUTE IMMEDIATE 'drop index EDGE_LEVEL_IDX'; END IF; EXECUTE IMMEDIATE 'create index EDGE_LEVEL_IDX on EDGE (floor((8-FUNC_CLASS)/3))'; log_message(' creating views', FALSE); -- create a view on the NODE table EXECUTE IMMEDIATE 'create or replace view NDM_US_NODE$ as select n.node_id node_id, n.geometry geometry, n.geometry.sdo_point.x x, n.geometry.sdo_point.y y from NODE n'; -- create a view on the EDGE table EXECUTE IMMEDIATE 'create or replace view NDM_US_LINK$ as select edge_id link_id, start_node_id start_node_id, end_node_id end_node_id, floor((8-FUNC_CLASS)/3) link_level, length length, speed_limit s, func_class f, geometry geometry, name name, divider divider from EDGE'; --create a view on the NODE table node_id and partition_id information EXECUTE IMMEDIATE 'create or replace view NDM_US_PART$ as select node_id node_id, partition_id partition_id, 1 link_level from NODE'; -- create a view on the PARTITION table adding link level and changing -- the format of the edge counts EXECUTE IMMEDIATE 'create or replace view NDM_US_PBLOB$ as select link_level link_level, a.partition_id partition_id, subnetwork blob, num_nodes num_inodes, num_outgoing_boundary_edges+num_incoming_boundary_edges num_enodes, num_non_boundary_edges num_ilinks, num_outgoing_boundary_edges+num_incoming_boundary_edges num_elinks, num_incoming_boundary_edges num_inlinks, num_outgoing_boundary_edges num_outlinks, ' || SYS.DBMS_ASSERT.ENQUOTE_LITERAL('Y') || ' user_data_included from (select 1 link_level, partition_id partition_id from PARTITION where partition_id > 0 union select 2 link_level, partition_id partition_id from PARTITION where partition_id = 0) a, PARTITION b where a.partition_id = b.partition_id'; log_message(' generating metadata', FALSE); --insert network metadata stmt := 'insert into USER_SDO_NETWORK_METADATA (network, network_category, geometry_type, node_table_name, node_geom_column, link_table_name, link_geom_column, link_cost_column, link_direction, partition_table_name, partition_blob_table_name, user_defined_data) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12)'; EXECUTE IMMEDIATE stmt USING l_network_name, 'SPATIAL', 'SDO_GEOMETRY', 'NDM_US_NODE$', 'GEOMETRY', 'NDM_US_LINK$', 'GEOMETRY', 'LENGTH', 'DIRECTED', 'NDM_US_PART$', 'NDM_US_PBLOB$', 'Y'; -- insert user data metadata -- node x coordinate stmt := 'insert into user_sdo_network_user_data (network, table_type, data_name,data_type) values (:1, :2, :3, :4)'; EXECUTE IMMEDIATE stmt USING l_network_name, 'NODE', 'X', 'NUMBER'; -- node y coordinate stmt := 'insert into user_sdo_network_user_data (network, table_type, data_name, data_type) values (:1, :2, :3, :4)'; EXECUTE IMMEDIATE stmt USING l_network_name, 'NODE', 'Y', 'NUMBER'; -- link speed limit stmt := 'insert into user_sdo_network_user_data (network, table_type, data_name, data_type) values (:1, :2, :3, :4)'; EXECUTE IMMEDIATE stmt USING l_network_name, 'LINK', 'S', 'NUMBER'; -- link function class stmt := 'insert into user_sdo_network_user_data (network, table_type, data_name, data_type) values (:1, :2, :3, :4)'; EXECUTE IMMEDIATE stmt USING l_network_name, 'LINK', 'F', 'NUMBER'; utl_file.fclose(part_log_file); END; -- -- Entry point to used to create a network on the Router data -- PROCEDURE delete_router_network(log_file_name IN VARCHAR2 := 'sdo_router_partition.log', network_name IN VARCHAR2 := 'NDM_US') IS full_file_name VARCHAR2(256); l_network_name VARCHAR2(256); stmt VARCHAR2(512); BEGIN full_file_name := open_log_file(log_file_name); l_network_name := UPPER(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(network_name)); log_message('INFO: deleting the Routeserver network: ' || l_network_name); -- cleanup metadata stmt := 'delete from USER_SDO_NETWORK_METADATA where NETWORK = :name'; EXECUTE IMMEDIATE stmt using l_network_name; stmt := 'delete from USER_SDO_NETWORK_USER_DATA where NETWORK = :name'; EXECUTE IMMEDIATE stmt using l_network_name; utl_file.fclose(part_log_file); END; -- -- Entry point and driver of the entire partitioning process. -- high level procedure for partitioning graph based on coordinate -- information (inertia bisecting). The parameters are defaulted -- so the customers don't have to worry about them. -- PROCEDURE partition_router(log_file_name IN VARCHAR2 := 'sdo_router_partition.log', max_v_no IN NUMBER DEFAULT 10000, driving_side IN VARCHAR2 := 'R', network_name IN VARCHAR := 'NDM_US', cleanup IN BOOLEAN DEFAULT TRUE) IS full_file_name VARCHAR2(256); stmt VARCHAR2(256); msg_cleanup VARCHAR2(10) := 'TRUE'; BEGIN IF (NOT cleanup) THEN msg_cleanup := 'FALSE'; END IF; full_file_name := open_log_file(log_file_name); log_message('******** Beginning SDO Router partitioning'); log_message('** Logfile location: ' || full_file_name, FALSE); log_message('** Nodes per partition: ' || max_v_no, FALSE); log_message('** Driving side: ' || driving_side, FALSE); log_message('** Router network name: ' || network_name, FALSE); log_message('** Cleanup temporary files: ' || msg_cleanup, FALSE); setup_tables; create_node_part; graph_partition('NODE_PART', max_v_no, TRUE); create_super_tables; -- Table of nodes that are either the start or end node of a restricted edge. create_restricted_nodes; -- Table of edges that that have either a start or end node in the -- restricted_nodes table. We store the first and last segment of each edge. -- These segments are far more accurate than the edge as a whole for -- computing turn angles. create_restricted_edges; -- Close the log file so the Java code can use it utl_file.fclose(part_log_file); -- Adjust the Oracle JVM maximum memory size to 800M. -- This is the equivelent to specifying -Xmx800m to -- java outside the database. Memory size is specified in bytes. ElocationSetJVMHeapSize(838860800); -- Java code to generate turn restrictions. This MUST be done after the -- NODE_PART and EDGE_PART tables have been populated but before we actually -- create the partition table. build_turn_restrictions(full_file_name, driving_side); BEGIN -- Java code to create the partiton table elocation_partition_router(full_file_name); EXCEPTION WHEN OTHERS THEN part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A'); log_message(SQLERRM); utl_file.fclose(part_log_file); RAISE JAVA_ERROR; END; -- Reopen the logfile full_file_name := open_log_file(log_file_name); log_message('INFO: creating the final partition table'); -- Rename the new partition table and build an index on it. IF (table_exists('partition')= 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE partition'; END IF; EXECUTE IMMEDIATE 'RENAME new_partition to partition'; log_message('INFO: create index partition_p_idx on partition table'); EXECUTE IMMEDIATE 'CREATE INDEX partition_p_idx on partition(partition_id)'; -- Create the indexes, views and metadata needed by the NDM on top of -- the Router data create_router_network(log_file_name, network_name); -- If trucking user data exists, partition it. IF (table_exists('router_transport') = 'TRUE') THEN create_trucking_user_data(log_file_name); END IF; clean_tables(cleanup); log_message('******** Completed SDO Router partitioning'); -- Close the log file utl_file.fclose(part_log_file); EXCEPTION WHEN JAVA_ERROR THEN raise_application_error(-20000, 'Oracle Router partitioning failed'); WHEN OTHERS THEN IF (utl_file.is_open(part_log_file) = FALSE) THEN part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A'); END IF; log_message(SQLERRM); utl_file.fclose(part_log_file); raise_application_error(-20000, 'Oracle Router partitioning failed'); END partition_router; --- --- Entry point to partition trucking data --- PROCEDURE create_trucking_user_data(log_file_name IN VARCHAR2 := 'sdo_router_partition.log') IS TYPE CURSOR_TYPE IS REF CURSOR; full_file_name VARCHAR2(256); ins_stmt VARCHAR2(256); stmt VARCHAR2(256); p_cursor CURSOR_TYPE; edge_id NUMBER; partition_id NUMBER; func_class NUMBER; l_maintype NUMBER; l_subtype NUMBER; l_value NUMBER; BEGIN full_file_name := open_log_file(log_file_name); log_message('******** Begin generation of trucking user data'); log_message('** Logfile location: ' || full_file_name, FALSE); -- Make sure the raw truck data exists and is in Router format IF (table_exists('router_transport') = 'FALSE') THEN log_message('ERROR: ROUTER_TRANSPORT table not found'); RETURN ; END IF; -- Make sure the Edge table exists IF (table_exists('EDGE') = 'FALSE') THEN log_message('ERROR: EDGE table not found'); RETURN ; END IF; -- Make sure the data version table exists IF (table_exists('sdo_router_data_version') = 'FALSE') THEN log_message('ERROR: SDO_ROUTER_DATA_VERSION table not found'); RETURN ; END IF; -- Cleanup the intermediate truck data partitioning table if its still around IF (table_exists('router_partitioned_truck_data') = 'TRUE') THEN execute immediate 'DROP TABLE router_partitioned_truck_data'; END IF; -- Create truck data intermediate partitioning table stmt := 'CREATE TABLE router_partitioned_truck_data(' || 'edge_id NUMBER, partition_id NUMBER, maintype NUMBER(2), ' || 'subtype NUMBER(2), value NUMBER(6,2))'; EXECUTE IMMEDIATE stmt; ins_stmt := 'INSERT INTO router_partitioned_truck_data VALUES(:eid, :pid, :mtype, :stype, :val)'; OPEN p_cursor FOR 'SELECT e.edge_id eid, partition_id, func_class, maintype, subtype, value ' || 'FROM edge e, router_transport r ' || 'WHERE (e.edge_id = r.edge_id)'; LOOP FETCH p_cursor INTO edge_id, partition_id, func_class, l_maintype, l_subtype, l_value; EXIT WHEN p_cursor%NOTFOUND; -- Associate the edge with a partition. EXECUTE IMMEDIATE ins_stmt USING edge_id, partition_id, l_maintype, l_subtype, l_value; -- If the edge is a highway place it in the highway partition also IF ((func_class = 1) OR (func_class = 2)) THEN partition_id := 0; EXECUTE IMMEDIATE ins_stmt USING edge_id, partition_id, l_maintype, l_subtype, l_value; END IF; END LOOP; CLOSE p_cursor; EXECUTE IMMEDIATE 'CREATE INDEX rtd_p_idx ON router_partitioned_truck_data(partition_id)'; -- Close the log file so the Java code can use it utl_file.fclose(part_log_file); -- Adjust the Oracle JVM maximum memory size to 800M. -- This is the equivelent to specifying -Xmx800m to -- java outside the database. Memory size is specified in bytes. ElocationSetJVMHeapSize(838860800); BEGIN -- Java code to partition the trucking data elocation_trucking_user_data(full_file_name); EXCEPTION WHEN OTHERS THEN part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A'); log_message(SQLERRM); utl_file.fclose(part_log_file); RAISE JAVA_ERROR; END; -- Reopen the logfile full_file_name := open_log_file(log_file_name); log_message('INFO: creating the final trucking user data table'); -- Rename the new partition table and build an index on it. IF (table_exists('trucking_user_data')= 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE trucking_user_data'; END IF; EXECUTE IMMEDIATE 'RENAME new_trucking_user_data to trucking_user_data'; log_message('INFO: create index trucking_ud_p_idx on trucking_user_data table'); EXECUTE IMMEDIATE 'CREATE INDEX trucking_ud_p_idx on trucking_user_data(partition_id)'; log_message('******** Completed generartion of trucking user data '); -- Close the log file utl_file.fclose(part_log_file); EXCEPTION WHEN JAVA_ERROR THEN raise_application_error(-20015, 'Oracle Router trucking user data generation failed'); WHEN OTHERS THEN IF (utl_file.is_open(part_log_file) = FALSE) THEN part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A'); END IF; log_message(SQLERRM); utl_file.fclose(part_log_file); raise_application_error(-20015, 'Oracle Router trucking user data generation failed'); END create_trucking_user_data; -- -- Entry point to cleanup tables used for debugging -- PROCEDURE cleanup_router(all_tables IN BOOLEAN) IS BEGIN -- Cleanup all temporary tables. clean_tables(all_tables); END; -- -- Entry point to used to produce a dump of the partition BLOBs -- PROCEDURE dump_partitions(log_file_name IN VARCHAR2 := 'sdo_router_partition.log', start_pid IN NUMBER DEFAULT 0, end_pid IN NUMBER DEFAULT -1, verbose IN BOOLEAN DEFAULT FALSE) IS full_file_name VARCHAR2(256); l_end_pid NUMBER := end_pid; max_pid NUMBER; stmt VARCHAR2(256); version VARCHAR2(32); BEGIN full_file_name := open_log_file(log_file_name); -- Make sure the table is actually there IF (table_exists('PARTITION') = 'FALSE' ) THEN log_message('ERROR: Partition dump failed, PARTITION table not found'); utl_file.fclose(part_log_file); RAISE PARAMETER_ERROR; END IF; stmt := 'SELECT MAX(PARTITION_ID) FROM PARTITION'; EXECUTE IMMEDIATE stmt INTO max_pid; -- The default value for the end partition id is max(partition_id) IF (l_end_pid < 0) THEN l_end_pid := max_pid; END IF; -- Validate the starting partition id. IF ((start_pid < 0) OR (start_pid > max_pid)) THEN log_message('ERROR: Invald Start Partition ID '||start_pid||', Valid Range (0,'||max_pid||')'); utl_file.fclose(part_log_file); RAISE PARAMETER_ERROR; END IF; -- Validate the ending partition id. IF ((l_end_pid < start_pid) OR (l_end_pid > max_pid)) THEN log_message('ERROR: Invald End Partition ID '||to_char(end_pid)||', Valid Range ('||start_pid||','||max_pid||')'); utl_file.fclose(part_log_file); RAISE PARAMETER_ERROR; END IF; version := get_version_info(); log_message('******** Beginning partition dump'); log_message('** Logfile location: ' || full_file_name, FALSE); log_message('** Routeserver data version: ' || version, FALSE); log_message('** Start partition id: ' || start_pid, FALSE); log_message('** End partition id: ' || l_end_pid, FALSE); IF (verbose) THEN log_message('** Verbose mode: TRUE', FALSE); ELSE log_message('** Verbose mode: FALSE', FALSE); END IF; log_message('', FALSE); -- Close the log file so the Java code can use it utl_file.fclose(part_log_file); elocation_dump_partition(full_file_name, start_pid, l_end_pid, verbose, is_10g(version)); EXCEPTION WHEN PARAMETER_ERROR THEN raise_application_error(-20004, 'Error, partition dump failed, see log file.'); WHEN OTHERS THEN IF (utl_file.is_open(part_log_file) = FALSE) THEN part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A'); END IF; log_message(SQLERRM); utl_file.fclose(part_log_file); raise_application_error(-20004, 'Error, partition dump failed, see log file.'); END; -- -- Entry point to used to validate the partition BLOBs -- PROCEDURE validate_partitions(log_file_name IN VARCHAR2 := 'sdo_router_partition.log', start_pid IN NUMBER DEFAULT 0, end_pid IN NUMBER DEFAULT -1, verbose IN BOOLEAN DEFAULT FALSE) IS full_file_name VARCHAR2(256); l_end_pid NUMBER := end_pid; max_pid NUMBER; stmt VARCHAR2(256); version VARCHAR2(32); BEGIN full_file_name := open_log_file(log_file_name); -- Make sure the table is actually there IF (table_exists('PARTITION') = 'FALSE' ) THEN log_message('ERROR: Partition validate failed, PARTITION table not found'); utl_file.fclose(part_log_file); RAISE PARAMETER_ERROR; END IF; stmt := 'SELECT MAX(PARTITION_ID) FROM PARTITION'; EXECUTE IMMEDIATE stmt INTO max_pid; -- The default value for the end partition id is max(partition_id) IF (l_end_pid < 0) THEN l_end_pid := max_pid; END IF; -- Validate the starting partition id. IF ((start_pid < 0) OR (start_pid > max_pid)) THEN log_message('ERROR: Invald Start Partition ID '||start_pid|| ', Valid Range (0,'||max_pid||')'); utl_file.fclose(part_log_file); RAISE PARAMETER_ERROR; END IF; -- Validate the ending partition id. IF ((l_end_pid < start_pid) OR (l_end_pid > max_pid)) THEN log_message('ERROR: Invald End Partition ID '||to_char(end_pid)|| ', Valid Range ('||start_pid||','||max_pid||')'); utl_file.fclose(part_log_file); RAISE PARAMETER_ERROR; END IF; version := get_version_info(); log_message('******** Beginning partition validation'); log_message('** Logfile location: ' || full_file_name, FALSE); log_message('** Routeserver data version: ' || version, FALSE); log_message('** Start partition id: ' || start_pid, FALSE); log_message('** End partition id: ' || l_end_pid, FALSE); IF (verbose) THEN log_message('** Verbose mode: TRUE', FALSE); ELSE log_message('** Verbose mode: FALSE', FALSE); END IF; log_message('', FALSE); -- Close the log file so the Java code can use it utl_file.fclose(part_log_file); -- Adjust the Oracle JVM maximum memory size to 800M. -- This is the equivelent to specifying -Xmx800m to -- java outside the database. Memory size is specified in bytes. ElocationSetJVMHeapSize(838860800); elocation_validate_partition(full_file_name, start_pid, l_end_pid, verbose, is_10g(version)); EXCEPTION WHEN PARAMETER_ERROR THEN raise_application_error(-20003, 'Error, partition validation failed, see log file.'); WHEN OTHERS THEN IF (utl_file.is_open(part_log_file) = FALSE) THEN part_log_file := utl_file.fopen ('SDO_ROUTER_LOG_DIR', log_file_name, 'A'); END IF; log_message(SQLERRM); utl_file.fclose(part_log_file); raise_application_error(-20003, 'Error, partition validation failed, see log file.'); END; -- -- Entry point to used to get the Router data version -- PROCEDURE get_version(log_file_name IN VARCHAR2 := 'sdo_router_partition.log') IS data_version VARCHAR2(32); full_file_name VARCHAR2(256); BEGIN full_file_name := open_log_file(log_file_name); data_version := get_version_info(); dbms_output.put_line('Routeserver: data version '|| data_version); log_message('INFO: Routeserver data version: ' || data_version); utl_file.fclose(part_log_file); END; -- -- main pl/sql procedure to partition a graph with coordinate information -- for recovering with -- PROCEDURE recover_graph_partition(p_tab_name IN VARCHAR2, min_pid IN NUMBER, max_pid IN NUMBER, p_level IN NUMBER, max_v_no IN NUMBER, make_equal IN BOOLEAN) IS stmt VARCHAR2(256); v_no NUMBER; pid NUMBER; p_counter NUMBER; tmp_p_tab_name VARCHAR2(32); BEGIN p_counter := max_pid+1; -- starting partition counter pid := min_pid; -- start logging log_message('INFO: starting recovery of '|| p_tab_name || ' partitioning' || ' partition level:' || p_level || ' min(partition id):' || min_pid || ' max(partition id)' || max_pid); FOR k IN min_pid..max_pid LOOP FOR i IN 0..p_level LOOP FOR j IN 1..power(2,i) LOOP tmp_p_tab_name := p_tab_name || '_' || pid; IF (table_exists(tmp_p_tab_name) = 'TRUE' ) THEN new_partition_proc(p_tab_name, max_v_no,pid,make_equal,p_counter); log_message('INFO: partitioning '|| p_tab_name || ' level: ' || j || ' partition id: ' || pid); ELSE p_counter := p_counter + 2; END IF; pid := pid +1; end loop; end loop; end loop; -- copy the result back to original table and ajust the pids adjust_final_pid(SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(p_tab_name)); log_message('INFO: completed recovery of '|| p_tab_name || ' partitioning'); EXCEPTION WHEN OTHERS THEN log_message(SQLERRM); log_message('ERROR: exception recovering partition '|| pid || ' of the ' || p_tab_name || ' table'); raise_application_error(-20010, 'Error Recovering Graph Partitioning'); END recover_graph_partition; -- -- Unused procedures and functions to delete -- PROCEDURE elocation_partition(node_tab_name in VARCHAR2, edge_tab_name in VARCHAR2, max_v_no in NUMBER, is_directed in BOOLEAN) IS p_v_tab_name VARCHAR2(64) := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(node_tab_name) || '_part'; p_e_tab_name VARCHAR2(64) := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(edge_tab_name) || '_part'; p_v_schema VARCHAR2(256) := '(vertex_id number, p_id number, x number,y number) storage (maxextents unlimited), nologging'; p_e_schema VARCHAR2(256) := '(edge_id number, source_id number, target_id number, source_p_id number, target_p_id number, length number, func_class number, speed_limit number) storage (maxextents unlimited), nologging'; p_log_tab_name VARCHAR2(256) := 'ELOCATION_PARTITION_LOG'; p_log_schema VARCHAR2(256) := '( node_tab_name varchar2(20), edge_tab_name varchar2(20), event varchar2(32), time varchar2(32) )'; log_stmt VARCHAR2(256) ; BEGIN -- log statement log_stmt := ' INSERT INTO ' || p_log_tab_name || ' (node_tab_name, edge_tab_name, event, time)' || ' values (:v_tab,:e_tab, :event, :time) '; -- Elocation tables not found IF (table_exists(node_tab_name) = 'FALSE' OR table_exists(edge_tab_name) = 'FALSE' ) THEN RETURN; END IF; -- prepare tables for graph partition IF (table_exists(p_v_tab_name) = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE ' || p_v_tab_name; END IF; IF (table_exists(p_e_tab_name) = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE ' || p_e_tab_name; END IF; IF (table_exists(p_log_tab_name) = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE ' || p_log_tab_name; END IF; -- create elocation partition log EXECUTE IMMEDIATE 'CREATE TABLE ' || p_log_tab_name || p_log_schema; -- create vertex table for partitioning EXECUTE IMMEDIATE 'CREATE TABLE ' || p_v_tab_name || p_v_schema; -- log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'CREATE VERTEX TABLE Done', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- create edge table for partitioning EXECUTE IMMEDIATE 'CREATE TABLE ' || p_e_tab_name || p_e_schema; -- log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'CREATE EDGE TABLE Done', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'POPULATE VERTEX TABLE ..,', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- populate vertex table EXECUTE IMMEDIATE 'INSERT INTO ' || p_v_tab_name || '(vertex_id,p_id,x,y) ' || ' SELECT a.node_id, 0, a.geometry.sdo_point.x, a.geometry.sdo_point.y FROM ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(node_tab_name) || ' a '; -- log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'POPULATE VERTEX TABLE Done', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'POPULATE EDGE TABLE ...', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- populate edge table EXECUTE IMMEDIATE ' INSERT INTO ' || p_e_tab_name || ' (edge_id,source_id,target_id, length,func_class, speed_limit) ' || ' SELECT edge_id, start_node_id,end_node_id,length,func_class,speed_limit FROM ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(edge_tab_name); -- log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'POPULATE EDGE TABLE Done', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'PARTITION STARTS', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- run graph partition -- partition_router(p_v_tab_name, max_v_no, 'R', true); -- log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'PARTITION Done', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- create indexes to speed up the update -- log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'PARTITION INDEXES STARTS', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); EXECUTE IMMEDIATE 'CREATE INDEX ' || p_v_tab_name || '_VIDX on ' || p_v_tab_name || '(VERTEX_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX ' || p_e_tab_name || '_EIDX on ' || p_e_tab_name || '(EDGE_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX ' || p_e_tab_name || '_ESIDX on ' || p_e_tab_name || '(SOURCE_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX ' || p_e_tab_name || '_ETIDX on ' || p_e_tab_name || '(TARGET_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX ' || p_e_tab_name || '_ESTIDX on ' || p_e_tab_name || '(SOURCE_ID,TARGET_ID)'; --log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'PARTITION INDEXES Done', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- update source_p_id, target_p_id in the partition edge table EXECUTE IMMEDIATE ' UPDATE ' || p_e_tab_name || ' SET source_p_id = ' || ' ( SELECT p_id FROM ' || p_v_tab_name || ' WHERE vertex_id = source_id), ' || ' target_p_id = ' || ' ( SELECT p_id FROM ' || p_v_tab_name || ' WHERE vertex_id = target_id)'; --log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'PARTITION EDGE Done', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- update partition_id in NODE and EDGE Table EXECUTE IMMEDIATE 'UPDATE ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(node_tab_name) || ' SET partition_id = ' || ' (SELECT p_id FROM ' || p_v_tab_name || ' WHERE vertex_id = node_id) '; --log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'ORIGINAL Node DONE', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); EXECUTE IMMEDIATE 'UPDATE ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(edge_tab_name) || ' SET partition_id = ' || ' (SELECT p_id FROM ' || p_v_tab_name || ' WHERE vertex_id = start_node_id) '; --log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'ORIGINAL Edge DONE', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- -- blob preparation will be based on p_v_tab_name and p_e_tab_name -- --log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'Proceed to Blobs', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); COMMIT; END elocation_partition; --- --- --- PROCEDURE elocation_prepare_blob(node_tab_name IN VARCHAR2, node_part_tab_name IN VARCHAR2, edge_tab_name IN VARCHAR2 ) IS p_v_tab_name VARCHAR2(64) := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(node_part_tab_name); p_e_tab_name VARCHAR2(64) := SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(edge_tab_name) || '_part'; p_v_schema VARCHAR2(256) := '(vertex_id number, p_id number, x number,y number) storage (maxextents unlimited), nologging'; p_e_schema VARCHAR2(256) := '(edge_id number, source_id number, target_id number, source_p_id number, target_p_id number, length number, func_class number, speed_limit number) storage (maxextents unlimited), nologging'; p_log_tab_name VARCHAR2(256) := 'ELOCATION_PARTITION_LOG'; p_log_schema VARCHAR2(256) := '( node_tab_name varchar2(20), edge_tab_name varchar2(20), event varchar2(32), time varchar2(32) )'; log_stmt VARCHAR2(256) ; BEGIN -- log statement log_stmt := ' INSERT INTO ' || p_log_tab_name || ' (node_tab_name,edge_tab_name, event, time) values (:v_tab,:e_tab, :event, :time) '; -- Elocation tables not found IF (table_exists(node_tab_name) = 'FALSE' OR table_exists(edge_tab_name) = 'FALSE' ) THEN RETURN; END IF; IF (table_exists(p_e_tab_name) = 'TRUE' ) THEN EXECUTE IMMEDIATE 'DROP TABLE ' || p_e_tab_name; END IF; IF (table_exists(p_log_tab_name) = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE ' || p_log_tab_name; END IF; -- create elocation partition log EXECUTE IMMEDIATE 'CREATE TABLE ' || p_log_tab_name || p_log_schema ; -- create edge table for partitioning EXECUTE IMMEDIATE 'CREATE TABLE ' || p_e_tab_name || p_e_schema; -- log EXECUTE IMMEDIATE log_stmt USING node_tab_name,edge_tab_name, 'CREATE EDGE TABLE Done', to_char(sysdate, 'YYYY:MM:DD:HH24:MI:SS'); -- log EXECUTE IMMEDIATE log_stmt USING node_tab_name,edge_tab_name, 'POPULATE EDGE TABLE ...', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- populate edge table EXECUTE IMMEDIATE ' INSERT INTO ' || p_e_tab_name || ' (edge_id,source_id,target_id, length,func_class, speed_limit) ' || ' SELECT edge_id, start_node_id,end_node_id,length,func_class,speed_limit FROM ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(edge_tab_name); -- log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'POPULATE EDGE TABLE Done', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- create indexes to speed up the update -- log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'PARTITION INDEXES STARTS', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); EXECUTE IMMEDIATE 'CREATE INDEX ' || p_v_tab_name || '_VIDX on ' || p_v_tab_name || '(VERTEX_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX ' || p_e_tab_name || '_EIDX on ' || p_e_tab_name || '(EDGE_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX ' || p_e_tab_name || '_ESIDX on ' || p_e_tab_name || '(SOURCE_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX ' || p_e_tab_name || '_ETIDX on ' || p_e_tab_name || '(TARGET_ID)'; EXECUTE IMMEDIATE 'CREATE INDEX ' || p_e_tab_name || '_ESTIDX on ' || p_e_tab_name || '(SOURCE_ID,TARGET_ID)'; --log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'PARTITION INDEXES Done', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- update source_p_id, target_p_id in the partition edge table EXECUTE IMMEDIATE ' UPDATE ' || p_e_tab_name || ' SET source_p_id = ' || ' ( SELECT p_id FROM ' || p_v_tab_name || ' WHERE vertex_id = source_id), ' || ' target_p_id = ' || ' ( SELECT p_id FROM ' || p_v_tab_name || ' WHERE vertex_id = target_id)'; --log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'PARTITION EDGE Done', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- update partition_id in NODE Table EXECUTE IMMEDIATE 'UPDATE ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(node_tab_name) || ' SET partition_id = ' || ' (SELECT p_id FROM ' || p_v_tab_name || ' WHERE vertex_id = node_id) '; --log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'ORIGINAL Node DONE', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- update partition_id in EDGE Table EXECUTE IMMEDIATE 'UPDATE ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(edge_tab_name) || ' SET partition_id = ' || ' (SELECT p_id FROM ' || p_v_tab_name || ' WHERE vertex_id = start_node_id) '; --log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'ORIGINAL Edge DONE', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); -- -- blob preparation will be based on p_v_tab_name and p_e_tab_name -- --log EXECUTE IMMEDIATE log_stmt USING node_tab_name, edge_tab_name, 'Proceed to Blobs', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); COMMIT; END elocation_prepare_blob; --- --- --- PROCEDURE elocation_test_tables(node_tab_name IN VARCHAR2, edge_tab_name IN VARCHAR2, n IN NUMBER) IS e_id NUMBER; rx NUMBER; ry NUMBER; r NUMBER := 10000.0; min_dist NUMBER := r/10.0; min_dist_2 NUMBER := min_dist*min_dist; max_connections NUMBER := 8; stmt VARCHAR2(256); x1 NUMBER; y1 NUMBER; x2 NUMBER; y2 NUMBER; dx NUMBER; dy NUMBER; counter NUMBER; TYPE num_array is VARRAY(10000000) of NUMBER; x_array num_array; y_array num_array; geom mdsys.sdo_geometry; length NUMBER; func_class NUMBER := 1; speed_limit NUMBER := 60.0; node_schema VARCHAR2(256) := '(node_id number, geometry mdsys.sdo_geometry, partition_id number)'; edge_schema VARCHAR2(256) := '(edge_id number ,start_node_id number, end_node_id number, partition_id number, func_class number,length number, speed_limit number, geometry mdsys.sdo_geometry, name varchar2(64))'; BEGIN IF (table_exists(node_tab_name) = 'TRUE' ) THEN EXECUTE IMMEDIATE 'DROP TABLE ' || SYS.DBMS_ASSERT.NOOP(node_tab_name); END IF; EXECUTE IMMEDIATE 'CREATE TABLE ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(node_tab_name) || node_schema; IF (table_exists(edge_tab_name) = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE ' || SYS.DBMS_ASSERT.NOOP(edge_tab_name); END IF; EXECUTE IMMEDIATE 'CREATE TABLE ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(edge_tab_name) || edge_schema; dbms_random.initialize(19254); x_array := num_array(); x_array.extend(n); y_array := num_array(); y_array.extend(n); -- create VERTEX table FOR i IN 1..n LOOP rx := abs(mod(dbms_random.random,r)); ry := abs(mod(dbms_random.random,r)); x_array(i) := rx; y_array(i) := ry; geom := mdsys.sdo_geometry(2001,null,mdsys.sdo_point_type(rx,ry,null),null,null); stmt := ' INSERT INTO ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(node_tab_name) || ' (node_id,geometry, partition_id) VALUES(:i,:geom,:pid)'; EXECUTE IMMEDIATE stmt USING i,geom,0; END LOOP; dbms_random.terminate; -- create an EDGE TABLE stmt := ' INSERT INTO ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(edge_tab_name) || ' (edge_id, start_node_id,end_node_id,func_class,length, speed_limit) ' || ' VALUES(:e_id,:start_id,:end_id,:func, :len, :speed)'; e_id := 1; FOR i IN 1..n LOOP counter := 1; x1 := x_array(i); y1 := y_array(i); FOR j IN i+1..n LOOP IF (counter > max_connections) THEN EXIT; ELSE x2 := x_array(j); y2 := y_array(j); dx := abs(x1-x2); dy := abs(y1-y2); IF ( dx > min_dist or dy > min_dist ) THEN null; ELSE IF ( (dx*dx + dy*dy ) < min_dist_2) THEN length := sqrt(dx*dx+dy*dy); EXECUTE IMMEDIATE stmt USING e_id,i,j, func_class, length, speed_limit; e_id := e_id +1; counter := counter +1; EXECUTE IMMEDIATE stmt USING e_id,j,i, func_class, length, speed_limit; e_id := e_id +1; counter := counter +1; END IF; END IF; END IF; END LOOP; END LOOP; COMMIT; END elocation_test_tables; --- --- --- FUNCTION outedge_array(p_id in NUMBER, edge_part_tab in VARCHAR2) RETURN mdsys.num_array IS TYPE CURSOR_TYPE IS REF CURSOR; e_cursor CURSOR_TYPE; e_id NUMBER; e_array mdsys.num_array; BEGIN e_array := mdsys.num_array(); OPEN e_cursor FOR 'SELECT edge_id FROM ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(edge_part_tab) || ' WHERE source_p_id = :id AND target_p_id != source_p_id ' USING p_id; LOOP FETCH e_cursor INTO e_id; EXIT WHEN e_cursor%NOTFOUND; e_array.extend(1); e_array(e_array.count) := e_id; END LOOP; CLOSE e_cursor; RETURN e_array; END outedge_array; --- --- --- PROCEDURE prepare_partition_edge(edge_tab in VARCHAR2, edge_part_tab in VARCHAR2, node_part_tab in VARCHAR2) IS stmt VARCHAR2(256); TYPE CURSOR_TYPE IS REF CURSOR; c CURSOR_TYPE; counter NUMBER; e_id NUMBER; s_id NUMBER; t_id NUMBER; s_pid NUMBER; t_pid NUMBER; geom mdsys.sdo_geometry; f_class NUMBER; len NUMBER; speed NUMBER; name VARCHAR2(64); p_e_schema VARCHAR2(256) := '(edge_id number, source_id number, target_id number, source_p_id number, target_p_id number, func_class number, speed_limit number,length number ) storage (maxextents unlimited), nologging'; no_to_commit NUMBER := 100000; BEGIN -- create an empty table IF (table_exists(edge_part_tab) = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE ' || SYS.DBMS_ASSERT.NOOP(edge_part_tab); END IF; EXECUTE IMMEDIATE 'CREATE TABLE ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(edge_part_tab) || p_e_schema; counter := 0; stmt := 'SELECT p_id FROM ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(node_part_tab) || ' WHERE vertex_id = :id '; EXECUTE IMMEDIATE ' INSERT INTO elocation_partition_log (node_tab_name,event,time) VALUES (:edge,:event,:time)' USING edge_part_tab, 'Partition Edge Table starts ...', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); OPEN c FOR 'SELECT edge_id, start_node_id, end_node_id, func_class, length, speed_limit, geometry, name FROM ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(edge_tab); LOOP FETCH c INTO e_id,s_id,t_id,f_class,len,speed; EXIT WHEN c%NOTFOUND; EXECUTE IMMEDIATE stmt into s_pid using s_id; EXECUTE IMMEDIATE stmt into t_pid using t_id; EXECUTE IMMEDIATE 'INSERT INTO ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(edge_part_tab) || ' VALUES (:e_id,:s_id,:t_id,:s_pid,:t_pid:f_class,:len,:speed)' USING e_id,s_id,t_id,s_pid,t_pid,f_class,len,speed; counter := counter + 1; IF (counter > no_to_commit) THEN EXECUTE IMMEDIATE ' INSERT INTO elocation_partition_log (node_tab_name,event,time) VALUES (:edge,:event,:time)' USING edge_part_tab, 'Prepare P Edge Commit ...', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); counter := 0; COMMIT; END IF; END LOOP; CLOSE c; EXECUTE IMMEDIATE ' INSERT INTO elocation_partition_log (node_tab_name,event,time) VALUES (:edge,:event,:time)' USING edge_part_tab, 'Prepare P Edge Table Done ...', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); COMMIT; END prepare_partition_edge; -- -- -- PROCEDURE set_log_info(file UTL_FILE.FILE_TYPE) IS BEGIN part_log_file := file; EXCEPTION WHEN OTHERS THEN RAISE; END set_log_info; -- -- -- FUNCTION table_type(table_name in varchar2) RETURN varchar2 is no number; owner varchar2(32); BEGIN execute immediate 'select count(*) from user_tables ' || ' where table_name = :tname ' into no using NLS_UPPER(table_name); IF ( no = 1) THEN RETURN 'TABLE'; END IF; -- test if it's a view execute immediate 'select count(*) from user_views ' || ' where view_name = :tname ' into no using NLS_UPPER(table_name); IF ( no = 1) THEN RETURN 'VIEW'; END IF; RETURN NULL; EXCEPTION WHEN OTHERS THEN RAISE; END table_type; --- --- --- PROCEDURE update_elocation_edge(in_edge_tab IN VARCHAR2, out_edge_tab IN VARCHAR2, node_part_tab IN VARCHAR2) IS stmt VARCHAR2(256); TYPE CURSOR_TYPE IS REF CURSOR; c CURSOR_TYPE; counter NUMBER; e_id NUMBER; s_id NUMBER; t_id NUMBER; p_id NUMBER; geom mdsys.sdo_geometry; f_class NUMBER; len NUMBER; speed NUMBER; name VARCHAR2(64); no_to_commit NUMBER := 100000; BEGIN -- create an empty table IF (table_exists(out_edge_tab) = 'TRUE') THEN EXECUTE IMMEDIATE 'DROP TABLE ' || SYS.DBMS_ASSERT.NOOP(out_edge_tab); END IF; EXECUTE IMMEDIATE 'CREATE TABLE ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(out_edge_tab) || ' STORAGE (maxextents unlimited), NOLOGGING as SELECT * FROM ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(in_edge_tab) || ' WHERE rownum < 0 '; counter := 0; stmt := 'SELECT p_id FROM ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(node_part_tab) || ' WHERE vertex_id = :id '; EXECUTE IMMEDIATE ' INSERT INTO elocation_partition_log (node_tab_name,event,time) VALUES (:edge,:event,:time)' USING out_edge_tab, 'Update Edge Table starts 50000 ...', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); OPEN c FOR 'SELECT edge_id, start_node_id, end_node_id, func_class, length, speed_limit, geometry, name FROM ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(in_edge_tab); LOOP FETCH c INTO e_id,s_id,t_id,f_class,len,speed,geom,name; EXIT WHEN c%NOTFOUND; EXECUTE IMMEDIATE stmt INTO p_id using s_id; EXECUTE IMMEDIATE 'INSERT INTO ' || SYS.DBMS_ASSERT.NOOP(out_edge_tab) || ' VALUES (:e_id,:s_id,:t_id,:p_id,:f_class,:len,:speed,:geom,:name)' USING e_id,s_id,t_id,p_id,f_class,len,speed,geom,name ; counter := counter +1 ; IF (counter > no_to_commit) THEN counter := 0; COMMIT; END IF; END LOOP; CLOSE c; EXECUTE IMMEDIATE ' INSERT INTO elocation_partition_log (edge_tab_name,event,time) values (:edge,:event,:time)' USING out_edge_tab, 'Update Edge Table Done...', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); COMMIT; END update_elocation_edge; -- -- -- PROCEDURE update_elocation_node(in_node_tab in VARCHAR2, out_node_tab in VARCHAR2, node_part_tab in VARCHAR2) IS stmt VARCHAR2(256); TYPE CURSOR_TYPE IS REF CURSOR; c CURSOR_TYPE; counter NUMBER; id NUMBER; geom mdsys.sdo_geometry; p_id NUMBER; no_to_commit NUMBER := 100000; BEGIN -- create an empty table IF (table_exists(out_node_tab) = 'TRUE') THEN EXECUTE immediate 'DROP TABLE ' || SYS.DBMS_ASSERT.NOOP(out_node_tab); END IF; EXECUTE IMMEDIATE 'CREATE TABLE ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(out_node_tab) || ' STORAGE (maxextents unlimited), NOLOGGING as SELECT * FROM ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(in_node_tab) || ' WHERE rownum < 0 '; counter := 0; stmt := 'SELECT p_id FROM ' || SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(node_part_tab) || ' WHERE vertex_id = :id '; EXECUTE IMMEDIATE ' INSERT INTO elocation_partition_log (node_tab_name,event,time) VALUES (:node,:event,:time)' USING out_node_tab, 'Update Node Table starts...', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); OPEN c for 'SELECT node_id, geometry FROM ' || SYS.DBMS_ASSERT.ENQUOTE_NAME(in_node_tab); LOOP FETCH c INTO id, geom; EXIT WHEN c%NOTFOUND; EXECUTE IMMEDIATE stmt INTO p_id using id; EXECUTE IMMEDIATE 'INSERT INTO ' || SYS.DBMS_ASSERT.NOOP(out_node_tab) || ' VALUES (:id,:geom,:p_id)' USING id,geom,p_id ; counter := counter +1 ; IF (counter > no_to_commit) THEN counter := 0; COMMIT; END IF; END LOOP; CLOSE c; EXECUTE IMMEDIATE ' INSERT INTO elocation_partition_log (node_tab_name,event,time) VALUES (:node,:event,:time)' USING out_node_tab, 'Update Node Table Done...', to_char(sysdate,'YYYY:MM:DD:HH24:MI:SS'); COMMIT; END update_elocation_node; -- -- End unused procedures and functions -- END SDO_ROUTER_PARTITION; / show errors; grant execute on SDO_ROUTER_PARTITION to public; create or replace public synonym SDO_ROUTER_PARTITION for MDSYS.SDO_ROUTER_PARTITION; commit;