Update Inlet symbology and enforce the same symbology in the field tool

[talllguy]

Red – Inlets with debris >25% (needs cleaning) Blue – Everything else Green – Inlets with a status of Cleaned Yellow – Skipped (not mentioned in UAT but later deemed necessary

• [x] Update program app
• [x] Update field app
• [x] Change schema?

[talllguy]

@johnshiu we're streamlining the symbology for display of the ICP inlet point data in the various tools in this platform. To meet stakeholder requirements, I've used an approach where two layers, each with a different symbology and filter, are displayed. This is complex and somewhat bulky, though it works within the "COTS" paradigm. However, it'd be much simpler if there was a single field for symbology and filtering. Therefore, could you consider the LOE of modifying the ICP ETL to include the following logic:

A logic for the proposed field to track inlet cleaning need and status

The field name should be ETL_PROGRAM_CATEGORY, since the field is generated by ETL, relevant to the PROGRAM goals, and is a CATEGORY in the sense that it holds categorical information.

The idea for the logic is that the ETL should first check if the clean status is populated and if so, write that to this field, if not, check if the chamber full is 25 or greater and write that to the field. Anything else is undefined/other/null.

The field type can be a string with the following coded values. The logic for each follows.

• RECENT_CLEANED - any inlet where CLI_CLEAN_STATUS is 'cleaned' AND CLI_INSPECTION_DATE is in the past 6 months
• RECENT_SKIP - any inlet where CLI_CLEAN_STATUS is 'skipped' AND CLI_INSPECTION_DATE is in the past 6 months
• TO_CLEAN - Where PRE_PERCENT_CHAMBER_FULL >= 25 AND PRE_DATE_INSP < CLI_INSPECTION_DATE unless CLI_INSPECTION_DATE is six months ago or more. This ensures cleaned inlets are not set to clean by the ETL unless the pre-date is after the last clean date.

Anything else, e.g. 0 chamber full or no pre-inspection, should remain null so that NULL can be designated as 'Other' in the symbology.

[talllguy]

Some recent updates:

1. The recent cleaned status will change in the future where the date becomes dependent on a table listing the time each inlet cleaning is good for. For now, the above works.
2. There's an additional logic to add out of a team meeting today. RECENT_CLEANED_PLUS_PIPE - any inlet where PIPE_CLEANED is 'cleaned' AND CLI_INSPECTION_DATE is in the past 6 months. This is dependent on #37

[johnshiu]

The ETL_PROGRAM_CATEGORY field has been added, and the ICP has been refreshed manually. You should see some of these values showing up. Please let me know if there appear to be any logic issues.

[talllguy]

@johnshiu thanks, I see the field but the logic does appear to be off. I edited the comment above to simplify but to further break out the logic, here's a table. Does this make sense?

logic	result
PRE_PERCENT_CHAMBER_FULL >= 25 AND none of these other statuses apply	TO_CLEAN
CLI_CLEAN_STATUS = 'skipped' AND CLI_INSPECTION_DATE is between today and six months ago	RECENT_SKIP
CLI_CLEAN_STATUS = 'cleaned' AND CLI_INSPECTION_DATE is between today and six months ago	RECENT_CLEANED
CLI_PIPE_CLEANED = 1 AND CLI_INSPECTION_DATE is between today and six months ago	RECENT_CLEANED_PLUS_PIPE

[johnshiu]

@talllguy, hmm, I think that all lines up with what I have in the script. Here is what we have in the ICP for non-null ETL_PROGRAM_CATEGORY:

Does anything there look incorrect?

[talllguy]

@johnshiu those look right. In the data on NPDES though there are only six inlets set to TO_CLEAN and none with any other categories. There should be many thousands set to TO_CLEAN and just a few with the other statuses.

[johnshiu]

@talllguy, ah ok, I think the removal of the inspection date logic for the TO_CLEAN should have fixed it. Please try it again; there should be over 4000 now.

[talllguy]

Wonderful, it’s working now! I’ll add some test ones that should trigger the various statuses tonight so they’ll appear tomorrow. What happens if I delete a survey? Will it clear the CLI ?

-- Elliott Plack, Senior Business Analyst Geospatial Solutions • KCI Technologies, Inc.

w: (410) 891-1750 m: (410) 206-6448

📧📲

On Mon, Apr 15, 2019 at 3:32 PM -0400, "John Shiu" notifications@github.com<mailto:notifications@github.com> wrote:

@talllguyhttps://github.com/talllguy, ah ok, I think the removal of the inspection date logic for the TO_CLEAN should have fixed it. Please try it again; there should be over 4000 now.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/kcigeospatial/Inlet-Field-and-Web-App-Configuration/issues/29#issuecomment-483386962, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ABvkKaZ1CWXlk_BHs2XDp5KOxRUAtp5Xks5vhNO1gaJpZM4cV9qw.

[johnshiu]

Hmm, if you delete a survey, it will clear most of the CLI fields except the CLI_CLEAN_STATUS field, which will preserve its clean status from before the refresh. It will only update if a new survey is submitted in AGOL, which then overwrites whatever is in the CLI_CLEAN_STATUS field. I think this logic is there to make sure that subsequent, new inspections will supersede what is in the field, while at the same time preserving any edits to the ICP via the feature service until a new survey is submitted.

[talllguy]

Everything in my testing on this is working great. I'll double check after it runs tonight but everything is operating as expected.

[talllguy]

@johnshiu after the overnight refresh, one small bit of the logic is not working: the RECENT_CLEANED_PLUS_PIPE part.

Structure Number 0300201.002 should have this status.

Here's why:

The highlighted field is aliased for CLI_PIPE_CLEANED. When it is 1, ETL_PROGRAM_CATEGORY should equal RECENT_CLEANED_PLUS_PIPE

[talllguy]

@johnshiu Hold that thought. There is another tweak. Let me close this issue and start a new one to better cover this.