Schema For VMS Data

completemiscreantData Management

Nov 28, 2012 (4 years and 4 months ago)

252 views

Portal Project


Schema for VMS Data


1





Schema For VMS Data













Portal Project


Schema for VMS Data


2


Change log

Initial Version

1.0

Priya Chavan

Ver 1.1

After Review By Kristin
-
Updated
by

PC

Ver1.2

April
12 2007

Updated By PC















Portal Project


Schema for VMS Data


3


Table Of Contents

Sample Data Received from OD
OT

................................
....

4

2004
-
2005 Data

................................
................................
................................
................................
.........

4

2006 Data

................................
................................
................................
................................
..................

5

ER Diagram

................................
................................
........

6

Tables

................................
................................
................

7

Updating Tables in PostGreSql

................................
..........

7

Data Clean up using Perl

................................
................................
................................
...........................

7

Login to portal database

................................
................................
................................
...........................

8

Using GUI

................................
................................
................................
................................
..............

8

Using Command Prompt

................................
................................
................................
.......................

8

Importing data

................................
................................
................................
................................
..........

8










Portal Project


Schema for VMS Data


4


Sample Data
Received

from ODOT

2004
-
2005 Data


ATMSUSER_ATMS_HIGHWAYS

file
contains
:

"HIGHWAYID","SHORTDIRECTION","LONGDIRECTION","HIGHWAYNAME","CMS_NAME","HAR_MEDIA_NAME"

1.00,"N","NORTH","I
-
5","I
-
5","I
-
5"

2.00,"S","SOUTH","I
-
5","I
-
5","I
-
5"

3.00,"N","NORTH","I
-
205","I205","I
-
205"

4.00,"S","SOUTH","I
-
205","I205","I
-
205"

5.00,"N","NORTH","I
-
405","I405","I
-
405"

6.00,"S","SOUTH","I
-
405","I405","I
-
405"

7.00,"E","EAST","I
-
84","I
-
84","I
-
84 EAST"

8.00,"W","WEST","I
-
84","I
-
84","I
-
84 WEST"

qry
ATMSUSER_CMDA_SIGNCONFIG


contains

"SIGNID","HIGHWAYID","MILEPOST","LOCATIONTEXT"

1.00,1.00,285.26,"I
-
5 NB at Wilsonville"

2.00,1.00,291.00,"I
-
5 NB at Carmen"

3.00,1.00,298.48,"I
-
5 NB at Iowa"

4.00,1.00,305.67,"I
-
5 NB at Columbia"

5.00,2.00,305.52,"I
-
5 SB
at Lombard"

6.00,8.00,1.52,"I
-
84 WB at 28th"

7.00,8.00,11.21,"I
-
84 WB at 148th"

12.00,3.00,20.17,"I
-
205 NB @ Stark"

9.00,11.00,68.14,"US 26 EB at Cedar Hills"



tblATMSUSER_EVLG_EVENTLOG_vms_displayed

file

contains
:

"TIMESTAMP","MESSAGETEXT"

2/3/2004 18:24
:24,"Operator atmssvr displayed message on sign 8: (Line 1) (Line 2) (Line
3) "

2/3/2004 18:31:09,"Operator atmssvr displayed message on sign 8: (Line 1) (Line 2) (Line
3)

"

Portal Project


Schema for VMS Data


5

2/3/2004 18:38:24,"Operator atmssvr displayed message on sign 8: (Line 1) (Line 2) (Line
3) "

2/3/2004 18:46:04,"Operator atmssvr displayed message on sign 8: (Line 1) (Line 2) (
Line
3) "

2/4/2004 9:20:33,"Operator root displayed message on sign 2: (Line 1) OREGON DEPARTMENT (Line 2) OF
TRANSPORTATION (Line 3) SIGN UNDER TEST"

2/4/2004 20:11:09,"Operator root displayed message on sign 6: (Line 1) TEST (Line 2)

(Line 3) "

2/5/2004 17:01:58,"Operator root displayed message on sign 11: (Line 1) ACCIDENT I
-
405 NB (Line 2)
FREMONT BRIDGE (Line 3) RIGHT LANES CLOSED"


2006 D
ata

tblATMSUSER_CMDA_SIGNCONFIG_locations

contains

"
SIGNID","CONTROLJURISDICTION","HIGHWAY
NAME","LONGDIRECTION","MILEPOST","LOCATIONTEXT"

1.00,300.00,"I
-
5","NORTH",285.26,"I
-
5 NB at Wilsonville"

2.00,300.00,"I
-
5","NORTH",291.00,"I
-
5 NB at Carmen"

3.00,300.00,"I
-
5","NORTH",298.48,"I
-
5 NB at Iowa"

4.00,300.00,"I
-
5","NORTH",305.67,"I
-
5 NB at Colum
bia"

5.00,300.00,"I
-
5","SOUTH",305.52,"I
-
5 SB at Lombard"

In
tblvms_msgs_2006 csv

file
,
We have

"TIMESTAMP","MESSAGETEXT"

9/19/2006 16:50:38,"Operator 1028 displayed message on sign 13: (Line 1) ACCIDENT NORTH OF (Line 2) US
26/POWELL BLVD (Line 3) RIG
HT LANE CLOSED"

9/19/2006 16:56:49,"Operator 1091 displayed message on sign 1: (Line 1) TEST (Line 2) TEST (Line 3) TEST"

9/19/2006 19:47:34,"Operator 1028 displayed message on sign 2: (Line 1) ACC NB HWY 217 (Line 2) 1 MILE N
OF I
-
5 (Line 3) LEFT
LANE CLOSED"

9/19/2006 20:37:01,"Operator 1028 displayed message on sign 2: (Line 1) HWY 217 NB (Line 2) 99W EXIT
CLOSED (Line 3) USE ALT ROUTE"

9/20/2006 5:46:15,"Operator 1071 displayed message on sign 53: (Line 1) THIS IS (Line 2) A TEST (Line 3
) "

9/20/2006 5:47:23,"Operator 1071 displayed message on sign 51: (Line 1) THIS IS (Line 2) A TEST (Line 3) "

9/20/2006 5:48:43,"Operator 1071 displayed message on sign 51: (Line 1) THIS IS (Line 2) A TEST (Line 3) "

9/20/2006 5:49:43,"Operator 10
71 displayed message on sign 54: (Line 1) THIS IS (Line 2) A TEST (Line 3) "

9/20/2006 7:22:20,"Operator 1025 displayed message on sign 18: (Line 1) ACCIDENT I
-
84 WB (Line 2) 3 MILES
AHEAD (Line 3) ALL LANES CLOSED"


Portal Project


Schema for VMS Data


6


ER Diagram








































Highway

Message

Sign

Short
Direction

Highway

ID

Long

Directio
n

Highway

Name

CMS Name

HAR Media
Name

Operator

ID

Msg Text

TimeStamp

Sign ID

Control
Jurisdiction

MilePost

Location

Text

Has

D
isplays

1

N

N

N

Operator

Operator ID

Operated By

Portal Project


Schema for VMS Data


7


Tables


Table Name:

Highway

Highway ID

PK

Integer

Highway Name


Varchar(10)

Short Direction

FK

Char(1)


Table Name:

Sign

Sign ID

PK

Integer

Control Jurisdiction


Integer

Highway ID

FK

Integer

MilePost


Real

Location
Text


text


Table Name:

Message

Sign ID

PK

Integer

Timestamp

PK

timestamp

Line1


Text

Line2


Text

Line3


Text

Operator

ID


????


Updating
Tables in PostGreSql

Data Clean up using Perl

There are 4 Perl scripts use
d to remove unwanted commas, newline characters, to break single entry
into multiple entries. The perl scripts are

Removeline.pl

Removecommas.pl

Split.pl

Trim_fields.pl

*Usage information of these scripts is in the respective files.

Portal Project


Schema for VMS Data


8

Login to portal databas
e

Using GUI

Go to
http://portal.its.pdx.edu/phpPgAdmin/



Login using user name and

password

Using Command Prompt

ssh to jam.its and login using command



cd
../../usr/lib/postgresql/7.4/bin;psql
-
h "
pg.research.pdx.edu
"
-
u portals .

Execute this from your
home directory.

Importing data

In the PhpAdmin GUI
,

there is an

import tab at the right hand corner. This is the easiest way of
importing data.

To import from command prom
p
t
,

use COPY command. If the file being uploaded is
huge then it can be broken into s
maller 5000 entry files using
Perl

script
“split.pl”
located at
utility/scripts in svn

or the Unix command split
.