Preview of a new and improved CCTV processor solution

[#1]

CCTV Manager is now available! Get the latest from ArcGIS Solutions for Water.

Over the years, we have had a lot of great feedback on the CCTV Processor solution.  If you are not familiar with this solution, it is a geoprocessing tool for ArcMap that imports NASSCO PACP standard version 6 closed circuit sewer video inspections data.  The Inspections and Conditions tables are converted into points for each observation and lines for the main surveyed.  The observation images or photos are also loaded as attachments, making it easy to publish the data as a service and allow anyone in your organization to access the CCTV data.  This solution simply unlocks the wealth of information captured during CCTV surveys.

Due to user requests to support other import types and the new PACP v7, we knew it was time to enhance CCTV Processor.

1:  Migrate from Model Builder to a Python Toolbox:  Model builder is great to define a complex process, we built a faster, easier to use, and more stable tool using a Python Toolbox.  

2:  Designed for ArcGIS Pro:  To support the upcoming Utility Network in addition to data represented in a Geometric Network, we migrated CCTV Processor to ArcGIS Pro.  This also let us streamline the code, making it faster and more reliable.

3:  Resolution of BUG-000104129:  The current tool works great for observation points on a pipe from multiple surveys, but we had one issue that was hard to fix in the current version.  If a pipe was surveyed more than once, we only created one inspection line segment.  The new version has resolved this bug.

4:  Support for multiple input schemas:  The update enables us to support more than just PACP v6.0.4 version data.  You are now provided an option to select PACP v6.0.4, PACP v7.0.7, and Custom.  Selecting either PACP options will auto fill out the rest of the tool's parameters.  If you select Custom, you will need to map these parameters to your input.  There is now an option to add additional schemas to the list of options.  We will detail this process at release or if you want to try it, post a comment and we will provide instructions.

We hope you like the new version.  This is just the first preview.  We have not migrated all the features over yet, such as messages or link conversion.  

We are also looking for some of your CCTV data to test.  If you have a PACP v7 export or another schema and you do not mind sharing it with us, we would love to test it to ensure everything is 100% functional.

Getting started with CCTV in Pro

There are some installations that are required:

1. Microsoft Access Database Engine 2010 Redistributable

• Since ArcGIS Pro is a 64-bit application, make sure you choose the x64 version.
• 

2. pyodbc

• You can easily install this via the Python Package Manager.
• 
• If ArcGIS Pro is crashing after the install, please see this GeoNet post for a guide on fixing the issue.
• The image shows version 3.0.10.  PYODBC version 4 is also supported.  4.0.17 was tested on RC2.

Mike & Paul

ArcGIS for Utilities

[AlinaTaus]

Excited to see what the new model brings! I've taken the last model and made it fit my needs, but I'll definitely check out the new version when it comes out! I always learn something new! This might be beyond the scope of this solution, but one of the things I still struggle with is dealing with multiple inspections for the same pipe. Over time, we might end up CCTVing a pipe two three times (pre and post-construction). I'd like to be able to easily see or query the observations that are associated with a specific inspections. The PACP database does have some fields that can easily be used to establish a relationship between the inspections and the conditions , the problem is that ID is only unique within one database. I'm not quite sure what I would have to do to preserve that relationship once I append one set of inspections and observations to my master CCTV geodatabase.         

[MilkoVelichkov]

I would like to test it with additional schemas so please - provide me instructions ..! 

[MilkoVelichkov]

Python toolbox doesn't load ...

[MikeMillerGIS]

Right click the toolbox and select check syntax.

Did you follow the install instructions, they are required to load the toolbox.

[MilkoVelichkov]

I install pyodbc package thru the console as the Package Manager is stuck during installation !

Check Syntax fire up an error:

[MikeMillerGIS]

Try this fix. Some reason conda is adding a dll that causes an issue

https://community.esri.com/thread/195911-workaround-installed-package-crashing-pro-141

[MilkoVelichkov]

I don't have Python3.dll  at all, so that's fix not worked for me...

[MikeMillerGIS]

Miko,

   I have not seen this error.  If you open the python window in ArcGIS Pro, can you add a simple statement like import ArcPy ?

Can you ensure python is working?  

[MilkoVelichkov]

python is working and pyodbc is working as well

[MikeMillerGIS]

Sorry, meant to say in ArcGIS Pro's python window.  

[MilkoVelichkov]

same

[MikeMillerGIS]

Can you make sure the PYT is valid, can you download it again.  If right clicking will not let you edit it, something else is going on.  Let me download it also to ensure it did not get corrupted

[MikeMillerGIS]

One other thought, this PYT requires the cctv folder, all the logic is in there, the pyt is just a shell that loads from there.  Can you make sure the folder structure looks like this?

* Mine is not loading as I do not have ODBC install

[MilkoVelichkov]

Finally! I really don't know what was it. Actually I download it again, extract it, open ArcGIS Pro ... didn't work, then remove it and add it again and ...BOOM!

[MikeMillerGIS]

Great, at least we have some nice troubleshooting steps logged for the next person.  Thanks!

[MilkoVelichkov]

 ... I'm expecting instructions for  the Custom format  (I saw it ask for Access database, but by default the data are in XML) now ..

[MikeMillerGIS]

At the moment, only an access database is support as an input type.  Is your data in an Access Format?  If so, select custom and then fill out the parameters.  As for how to get a custom item in the drop down, we have not typed those instructions up.

Paul93‌

[deleted-user-G2Ws9r74aRyw]

Python toolbox is not loading for me. I talked with my IT Dept about downloading that Access Database Engine x 64-bit because I am thinking this is my issue. They informed me not to do this as we are running 32 bit. Is there a work around for this? I cannot tell if I am experiencing another issue since I am unable to download the 64 bit Access Database Engine.

Thanks!  

[MikeMillerGIS]

That library is required to use the tool. That is why you are getting the !

[SMW_GIS]

Can someone please help me out?  I'm trying to use the tool and keep getting this error:

I tried using the sample data and it worked.  I've compared the field types in my PACP Database with the sample data and they are identical.   Is there something I'm missing?? 

[PaulLeBlanc1]

Steven,

Are you using a PACP v7 schema? The code is incorrectly guessing that 'Direction' is the Inspections ID field. If you change that 'InspectionID' or similar, it should work.

[SMW_GIS]

Ok yea that fixed the initial error.  I should have caught that but thank you Paul!  

I'm running into another error though.  Sorry, I'm not sure how to look through the script and debug it myself. I think there is any issue with the save_to_disk function.  Not sure why I'm getting this error with my own database and not the sample. 

Start Time: Monday, September 11, 2017 10:23:31 AM
Running script CreateConditionPoints...
Traceback (most recent call last):
File "<string>", line 442, in execute
File "C:\_MyTools\CCTVProcessorA4W_Preview_RC1\CCTVProcessorA4W\Maps and GDBs\cctv\cctv.py", line 433, in save_to_disk
target=table_names[self._inspection_table.lower()])
KeyError: 'pacp_inspections'
Failed to execute (CreateConditionPoints).
Failed at Monday, September 11, 2017 10:23:34 AM (Elapsed Time: 3.59 seconds)

[PaulLeBlanc1]

Does your sample database match the PACP schema? It's looking for 'inspections' and your PACP database table is called 'pacp_inspections'.

You can either rename your table to be 'inspections' (case doesn't matter) or modify the schema to use your table name of 'pacp_inspections'

CCTVProcessorA4W\Maps and GDBs\cctv\schema\PACP v6.0.4\schema.xml

[SMW_GIS]

That solved the problem.  I just needed to modify the table names in the original survey database.  Thank you very much Paul!!!

[AwesomeEvan]

I'm looking to set this up for the first time, should I just wait until its official released as an update and ArcGIS Pro integrated? CCTV Processor | ArcGIS for Water 

Any idea how much longer?

Thx

[MikeMillerGIS]

The goal is to have it updated and release for ArcGIS Pro 2.1 in January. You can start with the Beta and update when the release version is available. The end results will be very similar.

[deleted-user-pZlUMHhkW23Q]

I have Microsoft Office Suite 32bit and Access 32bit installed. If I try to install the Microsoft Access Database Engine 2010 Redistributable(64bit)  I get an error "You cannot install the 64-bit version of Microsoft Access Database Engine 2010 because you currently have 32-bit Office Products installed"

If I force the install using the /passive command prompt command it "installs" however when try to run the CCTV processor I get the following error, "Unable to load odbcji32.dll"

If I remove Office and Access and then install Microsoft Access Database Engine 2010 (64bit) the CCTV processor works but I don't have Office suite or Access now.

If I attempt to reinstall Office and Access 32bit, I get the following error "You cannot install the 32-bit version of Microsoft Office Suite because you currently have 64-bit Microsoft Access Database Engine 2010 installed"

This is a bit of a Catch 22

Is there a correct installation practice for Office 32bit and Access 32bit along with getting the CCTV processor to work other than installing the 64bit version of Office?

[UPDATE]

- I was able to get it to work alongside Office only by installing the 64bit version of Office and Access. This is not ideal. 

- I was able to run the processor (PCAP 6.04 Schema) however the results were incorrect only 146 conditions came through out of an expected 6431.

- I was able to run it with the old CCTV processor and the results were as expected 6431.

- Not really sure what wrong with the new CCTV processor, 

For example:Using the Old CCTV processor, I have a pipe with ID 4082. It has three separate inspections on it (95, 96, 403) and a total of 87 conditions.

Using the new one, it only has 3 conditions one for each pipe inspection (95,96, 403) and that's it, everything else is missing.

So for this year's CCTV going to use the Old CCTV Processor and hopefully next year the new one works correctly.

[UPDATE 2 - 28/11/2017]

- So it appears installing the 64bit version of office causes some additional headaches when using ArcGIS 10.3.1 and trying to load Excel tables into ArcMap.

        - It gives you the following error: "Connect: Failed to connect to database. An underlying database error occurred. Class not registered."

        - You can read more about this problem here: .XLSX PROBLEM - Failed to Connect to Database - Class Not Registered 

        - Easiest solution was to save the excel file as ".xls"

[PaulLeBlanc1]

Tristan, would it be possible for you to share you data with us? You can email to pleblanc@esri.com

What version of Office do you have installed? I have x86 Office 365, and I can install and use x64 Access driver without issue.

[DMFACER]

I am getting the following error when running this tool and would greatly appreciate any help in solving this problem:

Running script CreateConditionPoints...
Traceback (most recent call last):
File "<string>", line 438, in execute
File "C:\Users\mfacer\Documents\ArcGIS\Projects\PennHillsCCTV\CCTVProcessorA4W\Maps and GDBs\cctv\cctv.py", line 230, in __init__
self._check_dtypes()
File "C:\Users\mfacer\Documents\ArcGIS\Projects\PennHillsCCTV\CCTVProcessorA4W\Maps and GDBs\cctv\cctv.py", line 236, in _check_dtypes
pipe_pk = self.pipe[self.pipe_pk].dtype
File "C:\Users\mfacer\Documents\ArcGIS\Projects\PennHillsCCTV\CCTVProcessorA4W\Maps and GDBs\cctv\cctv.py", line 280, in pipe
sql = create_sql(self._pipe_table, self.pipe_pk, self.inspection[self.inspection_fk].tolist())
File "C:\Users\mfacer\Documents\ArcGIS\Projects\PennHillsCCTV\CCTVProcessorA4W\Maps and GDBs\cctv\cctv.py", line 37, in create_sql
values = sorted(set(values))
TypeError: '<' not supported between instances of 'NoneType' and 'str'
Failed to execute (CreateConditionPoints).

[PaulLeBlanc1]

There's probably an empty value in one of the access tables. I think this might be be ConditionID field.

[DMFACER]

Paul,  I do not see any empty values in the tables, specifically in the ConditionID field.  Any other assistance would be greatly appreciated.

[PaulLeBlanc1]

Hmm, I'd need to look at the data. Can you provide your pipes feature class and access database? You can email to pleblanc@esri.com

[deleted-user-pZlUMHhkW23Q]

Your error is saying TypeError: '<' not supported between instances of 'NoneType' and 'str', look if that value shows up anywhere in your data, the "<". Also looks for any strings in a number field or numbers in a string field. Otherwise it sounds like an problem with either  the  create_sql function which I assuming is writing a SQL clause as a string before passing to an sqlParser but something how the string is created is messed up. The tool usings python pandas. In pandas the object type does not necessarily mean dtype str. and pandas forces the type to change when it inserts NaN values so this could be happening here. However I'm mostly just speculating as I did not deep dive into debugging it

[PaulLeBlanc1]

sorted([None, ''])

gives that error message. As you noted, pandas will insert NaN (which is similar to None). The simple fix is to filter None out before sorting inside the create_sql function.

I'm curious what configuration of the data will get into this state.

[PaulLeBlanc1]

Thanks everyone for the feedback so far! We've just uploaded a new beta version with the following enhancements and fixes:

• added support for true curves
• v7 schema fix
• create relationship between TelevisedMains and CCTVConditions
• add count of conditions to each pipe
• add Inspection Name value to the feature classes (useful if the data will later be stored in a master database)
• fix for inspections without media
• fix for inspections with duplicate pipe

We're also investigating an import of MACP inspections. If you have any MACP data that you can share, please let us know. We're also interested in any version 7 data (PACP or MACP).

You can email to pleblanc@esri.com or mmiller@esri.com

[AlinaTaus]

I am running into the same issue as Tristan because my version of Office is 32bit.  Is there any way to get this new version of  CCTV Processor to work for those of us who can't install the 64bit version of Office?

[PaulLeBlanc1]

I'm working on a version that will read the CCTV data from a file geodatabase. There will be a separate tool (which still requires pyodbc), to convert Access to FGDB.

[DMFACER]

We're using the latest beta processor to convert multiple clients historic data from an old Access database system into a GIS system.  All of our clients have converted over successfully except for one, and we are getting the following error when we try to run their data through the processor.  Any help would be greatly appreciated!

Start Time: Monday, June 25, 2018 5:37:09 PM
Running script CreateConditionPoints...
17:37:09 Saving results to C:\Users\mfacer\Documents\ArcGIS\Projects\VersaillesCCTV\CCTVResults3.gdb
17:37:12 Loading 3,138 records from CONDITIONS table
17:37:13 Loading 243 records from INSPECTIONS table
17:37:16 Loading 3,142 records from MEDIA_CONDITIONS table
17:37:16 Loading 243 records from MEDIA_INSPECTIONS table
17:37:17 Splitting lines
17:37:17 Locating conditions
Traceback (most recent call last):
File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\pandas\core\common.py", line 399, in _asarray_tuplesafe
result[:] = values
ValueError: could not broadcast input array from shape (0,2) into shape (0)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\pandas\core\frame.py", line 2564, in _ensure_valid_index
value = Series(value)
File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\pandas\core\series.py", line 264, in __init__
raise_cast_failure=True)
File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\pandas\core\series.py", line 3277, in _sanitize_array
subarr = _asarray_tuplesafe(data, dtype=dtype)
File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\pandas\core\common.py", line 402, in _asarray_tuplesafe
result[:] = [tuple(x) for x in values]
ValueError: cannot copy sequence with size 2 to array axis with dimension 0

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "C:\Users\mfacer\Documents\ArcGIS\Projects\PennHillsCCTV\CCTVProcessorA4W_Preview_RC2\CCTVProcessorA4W\Maps and GDBs\cctv\gptools.py", line 455, in execute
image_name=abstract.media_condition_image)
File "C:\Users\mfacer\Documents\ArcGIS\Projects\PennHillsCCTV\CCTVProcessorA4W_Preview_RC2\CCTVProcessorA4W\Maps and GDBs\cctv\gptools.py", line 411, in run
points, lines = p.save_to_disk(target_gdb=gdb)
File "C:\Users\mfacer\Documents\ArcGIS\Projects\PennHillsCCTV\CCTVProcessorA4W_Preview_RC2\CCTVProcessorA4W\Maps and GDBs\cctv\core.py", line 467, in save_to_disk
df = self.process()
File "C:\Users\mfacer\Documents\ArcGIS\Projects\PennHillsCCTV\CCTVProcessorA4W_Preview_RC2\CCTVProcessorA4W\Maps and GDBs\cctv\core.py", line 423, in process
df[self.inspection_point] = df[[self.pipe_shape, 'percent']].apply(lambda f: self._locate_point(*f), axis=1)
File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\pandas\core\frame.py", line 2519, in __setitem__
self._set_item(key, value)
File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\pandas\core\frame.py", line 2584, in _set_item
self._ensure_valid_index(value)
File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\pandas\core\frame.py", line 2566, in _ensure_valid_index
raise ValueError('Cannot set a frame with no defined index '
ValueError: Cannot set a frame with no defined index and a value that cannot be converted to a Series
Failed to execute (CreateConditionPoints).
Failed at Monday, June 25, 2018 5:37:17 PM (Elapsed Time: 8.35 seconds)

[AlinaTaus]

I have noticed the CCTV Processor is being replaced with CCTV Manager. The 64-bit Microsoft Access Database Engine is now a requirement for these tools to work. Will ESRI still provide a workaround for those of us who are still running the 32-bit version?  

[MikeMillerGIS]

You will need a machine that has the 64 driver installed so you can create the fgdb from the access files. I was able to get 64 bit access driver on my machine with 32 bit office by first installing the 64 bit driver and then office.