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. <p>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).

大温哥华地区人口（20110729区域增长战略预测——人口、住房与就业2006–2041文件）到2040年将大幅增长，寻找新的饮用水水库水源（2015_用水消耗_统计文件）至关重要。为推动区域发展，需对饮用水问题进行优化与估算（数据挖掘文件）。目前大温哥华地区的三个水库水源为Capilano、Seymour和Coquitlam，其处理后的水正供应给用户使用。线性优化（LP）模型（优化与敏感性报告文件）可阐明各水库及区域的饮用水量。事实上，不列颠哥伦比亚省（B.C.）政府针对2040年前增长的人口制定了专项战略，以推动目标实现。此外，另一关键因素在于需对新饮用水源进行估算与监测，并预测2040年前的可行水源（水井）。因此，政府需决策地下水的使用量。本项目目标分为两步：（1）构建三个水库的优化模型；（2）估算2040年前的新水源。<p>本项目的数据分析流程包括：使用六个软件（Trifacta Wrangler、AMPL、Excel Solver、Arc GIS及SQL）进行数据分析，并在Tableau中实现可视化。1. Trifacta Wrangler软件用于清洗数据（数据挖掘文件）。2. AMPL与Excel Solver软件用于优化大温哥华地区的饮用水消耗（优化与敏感性报告文件中的数据）。3. 通过ArcGIS软件，ArcMap整合原始数据、水库优化结果及2040年人口估算数据（Tableau用GIS地图文件）。4. 使用SQL软件在Tableau中对大温哥华地区2040年前的饮用水源进行可视化、估算与优化（导出Tableau数据文件）。