This is an old revision of the document!
BZ10M: Bolzano Buses Network - Network Data Model
Import
imp user/pass file=/bz10m/bus_network-current.dmp full=y
Create network (needs to be done once for each bus):
INSERT INTO USER_SDO_NETWORK_METADATA( network, network_category, geometry_type, node_table_name, node_geom_column, link_table_name, link_geom_column, link_direction) VALUES( 'BZ_BUS_L2_NET', 'SPATIAL', 'SDO_GEOMETRY', 'BZ_BUS_L2_NODE$', 'GEOMETRY', 'BZ_BUS_L2_LINK$', 'GEOMETRY', ' DIRECTED'); INSERT INTO USER_SDO_NETWORK_METADATA( network, network_category, geometry_type, node_table_name, node_geom_column, link_table_name, link_geom_column, link_direction) VALUES( 'BZ_BUS_L10A_NET', 'SPATIAL', 'SDO_GEOMETRY', 'BZ_BUS_L10A_NODE$', 'GEOMETRY', 'BZ_BUS_L10A_LINK$', 'GEOMETRY', ' DIRECTED');
Current version: 1.0 (9/26/2007) (bus lines 2, 10A)
The imported network data model will contain two tables per bus line BZ_BUS_L*_LINK$ and BZ_BUS_L*_NODE$; and the table BZ_BUS_STOPS_ON_LINK to connect bus network with the roads network.
The links are taken from a bus network provided long time ago by Municipality, thus it does not exactly map on the corresponding links (streets) of the bz10m_roads_network. The nodes are the bus stations. The link directions corresponds to the direction of a bus.
To transfer oneself from a bus network to a roads network and vice versa, use the BZ_BUS_STOPS_ON_LINK table. Look at the column descriptions below.
Columns
BZ_BUS_L*_LINK$:
| LINK_ID | ID of the link | |
| START_NODE_ID | ID of the starting node (bus station) of the link in the BZ_BUS_L*_NODE$ | |
| END_NODE_ID | ID of the ending node (bus station) of the link in the BZ_BUS_L*_NODE$ | |
| COST | NUMBER value created for your needs. Now it is entirely set to NULL | |
| GEOMETRY | Geometry column to specify link appearance | |
BZ_BUS_L*_NODE$:
| NODE_ID | ID of the node |
| NAME | Name of the bus station |
| GEOMETRY | Geometry column to indicate node coordinates |
BZ_BUS_STOPS_ON_LINK:
| STOP_NODE_ID | ID of the bus stop in the BZ_BUS_L*_NODE$ table |
| LINE | Name of the bus line (e.g. '2', '10A') |
| LINK_ID | ID of the link of the BZ_ROADS_LINK$l table It indicates the link, on which the current bus stop (STOP_NODE_ID) of the bus line (LINE) resides |
| DISTANCE_RATIO | A value between 0 and 1, indicating on which part of the link (LINK_ID) the bus stop (STOP_NODE_ID) is (WRT to link start) |
Creating index
If you want to use any spatial operators (such as finding all segments within distance, retrieving bounding rectangle etc.) you need to create an index for any table of the bus line. How to do this for the LINK table (same for NODE, just substitute LINK$ with NODE$):
INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME, COLUMN_NAME, DIMINFO) VALUES('BZ_BUS_L*_LINK$', 'GEOMETRY', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -180, 180, 0.05), SDO_DIM_ELEMENT('Y', -90, 90, 0.05)) ); CREATE INDEX BZ_BUS_L*_LINK$_INDEX ON BZ_BUS_L*_LINK$(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX;