Systematic Approach to Scrub BMP Data for a Successful Upload to VA DEQ BMP Warehouse

The Best Management Practice (BMP) Warehouse is an application used by DEQ to maintain a centralized database for municipal separate storm sewer (MS4) permittees to upload their non-point source BMP data for state and federal reporting purposes. With the likelihood that the requirement to upload comprehensive BMP data into the Warehouse will be included in its Phase I MS4 permit, the City of Chesapeake (City) proactively evaluated their BMP (facility and inspection) data to prepare a comprehensive database in a format compatible with the City’s geographic information system (GIS) schema and the Virginia Department of Environmental Quality (DEQ) BMP Warehouse.

The BMP Warehouse template prescribes a list of fields that must be complete and formatted according to the Warehouse requirements prior to upload. After an initial review to compare the Warehouse reporting template and the City BMP record, the City developed strategies to fill data gaps in the required fields. Depending upon the data type and available information, multiple techniques were used to complete the required portions of the Warehouse BMP template. Some examples of the methods adopted to complete each record include assumptions and estimation techniques varying from creating new unique identifiers, assuming a conservative BMP type (Level I) when BMP type was not originally recorded, and assigning latitude and longitude based on the site address for records without geographic coordinates.

In addition to filling the data gaps, the City needed to identify which BMPs to upload to the BMP Warehouse. There are existing records in the BMP Warehouse database for BMPs in an MS4 jurisdiction from other sources, including records retrieved from Construction General Permit (CGP) database and BMPs on federal properties that are directly submitted by the federal agencies. The City performed a comparison of existing Warehouse records and sites covered by a CGP with its completed BMP record to avoid duplication in the Warehouse once data is uploaded.

This presentation will walk through the various steps involved in developing a complete dataset that will pass the validation test to be successfully uploaded into the BMP Warehouse. The presentation will also provide some strategies to address data gaps and provide an overview of some recent updates made to the Warehouse upload process.