Skip to main content

CCTV metadata files

We have received four different file formats from utility so far:

1. Access Database (`.mdb`) (DNV and SD1)
2. Database file (`.dbf`) (DNV)
3. Excel file (`.xlsx`) (COV)
4. pdf file (COV)

Access Database​

Important Tables​

  1. PACP_Conditions or Conditions table (depend on utility) contains the condition data (information regarding defect and location of the defect)
    1. Important columns:
      1. InspectionID: CCTV video unique id
      2. Distance: location of the defect
      3. Continuous: continuous defect column
      4. PACP_Code or Code: the defect code
      5. Value_Percent: the value in percent. This is needed for water level model
  2. PACP_Media_Inspections table is the cross reference table between Inspection ID and Video_Name.
    1. Important columns
      1. InspectionID: CCTV video unique id
      2. Video_Name: name of video file (example <pipe_id>_<sheet_number>.mp4)
        1. For the DNV access database, there is a discrepancy between the video name in the access database and the actual video file name. For example, the video SANMN01751_41.mp4 is corresponding toSANMN01751_39.MP4 in the access database.
  3. PACP_Inspection table contains the information about the CCTV survey
    1. Importance columns
      1. InspectionID: CCTV video unique id
      2. Pipe_Segment_Reference: pipe id
      3. Pipe_Use: type of pipe
      4. Height: pipe's diameter in mm
      5. Shape: pipe's shape
      6. Material: pipe's material
      7. Total_length: pipe length
      8. Length_Survey: total length of survey
      9. Year_Constructed
      10. Upstream_MH: name of upstream manhole
      11. Downstream_MH: name of downstream manhole
      12. Direction: Direaction of inspection

Create Metadata csv for image labelling​

  1. In the access, click Create tab -> Query Wizard

  2. Choose Simple Query Wizard -> OK

  3. Select the following fields:

    1. From PACP_Media_Inspections
      1. Video_Name
    2. From PACP_Conditons
      1. Distance
      2. Continuous
      3. PACP_Code or Code
      4. Value_Percent

  4. In the access, click External Data -> Excel

  5. Enter file name and location -> ok

  6. Convert the file to csv file

  7. If utility has more than one DBs, concatenate multiple csv files into a single csv.

  8. Standardize the condition metadata csv

    1. Rename the following column names
      1. Video_Name -> cross_reference_id
      2. PACP_Code -> Code

  9. Match the cross reference id to the video file and add the information to the list of video csv located in <utility>/Data/Video_Lists/. Note: For the DNV, there is mismatch between the video file and the name entered in the access database.For example, the video SANMN01751_41.mp4 is corresponding toSANMN01751_39.MP4 in the access database.

    1. Import a CCTV metadata csv as a dataframe
    2. Create an additional column pipe_id by extracting the pipe_id from the cross_reference_id column (SANMN01751_39.MP4 -> SANMN01751)
    3. Import a list of video csv as a dataframe
    4. For each video from the list of video dataframe, extract pipe id from video file name (SANMN01751_41.MP4 -> SANMN01751) and match the pipe id to CCTV metadata pipe_id
    5. If match is found, assign the value from cross_reference_id to the video
    6. If the video have more than one match, manually check the condition data and the video footage and assign the correct cross_reference_id to the video

Create CCTV inspection metadata csv​

  1. In the access, click Create tab -> Query Wizard
  2. Choose Simple Query Wizard -> OK
  3. Select the following fields:
    1. From PACP_Inspection
      1. All field (including pipe material, size and shape...etc)
    2. From PACP_Media_Inspections
      1. Video_Name
  4. In the access, click External Data -> Excel
  5. Enter file name and location -> ok
  6. Convert the file to csv file

DBF files​

  1. .dbf file can be opened using access.

  2. CCTV_Details is equivalent to PACP_Condition table in the access database (Note: not all codes inside CCTV_Detail follow the PACP standard)

  3. CCTV_Headers is equivalent to PACP_Inspection table in the access database

  4. JOBNUMBER column in CCTV_Details and CCTV_Headers is equivalent to Video_Name in the access database. It is used to link the video file name.

  5. The data in CCTV_Details is sorted by OBJECTID. The defect code was not sorted by DISTANCE and JOBNUMBER. To sort the data in the right order, you need to sort the table by JOBNUMBER, then by DISTANCE, and sorted by CODE.

  6. In the access, click Create tab -> Query Wizard

  7. Choose Simple Query Wizard -> OK

  8. Select the following fields:

    1. From CCTV_Details
      1. JOBNUMBER
      2. DISTANCE
      3. CONTINOUS
      4. CODE
      5. PERCENTAGE

  9. Convert the file to csv file.

  10. If utility has more than one DBs, concatenate multiple csv files into a single csv.

  11. Standardize the condition metadata csv.

    1. Rename the following column
      1. JOBNUMBER -> cross_reference_id
      2. CODE -> Code
      3. DISTANCE -> Distance
      4. PERCENTAGE -> Value_Percent
      5. CONTINOUS -> Continuous

  12. Match the cross reference id to the video file and add the information to the list of video csv located in <utility>/Data/Video_Lists/.

    1. Some cross_reference_id include the file extension of the video (eg SANMN05599_57.MP4, SANMN04042_467.MPG). However, most of them found the be incorrect file extension. Therefore, only use the video file name (without file extension) to match the CCTV video file with cross_reference_id.
    2. However, we will enter the original cross_reference_id (with file extension if there is one) to the list of video csv. In this way, this value can be used to look up for condition data in both original CCTV_Details and the cctv metadata csv.

Excel Files​

  1. UBC Maters Program - AI - Inspection 2023-03-01.xlsx and Gravity Main Inspections All_Inspections_UBC_AI.xlsx:sheet are equivalent to PACP_Inspection table in the access database
    1. The video is named using the following format: <Inspection ID>-<LEGACY_ID>-<Inspection Direction>-<Inspection Date>.mp4
    2. For an example: 000019-__FJBYYX-D-2023-01-05.mp4
      1. 000019 = Inspection ID or Reverse ID found in UBC Maters Program - AI - Inspection 2023-03-01.xlsx
      2. __FJBYYX = LEGACY_ID or Pipe ID
      3. D = Inspection Direction
      4. 2023-01-05 = Inspection Date
  2. Gravity Main Inspections All_Inspections_UBC_AI.xlsx:sheet 1 is equivalent to PACP_Condition table in the access database
  3. Inspection ID in Gravity Main Inspections All_Inspections_UBC_AI.xlsx is equivalent to cross_reference_id. This is different from Inspection ID in UBC Maters Program - AI - Inspection 2023-03-01.xlsx

Create Metadata csv for image labelling​

  1. Use Gravity Main Inspections All_Inspections_UBC_AI.xlsx:sheet 1 to create condition meta data csv

    1. Include and rename the following column and export the file as csv file.
      1. Inspection ID -> cross_reference_id
      2. Defect Code -> Code
      3. Distance -> Distance
      4. Value Percent -> Value_Percent
      5. Continuous -> Continuous

  2. Use report pdf file to get the condition data that is not found in Gravity Main Inspections All_Inspections_UBC_AI.xlsx:sheet 1

    1. Manually enter the new condition data to condition meta data csv.
    2. Enter the video file name for cross_reference_id columne.

  3. To match video file in the list of video CSV to Insepction ID in Gravity Main Inspections All_Inspections_UBC_AI.xlsx,

    1. Import the list of video csv as a datafarame.
    2. Create a temporary inspection_info column containing the following information <Pipe ID>-<Inspection Direction>-<Inspection Date> extracted from the video file name.
    3. Import Gravity Main Inspections All_Inspections_UBC_AI.xlsx:sheet (not sheet 1) as a dataframe.
    4. Create a temporary inspection_info column for each row. Use the following columns to get <Pipe ID>-<Inspection Direction>-<Inspection Date>:
      1. Pipe ID
      2. Direction
      3. Date
    5. Assign value from Inspection ID column to cross_reference_id column of the list of video csv dataframe if there is a matching inspection_info.
    6. Assign video file name to cross_refernce_id column of the list of video csv dataframe if a match is not found.
Last updated on by jacobgqc