Network scripts for Taiwan Road Map

Script早已經在科長那兒測試過了. 怕以後找不到, 先post上來以免日後忘了.


-- Create the SDO_NET directed network. Also creates the SDO_NET_NODE$,
-- SDO_NET_LINK$, SDO_NET_PATH$, SDO_NET_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('A012009', 1, TRUE, TRUE);
-- Populate the A012009_NODE$ A012009_LINK$ A012009_PATH$ tables.
create or replace procedure network_building as
count_num number :=0;
BEGIN
FOR A012009_rec IN (
SELECT MI_PRINX LINK_ID,
to_NUMBER(FNODE) FNODE,
get_point(geoloc)FGEOLOC,
to_NUMBER(TNODE) TNODE,
GET_POINT(GEOLOC,GET_NUM_POINTS(GEOLOC)) TGEOLOC ,
roadname,
roadtype,
GEOLOC
FROM A012009 A where (Fnode is not null)AND(Tnode is not null) AND (A.Geoloc.SDO_GTYPE = '2002') )
LOOP

select count(*) into count_num from A012009_node$ where node_id = A012009_rec.FNODE;
if count_num = 0 then
INSERT INTO A012009_node$(node_id,geometry,node_name,node_type,ACTIVE) VALUES(
A012009_rec.FNODE,
A012009_rec.FGEOLOC,
A012009_rec.roadname,
A012009_rec.roadtype,
'Y');
end if;

select count(*) into count_num from A012009_node$ where node_id = A012009_rec.TNODE;
if count_num = 0 then
INSERT INTO A012009_node$(node_id,geometry,node_name,node_type,ACTIVE) VALUES(
A012009_rec.TNODE,
A012009_rec.TGEOLOC,
A012009_rec.roadname,
A012009_rec.roadtype,
'Y');
end if;

-- select count(*) into count_num from A012009_LINK$ where LINK_id = A012009_rec.LINK_ID;
-- if count_num = 0 then
INSERT INTO A012009_link$(link_id,Link_name,start_node_id,end_node_id,LINK_TYPE,ACTIVE,GEOMETRY,BIDIRECTED) VALUES(
A012009_rec.LINK_ID ,
A012009_rec.ROADNAME,
A012009_rec.FNODE,
A012009_rec.TNODE,
A012009_rec.roadtype,
'Y',
A012009_rec.GEOLOC,
'N');
-- end if;

-- commit;
END LOOP;
commit;
end;
/
-- 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)
select 'A012009_NODE$','GEOMETRY',diminfo,SRID from user_sdo_geom_metadata where table_name = 'A012009';
INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID)
select 'A012009_LINK$','GEOMETRY',diminfo,SRID from user_sdo_geom_metadata where table_name = 'A012009';
INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID)
select 'A012009_PATH$','GEOMETRY',diminfo,SRID from user_sdo_geom_metadata where table_name = 'A012009';
-- Create the spatial indexes
CREATE INDEX A012009_nodes_idx ON A012009_node$(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
CREATE INDEX A012009_links_idx ON A012009_link$(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
CREATE INDEX A012009_path_idx ON A012009_path$(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;