TxDOT Motor Carrier Division Database Design Document – GIS/Bridge Databases
TxPROS Project FINAL MS-9 Deliverable 1.3.5 Version 2.1 March 1, 2009
DATABASE DESIGN
DOCUMENT
The TxPROS GIS Database/
Bridge Database
Texas Department of Transportation
Motor Carrier Division (MCD)
Texas Permit Routing
Optimization System
TxPROS
Version: 2.1 FINAL as MS-9 Del. Revision Date: 3/1/2009
Table of Contents
Introduction 3
Deliverable 3
Overview 3
Special Note on Restrictions 4
GIS Database 5
Data Subject Area 5
Conceptual Data Model 5
Business Description of Database 5
ID Identifying the Road Segment 5
Road Names 6
Road Ownership and Jurisdiction 6
Routing Information – Topology 6
Address Information 6
OS/OW Routing Properties 6
Geometry 7
Retention of Records 7
Primary Key 7
Glossary 8
Logical Data Model 13
Physical Data Model 26
Bridge Database 26
Data Subject Area 26
Conceptual Data Model 26
Business Description of Database 26
Glossary 28
Logical Data Model 28
Physical Data Model 31
Road Type Codes Appendix 32
Prefix/Suffix Codes Appendix 36
Revision History 37
Introduction
This Database Design Document shall provide a technical description of the TxPROS GIS Databases. This document shall describe the databases themselves and provide Conceptual Data Models, Logical Data Models, and Physical Data Models for the databases.
Deliverable
This document is a deliverable for item 1.3.5 of the TxPROS Statement of Work named the Database Design Documents which address the technical requirements for building the databases including a Conceptual Data Model, a Logical Data Model, and a Physical Data Model.
Overview
The TxPROS GIS database is at the heart of the TxPROS system. This database will be used to build the binary files that will be used for routing and mapping. This database will also be used as the GIS reference for the Restrictions.
The TxPROS Bridge database will be a database that connects the TxPROS GIS data with the bridges within the State of Texas. This database will connect road segments that travel on and under bridges with the bridges themselves. This Bridge database will be limited in that it will only address those road segments in which it immediately affects. That is, if multiple bridges pass over a road segment, the road segment will only be related to the bridge with the lowest clearance.
Special Note on Restrictions
Both of the databases described in this document contain OS/OW routing Restriction information. In particular, the GIS database contains Load Limit Roads, and the Bridge database indicates road segments that bridges affect. However, this OS/OW routing Restriction information will not be used directly to route any OS/OW loads. All Restrictions from these databases that affect routing will be stored and accessed in the Restrictions Database. Any information from the GIS and Bridge databases will have to be transferred to the Restriction Database before it will be used for routing. The reasons for this are the following:
- The GIS and Bridge databases will not be maintained in real time, but the Restriction Database will.
- It is likely that in some instances the values needed for OS/OW Restrictions will be different from the corresponding values in the GIS and Bridge databases.
- Directly accessing the GIS or Bridge database for Restrictions will significantly complicate programming of the routing system and also will significantly complicate retrieval of permits for reporting purposes whose travel has been affected by these Restrictions.
GIS Database
Data Subject Area
The data subject area for the TxPROS GIS database will be Motor Carrier.
Conceptual Data Model
Business Description of Database
The TxPROS GIS database provides the GIS data for the TxPROS system. This database has a record for each road segment within the state of Texas. These records will contain the following information about the road segment:
· ID identifying the road segment
· Road names
· Road ownership and jurisdiction
· Routing information – Topology
· Address information
· OS/OW routing properties
· Geometry
Each of these information items will be described in more detail below.
The primary purpose for this database is to provide a routing graph (in the mathematical sense) for the routing and mapping engines of the TxPROS project. It is critical that the routing and mapping capabilities of the database are not compromised by other potential applications for the database. A secondary purpose for this database is to provide a GIS bases for the TxPROS Restriction database.
ID Identifying the Road Segment
There will be a unique TxPROS road segment identifier for each record in this database. This ID will be an important foreign key for several other databases. In addition, there will be the Tele Atlas road segment identifier for each record that corresponds uniquely to a Tele Atlas road segment.
Road Names
Each road segment will have road names. If the road segment represents an on-system road, it will have the official TxDOT road name. In addition, the road segment will have each road name assigned by Tele Atlas.
Road Ownership and Jurisdiction
Each road segment will have an on-system flag. Important note: The primary purpose of this flag is to identify whether on not MCD is authorized to route OS/OW loads on the road segment and not necessarily if the road segment is maintained by TxDOT. Additionally, each on-system road segment will have the TxDOT district for which maintenance responsibilities have been assigned. Finally, each road segment will have the county in which it physically resides identified, and if the road segment falls within the boundary of a city as identified by the current Tele Atlas data, that city will also be identified.
Routing Information – Topology
Each road segment will have general routing information sufficient to be used by the routing engine. This information will include the FCC and ACC classes for the road segment, directionality for the road segment, and the Z-level at the ends of the segment. This information will also include connectivity, turn capabilities, and such other topological information as is necessary to correctly route vehicles.
Address Information
Road segments that represent a road with address information will have that information. This information will include the low and high address range along with the ZIP code for each side of the road.
OS/OW Routing Properties
Each on-system road segment will have additional TxDOT data that could be of use in routing OS/OW loads. This information includes load limits, shoulders, and medians.
Geometry
Each road segment will have sufficient GIS information to be used in mapping. This information will include the latitude and longitude of each point making up the road segment. An elevation field will be included but will not be populated by the TxPROS project.
Retention of Records
Highways and streets in the state of Texas are constantly changing. Because of this, the TxPROS GIS database will also be constantly changing. Road segments are deactivated when the roads those segments correspond to are dug up or changed, or when errors in the database are fixed that involve those road segments. While records in the GIS database are deactivated, they should not be deleted from the database until retention requirements have been met. Deactivated road segments will not be used to route new trips, but the road segments will be available to show the route taken by historical trips.
Primary Key
There is no natural field within the TxPROS GIS database that can be used as a primary key. Because of this, there will be a system generated TxPROS ID that will be used as the primary key. This primary key will be critical in that it will be referenced by a number of important databases including the Restrictions database and the Permit databases.
Glossary
Glossary Business Term / Definition / SourceACC / ACC is an acronym for Arterial Class Code. This code classifies roads according to the level of travel mobility that they provide in the road network. Mobility refers to the volume of traffic that a stretch of road carries and the length of trip that it serves. ACC is a number from 1 to 5 where 1 represents major interstates such as I-10 that are used for large volumes of traffic running from one end of the state to the other. An ACC of 5 represents residential and rural streets and roads that provide final access to homes. / Tele Atlas
BRINSAP / Bridge Inventory, Inspection, and Appraisal Program. / TxDOT Glossary
CPS / CPS is an acronym for Central Permitting System. This is a software system that was custom built for TxDOT MCD. It is Web-enabled and allows customers to submit OS/OW permit applications via the internet. MCD permit officers use CPS to create and issue permits. / TxPROS PMP
DFO / DFO is an acronym for Distance From Origin and is the TRM-generated value of distance from any point on a given route back to the beginning point of the route. This length includes lengths of all linked route segments with local roads and other signed highways occurring up to that point. / TxDOT Glossary
Directionality / Directionality refers to movement in one direction along a road segment. Every road segment has a ‘from’ node and a ‘to’ node. These nodes can be identified by the order of the points that make up the road segment. If travel happens in the direction of the from node to the to node, that travel is said to be with the road segment. If travel happens in the direction of the to node to the from node, then that travel is said to be against the road segment. / ProMiles
Glossary Business Term / Definition / Source
Distress Score / Distress score is a Pavement Management Information System (PMIS) term which describes the overall amount of pavement distress. Distress scores range from 1 (most distress) to 100 (least distress). / TxDOT Glossary
FCC / FCC is an acronym for Feature Class Code. This is a code that is used to describe the level of service the road provides. Follows are the levels of service for the roads:
· Primary roads with limited access
· Primary roads without limited access
· Secondary and connecting roads
· Local, neighborhood and rural roads
· Vehicular trail / Tele Atlas
Maneuver / A maneuver is used to describe the travel of a vehicle over a specific set of roads in a specified direction. Maneuvers are represented in the TxPROS GIS data base as two or more road segments run in a particular order. Maneuvers are used to restrict vehicles from making turns. / ProMiles
Reference Marker / A reference marker is the building block of the TRM Highway Data File and the link between the automated files and what is actually on the ground. Reference markers replace control-section-milepoint, milepost, link-node, or others as the sole source of location identification for highway-related on-system data. / TxDOT Glossary
Restriction / A Restriction is an object in the TxPROS system that affects OS/OW routing. This object can be a physical Restriction such as a bridge with limits on clearance or it can be a legal Restriction such as no travel during an area at set times. Restrictions have several components including: A description of the Restriction, road segments the Restriction applies to, conditions that apply to the Restriction such as height or length, time periods, how the Restriction affects travel, and an owner. / ProMiles
Glossary Business Term / Definition / Source
Restriction Condition / A Restriction condition is the condition that a vehicle or a load must have in order for that Restriction to affect travel for a vehicle. An example of a condition is a height limit of 16’ 4”. In this case, if the vehicle and load exceed this height then the Restriction applies to that load. If the vehicle and load do not exceed this height, then the Restriction does not apply. Restrictions can have multiple conditions. / ProMiles
Road Segment / A road segment represents a stretch of road in a GIS database. A road segment is represented by a single record in the GIS Road Segments table. A road segment will have attributes that represent attributes of the physical road. / ProMiles
Tandem / A tandem is two consecutive axles extending across the full width of the vehicle. The center of these axles must be at least 40 inches and no more than 96 inches apart. / TRM-PAVEMENT file specification
Tele Atlas / Tele Atlas is the primary 3rd party GIS data provider for the TxPROS project. Tele Atlas is a company currently based in Lebanon NH (note: as of the writing of this document Tele Atlas was in the act of being acquired by TomTom.) Tele Atlas is one of the two leading GIS database providers through out the world. / Tele Atlas
TRM / Texas Reference Marker System Database / TxDOT Glossary
Glossary Business Term / Definition / Source
Weighting / Weighting refers to how a Restriction affects travel on road segments. When the routing engine is calculating a trip it will look at all of the road segments to determine the shortest trip from the origin to the destination. The engine calculates a ‘length’ for each road segment to use in these calculations. The ‘length’ is based upon the physical length of the road segment and the class of road it represents. When a Restriction affects travel on a road segment it does so through the weighting. The more the Restriction discourages travel, the higher the weighting for that road segment. If the Restriction completely disallows travel, such as a low bridge would, then the weighting is set to infinity. The routing engine uses this weighting in its ‘length’ calculations for the road segment. / ProMiles
UTM / UTM is an acronym for Universal Transverse Mercator coordinate system. This is a grid-based method of specifying locations on the surface of the earth. The UTM system employs sixty zones each based on a specifically defined secant Transverse Mercator projection. These zones run from 80º S to 84º N and are 6º of longitude in width. The UTM’s accuracy is rated at one in 2,500. Texas is in zones 13, 14, and 15. / Colorado State and USGS
Glossary Business Term / Definition / Source
Z-Level / Z-level fields indicate the elevation at the end of each road segment and are used for planar connectivity for each end of the segment.
· Segments at ground level have Z-Level = 0.
· Segments with Z-Level >0 are man-made structures that are suspended or elevated above ground level. EXAMPLE: a bridge spanning a river or street would have a Z-Level > = 1.
· Segments with Z-Level <0 are underground or underwater structures below ground level.
· For two segments to route, the elevation at the common node must be the same. / Tele Atlas
Logical Data Model