docs/waternet.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The water network consists of a streamflow network, consisting of connections between river gauges, reservoirs, and junctions, and a canal network.  The canal network is a bipartite network, with links from nodes in the streamflow network and the counties.  The `waternet.jl` file loads these networks into the global environment.  It uses cached Julia Data (`.jld`) files to speed up the loading process, creating them if they aren't present in the `data` directory."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scientific documentation\n",
    "\n",
    "The river network is documented here:\n",
    "https://www.overleaf.com/read/gftdkjjkdrsn"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The Canal Network\n",
    "\n",
    "The canal network is first produced in `R` and then loaded into Julia.  In Julia, it is used as just a DataFrame with the names of the river network nodes added."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The columns in the `waterdraws.jld` data are as follows:\n",
    "\n",
    "* The **fips** column is the FIPS code of the receiving county.\n",
    "* The **source** column is the row number of the gauge, as it appears in the `network` variable of the `waternet.RData` file.\n",
    "* The **justif** column provides a justification for why the gauge should be available for feeding the county.  It contains short categorical entries, described in the `R` script that generates the `countydraws.RData` file (`network3/demand/allcounties.R`).\n",
    "* The **downhill** column compares the elevation of the county with the elevation of the gauge, if we know it.  It is 1 if county's average elevation is below the gauges, such that the water can be tapped for free.\n",
    "* The **exdist** column is greater than 0 if the county had to be connected to a gauge arbitrarily to ensure that it had any source.  In this case, the county is connected to the closest gauge, and this column is the geodesic distance in *km*.\n",
    "* The **gaugeid** is the only column added by Julia, and is in the same format as the keys in the `wateridverts` dictionary, which allows easy access to the nodes in the river network by name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><tr><th></th><th>fips</th><th>source</th><th>justif</th><th>downhill</th><th>exdist</th><th>gaugeid</th></tr><tr><th>1</th><td>1001</td><td>2262</td><td>contains</td><td>0</td><td>0.0</td><td>usgs.02422500</td></tr><tr><th>2</th><td>1003</td><td>2174</td><td>contains</td><td>0</td><td>0.0</td><td>usgs.02376500</td></tr><tr><th>3</th><td>1003</td><td>2175</td><td>contains</td><td>0</td><td>0.0</td><td>usgs.02377570</td></tr><tr><th>4</th><td>1003</td><td>2176</td><td>contains</td><td>0</td><td>0.0</td><td>usgs.02378170</td></tr><tr><th>5</th><td>1003</td><td>2177</td><td>contains</td><td>0</td><td>0.0</td><td>usgs.02378300</td></tr><tr><th>6</th><td>1003</td><td>2178</td><td>contains</td><td>0</td><td>0.0</td><td>usgs.02378500</td></tr><tr><th>7</th><td>1003</td><td>12946</td><td>contains</td><td>NA</td><td>0.0</td><td>junction.1405-dn</td></tr><tr><th>8</th><td>1003</td><td>12990</td><td>contains</td><td>NA</td><td>0.0</td><td>junction.1487-dn</td></tr><tr><th>9</th><td>1003</td><td>13046</td><td>contains</td><td>NA</td><td>0.0</td><td>junction.1607-up</td></tr><tr><th>10</th><td>1005</td><td>2085</td><td>contains</td><td>1</td><td>0.0</td><td>usgs.02342933</td></tr><tr><th>11</th><td>1005</td><td>13389</td><td>contains</td><td>NA</td><td>0.0</td><td>junction.2322-up</td></tr><tr><th>12</th><td>1005</td><td>13404</td><td>contains</td><td>NA</td><td>0.0</td><td>junction.2350-up</td></tr><tr><th>13</th><td>1007</td><td>2275</td><td>contains</td><td>1</td><td>0.0</td><td>usgs.02424000</td></tr><tr><th>14</th><td>1009</td><td>2314</td><td>contains</td><td>0</td><td>0.0</td><td>usgs.02449882</td></tr><tr><th>15</th><td>1009</td><td>2315</td><td>contains</td><td>0</td><td>0.0</td><td>usgs.02450000</td></tr><tr><th>16</th><td>1009</td><td>2316</td><td>contains</td><td>0</td><td>0.0</td><td>usgs.02450180</td></tr><tr><th>17</th><td>1009</td><td>2321</td><td>contains</td><td>0</td><td>0.0</td><td>usgs.02455000</td></tr><tr><th>18</th><td>1009</td><td>2322</td><td>contains</td><td>1</td><td>0.0</td><td>usgs.02455185</td></tr><tr><th>19</th><td>1009</td><td>11417</td><td>contains</td><td>1</td><td>0.0</td><td>reservoir.2341</td></tr><tr><th>20</th><td>1009</td><td>11423</td><td>contains</td><td>0</td><td>0.0</td><td>reservoir.2347</td></tr><tr><th>21</th><td>1009</td><td>14095</td><td>contains</td><td>NA</td><td>0.0</td><td>junction.3862-dn</td></tr><tr><th>22</th><td>1009</td><td>14110</td><td>contains</td><td>NA</td><td>0.0</td><td>junction.3896-up</td></tr><tr><th>23</th><td>1009</td><td>14181</td><td>contains</td><td>NA</td><td>0.0</td><td>junction.4085-up</td></tr><tr><th>24</th><td>1011</td><td>11553</td><td>contains</td><td>0</td><td>0.0</td><td>reservoir.2482</td></tr><tr><th>25</th><td>1015</td><td>2233</td><td>contains</td><td>1</td><td>0.0</td><td>usgs.02403310</td></tr><tr><th>26</th><td>1015</td><td>2234</td><td>contains</td><td>1</td><td>0.0</td><td>usgs.02403500</td></tr><tr><th>27</th><td>1015</td><td>11425</td><td>contains</td><td>1</td><td>0.0</td><td>reservoir.2349</td></tr><tr><th>28</th><td>1019</td><td>2225</td><td>contains</td><td>0</td><td>0.0</td><td>usgs.02398300</td></tr><tr><th>29</th><td>1019</td><td>2227</td><td>contains</td><td>0</td><td>0.0</td><td>usgs.02399200</td></tr><tr><th>30</th><td>1019</td><td>2228</td><td>contains</td><td>0</td><td>0.0</td><td>usgs.02400100</td></tr><tr><th>&vellip;</th><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td><td>&vellip;</td></tr></table>"
      ],
      "text/plain": [
       "21598x6 DataFrames.DataFrame\n",
       "| Row   | fips  | source | justif      | downhill | exdist  |\n",
       "|-------|-------|--------|-------------|----------|---------|\n",
       "| 1     | 1001  | 2262   | \"contains\"  | 0        | 0.0     |\n",
       "| 2     | 1003  | 2174   | \"contains\"  | 0        | 0.0     |\n",
       "| 3     | 1003  | 2175   | \"contains\"  | 0        | 0.0     |\n",
       "| 4     | 1003  | 2176   | \"contains\"  | 0        | 0.0     |\n",
       "| 5     | 1003  | 2177   | \"contains\"  | 0        | 0.0     |\n",
       "| 6     | 1003  | 2178   | \"contains\"  | 0        | 0.0     |\n",
       "| 7     | 1003  | 12946  | \"contains\"  | NA       | 0.0     |\n",
       "| 8     | 1003  | 12990  | \"contains\"  | NA       | 0.0     |\n",
       "| 9     | 1003  | 13046  | \"contains\"  | NA       | 0.0     |\n",
       "| 10    | 1005  | 2085   | \"contains\"  | 1        | 0.0     |\n",
       "| 11    | 1005  | 13389  | \"contains\"  | NA       | 0.0     |\n",
       "⋮\n",
       "| 21587 | 51199 | 1281   | \"down-pipe\" | 1        | 83.2334 |\n",
       "| 21588 | 51650 | 10907  | \"down-pipe\" | 1        | 33.3593 |\n",
       "| 21589 | 51700 | 10907  | \"down-pipe\" | 1        | 31.2913 |\n",
       "| 21590 | 53023 | 8784   | \"down-pipe\" | 1        | 26.3208 |\n",
       "| 21591 | 53029 | 8407   | \"down-pipe\" | 1        | 18.3806 |\n",
       "| 21592 | 53055 | 8423   | \"down-pipe\" | 1        | 37.3665 |\n",
       "| 21593 | 54017 | 2474   | \"down-pipe\" | 1        | 28.941  |\n",
       "| 21594 | 54063 | 2637   | \"down-pipe\" | 1        | 51.7676 |\n",
       "| 21595 | 54087 | 2656   | \"down-pipe\" | 1        | 57.5247 |\n",
       "| 21596 | 55015 | 4093   | \"down-pipe\" | 1        | 57.7027 |\n",
       "| 21597 | 55029 | 9746   | \"down-pipe\" | 1        | 59.0901 |\n",
       "| 21598 | 8014  | 5001   | \"down-pipe\" | 1        | 6.67641 |\n",
       "\n",
       "| Row   | gaugeid            |\n",
       "|-------|--------------------|\n",
       "| 1     | \"usgs.02422500\"    |\n",
       "| 2     | \"usgs.02376500\"    |\n",
       "| 3     | \"usgs.02377570\"    |\n",
       "| 4     | \"usgs.02378170\"    |\n",
       "| 5     | \"usgs.02378300\"    |\n",
       "| 6     | \"usgs.02378500\"    |\n",
       "| 7     | \"junction.1405-dn\" |\n",
       "| 8     | \"junction.1487-dn\" |\n",
       "| 9     | \"junction.1607-up\" |\n",
       "| 10    | \"usgs.02342933\"    |\n",
       "| 11    | \"junction.2322-up\" |\n",
       "⋮\n",
       "| 21587 | \"usgs.02041650\"    |\n",
       "| 21588 | \"reservoir.1822\"   |\n",
       "| 21589 | \"reservoir.1822\"   |\n",
       "| 21590 | \"usgs.13334450\"    |\n",
       "| 21591 | \"usgs.12158010\"    |\n",
       "| 21592 | \"usgs.12201500\"    |\n",
       "| 21593 | \"usgs.03058500\"    |\n",
       "| 21594 | \"usgs.03178500\"    |\n",
       "| 21595 | \"usgs.03191500\"    |\n",
       "| 21596 | \"usgs.05423000\"    |\n",
       "| 21597 | \"reservoir.621\"    |\n",
       "| 21598 | \"usgs.06720820\"    |"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "using DataFrames\n",
    "draws = deserialize(open(\"../data/waterdraws.jld\", \"r\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Development and contributing\n",
    "\n",
    "You can replace the `data/countydraws.RData` file with another R Data file which contains the variable `draws`.  `draws` should be a `data.frame` minimally with the columns **fips** and **source**.\n",
    "\n",
    "Until the script for generating the `countydraws.RData` file is migrated into the repository, please do the following to extend the dataset:\n",
    "\n",
    "1. Copy the `countydraws.RData` file into a new `sources/waternet` directory as `countydraws.v1.RData`.\n",
    "2. Add your script for modifying the data to the same directory, and have it output a new `countydraws.v2.RData` file.  Copy this into the `data` directory.\n",
    "3. If there is already a `countydraws.v(N).RData` (for $N \\ge 2$) file, use the latest one as your input, and output a file `countydraws.v(N+1).RData`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Future work:\n",
    "* Have each canal specify a flow limit.\n",
    "* Include a column for an optional price for using that canal."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Missing canals\n",
    "*By Laureline*\n",
    "\n",
    "Several utilities and facilities operate accros multiple counties. For instance, New York City water supply system source its water from the Catskill Mountains and the Delaware river in Delaware County and distribute the water to all of the boroughs.\n",
    "\n",
    "Utilities of this type are not rare and occur at many locations across CONTUS. So that the water network allows to link the point of source and the point of use, additionnal connections have been added to the countydraws file. \n",
    "\n",
    "##### Cross-county utilities\n",
    "The first step consists in finding the utilities and facilities that operate across multiple counties. This is done by finding all of the water utilities operating within a given county on the Drinking Water Mapping Application to Protect Source Waters website (https://dwmapspublic.rti.org/). The website then redirects to the Safe Drinking Water Information System (SDWIS) Federal Reporting Services, which provides the list of counties served by the given water system (such as https://ofmpub.epa.gov/apex/sfdw/f?p=SDWIS_FED_REPORTS_PUBLIC:PWS_SEARCH:::::PWSID:NY7003493). This led to the construction of a dataset canals.txt with the first column being the source county (referred by FIPS), and the other ones listing the counties the water facilities present in the source county serve. \n",
    "\n",
    "##### Add missing canals to the water network\n",
    "The second step is to complete the countydraws dataset. This is done using the R script script_incorporation_missing_data.R, which simply adds a connection between each gauge within a source county to the point of use county.\n",
    "\n",
    "##### Current status\n",
    "As the number of water utilities is consequent, this task has not been accomplished for all counties yet. As a starting point, we focused our research on problematic areas: counties that presented a suspicious public supply withdrawals/population ratio, and counties with important population. The following plot (fig 1.a) shows the USGS 2010 public supply fresh water withdrawals in function of the population. The dots in green are the counties that have been added to the missing canals set. \n",
    "![missingcanals](https://raw.githubusercontent.com/AmericasWater/operational-problem/master/data/prepared/missing_canals.png)\n",
    "Using this new set of connections, a public supplied demand has been estimated by assuming that the withdrawals performed within a given county are distributed to all of the linked counties (including itself) proportionnally to the population they contain. The new estimated demand is illustrated in fig 1.b in function of population and a comparison for the treated counties between demands and withdrawals can be found in fig 1.c.\n",
    "\n",
    "The following figure shows the difference between withdrawals and the estimated demand for all CONTUS.\n",
    "![urbantransfer](https://raw.githubusercontent.com/AmericasWater/operational-problem/master/data/prepared/urban_transfert.png)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Julia 0.4.2",
   "language": "julia",
   "name": "julia-0.4"
  },
  "language_info": {
   "file_extension": ".jl",
   "mimetype": "application/julia",
   "name": "julia",
   "version": "0.4.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}