Sewer condition assessment - GIS database without introducing processing errors
Database Management and Use
Establish Database Design
GIS Southwest, Inc. (GISSW) had the task of bringing data from a variety of sources together
into a coherent database structure. The goals of the database were several. First, the database
must be compatible with the COP GIS database. The COP database was provided at the
beginning of the project. Second, the database must incorporate the field data collected by Hoffman Southwest, Inc. (ProPipe). The ProPipe data was collected into a Microsoft Access
database running behind an application called Flexidata TM which is produced and distributed by
PearPoint, Inc. Third, the database must incorporate manhole inspection data collected in the
field outside of the Flexidata application. To this end, GISSW designed a manhole inspection
database including an input form. This database was designed based on input from the other team
members in terms of the data that were needed and in terms of integration with the final
database. Fourth, the database had to be dynamic in nature to accommodate the evolving needs
of the project and had to provide the flexibility to incorporate analysis parameters such as pipe
defect scoring and impact analysis factors. Finally, the database had to provide a useful and
efficient tool for this project, as well as for the COP to conduct future analysis.
PearPoint Flexidata TM Conversion Requirements
The initial database provided by the COP was an ArcView shapefile containing sewer lines from
15-inch to 42-inch in diameter within the COP. This database contained information about each
line segment including upstream and downstream manhole numbers, pipe age, pipe slope, pipe
length, pipe diameter, and others.
The Flexidata database was provided on a periodic basis by ProPipe as data came in from the
field. Generally, data were transferred to GISSW on a weekly basis. The Flexidata database is a
Microsoft Access database comprised of numerous related tables. Each week, the data collected
during the previous week were sent to GISSW via e-mail. GISSW then processed the weekly
data using a group of Avenue scripts to conduct QA/QC and to append the weekly data to the
project database. The data contained within the structure of the Flexidata database had to be
parsed to pull out the specific data items that were needed for this project. Of twelve tables
contained in the Flexidata database, data from only five tables are being used for this project.
The manhole inspection database was designed to collect and store data about the condition of
manholes in the system, the flow direction through the manhole and grit depth. The team
collectively identified the data items needed to provide the proper information about each
manhole for future analysis to facilitate repair and rehab needs. In order to enable incorporation
of the manhole inspection data into the main database, GISSW designed an input form that the
field crews could use and data entry personnel could subsequently use to populate the database.
Each of the pieces described above keyed on the upstream and downstream manhole numbers as
the link to the GIS database. The pipe segments throughout the database structure are identified
by their upstream and downstream manhole. The manholes are uniquely identified by an eight-
digit number, comprised of the quarter section map number and the assigned manhole number
within that quarter section.
The final step in the database design/data conversion tasks was to incorporate all the pertinent
data into the GIS database originally provided by the COP. This was accomplished with an
Avenue script that joins the Access database tables to the ArcView attribute table using the
upstream and downstream manhole numbers as the key fields for the join. The resulting database
enables GIS functionality to visualize and analyze the data. The user is then able to click on
individual defects along a line segment to get information about that defect and see a picture of the defect as captured by the CCTV team. The user can identify patterns of defect type and
severity, can get pipe segment score and impact factors, and can see manhole condition. The user
is also able to conduct spatial analysis using GIS tools to determine the relationships between the
various factors.
Data Flow
ProPipe provided the CCTV inspection and manhole observations for the project. The CCTV
inspections were saved using PearPoint Flexidata software. The manhole observations are used to
eliminate the majority of manholes that may require further inspection.
GISSW obtained the raw data for the CCTV inspections from ProPipe and applied the
information as a theme to the COP Water Department GIS database. The COP distributed a copy
of the database at the beginning of the project with the knowledge that all adjustments to the
database will be made at the conclusion of the project. GISSW added the scoring based upon the
field generated defect coding and defect severity.
URS reviewed all CCTV tapes generated by ProPipe and made adjustments in the database as
required. URS returned the database, with the modifications, to GISSW to include on the COP
GIS database.
GISSW assigned the impact factors, using GIS. The line segments would be sorted for debris/grit
defects and pipe defects. The debris/grit pipe segment list was used to determine debris problem
areas. The pipe segment list was used to compile a CIP for the repairs/rehabilitation of the
system.
