Case Background
Kansas Department of Transportation’s (KDOT) Environmental Service Section (ESS) program manages tasks required by the National Environmental Policy Act (NEPA) for every transportation highway project let by KDOT. KDOT must receive clearance or approval from the regulatory agencies before proceeding with the purchase of right of way with federal funds. Once all tasks are complete KDOT’s NEPA requirements are met, the Federal Highway Administration (FHWA) will concur with the project’s environmental classification and right of way acquisition can begin. KDOT project had these unique challenges:
- The current process was accomplished utilizing a Microsoft Access relational database which had, over the years, had several data integrity issues
- Data from all of the thousands of existing projects had to be migrated
- The program had requirements for staff to work offsite and record pictures and notes on their mobile device
Implementation Overview
Scope IT was awarded the contract to keep track of all KDOT construction projects, associated environmental reviews, and track all permits through their lifecycle. Scope IT delivered a system that tracks all construction projects through all phases of environmental review including Noise, Air, Archeology, Historical, Native American, Wildlife, Farmland, Hazardous Waste, and Permit. The system keeps track of all communications, dates, and status designations to document the daily coordination with federal and state regulatory agencies.
Scope IT migrated all data by first creating staging tables within the new MySQL relational schema and transferred all existing tables using the Talend mapping tool. A mapping sheet was created documenting the mapping from the staging tables to the tables in the target schema. Migration scripts were developed, tested, and implemented. Data relating to over 20,000+ construction projects were migrated as part of this effort.
The system had several integrations with external systems.
- The system integrated with KDOT’s document management system, Hyland’s OnBase to store final status memos and associated project documents. The integration was real-time and was performed using secure FTP.
- The system integrated with KDOT GIS services web services using REST. This allowed users to search projects in the Kansas projects database, view the project on the map using the longitude and latitude, view project details and images.
- The system integrated with ArcGIS to provide several critical functionalities that the users utilized when they were visiting project sites.
- Customer can pull up the map and find the list of all projects in a geographic area
- Customer can click on a project in the map and get all of the details about the project
- Customer can click on a location and find the longitude and latitude of that location to enter into the database
- Customer can select a location on the map and add comments or pictures relating to a project that they can pull up later
- The system integrated with K-DOT’s Data Warehouse using secure FTP to support data warehousing efforts.
Results
KDOT now has a modern FedRAMP certified system to manage environment reviews and permits for all Kansas DOT construction projects. Scope IT has been awarded the O&M (Operations and Maintenance) contract for the last 4 annual option periods where Scope IT continues to support any issues and enhancements to the system.
