2022-07-14 DB querying examples.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4fff91a0",
   "metadata": {},
   "source": [
    "## Querying weather data from the PostGreSQL DB\n",
    "examples pertaining to the monthwise-aggregated nature of the DB data\n",
    "\n",
    "DB schema:\n",
    "```\n",
    "CREATE TABLE imd_data(\n",
    "\tsr SERIAL PRIMARY KEY,\n",
    "\tyear1 INT NULL,\n",
    "\tmonth1 INT NULL,\n",
    "\tdata JSONB DEFAULT '{}' NOT NULL,\n",
    "\tgeometry GEOMETRY(POINT,4326) NOT NULL\n",
    ");\n",
    "\n",
    "CREATE TABLE grid(\n",
    "\tsr SERIAL PRIMARY KEY,\n",
    "\tgeometry GEOMETRY(POINT,4326) NOT NULL\n",
    ");\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ad36a739",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "# import geopandas as gpd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "59f028b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "creds = json.load(open('dbcreds.json','r'))\n",
    "engine = create_engine(f\"postgresql://{creds['DB_USER']}:{creds['DB_PW']}@{creds['DB_SERVER']}:{creds['DB_PORT']}/{creds['DB_DBNAME']}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cdbdcf18",
   "metadata": {},
   "source": [
    "### Use case 1: Get following metrics for one location for a 10 year period  : \n",
    "- cumulative monthly rainfall\n",
    "- monthly average max and min temperatures \n",
    "- number of days with no rain\n",
    "- number of days with max temp above 30\n",
    "- number of dats with min temp below 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b190ca8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "lat, lon = 28.5, 72.5\n",
    "start_year = 1961\n",
    "end_year = 1970\n",
    "highT = 40\n",
    "lowT = 15"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "03e9696e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "select year1, month1, data from imd_data\n",
      "where year1 between 1961 and 1970\n",
      "and ST_Y(geometry)=28.5\n",
      "and ST_X(geometry)=72.5\n",
      "order by year1, month1\n",
      "\n"
     ]
    }
   ],
   "source": [
    "s1 = f\"\"\"select year1, month1, data from imd_data\n",
    "where year1 between {start_year} and {end_year}\n",
    "and ST_Y(geometry)={lat}\n",
    "and ST_X(geometry)={lon}\n",
    "order by year1, month1\n",
    "\"\"\"\n",
    "print(s1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7e43f006",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_sql(s1, con=engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "e2c70387",
   "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>year1</th>\n",
       "      <th>month1</th>\n",
       "      <th>data</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1961</td>\n",
       "      <td>1</td>\n",
       "      <td>{'1961-01-01': {'rain': 0.0, 'tmax': 21.510000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1961</td>\n",
       "      <td>2</td>\n",
       "      <td>{'1961-02-01': {'rain': 11.967379570007324, 't...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1961</td>\n",
       "      <td>3</td>\n",
       "      <td>{'1961-03-01': {'rain': 0.0, 'tmax': 32.369998...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1961</td>\n",
       "      <td>4</td>\n",
       "      <td>{'1961-04-01': {'rain': 0.0, 'tmax': 35.520000...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1961</td>\n",
       "      <td>5</td>\n",
       "      <td>{'1961-05-01': {'rain': 0.0, 'tmax': 41.630001...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>1970</td>\n",
       "      <td>8</td>\n",
       "      <td>{'1970-08-01': {'rain': 0.0, 'tmax': 37.400001...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>1970</td>\n",
       "      <td>9</td>\n",
       "      <td>{'1970-09-01': {'rain': 0.0, 'tmax': 33.759998...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>1970</td>\n",
       "      <td>10</td>\n",
       "      <td>{'1970-10-01': {'rain': 0.0, 'tmax': 34.419998...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>1970</td>\n",
       "      <td>11</td>\n",
       "      <td>{'1970-11-01': {'rain': 0.0, 'tmax': 32.479999...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>1970</td>\n",
       "      <td>12</td>\n",
       "      <td>{'1970-12-01': {'rain': 0.0, 'tmax': 27.809999...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>120 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     year1  month1                                               data\n",
       "0     1961       1  {'1961-01-01': {'rain': 0.0, 'tmax': 21.510000...\n",
       "1     1961       2  {'1961-02-01': {'rain': 11.967379570007324, 't...\n",
       "2     1961       3  {'1961-03-01': {'rain': 0.0, 'tmax': 32.369998...\n",
       "3     1961       4  {'1961-04-01': {'rain': 0.0, 'tmax': 35.520000...\n",
       "4     1961       5  {'1961-05-01': {'rain': 0.0, 'tmax': 41.630001...\n",
       "..     ...     ...                                                ...\n",
       "115   1970       8  {'1970-08-01': {'rain': 0.0, 'tmax': 37.400001...\n",
       "116   1970       9  {'1970-09-01': {'rain': 0.0, 'tmax': 33.759998...\n",
       "117   1970      10  {'1970-10-01': {'rain': 0.0, 'tmax': 34.419998...\n",
       "118   1970      11  {'1970-11-01': {'rain': 0.0, 'tmax': 32.479999...\n",
       "119   1970      12  {'1970-12-01': {'rain': 0.0, 'tmax': 27.809999...\n",
       "\n",
       "[120 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "65d72338",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'1961-01-01': {'rain': 0.0, 'tmax': 21.510000228881836, 'tmin': 7.5},\n",
       " '1961-01-02': {'rain': 0.0,\n",
       "  'tmax': 19.479999542236328,\n",
       "  'tmin': 6.190000057220459},\n",
       " '1961-01-03': {'rain': 5.3971171379089355,\n",
       "  'tmax': 18.969999313354492,\n",
       "  'tmin': 6.010000228881836},\n",
       " '1961-01-04': {'rain': 0.0,\n",
       "  'tmax': 16.510000228881836,\n",
       "  'tmin': 4.260000228881836},\n",
       " '1961-01-05': {'rain': 0.0,\n",
       "  'tmax': 18.670000076293945,\n",
       "  'tmin': 4.130000114440918},\n",
       " '1961-01-06': {'rain': 0.0,\n",
       "  'tmax': 20.579999923706055,\n",
       "  'tmin': 4.010000228881836},\n",
       " '1961-01-07': {'rain': 0.0,\n",
       "  'tmax': 21.1299991607666,\n",
       "  'tmin': 5.119999885559082},\n",
       " '1961-01-08': {'rain': 0.0,\n",
       "  'tmax': 21.90999984741211,\n",
       "  'tmin': 6.150000095367432},\n",
       " '1961-01-09': {'rain': 0.0,\n",
       "  'tmax': 21.200000762939453,\n",
       "  'tmin': 5.579999923706055},\n",
       " '1961-01-10': {'rain': 0.0,\n",
       "  'tmax': 21.709999084472656,\n",
       "  'tmin': 5.369999885559082},\n",
       " '1961-01-11': {'rain': 0.0,\n",
       "  'tmax': 20.65999984741211,\n",
       "  'tmin': 3.490000009536743},\n",
       " '1961-01-12': {'rain': 0.0, 'tmax': 18.3700008392334, 'tmin': 1.75},\n",
       " '1961-01-13': {'rain': 0.0,\n",
       "  'tmax': 18.90999984741211,\n",
       "  'tmin': 2.8299999237060547},\n",
       " '1961-01-14': {'rain': 0.0,\n",
       "  'tmax': 20.420000076293945,\n",
       "  'tmin': 3.990000009536743},\n",
       " '1961-01-15': {'rain': 0.0,\n",
       "  'tmax': 23.510000228881836,\n",
       "  'tmin': 5.519999980926514},\n",
       " '1961-01-16': {'rain': 0.0,\n",
       "  'tmax': 25.020000457763672,\n",
       "  'tmin': 6.949999809265137},\n",
       " '1961-01-17': {'rain': 0.0,\n",
       "  'tmax': 26.760000228881836,\n",
       "  'tmin': 8.949999809265137},\n",
       " '1961-01-18': {'rain': 0.2905430495738983,\n",
       "  'tmax': 25.860000610351562,\n",
       "  'tmin': 9.9399995803833},\n",
       " '1961-01-19': {'rain': 0.0,\n",
       "  'tmax': 25.93000030517578,\n",
       "  'tmin': 8.890000343322754},\n",
       " '1961-01-20': {'rain': 0.0,\n",
       "  'tmax': 25.799999237060547,\n",
       "  'tmin': 9.5600004196167},\n",
       " '1961-01-21': {'rain': 0.0,\n",
       "  'tmax': 26.68000030517578,\n",
       "  'tmin': 9.329999923706055},\n",
       " '1961-01-22': {'rain': 0.0,\n",
       "  'tmax': 26.700000762939453,\n",
       "  'tmin': 7.519999980926514},\n",
       " '1961-01-23': {'rain': 0.0,\n",
       "  'tmax': 26.309999465942383,\n",
       "  'tmin': 8.170000076293945},\n",
       " '1961-01-24': {'rain': 0.0,\n",
       "  'tmax': 26.780000686645508,\n",
       "  'tmin': 9.420000076293945},\n",
       " '1961-01-25': {'rain': 0.0,\n",
       "  'tmax': 27.56999969482422,\n",
       "  'tmin': 11.520000457763672},\n",
       " '1961-01-26': {'rain': 0.0,\n",
       "  'tmax': 26.280000686645508,\n",
       "  'tmin': 9.220000267028809},\n",
       " '1961-01-27': {'rain': 0.0,\n",
       "  'tmax': 25.389999389648438,\n",
       "  'tmin': 9.09000015258789},\n",
       " '1961-01-28': {'rain': 0.0, 'tmax': 24.0, 'tmin': 9.970000267028809},\n",
       " '1961-01-29': {'rain': 0.0,\n",
       "  'tmax': 23.639999389648438,\n",
       "  'tmin': 10.869999885559082},\n",
       " '1961-01-30': {'rain': 0.0,\n",
       "  'tmax': 21.6299991607666,\n",
       "  'tmin': 10.069999694824219},\n",
       " '1961-01-31': {'rain': 0.0,\n",
       "  'tmax': 20.350000381469727,\n",
       "  'tmin': 9.670000076293945}}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# preview one data cell:\n",
    "df1.at[0,'data']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e7e1f73",
   "metadata": {},
   "source": [
    "so its a dict with dates as keys"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6eb70d9c",
   "metadata": {},
   "source": [
    "#### since the data is already at 1 row = 1 month, we can directly process each row to get the metrics needed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "fdc64fb1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def process1(data):\n",
    "    # data is going to be the dict above\n",
    "    rainArr = []\n",
    "    tmaxArr = []\n",
    "    tminArr = []\n",
    "    \n",
    "    zeroRainCounter = 0\n",
    "    highTCounter = 0\n",
    "    lowTCounter = 0\n",
    "    \n",
    "    for date1 in data.keys():\n",
    "        row = data[date1]\n",
    "        if row.get('rain',False) is not False:\n",
    "            rainArr.append(row['rain'])\n",
    "            if row['rain'] == 0: zeroRainCounter += 1\n",
    "        \n",
    "        if row.get('tmax',False) is not False:\n",
    "            tmaxArr.append(row['tmax'])\n",
    "            if row['tmax'] >= highT: highTCounter += 1\n",
    "        \n",
    "        if row.get('tmin',False) is not False:\n",
    "            tminArr.append(row['tmin'])\n",
    "            if row['tmin'] <= lowT: lowTCounter += 1\n",
    "        # the if conditions are there so that only existing records get counted. and Zero should also get counted, not skipped\n",
    "    \n",
    "    cumulativeRain = round(sum(rainArr),2)\n",
    "    avgTmax = round(sum(tmaxArr)/len(tmaxArr),1)\n",
    "    avgTmin = round(sum(tminArr)/len(tminArr),1)\n",
    "    \n",
    "    return cumulativeRain, avgTmax, avgTmin, zeroRainCounter, highTCounter, lowTCounter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "715e110f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1[['cumulativeRain','avgTmax','avgTmin', 'days_noRain', f\"days_over{highT}\", f\"days_below{lowT}\"]] = df1['data'].apply(lambda x: pd.Series(process1(x)))\n",
    "\n",
    "# this method is used to derive muliple columns out of one column in dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "404b2b8a",
   "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>year1</th>\n",
       "      <th>month1</th>\n",
       "      <th>data</th>\n",
       "      <th>cumulativeRain</th>\n",
       "      <th>avgTmax</th>\n",
       "      <th>avgTmin</th>\n",
       "      <th>days_noRain</th>\n",
       "      <th>days_over40</th>\n",
       "      <th>days_below15</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1961</td>\n",
       "      <td>1</td>\n",
       "      <td>{'1961-01-01': {'rain': 0.0, 'tmax': 21.510000...</td>\n",
       "      <td>5.69</td>\n",
       "      <td>22.8</td>\n",
       "      <td>7.1</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1961</td>\n",
       "      <td>2</td>\n",
       "      <td>{'1961-02-01': {'rain': 11.967379570007324, 't...</td>\n",
       "      <td>12.87</td>\n",
       "      <td>22.8</td>\n",
       "      <td>8.4</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1961</td>\n",
       "      <td>3</td>\n",
       "      <td>{'1961-03-01': {'rain': 0.0, 'tmax': 32.369998...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>32.9</td>\n",
       "      <td>16.1</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1961</td>\n",
       "      <td>4</td>\n",
       "      <td>{'1961-04-01': {'rain': 0.0, 'tmax': 35.520000...</td>\n",
       "      <td>0.67</td>\n",
       "      <td>36.5</td>\n",
       "      <td>20.2</td>\n",
       "      <td>29.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1961</td>\n",
       "      <td>5</td>\n",
       "      <td>{'1961-05-01': {'rain': 0.0, 'tmax': 41.630001...</td>\n",
       "      <td>18.84</td>\n",
       "      <td>40.7</td>\n",
       "      <td>25.5</td>\n",
       "      <td>25.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>1970</td>\n",
       "      <td>8</td>\n",
       "      <td>{'1970-08-01': {'rain': 0.0, 'tmax': 37.400001...</td>\n",
       "      <td>66.12</td>\n",
       "      <td>34.6</td>\n",
       "      <td>25.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>1970</td>\n",
       "      <td>9</td>\n",
       "      <td>{'1970-09-01': {'rain': 0.0, 'tmax': 33.759998...</td>\n",
       "      <td>92.02</td>\n",
       "      <td>34.5</td>\n",
       "      <td>23.4</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>1970</td>\n",
       "      <td>10</td>\n",
       "      <td>{'1970-10-01': {'rain': 0.0, 'tmax': 34.419998...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>35.5</td>\n",
       "      <td>18.6</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>1970</td>\n",
       "      <td>11</td>\n",
       "      <td>{'1970-11-01': {'rain': 0.0, 'tmax': 32.479999...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>30.1</td>\n",
       "      <td>9.7</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>1970</td>\n",
       "      <td>12</td>\n",
       "      <td>{'1970-12-01': {'rain': 0.0, 'tmax': 27.809999...</td>\n",
       "      <td>0.00</td>\n",
       "      <td>25.9</td>\n",
       "      <td>5.9</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>120 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     year1  month1                                               data  \\\n",
       "0     1961       1  {'1961-01-01': {'rain': 0.0, 'tmax': 21.510000...   \n",
       "1     1961       2  {'1961-02-01': {'rain': 11.967379570007324, 't...   \n",
       "2     1961       3  {'1961-03-01': {'rain': 0.0, 'tmax': 32.369998...   \n",
       "3     1961       4  {'1961-04-01': {'rain': 0.0, 'tmax': 35.520000...   \n",
       "4     1961       5  {'1961-05-01': {'rain': 0.0, 'tmax': 41.630001...   \n",
       "..     ...     ...                                                ...   \n",
       "115   1970       8  {'1970-08-01': {'rain': 0.0, 'tmax': 37.400001...   \n",
       "116   1970       9  {'1970-09-01': {'rain': 0.0, 'tmax': 33.759998...   \n",
       "117   1970      10  {'1970-10-01': {'rain': 0.0, 'tmax': 34.419998...   \n",
       "118   1970      11  {'1970-11-01': {'rain': 0.0, 'tmax': 32.479999...   \n",
       "119   1970      12  {'1970-12-01': {'rain': 0.0, 'tmax': 27.809999...   \n",
       "\n",
       "     cumulativeRain  avgTmax  avgTmin  days_noRain  days_over40  days_below15  \n",
       "0              5.69     22.8      7.1         29.0          0.0          31.0  \n",
       "1             12.87     22.8      8.4         25.0          0.0          28.0  \n",
       "2              0.00     32.9     16.1         31.0          0.0          10.0  \n",
       "3              0.67     36.5     20.2         29.0          6.0           0.0  \n",
       "4             18.84     40.7     25.5         25.0         24.0           0.0  \n",
       "..              ...      ...      ...          ...          ...           ...  \n",
       "115           66.12     34.6     25.0         16.0          0.0           0.0  \n",
       "116           92.02     34.5     23.4         21.0          0.0           0.0  \n",
       "117            0.00     35.5     18.6         31.0          0.0           2.0  \n",
       "118            0.00     30.1      9.7         30.0          0.0          30.0  \n",
       "119            0.00     25.9      5.9         31.0          0.0          31.0  \n",
       "\n",
       "[120 rows x 9 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "66ec8bb6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# to do : visualizing this"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4447646c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "d35c8aa2",
   "metadata": {},
   "source": [
    "## Use case 2: Find the nearest grid points to a given location\n",
    "do proximity search query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8df14877",
   "metadata": {},
   "outputs": [],
   "source": [
    "lat1, lon1 = 18.4910, 73.8534\n",
    "radiusKm = 35"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "18d44652",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "select sr, ST_Y(geometry) as lon, ST_X(geometry) as lat,\n",
      "ST_Distance(geometry::geography, ST_GeogFromText('SRID=4326;POINT(73.8534 18.491)'))/1000 as distance\n",
      "from grid\n",
      "where ST_DWithin(\n",
      "    geometry::geography, \n",
      "    ST_GeogFromText('SRID=4326;POINT(73.8534 18.491)'), \n",
      "    35000 )\n",
      "order by ST_Distance(geometry, ST_GeomFromText('POINT(73.8534 18.491)',4326));\n",
      "\n"
     ]
    }
   ],
   "source": [
    "s2 = f\"\"\"select sr, ST_Y(geometry) as lon, ST_X(geometry) as lat,\n",
    "ST_Distance(geometry::geography, ST_GeogFromText('SRID=4326;POINT({lon1} {lat1})'))/1000 as distance\n",
    "from grid\n",
    "where ST_DWithin(\n",
    "    geometry::geography, \n",
    "    ST_GeogFromText('SRID=4326;POINT({lon1} {lat1})'), \n",
    "    {radiusKm*1000} )\n",
    "order by ST_Distance(geometry, ST_GeomFromText('POINT({lon1} {lat1})',4326));\n",
    "\"\"\"\n",
    "print(s2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f4c142a8",
   "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>sr</th>\n",
       "      <th>lon</th>\n",
       "      <th>lat</th>\n",
       "      <th>distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4324</td>\n",
       "      <td>18.50</td>\n",
       "      <td>73.75</td>\n",
       "      <td>10.964929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4293</td>\n",
       "      <td>18.50</td>\n",
       "      <td>74.00</td>\n",
       "      <td>15.513747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2185</td>\n",
       "      <td>18.25</td>\n",
       "      <td>73.75</td>\n",
       "      <td>28.826468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>898</td>\n",
       "      <td>18.75</td>\n",
       "      <td>73.75</td>\n",
       "      <td>30.674459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>129</td>\n",
       "      <td>18.25</td>\n",
       "      <td>74.00</td>\n",
       "      <td>30.847787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3251</td>\n",
       "      <td>18.75</td>\n",
       "      <td>74.00</td>\n",
       "      <td>32.575967</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     sr    lon    lat   distance\n",
       "0  4324  18.50  73.75  10.964929\n",
       "1  4293  18.50  74.00  15.513747\n",
       "2  2185  18.25  73.75  28.826468\n",
       "3   898  18.75  73.75  30.674459\n",
       "4   129  18.25  74.00  30.847787\n",
       "5  3251  18.75  74.00  32.575967"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_sql(s2, con=engine)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e18ab2cc",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}