Problem/Question
Working with SQL, template editor and report designer requires deeper knowledge of the database structure as well as the fields to use for correct data input and output.
Where can I get this information from?
Solution/Answer
To define database fields one must first know which table they belong to. The following list contains the most important tables all the different kinds of data are saved into when working on a project in WinCanVX:
Table | Meaning |
PROJECT | Contains project header data |
JOB | Contains data linked to a specific job (usually 1 job/project) |
LEG | Contains all legs the pipes sections are assigned to (important for AUSTRIA; leg=Strang!) |
SECTION | Contains all pipe sections created within the current project |
SECINSP | Contains inspections created per section (usually 1 insp./section) |
SECOBS | Contains observations entered per section inspection |
SECOBSMM | Contains links to video clips and photos recorded/taken per section observation |
NODE | Contains all manholes created within the current project |
NODINSP | Contains inspections created per manhole (usually 1 insp./manhole) |
NODOBS | Contains observations entered per manhole inspection |
NODOBSMM | Contains links to video clips and photos recorded/taken per manhole observation |
NODPART | Contains parts (cover, access, chamber, bench) a manhole consists on |
NODENTRY | Contains all in- and outlets linked to a manhole |
CONTACT | Contains all addresses (i.e. clients, managers, contractors) that may be assigned to the current or future projects |
PARTICIPANT | Contains those addresses currently assigned to the project |
OPERATOR | Contains details to CCTV operators working for the current company |
EQUIPMENT | Contains details to CCTV trucks and camera systems used by the current company |
To work efficiently with the editor tools mentioned above just refer to the fields listed below. This table is focusing on important tables and database fields used in the bulk of the templates and reports:
Table | Field | Meaning |
SECTION | OBJ_SortOrder | Section counter: this value is automatically renumbered when sections are deleted |
SECTION | OBJ_Key | Section name: this field must contain a unique name to identify the pipe section located between the US and DS manhole |
SECTION | OBJ_Length | Length of the whole section in [m] OR [ft] |
SECTION | OBJ_Type | Type of pipe line: section (main pipe) or lateral |
SECTION | OBJ_City | Name of the city/town |
SECTION | OBJ_Street | Name of the street |
SECTION | OBJ_FromNode_REF<-Node.OBJ_Key | Name of the Upstream (US) manhole |
SECTION | OBJ_ToNode_REF | Name of the Downstream (DS) manhole |
SECTION | OBJ_FromNodeDepth | Depth of the pipe connection, measured from US manhole cover |
SECTION | OBJ_FromNodeInvert | Altitude of the US manhole bottom above sea level |
SECTION | OBJ_ToNodeDepth | Depth of the pipe connection, measured from DS manhole cover |
SECTION | OBJ_ToNodeInvert | Altitude of the DS manhole bottom above sea level |
SECTION | OBJ_FromNode_REF<-Node.OBJ_NodeType | Type of node/connection: manhole, connection, building |
SECTION | OBJ_System | Function of the node: manhole, lamphole, rodding eye etc. |
SECTION | OBJ_Situation | Description of the surroundings of the section |
SECTION | OBJ_SectionType | Use/purpose of the sewer |
SECTION | OBJ_Usage | Type of sewer (storm water, waste water etc.) |
SECTION | OBJ_Shape | Shape of the pipe (circle, rectangle, oval) |
SECTION | OBJ_Size1 | Diameter/height of the pipe; [mm], [inch] |
SECTION | OBJ_Size2 | Width of the pipe; [mm], [inch] |
SECTION | OBJ_Material | List of different pipe materials |
SECTION | OBJ_ConstructionDate | Year of construction |
SECTION | OBJ_Memo | General remark to the section |
SECINSP | SI_InspName | Name/Number of the survey (will mostly be set automatically). This field must NEVER be empty |
SECINSP | INS_Operator_REF | Link to the operator name |
SECINSP | INS_Equipment_REF | Link to the list of the vehicles used for section surveys |
SECINSP | INS_Camera_REF | Link to the list of the cameras used for section surveys |
SECINSP | INS_StartDate | Date of the beginning of the inspection |
SECINSP | INS_StartTime | Time of the inspection beginning |
SECINSP | INS_EndTime | Time of the inspection termination |
SECINSP | INS_InspectionDir | Inspection direction (WITH or AGAINST the flow). This field must NEVER be empty |
SECINSP | INS_PhotoRef | DVD N°: value automatically set for every section during a ViewerExport. Important for creation of a DVD box cover |
SECINSP | INS_Cleaned | Pipe section previously cleaned: YES / NO |
SECINSP | INS_Weather | Weather conditions during the inspection |
SECINSP | INS_Method | Camera technology: crawler, push camera etc. |
SECINSP | INS_Purpose | Reason of inspection: general survey, rehab. survey |
SECINSP | INS_PhotoRef | N° of storage medium set autom. during data distrib. |
SECINSP | INS_Memo | General remark to the survey |
SECOBS | OBS_SortOrder | Observation counter: this value is automatically renumbered when observations are deleted |
SECOBS | OBS_Distance | Position ([m] or [ft]) of the section observation measured from inspection start node |
SECOBS | OBS_DistanceToGo | Position ([m] or [ft]) of the section observation measured from inspection end node |
SECOBS | OBS_OpCode | Display of observation code (letter code) linked to the corresponding observation text |
SECOBS | OBS_Observation | Entry of observation text via country specific damage catalogues for sections (double click) |
SECOBS | OBS_RateValue | Entry of damage grades |
SECOBS | OBS_GradeS | Calculation of structural damage grades |
SECOBS | OBS_GradeO | Calculation of operational/service damage grades |
SECOBS | V_OBS_Picture1 | Taking the 1st picture of an observation (double click) |
SECOBS | V_OBS_Picture2 | Taking the 2nd picture of an observation (double click) |
SECOBS | V_OBS_Clip | Recording of video clip(s) of the current section (double click) |
SECOBS | OBS_LateralSection_REF | Recording of satellite sections (lateral sections) linked to the current main section (double click) |
SECOBS | OBS_TimeCtr | Displaying the MPEG timecode of the current observation |
SECOBS | V_OBS_Scan | Import data from a pipe scan (DigiSewer, Panoramo, RPP) and launch the ScanExplorer module |
SECOBS | V_OBS_LaserScan | Run the LaserScan module for pipe deformation measurement (RingLaser, MultiLaser) |
SECOBS | OBS_Memo | Special remark to the current observation |
NODE | OBJ_Key | The values entered into the section fields OBJ_FromNod_REF or OBJ_ToNode_REF are automatically transferred into this field |
NODE | OBJ_NodeType | Type of the node/connection: manhole, connection point, building |
NODE | OBJ_System | Function of the node: manhole, lamphole, rodding eye etc. |
NODE | OBJ_City | Name of the city/town |
NODE | OBJ_Street | Name of the street |
NODE | OBJ_Situation | Description of the surroundings of the manhole |
NODE | OBJ_DepthToInvert | Depth of the manhole in [m] or [ft] |
NODE | OBJ_Point_REF | Altitude of the manhole cover ([m]/[ft] above sea level |
NODE | V_OBJ_Shape_WKT_X | X-coordinate of the manhole (used in MAP and 3D) |
NODE | V_OBJ_Shape_WKT_Y | Y-coordinate of the manhole (used in MAP and 3D) |
NODE | V_OBJ_Shape_WKT_Z | Z-coordinate of the manhole (i.e. cover altitude, used in 3D) |
NODINSP | INS_StartDate | Date of the manhole survey |
NODINSP | INS_StartTime | Start time of the manhole survey |
NODINSP | INS_EndTime | End time of the manhole survey |
NODINSP | INS_RadialRef | Horizontal reference point for setting the clock position of the in- and outlets |
NODPART | NOP_Type | Description of the manhole part: |
NODPART | NOP_InsertType | Cone type: conical centered, conical off centered… |
NODPART | NOP_Shape | Shape: cover, shaft, chamber |
NODPART | NOP_Size1 | Diameter/Length: cover, shaft, chamber |
NODPART | NOP_Size2 | Width: cover, shaft, chamber |
NODPART | NOP_Height | Height: cover (“thickness”), shaft, cone, chamber |
NODPART | NOP_Material | Material: cover, shaft, cone, chamber |
NODENTRY | NOE_Type | Type of entry: inlet or outlet |
NODENTRY | NOE_ClockPosition | Clock position of the entry in relation to value set in INS_RadialRef (i.e. 6 or 12 o’clock) |
NODENTRY | NOE_Shape | Shape of the inlet/outlet |
NODENTRY | NOE_Size1 | Diameter/Height of the inlet/outlet |
NODENTRY | NOE_Size2 | Width of the inlet/outlet |
NODENTRY | NOE_Drop | Depth of the inlet/outlet, measured from cover |
NODOBS | OBS_Depth | Position ([m] or [ft]) of the manhole observation measured from cover (0 m or 0 ft) |
NODOBS | OBS_OpCode | Display of observation code (letter code) linked to the corresponding observation text |
NODOBS | OBS_Observation | Entry of observation text via country specific damage catalogues for manholes (double click) |
NODOBS | V_OBS_Picture1 | Taking the 1st manhole picture |
NODOBS | V_OBS_Picture2 | Taking the 2nd manhole picture |
NODOBS | V_OBS_Clip | Recording of video clip(s) for the current manhole |
NODOBS | V_OBS_Scan | Import data from a manhole scan and launch the ScanExplorer or the CleverScan module |