About the author: Brian K. McMahan is drainage specialist for Charlotte Storm Water Services. McMahan can be reached at 704.336.7035 or by e-mail at [email protected].
Brian K. McMahan
undefinedCharlotte’s Storm Water Services was established in 1993 when the city acquired its first National Pollutant Discharge Elimination System permit.
The utility was set up to address problems caused by storm water runoff from public streets. Its goal is to provide the community with a quality storm drainage system that is safe, clean and cost-effective.
Each project begins with a request for service and is then qualified by an investigator. Once a request has been qualified, it is assigned to a watershed work area and a drainage specialist determines the scope of work. Consent is obtained from affected property owners, and the project is designed, constructed and completed.
Storm Water Services’ drainage specialists had different ways of designing and producing project documentation. In order to bring the best of each specialist’s methods together, a system with the ability to track all aspects of work geographically was needed.
Microsoft Excel Workbook
The spreadsheets that make up Charlotte’s project-tracking workbook already existed. Various copies of the original, however, were in use. These inconsistencies, along with variations in the mode of submission, led to confusion and inaccuracies in data collection.
With the educational and system tools available, staff began working to establish a standard project-tracking workbook. Spreadsheets with the ability to open, plan, design and close a project were compiled. One benefit immediately identified by users was that primary data is entered only once before macro populates the additional spreadsheets.
The project-tracking workbook then automatically generates construction documents, including a work order, anticipated project quantities, notice to proceed, time of concentration data, environmental impact information and pipe installation cut sheets. These documents are not only generated but printed in the quantity needed for project completion.
The final sheet in the workbook, the closeout form, provides the information for tracking and assessment of a project. The closeout form includes the requests for service that are closed, the parcel identification numbers where the project was completed, easement requirements, project cost and the hard structures used in construction. The status of the GIS asset inventory is also tracked; this data is crucial to the storm water data set.
When this information is entered in its entirety, the closeout sheet and supporting documentation are printed and distributed as indicated. The user is prompted to save the document under a unique name, and information from the closeout sheet is sent electronically in database file III (dbfIII) format to the GIS staff. GIS staff members save these records, which are compiled monthly into one database file and quality-checked for data completeness. The resulting database file becomes the basis for the project-tracking data set.
This system became the official project work, tracking and submission tool for Charlotte Storm Water Services in 2000. Staff began to see consistency, accuracy and ease in reporting with the new database.
ESRI ArcMap Shape Files
Once the data is compiled in the Excel workbook, the parcel identification, request number and starting/ending date fields are quality-checked for population and correctness. If errors or omissions are found, the record is removed from the database, corrected and resubmitted. The worksheet is then saved as a database and queried for completion and, again, errors.
When the record is complete, it is e-filed in dbfIII format and saved in the master database directory. GIS staff members then open the file in Microsoft FoxPro to check the parcel information numbers for correct formatting. With these steps complete, the data is resaved as a database file and is ready to be added to the Mecklenburg County parcel data set.
In ArcMap, the parcel feature class is added to the project from the Enterprise SDE server. A join is then made from the parcel table to the newly formatted project-tracking table. The join to the county parcel layer is done through the common parcel identification number field in the county layer and the additional parcel identifier in the project-tracking database. Only parcels that are identified in the project-tracking database are kept in the join process, and these are exported to a new shapefile labeled with a creation date.
The latest shapefile is submitted to the drop zone to be updated on the Enterprise server and plotted for a wall map. Project tracking is attached to Storm Water Services’ “Add Common Themes” tool bar, an easy menu kept current by GIS staff for users to select and grab data without having to surf multiple data access points.
The maintenance team project-tracking layer is available for continued analysis with all other Enterprise data such as construction projects, parcels and road resurfacing. This information allows Storm Water Services to plan future work based on easements acquired through existing projects, save money by working with other city entities that have projects in the same area and assess trends to identify a larger problem that may exist.
Before the project-tracking data was set, many files and individuals’ memories had to be queried in an attempt to identify flooding patterns and previous actions taken. With project tracking, the facts of who, what and when can be pulled instantly to efficiently plan projects and assist in forecasting the future workload. Project tracking provides the city of Charlotte with a clear understanding of where its storm water fees are being spent and who is benefiting from the storm water utility.
Reaping Tracking Benefits
The Government Accounting Standards Board (GASB) establishes financial accounting and reporting standards for state and local governments. As part of the GASB reporting, Storm Water Services submits an account of hard-structure inventory items—a report of the amount and value of all tangible items installed throughout the fiscal year. This report can be prepared in minutes from the current project-tracking database.
Storm Water Services receives data and information requests from various government entities as well as residents and businesses. Most inquiries are easily answered using project tracking. Responses can be provided verbally, in maps or by being exported to spreadsheets for further analysis.
With updates and feedback provided through the project-tracking data set, employees are able to adjust their workload according to goals. Contractor performance can be measured to ensure construction crew output and spending rates remain on target through the year. Project tracking is also used to forecast production quotas for departmental goals. By accurately tracking vital project information, staff can more accurately determine trends and workforce and budgetary requirements.
Charlotte’s project-tracking system is an ever-evolving program. With newer versions of software released daily and systems for data collection changing, opportunities for further development are identified. GIS solutions like project tracking are increasingly essential to managing and predicting the needs of Charlotte’s growing storm water services.
Download: Here
