User Tools

Site Tools


contrib:bz10m_roads2_network

This is an old revision of the document!


BZ10M: Bolzano Roads 2 (New) Network - Network Data Model

Import
imp user/pass file=/bz10m/roads2_network-1.0.dmp full=y

Create network (needs to be done once):

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_ROADS2_NET', 'SPATIAL', 'SDO_GEOMETRY',
                'BZ_ROADS2_NODE$', 'GEOMETRY',
                'BZ_ROADS2_LINK$', 'GEOMETRY', 'UNDIRECTED');

Current version: 1.0 (1/10/2008)

This is the TeleAtlas table we got from the Creaform on November the 12th. The table contains linestrings (in GEOMETRY column), which represent roads segments. The most important change here is the number of lanes (NUMCORSIE). Whenever NUMCORSIE=0, it is assumed NUMCORSIE=2 (one lane per one direction, bi-directed road).

The imported network data model will contain two tables: BZ_ROADS_LINK$ and BZ_ROADS_NODE$

The links are taken directly from the Spatial table road segments (linestrings). The nodes are created by generating a node for each segment start and end points.

Columns of the table

We are working on the meanings of the columns:
BZ_ROADS:

LINK_ID
LINK_NAME
START_NODE_ID
END_NODE_ID
LINK_TYPE
ACTIVE
LINK_LEVEL
GEOMETRY
COST
BIDIRECTED
GC_ID
GC_CODICE
GC_CODAGGR
GC_QMED
GC_COUNT
GC_SVIL
ID
FEATTYP
FT
F_JNCTID
F_JNCTTYP
T_JNCTID
T_JNCTTYP
PJ
METERS
FRC
NET1CLASS
NET2CLASS
NAME
NAMELC
SOL
JUMP
ROUTENUM
RTETYP
RTEDIR
RTEDIRVD
PROCSTAT
COD_REG
COD_PROV
COD_COM
GC_DESCR
LONG1
LONG2
IDASSE
LST_ONEWAY
LST_PUT
ID_STRASSE
TEXT_D
TEXT_I
TEXT_DIL
ANNOTAZION
NODO_VL
CORSIABUS
NUMCORSIE Number of lanes. When =0, means there are two lanes (one per direction)
NODO_MT

Explanations from an old table (may not match):

IDlong integer, the ID for each road segment
FEATTYP?
FT?
F_JNCTID?
F_JNCTTYP?
T_JNCTID?
T_JNCTTYP?
PJ?
METERSLength of a segment in meters, rounded up to the hundredth part. See LENGTH
FRC?
NETCLASS?
NETBCLASS?
NET2CLASS?
NAMEName of the street/highway number
NAMELC?
SOL?
NAMETYP?
CHARGE?
ROUTENUMHighway number
RTETYP?
RTEDIR?
RTEDIRVD?
PROCSTAT?
FOW?
SLIPRD?
BACKRD?
TOLLRD?
RDCOND?
STUBBLE?
PRIVATERD?
CONSTATUS?
ONEWAYRoad type. Looks utterly incomplete.
FT-one way street
N-only for bus or cycle (transit limited e.g. Downtown)

From the old database documentation (sent by Linas):
DW = Double Way
FT = One Way (you must symbolize with an arrow)
N = only for bus or cycle (transit Limited e.g. Downtown)
E = Motorway or Expressway (autostrada del Brennero A22)
FE = Subway or Tunnel
FG = not consider
F_BP?
T_BP?
F_ELEV?
T_ELEV?
KPHSpeed limit
MINUTESCalculated column: amount of time in minutes which takes to drive the road segment, when driving at the speed limit
POSACCUR?
CARRIAGE?
LANESNumber of lanes. Utterly incomplete (98.7% are zeroes)
LENGTHThe length of the road in meters, precise up to a thousandth part
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 this table. How to do this for the LINK table (same for NODE, just substitute LINK$ with NODE$):

DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME='BZ_ROADS2_LINK$';
INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
       VALUES('BZ_ROADS2_LINK$', 'GEOMETRY',
              SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 0, 0, .001),
                            SDO_DIM_ELEMENT('Y', 0, 0, .001),
              82344)
       );
CREATE INDEX BZ_ROADS2_LINK$_INDEX ON BZ_ROADS2_LINK$(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
/var/www/wiki.inf.unibz.it/data/attic/contrib/bz10m_roads2_network.1200564890.txt.gz · Last modified: 2019/01/16 10:03 (external edit)