Transcript MOD3 Class2 Relation..

RELATIONAL DATABASES, LOCATION
REFERENCING SYSTEMS, AND
PAVEMENT MANAGEMENT SYSTEMS
Instructional Objectives


Understand principles and concepts of
relational databases
Understand need for location referencing
systems

Global Positioning System

Geographic Information Systems
Instructional Objectives
WHY Examine DATABASES?
Databases are the storage vessel for PMS
data and the basis for PMS analysis.
The PMS engineer needs some background
in database design to discuss the
database setup with the database
designer.
Database Management Systems
Route
Year

Hierarchical

Network

Relational
Mile Ref
Tables, Fields and Records

Example Road Section Table
Field or
Attribute
Road Section Table
Table
Record
Section ID
d
e
f
From
0.0
1.0
3.0
To
1.0
3.0
5.0
AADT
1000
500
100
Func_Class
ART
COLL
ART
Min_Width
1500
1000
1500
Keys





A field or fields in table used to
access data in table
Can be unique or non-unique
Only a unique key can be used as
‘Primary Key’
Section ID was Primary Key in
Road Section Table
Used in relating tables
One-to-Many Relationships
Consider relating Condition Table
to Road Section Table
Foreign Key
Survey Sheet No.
1028347
8472039
8437620
6778902
Section ID
d
e
f
d
Condition Table
Year
1985
1985
1985
1986
%_Cracking
10
20
15
15
Rut_Depth
0.2
0.2
0.2
0.3
One-to-Many Relationship
Road Section Table
Section ID
d
e
f
Primary Key
From
0.0
1.0
3.0
To
1.0
3.0
5.0
AADT
1000
500
100
Func_Class
ART
COLL
ART
Min_Width
1500
1000
1500
Many-to-Many Relationships
Consider relating Construction History Table
below to Road Section Table
Construction History Table
Contract Number
100
200
300
400
From
0.0
0.0
1.0
3.0
To
5.0
1.0
3.0
5.0
Year
1955
1986
1987
1988
Work_Type
Construct
Overlay
Overlay
Overlay
?
Section ID
d
e
f
From
0.0
1.0
3.0
Unit_Cost
100000
50000
50000
50000
Road Section Table
To
1.0
3.0
5.0
AADT
1000
500
100
Func_Class
ART
COLL
ART
Min_Width
1500
1000
1500
The Junction Table
Construction History Table
Contract Number
100
200
300
400
From
0.0
0.0
1.0
3.0
To
5.0
1.0
3.0
5.0
Contract Number
100
100
100
200
300
400
Year
1955
1986
1987
1988
Section ID
d
e
f
d
e
f
Work_Type
Construct
Overlay
Overlay
Overlay
Unit_Cost
100000
50000
50000
50000
Junction Table
Road Section Table
Section ID
d
e
f
From
0.0
1.0
3.0
To
1.0
3.0
5.0
AADT
1000
500
100
Func_Class
ART
COLL
ART
Min_Width
1500
1000
1500
Normalization


Process of dividing database into separate
tables which eliminates unnecessary
duplication and facilitates all relationships as
‘one-to-many’
Rule of thumb:
significantly reduce duplicate data
Example of Normalization
Original Road Section Table
Section ID
d
e
f
From
0.0
1.0
3.0
To
1.0
3.0
5.0
AADT
1000
500
100
Func_Class
ART
COLL
ART
Min_Width
1500
1000
1500
Duplicate values
BECOMES:
Modified Road Section Table
Section ID
d
e
f
From
0.0
1.0
3.0
To
1.0
3.0
5.0
AADT
1000
500
100
Func_Class
ART
COLL
ART
Standards Table
Func_Class
ART
COLL
Min_Width
1500
1000
Structured Query Language (SQL)


Official standard language for dealing with
relational systems
Defined by ANSI standard, though every
vendor has own version
Data vs Information



‘Data’ are values physically stored in
database
‘Information’ is meaning of those values as
understood by some user
For example, values in Min_Width field of
Standards Table are not information until you
know their units (e.g., 27 vs. 27 ft)
Pavement Sections
• Different Pavement section lengths for
inventory, condition, costs,
maintenance/rehabilitation history
• Fixed length sections or Dynamic
segmentation based on a variable level, such
as condition
• Temporary analysis sections based on the
“least common denominator” or section
length using Concurrent transformation
routines
Pavement Sections
DYNAMIC SEGMENTATION
Pavement Sections
CONCURRENT TRANSFORMATION
AADT
1000
Material
Cost
Trans
500
100
100
240
AADT
750
233
Mat Cost
100
240
1000 *1  500 * 2  500 *1
750 
2
The transforms are based on weighted-averages base on length
Pavement Sections
CONCURRENT TRANSFORMATION
Transformation Classes:
(1) weighted average,
(2) sum,
(3) maximum value,
(4) minimum value,
(5) statistical average,
(6) first occurrence, and
(7) most length
PMS DATA
LOCATION REFERENCE SYSTEMS
Support for PMS
Linking PMS data to the pavement
LOCATION REFERENCE SYSTEMS
Where are we Toto?
We’re not in Kansas anymore!!!
Definitions




Location: position on road
Address: string of characters uniquely
identifying a location
Location Reference Method (LRM):
procedures used in field to find address of a
location
Location Reference System (LRS):
procedures used to manage location
referencing
Location Reference Methods
Linear:
The mile point is the offset in miles from the beginning
of the road in the primary direction.
The mile post is a post placed along the road, with a
number placed on it representing the mile point of
the post.
Reference Point
Reference Post
Linear Location Reference Methods



Fundamentally same; get address by
getting distance from known point
Problems associated with inability to
reproduce distances between points
Difference between “plan” distance and
over the road distances
Plan distance
Mile Point
The mile (or kilometer) point location reference method
is the most fundamental linear method of all.
This method assumes each road has one reference
point located at the beginning of the road. The
address of any point along the road is given as an
offset.
Mile
2.5
4.5
5.75
Point 0.0
Offset
7.25
Mile Post

‘Known point’ is a post

Re-establishing milepost signs

Issues with route realignment
0.0
Mile
Posts
0
2.5
1
2
4.5
3
4
5.75
5
7.25
6
7
REFERENCE SYSTEMS
MILE POSTS
MP3.0
MP7.0
MP 5.0
MP 1.0
START MP 0.0
0.0
Mile
Posts
0
2.5
1
2
4.5
3
4
5.75
5
7.25
6
7
REFERENCE SYSTEMS
MILE POSTS
South & West
Secondary Direction
MP3.0
MP7.0
MP 5.0
MP 1.0
START MP 0.0
Primary Direction
North & East
Length of Route in Primary Direction may be different
than that in the Secondary Direction.
REFERENCE SYSTEMS
MILE POSTS
MP3.0
MP7.0
MP 5.0
MP 1.0
Over the Road Miles
START MP 0.0
As measured by a
set of plans
As measured by
the collection
vehicle DMI
REFERENCE SYSTEMS
LINK-NODE
RT 521
NODE 121
LINK 121-231
NODE 231
RT 611
RT 713
A +0.0
B +0.0
C +0.0
C +1.25
D +0.0
1.25
Reference
A
Point
B
C
D
Reference Post

‘Known point’ is a post
A +0.0
A
B +1.0
B
1.0
C +0.5
C
1.75
0.5
C +1.75
D +0.75
D
0.75
Reference Point

‘Known point’ is identifiable
physical feature on road
Railroad
crossing
Intersection
Bridge Center
A +0.0
B +0.0
C +0.0
C +1.25
D +0.0
1.25
Reference
A
Point
B
C
D
Global Positioning System (GPS)

What is GPS? Triangulation

Sources of Error – overheard cover


Differential GPS based on permanent
base station
Using GPS for PMS
REFERENCE SYSTEMS
Global Positioning System
(GPS)
LONG
LONG
LONG
STATE COORDINATE
SYSTEM - (GPS/GIS)
LAT
LAT
LAT
Circle of inf luence
Satellite 2
Satellite 1
Location

Satellite 3

Triangulation
Satellites send radio
signal.
Receiver uses ‘velocity *
travel-time to calculate
location.
Geographic Information Systems (GIS)

What is GIS?
[ESRI ARC GIS] ARCMap Demo

Main Ingredients
– Spatial Data
– Attribute Data
The Base Map

Contains all fundamental geographic features

Source and content varies


TIGER files, aerial photographs, or local hard
copy maps are typical sources
Base Map preparation of GIS is the most time
consuming
Integrating Data with a GIS


Useful for integrating line
objects (roads), point
objects (signs), and
polygon objects (political
boundaries) with
pavement data.
Used for display and
analyses of system data
“The picture is worth a
1000 words!”
Application of GIS in PMS


If PMS uses only traditional roadway data,
then GIS is not required for integration
If it uses non-traditional data such as
boundaries, then a GIS is required for
spatial integration
Application of GIS in PMS (continued)



Dynamic Segmentation and Concurrent
Transformation are linear functions not GIS
functions
If PMS needs to show map, and no GIS
exists, use Automated Mapping
If GIS exists, use it
PMS DATABASES
COMPUTERIZED DATABASE
MANAGEMENT SYSTEMS (DBMS)
INTEGRATED RELATIONAL DATABASES
PMS COORDINATED DATABASES
COMPUTERIZED DATABASE MANAGEMENT SYSTEMS
INTEGRATED RELATIONAL DATABASES
INVENTORY - RT NUMBER, FUNCTIONAL CLASS,
PAVEMENT TYPE, etc.
CONDITION - RIDE QUALITY, DISTRESS, FRICTION,
DEFLECTION
COSTS
HISTORY
TRAFFIC / LOADS
PAVEMENT HISTORY
Initial Construction Data
Date, Cost, Material, Structure, etc.
Pavement Preservation
Date, Treatment, Cost, Material, Structure, etc.
Rehabilitation
Date, Treatment, Cost, Material, Structure, etc.
Reconstruction
Date, Treatment, Cost, Material, Structure, etc.
COSTS
AGENCY COSTS
P&E
DESIGN
CONSTRUCTION
PREVENTIVE AND ROUTINE MAINTENANCE
PRESERVATION / REHABILITATION / RECONSTRUCTION
SALVAGE
PMS DATABASES
DATABASE PRODUCTS/REPORTS
DEFICIENCY REPORTS –
SECTIONS WITH UNACCEPTABLE CONDITION
PERFORMANCE HISTORIES
DISPLAY GIVEN CONDITION PARAMETER OVER TIME OR
LOADS
CONSTRUCTION, MAINTENANCE, REHAB HISTORIES
LIST OF BUDGET NEEDS - STATE, MPO, COUNTY, TOLL
AUTHORITIES
PMS DATABASES
DATABASE PRODUCTS/REPORTS
[TABULAR, BUSINESS GRAPHICS, GIS MAP, Video]
90
Section
Year
Cost
32
2006
$100,000
47
2008
$237,999
80
70
60
East
West
North
50
40
30
20
10
0
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr
Data Storage
Terabyte
Mountain
1 Terabyte = 1,024 Gigabytes
ASSET MANAGEMENT
• Pavement Management
•
•
•
•
•
•
•
•
•
Bridge Management
Water System Management
Sewer System Management
Sign Management
Traffic Signal Management
Facilities Management
Equipment Management
Work Order Cost Accounting
Etc.
PAVEMENT MANAGEMENT SYSTEMS
• Deighton dTIMS
• Stantec Highway Performance Monitoring
System
• Agile Assets Pavement Analyst
• Cartegraph Pavement View and Pavement
View Plus
• In-house developed
Deighton dTIMS CT
Deighton dTIMS CT
Deighton dTIMS CT
AgileAssets
Pavement Analyst
Pavement Analyst’s
features:
• Access to PMS data
• Custom Configuration
• Integrated GIS
• Built-in Reporting
Cartegraph
Critical Elements in Models and Scenarios
1
2
3
6
7
5
8
4
9
Cartegraph
Setting up options
Cartegraph
Establishing Performance Models
1
2
3
5
4
Questions?