Development of a Web Based Design, and Construction Bridge Substructure Database

1
Development of a Web Based Design, and
Construction Bridge Substructure Database

• Mike McVay, Marc Hoit,
• Erica Hughes, Thai Nguyen, and Peter Lai

2
Need for Unified Data Access

• Most existing data is in scanned form or paper
• Many states developing Geotechnical Database
• Access through internet
• Most using XML for outputting data
• Some efforts to standardize data representation
• TransXML, agsXML
• No unified schema
• No transparent access from applications

3
Database Standardization Benefits

• Savings from unified DB
• single entry of data
• reusable data
• QA/QC allows for software verification
• More accurate bids (using existing data)
• Time Reduction
• Reduce data transfer time and re-keying
• Accessibility
• Open standard
• Web entry and retrieval (with security)

4
Current DB System

• Data restricted by user security
• Upload and retrieve through XML file
• Web App controls conversion to DB format
• DB format is not seen by users or applications
• Application centric general access
• Developed to allow multiple XML standards
• Browser access to view, update or retrieve in
  Excel
• Data controlled by designated user
• Add users, create projects and data

5
XML Data Interchange Process

• Database is application centered
• Repository for project data
• Designed for using data in decision process
• Acts as archive
• Security is table based and hierarchical
• Access established by FDOT
• Built in ability for multiple XML standards

6
Application Centric Architecture

• Applications directly access data
• API (DLL) callable from any language
• Security enforced for all access

7
Extract or Submit any data fragment
User Authenticates
Excel Transfers XML Data File
XML Data is Translated into Database
Access Levels for User are identified View
security section for more details
XML Example Code ltPile ID32gt
ltDrivinggt ltDrv_Dategt5/5/1987lt/Drv_Dategt
ltR_Energygt16.2lt/R_Energygt
lt/Drivinggt lt/Pilegt

• XML Tags are Parsed and the Data is entered into
  the appropriate tables as long as the User has
  the appropriate Access Levels to do so.
• Any fields the User Does not have Access for will
  NOT be updated.

Only data for which the user has security is
available
8
Spreadsheet for Soil Property Access
Select pre-defined description items
Scroll between different Lab sets
9
Example As-Built Data Plot
10
FB-Deep Pile Design Using API
11
API Requires User Login (secure)
12
Returned Data Tree Based on User Security
13
Possible Future Additions

• Structure root is project
• Currently
• Bridge
• Subsurface (holes)
• Future possibilities
• Walls
• Roadways
• Site Data
• Schedule

14
Subsurface High Level Schema

• Hole data includes
• SPT, DMT, PMT, CPT
• Lab test data

15
Bridge Data High Level Schema

• Project has multiple
• Bridges, Piers, Piles/Shafts
• Test results

16
Detailed High Level Schema
17
Data Structure - Example
1 Project can have many Bridges
Table Projects Table Projects
ProjectID 3
Project_Name Port Orange IntraCoastal
Project_Number 79180-3514
Client FDOT
Consultant Sverdrup Corporation
Contractor Danis Industries Corporation
Comments There are no comments
Table Bridges Table Bridges
BridgeID 27
Bridge_Name Port Orange Bridge
ProjectID 3
Finiancial_Number 123A-45
Latitude 135
Longitude 20
Station 22110
Offset -11
State FL
County 50
Zipcode  
Client FDOT
Consultant Sverdrup Corporation
Contractor Danis Industries Corporation
PileShaft_Company Industrial Contracting Company (ICC)
PileTest_Company Schmertmann_Crapps, Inc.
Comments There are no comments
AsBuilt 1
AsBuiltID  
18
Security Structure ExampleMultiple levels of
approval and locking
19
Data Security Role BasedCan restrict access to
table and/or individual data

• A User Account can have Access to Mutiple
  Projects, Bridges, Piles, etc.
• User can be assigned different Access Levels
  (AccessID) per Project/Bridge/Pile/etc.

This example shows UserID 34 has different Access
Levels (AccessID) for 2 different Bridges of the
same Project
20
Data SecurityUsers can have different security
for each project
This example shows UserID 34 has different Access
Levels (AccessID) for 2 different Bridges of the
same Project
21
Summary

• Schema developed for Geotechnical Data
• Database has security built into the design
• Database can be expanded to add unlimited types
  of projects (roadway, walls, slopes )
• XML interaction allows applications upload and
  download
• Scalability is handled through standard database
  and web growth options