# Presentation: 324904

##  Data Warehousing

** ****Data Warehousing**
** ****at**
** ****Maine DOT**
***The Long and Winding Road***
**December 2001**

## Since the 70’s - We had Road Attribute Data

**TINIS - ****Vintage yet Visionary Mainframe System**
***	******	*****almost everything you ever wanted**** ****to know about roads ****	****	****– *****Locationally Synchronized***
**	****Road Inventory - Designations, Widths, Speed Zones **
**Traffic, Pavement, Functional Classifications, etc.**
**Bridge Inventory **
**Project History**
**Accidents **
**RR Crossings**

*“**I know there’s a road here somewhere”*

## In the 90’s we had prepared 1:24k Centerlines to be our Basemaps

**Digitized 7.5 minute USGS maps from Office of GIS**
**Added MDOT Linear Referencing System **
**Added TINIS Node Number to**
**    ****Arc/Info nodes**
**Built Arc/Info Route System**
**    ****representing TINIS Links**

## We had GIS with statewide Basemaps linked to the attributes

**Linear Referencing**
**we could do event overlays **
**for Link/Node Based Data**
**for Route/Cumulative Mileage Data**

## Such as...          

**   ****Using**** ****Dynamic Segmentation**
**Point Events based on Link Offset**
**  ****Accidents, Bridges, RR Crossings****	****	**
**Point Events based on Route Milepoint **
**  ****Culverts, Struts, Hot Mulch**
**Linear Events - ****Construction**** ****Projects, Guard Rail**

## We thought we had it made...

**But...**
**Dynamic Segmentation was very slow to use**
**Much of our data was not normalized so that dynamic segmentation could not be applied easily**
**Applications needed to be built for most people to take advantage of our GIS **

## The TIDE Project was conceived as:

**A tool combining data warehouse & GIS technology which would:**
**Enable ****system-wide analysis**** and reporting**
**Provide ****wider user access**** to reliable information**
**Be ****easy to use****, administer and maintain**
**Accelerate**** the Department’s ****GIS**** efforts**
**Integrate data**** from different systems for broader analysis**

**Notes:**
Supplement to existing operational systems (information may be more easily extracted and displayed)
Robust querying and reporting functions to answer/address strategic transportation questions
Cross-correlation between data in different operational systems
Provide better access to information stored in various/numerous DOT operational system (highway information system, pavement, project history, etc.)

## TIDE- a GIS Linked Data Warehouse

**TINIS DATA**

**PMS DATA**

**Ad-hoc Queries**

**Highway Needs & Adequacy**

**Highway Capacity**

**Highway Safety**

**Bridges**

**Average Speeds**

**Project History**

## Constraints we were trying to solve

**Custom programming required to obtain needed information**
**Highly reliant on a single programmer**
**Data not always in readily useable format**
**Databases maintain primarily current data **
**Trend analysis difficult**
**Current system focus on individual highway components not system wide analysis**

## Legacy Look and Feel

## What does TIDE provide?

**Easy to use Query and Reporting Tool**
**BI-Query accessing Oracle tables**
**Results Mapped with Customized Views and Layouts**
**ArcView**
**Access to Historical Data**
**Metadata**
**Users can choose data suitable for a particular use and understand data limitations**
**Aids in the development of ad hoc queries**** **
**On-line, context-sensitive Help**

## Querying

**Simple and complex**
**Standard (push button, usually only a couple of prompts)**
**Template (select a previously developed complex query and modify it)**
**Ad hoc (pick whatever data you want but you do need to understand the relationships between the objects)**

- ?

- ?

- ?

- ?

**Notes:**

Can Query on any Field or Combination of Fields

Can Format Result into a Report, Map or Insert into a Spreadsheet, etc.
Can Save Query to Run Again 

Will also have Pre-Built Queries
Highway Needs and Adequacy
Highway Safety
etc. 

## TIDE BI-Query Look and Feel

**Notes:**

Can Query on any Field or Combination of Fields

Can Format Result into a Report, Map or Insert into a Spreadsheet, etc.
Can Save Query to Run Again 

Will also have Pre-Built Queries
Highway Needs and Adequacy
Highway Safety
etc. 

## Why a GIS-linked DW?

**Problem:**** inadequate access to agency data**
**Open up needed data to a wider community**
**Integrate the locational side of data**
**An estimated 80% of data in a DOT have location as a primary key for reference**
**Primary Objective:**** establish an integrated environment where data can be accessed the way transportation agency professionals think!**

**Notes:**
Graphical and Non-graphical Integration

Need to establish GIS links to the DW
Key method is through LRS coding in the GIS data
Will enable disparate data sets (with no common fields) to be integrated for analysis and display based on their location
Bitmaps and join indexes aid in performance  (also use incremental indexing)
Use indexes to insure that each query can be resolved without scanning entire tables
Indexes essentially amp column values of rows in a single table (an ID)
Can be an integral part of the DW
Location, geometry, and topology are essential data attributes
Compare, interpret, and analyze data based on spatial characteristics
Results: 
possible to discern patterns, trends, and distribution that would not be evident otherwise
Visualization tool for more persuasive presentations

## Geographic Component

**Query Results can be Displayed on a Map**
**Show all roads designated with FFC of collector road or above **
**    ****with a jurisdiction of town way**
**Show all roads with AADT > 2000 that **
**	****do not meet state standard widths**
**Can Ask Spatial Queries**
**List the Transportation Improvement Program projects by Legislative District**
**Identify all high crash locations on the NHS in MPO 4; which ones have PCR below 2.5?**
**Show all bridges in fair to poor condition where backlog, multiple crashes, or low PCR conditions exist within a tenth of a mile.**

## TIDE ArcView Look and Feel

## Linear Referencing in TIDE

**Principal means of integrating roadway-related information**** **
**Consists of the link/node and route/milepoint systems**** **
***S*****pecify roadway locations (or sections) by multiple, familiar means, for both querying and reporting:**
**Entire route (numbered, inventory road, maintenance, or superlink)**
**Route / begin_milepoint / end_milepoint**
**Link IDs (county, low node, high node)**
**Link / begin_offset / end_offset**
**Route / begin_node / end_node (by node ID or node description)**
**Project Identification Number (PIN) **
**Node point**

## TIDE Location Data Storage

_**Stored**_** by link/node system instead of route milepoint (RLM) :**
**Less impact than RLM when updates are made to linear referencing for managing historical data**
**Enables features (e.g., crashes) to be returned by queries of any route that passes over a stretch of road *****- - If the crash was stored by RLM, it would only “appear” on the route used to reference its location***
**Corresponds well with static segmentation model**

## Why Static Segmentation? 

**Static segments created each week using dynamic segmentation**** **
**Used static segmentation to enable ad hoc querying in the BI-Query environment**
**Dynamic segmentation would have precluded this as the SQL needed to overlay event tables (could not be automated in BI-Query)**
**Anticipate moving to dynamic segmentation as technology improves**

## Benefits of TIDE

**Easy access to information**
**Single button query and reporting capabilities**
**See data at any level (from single road segment to statewide summaries)**
**Users can develop, perform and save ad hoc queries**
** ****(no programmer needed)**** **
**GIS analytical capabilities (full ArcView functionality)**
**System is expandable to pull in more datasets and promote integration of data in subsequent phases **
**Historical data available for trend analysis**

## Some Broader Benefits

**Management and staff alike have a powerful, flexible, easy to use doorway into our data**
**From data we now have information**
**Data that can be seen, can be improved**
**Better understanding of our transportation system data which translates into new ways to serve the public, etc.**

## Costs

**Besides the tangible contract price**
**Staff time to participate, download data, review comment and revise documents, walkthroughs**
**Software licenses for Oracle, GQL and ArcView**
**Making changes to source systems**
**Upgrades to PCs**
**New staff responsibilities to administer TIDE**

## We thought we had it all now...

***However*****...**
**We could not add linear data from other databases because their LRS’s were not synchronized**

## Phase I Limitations, Examples:

**Cannot track locations of active projects**
**When analyzing high accident locations, signing and signaling is unknown**
**Cannot confidently include culvert or guardrail location and condition information when analyzing candidate projects**

## The Linear Referencing dilemma

**Nodes:****	****A****	****	****B****	****	****C**
**Links:****	****	****AB****	****	****BC**

**Route:****	****0.0****	****0.9****	****1.5****	****2.0 Miles**

**Project:**

**Project Location by Link - Node:**	_Link_	_Offsets_
- 	AB	0.0 - 0.9
- 	BC	0.0 - 0.6
**Project Location by Route-Milepoint:**	_Route_	_Milepoints_
- 	US 2	0.0 - 1.5

## Why Synchronization is important – Linear Network Changes

**Nodes:****	****A****	****	****B****	****	****C**
**Links:****	****	****AB****	****	****BC**

**Project:**

**The project is now located on 2 new links**

**BEFORE:**

**AFTER:**

**Nodes:****	****A****	****	****B****	****	****D****	****	****C**
**Links:****	****	****AB****	****	****BD****	****	****CD**

## Road Data Not in TIDE

- Examples :
**Portions of BMS, BTIP & SMS **
**Project information**
**Culverts, guardrail, Signs, signals**
**ROW permits **
**Maintenance Activities**

## Non Road Data also maintained by MDOT but not in TIDE yet:

**Rail **
**Airports **
**Ferry Routes **
**Truck Routes**
**Ports**
**Trails **
**Bus Routes - future**

## What MDOT or TIDE still needed to Address

**Provide a common linear referencing ****engine (synchronizer)**** to be used by all systems in the department**
**To integrate, accurately, more data than TINIS and PMS**
**To more correctly maintain historical data location references**

## Location Referencing Synchronizer will:  

**manage several linear referencing methods for the same network.**
**translate across LRMs**
**keep databases’ location references up to date **
**understand changes in network over time and can roll back**
**include non road features****  **

## TIDE- Phase 2

**Municipal Database**

**Truck Classification**

**Turning Movements**

**Project **
**Tracking**

**Project Planning**

**ProjEx DATA**

**BackLog DATA**

**ARAN Image Viewer**

## TIDE: Phase 2

**Location Referencing Management System**
**Synchronizer Needed to Integrate Databases**
**Moving toward Exor Network Manager**
**Advanced functionality**
**Viewer for VideoLog Images**
**More Layout Choices in ArcView**
**SQL Queries Directly from ArcView**
**External users **
**MPOs, COGs, Other State Agencies**

## TIDE: Phase 3

**Will primarily be a migration of TINIS to an Oracle Spatial environment **
**Will add data to the data warehouse**
**Probably will move TIDE into Web-based environment**

## Questions?