search

kcigeospatial / balt_co_ETL

This will provide a place to track internal issues around the Baltimore County NPDES NCT application ETL.
0 stars 0 forks source link

ETL - IDDE.CB_SEG_SHED is not populated #90

Open dhenry-KCI opened 6 years ago

dhenry-KCI commented 6 years ago

MDE.IDDE.CB_SEG_SHED is conditionally required if MDE.IDDE.ILLICIT_CREDIT == 'Yes'. The ETL excel file (v8) states that IDDE.CB_SEG_SHED uses source "Derive from associated spatial overlay view."

However, the ETL did not populate IDDE.CB_SEG_SHED for any of the records in MDE.IDDE. This is the case for IDDE records where ILLICIT_CREDIT is 'No' or 'Yes'.

image

see original issue here for image: https://github.com/kcigeospatial/balt_co_npdes/issues/408

dhenry-KCI commented 6 years ago

@gerrykelly Please update the ETL translation where IDDE. ILLICIT_CREDIT = TRUE, system populates IDDE.CB_SEG_SHED based on CB_SEG_SHED overlay

gerrykelly commented 6 years ago

@dhenry-KCI Not clear on where this attribute would come from and what CB_SEG_SHED overlay/associated spatial overlay view is (nor is Brendan). I imagine it needs to come from a spatial view for outfalls then joined to IDDE

gerrykelly commented 6 years ago

@leeensminger .... Just in case you can interpret this, please see the previous question

dhenry-KCI commented 6 years ago

@gerrykelly this value should be populated from the reference dataset NDPES.LAND_RIVER_SEG.CBSEG_92

There are some null values in this dataset - the county will need to clean up the outstanding NUll values.

gerrykelly commented 6 years ago

@dhenry-KCI @leeensminger To implement this approach to populating the CB_SEG_SHED field I needed to create a new spatial view that joins IDDE_INSP to OUTFALLS and spatially joins to LAND_RIVER_SEG (there is no NDPES.LAND_RIVER_SEG.CBSEG_92 for an ETL IDDE_INSP record that goes to MDE IDDE unless it relates to an OUTFALL, correct?).

But then when I was revising my code, I noticed that IDDE.CB_SEG_SHED should be getting populated if the data is correct to support it. It is now coming from the IDDE_CREDIT CB_SEG_SHED field (and converting the domain value). So if you have the OUTFALL-IDDE_INSP record, then a related IDDE_CHAR record with a related IDDE_CREDIT record (in which case IDDE_CHAR.ILLICIT_CREDIT should be (and is on our data) True, you will get the IDDE_CREDIT.CB_SEG_SHED value in the MDE IDDE output.

Before proceeding to change the ETL and possibly throwing more good time after bad, I though you should review this and make the call, If IDDE_CREDIT.CB_SEG_SHED is not good for this, why and what happens on the app side to populate it?

Let me know how to proceed.

For RY 2019, there is only one IDDE_INSP record (and I updated an OUTFALLS.OUTFALL_ID to match it's FEATURE_ID), so you can test and get: image

dhenry-KCI commented 6 years ago

@BC89 @gerrykelly BC - im trying to test this issue and i am encountering as issue within the IDDE workflow. Under the Discharge Characteristics, the drop downs for both NPDES.IDDE_CHAR.Illicit_Q (D_TRUE_FALSE) and NPDES.IDDE_CHAR.Illicit Credit Taken(D_TRUE_FALSE) are not responding. When i click the dropdown for both the system does not display and values.

BC89 commented 6 years ago

done. pls test