{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "889e3048",
   "metadata": {},
   "outputs": [],
   "source": [
    "from create_daily_csv import xarray2df,check_data_gap,readnetcdf_in_shp_db,get_discharge_from_DB\n",
    "from create_daily_csv import spatial_stats_daily_input\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "from matplotlib import pyplot as plot\n",
    "import rioxarray as xr\n",
    "import datetime\n",
    "import psycopg2\n",
    "from geopandas import GeoSeries\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import xarray as xr\n",
    "import geopandas as gpd\n",
    "from shapely.geometry import Point, Polygon\n",
    "import matplotlib.pyplot as plt\n",
    "import psycopg2\n",
    "import shapely.wkb as wkb\n",
    "from geopandas import GeoSeries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "dff9323e",
   "metadata": {},
   "outputs": [],
   "source": [
    "LIST =['ADO_DSC_CH03_0075',\n",
    "     'ADO_DSC_AT31_0254',\n",
    "     'ADO_DSC_ITC1_0072',\n",
    "     'ADO_DSC_ITC1_0020',\n",
    "     'ADO_DSC_CH07_0147',\n",
    "     'ADO_DSC_AT31_0206',\n",
    "     'ADO_DSC_ITH1_0012',\n",
    "     'ADO_DSC_AT12_0280',\n",
    "     'ADO_DSC_CH07_0100',\n",
    "     'ADO_DSC_CH05_0201',\n",
    "     'ADO_DSC_SI03_0148',\n",
    "     'ADO_DSC_ITC1_0037',\n",
    "     'ADO_DSC_FRK2_0042',\n",
    "     'ADO_DSC_CH04_0011',\n",
    "     'ADO_DSC_ITH2_0035',\n",
    "     'ADO_DSC_SI03_0033',\n",
    "     'ADO_DSC_FRK2_0041',\n",
    "     'ADO_DSC_ITH5_0006',\n",
    "     'ADO_DSC_CH07_0006']\n",
    "\n",
    "tif_fileName =  r'C:\\Users\\mmazzolini\\OneDrive - Scientific Network South Tyrol\\DEM_EU\\DEM.tif'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f5fcdfad",
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "table=pd.DataFrame(data=None)\n",
    "dem=xr.open_rasterio(tif_fileName)\n",
    "# Open the shape file and reproject it to the MESCAN-Surfex grid (unit=meters)\n",
    "\n",
    "conn = psycopg2.connect(host=\"10.8.244.31\",\n",
    "                   database=\"climate_data\",\n",
    "                   user=\"ado_user\",\n",
    "                   password=\"hydro#ado\",\n",
    "                   port=5432)\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "755aa18f",
   "metadata": {},
   "outputs": [],
   "source": [
    "for STAT_CODE in LIST:\n",
    "   \n",
    "\n",
    "        \n",
    "    # get the metadata\n",
    "    query = f\"\"\"\n",
    "            SELECT \"geom\" FROM \"hydrology\".\"catchment_area\" WHERE \"id_station\" = '{STAT_CODE}'    \n",
    "            \"\"\"\n",
    "    df = pd.read_sql_query(query, conn)\n",
    "    \n",
    "\n",
    "    \n",
    "    shp=GeoSeries(wkb.loads(df.geom[0], hex=True))\n",
    "    shp=shp.set_crs(\"EPSG:4326\")\n",
    "    \n",
    "    \n",
    "    dem_catch=dem.rio.clip(shp[0],from_disk=True)\n",
    "\n",
    "    h=np.array(dem_catch)\n",
    "    h = h[h!=-99999.]\n",
    "    \n",
    "    h_mean=h.mean()\n",
    "    \n",
    "    update = {}\n",
    "    update['name'] = STAT_CODE\n",
    "    update['mean_h']=h_mean\n",
    "    \n",
    "    table = table.append(update,ignore_index=True)\n",
    "    \n",
    "# close the connection when finished\n",
    "cur.close()\n",
    "conn.close()   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe610a00",
   "metadata": {},
   "outputs": [],
   "source": [
    "table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f6edb55",
   "metadata": {},
   "outputs": [],
   "source": [
    "table.to_csv('id_stat_avgh.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}