Importing External Data

Owned by Steve Peregrine
Last updated: Nov 02, 2022
16 min read
Back to

Chapters

Page Contents

Introduction

It is normal in HADDMS projects to combine CCTV inspection data with GIS data into the required deliverable standards. Historically, this has often been done by entering into joint ventures between drainage contractors and GIS specialists, with the drainage contractor taking the lead.

For many such contractors, the skills required on each side of the fence do not really overlap very much, but the fact remains that both parts are needed in the final deliverable to the client. WinCan VX allows all of these actions and data processing functions to be done in one application, so there is no need for other software applications. This means that we have to be able to handle the GIS data as effectively and efficiently as possible alongside the CCTV inspection data where WinCan holds its roots.

As has been described previously in this manual, there is little or no chance that the coordinates in 3 dimensions of an asset will be measured as exactly the same when it is measured on multiple occasions. This is normal, so after recording this data on site, we need to be able to import that data into the WinCan project, assess its quality and reconnect the geometric shapes of the linear objects to the new node coordinates.

The deviation of the coordinates measured for an asset can be anything between a few mm to many metres. Again, this is normal and there are a number of reasons why there can be such large or small differences including local site conditions.

Preparing the Data

The tool in WinCan VX for importing GIS data into an existing project can accept data in the following formats, in order of preference and ease of use:

  • .shp shapefile

  • .dbf database file

  • .csv file

The structure of this data is completely open to the user and anything can be ‘mapped’ into the WinCan VX project within reason, but the data really needs to be grouped into individual data files for point, continuous and region items in the same way that it is in the original HADDMS data.

Furthermore, although not absolutely necessary, life becomes a lot easier if the data formats and column headings are exactly the same as the original HADDMS data files. Following this rule means that there will be little or no mapping edits required to import the data, because we have already done them for you in the software. So, creating your own style of data is possible, but will require additional skills with mapping editing and a detailed knowledge of WinCan VX database formatting that will not be covered here in any great detail, except for some minor advanced tweaks described later on this page.

This attached files contain the column headings for all the most significant shapefiles in the HADDMS data structure in .csv format:

While .csv file format is easy to work with and edit in Microsoft Excel, it has some limitations in what data can be transferred and how, so it can sometimes be useful to setup and edit the data in Excel, and then convert the final file into a .dbf file (is more robust for import) using a free online web service like anyconv.com. The conversion process is fast and free.

Advice - If you have followed this guide thoroughly, then you will be aware that good inspection data validation as described in is essential for good data delivery, and if you have reached the point where you are ready to import GIS data, then hopefully your CCTV inspection data is in a good shape first.

However, importing this data can (and often does) introduce a whole new range of errors, so it is a good idea to check the data for logic errors before import and to keep on validating the VX data throughout all stages of data processing, especially after importing external data.

As just an example of this - when looking at some recent data where the WinCan VX data was well validated with little or no errors, we saw that after import of GIS data, there were many data errors. On closer inspection of the imported data which had been supplied by a partner contractor to the CCTV contractor, the errors became clear. Although it is not difficult to fix the errors in WinCan VX using tools such as the filters and the Search & Replace tool, it was easier in the end to roll back the project database, fix the source data and then re-import it again without the errors.

Adding Photos

In WinCan VX v10.0 and higher, a new feature has been added that automatically imports photos into the WinCan VX project while importing the GIS data. This is most useful for creating reports for point, region and surface continuous items, and these photos are usually taken by the GIS team at the point of inspection.

Once the photos are in the project and linked to an object, then manhole cards and inspection reports can easily be created from the printing engine in WinCan VX.

There is no limit to the number of photos that can be added to an object using this tool. To use this feature:

  1. Add the photos to the same folder directory as the GIS data import files.

  2. Name the photos strictly using the naming convention [SUPP_REF]_[Some Text]

    1. Example: CP501_Internal condition photo.jpg

  3. Do not use any underscores in the [Some Text] part of the file name.

    1. You can add as many photos as you like of CP501, provided the [Some Text] part of the file name is unique for each one. Use this part of the file name to describe the photo clearly.

  4. Run the GIS import tool.

The result will be that after import, each object (node, continuous item etc) where a matching SUPP_REF is found inside the project database will now have a GP code added at zero metres for each photo assigned to the object where the [Some Text] part of the name string will be in the ‘Remark’ of the photo against the observation.

From here, you can print node cards very easily and the node photos can be printed in the same way that you do for pipe inspections.

Running the Import

The first thing to remember before running this import GIS tool into an existing project is that it will modify the data, so your project database will not look the same after running the tool as it did before. As has been mentioned already, it is not uncommon to introduce errors into the project during this process.

It is for this reason, that when the tool is executed, WinCan makes a backup copy of the database at the very beginning of the process, and saves it inside [Project Folder] → Misc → Backup with a date and time stamp on the folder name. In the event that the result is not as expected, then you can roll back the database to where it was a few minutes ago, and reconsider your import data and try again.

It is also for this reason that it is important to make some sanity checks on the result immediately after running the tool, because if you accept the changes and then move forwards with additional data processing options, then your backup point becomes more and more distant from your current point in time.

What these sanity checks look like is up to you, and will differ between users, but you should at least have a close inspection of some nodes or surface items that you have imported and check that the data in WinCan VX and in WinCan Map looks good and has been updated.

As has been described above, if you have the data in exactly the same column format as the HADDMS shapefiles, then the import process is easy, because the data mapping files are already done for you. If you are using .csv files, then ensure that the point.csv file has coordinates in it. If not, the import will fail.

So, now we are ready to import the GIS data and our folder directory maybe looks like this:

ImportGIS2.jpg

Import GIS data folder directory.

See that there is a .csv file with the import data (can also be .dbf or .shp file) and there are a series of photos for each item with the naming convention [SUPP_REF]_[Some Text].

To start the process, click on the ‘Import from GIS’ button in the ‘Data Exchange’ ribbon to launch the data transfer tool, and see the option to import GIS data into an existing project which you can select from the drop down list here:

Selecting an existing project to import GIS data into.

From here, the process is very similar to that described at the very beginning of this process when we first imported the HADDMS data into the project for round-tripping as described in . Select the file to import and select a mapping file. If you have created your import file in exactly the same style as the HADDMS shapefiles files, then you can use the existing mapping file which is already installed in VX:

Importing the data.

Finally, click the green ticket and wait for the data to import. Once complete, review the data in your VX project. See in this example that the nodes now have a new inspection (this is important for export reasons) because the node has been inspected by the GIS team and that this new inspection has a series of GP observation codes at zero metres with the photos attached and the [Some Text] in the remarks field of the code. Furthermore, the data inside the header has also been updated with the new attributes that were inside the import file, and now we are in a much better place to create manhole cards:

Result.

Remember, during this import, a database backup has been created as described previously, so if you are not happy with the results, you can roll back.

Rolling Back the Database

There are a number of processes within WinCan VX where a backup copy is made against a date and time stamp of the current project database before the action is executed. This is done at points during different functions where the software is modifying the data automatically, and there is a chance that the user may not like the results. These include:

  • Running the ‘Import from GIS' tool into an existing project.

  • Exporting data from a HADDMS project.

  • Using the project merger.

  • Converting the database file type from SQLce to SQLite on opening in WinCan VX version 9.0 and higher.

  • Downloading a project from WinCan Web as SQLite databases where the original project that was uploaded used SQLce databases.

Info - there is a plan to develop a tool within the user interface to allow users to create manual backups at any point they wish during their data processing and also to be able to restore a database from a previous backup. While we are waiting for this tool to be developed, the process must be done manually.

The backup files are saved inside the [Project Folder] → Misc → Backup folder and generally have the naming convention DB[Date:yyyyMMddhhmm]. Inside the folder you will find copies of the two database files that contain your project that were created before the tool was executed.

To roll back the project to a previous state, simply take the relevant backup databases, copy them and paste them into [Project Folder] → DB. You may choose to overwrite the current database files or take your own backup again of these but this must be done manually and there is no automated tool right now for doing this in WinCan VX.

Resizing Photos

In-pipe inspection photos taken inside pipe inspections have their file size specified by the video quality setting applied in the User Settings on the PC, but even when these settings are set to their highest values, the picture file sizes are not particularly large.

Most importantly, they are big enough to print a nice photo in the pdf report at the largest size of 2 photos per page and the picture is clear and sharp. In other words, they are 'good enough'.

When taking photos with a handheld digital device such as a mobile phone or a handheld camera, the file size and image quality can become very large, very quickly. Although this is great for taking family photos on holiday, we really don’t need this level of detail for drainage inspection reports, and adding maybe 500 manhole photos to a WinCan project where each photo has come from a mobile phone is maybe 5mb in size will very quickly make the project folder very large and possible cause issues with upload/download with WinCan Web.

None of this is an issue to WinCan VX or WinCan Web, but it will take much longer to upload/download and may cause the user issues with data transfer costs from site via the mobile data network.

So, we have a tool in WinCan VX which can ‘Resize’ photo files to a much more manageable and suer friendly size that is still creates a good quality and sharp pdf file report and drastically reduces the size of the photos folder inside the project folder.

Photo Resizing tool in the Extended Modules ribbon.

This tool can be run at any time and on any folder containing photo image files, so can be run before or after the import GIS data process, and can even be used for folders of photos that are nothing to do with WinCan VX.

There are no user options included in this tool other than selecting a folder to apply the changes. When the tool is executed, it simply resizes every image file found inside the specified folder to 1,000 pixels on the longest side.

This tool is available for all WinCan VX projects, not just HADDMS, but it is particularly useful for HADDMS work because of the often large number of files that need to be added to the project.

Options to Improve the Data for Import

As described in , a solid understanding of WinCan VX object types as well as HADDMS object types is essential to creating good data in the WinCan arena. Fortunately, there are only 3 WinCan continuous types and 3 WinCan node types.

Furthermore, in we talk about marking objects as ‘Inspected’ and this is also a WinCan field with no transfer of data into or out of the HADDMS shapefiles, but working with these fields efficiently at the point of import can greatly reduce future problems with validation errors and help to understand what has and hasn’t been done in the project.

So, we can manipulate these fields during import to improve the data quality inside the project, but this is advanced stuff, and needs some careful thought.

Inspection Status

In both the Section and Node tables inside the WinCan VX database, the value for this is stored in the field OBJ_DataState and has the following internal database key values:

  • 0 - Not Attempted

  • 1 - Inspected

  • 9 - Attempted but not Possible

  • 10 - To be Inspected

  • NULL - Not Defined

So, when we are preparing the data for site works as described in we are in the background setting the value ‘10’ to this database field in 2 tables inside our database.

When we import GIS data, we have done an inspection and we should mark the object as Inspected, Not Attempted, or Unable by updating this database field value with an appropriate value as described just above during the import phase.

So, if we add a new column to the import data and carefully decide on the most suitable values for this field against each node or surface item, then we can update this field dynamically.

Create a new column in your data file with a new column name that does not match any other column name in the file. In this example, I have created a new column called ‘Inspect’ and have added the numeric values against each SUPP_REF according to the work that was done on side. Notice that the connector nodes are set to zero as ‘Not Attempted' - logically, there isn’t really any other value that you can assign to any of the 4 types of modelling node in the point table:

Adding the ‘Inspection Status’ data.

Info - before proceeding here, it is a good idea to create a copy of the mapping file that you are going to edit, so that you build up your own library of mapping files which can be used for different purposes.

To do this, navigate in Windows to the folder:

  • C:\Users\Public\Documents\CDLAB\Common\DataExchange\ShapeMappings

In here you will see the shape mapping files that are included in the software and it will be clear by their name which one is which. In this example, we will take the ‘DDMS Point’ mapping file, copy and paste it, and then rename it to something useful, like ‘DDMS Point with Inspection Status’.

Now it will appear in the mapping file list in the next screen shot, and we can edit this file without messing up the default one inside WinCan VX.

This process will be improved in the future with an option to create cloned mapping files directly from the mapping editor and save them with a new file name.

Now, follow the same steps as above for importing data into an existing project, but this time pause at the point where you select the mapping file for the import data. We must modify the mapping file to read the data in the new column, so select the mapping file, highlight the line in the table view and click on the ‘Edit Mappings’ button:

Launching the mapping editor.

When you click this button, the mapping file editor will open. There is a lot going on in this small window and you should consider all of the options available to you on the screen at all times and think about whether or not they look correct:

The data file mapping editor.

The column on the left shows the fields that are in your import data file, the column in middle shows the WinCan VX database fields for the table highlighted at the top, and the panel on the right shows the currently mapped fields, so in this case, these are the WinCan derived mappings for HADDMS data round tripping import.

All we need to do now is add a mapping for the new field ‘Inspect’ in the import data file into the field OBJ_DataState in the WinCan VX database. You can use the search box above the middle column to search for the WinCan field. Highlight the two fields that are to be mapped, click on the ‘Add’ button, then ‘Save’ the modified file and click on the green tick to go back to the previous panel.

Now just click on the green ticket and import the data, and the result in the node table looks like this:

Imported modified data.

Using small tricks like this to your advantage can save a lot of processing time and difficulties with fixing validation errors.

WinCan VX Object Types

Please be sure to fully understand the theory described in before proceeding with anything like this.

So, we have 3 ‘WinCan’ types of continuous object and 3 ‘WinCan’ types of point object, and the values that can go into the WinCan database MUST be used exactly as described here. Failure to get this right will lead to the project failing to load in WinCan VX, but using this hack skilfully can also greatly enhance the quality of the WinCan project extremely quickly:

Continuous Items:

When considering these WinCan object types, think carefully about which type of ITEM_TY_CO objects fit into which group, and it may be necessary to consider the nodes at each end of a continuous item to make a clear distinction in the case of the difference between SEC and LAT:

  • LAT (lateral) - any type of below ground linear object that has a connector node at the downstream end.

  • SEC (section) - any type of below ground linear object that is not a LAT.

  • SUR (surface item) - any type of continuous surface item.

Point and Region Items:

As above for continuous items but this time think about the 3 groups of WinCan objects as:

  • CON (connector) - any of the 4 types of modelling node in the HADDMS point file.

  • NOD (node) - any type of point item that is not a CON.

  • REG (region item) - any type of polygon geometric shape.

With these values (SEC, LAT, SUR, NOD, CON, REG), you can follow the process described above and create additional columns in your import data to again greatly improve the quality of the results in the WinCan world, but it cannot be stressed strongly enough that you must be considerate when deciding which value to assign to which object and you must only use the text strings exactly as they are listed above.

WinCan VX v11 Update

From WinCan VX v11 onwards, there are a number of improvements in the GIS file mapping editor:

Import mapping editor improvements.

  1. The buttons with text captions have been replaced with icons with hover tip text.

  2. A new ‘Clone’ button has been added. This allows you to clone and rename the current mapping file so that you can take an existing mapping file (the one that is currently loaded) and make a direct copy of it which you can then modify to suit your own requirements.

  3. Transfer mapping tool has been added. This allows you to specify import transfers inot the current mapping file, so where an import file has data that does not align with the drainage standard that you are using, you can exchange the data at the point of import.

    1. The exchange mappings for HADDMS are already defined in the standard mapping files, so for HADDMS, there is nothing to do here from v11 onwards. The exchange mappings include things like converting the bad material code PVC to PV, converting the HA ownership code to HE, and many more besides where the data is not clean in the import shapefiles. This then immediately removes a lot of validation errors from the project right from the beginning.

    2. The converter settings can include wildcards if needed, but these are not needed for HADDMS.

Back to the top.