NPDES Field Data Manager - IDDE Login Tools

[KCI-Ablowers]

Based on the last meeting with ECD on the IDDE Next Gen Application and overall program, the stakeholders indicated that inspectors would not make updates to the inventory beyond creating a new FLDSC_Site record or updating an existing one. In the future, we will likely add the "illicit discharge point" and have the option to launch the inspection from the FLDSC_Site record related to that point.

The "Validate Field Changes" tool should likely change to something related specifically to the "FLDSC_SITE" records for now.

The IDDE login does not need access to the "BMP Inspections" option on the "Submit Updates for Review" page. @johnshiu can the FLDSC_SITE (Inventory) push be added to the "Submit IDDE Inspections for Review" page? This would eliminate the "Submit Updates for Review" page from the UI.

[johnshiu]

@KCI-Ablowers, I think the easiest way to address these items is the following. Please let me know if those sound good to you:

1. Remove the "Validate Field Changes" tool for IDDE.

Since they are not really doing any ETL fixes or validation, it is probably not needed. Instead, any ID generation will be done in the background prior to submitting changes to the source GDB.

If, however, there are some data reviewer-type functions that we should run against the table, we can certainly do so. I would just run these automatically when the IDDE inspections are synced so that they don't need to run two separate items.

2. Update the "Submit IDDE Inspections" to include FLDSC_SITE changes.

This adjustment aligns with your proposed idea. I would basically be running the IDDE inventory field to source script followed by the inspection field to source script back-to-back. The user would just see it as one operation, however.

Please note that the IDDE inventory field to source script will commit any version changes for the tables (i.e. STRUCTURES, SWMFAC, FLDSC_SITE, etc.) This is because I don't have a separate routine written for just the FLDSC_SITE table. Therefore, please make sure that the inventory editing is locked down on the AGOL side of things to prevent inadvertent editing of other features and tables.

[KCI-Ablowers]

1. There are some data reviewer functions that check against structures, inspection (idde), and the FLDSC_SITE table -

Test#	Table	Field	Rule
156	FLDSC_SITE	STREAMDIST	Cannot be NULL
157	FLDSC_SITE	STRUCTURE_ID	Cannot be NULL
165	INSPECTION	DEPTH	if DEPTH value is greater than 1, then FLOW_OBSERV is true
167	INSPECTION	FLOWOBSERV	If yes, Then DEPTH must be greater than 0
262	STRUCTURES	MAJ_OUTF	If yes and STREAMDIST in FLDSC_SITE is not 9999 then NPDES in STRUCTURES is yes
155	FLDSC_SITE	LOC_SCREEN	Cannot be NULL
158	FLDSC_SITE	VIC_SCREEN	Cannot be NULL
159	FLOW_CHAR	INSPECT_ID	Cannot be NULL
163	INSPECTION	DATE_SCRN	Must be three days after LAST_RAIN
164	INSPECTION	DATE_SCRN	Cannot be NULL
166	INSPECTION	FLOWOBSERV	If yes, then record needs to exist in FLOW_CHAR
168	INSPECTION	INSPECT_ID	Cannot be NULL
169	INSPECTION	INSPECTR	Cannot be NULL
170	INSPECTION	LAST_RAIN	Cannot be NULL
171	INSPECTION	SCRTIME	Cannot be NULL
172	INSPECTION	STRUCTURE_ID	Cannot be NULL
183	FLDSC_SITE	STRUCTURE_ID	If has value, then DESC_OUTF must equal PIPES MTRL_PIPE

• https://github.com/kcigeospatial/MDOT-SHA-NPDES-Next-Gen/blob/master/NPDES_FinalSQLTests.csv

@johnshiu If you could present the issues in the "Errors" column on the unsubmitted inspection table after sync is run, then I think we could do away with the "Validate." If not, we may need to leave it in there and present the issues in the inspections export. @talllguy Were we planning on having a web application component for IDDE to present the ETL issues to the user?

2. @talllguy and I can make sure to lock down the editing in a way that only allows a new record or edits to the FLDSC_SITE table.

[johnshiu]

@KCI-Ablowers, I think this makes sense. I should be able to make these changes and add the data reviewer items as well.

As far as timelines, when do we expect the IDDE team to begin collection? Just want to plan accordingly.

[KCI-Ablowers]

@johnshiu I believe NTP for field work won't occur until mid to late April based on what I have heard from @brentreeves75.

For number one above, we may want to update the text on the button to "sync and validate" or something along those lines, just to make it more clear on the UI.

[johnshiu]

@KCI-Ablowers, these updates have been made and committed to SHA production. Please try things out and let me know how it goes.

Once it's confirmed as working as expected, I can wipe the IDDE versions.

[KCI-Ablowers]

@johnshiu I got an error on the IDDE NPDES FDM page - ID Num. 3109 when I started a sync this morning. Can you take a look?

[johnshiu]

@KCI-Ablowers @talllguy

Something appears to be going wrong with the service and/or database. I'm getting an error when attempting to delete via the feature service:

Unable to complete operation. Unable to perform deleteFeatures operation.

Also, deleting manually from Oracle does not appear to be working. It just hangs:

This was definitely working previously, so I wonder if the database is having some issues. I will contact Mark/Peggy about this.

[johnshiu]

@KCI-Ablowers, I just retried after @talllguy reset the services, and it is working again. Very odd behavior, but I think we can assume that the IDDE inspection service may have stopped working after a while. Perhaps the next time this happens, we can check the server logs.

[talllguy]

The service was completely stopped the last time I went and looked at it. Maybe it errored out.

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

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

📧📲

On Tue, May 7, 2019 at 10:43 AM -0400, "John Shiu" notifications@github.com<mailto:notifications@github.com> wrote:

@KCI-Ablowershttps://github.com/KCI-Ablowers, I just retried after @talllguyhttps://github.com/talllguy reset the services, and it is working again. Very odd behavior, but I think we can assume that the IDDE inspection service may have stopped working after a while. Perhaps the next time this happens, we can check the server logs.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/kcigeospatial/MDOT-SHA-NPDES-Next-Gen/issues/313#issuecomment-490109960, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AAN6IKKHSRBFLZ5GCTDVKETPUGIP5ANCNFSM4HCXDL4A.

[KCI-Ablowers]

@johnshiu & @talllguy I think the UI is functioning as expected and no issues were present in the test inspections I QA/QC'd.

[KCI-Ablowers]

@johnshiu I am working on QC for the IDDE inspections and wanted to confirm two things -

1

Want to confirm that this check is operating correctly -

Test#	Table	Field	Rule
157	FLDSC_SITE	STRUCTURE_ID	Cannot be NULL

Since the STRUCTURE_ID isn't written back into the FLDSC_SITE table in the feature service, this is the only way to confirm that every Structure has a FLDSC_SITE record. Since the inspection must launch from the hyperlink in the FLDSC_SITE table, we can assume there is a record for it.

The number of records that join (assume existing records) and the FLDSC_SITE with a null STRUCTURE_ID is consistent with our initial 186 sites.

2

I am not sure that the tests are working correctly because I know we entered a few blank FLDSC_SITE records to quickly launch the inspections while out in the field.

Test#	Table	Field	Rule
156	FLDSC_SITE	STREAMDIST	Cannot be NULL
155	FLDSC_SITE	LOC_SCREEN	Cannot be NULL
158	FLDSC_SITE	VIC_SCREEN	Cannot be NULL
183	FLDSC_SITE	STRUCTURE_ID	If has value, then DESC_OUTF must equal PIPES MTRL_PIPE

Knowing these checks work will make QC easier because of the ID situation I described in the first header since I can't narrow down the records based on null values.

[johnshiu]

@KCI-Ablowers, yes, I believe the null checks are working; here's a snapshot of some of the ETL_ISSUES in KCI_1:

The Test 183, however, is not in the system right now. I'm not sure how that would work exactly; is that doing a join against the STRUCTURE_ID to get the structure, and then another join against the related conveyance if the structure is an upstream/downstream structure, and then another join against the pipe table, if the conveyance has it, and then checking an exact text match for the FLDSC_SITE.DESC_OUTFL == PIPES.MTRL_PIPE? Seems pretty convoluted if so!

[KCI-Ablowers]

@johnshiu Ok, that screenshot makes sense because those are all test inspections, I would agree that test 183 shoulds like a bit much when you lay it out like that. Leave 183 out for now and worse case we can check manually.

[KCI-Ablowers]

@johnshiu While reviewing and QC'ing IDDE inspection data for this cycle, I noticed that new FLDSC_SITE records do not appear in the version export you gave me here - https://github.com/kcigeospatial/MDOT-SHA-NPDES-Next-Gen/issues/363#issuecomment-514282489.

There are 34 records in FLDSC_SITE that join to our 181 unique inspection records. The other 147 FLDSC_SITE records are not present in the export. Am I missing something?

[johnshiu]

@KCI-Ablowers, I see what the problem was; the field processing routine did not run before the submit. I'll update the scripts and re-run the inventory submit so that all the records should appear.

[johnshiu]

@KCI-Ablowers, please use this link to download the latest snapshot of the IDDE_QC version: http://apps.ecointegration.com/files/NPDES/NPDES_IDDE_QC_20190729.gdb.zip

I believe it has all the needed records. It also includes 3 FLOW_CHAR records that were not present in the previous one due to a schema issue (field schema had a 255-length COM_FLOW field, versus a 120-length field in source; I updated source to 255-length).

[KCI-Ablowers]

@johnshiu Thanks, they are in that version. If I need to make updates to a FLDSC_SITE record, can I do so in the IDDE version and run the sync and submit processes even though no inspections are present?