Skip to content
Snippets Groups Projects
stations_elevation.ipynb 8.58 KiB
Newer Older
{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e3a39119",
   "metadata": {},
   "source": [
    "### CREATE DAILY INPUT FOR THE CNN MODEL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "889e3048",
   "metadata": {},
   "outputs": [],
   "source": [
    "\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": 9,
   "id": "46ee1b5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#retrieve the list of stations in the metadata database.\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()\n",
    "\n",
    "# get the metadata\n",
    "query = f\"\"\"\n",
    "        SELECT \"id_station\" FROM \"hydrology\".\"metadata_disc\"\n",
    "        \"\"\"\n",
    "df = pd.read_sql_query(query, conn)\n",
    "\n",
    "# close the connection when finished\n",
    "cur.close()\n",
    "conn.close()\n",
    "LIST = list(df.id_station)\n",
    "tif_fileName =  'Z:\\ADO\\EU_DEM\\eudem_WGS84_adoext.tif'\n",
    "dem=xr.open_rasterio(tif_fileName)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "af4658ca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id_station</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ADO_DSC_ITH5_0001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ADO_DSC_ITH5_0002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ADO_DSC_ITH5_0003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ADO_DSC_ITH5_0004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ADO_DSC_ITH5_0005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ADO_DSC_ITH5_0006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ADO_DSC_ITH5_0007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ADO_DSC_ITH5_0008</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id_station\n",
       "0  ADO_DSC_ITH5_0001\n",
       "1  ADO_DSC_ITH5_0002\n",
       "2  ADO_DSC_ITH5_0003\n",
       "3  ADO_DSC_ITH5_0004\n",
       "4  ADO_DSC_ITH5_0005\n",
       "5  ADO_DSC_ITH5_0006\n",
       "6  ADO_DSC_ITH5_0007\n",
       "7  ADO_DSC_ITH5_0008"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "15c6bed4",
   "metadata": {},
   "outputs": [],
   "source": [
    "table=pd.DataFrame(data=None)\n",
    "    # Open the shape file\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()\n",
    "    \n",
    "    \n",
    "for STAT_CODE in LIST:\n",
    "    \n",
    "    # get the metadata\n",
    "    query = f\"\"\"\n",
    "            SELECT \"geom\" FROM \"hydrology\".\"metadata_disc\" WHERE \"id_station\" = '{STAT_CODE}'    \n",
    "            \"\"\"\n",
    "    df = pd.read_sql_query(query, conn)\n",
    "    \n",
    "    shp=GeoSeries(wkb.loads(df.geom[0], hex=True))\n",
    "    shp=shp.set_crs(\"EPSG:4326\")\n",
    "    \n",
    "    h_station=dem.sel(x=shp[0].x,y=shp[0].y,method='nearest').values[0]\n",
    "\n",
    "    update = {}\n",
    "    update['name'] = STAT_CODE\n",
    "    update['h']=h_station\n",
    "    \n",
    "    table = table.append(update,ignore_index=True)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e4eb9de0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>h</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ADO_DSC_ITH5_0001</td>\n",
       "      <td>21.585526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ADO_DSC_ITH5_0002</td>\n",
       "      <td>14.239743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ADO_DSC_ITH5_0003</td>\n",
       "      <td>29.443939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ADO_DSC_ITH5_0004</td>\n",
       "      <td>5.580721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ADO_DSC_ITH5_0005</td>\n",
       "      <td>42.813999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ADO_DSC_ITH5_0006</td>\n",
       "      <td>3.534000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ADO_DSC_ITH5_0007</td>\n",
       "      <td>6.214500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ADO_DSC_ITH5_0008</td>\n",
       "      <td>48.115002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                name          h\n",
       "0  ADO_DSC_ITH5_0001  21.585526\n",
       "1  ADO_DSC_ITH5_0002  14.239743\n",
       "2  ADO_DSC_ITH5_0003  29.443939\n",
       "3  ADO_DSC_ITH5_0004   5.580721\n",
       "4  ADO_DSC_ITH5_0005  42.813999\n",
       "5  ADO_DSC_ITH5_0006   3.534000\n",
       "6  ADO_DSC_ITH5_0007   6.214500\n",
       "7  ADO_DSC_ITH5_0008  48.115002"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "4f6edb55",
   "metadata": {},
   "outputs": [],
   "source": [
    "table.to_csv('STATIONS_HEIGHT_emilia.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5430006c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}