cctv data migration project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/d3... · cctv...
TRANSCRIPT
![Page 1: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/1.jpg)
CCTV Data Migration Project
Charlotte WyattAlex Wojcik
Courtney MacMullenGIS Technicians, SEG Consulting
![Page 2: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/2.jpg)
About the Project• Planning, prioritizing inspection areas• Accessibility and maintenance of data• Manual data entry
![Page 3: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/3.jpg)
About UsSimplifying Complex Information is What We Do
• Automation
• Usable data formats
![Page 4: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/4.jpg)
Project Scope• Extract data from CCTV pipe inspection reports• Process ~1,800 PDFs within three months• Develop an automated solution
![Page 5: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/5.jpg)
Survey Data
Site Data
Pipe Data
Observation Data
Condition Ratings
![Page 6: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/6.jpg)
![Page 7: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/7.jpg)
ApproachGIS provides a central location to store, manage,
and disseminate data
![Page 8: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/8.jpg)
GIS for Sewer Data• Central location for asset data• Increased accessibility of CCTV data• Access to condition ratings, inspection dates• Monitoring of asset lifecycle and cost
![Page 9: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/9.jpg)
Methodology• Python is a powerful programming language• Leverage ArcPy (interaction with Python and ArcGIS)• Third party modules augment automation• Loop through PDFs to extract data• Migrate into optimized geodatabase
![Page 10: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/10.jpg)
1,800 PDF
Reports
Define Report
Template
Extract Report
Components
Survey Data
(line)
Validate
(QC)
Observation
Data (points)
![Page 11: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/11.jpg)
Define Report Style• Report layout varies• Inconsistent location of text• Trial and error testing
![Page 12: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/12.jpg)
1,800 PDF
Reports
Define Report
Template
Extract Report
Components
Survey Data
(line)
Validate
(QC)
Observation
Data (points)
![Page 13: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/13.jpg)
Text CoordinatesDetermine text coordinates within PDF using GhostScript
![Page 14: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/14.jpg)
1,800 PDF
Reports
Define Report
Template
Extract Report
Components
Survey Data
(line)
Validate
(QC)
Observation
Data (points)
![Page 15: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/15.jpg)
Add Data to Layers• Attribute table for point feature class:
![Page 16: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/16.jpg)
1,800 PDF
Reports
Define Report
Template
Extract Report
Components
Survey Data
(line)
Validate
(QC)
Observation
Data (points)
![Page 17: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/17.jpg)
Linear Referencing• Find matching manhole IDs to locate pipe feature• Place observation points along the pipe line• Give incident data a spatial reference
![Page 18: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/18.jpg)
Linear Referencing
![Page 19: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/19.jpg)
Symbology• Observation points symbolized according
to NASSCO PACP defect codes• Direction of survey indicated by arrows
![Page 20: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/20.jpg)
1,800 PDF
Reports
Define Report
Template
Extract Report
Components
Survey Data
(line)
Validate
(QC)
Observation
Data (points)
![Page 21: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/21.jpg)
Recommendations• Ensure validity of manhole IDs• Create consistent report template structure• Save edited reports as text-based PDFs
![Page 22: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/22.jpg)
The Solution• Increases accessibility of data• Reduces manual data entry• Improves cost-efficiency and productivity• Promotes monitoring and maintenance of assets
![Page 23: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/23.jpg)
Tying it all Together• What does a PDF report look like once it is in GIS?• How would an Asset Manager use the data?
![Page 24: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/24.jpg)
Fraser St July23 2010.pdf
Dartmouth, NS
![Page 25: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/25.jpg)
Header Fraser St July23 2010.pdf
Footer
Pipe Data in Report
… becomes associated to line feature
![Page 26: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/26.jpg)
Pipe Data in GIS
Condition Ratings
Upstream MHCB14054
Downstream MHCB14055
Attribute Table:
![Page 27: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/27.jpg)
CCTV Data in Report
… becomes associated to point feature
![Page 28: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/28.jpg)
CCTV Data in GIS
First observation at0.0 metres
Attribute Table:
1.74 m3.47 m
0 m
![Page 29: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/29.jpg)
Second observation at1.74 metres
1.74 m3.47 m
0 m
CCTV Data in GISAttribute Table
![Page 30: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/30.jpg)
Using the Data
Fields can be sortedE.g.: Pipes sorted by condition rating or by date surveyed
![Page 31: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/31.jpg)
SQL queries can be made on fields to find specific dataGIS affords an added level of intelligence, spatial analysis is possible
Search by PDF name
Search by street
Search for pipes that have a Quick Structural Rating not equal to 0
Search by manhole ID
Using the Data
![Page 32: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/32.jpg)
Results93.4% of PDF reports were successfully processed
• 11,317 pipe lines• 92,610 observation points
![Page 33: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/33.jpg)
% of records “on the map”:• 81.2% Pipe lines• 85.7% Observation
points
Results
“Missing” records:• No matching manholes
![Page 34: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/34.jpg)
• Quantity and comprehensiveness of data obtained• Time efficient solution:
• Three months of script developmentvs. years of traditional manual data entry
• Great return on investment• Data now exists in GIS format!
Achievements
![Page 35: CCTV Data Migration Project - cnam.cacnam.ca/wordpress/wp-content/uploads/pediadocs/D3... · CCTV Data Migration Project Charlotte Wyatt Alex Wojcik Courtney MacMullen GIS Technicians,](https://reader031.vdocuments.us/reader031/viewer/2022030418/5aa41e2f7f8b9ae7438b95ec/html5/thumbnails/35.jpg)
Courtney MacMullenSEG ConsultingGIS [email protected]
Alexander WojcikSEG ConsultingGIS [email protected]
Charlotte WyattSEG ConsultingGIS [email protected]
Questions