FRA Data Dictionary for External Use Grade Crossing Inventory System (GCIS) v2.4.0.0, Released: 02/12/2016

Document Revision Date: 02/12/2016


U.S. Department of Transportation

Federal Railroad Administration                            Office of Railroad Safety



DOT/FRA/RRS-23

Published: January 6, 2015

Effective: March 7, 2015

This page intentionally left blank

Revision Summary


Revision Date

Document Version #

Revision Class

Summary

03/06/2015

1.0

Major

Initial Availability

10/02/2015

1.1

Minor

Added Document Versioning

11/13/2015

2.0

Major

Updated field type for III.2.J, OthSgn2, and OthSgn3

12/18/2015

2.1

Minor

Updated Document Versioning

01/15/2016

3.0

Major

Updated Part III.4.C, III.6, and IV.6

02/12/2016

4.0

Major

Updated Data Model

Changed CI_CrossingHeader from Lookup table to Data table





Table of Contents

Revision Summary 3

Table of Contents 4

Table of Figures 4

List of Tables 4

Section 1. Introduction 5

    1. Purpose 5

    2. Background 5

    3. Scope 5

Section 2. Overview 5

    1. Data Model 5

    2. Data Tables 7

      1. Tables Storing Crossing Inventory Records 7

      2. Tables that Stores Lookup Values 19

Table of Figures

Figure 2-1: GCIS v2.0 Logical Data Model 6

List of Tables

Table 2-1: Fields in CI_CrossingHeader 7

Table 2-2: Fields in CI_HighwayTrafficControlDevice 10

Table 2-3: Fields in CI_LocationAndClassification 13

Table 2-4: Fields in CI_OperatingRailroad 15

Table 2-5: Fields in CI_PhysicalCharacteristics 16

Table 2-6: Fields in CI_PublicHighway 17

Table 2-7: Fields in CI_ReportBase 19

Table 2-8: Fields in CI_Errors 19

Table 2-9: Fields in CI_Lookups 20

Table 2-10: Fields in Lp_Reason 21

Federal Railroad Administration Highway-Rail Crossing Division


Section 1. Introduction

    1. Purpose

      GCIS v2.0 is a web-based application allowing Railroad, State, and Transit users the ability to directly submit crossing records through two methods, (1) a web user interface to include the ability to upload multiple records using an FRA preapproved Excel template and (2) a web Application Programming Interface (API). The data received are stored within a SQL Server database that has been redesigned and contains new schemas.

    2. Background

      The previous Grade Crossing Inventory System was designed over a decade ago by FRA using Visual Basic 6.0 (VB) with SQL Server and MS Access database backend. The system had two versions: GX32 and GCIS. GX32 was a VB application with an MS Access database backend and some Railroads and States used it to maintain and upload grade crossing data. The previous GCIS was a VB application with a SQL Server backend and was used by the FRA data entry team to process grade crossing inventory data. GCIS v2.0 replaced all previous versions and these legacy applications are retired.

    3. Scope

      This document provides a detailed explanation of all the data tables that is used in the GCIS v2.0 database. A brief summary of each table will be provided along with the column name, a description of each column, the value(s) that will be stored, field types, and the location of the field within the Grade Crossing Inventory Form (6180.71) (if applicable).


      Section 2. Overview

      At a high level, the GCIS v2.0 data model contains two main categories as follows:

      • Crossing Records – contains data consisting of all five parts of the Grade Crossing Inventory Form including the Header information

      • Lookups – stores all the lookup values used throughout the application


    1. Data Model

      The data model provides an overview of the GCIS v2.0 new database structure. The table prefixes are labeled with, CI_ to indicate that those tables capture crossing inventory information. The subsequent sections provides further explanation regarding each table in the GCIS database.


      5

      Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

      Document Revision Date: 02/12/2016

      Federal Railroad Administration Highway-Rail Crossing Division


      Figure 2-1: GCIS v2.0 Logical Data Model



      6

      Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

      Document Revision Date: 02/12/2016

      Federal Railroad Administration Highway-Rail Crossing Division

    2. Data Tables

      1. Tables Storing Crossing Inventory Records

        1. CI_CrossingHeader

          This table contains data related to the crossing and ownership information of the Primary Operating Railroad agency.


          Column Name

          Description

          Field Values

          Type

          AgencyId

          The Reporting Agency Type ID from which the submission originated

          1 = Railroad

          2 = State

          3 = Transit

          4 = FRA Internal

          int

          CountyCode

          The code assigned to each U.S. County


          varchar(5)

          CrossingID

          Primary Key. Also the Crossing Inventory Number.


          varchar(50)

          PublishedReportBaseId

          Foreign key to the CI_ReportBase table


          int

          StateCode

          The code assigned to each U.S. State


          varchar(5)

          Created

          Date for which the original submission was created


          datetime

          CreatedBy

          Username of the user who originally submitted the records


          varchar(50)

          LastUpdated

          Date for which the existing submission was modified


          datetime

          LastUpdatedBy

          Username of the user who last submitted the updated records


          varchar(50)


        2. CI_HighwayTrafficControlDevice

          Table 2-1: Fields in CI_CrossingHeader

          This table stores information pertaining to Part III: Highway or Pathway Traffic Control Device Information of the Grade Crossing Inventory Form (6180.71). Detailed information regarding the table is described below.


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          III.2.D

          AdvW10_1

          Count of Advance Warning Signs W10-1 flag


          numeric(1,0)

          III.2.D

          AdvW10_11

          Count of Advance Warning Signs W10-11 flag


          numeric(1,0)

          III.2.D

          AdvW10_12

          Count of Advance Warning Signs W10-12 flag


          numeric(1,0)

          III.2.D

          AdvW10_2

          Count of Advance Warning Signs W10-2 flag


          numeric(1,0)

          III.2.D

          AdvW10_3

          Count of Advance Warning Signs W10-3 flag


          numeric(1,0)

          III.2.D

          AdvW10_4

          Count of Advance Warning Signs W10-4 flag


          numeric(1,0)


          7

          Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

          Document Revision Date: 02/12/2016

          Federal Railroad Administration Highway-Rail Crossing Division


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          III.2.D

          AdvWarn

          Advance Warning Signs

          0 = None

          1 = W10-1

          2 = W10-2

          3 = W10-3

          4 = W10-4

          11 = W10-11

          12 = W10-12

          varchar(32)

          III.3.F

          AwdIDate

          Installation Date of Current Active Warning Devices


          varchar(6)

          III.3.G

          AwhornChk

          Wayside Horn

          1 = Yes

          2 = No

          varchar(1)

          III.3.G

          AwhornlDate

          Wayside Horn Installed On (date)


          varchar(6)

          III.3.I

          Bells

          Number of Bells


          numeric(2,0)

          III.3.D

          Bkl_FlashPost

          Mast Mounted Flashing Lights: Back Lights Included

          1 = Yes

          2 = No

          varchar(32)

          III.3.C

          CFlashType

          Type of Cantilevered (or Bridged) Flashing Light Structures

          0 = None

          1 = Incandescent

          2 = LED

          varchar(32)

          III.2.G

          Channel

          Channelization Devices/Medians

          1 = All Approaches

          2 = One Approach

          3 = Median – All Approaches

          4 = Median – One Approach

          5 = None

          varchar(1)

          III.2.I

          EnsSign

          ENS Sign Displayed

          1 = Yes

          2 = No

          varchar(1)

          III.2.H

          Exempt

          EXEMPT Signs

          1 = Yes

          2 = No

          varchar(1)

          III.3.C

          FlashNov

          Count of Cantilevered (or Bridged) Flashing Light Structures Not Over Traffic Lane


          numeric(2,0)

          III.3.K

          FlashOth

          Other Flashing Lights or Warning Devices: Count


          numeric(2,0)

          III.3.K

          FlashOthDes

          Other Flashing Lights or Warning Devices: Specify type


          varchar(256)

          III.3.C

          FlashOv

          Count of Cantilevered (or Bridged) Flashing Light Structures Over Traffic Lane


          numeric(2,0)

          III.3.E

          FlashPai

          Total Count of Flashing Light Pairs


          numeric(2,0)

          III.3.D

          FlashPost

          Mast Mounted Flashing Lights (count)


          numeric(2,0)



          8

          Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

          Document Revision Date: 02/12/2016

          Federal Railroad Administration Highway-Rail Crossing Division


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          III.3.D

          FlashPostType

          Mast Mounted Flashing Lights Type

          0 = None

          1 = Incandescent

          2 = LED

          varchar(32)

          III.3.B

          GateConf

          Gate Configuration

          1 = 2 Quad

          2 = 3 Quad

          3 = 4 Quad

          varchar(32)

          III.3.B

          GateConfType

          Type of Gate Configuration

          4 = Full (Barrier) Resistance

          6 = Median Gates

          varchar(32)

          III.3.A

          GatePed

          Count of Pedestrian Gate Arms


          numeric(2,0)

          III.3.A

          Gates

          Count of Roadway Gate Arms


          numeric(2,0)

          III.5

          HwtrfPsig

          Highway Traffic Pre-Signals

          1 = Yes

          2 = No

          varchar(1)

          III.5

          HwtrfPsiglndis

          Stop Line Distance (count)


          numeric(3,0)

          III.5

          HwtrfPsigsdis

          Storage Distance (count)


          numeric(3,0)

          III.4.A

          HwynrSig

          Does Nearby Hwy Intersection have Traffic Signals?

          1 = Yes

          2 = No

          varchar(1)

          III.3.H

          HwyTrafSignl

          Highway Traffic Signals Controlling Crossing

          1 = Yes

          2 = No

          varchar(1)

          III.4.B

          Intrprmp

          Hwy Traffic Signal Interconnection

          1 = Not Interconnected

          2 = For Traffic Signals

          3 = For Warning Signs

          varchar(32)

          III.2.L

          Led

          LED Enhanced Signs


          varchar(256)

          III.2.E

          Low_Grnd

          Low Ground Clearance Signs

          1 = Yes

          2 = No

          varchar(1)

          III.2.E

          Low_GrndSigns

          Number of Low Ground Clearance Signs


          numeric(2,0)

          III.6

          MonitorDev

          Highway Monitoring Devices

          0 = None

          1 = Yes-Photo/Video Recording

          2 = Yes-Vehicle Presence Detection

          varchar(32)

          III.1

          NoSigns

          Are there Signs or Signals?

          1 = Yes

          2 = No

          varchar(1)

          III.2.J

          OthDes1

          Specify Type of Other MUTCD Signs


          varchar(10)

          III.2.J

          OthDes2

          Specify Type of Other MUTCD Signs 2


          varchar(10)

          III.2.J

          OthDes3

          Specify Type of Other MUTCD Signs 3


          varchar(10)

          III.2.J

          OthSgn

          Other MUTCD Signs

          1 = Yes

          2 = No

          varchar(1)


          9

          Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

          Document Revision Date: 02/12/2016

          Federal Railroad Administration Highway-Rail Crossing Division


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          III.2.J

          OthSgn1

          Number of Other MUTCD Signs


          numeric(2,0)

          III.2.J

          OthSgn2

          Number of Other MUTCD Signs 2


          numeric(2,0)

          III.2.J

          OthSgn3

          Number of Other MUTCD Signs 3


          numeric(2,0)

          III.2.F

          PaveMrkIDs

          Pavement Markings

          0 = None

          1 = Stop Lines

          2 = RR Xing Symbols

          3 = Dynamic Envelope

          varchar(32)

          III.4.C

          PrempType

          Highway Traffic Signal Preemption

          1 = Simultaneous

          2 = Advance

          varchar(1)

          III.2.K

          PrvxSign

          Private Crossing Signs

          1 = Yes

          2 = No

          varchar(1)


          ReportBaseId

          Foreign Key to the CI_ReportBase table


          int

          III.3.D

          Sdl_FlashPost

          Mast Mounted Flashing Lights: Side Lights Included

          1 = Yes

          2 = No

          varchar(32)

          III.3.J

          SpecPro

          Non-Train Active Warning

          0 = None

          1 = Flagging/Flagman

          2 = Manually Operated Signals

          3 = Watchman

          4 = Floodlighting

          varchar(20)

          III.2.B

          StopStd

          Number of STOP Signs


          numeric(1,0)

          III.2.A

          XBuck

          Number of Crossbuck Assemblies


          numeric(2,0)

          III.2.C

          YieldStd

          Number of YIELD Signs


          numeric(1,0)


          WdCode

          Warning Device Code


          varchar(1)


          Created

          Date for which the original submission was created


          datetime


          CreatedBy

          Username of the user who originally submitted the records


          varchar(50)


          LastUpdated

          Date for which the existing submission was modified


          datetime


          LastUpdatedBy

          Username of the user who last submitted the updated records


          varchar(50)


        3. CI_LocationAndClassification

          Table 2-2: Fields in CI_HighwayTrafficControlDevice

          This table stores information pertaining to Part I: Location and Classification Information of the Grade Crossing Inventory Form (6180.71).


          Box No. on Form

          Column Name

          Description

          Field Values

          Type


          10

          Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

          Document Revision Date: 02/12/2016

          Federal Railroad Administration Highway-Rail Crossing Division


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          I.5

          BlockNumb

          Block Number


          varchar(6)

          I.4

          CityCD

          The code assigned to each U.S. City


          varchar(10)

          I.3

          CntyCD

          The code assigned to each U.S. County


          varchar(10)

          I.23

          DevelTypID

          Type of Land Use

          11 = Open Space

          12 = Residential

          13 = Commercial

          14 = Industrial

          15 = Institutional

          16 = Farm

          17 = Recreational

          18 = RR Yard

          varchar(32)

          I.6

          Highway

          Highway Type & No.


          varchar(256)

          I.26

          HscoRrid

          HSR Corridor ID


          varchar(4)

          I.35

          HwyCont

          State Contact (Telephone No.)


          varchar(10)

          I.27

          Latitude

          Latitude


          varchar(256)

          I.29

          LLsource

          Lat/Long Source

          1 = Actual

          2 = Estimated

          varchar(1)

          I.28

          Longitude

          Longitude


          varchar(256)

          I.7

          MultFrmsFiled

          Do Other Railroads Operate a Separate Track at Crossing?

          1 = Yes

          2 = No

          varchar(1)

          I.4

          Nearest

          In/Near

          0 = In

          1 = Near

          varchar(1)

          I.20

          OpenPub

          Public Access (If Private Crossing)

          1 = Yes

          2 = No

          varchar(1)

          I.33

          PolCont

          Emergency Notification Telephone No.


          varchar(10)

          I.19

          PosXing

          Crossing Position

          1 = At Grade

          2 = RR Under

          3 = RR Over

          varchar(1)

          I.1

          Railroad

          The code associated with the Primary Operating Railroad


          varchar(32)


          ReportBaseId

          Foreign Key to the CI_ReportBase table


          int

          I.34

          RrCont

          Railroad Contact (Telephone No.)


          varchar(10)

          I.13

          RrID

          Line Segment


          varchar(256)

          I.15

          RrMain

          The code associated with the Parent Railroad


          varchar(32)

          I.32.A

          RrNarr

          Railroad Narrative


          varchar(max)


          11

          Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

          Document Revision Date: 02/12/2016

          Federal Railroad Administration Highway-Rail Crossing Division


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          I.30.A

          RrNarr1

          Railroad narrative A


          varchar(256)

          I.30.B

          RrNarr2

          Railroad narrative B


          varchar(256)

          I.30.C

          RrNarr3

          Railroad narrative C


          varchar(256)

          I.30.D

          RrNarr4

          Railroad narrative D


          varchar(256)

          I.8.

          SameInd

          Do Other Railroads Operate Over Your Track at Crossing?

          1 = Yes

          2 = No

          varchar(1)

          I.8.

          SameRr1

          The code associated with the Railroad selected in the 1st drop-down list for field I.8


          varchar(32)

          I.8.

          SameRr2

          The code associated with the Railroad selected in the 2nd drop-down list for field I.8


          varchar(32)

          I.8.

          SameRr3

          The code associated with the Railroad selected in the 3rd drop-down list for field I.8


          varchar(32)

          I.8.

          SameRr4

          The code associated with the Railroad selected in the 4th drop-down list for field I.8


          varchar(32)

          I.7

          SepInd

          Do Other Railroads Operate a Separate Track at Crossing?

          1 = Yes

          2 = No

          varchar(1)

          I.7

          SepRr1

          The code associated with the Railroad selected in the 1st drop-down list for field I.7


          varchar(32)

          I.7

          SepRr2

          The code associated with the Railroad selected in the 2nd drop-down list for field I.7


          varchar(32)

          I.7

          SepRr3

          The code associated with the Railroad selected in the 3rd drop-down list for field I.7


          varchar(32)

          I.7

          SepRr4

          The code associated with the Railroad selected in the 4th drop-down list for field I.7


          varchar(32)

          I.26

          SfxHscoRrid

          HSR Corridor ID Suffix


          varchar(4)

          I.2

          StateCD

          The code assigned to each U.S. States


          varchar(2)

          I.32.B

          StNarr

          State Narrative


          varchar(max)

          I.31.A

          StNarr1

          State narrative A


          varchar(256)

          I.31.B

          StNarr2

          State narrative B


          varchar(256)

          I.31.C

          StNarr3

          State narrative C


          varchar(256)

          I.31.D

          StNarr4

          State narrative D


          varchar(256)

          I.5

          Street

          Street or Road Name


          varchar(256)

          I.14

          Ttstn

          Timetable Station


          varchar(6)

          I.14

          TtstnNam

          Nearest RR Timetable Station


          varchar(256)

          I.21

          TypeTrnSrvcIDs

          Type of Train Service

          11 = Freight

          12 = Intercity Passenger

          13 = Commuter

          14 = Transit

          15 = Shared Use Transit

          16 = Tourist/Other

          varchar(32)

          I.17

          TypeXing

          Crossing Type

          2 = Private

          3 = Public

          varchar(1)


          12

          Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

          Document Revision Date: 02/12/2016

          Federal Railroad Administration Highway-Rail Crossing Division


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          I.25

          Whistban

          Quiet Zone

          0 = No

          1 = 24 hr

          2 = Partial

          3 = Chicago Excused

          varchar(1)

          I.25

          WhistDate

          Date Established (Quiet Zone)


          datetime

          I.24

          XingAdj

          Is there an Adjacent Crossing with a Separate Number?

          1 = Yes

          2 = No

          varchar(1)

          I.16

          XingOwnr

          Crossing Owner (RR ID)


          varchar(32)

          I.24

          XngAdjNo

          If Yes, Provide Crossing Number


          varchar(7)

          I.18

          XPurpose

          Crossing Purpose

          1 = Highway

          2 = Pathway, Pedestrian

          3 = Station, Pedestrian

          varchar(1)


          Created

          Date for which the original submission was created


          datetime


          CreatedBy

          Username of the user who originally submitted the records


          varchar(50)


          LastUpdated

          Date for which the existing submission was modified


          datetime


          LastUpdatedBy

          Username of the user who last submitted the updated records


          varchar(50)


        4. CI_OperatingRailroad

          Table 2-3: Fields in CI_LocationAndClassification

          This table contains 2 Primary Keys consisting of the OperatingRailroadCode and OperatingRailroadType that distinctly identifies that the record is a Multiple Forms Filed (MFF) record and the ReportBaseId that is a foreign key to the CI_ReportBase table capturing the Header information. It also captures data for Part II: Railroad Information information of the Grade Crossing Inventory Form (6180.71).


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          I.11

          Branch

          Branch or Line Name


          varchar(256)

          II.1.A

          DayThru

          Total Daylight Thru Trains


          numeric(3,0)

          II.7.A

          EMonitorDvce

          Event Recorder

          1 = Yes

          2 = No

          varchar(1)

          II.7.B

          HealthMonitor

          Remote Health Monitoring

          1 = Yes

          2 = No

          varchar(1)

          II.4

          IndustryTrk

          Number of Industry tracks


          numeric(2,0)

          II.1.E

          Lt1Mov

          Check if Less Than One Movement Per Day

          1 = Less Than One Movement Per Day

          2 = One or More Movements Per Day

          varchar(1)


          13

          Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

          Document Revision Date: 02/12/2016

          Federal Railroad Administration Highway-Rail Crossing Division


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          I.22

          Lt1PassMov

          Average Passenger Train Count Per Day: Less Than One Per Day

          1 = Yes

          2 = No

          varchar(1)

          II.4

          MainTrk

          Number of Main tracks


          numeric(2,0)

          II.3.B

          MaxSpd

          Typical Speed Range Over Crossing - MaxTypical Speed Range Over Crossing (maximum)


          numeric(3,0)

          II.3.A

          MaxTtSpd

          Maximum Timetable Speed


          numeric(3,0)

          I.12

          MilePost

          RR Milepost Number


          varchar(8)

          II.3.B

          MinSpd

          Typical Speed Range Over Crossing (minimum)


          numeric(3,0)

          II.1.B

          NghtThru

          Total Night time Thru Trains


          float


          OperatingRailroadCode

          Primary Key. The code associated with the Primary Operating Railroad


          varchar(32)


          OperatingRailroadType

          Primary Key. The type distinguishing whether the operating

          Railroad is the Primary, Operate a Separate Track, or Operate Over a Track

          Primary = Primary Operating Railroad

          Samerr = Operate Over Your Track at Crossing Seprr = Operate a Separate Track at Crossing

          varchar(10)

          I.22

          PassCnt

          Average Passenger Train Count Per Day: Number per day


          numeric(3,0)

          I.12

          PrfxMilePost

          RR Milepost Prefix


          varchar(3)


          ReportBaseId

          Foreign key to the CI_ReportBase table


          int

          I.9

          RrDiv

          Railroad Division or Region


          varchar(256)

          I.10

          RrSubDiv

          Railroad Subdivision or District


          varchar(256)

          I.12

          SfxMilePost

          RR Milepost Suffix


          varchar(3)

          II.6

          Sgnleqp

          Is Track Signaled?

          1 = Yes

          2 = No

          varchar(1)

          II.4

          SidingTrk

          Number of Siding tracks


          numeric(2,0)

          II.5

          SpselIDs

          Train Detection

          0 = None

          11 = Constant Warning Time

          12 = Motion Detection 14 = Other

          16 = AFO

          17 = PTC

          18 = DC

          varchar(32)

          II.1.D

          TotalLtr

          Total Transit Trains


          float

          II.1.C

          TotalSwt

          Total Switching Trains


          float

          II.4

          TransitTrk

          Number of Transit tracks


          numeric(2,0)

          II.1.E

          WeekTrnMov

          How many Trains per Week?


          float


          14

          Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

          Document Revision Date: 02/12/2016

          Federal Railroad Administration Highway-Rail Crossing Division


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          II.4

          YardTrk

          Number of Yard tracks


          numeric(2,0)

          II.2

          YearTrnMov

          Year of Train Count Data


          float


          Created

          Date for which the original submission was created


          datetime


          CreatedBy

          Username of the user who originally submitted the records


          varchar(50)


          LastUpdated

          Date for which the existing submission was modified


          datetime


          LastUpdatedBy

          Username of the user who last submitted the updated records


          varchar(50)


          CI_PhysicalCharacteristics

          Table 2-4: Fields in CI_OperatingRailroad


          This table stores information pertaining to Part IV: Physical Characteristics of the Grade Crossing Inventory Form (6180.71).


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          IV.8

          ComPower

          Is Commercial Power Available?

          1 = Yes

          2 = No

          varchar(1)

          IV.3

          Downst

          Does Track Run Down a Street?

          1 = Yes

          2 = No

          varchar(1)

          IV.6

          HwynDist

          Approximate Intersecting Roadway Distance (feet)


          numeric(4,0)

          IV.6

          HwyNear

          Intersecting Roadway within 500 feet?

          1 = Yes

          2 = No

          varchar(1)

          IV.2

          HwyPved

          Is Roadway/Pathway Paved?

          1 = Yes

          2 = No

          varchar(1)

          IV.4

          Illumina

          Is Crossing Illuminated?

          1 = Yes

          2 = No

          varchar(1)


          ReportBaseId

          Foreign key to the CI_ReportBase table


          int

          IV.1

          TraficLn

          Number of Traffic Lanes Crossing Railroad


          varchar(2)

          IV.1

          TraflnType

          Traffic Lane Type

          1 = One-way Traffic

          2 = Two-way Traffic

          3 = Divided Traffic

          varchar(1)

          IV.7

          XAngle

          Smallest Crossing Angle

          1 = 0° – 29°

          2 = 30° – 59°

          3 = 60° ‐ 90°

          varchar(1)


          15

          Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

          Document Revision Date: 02/12/2016

          Federal Railroad Administration Highway-Rail Crossing Division


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          IV.5

          XSurfaceIDs

          Crossing Surface

          11 = 1. Timber

          12 = 2. Asphalt

          13 = 3. Asphalt and Timber

          14 = 4. Concrete

          15 = 5. Concrete and Rubber

          16 = 6. Rubber

          17 = 7. Metal

          18 = 8. Unconsolidated

          19 = 9. Composite

          20 = 10. Other (specify)

          varchar(32)

          IV.5

          XSurfDate

          Crossing Surface Installation Date


          varchar(6)

          IV.5

          XSurfLength

          Crossing Surface Length


          numeric(3,0)

          IV.5

          XSurfWidth

          Crossing Surface Width


          numeric(3,0)

          IV.5

          XSurOthr

          Other Crossing Surface (description)


          varchar(256)


          Created

          Date for which the original submission was created


          datetime


          CreatedBy

          Username of the user who originally submitted the records


          varchar(50)


          LastUpdated

          Date for which the existing submission was modified


          datetime


          LastUpdatedBy

          Username of the user who last submitted the updated records


          varchar(50)


        5. CI_PublicHighway

          Table 2-5: Fields in CI_PhysicalCharacteristics

          This table stores information pertaining to Part V: Public Highway Information of the Grade Crossing Inventory Form (6180.71).


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          V.7

          Aadt

          Annual Average Daily Traffic (AADT) Count


          varchar(6)

          V.7

          AadtYear

          Annual Average Daily Traffic (AADT) Year


          varchar(4)

          V.10

          EmrgncySrvc

          Emergency Services Route

          1 = Yes

          2 = No

          varchar(1)

          V.2

          HwyClassCD

          Functional Classification of Road at Crossing: Rural or Urban

          0 = (0) Rural

          1 = (1) Urban

          varchar(1)


          16

          Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

          Document Revision Date: 02/12/2016

          Federal Railroad Administration Highway-Rail Crossing Division


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          V.2

          HwyClassrdtpID

          Functional Classification of Road at Crossing: Type of Highway/Roadway (ID)

          11 = (1) Interstate

          12 = (2) Other Freeways and Expressways

          13 = (3) Other Principal Arterial

          16 = (4) Minor Arterial

          17 = (5) Major Collector

          18 = (6) Minor Collector

          19 = (7) Local

          varchar(32)

          V.4

          HwySpeed

          Highway Speed Limit


          numeric(3,0)

          V.4

          HwySpeedps

          Highway Speed Limit: Posted or Statutory

          1 = Posted

          2 = Statutory

          varchar(1)

          V.1

          HwySys

          Highway System

          1 = (01) Interstate Highway System

          2 = (02) Other Nat Hwy System (NHS)

          3 = (03) Federal Aid, Not NHS 8 = (08) Non-Federal Aid

          varchar(2)

          V.6

          LrsMilePost

          LRS Milepost


          varchar(256)

          V.5

          LrsRouteid

          Linear Referencing System (LRS Route ID)


          varchar(256)

          V.8

          PctTruk

          Estimated Percent Trucks


          varchar(2)


          ReportBaseId

          Foreign key to the CI_ReportBase table


          int

          V.9

          SchlBsCnt

          Average Number School Bus Count per Day


          numeric(3,0)

          V.9

          SchlBusChk

          Regularly Used by School Buses?

          1 = Yes

          2 = No

          varchar(1)

          V.3

          StHwy1

          Is Crossing on State Highway System?

          1 = Yes

          2 = No

          varchar(1)


          Created

          Date for which the original submission was created


          datetime


          CreatedBy

          Username of the user who originally submitted the records


          varchar(50)


          LastUpdated

          Date for which the existing submission was modified


          datetime


          LastUpdatedBy

          Username of the user who last submitted the updated records


          varchar(50)


        6. CI_ReportBase

          Table 2-6: Fields in CI_PublicHighway

          This table stores the Header information, as well as the crossing records where the Primary Operating Railroad selected the value of Yes for field

          I.7 Do Other Railroads Operate a Separate Track at Crossing?. This means that the Primary Operating Railroad is listing up to four (4) additional railroads to submit their railroad and train count data. These submissions will be flagged with a value of 1 in the database, denoting that the record


          17

          Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

          Document Revision Date: 02/12/2016

          Federal Railroad Administration Highway-Rail Crossing Division

          is a Multiple Forms Filed (MFF) record. Detailed information regarding the table is described in the table below.


          Box No. on Form

          Column Name

          Description

          Field Values

          Type

          D

          CrossingID

          DOT Crossing Inventory Number


          varchar(20)

          D

          CrossingIdSuffix

          Crossing ID suffix


          varchar(20)


          MultipleFormsFiled

          Multiple Forms Filed (boolean)

          1 = Yes

          2 = No

          int


          ParentReportBaseId

          Unique ID for all crossings in this table


          int

          A

          PostmarkDate

          Submission Date


          Datetime

          C

          ReasonID

          Reason for Update

          14 = Change in Data

          15 = New Crossing

          16 = Closed

          19 = Re-Open

          20 = Date Change Only

          21 = Change in Primary Operating RR

          22 = Admin. Correction

          23 = Quiet Zone Update

          24 = No Train Traffic

          int


          ReportBaseId

          Foreign key to the CI_ReportBase table


          int


          ReportingAgencyID

          Reporting Agency ID


          int

          B

          ReportingAgencyTypeID

          Reporting Agency Type ID

          1 = Railroad

          2 = State

          3 = Transit

          4 = FRA Internal

          int


          ReportStatus

          The status of the submission

          Bulk Upload Error Cancelled

          Expired Pending

          Published

          varchar(20)


          ReportType

          Major or Minor Railroad (used for MFF)

          Major = Primary Operating Railroad Submitting the Full Crossing Inventory Record

          Minor = A Railroad agency submitting only the railroad and train count data

          varchar(20)

          A

          RevisionDate

          Revision Date


          Datetime


          ValidationErrors

          Stores all the error code(s) that failed validations


          varchar(4000)


          Created

          Date for which the original submission was created


          datetime


          CreatedBy

          Username of the user who originally submitted the records


          varchar(50)


          18

          Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

          Document Revision Date: 02/12/2016

          Federal Railroad Administration Highway-Rail Crossing Division


          Box No. on Form

          Column Name

          Description

          Field Values

          Type


          LastUpdated

          Date for which the existing submission was modified


          datetime


          LastUpdatedBy

          Username of the user who last submitted the updated records


          varchar(50)

          Table 2-7: Fields in CI_ReportBase


      2. Tables that Stores Lookup Values

        1. CI_Errors

          This table contains all the validation error messages that are returned by the system.


          Column Name

          Description

          Field Values

          Type

          Code

          Primary Key. Also the unique code assigned to each error.


          varchar(10)

          Description

          The description of each error message


          varchar(MAX)

          Section

          Stores the section name of the crossing inventory form

          Header

          Part I: Location and Classification Information Part II: Railroad Information

          Part III: Highway or Pathway Traffic Control Device Information

          Part IV: Physical Characteristics Part V: Public Highway Information

          varchar(100)

          ShowForMinorReports

          Indentify whether the error message should be returned for users submitting

          the short form (Railroad Data Only)

          0 = True

          1 = False

          int

          ShowForRailroads

          Indentify whether the error message should be returned for Railroad users

          0 = True

          1 = False

          int

          ShowForStates

          Indentify whether the error message should be returned for State users

          0 = True

          1 = False

          int

          SortOrder

          Stores the sort order number


          int

          Created

          Date for which the original error was created


          datetime

          CreatedBy

          Username of the user who originally created the records


          varchar(50)

          LastUpdated

          Date for which the existing error was modified


          datetime

          LastUpdatedBy

          Username of the user who last modified the record


          varchar(50)

          Table 2-8: Fields in CI_Errors


        2. CI_Lookups


          19

          Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

          Document Revision Date: 02/12/2016

          Federal Railroad Administration Highway-Rail Crossing Division

          This table contains all the valid values available when completing a crossing record.


          Column Name

          Description

          Field Values

          Type

          Code

          The unique code associated with each lookup type


          varchar(50)

          EndDate

          The date in which the item is no longer active


          datetime

          ID

          Primary Key


          varchar(50)

          LookupText

          Description of the lookup value


          varchar(500)

          LookupType

          The type of lookup


          varchar(50)

          LookupValue

          The value the lookup is stored as in the database (compared to what is displayed on the front-end UI)


          varchar(50)

          StartDate

          The date in which the item was made active


          datetime

          Status

          Active/Inactive

          0 = Inactive

          1 = Active

          bit

          Created

          Date for which the original submission was created


          datetime

          CreatedBy

          Username of the user who originally submitted the records


          varchar(50)

          LastUpdated

          Date for which the existing submission was modified


          datetime

          LastUpdatedBy

          Username of the user who last submitted the updated records


          varchar(50)


        3. Lp_Reason

Table 2-9: Fields in CI_Lookups

This table stores all the available values that will populate the drop-down list field in Box C. Reason for Update of the Grade Crossing Inventory Form (6180.71).


Column Name

Description

Field Values

Type

ReasonID

Primary Key for this table


tinyint

Code

Unique code assigned to each item in this table


varchar(3)

Descr

The description of each reason for update


varchar(50)

StartDate

The date in which the item was made active


datetime

EndDate

The date in which the item was made inactive


datetime

SortOrder

Username of the user who originally submitted the records


int

ShowForMinorReports

Indentify whether the error message should be returned for users submitting the short form (Railroad Data Only)

0 = True

1 = False

int

ShowForRailroads

Indentify whether the error message should be returned for Railroad users

0 = True

1 = False

int

ShowForStates

Indentify whether the error message should be returned for State users

0 = True

1 = False

int


20

Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

Document Revision Date: 02/12/2016

Federal Railroad Administration Highway-Rail Crossing Division

Table 2-10: Fields in Lp_Reason



21

Grade Crossing Inventory System (GCIS) v2.4.0.0 Data Dictionary for External Use

Document Revision Date: 02/12/2016