To Estimate and Optimize the Source of Drinking Water for Metro Vancouver until 2040

The population of Metro Vancouver (20110729Regional Growth Strategy Projections Population, Housing and Employment 2006 – 2041 File) will have increased greatly by 2040, and finding a new source of reservoirs for drinking water (2015_ Water Consumption_ Statistics File) will be essential. This issue of drinking water needs to be optimized and estimated (Data Mining file) with the aim of developing the region. Three current sources of water reservoirs for Metro Vancouver are Capilano, Seymour, and Coquitlam, in which the treated water is being supplied to the customer. The linear optimization (LP) model (Optimization, Sensitivity Report File) illustrates the amount of drinking water for each reservoir and region. In fact, the B.C. government has a specific strategy for the growing population till 2040, which leads them toward their goal. In addition, another factor is the new water source for drinking water that needs to be estimated and monitored to anticipate the feasible water source (wells) until 2040. As such, the government will have to make a decision on how much groundwater is used. The goal of the project is two steps: (1) an optimization model for three water reservoirs, and (2) estimating the new source of water to 2040. The process of data analysis for the project includes: the data is analyzed with six software—Trifacta Wrangler, AMPL, Excel Solver, Arc GIS, and SQL—and is visualized in Tableau. 1. Trifacta Wrangler Software clean data (Data Mining file). 2. AMPL and Solver Excel Software optimize drinking water consumption for Metro Vancouver (data in the Optimization and Sensitivity Report file). 3. ArcMap collaborates the raw data and result of the optimization water reservoir and estimating population till 2040 with the ArcGIS software (GIS Map for Tableau file). 4. Visualizing, estimating, and optimizing the source of drinking water for Metro Vancouver until 2040 with SQL software in Tableau (export tableau data file).温哥华都会区（Metro Vancouver）的人口（数据来源为20110729发布的《2006-2041年区域增长战略预测人口、住房与就业文件》）预计到2040年将大幅增长，因此寻找新的饮用水水源地（数据来源为《2015年水资源消耗统计文件》）将成为必要之举。为推动该区域发展，需对饮用水供需问题开展优化建模与估算工作（数据挖掘（Data Mining）文件）。 目前温哥华都会区的饮用水供水水库共有三处，分别为卡皮拉诺（Capilano）、西摩（Seymour）与库特兰（Coquitlam），经处理后的净水将供应给终端用户。线性优化（Linear Optimization, LP）模型（《优化与敏感性分析报告文件》）可明确各水库与对应区域的供水量。 事实上，不列颠哥伦比亚省（British Columbia, B.C.）已针对至2040年的人口增长制定了专项战略，以推进其既定目标的达成。此外，另一项核心议题是需对新饮用水水源进行估算与监测，以预判至2040年时可行的水源（水井）供给情况，因此政府需就地下水的使用规模做出决策。 本项目的目标分为两个部分：（1）针对三处饮用水水库构建优化模型；（2）估算至2040年时的新增饮用水水源。本项目的数据分析流程涉及六款工具软件：Trifacta Wrangler、AMPL、Excel Solver、ArcGIS、SQL与Tableau，具体实施步骤如下： 1. 使用Trifacta Wrangler软件完成数据清洗工作（数据挖掘文件）。 2. 借助AMPL与Excel Solver软件对温哥华都会区的饮用水消耗量开展优化建模（数据来源为《优化与敏感性分析报告文件》）。 3. 借助ArcMap与ArcGIS软件整合原始数据、水库优化结果及至2040年的人口预测数据（对应《Tableau用GIS地图文件》）。 4. 结合SQL软件与Tableau工具，对温哥华都会区至2040年的饮用水水源开展可视化、估算与优化工作（对应《Tableau导出数据文件》）。