Selection Set 1B - add BMPPOI to existing selection set

[leeensminger]

From balt_co_npdes 387

ETL shows all Rest BMPs BMPPOI_ID as orphans (45 records). However, the MDE NDPES guidance, page 41 states BMPPOI is mandatory for RESTBMPs. Shouldn't every REST BMP have a BMPPOI made?

[leeensminger]

In addition, for the related BMPDrainageArea in the ETL, the BMPOI_ID field states "missing"

[leeensminger]

@gerrykelly Every target RestBMP output needs to also have an MDE.BMPPOI record. The template MDE geodatabase provides for this relationship with a RestBMP.BMPPOI_ID field (required field). Currently, we populate this RestBMP.BMPPOI_ID field as "orphan".

• [ ] Please update the selection set logic as highlighted below:

• [ ] Update RestBMP.BMPPOI_ID to be populated with the MDE ID for the associated BMPPOI
• [ ] Implement the field-by-field source to target for BMPPOIs. The source to target is not different than it is for selection set 1D, but the original v8 Excel document has been updated to include this logic within selection set 1B, and those rows copied here:

Entity_Type	Entity_Name	Field_Name	Type	Length	Domain/Lookup_File	isNullable	ETL_STEP_NUM	Selection_Set_Reference	Source	Source_Type	Source_Length	Source_Domain	Source_isNullable	Nullable_Fields_Match	Translation
Feature Class	BMPPOI	APPR_DATE	Date	8		FALSE	822	1B	BMP_POIS.APPR_DATE	Date	8		TRUE	Possible nullable mismatch	None
Feature Class	BMPPOI	BMPPOI_ID	String	13		FALSE	823	1B	BMP_POIS.BMP_POI_NUM	String	20		TRUE	Possible nullable mismatch	"BA" + YY +  BMP_POI_NUM YY = Rightmost 2 characters of REPORTING_YEAR
Feature Class	BMPPOI	BUILT_DATE	Date	8		FALSE	824	1B	BMP_POIS.APPR_DATE	Date	8		TRUE	Possible nullable mismatch	None
Feature Class	BMPPOI	GEN_COMMENTS	String	255		TRUE	825	1B	BMP_POIS.MDE_COMMENTS	String	255		TRUE		None
Feature Class	BMPPOI	IMP_ACRES	Double	8		FALSE	826	1B	BMP_DAS.IMP_ACRES	Double	8		TRUE	Possible nullable mismatch	None
Feature Class	BMPPOI	LAND_USE	SmallInteger	2	dMDPLandUse	FALSE	827	1B	Derive from associated spatial overlay view						None
Feature Class	BMPPOI	LAST_CHANGE	Date	8		FALSE	828	1B	None						Set all values as current system date of ETL processing.
Feature Class	BMPPOI	LU_COUNTY	String	25		TRUE	829	1B	None						None
Feature Class	BMPPOI	MD_EAST	Double	8		FALSE	830	1B	Derive on the fly						None
Feature Class	BMPPOI	MD_NORTH	Double	8		FALSE	831	1B	Derive on the fly						None
Feature Class	BMPPOI	PE_ADR	Double	8		FALSE	832	1B	BMP_POIS.PE_ADR	Double	8		TRUE	Possible nullable mismatch	None
Feature Class	BMPPOI	PE_REQ	Double	8		FALSE	833	1B	BMP_POIS.PE_REQ	Double	8		TRUE	Possible nullable mismatch	None
Feature Class	BMPPOI	PERMIT_NUM	String	11		FALSE	834	1B	target.PermitInfo.PERMIT_NUM	String			FALSE		Find in mde.PermitInfo where PermitInfo.REPORTING_YEAR = user-input Reporting Year, then get mde.PermitInfo.PERMIT_NUM.
Feature Class	BMPPOI	Q_POST	Double	8		TRUE	835	1B	BMP_POIS.Q_POST	Double	8		TRUE		None
Feature Class	BMPPOI	Q_PRE	Double	8		TRUE	836	1B	BMP_POIS.Q_PRE	Double	8		TRUE		None
Feature Class	BMPPOI	Q_WOODS	Double	8		TRUE	837	1B	BMP_POIS.Q_WOODS	Double	8		TRUE		None
Feature Class	BMPPOI	RCN_POST	SmallInteger	2		TRUE	838	1B	BMP_POIS.RCN_PRE	SmallInteger	2		TRUE		None
Feature Class	BMPPOI	RCN_PRE	SmallInteger	2		TRUE	839	1B	BMP_POIS.RCN_POST	SmallInteger	2		TRUE		None
Feature Class	BMPPOI	RCN_WOODS	SmallInteger	2		TRUE	840	1B	BMP_POIS.RCN_WOODS	SmallInteger	2		TRUE		None
Feature Class	BMPPOI	WATERSHED12DGT	String	12	dHUC12digit	TRUE	841	1B	Derive from associated spatial overlay view						None
Feature Class	BMPPOI	WATERSHED8DGT	String	8	dMD8digit	FALSE	842	1B	Derive from associated spatial overlay view						None

[leeensminger]

@gerrykelly The data in KCI dev has been updated to support this. You should find 45 SW_TRACKING records for selection set 1B, that relate to 45 BMP records, that now relate to 45 BMP_POIS records. BMP_POIS.MDE_COMMENTS = "Selection set 1B".

[gerrykelly]

reminder to update View ETL_BMP_DAS_ID_AND_ACRES_VW

[gerrykelly]

@leeensminger I think this is working at KCI, (in this case and others, there may still be some attributes not getting populated, but I tried my best to cover all bases). With the relationships and populating all the involved fields with the correct IDs from the MDE world, I get 39 BMPDrainageAreas matching the 45 restbmps, but going back to the source data I think that is correct, please check.

[leeensminger]

@gerrykelly Yes 39 BMPDrainageAreas for the 45 RestBMPs is correct.

So as it is now, I'm guessing in this case you are populating BMPDrainageArea.BMPPOI_ID as null - correct? I believe the MDE target gdb allows this as null. I don't know if populating this as null is right or wrong - none of our clients have used this RestBMP-POI implementation before.

[leeensminger]

@gerrykelly @dhenry-KCI looks ok at KCI