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. Excel File PDA Website FB-Deep 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: … <Pile ID=“32”> <Driving> <Drv_Date>5/5/1987</Drv_Date> <R_Energy>16.2</R_Energy> </Driving> </Pile> … • 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. Structure root is project Currently: Bridge Subsurface (holes) Future possibilities: Walls Roadways Site Data Schedule Possible Future Additions

14. Hole data includes: SPT, DMT, PMT, CPT Lab test data Subsurface High Level Schema

15. Project has multiple: Bridges, Piers, Piles/Shafts Test results Bridge Data High Level Schema

16. Detailed High Level Schema

17. Data Structure - Example 1 Project can have many Bridges

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