netezza-spatial-analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: CLASSPATH=nzjdbc3.jar\n"
     ]
    }
   ],
   "source": [
    "%env CLASSPATH = nzjdbc3.jar\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "from nzpyida import IdaDataBase, IdaDataFrame\n",
    "from nzpyida.ae import NZFunGroupedApply,NZFunTApply\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Connecting to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "nzpy_dsn ={\n",
    "        \"database\":\"housing\",\n",
    "         \"port\" :5480,\n",
    "        \"host\" : \"x.xx.xxx.xxx\",\n",
    "        \"securityLevel\":3,\n",
    "        \"logLevel\":0\n",
    "       \n",
    "        }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "idadb = IdaDataBase(nzpy_dsn, uid=\"admin\", pwd=\"password\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "properties_idadf =  IdaDataFrame(idadb,'properties')\n",
    "cleaned_properties_idadf = IdaDataFrame(idadb,'cleaned_properties')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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</th>\n",
       "      <th>REGION</th>\n",
       "      <th>TYPE</th>\n",
       "      <th>SQFEET</th>\n",
       "      <th>BEDS</th>\n",
       "      <th>BATHS</th>\n",
       "      <th>CATS_ALLOWED</th>\n",
       "      <th>DOGS_ALLOWED</th>\n",
       "      <th>SMOKING_ALLOWED</th>\n",
       "      <th>WHEELCHAIR_ACCESS</th>\n",
       "      <th>ELECTRIC_VEHICLE_CHARGE</th>\n",
       "      <th>COMES_FURNISHED</th>\n",
       "      <th>LAUNDRY_OPTIONS</th>\n",
       "      <th>PARKING_OPTIONS</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "      <th>PRICE_PREDICTED</th>\n",
       "      <th>accuracy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7004529237</td>\n",
       "      <td>florence / muscle shoals</td>\n",
       "      <td>house</td>\n",
       "      <td>815.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d in unit</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>34.757801</td>\n",
       "      <td>-87.697098</td>\n",
       "      <td>606.817973</td>\n",
       "      <td>-0.527060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7004588837</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>house</td>\n",
       "      <td>780.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d in unit</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>30.428600</td>\n",
       "      <td>-84.259300</td>\n",
       "      <td>718.665190</td>\n",
       "      <td>0.738930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7004638292</td>\n",
       "      <td>flagstaff / sedona</td>\n",
       "      <td>apartment</td>\n",
       "      <td>814.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d in unit</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>35.212299</td>\n",
       "      <td>-111.578003</td>\n",
       "      <td>1337.036042</td>\n",
       "      <td>0.281961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7004645442</td>\n",
       "      <td>columbus</td>\n",
       "      <td>house</td>\n",
       "      <td>700.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d in unit</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>32.561600</td>\n",
       "      <td>-84.855904</td>\n",
       "      <td>622.568214</td>\n",
       "      <td>0.701028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7004692207</td>\n",
       "      <td>athens</td>\n",
       "      <td>apartment</td>\n",
       "      <td>1232.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d hookups</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>33.934502</td>\n",
       "      <td>-83.461601</td>\n",
       "      <td>1326.713559</td>\n",
       "      <td>0.656277</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           ID                    REGION       TYPE  SQFEET  BEDS  BATHS  \\\n",
       "0  7004529237  florence / muscle shoals      house   815.0   2.0    2.0   \n",
       "1  7004588837               tallahassee      house   780.0   2.0    1.0   \n",
       "2  7004638292        flagstaff / sedona  apartment   814.0   2.0    1.5   \n",
       "3  7004645442                  columbus      house   700.0   3.0    3.0   \n",
       "4  7004692207                    athens  apartment  1232.0   2.0    2.0   \n",
       "\n",
       "   CATS_ALLOWED  DOGS_ALLOWED  SMOKING_ALLOWED  WHEELCHAIR_ACCESS  \\\n",
       "0             1             1                1                  0   \n",
       "1             1             1                1                  0   \n",
       "2             1             1                1                  0   \n",
       "3             1             1                1                  0   \n",
       "4             1             1                0                  0   \n",
       "\n",
       "   ELECTRIC_VEHICLE_CHARGE  COMES_FURNISHED LAUNDRY_OPTIONS  \\\n",
       "0                        0                0     w/d in unit   \n",
       "1                        0                0     w/d in unit   \n",
       "2                        0                0     w/d in unit   \n",
       "3                        0                0     w/d in unit   \n",
       "4                        0                0     w/d hookups   \n",
       "\n",
       "      PARKING_OPTIONS        lat         lon  PRICE_PREDICTED  accuracy  \n",
       "0  off-street parking  34.757801  -87.697098       606.817973 -0.527060  \n",
       "1  off-street parking  30.428600  -84.259300       718.665190  0.738930  \n",
       "2  off-street parking  35.212299 -111.578003      1337.036042  0.281961  \n",
       "3  off-street parking  32.561600  -84.855904       622.568214  0.701028  \n",
       "4  off-street parking  33.934502  -83.461601      1326.713559  0.656277  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "properties_idadf.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID                         265144\n",
       "REGION                     265144\n",
       "PRICE                      265144\n",
       "TYPE                       265144\n",
       "SQFEET                     265144\n",
       "BEDS                       265144\n",
       "BATHS                      265144\n",
       "CATS_ALLOWED               265144\n",
       "DOGS_ALLOWED               265144\n",
       "SMOKING_ALLOWED            265144\n",
       "WHEELCHAIR_ACCESS          265144\n",
       "ELECTRIC_VEHICLE_CHARGE    265144\n",
       "COMES_FURNISHED            265144\n",
       "LAUNDRY_OPTIONS            265144\n",
       "PARKING_OPTIONS            265144\n",
       "LAT                        265144\n",
       "LON                        265144\n",
       "POINT                      265144\n",
       "DISTANCE_TO_SCHOOLS        167100\n",
       "STATE_PARKS_COUNT          265144\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "properties_idadf.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Geo-spatial use-cases:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Finding nearest properties for a selected property of interest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_houses_in_the_area(record_id,buffer_miles):\n",
    "    try: \n",
    "\n",
    "        #User selects a record  \n",
    "\n",
    "        record_selected_by_user = idadb.ida_query(f'''SELECT inza..ST_AsText(POINT) as POINT,REGION,PRICE from cleaned_properties where id = {record_id}''') \n",
    "\n",
    "        \n",
    "        \n",
    "\n",
    "        #Getting the buffer region around the specified point  \n",
    "\n",
    "        buffer_region = idadb.ida_query(f'''SELECT inza..ST_AsText(inza..ST_Buffer(inza..ST_WKTToSQL('{record_selected_by_user['POINT'][0]}'),{buffer_miles},8,'mile'))''')  \n",
    "\n",
    "        \n",
    "\n",
    "        #Properties within the buffer region \n",
    "\n",
    "        properties_within_distance = idadb.ida_query(f'''SELECT price from cleaned_properties where inza..ST_Contains( inza..ST_WKTToSQL('{buffer_region[0]}'),inza..ST_WKTToSQL(inza..ST_AsText(cleaned_properties.point)));''')  \n",
    "\n",
    "        \n",
    "\n",
    "        summary_list = [max(properties_within_distance),min(properties_within_distance),np.mean(properties_within_distance)] \n",
    "\n",
    "        \n",
    "\n",
    "        properties_list = idadb.ida_query(f'''SELECT * from \t\tcleaned_properties where \n",
    "\n",
    "        region='{record_selected_by_user['REGION'][0]}'  \n",
    "\n",
    "        and PRICE BETWEEN {min(properties_within_distance)} and { \n",
    "\n",
    "        max(properties_within_distance)} ''') \n",
    "\n",
    "        \n",
    "\n",
    "        return summary_list,properties_list \n",
    "\n",
    "    except: \n",
    "\n",
    "        return 'Statistics for the region could not be found' "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "summary_list , properties_list = get_houses_in_the_area(7004588837,10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Statistics:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Maximum price : 3495.0$\n",
      "Minimum price : 300.0$\n",
      "Average price : 952.1256627783669$\n"
     ]
    }
   ],
   "source": [
    "if summary_list[0] != None or summary_list[1] !=None or summary_list[2] != None:\n",
    "    print(f\"Maximum price : {summary_list[0]}$\")\n",
    "    print(f\"Minimum price : {summary_list[1]}$\")\n",
    "    print(f\"Average price : {(summary_list[2])}$\")\n",
    "else:\n",
    "    print('Summary information could not be found..')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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</th>\n",
       "      <th>REGION</th>\n",
       "      <th>PRICE</th>\n",
       "      <th>TYPE</th>\n",
       "      <th>SQFEET</th>\n",
       "      <th>BEDS</th>\n",
       "      <th>BATHS</th>\n",
       "      <th>CATS_ALLOWED</th>\n",
       "      <th>DOGS_ALLOWED</th>\n",
       "      <th>SMOKING_ALLOWED</th>\n",
       "      <th>WHEELCHAIR_ACCESS</th>\n",
       "      <th>ELECTRIC_VEHICLE_CHARGE</th>\n",
       "      <th>COMES_FURNISHED</th>\n",
       "      <th>LAUNDRY_OPTIONS</th>\n",
       "      <th>PARKING_OPTIONS</th>\n",
       "      <th>LAT</th>\n",
       "      <th>LON</th>\n",
       "      <th>POINT</th>\n",
       "      <th>DISTANCE_TO_SCHOOLS</th>\n",
       "      <th>STATE_PARKS_COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7046229774</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>799.0</td>\n",
       "      <td>apartment</td>\n",
       "      <td>954.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d hookups</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>30.4195</td>\n",
       "      <td>-84.234901</td>\n",
       "      <td>\u0001\u0001\u0000\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000`dk&gt;@\u0000\u0000\u0000`dk&gt;@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...</td>\n",
       "      <td>0.679534</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7046229367</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>799.0</td>\n",
       "      <td>apartment</td>\n",
       "      <td>954.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d hookups</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>30.4195</td>\n",
       "      <td>-84.234901</td>\n",
       "      <td>\u0001\u0001\u0000\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000`dk&gt;@\u0000\u0000\u0000`dk&gt;@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...</td>\n",
       "      <td>0.679534</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7023939868</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>799.0</td>\n",
       "      <td>apartment</td>\n",
       "      <td>954.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d hookups</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>30.4195</td>\n",
       "      <td>-84.234901</td>\n",
       "      <td>\u0001\u0001\u0000\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000`dk&gt;@\u0000\u0000\u0000`dk&gt;@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...</td>\n",
       "      <td>0.679534</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7023936944</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>799.0</td>\n",
       "      <td>apartment</td>\n",
       "      <td>954.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d hookups</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>30.4195</td>\n",
       "      <td>-84.234901</td>\n",
       "      <td>\u0001\u0001\u0000\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000`dk&gt;@\u0000\u0000\u0000`dk&gt;@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...</td>\n",
       "      <td>0.679534</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7023628504</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>799.0</td>\n",
       "      <td>apartment</td>\n",
       "      <td>954.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d hookups</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>30.4195</td>\n",
       "      <td>-84.234901</td>\n",
       "      <td>\u0001\u0001\u0000\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000`dk&gt;@\u0000\u0000\u0000`dk&gt;@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...</td>\n",
       "      <td>0.679534</td>\n",
       "      <td>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",
       "      <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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1973</th>\n",
       "      <td>7009345317</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>799.0</td>\n",
       "      <td>apartment</td>\n",
       "      <td>954.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d hookups</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>30.4195</td>\n",
       "      <td>-84.234901</td>\n",
       "      <td>\u0001\u0001\u0000\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000`dk&gt;@\u0000\u0000\u0000`dk&gt;@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...</td>\n",
       "      <td>0.679534</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1974</th>\n",
       "      <td>7009342601</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>799.0</td>\n",
       "      <td>apartment</td>\n",
       "      <td>954.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d hookups</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>30.4195</td>\n",
       "      <td>-84.234901</td>\n",
       "      <td>\u0001\u0001\u0000\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000`dk&gt;@\u0000\u0000\u0000`dk&gt;@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...</td>\n",
       "      <td>0.679534</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1975</th>\n",
       "      <td>7006011244</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>795.0</td>\n",
       "      <td>duplex</td>\n",
       "      <td>850.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d in unit</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>30.4188</td>\n",
       "      <td>-84.245102</td>\n",
       "      <td>\u0001\u0001\u0000\u0000\u0000\u0000À¯\u000fUÀ\u0000\u0000\u0000À¯\u000fUÀ\u0000\u0000\u00006k&gt;@\u0000\u0000\u00006k&gt;@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...</td>\n",
       "      <td>0.844399</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1976</th>\n",
       "      <td>7023613842</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>879.0</td>\n",
       "      <td>apartment</td>\n",
       "      <td>962.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d in unit</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>30.4874</td>\n",
       "      <td>-84.318901</td>\n",
       "      <td>\u0001\u0001\u0000\u0000\u0000\u0000àh\u0014UÀ\u0000\u0000\u0000àh\u0014UÀ\u0000\u0000\u0000@Æ|&gt;@\u0000\u0000\u0000@Æ|&gt;@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...</td>\n",
       "      <td>0.427583</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1977</th>\n",
       "      <td>7010892044</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>959.0</td>\n",
       "      <td>apartment</td>\n",
       "      <td>1172.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>w/d in unit</td>\n",
       "      <td>off-street parking</td>\n",
       "      <td>30.4874</td>\n",
       "      <td>-84.318901</td>\n",
       "      <td>\u0001\u0001\u0000\u0000\u0000\u0000àh\u0014UÀ\u0000\u0000\u0000àh\u0014UÀ\u0000\u0000\u0000@Æ|&gt;@\u0000\u0000\u0000@Æ|&gt;@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...</td>\n",
       "      <td>0.427583</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1978 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              ID       REGION  PRICE       TYPE  SQFEET  BEDS  BATHS  \\\n",
       "0     7046229774  tallahassee  799.0  apartment   954.0   2.0    2.0   \n",
       "1     7046229367  tallahassee  799.0  apartment   954.0   2.0    2.0   \n",
       "2     7023939868  tallahassee  799.0  apartment   954.0   2.0    2.0   \n",
       "3     7023936944  tallahassee  799.0  apartment   954.0   2.0    2.0   \n",
       "4     7023628504  tallahassee  799.0  apartment   954.0   2.0    2.0   \n",
       "...          ...          ...    ...        ...     ...   ...    ...   \n",
       "1973  7009345317  tallahassee  799.0  apartment   954.0   2.0    2.0   \n",
       "1974  7009342601  tallahassee  799.0  apartment   954.0   2.0    2.0   \n",
       "1975  7006011244  tallahassee  795.0     duplex   850.0   2.0    2.0   \n",
       "1976  7023613842  tallahassee  879.0  apartment   962.0   1.0    1.0   \n",
       "1977  7010892044  tallahassee  959.0  apartment  1172.0   2.0    2.0   \n",
       "\n",
       "      CATS_ALLOWED  DOGS_ALLOWED  SMOKING_ALLOWED  WHEELCHAIR_ACCESS  \\\n",
       "0                1             1                1                  0   \n",
       "1                1             1                1                  0   \n",
       "2                1             1                1                  0   \n",
       "3                1             1                1                  0   \n",
       "4                1             1                1                  0   \n",
       "...            ...           ...              ...                ...   \n",
       "1973             1             1                1                  0   \n",
       "1974             1             1                1                  0   \n",
       "1975             0             0                0                  0   \n",
       "1976             0             0                1                  0   \n",
       "1977             0             0                1                  0   \n",
       "\n",
       "      ELECTRIC_VEHICLE_CHARGE  COMES_FURNISHED LAUNDRY_OPTIONS  \\\n",
       "0                           0                0     w/d hookups   \n",
       "1                           0                0     w/d hookups   \n",
       "2                           0                0     w/d hookups   \n",
       "3                           0                0     w/d hookups   \n",
       "4                           0                0     w/d hookups   \n",
       "...                       ...              ...             ...   \n",
       "1973                        0                0     w/d hookups   \n",
       "1974                        0                0     w/d hookups   \n",
       "1975                        0                0     w/d in unit   \n",
       "1976                        0                0     w/d in unit   \n",
       "1977                        0                0     w/d in unit   \n",
       "\n",
       "         PARKING_OPTIONS      LAT        LON  \\\n",
       "0     off-street parking  30.4195 -84.234901   \n",
       "1     off-street parking  30.4195 -84.234901   \n",
       "2     off-street parking  30.4195 -84.234901   \n",
       "3     off-street parking  30.4195 -84.234901   \n",
       "4     off-street parking  30.4195 -84.234901   \n",
       "...                  ...      ...        ...   \n",
       "1973  off-street parking  30.4195 -84.234901   \n",
       "1974  off-street parking  30.4195 -84.234901   \n",
       "1975  off-street parking  30.4188 -84.245102   \n",
       "1976  off-street parking  30.4874 -84.318901   \n",
       "1977  off-street parking  30.4874 -84.318901   \n",
       "\n",
       "                                                  POINT  DISTANCE_TO_SCHOOLS  \\\n",
       "0     \u0001\u0001\u0000\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000`dk>@\u0000\u0000\u0000`dk>@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...             0.679534   \n",
       "1     \u0001\u0001\u0000\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000`dk>@\u0000\u0000\u0000`dk>@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...             0.679534   \n",
       "2     \u0001\u0001\u0000\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000`dk>@\u0000\u0000\u0000`dk>@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...             0.679534   \n",
       "3     \u0001\u0001\u0000\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000`dk>@\u0000\u0000\u0000`dk>@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...             0.679534   \n",
       "4     \u0001\u0001\u0000\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000`dk>@\u0000\u0000\u0000`dk>@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...             0.679534   \n",
       "...                                                 ...                  ...   \n",
       "1973  \u0001\u0001\u0000\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000`dk>@\u0000\u0000\u0000`dk>@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...             0.679534   \n",
       "1974  \u0001\u0001\u0000\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000 \b\u000fUÀ\u0000\u0000\u0000`dk>@\u0000\u0000\u0000`dk>@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...             0.679534   \n",
       "1975  \u0001\u0001\u0000\u0000\u0000\u0000À¯\u000fUÀ\u0000\u0000\u0000À¯\u000fUÀ\u0000\u0000\u00006k>@\u0000\u0000\u00006k>@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...             0.844399   \n",
       "1976  \u0001\u0001\u0000\u0000\u0000\u0000àh\u0014UÀ\u0000\u0000\u0000àh\u0014UÀ\u0000\u0000\u0000@Æ|>@\u0000\u0000\u0000@Æ|>@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...             0.427583   \n",
       "1977  \u0001\u0001\u0000\u0000\u0000\u0000àh\u0014UÀ\u0000\u0000\u0000àh\u0014UÀ\u0000\u0000\u0000@Æ|>@\u0000\u0000\u0000@Æ|>@æ\u0010\u0000\u0000\u0000\u0000\u0000\u0001\u0001\u0000...             0.427583   \n",
       "\n",
       "      STATE_PARKS_COUNT  \n",
       "0                     0  \n",
       "1                     0  \n",
       "2                     0  \n",
       "3                     0  \n",
       "4                     0  \n",
       "...                 ...  \n",
       "1973                  0  \n",
       "1974                  0  \n",
       "1975                  0  \n",
       "1976                  0  \n",
       "1977                  0  \n",
       "\n",
       "[1978 rows x 20 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "properties_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Finding the cheaper rental options closer to the selected property "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_discounted_houses(record_id): \n",
    "\n",
    "    try: \n",
    "\n",
    "#User selects a record  \n",
    "\n",
    "        record_selected_by_user = idadb.ida_query(f'''SELECT inza..ST_AsText(POINT) as POINT,REGION, \n",
    "\n",
    "        PRICE from cleaned_properties where id = {record_id}''') \n",
    "\n",
    "        \n",
    "        \n",
    "\n",
    "        #Get all the records where it is lower than the discounted price. \n",
    "\n",
    "        selected_house_price = record_selected_by_user['PRICE'][0] \n",
    "\n",
    "        discount = 10 \n",
    "\n",
    "        \n",
    "        \n",
    "\n",
    "        final_price = selected_house_price*(1-(discount/100)) \n",
    "\n",
    "        \n",
    "        \n",
    "\n",
    "        #Calculate distances between properties and display all the records matching the \t#condition \n",
    "\n",
    "        \n",
    "        \n",
    "\n",
    "        final_houses_with_discounted_prices = idadb.ida_query(f'''select temp.id,temp.price,temp.distance_between_properties,temp.region,\n",
    "        temp.beds,temp.baths,temp.sqfeet,temp.cats_allowed,temp.dogs_allowed,temp.smoking_allowed,temp.wheelchair_access,temp.electric_vehicle_charge,\n",
    "        temp.comes_furnished\n",
    "         from  ( select cleaned_properties.*,inza..ST_Distance(\n",
    "                            inza..ST_Transform(inza..ST_WKTToSQL(inza..ST_AsText(cleaned_properties.point)), 4326), \n",
    "                            inza..ST_Transform(inza..ST_WKTToSQL('{record_selected_by_user['POINT'][0]}'),4326), 'mile')as \n",
    "                            distance_between_properties from cleaned_properties) as temp \n",
    "                            where temp.price<={final_price} and temp.distance_between_properties<5 order by temp.distance_between_properties;''')\n",
    "\n",
    "        \n",
    "\n",
    "        return final_houses_with_discounted_prices \n",
    "\n",
    " \n",
    "\n",
    "    except: \n",
    "\n",
    "        return 'No houses found nearby at a discount' "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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</th>\n",
       "      <th>PRICE</th>\n",
       "      <th>DISTANCE_BETWEEN_PROPERTIES</th>\n",
       "      <th>REGION</th>\n",
       "      <th>BEDS</th>\n",
       "      <th>BATHS</th>\n",
       "      <th>SQFEET</th>\n",
       "      <th>CATS_ALLOWED</th>\n",
       "      <th>DOGS_ALLOWED</th>\n",
       "      <th>SMOKING_ALLOWED</th>\n",
       "      <th>WHEELCHAIR_ACCESS</th>\n",
       "      <th>ELECTRIC_VEHICLE_CHARGE</th>\n",
       "      <th>COMES_FURNISHED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7045946902</td>\n",
       "      <td>395.0</td>\n",
       "      <td>2.768783</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7045959216</td>\n",
       "      <td>415.0</td>\n",
       "      <td>2.768783</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1650.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7045950765</td>\n",
       "      <td>410.0</td>\n",
       "      <td>2.768783</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7045936341</td>\n",
       "      <td>450.0</td>\n",
       "      <td>3.589566</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1250.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7023838406</td>\n",
       "      <td>395.0</td>\n",
       "      <td>3.918576</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>929.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7045964077</td>\n",
       "      <td>415.0</td>\n",
       "      <td>3.918576</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2200.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7044742622</td>\n",
       "      <td>395.0</td>\n",
       "      <td>3.942118</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1650.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7041234093</td>\n",
       "      <td>375.0</td>\n",
       "      <td>3.944454</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1209.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7040004702</td>\n",
       "      <td>390.0</td>\n",
       "      <td>3.944454</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1516.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7039997105</td>\n",
       "      <td>415.0</td>\n",
       "      <td>3.944454</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1209.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>7040619897</td>\n",
       "      <td>395.0</td>\n",
       "      <td>3.944454</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1209.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>7040700955</td>\n",
       "      <td>390.0</td>\n",
       "      <td>3.944454</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1516.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>7043328236</td>\n",
       "      <td>390.0</td>\n",
       "      <td>3.944454</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1516.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>7040705576</td>\n",
       "      <td>415.0</td>\n",
       "      <td>4.155295</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1209.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>7036368389</td>\n",
       "      <td>350.0</td>\n",
       "      <td>4.155295</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1516.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>7013972258</td>\n",
       "      <td>415.0</td>\n",
       "      <td>4.155295</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1209.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>7013631284</td>\n",
       "      <td>340.0</td>\n",
       "      <td>4.155295</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1516.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>7036655019</td>\n",
       "      <td>400.0</td>\n",
       "      <td>4.155295</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1209.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>7024923870</td>\n",
       "      <td>360.0</td>\n",
       "      <td>4.155295</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1209.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>7044483645</td>\n",
       "      <td>375.0</td>\n",
       "      <td>4.155295</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1209.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>7040370135</td>\n",
       "      <td>395.0</td>\n",
       "      <td>4.155295</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1209.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>7020462242</td>\n",
       "      <td>360.0</td>\n",
       "      <td>4.155295</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1209.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>7032630676</td>\n",
       "      <td>325.0</td>\n",
       "      <td>4.348547</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>725.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>7032649347</td>\n",
       "      <td>325.0</td>\n",
       "      <td>4.348547</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>7032651073</td>\n",
       "      <td>325.0</td>\n",
       "      <td>4.348547</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>700.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>7041182133</td>\n",
       "      <td>300.0</td>\n",
       "      <td>4.674165</td>\n",
       "      <td>tallahassee</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1012.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID  PRICE  DISTANCE_BETWEEN_PROPERTIES       REGION  BEDS  BATHS  \\\n",
       "0   7045946902  395.0                     2.768783  tallahassee   3.0    0.0   \n",
       "1   7045959216  415.0                     2.768783  tallahassee   4.0    0.0   \n",
       "2   7045950765  410.0                     2.768783  tallahassee   4.0    0.0   \n",
       "3   7045936341  450.0                     3.589566  tallahassee   2.0    0.0   \n",
       "4   7023838406  395.0                     3.918576  tallahassee   3.0    2.0   \n",
       "5   7045964077  415.0                     3.918576  tallahassee   5.0    3.0   \n",
       "6   7044742622  395.0                     3.942118  tallahassee   3.0    2.5   \n",
       "7   7041234093  375.0                     3.944454  tallahassee   3.0    2.0   \n",
       "8   7040004702  390.0                     3.944454  tallahassee   4.0    2.0   \n",
       "9   7039997105  415.0                     3.944454  tallahassee   3.0    2.0   \n",
       "10  7040619897  395.0                     3.944454  tallahassee   3.0    2.0   \n",
       "11  7040700955  390.0                     3.944454  tallahassee   4.0    2.0   \n",
       "12  7043328236  390.0                     3.944454  tallahassee   4.0    2.0   \n",
       "13  7040705576  415.0                     4.155295  tallahassee   3.0    2.0   \n",
       "14  7036368389  350.0                     4.155295  tallahassee   4.0    2.0   \n",
       "15  7013972258  415.0                     4.155295  tallahassee   3.0    2.0   \n",
       "16  7013631284  340.0                     4.155295  tallahassee   4.0    2.0   \n",
       "17  7036655019  400.0                     4.155295  tallahassee   3.0    2.0   \n",
       "18  7024923870  360.0                     4.155295  tallahassee   3.0    2.0   \n",
       "19  7044483645  375.0                     4.155295  tallahassee   3.0    2.0   \n",
       "20  7040370135  395.0                     4.155295  tallahassee   3.0    2.0   \n",
       "21  7020462242  360.0                     4.155295  tallahassee   3.0    2.0   \n",
       "22  7032630676  325.0                     4.348547  tallahassee   1.0    1.0   \n",
       "23  7032649347  325.0                     4.348547  tallahassee   1.0    1.0   \n",
       "24  7032651073  325.0                     4.348547  tallahassee   1.0    1.0   \n",
       "25  7041182133  300.0                     4.674165  tallahassee   2.0    1.0   \n",
       "\n",
       "    SQFEET  CATS_ALLOWED  DOGS_ALLOWED  SMOKING_ALLOWED  WHEELCHAIR_ACCESS  \\\n",
       "0   1500.0             0             0                0                  0   \n",
       "1   1650.0             0             0                0                  0   \n",
       "2   1500.0             0             0                0                  0   \n",
       "3   1250.0             0             0                0                  0   \n",
       "4    929.0             1             1                1                  0   \n",
       "5   2200.0             0             0                0                  0   \n",
       "6   1650.0             0             0                0                  0   \n",
       "7   1209.0             1             1                1                  0   \n",
       "8   1516.0             1             1                1                  0   \n",
       "9   1209.0             1             1                1                  0   \n",
       "10  1209.0             1             1                1                  0   \n",
       "11  1516.0             1             1                1                  0   \n",
       "12  1516.0             1             1                1                  0   \n",
       "13  1209.0             1             1                1                  0   \n",
       "14  1516.0             1             1                1                  0   \n",
       "15  1209.0             1             1                1                  0   \n",
       "16  1516.0             1             1                1                  0   \n",
       "17  1209.0             1             1                1                  0   \n",
       "18  1209.0             1             1                1                  0   \n",
       "19  1209.0             1             1                1                  0   \n",
       "20  1209.0             1             1                1                  0   \n",
       "21  1209.0             1             1                1                  0   \n",
       "22   725.0             1             1                1                  0   \n",
       "23   700.0             1             1                1                  0   \n",
       "24   700.0             1             1                1                  0   \n",
       "25  1012.0             1             1                1                  0   \n",
       "\n",
       "    ELECTRIC_VEHICLE_CHARGE  COMES_FURNISHED  \n",
       "0                         0                0  \n",
       "1                         0                0  \n",
       "2                         0                0  \n",
       "3                         0                0  \n",
       "4                         0                1  \n",
       "5                         0                0  \n",
       "6                         0                0  \n",
       "7                         0                0  \n",
       "8                         0                0  \n",
       "9                         0                0  \n",
       "10                        0                0  \n",
       "11                        0                0  \n",
       "12                        0                0  \n",
       "13                        0                0  \n",
       "14                        0                0  \n",
       "15                        0                0  \n",
       "16                        0                0  \n",
       "17                        0                0  \n",
       "18                        0                0  \n",
       "19                        0                0  \n",
       "20                        0                0  \n",
       "21                        0                0  \n",
       "22                        0                0  \n",
       "23                        0                0  \n",
       "24                        0                0  \n",
       "25                        0                0  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "discounted_houses = get_discounted_houses(7004588837)\n",
    "discounted_houses"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Finding the green space around the selected property "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Updating the park count for each record\n",
    "idadb.ida_query(f'''update cleaned_properties set cleaned_properties.state_parks_count = parks_count.count from \n",
    "(select temp.id,count(temp.c) as count from (select cleaned_properties.id,inza..ST_Intersects( \n",
    "inza..ST_WKTToSQL(inza..ST_AsText( \n",
    "inza..ST_Buffer(cleaned_properties.point \n",
    ",100,8,'mile'))) \n",
    ", national_parks.the_geom) as c from cleaned_properties,national_parks where c='True') as temp group by temp.id) as  \n",
    "parks_count where parks_count.id=cleaned_properties.id;''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Get the national_parks_area for each record \n",
    "\n",
    "idadb.ida_query(f'''update green_area set green_area.national_parks_area = d.sum from(  \n",
    "\n",
    "select f.id,SUM(f.area) as sum from (select temp.id,inza..ST_Area(inza..ST_WKTToSQL(temp.c),'mile') as area  \n",
    "\n",
    "from (select cleaned_properties.id,inza..ST_AsText(inza..ST_Intersection(  \n",
    "\n",
    "inza..ST_WKTToSQL(inza..ST_AsText(  \n",
    "\n",
    "inza..ST_Buffer(cleaned_properties.point ,100,8,'mile')))  \n",
    "\n",
    ", national_parks.the_geom)) as c from cleaned_properties,national_parks ) as temp  \n",
    "\n",
    "where temp.c!='POINT EMPTY') as f group by f.id) as d where d.id = green_area.id;''') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_green_area(record_id):\n",
    "    try:\n",
    "        record_selected_by_user =  cleaned_properties_idadf.ida_query(f'''SELECT inza..ST_AsText(POINT) as POINT,REGION from cleaned_properties where id = {record_id} ''')\n",
    "        \n",
    "        #Getting the properties in the same region ordered by national_parks_area and park_count\n",
    "        ranked_properties = cleaned_properties_idadf.ida_query(f'''select green_area.national_parks_area,temp.state_parks_count,temp.ID,\n",
    "        temp.PRICE,temp.REGION,temp.BEDS,temp.BATHS,temp.SQFEET,temp.CATS_ALLOWED,temp.DOGS_ALLOWED, temp.SMOKING_ALLOWED,temp.WHEELCHAIR_ACCESS, \n",
    "        temp.ELECTRIC_VEHICLE_CHARGE,temp.COMES_FURNISHED,temp.LAT as \"lat\" , temp.LON as \"lon\"  from (SELECT * from cleaned_properties where \n",
    "                                        cleaned_properties.region ='{record_selected_by_user['REGION'][0]}') as temp inner join green_area\n",
    "                                        on green_area.id = temp.id \n",
    "                                        order by state_parks_count desc,national_parks_area desc;''')\n",
    "\n",
    "        green_information = [200,ranked_properties]\n",
    "\n",
    "        return green_information\n",
    "    except:\n",
    "        return [404,'Unknown error happened! Green information could not be found, please try again..']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>NATIONAL_PARKS_AREA</th>\n",
       "      <th>STATE_PARKS_COUNT</th>\n",
       "      <th>ID</th>\n",
       "      <th>PRICE</th>\n",
       "      <th>REGION</th>\n",
       "      <th>BEDS</th>\n",
       "      <th>BATHS</th>\n",
       "      <th>SQFEET</th>\n",
       "      <th>CATS_ALLOWED</th>\n",
       "      <th>DOGS_ALLOWED</th>\n",
       "      <th>SMOKING_ALLOWED</th>\n",
       "      <th>WHEELCHAIR_ACCESS</th>\n",
       "      <th>ELECTRIC_VEHICLE_CHARGE</th>\n",
       "      <th>COMES_FURNISHED</th>\n",
       "      <th>lat</th>\n",
       "      <th>lon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6272.849397</td>\n",
       "      <td>4</td>\n",
       "      <td>7043795019</td>\n",
       "      <td>975.0</td>\n",
       "      <td>reno / tahoe</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1819.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>36.068100</td>\n",
       "      <td>-115.079002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>435.330687</td>\n",
       "      <td>2</td>\n",
       "      <td>7048719596</td>\n",
       "      <td>988.0</td>\n",
       "      <td>reno / tahoe</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>34.231300</td>\n",
       "      <td>-119.045998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>435.330687</td>\n",
       "      <td>2</td>\n",
       "      <td>7044091106</td>\n",
       "      <td>988.0</td>\n",
       "      <td>reno / tahoe</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>34.231300</td>\n",
       "      <td>-119.045998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>435.330687</td>\n",
       "      <td>2</td>\n",
       "      <td>7044091322</td>\n",
       "      <td>988.0</td>\n",
       "      <td>reno / tahoe</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>34.231300</td>\n",
       "      <td>-119.045998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>435.330687</td>\n",
       "      <td>2</td>\n",
       "      <td>7048019861</td>\n",
       "      <td>988.0</td>\n",
       "      <td>reno / tahoe</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>34.231300</td>\n",
       "      <td>-119.045998</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",
       "      <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>2531</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>7041479328</td>\n",
       "      <td>1890.0</td>\n",
       "      <td>reno / tahoe</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.5</td>\n",
       "      <td>2413.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.590000</td>\n",
       "      <td>-119.150002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2532</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>7039350178</td>\n",
       "      <td>1100.0</td>\n",
       "      <td>reno / tahoe</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.648102</td>\n",
       "      <td>-119.292000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2533</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>7044292392</td>\n",
       "      <td>1100.0</td>\n",
       "      <td>reno / tahoe</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1425.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.648102</td>\n",
       "      <td>-119.292000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2534</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>7049031613</td>\n",
       "      <td>1075.0</td>\n",
       "      <td>reno / tahoe</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>790.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2535</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>7044292078</td>\n",
       "      <td>1100.0</td>\n",
       "      <td>reno / tahoe</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1425.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>39.648102</td>\n",
       "      <td>-119.292000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2536 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      NATIONAL_PARKS_AREA  STATE_PARKS_COUNT          ID   PRICE  \\\n",
       "0             6272.849397                  4  7043795019   975.0   \n",
       "1              435.330687                  2  7048719596   988.0   \n",
       "2              435.330687                  2  7044091106   988.0   \n",
       "3              435.330687                  2  7044091322   988.0   \n",
       "4              435.330687                  2  7048019861   988.0   \n",
       "...                   ...                ...         ...     ...   \n",
       "2531             0.000000                  0  7041479328  1890.0   \n",
       "2532             0.000000                  0  7039350178  1100.0   \n",
       "2533             0.000000                  0  7044292392  1100.0   \n",
       "2534             0.000000                  0  7049031613  1075.0   \n",
       "2535             0.000000                  0  7044292078  1100.0   \n",
       "\n",
       "            REGION  BEDS  BATHS  SQFEET  CATS_ALLOWED  DOGS_ALLOWED  \\\n",
       "0     reno / tahoe   3.0    2.5  1819.0             1             0   \n",
       "1     reno / tahoe   3.0    2.5  1400.0             1             1   \n",
       "2     reno / tahoe   3.0    2.5  1400.0             1             1   \n",
       "3     reno / tahoe   3.0    2.5  1400.0             1             1   \n",
       "4     reno / tahoe   3.0    2.5  1400.0             1             1   \n",
       "...            ...   ...    ...     ...           ...           ...   \n",
       "2531  reno / tahoe   3.0    2.5  2413.0             0             1   \n",
       "2532  reno / tahoe   2.0    2.0  1400.0             1             1   \n",
       "2533  reno / tahoe   2.0    2.0  1425.0             1             1   \n",
       "2534  reno / tahoe   1.0    1.0   790.0             1             0   \n",
       "2535  reno / tahoe   2.0    2.0  1425.0             1             1   \n",
       "\n",
       "      SMOKING_ALLOWED  WHEELCHAIR_ACCESS  ELECTRIC_VEHICLE_CHARGE  \\\n",
       "0                   1                  0                        0   \n",
       "1                   1                  0                        0   \n",
       "2                   1                  0                        0   \n",
       "3                   1                  0                        0   \n",
       "4                   1                  0                        0   \n",
       "...               ...                ...                      ...   \n",
       "2531                1                  0                        0   \n",
       "2532                1                  0                        0   \n",
       "2533                1                  0                        0   \n",
       "2534                1                  0                        0   \n",
       "2535                1                  0                        0   \n",
       "\n",
       "      COMES_FURNISHED        lat         lon  \n",
       "0                   0  36.068100 -115.079002  \n",
       "1                   0  34.231300 -119.045998  \n",
       "2                   0  34.231300 -119.045998  \n",
       "3                   0  34.231300 -119.045998  \n",
       "4                   0  34.231300 -119.045998  \n",
       "...               ...        ...         ...  \n",
       "2531                0  39.590000 -119.150002  \n",
       "2532                0  39.648102 -119.292000  \n",
       "2533                0  39.648102 -119.292000  \n",
       "2534                0   0.000000    0.000000  \n",
       "2535                0  39.648102 -119.292000  \n",
       "\n",
       "[2536 rows x 16 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "green_information = get_green_area(7043564878)\n",
    "green_information[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Predicting the house prices using the geospatial features  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Adding distance_to_schools_column\n",
    "idadb.ida_query('''update cleaned_properties set cleaned_properties.distance_to_schools = temp.distance_to_schools  \n",
    "\n",
    "from (select cleaned_properties.id,MIN(inza..ST_DISTANCE( \n",
    "\n",
    "inza..ST_Transform(cleaned_properties.point, 4326),  \n",
    "\n",
    "inza..ST_Transform(Public_Schools.point,4326), \n",
    "\n",
    "'mile')) as distance_to_schools from Public_Schools,cleaned_properties  \n",
    "\n",
    "where cleaned_properties.REGION = LOWER(Public_Schools.CITY) \n",
    "\n",
    "GROUP BY cleaned_properties.id) as temp \n",
    "\n",
    "where temp.id = cleaned_properties.id; ''') "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### In-database function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "if idadb.exists_table(\"predictions\"):\n",
    "        idadb.drop_table(\"predictions\")\n",
    "\n",
    "\n",
    "code_house_prediction_model = \"\"\"def house_prediction_model(self,df):\n",
    "                import numpy as np\n",
    "                import pandas as pd\n",
    "                \n",
    "                import sklearn\n",
    "                import pickle\n",
    "                import subprocess\n",
    "                \n",
    "                from sklearn.model_selection import cross_val_score\n",
    "                from sklearn.model_selection import train_test_split\n",
    "                from sklearn.linear_model import LinearRegression\n",
    "                from sklearn.ensemble import GradientBoostingRegressor\n",
    "                # from xgboost import XGBRegressor\n",
    "                from sklearn.preprocessing import LabelEncoder\n",
    "                from sklearn.metrics import r2_score,mean_squared_error\n",
    "                \n",
    "            \n",
    "                \n",
    "                data = df.copy()\n",
    "                \n",
    "                \n",
    "\n",
    "                data['DISTANCE_TO_SCHOOLS'] = data['DISTANCE_TO_SCHOOLS'].replace(to_replace = 0 , value = 10000)\n",
    "                data['DISTANCE_TO_SCHOOLS'] = data['DISTANCE_TO_SCHOOLS']/data['DISTANCE_TO_SCHOOLS'].abs().max()\n",
    "            \n",
    "                encoder={}\n",
    "                categorical_columns_list = []\n",
    "                for i in data.columns:\n",
    "                    if data[i].dtype == 'object':\n",
    "                        categorical_columns_list.append(i)\n",
    "                        encoder[i] = LabelEncoder()\n",
    "                        encoder[i].fit(list(data[i].values))\n",
    "                        data[i] = encoder[i].transform(list(data[i].values))\n",
    "                \n",
    "                \n",
    "                #Splitting into training and testing dataset\n",
    "                \n",
    "                y = data['PRICE']\n",
    "                X = data.drop(columns=['PRICE'],axis=1)\n",
    "                \n",
    "                X_train, X_test, y_train, y_test = train_test_split(X,y, test_size = 0.20 , train_size = 0.80, random_state=100)\n",
    "                \n",
    "        \n",
    "                \n",
    "                #Gradient Boosting Regression\n",
    "                from sklearn.ensemble import GradientBoostingRegressor\n",
    "                \n",
    "                gbr = GradientBoostingRegressor()\n",
    "                gbr.fit(X_train, y_train)\n",
    "                y_pred_gbr= gbr.predict(X_test)\n",
    "                y_pred_gbr = np.around(y_pred_gbr,decimals=0)\n",
    "                r2_score = gbr.score(X_test,y_test)\n",
    "                \n",
    "                \n",
    "                from sklearn.model_selection import cross_val_score\n",
    "                cv_scores = np.sqrt(-cross_val_score(gbr, X, y,scoring='neg_mean_squared_error',cv=2))\n",
    "                final_score = (np.mean(cv_scores/y.max()))\n",
    "                \n",
    "                predictions = X_test.copy()\n",
    "                \n",
    "                for column in categorical_columns_list:   \n",
    "                    predictions[column] = encoder[column].inverse_transform(list(predictions[column].values))\n",
    "                \n",
    "                predictions['PRICE_PREDICTED'] = y_pred_gbr\n",
    "                predictions['ACCURACY'] = r2_score\n",
    "                def print_output(x):\n",
    "                    row = [x['ID'],x['REGION'],\n",
    "                   x['TYPE'],x['PRICE_PREDICTED'],\n",
    "                    x['SQFEET'],x['BEDS'],\n",
    "                    x['BATHS'],x['CATS_ALLOWED'],x['DOGS_ALLOWED'],x['SMOKING_ALLOWED'],\n",
    "                    x['WHEELCHAIR_ACCESS'],x['ELECTRIC_VEHICLE_CHARGE'],\n",
    "                    x['COMES_FURNISHED'],x['LAUNDRY_OPTIONS'],x['PARKING_OPTIONS'],x['DISTANCE_TO_SCHOOLS'],x['ACCURACY']]\n",
    "                    self.output(row)\n",
    "                predictions.apply(print_output, axis=1)\n",
    "                \n",
    "            \n",
    "                pickle.dump(gbr, open('/export/home/nz/git_repo/models-repo/model.pkl','wb'))\n",
    "                \n",
    "                    \n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_signature = {'ID':'int64','REGION':'str','TYPE':'str','PRICE_PREDICTED':'double',\n",
    "    'SQFEET':'double','BEDS':'float','BATHS':'float','CATS_ALLOWED':'int','DOGS_ALLOWED':'int',\n",
    "'SMOKING_ALLOWED':'int','WHEELCHAIR_ACCESS':'int','ELECTRIC_VEHICLE_CHARGE'\t: 'int','COMES_FURNISHED':'int',\n",
    "'LAUNDRY_OPTIONS':'str','PARKING_OPTIONS':'str','DISTANCE_TO_SCHOOLS':'double','ACCURACY':'double'\n",
    "    }\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               ID       REGION          TYPE  PRICE_PREDICTED  SQFEET  BEDS  \\\n",
      "0      7049993823  SF bay area     apartment           3001.0  1073.0   2.0   \n",
      "1      7050001407  SF bay area     apartment           3572.0  1650.0   2.0   \n",
      "2      7049954093  SF bay area     apartment           2000.0   640.0   1.0   \n",
      "3      7037717802  SF bay area     apartment           3213.0  1000.0   1.0   \n",
      "4      7049934118  SF bay area     apartment           2480.0   660.0   1.0   \n",
      "...           ...          ...           ...              ...     ...   ...   \n",
      "53141  7049040991         yuma     apartment            825.0   885.0   2.0   \n",
      "53142  7040443337         yuma  manufactured            790.0   980.0   2.0   \n",
      "53143  7028129768         yuma     apartment            848.0   885.0   2.0   \n",
      "53144  7037956523         yuma     apartment            904.0   845.0   2.0   \n",
      "53145  7035915874         yuma     apartment            791.0   675.0   2.0   \n",
      "\n",
      "       BATHS  CATS_ALLOWED  DOGS_ALLOWED  SMOKING_ALLOWED  WHEELCHAIR_ACCESS  \\\n",
      "0        2.0             1             1                1                  1   \n",
      "1        2.0             0             0                1                  0   \n",
      "2        1.0             0             0                0                  0   \n",
      "3        1.0             1             1                1                  0   \n",
      "4        1.0             1             1                0                  0   \n",
      "...      ...           ...           ...              ...                ...   \n",
      "53141    2.0             1             1                0                  0   \n",
      "53142    2.0             1             1                1                  0   \n",
      "53143    2.0             1             1                0                  0   \n",
      "53144    1.0             0             0                1                  0   \n",
      "53145    2.0             1             1                0                  0   \n",
      "\n",
      "       ELECTRIC_VEHICLE_CHARGE  COMES_FURNISHED  LAUNDRY_OPTIONS  \\\n",
      "0                            1                1      w/d in unit   \n",
      "1                            0                0      w/d in unit   \n",
      "2                            0                0  laundry on site   \n",
      "3                            0                0  laundry in bldg   \n",
      "4                            0                0      w/d in unit   \n",
      "...                        ...              ...              ...   \n",
      "53141                        0                0  laundry on site   \n",
      "53142                        0                1      w/d in unit   \n",
      "53143                        0                0  laundry on site   \n",
      "53144                        0                0  laundry on site   \n",
      "53145                        0                0  laundry on site   \n",
      "\n",
      "       PARKING_OPTIONS  DISTANCE_TO_SCHOOLS  ACCURACY  \n",
      "0      attached garage             1.000000  0.506022  \n",
      "1      attached garage             1.000000  0.506022  \n",
      "2              carport             1.000000  0.506022  \n",
      "3      attached garage             1.000000  0.506022  \n",
      "4              carport             1.000000  0.506022  \n",
      "...                ...                  ...       ...  \n",
      "53141          carport             0.004822  0.869171  \n",
      "53142          carport             0.005533  0.869171  \n",
      "53143          carport             0.004822  0.869171  \n",
      "53144          carport             0.003965  0.869171  \n",
      "53145          carport             0.005533  0.869171  \n",
      "\n",
      "[53146 rows x 17 columns]\n"
     ]
    }
   ],
   "source": [
    "nz_fun_grouped_apply = NZFunGroupedApply(df=cleaned_properties_idadf, columns = ['ID','REGION','TYPE','SQFEET','PRICE','BEDS','BATHS','CATS_ALLOWED','DOGS_ALLOWED'\n",
    ",'SMOKING_ALLOWED','WHEELCHAIR_ACCESS','ELECTRIC_VEHICLE_CHARGE','COMES_FURNISHED','LAUNDRY_OPTIONS','PARKING_OPTIONS','DISTANCE_TO_SCHOOLS'],code_str=code_house_prediction_model, index='REGION',fun_name =\"house_prediction_model\", output_table='predictions',output_signature=output_signature)\n",
    "result_idadf = nz_fun_grouped_apply.get_result()\n",
    "result = result_idadf.as_dataframe()\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.13 64-bit",
   "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.13"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "b0fa6594d8f4cbf19f97940f81e996739fb7646882a419484c72d19e05852a7e"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}