Create process for updating BDIT database with live FLOW data

[aharpalaniTO]

No description provided.

[aharpalaniTO]

Process likely to involve:

1. Create links to FLOW (oracle DB) within Postgres
2. Identify new artery codes
3. Run new artery codes through some sort of QC process to ensure they are assigned to artery_tcl correctly
4. Update all respective tables in Postgres (arterydata, cnt_det, det, etc.)
5. Re-run count data checks developed in Develop Automated Process for Identifying Bad Count Data #23

[aharpalaniTO]

I will produce initial delta tables + summaries. Will leave to @sunnyqywang to determine best approach. Most likely will look to refresh our version of FLOW counts every month, if the process isn't too time consuming.

[aharpalaniTO]

@aharpalani started to develop this process; still in progress. @sunnyqywang has updated artery locations to reflect more recent version of FLOW.

[aharpalaniTO]

@aharpalaniTO wrote process for creating 5 tables:

• new_arterydata: diff between traffic.arterydata AND flow::arterydata
• new_countinfo: diff between traffic.countinfo AND flow::countinfo
• new_cnt_det: all records from traffic.cnt_det where count_info_id IN new_countinfo
• new_countinfomics: diff between traffic.countinfomics AND flow::countinfomics
• new_det: all records from traffic.det where count_info_id IN new_countinfomics

[aharpalaniTO]

@aharpalaniTO and @sunnyqywang to discuss streamlining process for future use

[sunnyqywang]

@aharpalaniTO loading new data from FLOW to the traffic schema, and @sunnyqywang's script only processes the difference between updated tables and their clean versions in prj_volume schema.