Postgclassification_using_neighboring_info.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Recommandation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Please be test at first time the rules for postclassification on a restricted subset of the area of interest as the computation on a very large area could take a while "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center> <font size=6> <b> Table of Contents </b> </font> </center> \n",
    "<div id=\"toc\"></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "$.getScript('https://kmahelona.github.io/ipython_notebook_goodies/ipython_notebook_toc.js')"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%javascript\n",
    "$.getScript('https://kmahelona.github.io/ipython_notebook_goodies/ipython_notebook_toc.js')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center> <font size=5> <h1>Define working environment</h1> </font> </center> "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Import libraries**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Import libraries needed for setting parameters of operating system \n",
    "import os\n",
    "import sys\n",
    "import shutil\n",
    "\n",
    "## Import Pandas library\n",
    "import pandas as pd\n",
    "\n",
    "## Import Psycopg2 library (interection with postgres database)\n",
    "import psycopg2 as pg\n",
    "\n",
    "## Import libraries for .zip file creation\n",
    "import zipfile\n",
    "from os.path import basename"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center> <font size=3> <h3>Environment variables when working on Linux Mint</h3> </font> </center> "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Set 'Python' and 'GRASS GIS' environment variables**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "### Define GRASS GIS environment variables for LINUX UBUNTU Mint 18.1 (Serena)\n",
    "# Check is environmental variables exists and create them (empty) if not exists.\n",
    "if not 'PYTHONPATH' in os.environ:\n",
    "    os.environ['PYTHONPATH']=''\n",
    "if not 'LD_LIBRARY_PATH' in os.environ:\n",
    "    os.environ['LD_LIBRARY_PATH']=''\n",
    "# Set environmental variables\n",
    "os.environ['GISBASE'] = '/home/tais/SRC/GRASS/grass_trunk/dist.x86_64-pc-linux-gnu'\n",
    "os.environ['PATH'] += os.pathsep + os.path.join(os.environ['GISBASE'],'bin')\n",
    "os.environ['PATH'] += os.pathsep + os.path.join(os.environ['GISBASE'],'script')\n",
    "os.environ['PATH'] += os.pathsep + os.path.join(os.environ['GISBASE'],'lib')\n",
    "#os.environ['PATH'] += os.pathsep + os.path.join(os.environ['GISBASE'],'etc','python')\n",
    "os.environ['PYTHONPATH'] += os.pathsep + os.path.join(os.environ['GISBASE'],'etc','python')\n",
    "os.environ['PYTHONPATH'] += os.pathsep + os.path.join(os.environ['GISBASE'],'etc','python','grass')\n",
    "os.environ['PYTHONPATH'] += os.pathsep + os.path.join(os.environ['GISBASE'],'etc','python','grass','script')\n",
    "os.environ['PYTHONLIB'] = '/usr/lib/python2.7'\n",
    "os.environ['LD_LIBRARY_PATH'] += os.pathsep + os.path.join(os.environ['GISBASE'],'lib')\n",
    "os.environ['GIS_LOCK'] = '$$'\n",
    "os.environ['GISRC'] = os.path.join(os.environ['HOME'],'.grass7','rc')\n",
    "os.environ['PATH'] += os.pathsep + os.path.join(os.environ['HOME'],'.grass7','addons')\n",
    "os.environ['PATH'] += os.pathsep + os.path.join(os.environ['HOME'],'.grass7','addons','bin')\n",
    "os.environ['PATH'] += os.pathsep + os.path.join(os.environ['HOME'],'.grass7','addons')\n",
    "os.environ['PATH'] += os.pathsep + os.path.join(os.environ['HOME'],'.grass7','addons','scripts')\n",
    "\n",
    "## Define GRASS-Python environment\n",
    "sys.path.append(os.path.join(os.environ['GISBASE'],'etc','python'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "**Import GRASS Python packages**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Import libraries needed to launch GRASS GIS in the jupyter notebook\n",
    "import grass.script.setup as gsetup\n",
    "\n",
    "## Import libraries needed to call GRASS using Python\n",
    "import grass.script as gscript"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "**Display current environment variables of your computer**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MDMSESSION = mate \t\n",
      "MANDATORY_PATH = /usr/share/gconf/mate.mandatory.path \t\n",
      "MATE_DESKTOP_SESSION_ID = this-is-deprecated \t\n",
      "LESSOPEN = | /usr/bin/lesspipe %s \t\n",
      "MDM_LANG = fr_BE.UTF-8 \t\n",
      "LOGNAME = tais \t\n",
      "USER = tais \t\n",
      "HOME = /home/tais \t\n",
      "XDG_VTNR = 8 \t\n",
      "PATH = /usr/local/bin:/home/tais/BIN:/home/tais/bin:/home/tais/.local/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/home/tais/SRC/GRASS/grass_trunk/dist.x86_64-pc-linux-gnu/bin:/home/tais/SRC/GRASS/grass_trunk/dist.x86_64-pc-linux-gnu/script:/home/tais/SRC/GRASS/grass_trunk/dist.x86_64-pc-linux-gnu/lib:/home/tais/.grass7/addons:/home/tais/.grass7/addons/bin:/home/tais/.grass7/addons:/home/tais/.grass7/addons/scripts \t\n",
      "CLICOLOR = 1 \t\n",
      "DISPLAY = :0.0 \t\n",
      "SSH_AGENT_PID = 1835 \t\n",
      "LANG = fr_BE.UTF-8 \t\n",
      "TERM = xterm-color \t\n",
      "SHELL = /bin/bash \t\n",
      "GIS_LOCK = $$ \t\n",
      "XAUTHORITY = /home/tais/.Xauthority \t\n",
      "SESSION_MANAGER = local/tais-HP-Z620-Workstation:@/tmp/.ICE-unix/1763,unix/tais-HP-Z620-Workstation:/tmp/.ICE-unix/1763 \t\n",
      "SHLVL = 1 \t\n",
      "QT_LINUX_ACCESSIBILITY_ALWAYS_ON = 1 \t\n",
      "INSIDE_CAJA_PYTHON =  \t\n",
      "QT_ACCESSIBILITY = 1 \t\n",
      "LD_LIBRARY_PATH = :/home/tais/SRC/GRASS/grass_trunk/dist.x86_64-pc-linux-gnu/lib \t\n",
      "COMPIZ_CONFIG_PROFILE = mate \t\n",
      "WINDOWPATH = 8 \t\n",
      "GTK_OVERLAY_SCROLLING = 0 \t\n",
      "PYTHONPATH = :/home/tais/SRC/GRASS/grass_trunk/dist.x86_64-pc-linux-gnu/etc/python:/home/tais/SRC/GRASS/grass_trunk/dist.x86_64-pc-linux-gnu/etc/python/grass:/home/tais/SRC/GRASS/grass_trunk/dist.x86_64-pc-linux-gnu/etc/python/grass/script \t\n",
      "GISBASE = /home/tais/SRC/GRASS/grass_trunk/dist.x86_64-pc-linux-gnu \t\n",
      "CLUTTER_BACKEND = x11 \t\n",
      "USERNAME = tais \t\n",
      "XDG_SESSION_DESKTOP = mate \t\n",
      "GDM_XSERVER_LOCATION = local \t\n",
      "XDG_RUNTIME_DIR = /run/user/1000 \t\n",
      "JPY_PARENT_PID = 6793 \t\n",
      "QT_STYLE_OVERRIDE = gtk \t\n",
      "SSH_AUTH_SOCK = /run/user/1000/keyring/ssh \t\n",
      "VTE_VERSION = 4205 \t\n",
      "GDMSESSION = mate \t\n",
      "GISRC = /home/tais/.grass7/rc \t\n",
      "GIT_PAGER = cat \t\n",
      "XDG_CONFIG_DIRS = /etc/xdg/xdg-mate:/etc/xdg \t\n",
      "XDG_CURRENT_DESKTOP = MATE \t\n",
      "XDG_SESSION_ID = c1 \t\n",
      "DBUS_SESSION_BUS_ADDRESS = unix:abstract=/tmp/dbus-7a5jXuGN5i,guid=725030f3f70fb940b7f858365ac72e76 \t\n",
      "_ = /usr/local/bin/jupyter \t\n",
      "XDG_SESSION_COOKIE = 8441891e86e24d76b9616edf516d5734-1523002998.44844-308985481 \t\n",
      "DESKTOP_SESSION = mate \t\n",
      "WINDOWID = 65011718 \t\n",
      "LESSCLOSE = /usr/bin/lesspipe %s %s \t\n",
      "DEFAULTS_PATH = /usr/share/gconf/mate.default.path \t\n",
      "MPLBACKEND = module://ipykernel.pylab.backend_inline \t\n",
      "MDM_XSERVER_LOCATION = local \t\n",
      "GTK_MODULES = gail:atk-bridge \t\n",
      "XDG_DATA_DIRS = /usr/share/mate:/usr/local/share/:/usr/share/:/usr/share/mdm/ \t\n",
      "PWD = /media/tais/My_Book_1/MAUPP/Traitement/Ouagadougou/Segmentation_fullAOI_localapproach/Notebooks/Postclassification \t\n",
      "COLORTERM = mate-terminal \t\n",
      "PYTHONLIB = /usr/lib/python2.7 \t\n",
      "LS_COLORS = rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:mi=00:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arc=01;31:*.arj=01;31:*.taz=01;31:*.lha=01;31:*.lz4=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.tzo=01;31:*.t7z=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lrz=01;31:*.lz=01;31:*.lzo=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.war=01;31:*.ear=01;31:*.sar=01;31:*.rar=01;31:*.alz=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.cab=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.webm=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=00;36:*.au=00;36:*.flac=00;36:*.m4a=00;36:*.mid=00;36:*.midi=00;36:*.mka=00;36:*.mp3=00;36:*.mpc=00;36:*.ogg=00;36:*.ra=00;36:*.wav=00;36:*.oga=00;36:*.opus=00;36:*.spx=00;36:*.xspf=00;36: \t\n",
      "PAGER = cat \t\n",
      "XDG_SEAT = seat0 \t\n"
     ]
    }
   ],
   "source": [
    "## Display the current defined environment variables\n",
    "for key in os.environ.keys():\n",
    "    print \"%s = %s \\t\" % (key,os.environ[key])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center> <font size=5> <h1>User inputs</h1> </font> </center> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Define a empty dictionnary for saving user inputs\n",
    "user={}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Enter the path to GRASSDATA folder\n",
    "user[\"gisdb\"] = \"/media/tais/My_Book_1/MAUPP/Traitement/Ouagadougou/Segmentation_fullAOI_localapproach/GRASSDATA\"\n",
    "\n",
    "## Enter the name of the location (existing or for a new one)\n",
    "user[\"location\"] = \"Ouaga_32630\"\n",
    "\n",
    "## Enter the EPSG code for this location \n",
    "user[\"locationepsg\"] = \"32630\"\n",
    "\n",
    "## Enter the name of the mapset to use\n",
    "user[\"working_mapsetname\"] = \"postclassification\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<center> <font size=5> <h1>Define functions</h1> </font> </center> "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This section of the notebook is dedicated to defining functions which will then be called later in the script. If you want to create your own functions, define them here."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function for computing processing time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The \"print_processing_time\" is used to calculate and display the processing time for various stages of the processing chain. At the beginning of each major step, the current time is stored in a new variable, using [time.time() function](https://docs.python.org/2/library/time.html). At the end of the stage in question, the \"print_processing_time\" function is called and takes as argument the name of this new variable containing the recorded time at the beginning of the stage, and an output message."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Import library for managing time in python\n",
    "import time  \n",
    "\n",
    "## Function \"print_processing_time()\" compute processing time and printing it.\n",
    "# The argument \"begintime\" wait for a variable containing the begintime (result of time.time()) of the process for which to compute processing time.\n",
    "# The argument \"printmessage\" wait for a string format with information about the process. \n",
    "def print_processing_time(begintime, printmessage):    \n",
    "    endtime=time.time()           \n",
    "    processtime=endtime-begintime\n",
    "    remainingtime=processtime\n",
    "\n",
    "    days=int((remainingtime)/86400)\n",
    "    remainingtime-=(days*86400)\n",
    "    hours=int((remainingtime)/3600)\n",
    "    remainingtime-=(hours*3600)\n",
    "    minutes=int((remainingtime)/60)\n",
    "    remainingtime-=(minutes*60)\n",
    "    seconds=round((remainingtime)%60,1)\n",
    "\n",
    "    if processtime<60:\n",
    "        finalprintmessage=str(printmessage)+str(seconds)+\" seconds\"\n",
    "    elif processtime<3600:\n",
    "        finalprintmessage=str(printmessage)+str(minutes)+\" minutes and \"+str(seconds)+\" seconds\"\n",
    "    elif processtime<86400:\n",
    "        finalprintmessage=str(printmessage)+str(hours)+\" hours and \"+str(minutes)+\" minutes and \"+str(seconds)+\" seconds\"\n",
    "    elif processtime>=86400:\n",
    "        finalprintmessage=str(printmessage)+str(days)+\" days, \"+str(hours)+\" hours and \"+str(minutes)+\" minutes and \"+str(seconds)+\" seconds\"\n",
    "    \n",
    "    return finalprintmessage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Function for Postgres database vaccum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Do a VACUUM on the current Postgresql database\n",
    "def vacuum(db):\n",
    "    old_isolation_level = db.isolation_level\n",
    "    db.set_isolation_level(0)\n",
    "    query = \"VACUUM\"\n",
    "    cur.execute(query)\n",
    "    db.set_isolation_level(old_isolation_level)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define function to display header of a PostGis table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def display_header(db_connect, schema, table, nrow=15, subcolumns=\"*\", where=\"\", orderby=\"\"):\n",
    "    # Where subquery\n",
    "    where_query=where\n",
    "    if len(where)>0:\n",
    "        where_query=\"WHERE %s\"%where\n",
    "    # Order by subquery\n",
    "    orderby_query=orderby\n",
    "    if len(orderby)>0:\n",
    "        orderby_query=\"ORDER BY %s\"%orderby\n",
    "    query=\"SELECT %s FROM %s.%s %s %s LIMIT %s\"%(subcolumns,schema,table,where_query,orderby_query,nrow)\n",
    "    # Execute query through panda\n",
    "    df=pd.read_sql(query, db_connect)\n",
    "    # Show dataframe\n",
    "    return df.head(nrow)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Instal GRASS extensions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "GRASS GIS have both a core part (the one installed by default on your computer) and add-ons (which have to be installed using the extension manager ['g.extension'](https://grass.osgeo.org/grass72/manuals/g.extension.html)).\n",
    "\n",
    "In the next cell, 'i.segment.uspo' will be installed (if not yet) and also other add-ons ['r.neighborhoodmatrix'](https://grass.osgeo.org/grass70/manuals/addons/r.neighborhoodmatrix.html) and ['i.segment.hierarchical'](https://grass.osgeo.org/grass70/manuals/addons/i.segment.hierarchical.html) required for running i.segment.uspo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "You are now working in mapset 'PERMANENT'\n",
      "r.neighborhoodmatrix is already installed on your computer\n",
      "i.segment.stats is already installed on your computer\n",
      "r.object.geometry is already installed on your computer\n"
     ]
    }
   ],
   "source": [
    "## Launch GRASS GIS working session in the PERMANENT mapset\n",
    "if os.path.exists(os.path.join(user[\"gisdb\"],user[\"location\"],\"PERMANENT\")):\n",
    "    gsetup.init(os.environ['GISBASE'], user[\"gisdb\"], user[\"location\"], \"PERMANENT\")\n",
    "    print \"You are now working in mapset 'PERMANENT'\" \n",
    "else: \n",
    "    print \"'PERMANENT' mapset doesn't exists in \"+user[\"gisdb\"]\n",
    "    \n",
    "## Instal r.neighborhoodmatrix if not yet installed\n",
    "if \"r.neighborhoodmatrix\" not in gscript.parse_command('g.extension', flags=\"a\"):\n",
    "    gscript.run_command('g.extension', extension=\"r.neighborhoodmatrix\")\n",
    "    print \"r.neighborhoodmatrix have been installed on your computer\"\n",
    "else: print \"r.neighborhoodmatrix is already installed on your computer\" \n",
    "   \n",
    "## Instal i.segment.stats if not yet installed\n",
    "if \"i.segment.stats\" not in gscript.parse_command('g.extension', flags=\"a\"):\n",
    "    gscript.run_command('g.extension', extension=\"i.segment.stats\")\n",
    "    print \"i.segment.stats have been installed on your computer\"\n",
    "else: print \"i.segment.stats is already installed on your computer\" \n",
    "    \n",
    "## Instal r.object.geometry if not yet installed\n",
    "if \"r.object.geometry\" not in gscript.parse_command('g.extension', flags=\"a\"):\n",
    "    gscript.run_command('g.extension', extension=\"r.object.geometry\")\n",
    "    print \"r.object.geometry have been installed on your computer\"\n",
    "else: print \"r.object.geometry is already installed on your computer\" "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime_full=time.time()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Launch GRASS GIS working session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "'postclassification' mapset already exists\n"
     ]
    }
   ],
   "source": [
    "## Set the name of the mapset in which to work\n",
    "mapsetname=user[\"working_mapsetname\"]\n",
    "\n",
    "## Create the mapset if not exists\n",
    "if os.path.exists(os.path.join(user[\"gisdb\"],user[\"location\"],mapsetname)):\n",
    "    print \"'\"+mapsetname+\"' mapset already exists\"\n",
    "    if not os.path.exists(os.path.join(user[\"gisdb\"],user[\"location\"],mapsetname, 'WIND')):\n",
    "        shutil.copy(os.path.join(user[\"gisdb\"],user[\"location\"],'PERMANENT','WIND'),\n",
    "                    os.path.join(user[\"gisdb\"],user[\"location\"],mapsetname,'WIND'))\n",
    "else: \n",
    "    os.makedirs(os.path.join(user[\"gisdb\"],user[\"location\"],mapsetname))\n",
    "    shutil.copy(os.path.join(user[\"gisdb\"],user[\"location\"],'PERMANENT','WIND'),\n",
    "                os.path.join(user[\"gisdb\"],user[\"location\"],mapsetname,'WIND'))\n",
    "    print \"'\"+mapsetname+\"' mapset created in location \"+user[\"gisdb\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "You are now working in mapset 'postclassification'\n"
     ]
    }
   ],
   "source": [
    "## Launch GRASS GIS working session in the mapset\n",
    "if os.path.exists(os.path.join(user[\"gisdb\"],user[\"location\"],mapsetname)):\n",
    "    gsetup.init(os.environ['GISBASE'], user[\"gisdb\"], user[\"location\"], mapsetname)\n",
    "    print \"You are now working in mapset '\"+mapsetname+\"'\" \n",
    "else: \n",
    "    print \"'\"+mapsetname+\"' mapset doesn't exists in \"+user[\"gisdb\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Define files' path and layers' name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** Define name of layers **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "segment_layer='segmentation_raster@LOCAL_SEGMENT'\n",
    "classification_layer='rf_classif_l2b@classif_E'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** Define path to files **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Neighborhood matrix\n",
    "nmatrix='/media/tais/My_Book_1/MAUPP/Traitement/Ouagadougou/Segmentation_fullAOI_localapproach/Results/POSTCLASSIF/nmatrix'\n",
    "#\n",
    "seglabels='/media/tais/My_Book_1/MAUPP/Traitement/Ouagadougou/Segmentation_fullAOI_localapproach/Results/POSTCLASSIF/seglabels'\n",
    "#\n",
    "segstats='/media/tais/My_Book_1/MAUPP/Traitement/Ouagadougou/Segmentation_fullAOI_localapproach/Results/POSTCLASSIF/segstats'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Compute neighborhood matrix"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check if segmentation layer is in the current folder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Check if segmentation layer is in the current mapset\n",
    "current_mapset=gscript.gisenv()['MAPSET']\n",
    "result = gscript.find_file(segment_layer.split(\"@\")[0]+\"@\"+current_mapset, element='cell')\n",
    "if len(result['name']) == 0:\n",
    "    print \"Segmentation layer <%s> not found in <%s> mapset.\"%(segment_layer,current_mapset)\n",
    "    print \"The layer will be copied in the current mapset.\"\n",
    "    gscript.run_command('g.copy', overwrite=True, raster='%s,%s'%(segment_layer,segment_layer.split(\"@\")[0]))\n",
    "    segment_layer=segment_layer.split(\"@\")[0]+\"@\"+current_mapset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following cell use r.neighborhoodmatrix. If you faced this error message, it is probably because you have an old version of this add-on installer on your computer. Please update it and retry.\n",
    "\n",
    "*ERREUR: r.neighborhoodmatrix : désolé, <l> n'est pas une option valide*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-19-93f01fb47910>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     11\u001b[0m \u001b[0mgscript\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'g.region'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mraster\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0msegment_layer\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;31m# Define the region to match the extension and resolution of the segmentation layer\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     12\u001b[0m gscript.run_command('r.neighborhoodmatrix', overwrite=True, flags='l', \n\u001b[0;32m---> 13\u001b[0;31m                     input=segment_layer, output=nmatrix, processes=n_jobs)\n\u001b[0m\u001b[1;32m     14\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     15\u001b[0m \u001b[0;31m# Print processing time\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/tais/SRC/GRASS/grass_trunk/dist.x86_64-pc-linux-gnu/etc/python/grass/script/core.pyc\u001b[0m in \u001b[0;36mrun_command\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m    418\u001b[0m             \u001b[0msys\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstderr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwrite\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstderr\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    419\u001b[0m     \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 420\u001b[0;31m         \u001b[0mreturncode\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mps\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwait\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    421\u001b[0m     \u001b[0;32mreturn\u001b[0m \u001b[0mhandle_errors\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mreturncode\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mreturncode\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    422\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/lib/python2.7/subprocess.pyc\u001b[0m in \u001b[0;36mwait\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1390\u001b[0m             \u001b[0;32mwhile\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreturncode\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1391\u001b[0m                 \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1392\u001b[0;31m                     \u001b[0mpid\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msts\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_eintr_retry_call\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mos\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwaitpid\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpid\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1393\u001b[0m                 \u001b[0;32mexcept\u001b[0m \u001b[0mOSError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1394\u001b[0m                     \u001b[0;32mif\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0merrno\u001b[0m \u001b[0;34m!=\u001b[0m \u001b[0merrno\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mECHILD\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/lib/python2.7/subprocess.pyc\u001b[0m in \u001b[0;36m_eintr_retry_call\u001b[0;34m(func, *args)\u001b[0m\n\u001b[1;32m    474\u001b[0m     \u001b[0;32mwhile\u001b[0m \u001b[0mTrue\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    475\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 476\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    477\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mOSError\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mIOError\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    478\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0merrno\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0merrno\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mEINTR\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime_rmatrix=time.time()\n",
    "\n",
    "# Set-up number of cores to be used for r.neighborhoodmatrix\n",
    "import multiprocessing\n",
    "n_jobs=multiprocessing.cpu_count()-1  # Leave one core available to not freeze the computer\n",
    "if n_jobs > 4:\n",
    "    n_jobs=4 # Maximum parallelization available for this module.\n",
    "    \n",
    "# Compute neighborhood matrix\n",
    "gscript.run_command('g.region', raster=segment_layer) # Define the region to match the extension and resolution of the segmentation layer\n",
    "gscript.run_command('r.neighborhoodmatrix', overwrite=True, flags='l', \n",
    "                    input=segment_layer, output=nmatrix, processes=n_jobs)\n",
    "\n",
    "# Print processing time\n",
    "print_processing_time(begintime_rmatrix, \"Neighborhood matrix computed in \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Create a .zip archive with the neighborhood matrix file\n",
    "with zipfile.ZipFile(nmatrix+\".zip\", 'w', zipfile.ZIP_DEFLATED) as myzip:\n",
    "    myzip.write(nmatrix,basename(nmatrix))\n",
    "    myzip.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get segments' labels and statistics (shape etc..)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you already have .csv files with labels and statistics of the segments, please set up the previous paths correctly and skip the following cell"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# Get labels of segments\n",
    "gscript.run_command('i.segment.stats', overwrite=True, flags='s', map=segment_layer, \n",
    "                    rasters=classification_layer, raster_statistics='max', csvfile=seglabels)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# Set a list of raster from which to compute segment statistics\n",
    "listraster=[]\n",
    "listraster.append('ndsm@postclassification')\n",
    "listraster.append('ndvi@postclassification')\n",
    "\n",
    "# Set of raster statistics to be used\n",
    "rasterstat=[]\n",
    "rasterstat.append('min')\n",
    "rasterstat.append('max')\n",
    "rasterstat.append('mean')\n",
    "rasterstat.append('stddev')\n",
    "rasterstat.append('median')\n",
    "\n",
    "# Set of shape statistics to be computed\n",
    "shapestat=[]\n",
    "shapestat.append('area')\n",
    "shapestat.append('perimeter')\n",
    "shapestat.append('compact_circle')\n",
    "shapestat.append('compact_square')\n",
    "shapestat.append('fd')\n",
    "\n",
    "# Get some statistics of segments\n",
    "gscript.run_command('i.segment.stats', overwrite=True, map=segment_layer, \n",
    "                    rasters=listraster, raster_statistics=rasterstat,\n",
    "                    area_measures=shapestat, csvfile=segstats)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "# Print processing time\n",
    "print_processing_time(begintime, \"Segments' labels and statistics computed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import in PostGIS database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the next cell, please enter the information needed to connect to an existing PostGIS database. Please notice that it is assumed you already created this database and CREATE EXTENSION postgis on it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# User for postgresql connexion\n",
    "dbuser=\"tais\"\n",
    "# Password of user\n",
    "dbpassword=\"tais\"\n",
    "# Host of database\n",
    "host=\"localhost\"\n",
    "# Name of the new database\n",
    "dbname=\"ouaga_fullaoi_localsegment\"\n",
    "# Set name of schema for objects statistics\n",
    "postclass_schema=\"postclassif\"\n",
    "# Set the name for the final table containing the neighborhood information\n",
    "object_stats_table=\"neighborhood_information\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to an existing database\n",
    "db=pg.connect(database=dbname, user=dbuser, password=dbpassword, host=host)\n",
    "# Open a cursor to perform database operations\n",
    "cur=db.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create schema if not exists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Queries to exectue\n",
    "query=\"CREATE SCHEMA IF NOT EXISTS %s AUTHORIZATION %s\"%(postclass_schema,dbuser)\n",
    "# Execute the queries \n",
    "cur.execute(query)\n",
    "# Make the changes to the database persistent\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create extension 'postgis' and 'tablefunc'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On linux: required the 'postgresql-contrib-9.X' package to be installed (with X corresponding to your version of PostgreSQL)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Queries to exectue\n",
    "queries=[]\n",
    "queries.append(\"CREATE EXTENSION IF NOT EXISTS postgis\")\n",
    "queries.append(\"CREATE EXTENSION IF NOT EXISTS tablefunc\")\n",
    "# Execute the queries \n",
    "cur.execute(\";\".join(queries))\n",
    "# Make the changes to the database persistent\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import neighborhood matrix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Table imported in PostGis in 5 minutes and 54.6 seconds'"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "\n",
    "# Queries to exectue\n",
    "queries=[]\n",
    "queries.append(\"DROP TABLE IF EXISTS {schema}.rmatrix\".format(schema=postclass_schema))\n",
    "queries.append(\"CREATE TABLE {schema}.rmatrix \\\n",
    "(key_value serial PRIMARY KEY, segA integer, segB integer, border_count integer)\".format(schema=postclass_schema))\n",
    "queries.append(\"COPY {schema}.rmatrix(segA, segB, border_count) \\\n",
    "FROM '{pathtofile}' DELIMITER '|' CSV\".format(schema=postclass_schema,pathtofile=nmatrix))\n",
    "\n",
    "# Execute the queries \n",
    "cur.execute(\";\".join(queries))\n",
    "# Make the changes to the database persistent\n",
    "db.commit()\n",
    "               \n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Table imported in PostGis in \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key_value</th>\n",
       "      <th>sega</th>\n",
       "      <th>segb</th>\n",
       "      <th>border_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3827</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>3827</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>19</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>3827</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>3827</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   key_value  sega  segb  border_count\n",
       "0          1     1  3827            43\n",
       "1          2     2     3             1\n",
       "2          3     2     4            17\n",
       "3          4     2  3827             7\n",
       "4          5     3     2             1\n",
       "5          6     3     5             8\n",
       "6          7     3    19             2\n",
       "7          8     3  3827             5\n",
       "8          9     4     2            17\n",
       "9         10     4  3827            89"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display_header(db, postclass_schema, \"rmatrix\", nrow=10, subcolumns=\"*\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import segment labels"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run the following cells only if segment labels table do not yet exists in the PostGis database."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "\n",
    "# Queries to exectue\n",
    "queries=[]\n",
    "queries.append(\"DROP TABLE IF EXISTS {schema}.seglabel\".format(schema=postclass_schema))\n",
    "queries.append(\"CREATE TABLE {schema}.seglabel \\\n",
    "(key_value serial PRIMARY KEY, seg integer, label integer)\".format(schema=postclass_schema))\n",
    "queries.append(\"COPY {schema}.seglabel(seg, label) \\\n",
    "FROM '{pathtofile}' HEADER DELIMITER '|' CSV\".format(schema=postclass_schema,pathtofile=seglabels))\n",
    "\n",
    "# Execute the queries \n",
    "cur.execute(\";\".join(queries))\n",
    "# Make the changes to the database persistent\n",
    "db.commit()\n",
    "\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Table imported in PostGis in \")"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "display_header(db, postclass_schema, \"seglabel\", nrow=10, subcolumns=\"*\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import segment statistics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run the following cells only if segment statistics table do not yet exists in the PostGis database."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "\n",
    "# Import library\n",
    "import csv\n",
    "\n",
    "# Create a reader for the first csv file in the stack of csv to be imported\n",
    "fin=open(segstats, 'r')\n",
    "reader=csv.reader(fin, delimiter='|') \n",
    "column_name=reader.next() # Get the content of the first row\n",
    "\n",
    "## Build a query for creation of a new table with auto-incremental key-value (thus avoiding potential duplicates of 'cat' value)\n",
    "# All column data-types are set to 'text' in order to be able to import some 'nan', 'inf' or 'null' values present in statistics files\n",
    "queries=[]\n",
    "queries.append(\"DROP TABLE IF EXISTS {schema}.segstats\".format(schema=postclass_schema))\n",
    "queries.append(\"CREATE TABLE {schema}.segstats (\\\n",
    "key_value serial PRIMARY KEY, \\\n",
    "{firstcolumn} integer, \\\n",
    "{remaining_columns_declararation})\".format(schema=postclass_schema,\n",
    "                                           firstcolumn=column_name[0],\n",
    "                                           remaining_columns_declararation=\n",
    "                                           ','.join([\"%s double precision\"%column for column in column_name[1:]])))\n",
    "               \n",
    "# Create query for copy data from csv, avoiding the header, and updating only the column which are in the csv (to allow auto-incremental key value to wokr)\n",
    "queries.append(\"COPY {schema}.segstats ({columnsname}) \\\n",
    "FROM '{pathtofile}' HEADER DELIMITER '|' CSV\".format(schema=postclass_schema,\n",
    "                                                     columnsname=', '.join(column_name),\n",
    "                                                     pathtofile=segstats))\n",
    "\n",
    "# Execute the queries \n",
    "cur.execute(\";\".join(queries))\n",
    "# Make the changes to the database persistent\n",
    "db.commit() \n",
    "               \n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Table imported in PostGis in \")"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "scrolled": true
   },
   "source": [
    "display_header(db, postclass_schema, \"segstats\", nrow=10, subcolumns=\"*\")"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Close cursor and communication with the database\n",
    "cur.close()\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Copy segment labels table (class predictions for each segment)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run the following cells only if segment labels table already exists in the PostGis database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Set the name of the table containing the labels (including the schema name, e.g. <myschema.mytable>)\n",
    "classif_table=\"classif_e.classif\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Table copied in PostGis in 15.8 seconds'"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "\n",
    "# Queries to exectue\n",
    "queries=[]\n",
    "queries.append(\"DROP TABLE IF EXISTS {schema}.seglabel\".format(schema=postclass_schema))\n",
    "queries.append(\"CREATE TABLE {schema}.seglabel AS(\\\n",
    "SELECT * FROM {classif_table})\".format(schema=postclass_schema,classif_table=classif_table))\n",
    "queries.append(\"ALTER TABLE {schema}.seglabel RENAME COLUMN seg_id TO seg;\".format(schema=postclass_schema))\n",
    "queries.append(\"ALTER TABLE {schema}.seglabel RENAME COLUMN rf_pred_l2b TO label;\".format(schema=postclass_schema))\n",
    "queries.append(\"ALTER TABLE {schema}.seglabel DROP COLUMN IF EXISTS rf_pred_l2;\".format(schema=postclass_schema))\n",
    "queries.append(\"ALTER TABLE {schema}.seglabel DROP COLUMN IF EXISTS rf_pred_l1;\".format(schema=postclass_schema))\n",
    "\n",
    "# Execute the queries \n",
    "cur.execute(\";\".join(queries))\n",
    "# Make the changes to the database persistent\n",
    "db.commit()\n",
    "\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Table copied in PostGis in \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seg</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15596</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15598</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22375</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22376</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22377</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>23732</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>24411</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>24412</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>24413</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>24415</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     seg  label\n",
       "0  15596     30\n",
       "1  15598     30\n",
       "2  22375     30\n",
       "3  22376     30\n",
       "4  22377     30\n",
       "5  23732     30\n",
       "6  24411     30\n",
       "7  24412     30\n",
       "8  24413     30\n",
       "9  24415     30"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display_header(db, postclass_schema, \"seglabel\", nrow=10, subcolumns=\"*\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Copy segment statistics table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run the following cells only if segment statistics table already exists in the PostGis database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Set the name of the table containing the segment statistics (including the schema name, e.g. <myschema.mytable>)\n",
    "statistics_table=\"statistics.object_stats_optical\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Table copied in PostGis in 4 minutes and 23.9 seconds'"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "\n",
    "# Queries to exectue\n",
    "queries=[]\n",
    "queries.append(\"DROP TABLE IF EXISTS {schema}.segstats\".format(schema=postclass_schema))\n",
    "queries.append(\"CREATE TABLE {schema}.segstats AS(\\\n",
    "SELECT * FROM {statistics_table})\".format(schema=postclass_schema,statistics_table=statistics_table))\n",
    "\n",
    "# Execute the queries \n",
    "cur.execute(\";\".join(queries))\n",
    "# Make the changes to the database persistent\n",
    "db.commit()\n",
    "\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Table copied in PostGis in \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cat</th>\n",
       "      <th>area</th>\n",
       "      <th>perimeter</th>\n",
       "      <th>compact_circle</th>\n",
       "      <th>compact_square</th>\n",
       "      <th>fd</th>\n",
       "      <th>opt_blue_min</th>\n",
       "      <th>opt_blue_max</th>\n",
       "      <th>opt_blue_range</th>\n",
       "      <th>opt_blue_mean</th>\n",
       "      <th>...</th>\n",
       "      <th>ndsm_perc_90</th>\n",
       "      <th>ndvi_min</th>\n",
       "      <th>ndvi_max</th>\n",
       "      <th>ndvi_range</th>\n",
       "      <th>ndvi_mean</th>\n",
       "      <th>ndvi_stddev</th>\n",
       "      <th>ndvi_median</th>\n",
       "      <th>ndvi_first_quart</th>\n",
       "      <th>ndvi_third_quart</th>\n",
       "      <th>ndvi_perc_90</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8160198</td>\n",
       "      <td>104</td>\n",
       "      <td>78</td>\n",
       "      <td>2.157610</td>\n",
       "      <td>0.522976</td>\n",
       "      <td>1.876112</td>\n",
       "      <td>202</td>\n",
       "      <td>275</td>\n",
       "      <td>73</td>\n",
       "      <td>242.730769</td>\n",
       "      <td>...</td>\n",
       "      <td>2.673800</td>\n",
       "      <td>0.246829</td>\n",
       "      <td>0.507726</td>\n",
       "      <td>0.260897</td>\n",
       "      <td>0.362308</td>\n",
       "      <td>0.058590</td>\n",
       "      <td>0.367324</td>\n",
       "      <td>0.315093</td>\n",
       "      <td>0.403751</td>\n",
       "      <td>0.443015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8160199</td>\n",
       "      <td>17</td>\n",
       "      <td>26</td>\n",
       "      <td>1.778869</td>\n",
       "      <td>0.634324</td>\n",
       "      <td>2.299883</td>\n",
       "      <td>230</td>\n",
       "      <td>269</td>\n",
       "      <td>39</td>\n",
       "      <td>248.705882</td>\n",
       "      <td>...</td>\n",
       "      <td>2.346530</td>\n",
       "      <td>0.305035</td>\n",
       "      <td>0.420202</td>\n",
       "      <td>0.115167</td>\n",
       "      <td>0.361440</td>\n",
       "      <td>0.032832</td>\n",
       "      <td>0.351406</td>\n",
       "      <td>0.331461</td>\n",
       "      <td>0.390244</td>\n",
       "      <td>0.394928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8152489</td>\n",
       "      <td>25</td>\n",
       "      <td>40</td>\n",
       "      <td>2.256758</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>2.292001</td>\n",
       "      <td>199</td>\n",
       "      <td>219</td>\n",
       "      <td>20</td>\n",
       "      <td>208.560000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.196655</td>\n",
       "      <td>0.218605</td>\n",
       "      <td>0.494540</td>\n",
       "      <td>0.275935</td>\n",
       "      <td>0.393048</td>\n",
       "      <td>0.084720</td>\n",
       "      <td>0.402640</td>\n",
       "      <td>0.316031</td>\n",
       "      <td>0.473851</td>\n",
       "      <td>0.484419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7983940</td>\n",
       "      <td>28</td>\n",
       "      <td>40</td>\n",
       "      <td>2.132436</td>\n",
       "      <td>0.529150</td>\n",
       "      <td>2.214054</td>\n",
       "      <td>205</td>\n",
       "      <td>253</td>\n",
       "      <td>48</td>\n",
       "      <td>223.214286</td>\n",
       "      <td>...</td>\n",
       "      <td>0.094849</td>\n",
       "      <td>0.305687</td>\n",
       "      <td>0.679752</td>\n",
       "      <td>0.374065</td>\n",
       "      <td>0.486583</td>\n",
       "      <td>0.080984</td>\n",
       "      <td>0.491389</td>\n",
       "      <td>0.414216</td>\n",
       "      <td>0.551648</td>\n",
       "      <td>0.568362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7959071</td>\n",
       "      <td>96</td>\n",
       "      <td>80</td>\n",
       "      <td>2.303294</td>\n",
       "      <td>0.489898</td>\n",
       "      <td>1.920106</td>\n",
       "      <td>188</td>\n",
       "      <td>263</td>\n",
       "      <td>75</td>\n",
       "      <td>223.458333</td>\n",
       "      <td>...</td>\n",
       "      <td>0.399719</td>\n",
       "      <td>0.244635</td>\n",
       "      <td>0.738839</td>\n",
       "      <td>0.494204</td>\n",
       "      <td>0.486502</td>\n",
       "      <td>0.109964</td>\n",
       "      <td>0.477218</td>\n",
       "      <td>0.401036</td>\n",
       "      <td>0.558223</td>\n",
       "      <td>0.649695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7959072</td>\n",
       "      <td>33</td>\n",
       "      <td>42</td>\n",
       "      <td>2.062469</td>\n",
       "      <td>0.547101</td>\n",
       "      <td>2.137926</td>\n",
       "      <td>296</td>\n",
       "      <td>350</td>\n",
       "      <td>54</td>\n",
       "      <td>325.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.231884</td>\n",
       "      <td>0.371223</td>\n",
       "      <td>0.139339</td>\n",
       "      <td>0.299076</td>\n",
       "      <td>0.035026</td>\n",
       "      <td>0.304956</td>\n",
       "      <td>0.269777</td>\n",
       "      <td>0.315436</td>\n",
       "      <td>0.338742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7959073</td>\n",
       "      <td>18</td>\n",
       "      <td>24</td>\n",
       "      <td>1.595769</td>\n",
       "      <td>0.707107</td>\n",
       "      <td>2.199020</td>\n",
       "      <td>277</td>\n",
       "      <td>399</td>\n",
       "      <td>122</td>\n",
       "      <td>357.833333</td>\n",
       "      <td>...</td>\n",
       "      <td>0.301270</td>\n",
       "      <td>0.119691</td>\n",
       "      <td>0.462926</td>\n",
       "      <td>0.343235</td>\n",
       "      <td>0.307525</td>\n",
       "      <td>0.099299</td>\n",
       "      <td>0.295397</td>\n",
       "      <td>0.237086</td>\n",
       "      <td>0.378653</td>\n",
       "      <td>0.447022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7959074</td>\n",
       "      <td>235</td>\n",
       "      <td>108</td>\n",
       "      <td>1.987398</td>\n",
       "      <td>0.567767</td>\n",
       "      <td>1.715195</td>\n",
       "      <td>196</td>\n",
       "      <td>261</td>\n",
       "      <td>65</td>\n",
       "      <td>225.634043</td>\n",
       "      <td>...</td>\n",
       "      <td>0.957275</td>\n",
       "      <td>0.213675</td>\n",
       "      <td>0.633609</td>\n",
       "      <td>0.419934</td>\n",
       "      <td>0.378603</td>\n",
       "      <td>0.083197</td>\n",
       "      <td>0.373955</td>\n",
       "      <td>0.320122</td>\n",
       "      <td>0.431988</td>\n",
       "      <td>0.488987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7959075</td>\n",
       "      <td>225</td>\n",
       "      <td>190</td>\n",
       "      <td>3.573201</td>\n",
       "      <td>0.315789</td>\n",
       "      <td>1.937564</td>\n",
       "      <td>234</td>\n",
       "      <td>318</td>\n",
       "      <td>84</td>\n",
       "      <td>276.604444</td>\n",
       "      <td>...</td>\n",
       "      <td>4.924220</td>\n",
       "      <td>0.175573</td>\n",
       "      <td>0.446809</td>\n",
       "      <td>0.271236</td>\n",
       "      <td>0.326123</td>\n",
       "      <td>0.047269</td>\n",
       "      <td>0.325041</td>\n",
       "      <td>0.290889</td>\n",
       "      <td>0.362787</td>\n",
       "      <td>0.391530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7959076</td>\n",
       "      <td>23</td>\n",
       "      <td>26</td>\n",
       "      <td>1.529342</td>\n",
       "      <td>0.737820</td>\n",
       "      <td>2.078174</td>\n",
       "      <td>233</td>\n",
       "      <td>258</td>\n",
       "      <td>25</td>\n",
       "      <td>247.869565</td>\n",
       "      <td>...</td>\n",
       "      <td>3.172670</td>\n",
       "      <td>0.541850</td>\n",
       "      <td>0.657778</td>\n",
       "      <td>0.115928</td>\n",
       "      <td>0.598360</td>\n",
       "      <td>0.031864</td>\n",
       "      <td>0.594512</td>\n",
       "      <td>0.574528</td>\n",
       "      <td>0.624373</td>\n",
       "      <td>0.631617</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 60 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       cat  area  perimeter  compact_circle  compact_square        fd  \\\n",
       "0  8160198   104         78        2.157610        0.522976  1.876112   \n",
       "1  8160199    17         26        1.778869        0.634324  2.299883   \n",
       "2  8152489    25         40        2.256758        0.500000  2.292001   \n",
       "3  7983940    28         40        2.132436        0.529150  2.214054   \n",
       "4  7959071    96         80        2.303294        0.489898  1.920106   \n",
       "5  7959072    33         42        2.062469        0.547101  2.137926   \n",
       "6  7959073    18         24        1.595769        0.707107  2.199020   \n",
       "7  7959074   235        108        1.987398        0.567767  1.715195   \n",
       "8  7959075   225        190        3.573201        0.315789  1.937564   \n",
       "9  7959076    23         26        1.529342        0.737820  2.078174   \n",
       "\n",
       "   opt_blue_min  opt_blue_max  opt_blue_range  opt_blue_mean      ...       \\\n",
       "0           202           275              73     242.730769      ...        \n",
       "1           230           269              39     248.705882      ...        \n",
       "2           199           219              20     208.560000      ...        \n",
       "3           205           253              48     223.214286      ...        \n",
       "4           188           263              75     223.458333      ...        \n",
       "5           296           350              54     325.000000      ...        \n",
       "6           277           399             122     357.833333      ...        \n",
       "7           196           261              65     225.634043      ...        \n",
       "8           234           318              84     276.604444      ...        \n",
       "9           233           258              25     247.869565      ...        \n",
       "\n",
       "   ndsm_perc_90  ndvi_min  ndvi_max  ndvi_range  ndvi_mean  ndvi_stddev  \\\n",
       "0      2.673800  0.246829  0.507726    0.260897   0.362308     0.058590   \n",
       "1      2.346530  0.305035  0.420202    0.115167   0.361440     0.032832   \n",
       "2      0.196655  0.218605  0.494540    0.275935   0.393048     0.084720   \n",
       "3      0.094849  0.305687  0.679752    0.374065   0.486583     0.080984   \n",
       "4      0.399719  0.244635  0.738839    0.494204   0.486502     0.109964   \n",
       "5      0.000000  0.231884  0.371223    0.139339   0.299076     0.035026   \n",
       "6      0.301270  0.119691  0.462926    0.343235   0.307525     0.099299   \n",
       "7      0.957275  0.213675  0.633609    0.419934   0.378603     0.083197   \n",
       "8      4.924220  0.175573  0.446809    0.271236   0.326123     0.047269   \n",
       "9      3.172670  0.541850  0.657778    0.115928   0.598360     0.031864   \n",
       "\n",
       "   ndvi_median  ndvi_first_quart  ndvi_third_quart  ndvi_perc_90  \n",
       "0     0.367324          0.315093          0.403751      0.443015  \n",
       "1     0.351406          0.331461          0.390244      0.394928  \n",
       "2     0.402640          0.316031          0.473851      0.484419  \n",
       "3     0.491389          0.414216          0.551648      0.568362  \n",
       "4     0.477218          0.401036          0.558223      0.649695  \n",
       "5     0.304956          0.269777          0.315436      0.338742  \n",
       "6     0.295397          0.237086          0.378653      0.447022  \n",
       "7     0.373955          0.320122          0.431988      0.488987  \n",
       "8     0.325041          0.290889          0.362787      0.391530  \n",
       "9     0.594512          0.574528          0.624373      0.631617  \n",
       "\n",
       "[10 rows x 60 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display_header(db, postclass_schema, \"segstats\", nrow=10, subcolumns=\"*\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Close cursor and communication with the database\n",
    "cur.close()\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create table with neighbors information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to an existing database\n",
    "db=pg.connect(database=dbname, user=dbuser, password=dbpassword, host=host)\n",
    "# Open a cursor to perform database operations\n",
    "cur=db.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get label of neighbor segments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def neighbors_label_bordercount(db, cur, postclass_schema):\n",
    "    # Queries to exectue\n",
    "    queries=[]\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.tmp_A\".format(schema=postclass_schema))\n",
    "    queries.append(\"CREATE TABLE {schema}.tmp_A AS(SELECT sega AS seg, b.label, border_count \\\n",
    "    FROM {schema}.rmatrix AS a \\\n",
    "    LEFT JOIN {schema}.seglabel AS b ON a.segb=b.seg);\".format(schema=postclass_schema))\n",
    "    # Execute the queries \n",
    "    cur.execute(\";\".join(queries))\n",
    "    # Make the changes to the database persistent\n",
    "    db.commit()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Function executed in 3 minutes and 21.9 seconds'"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Create table with neighbors label and border count\n",
    "neighbors_label_bordercount(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seg</th>\n",
       "      <th>label</th>\n",
       "      <th>border_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3827</td>\n",
       "      <td>30</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>30</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3827</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>30</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>19</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3827</td>\n",
       "      <td>30</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3827</td>\n",
       "      <td>31</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    seg  label  border_count\n",
       "0  3827     30            43\n",
       "1     4     30            17\n",
       "2     3     30             1\n",
       "3  3827     30             7\n",
       "4     2     30             1\n",
       "5     5     30             8\n",
       "6    19     30             2\n",
       "7  3827     30             5\n",
       "8  3827     31            89\n",
       "9     2     31            17"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display_header(db, postclass_schema, \"tmp_A\", nrow=10, subcolumns=\"*\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get cumulative percentage of border shared with neighboring classes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def neighbors_label_cumulatedbordercount(db, cur, postclass_schema):\n",
    "    # Queries to exectue\n",
    "    queries=[]\n",
    "    # Create table\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.tmp_b\".format(schema=postclass_schema))\n",
    "    queries.append(\"CREATE TABLE {schema}.tmp_b AS(WITH \\\n",
    "    max_border AS(\\\n",
    "    SELECT seg, sum(border_count) AS sum_border FROM {schema}.tmp_a GROUP BY seg), \\\n",
    "    tempotable AS(\\\n",
    "    SELECT a.seg, a.label, round((a.border_count*1.0/b.sum_border*1.0),8) AS percent_border \\\n",
    "    FROM {schema}.tmp_a AS a LEFT JOIN max_border AS b ON a.seg=b.seg)\\\n",
    "    SELECT seg, label, sum(percent_border) AS percent_border \\\n",
    "    FROM tempotable GROUP BY seg, label ORDER BY seg, label)\".format(schema=postclass_schema))\n",
    "    # Update percent_border for case with strange values like 0.999999999999... or 0.000000000001\n",
    "    queries.append(\"UPDATE {schema}.tmp_b SET percent_border=1.0 WHERE percent_border>0.9999\".format(schema=postclass_schema))\n",
    "    queries.append(\"UPDATE {schema}.tmp_b SET percent_border=0.0 WHERE percent_border<0.0001\".format(schema=postclass_schema))\n",
    "    # Execute the queries \n",
    "    cur.execute(\";\".join(queries))\n",
    "    # Make the changes to the database persistent\n",
    "    db.commit()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Function executed in 13 minutes and 43.9 seconds'"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Create table with neighbors label and cumulated border count (total for each type of neighboring label)\n",
    "neighbors_label_cumulatedbordercount(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seg</th>\n",
       "      <th>label</th>\n",
       "      <th>percent_border</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>30</td>\n",
       "      <td>0.320000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>0.680000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>31</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>30</td>\n",
       "      <td>0.936073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>31</td>\n",
       "      <td>0.063927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>30</td>\n",
       "      <td>0.605263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6</td>\n",
       "      <td>31</td>\n",
       "      <td>0.394737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>0.866667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>31</td>\n",
       "      <td>0.133333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   seg  label  percent_border\n",
       "0    2     30        0.320000\n",
       "1    2     31        0.680000\n",
       "2    3     30        0.500000\n",
       "3    3     31        0.500000\n",
       "4    5     30        0.936073\n",
       "5    5     31        0.063927\n",
       "6    6     30        0.605263\n",
       "7    6     31        0.394737\n",
       "8    9     30        0.866667\n",
       "9    9     31        0.133333"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display_header(db, postclass_schema, \"tmp_B\", nrow=10, subcolumns=\"*\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivoting table to get percentage of neighbors for each class in distinct columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Define a global variable which will contain the list of current labels\n",
    "global labels_list\n",
    "\n",
    "# Define function\n",
    "def pivoted_proportion_label(db, cur, postclass_schema):\n",
    "    global labels_list\n",
    "    ## Get distinct values for 'label' class\n",
    "    distinctlabelquery=\"SELECT DISTINCT label FROM {schema}.tmp_b ORDER BY 1\".format(schema=postclass_schema)\n",
    "    labels_list=list(pd.read_sql(distinctlabelquery, db)['label'])\n",
    "    # Crosstab query argument\n",
    "    crosstabquery=\"SELECT seg, label, percent_border FROM {schema}.tmp_b ORDER  BY 1,2\".format(schema=postclass_schema)\n",
    "    # Declaration of colums for the pivot table\n",
    "    columns=[\"seg_id INTEGER\",]\n",
    "    [columns.append(\"prop_{label} NUMERIC\".format(label=label)) for label in labels_list]\n",
    "    # Queries to exectue\n",
    "    queries=[]\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.pivot_prop_label\".format(schema=postclass_schema))\n",
    "    queries.append(\"CREATE TABLE {schema}.pivot_prop_label AS(\\\n",
    "    SELECT * FROM crosstab('{crosstabquery}', '{distinctlabelquery}') \\\n",
    "    AS ct ({columns_declaration}))\".format(schema=postclass_schema,\n",
    "                                           crosstabquery=crosstabquery,\n",
    "                                           distinctlabelquery=distinctlabelquery,\n",
    "                                           columns_declaration=','.join(columns)))\n",
    "    # Create a list of with queries to update each column\n",
    "    [queries.append(\"UPDATE {schema}.pivot_prop_label SET prop_{label}=0 \\\n",
    "    WHERE prop_{label} is null\".format(schema=postclass_schema,label=label)) for label in labels_list]\n",
    "    # Execute the queries \n",
    "    cur.execute(\";\".join(queries))\n",
    "    # Make the changes to the database persistent\n",
    "    db.commit()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Function executed in 27 minutes and 2.9 seconds\n",
      "Currently the labels are 11,13,14,20,30,31,41,51\n"
     ]
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Create pivot table with, for each segment, the shared proportion of border with each class\n",
    "pivoted_proportion_label(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print print_processing_time(begintime, \"Function executed in \")\n",
    "# Print the current labels\n",
    "print \"Currently the labels are %s\"%','.join([str(x) for x in labels_list])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seg_id</th>\n",
       "      <th>prop_11</th>\n",
       "      <th>prop_13</th>\n",
       "      <th>prop_14</th>\n",
       "      <th>prop_20</th>\n",
       "      <th>prop_30</th>\n",
       "      <th>prop_31</th>\n",
       "      <th>prop_41</th>\n",
       "      <th>prop_51</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>212066</td>\n",
       "      <td>0.089481</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.037063</td>\n",
       "      <td>0.158477</td>\n",
       "      <td>0.597578</td>\n",
       "      <td>0.015321</td>\n",
       "      <td>0.003350</td>\n",
       "      <td>0.098695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>840016</td>\n",
       "      <td>0.308514</td>\n",
       "      <td>0.002803</td>\n",
       "      <td>0.006833</td>\n",
       "      <td>0.217064</td>\n",
       "      <td>0.328136</td>\n",
       "      <td>0.031535</td>\n",
       "      <td>0.001051</td>\n",
       "      <td>0.104064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000775</td>\n",
       "      <td>0.046666</td>\n",
       "      <td>0.000156</td>\n",
       "      <td>0.004925</td>\n",
       "      <td>0.321387</td>\n",
       "      <td>0.581842</td>\n",
       "      <td>0.002814</td>\n",
       "      <td>0.001016</td>\n",
       "      <td>0.041194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1742402</td>\n",
       "      <td>0.147894</td>\n",
       "      <td>0.000831</td>\n",
       "      <td>0.065115</td>\n",
       "      <td>0.535883</td>\n",
       "      <td>0.170269</td>\n",
       "      <td>0.002355</td>\n",
       "      <td>0.010668</td>\n",
       "      <td>0.066985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1869174</td>\n",
       "      <td>0.039262</td>\n",
       "      <td>0.007487</td>\n",
       "      <td>0.072681</td>\n",
       "      <td>0.724434</td>\n",
       "      <td>0.004383</td>\n",
       "      <td>0.002557</td>\n",
       "      <td>0.011322</td>\n",
       "      <td>0.137874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1995575</td>\n",
       "      <td>0.290968</td>\n",
       "      <td>0.000139</td>\n",
       "      <td>0.005786</td>\n",
       "      <td>0.287172</td>\n",
       "      <td>0.142163</td>\n",
       "      <td>0.011041</td>\n",
       "      <td>0.003148</td>\n",
       "      <td>0.259582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2016753</td>\n",
       "      <td>0.456627</td>\n",
       "      <td>0.030522</td>\n",
       "      <td>0.159839</td>\n",
       "      <td>0.017269</td>\n",
       "      <td>0.120884</td>\n",
       "      <td>0.006426</td>\n",
       "      <td>0.010442</td>\n",
       "      <td>0.197992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2078706</td>\n",
       "      <td>0.287263</td>\n",
       "      <td>0.000202</td>\n",
       "      <td>0.012097</td>\n",
       "      <td>0.389505</td>\n",
       "      <td>0.220060</td>\n",
       "      <td>0.007080</td>\n",
       "      <td>0.003237</td>\n",
       "      <td>0.080555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2285172</td>\n",
       "      <td>0.160973</td>\n",
       "      <td>0.000719</td>\n",
       "      <td>0.011986</td>\n",
       "      <td>0.196332</td>\n",
       "      <td>0.500779</td>\n",
       "      <td>0.023792</td>\n",
       "      <td>0.003716</td>\n",
       "      <td>0.101702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2297679</td>\n",
       "      <td>0.256211</td>\n",
       "      <td>0.000204</td>\n",
       "      <td>0.001756</td>\n",
       "      <td>0.222686</td>\n",
       "      <td>0.173302</td>\n",
       "      <td>0.011251</td>\n",
       "      <td>0.001044</td>\n",
       "      <td>0.333545</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    seg_id   prop_11   prop_13   prop_14   prop_20   prop_30   prop_31  \\\n",
       "0   212066  0.089481  0.000000  0.037063  0.158477  0.597578  0.015321   \n",
       "1   840016  0.308514  0.002803  0.006833  0.217064  0.328136  0.031535   \n",
       "2  1000775  0.046666  0.000156  0.004925  0.321387  0.581842  0.002814   \n",
       "3  1742402  0.147894  0.000831  0.065115  0.535883  0.170269  0.002355   \n",
       "4  1869174  0.039262  0.007487  0.072681  0.724434  0.004383  0.002557   \n",
       "5  1995575  0.290968  0.000139  0.005786  0.287172  0.142163  0.011041   \n",
       "6  2016753  0.456627  0.030522  0.159839  0.017269  0.120884  0.006426   \n",
       "7  2078706  0.287263  0.000202  0.012097  0.389505  0.220060  0.007080   \n",
       "8  2285172  0.160973  0.000719  0.011986  0.196332  0.500779  0.023792   \n",
       "9  2297679  0.256211  0.000204  0.001756  0.222686  0.173302  0.011251   \n",
       "\n",
       "    prop_41   prop_51  \n",
       "0  0.003350  0.098695  \n",
       "1  0.001051  0.104064  \n",
       "2  0.001016  0.041194  \n",
       "3  0.010668  0.066985  \n",
       "4  0.011322  0.137874  \n",
       "5  0.003148  0.259582  \n",
       "6  0.010442  0.197992  \n",
       "7  0.003237  0.080555  \n",
       "8  0.003716  0.101702  \n",
       "9  0.001044  0.333545  "
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display_header(db, postclass_schema, \"pivot_prop_label\", nrow=10, subcolumns=\"*\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Join segment label and proportion of each neighboring label"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def join_segment_label(db, cur, postclass_schema):\n",
    "    # Queries to exectue\n",
    "    queries=[]\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.pivot_labeled\".format(schema=postclass_schema))\n",
    "    queries.append(\"CREATE TABLE {schema}.pivot_labeled AS(\\\n",
    "    SELECT a.seg, a.label, b.* \\\n",
    "    FROM {schema}.seglabel AS a LEFT JOIN {schema}.pivot_prop_label AS b \\\n",
    "    ON a.seg=b.seg_id)\".format(schema=postclass_schema))\n",
    "    queries.append(\"ALTER TABLE {schema}.pivot_labeled DROP COLUMN IF EXISTS seg_id\".format(schema=postclass_schema))\n",
    "    # Execute the queries \n",
    "    cur.execute(\";\".join(queries))\n",
    "    # Make the changes to the database persistent\n",
    "    db.commit()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Function executed in 3 minutes and 16.6 seconds'"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Join the label of each segment with the proportion of shared neighbors\n",
    "join_segment_label(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seg</th>\n",
       "      <th>label</th>\n",
       "      <th>prop_11</th>\n",
       "      <th>prop_13</th>\n",
       "      <th>prop_14</th>\n",
       "      <th>prop_20</th>\n",
       "      <th>prop_30</th>\n",
       "      <th>prop_31</th>\n",
       "      <th>prop_41</th>\n",
       "      <th>prop_51</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6703262</td>\n",
       "      <td>20</td>\n",
       "      <td>0.311113</td>\n",
       "      <td>0.002911</td>\n",
       "      <td>0.021885</td>\n",
       "      <td>0.147525</td>\n",
       "      <td>0.131463</td>\n",
       "      <td>0.022237</td>\n",
       "      <td>0.118412</td>\n",
       "      <td>0.244453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14481204</td>\n",
       "      <td>20</td>\n",
       "      <td>0.501986</td>\n",
       "      <td>0.005959</td>\n",
       "      <td>0.079455</td>\n",
       "      <td>0.093360</td>\n",
       "      <td>0.037457</td>\n",
       "      <td>0.017594</td>\n",
       "      <td>0.028377</td>\n",
       "      <td>0.235812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2772900</td>\n",
       "      <td>30</td>\n",
       "      <td>0.101768</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.116077</td>\n",
       "      <td>0.169614</td>\n",
       "      <td>0.485691</td>\n",
       "      <td>0.006270</td>\n",
       "      <td>0.022187</td>\n",
       "      <td>0.098392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4427336</td>\n",
       "      <td>51</td>\n",
       "      <td>0.384328</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.108209</td>\n",
       "      <td>0.100746</td>\n",
       "      <td>0.134328</td>\n",
       "      <td>0.029851</td>\n",
       "      <td>0.138060</td>\n",
       "      <td>0.104478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5472210</td>\n",
       "      <td>20</td>\n",
       "      <td>0.279019</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000144</td>\n",
       "      <td>0.248738</td>\n",
       "      <td>0.182985</td>\n",
       "      <td>0.011319</td>\n",
       "      <td>0.000144</td>\n",
       "      <td>0.277650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7105191</td>\n",
       "      <td>20</td>\n",
       "      <td>0.374344</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000146</td>\n",
       "      <td>0.310591</td>\n",
       "      <td>0.131152</td>\n",
       "      <td>0.005301</td>\n",
       "      <td>0.005301</td>\n",
       "      <td>0.173167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7923682</td>\n",
       "      <td>20</td>\n",
       "      <td>0.096874</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.005120</td>\n",
       "      <td>0.270143</td>\n",
       "      <td>0.532606</td>\n",
       "      <td>0.019536</td>\n",
       "      <td>0.001617</td>\n",
       "      <td>0.074104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8128087</td>\n",
       "      <td>30</td>\n",
       "      <td>0.094444</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.177778</td>\n",
       "      <td>0.037037</td>\n",
       "      <td>0.337037</td>\n",
       "      <td>0.288889</td>\n",
       "      <td>0.044444</td>\n",
       "      <td>0.020370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10154979</td>\n",
       "      <td>20</td>\n",
       "      <td>0.230594</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.039633</td>\n",
       "      <td>0.280088</td>\n",
       "      <td>0.245512</td>\n",
       "      <td>0.036852</td>\n",
       "      <td>0.007775</td>\n",
       "      <td>0.159545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10533266</td>\n",
       "      <td>20</td>\n",
       "      <td>0.413819</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.071244</td>\n",
       "      <td>0.133677</td>\n",
       "      <td>0.113260</td>\n",
       "      <td>0.038147</td>\n",
       "      <td>0.006233</td>\n",
       "      <td>0.223619</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        seg  label   prop_11   prop_13   prop_14   prop_20   prop_30  \\\n",
       "0   6703262     20  0.311113  0.002911  0.021885  0.147525  0.131463   \n",
       "1  14481204     20  0.501986  0.005959  0.079455  0.093360  0.037457   \n",
       "2   2772900     30  0.101768  0.000000  0.116077  0.169614  0.485691   \n",
       "3   4427336     51  0.384328  0.000000  0.108209  0.100746  0.134328   \n",
       "4   5472210     20  0.279019  0.000000  0.000144  0.248738  0.182985   \n",
       "5   7105191     20  0.374344  0.000000  0.000146  0.310591  0.131152   \n",
       "6   7923682     20  0.096874  0.000000  0.005120  0.270143  0.532606   \n",
       "7   8128087     30  0.094444  0.000000  0.177778  0.037037  0.337037   \n",
       "8  10154979     20  0.230594  0.000000  0.039633  0.280088  0.245512   \n",
       "9  10533266     20  0.413819  0.000000  0.071244  0.133677  0.113260   \n",
       "\n",
       "    prop_31   prop_41   prop_51  \n",
       "0  0.022237  0.118412  0.244453  \n",
       "1  0.017594  0.028377  0.235812  \n",
       "2  0.006270  0.022187  0.098392  \n",
       "3  0.029851  0.138060  0.104478  \n",
       "4  0.011319  0.000144  0.277650  \n",
       "5  0.005301  0.005301  0.173167  \n",
       "6  0.019536  0.001617  0.074104  \n",
       "7  0.288889  0.044444  0.020370  \n",
       "8  0.036852  0.007775  0.159545  \n",
       "9  0.038147  0.006233  0.223619  "
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display_header(db, postclass_schema, \"pivot_labeled\", nrow=10, subcolumns=\"*\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get the label of class sharing the largest common border, the second largest and third largest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def add_larger_neighboring_labels(db, cur, postclass_schema):\n",
    "    # The trick was found here: https://stackoverflow.com/questions/8436919/second-maximum-and-minimum-values\n",
    "    # Get the largest, second largest and third largest value\n",
    "    queries=[]\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.tmp_C\".format(schema=postclass_schema))\n",
    "    queries.append(\"CREATE TABLE {schema}.tmp_C AS(\\\n",
    "    SELECT  seg AS seg_id,\\\n",
    "    max(case when rnDesc = 1 then percent_border end) as First_prop,\\\n",
    "    max(case when rnDesc = 2 then percent_border end) as Second_prop,\\\n",
    "    max(case when rnDesc = 3 then percent_border end) as Third_prop \\\n",
    "    from(select seg, percent_border, row_number() over (partition by seg order by percent_border desc) as rnDesc \\\n",
    "        FROM {schema}.tmp_B) as SubQueryAlias \\\n",
    "    GROUP BY seg)\".format(schema=postclass_schema))\n",
    "    # Join to existing table\n",
    "    subquery=\"SELECT * FROM {schema}.pivot_labeled AS a \\\n",
    "    LEFT JOIN {schema}.tmp_C AS b ON a.seg=b.seg_id\".format(schema=postclass_schema)\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.tmp_D\".format(schema=postclass_schema))\n",
    "    queries.append(\"CREATE TABLE {schema}.tmp_D AS({subquery})\".format(schema=postclass_schema,subquery=subquery))\n",
    "    queries.append(\"ALTER TABLE {schema}.tmp_D DROP COLUMN IF EXISTS seg_id\".format(schema=postclass_schema))\n",
    "    # Get the label of the largest, second and thirds largest neighboring class\n",
    "    queries.append(\"ALTER TABLE {schema}.tmp_D ADD COLUMN first_label integer\".format(schema=postclass_schema))\n",
    "    queries.append(\"ALTER TABLE {schema}.tmp_D ADD COLUMN second_label integer\".format(schema=postclass_schema))\n",
    "    queries.append(\"ALTER TABLE {schema}.tmp_D ADD COLUMN third_label integer\".format(schema=postclass_schema))\n",
    "    for label in labels_list:\n",
    "        queries.append(\"UPDATE {schema}.tmp_D SET \\\n",
    "        first_label={label} WHERE first_prop=prop_{label}\".format(schema=postclass_schema,label=label))\n",
    "        queries.append(\"UPDATE {schema}.tmp_D SET \\\n",
    "        second_label={label} WHERE second_prop=prop_{label}\".format(schema=postclass_schema,label=label))\n",
    "        queries.append(\"UPDATE {schema}.tmp_D SET \\\n",
    "        third_label={label} WHERE third_prop=prop_{label}\".format(schema=postclass_schema,label=label))\n",
    "    queries.append(\"ALTER TABLE {schema}.tmp_D DROP COLUMN IF EXISTS first_prop\".format(schema=postclass_schema))\n",
    "    queries.append(\"ALTER TABLE {schema}.tmp_D DROP COLUMN IF EXISTS second_prop\".format(schema=postclass_schema))\n",
    "    queries.append(\"ALTER TABLE {schema}.tmp_D DROP COLUMN IF EXISTS third_prop\".format(schema=postclass_schema))\n",
    "    # Execute the queries \n",
    "    cur.execute(\";\".join(queries))\n",
    "    # Make the changes to the database persistent\n",
    "    db.commit()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Function executed in 40 minutes and 15.4 seconds'"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Add largest neighboring labels\n",
    "add_larger_neighboring_labels(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seg</th>\n",
       "      <th>label</th>\n",
       "      <th>prop_11</th>\n",
       "      <th>prop_13</th>\n",
       "      <th>prop_14</th>\n",
       "      <th>prop_20</th>\n",
       "      <th>prop_30</th>\n",
       "      <th>prop_31</th>\n",
       "      <th>prop_41</th>\n",
       "      <th>prop_51</th>\n",
       "      <th>first_label</th>\n",
       "      <th>second_label</th>\n",
       "      <th>third_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.320000</td>\n",
       "      <td>0.680000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.936073</td>\n",
       "      <td>0.063927</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.605263</td>\n",
       "      <td>0.394737</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.866667</td>\n",
       "      <td>0.133333</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   seg  label  prop_11  prop_13  prop_14  prop_20   prop_30   prop_31  \\\n",
       "0    1     30        0        0        0        0  1.000000  0.000000   \n",
       "1    2     30        0        0        0        0  0.320000  0.680000   \n",
       "2    3     30        0        0        0        0  0.500000  0.500000   \n",
       "3    4     31        0        0        0        0  1.000000  0.000000   \n",
       "4    5     31        0        0        0        0  0.936073  0.063927   \n",
       "5    6     30        0        0        0        0  0.605263  0.394737   \n",
       "6    7     30        0        0        0        0  1.000000  0.000000   \n",
       "7    8     30        0        0        0        0  0.000000  1.000000   \n",
       "8    9     31        0        0        0        0  0.866667  0.133333   \n",
       "9   10     31        0        0        0        0  1.000000  0.000000   \n",
       "\n",
       "   prop_41  prop_51  first_label  second_label third_label  \n",
       "0        0        0           30           NaN        None  \n",
       "1        0        0           31            30        None  \n",
       "2        0        0           31            31        None  \n",
       "3        0        0           30           NaN        None  \n",
       "4        0        0           30            31        None  \n",
       "5        0        0           30            31        None  \n",
       "6        0        0           30           NaN        None  \n",
       "7        0        0           31           NaN        None  \n",
       "8        0        0           30            31        None  \n",
       "9        0        0           30           NaN        None  "
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display_header(db, postclass_schema, \"tmp_D\", nrow=10, subcolumns=\"*\", orderby=\"seg\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Join segments stats (ndvi, etc...)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def join_segment_stats(db, cur, postclass_schema):\n",
    "    # Queries to exectue\n",
    "    queries=[]\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.pivot_final\".format(schema=postclass_schema))\n",
    "    queries.append(\"CREATE TABLE {schema}.pivot_final AS(SELECT a.*, b.* FROM {schema}.tmp_D AS a \\\n",
    "    LEFT JOIN {schema}.segstats AS b ON a.seg=b.cat)\".format(schema=postclass_schema))\n",
    "    queries.append(\"ALTER TABLE {schema}.pivot_final DROP COLUMN IF EXISTS cat\".format(schema=postclass_schema))\n",
    "    queries.append(\"ALTER TABLE {schema}.pivot_final DROP COLUMN IF EXISTS key_value\".format(schema=postclass_schema))\n",
    "\n",
    "    # Execute the queries \n",
    "    cur.execute(\";\".join(queries))\n",
    "    # Make the changes to the database persistent\n",
    "    db.commit() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Function executed in 1 hours and 42 minutes and 2.5 seconds'"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Join segment statistics (ndvi, etc)\n",
    "join_segment_stats(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seg</th>\n",
       "      <th>label</th>\n",
       "      <th>prop_11</th>\n",
       "      <th>prop_13</th>\n",
       "      <th>prop_14</th>\n",
       "      <th>prop_20</th>\n",
       "      <th>prop_30</th>\n",
       "      <th>prop_31</th>\n",
       "      <th>prop_41</th>\n",
       "      <th>prop_51</th>\n",
       "      <th>...</th>\n",
       "      <th>ndsm_perc_90</th>\n",
       "      <th>ndvi_min</th>\n",
       "      <th>ndvi_max</th>\n",
       "      <th>ndvi_range</th>\n",
       "      <th>ndvi_mean</th>\n",
       "      <th>ndvi_stddev</th>\n",
       "      <th>ndvi_median</th>\n",
       "      <th>ndvi_first_quart</th>\n",
       "      <th>ndvi_third_quart</th>\n",
       "      <th>ndvi_perc_90</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2279587</td>\n",
       "      <td>11</td>\n",
       "      <td>1</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>0</td>\n",
       "      <td>...</td>\n",
       "      <td>8.208470</td>\n",
       "      <td>0.085793</td>\n",
       "      <td>0.143600</td>\n",
       "      <td>0.057807</td>\n",
       "      <td>0.118692</td>\n",
       "      <td>0.011966</td>\n",
       "      <td>0.118252</td>\n",
       "      <td>0.110083</td>\n",
       "      <td>0.127182</td>\n",
       "      <td>0.134959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3167671</td>\n",
       "      <td>11</td>\n",
       "      <td>1</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>0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.175200</td>\n",
       "      <td>0.042071</td>\n",
       "      <td>0.166833</td>\n",
       "      <td>0.124762</td>\n",
       "      <td>0.116146</td>\n",
       "      <td>0.039958</td>\n",
       "      <td>0.136159</td>\n",
       "      <td>0.071031</td>\n",
       "      <td>0.142066</td>\n",
       "      <td>0.152216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6739654</td>\n",
       "      <td>11</td>\n",
       "      <td>1</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>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.488740</td>\n",
       "      <td>0.020408</td>\n",
       "      <td>0.070211</td>\n",
       "      <td>0.049803</td>\n",
       "      <td>0.046398</td>\n",
       "      <td>0.013263</td>\n",
       "      <td>0.048702</td>\n",
       "      <td>0.035842</td>\n",
       "      <td>0.054671</td>\n",
       "      <td>0.062069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7665608</td>\n",
       "      <td>11</td>\n",
       "      <td>1</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>0</td>\n",
       "      <td>...</td>\n",
       "      <td>5.381320</td>\n",
       "      <td>0.043326</td>\n",
       "      <td>0.083444</td>\n",
       "      <td>0.040118</td>\n",
       "      <td>0.060147</td>\n",
       "      <td>0.010970</td>\n",
       "      <td>0.059570</td>\n",
       "      <td>0.049482</td>\n",
       "      <td>0.068301</td>\n",
       "      <td>0.075518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9449848</td>\n",
       "      <td>11</td>\n",
       "      <td>1</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>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.028898</td>\n",
       "      <td>0.069866</td>\n",
       "      <td>0.098764</td>\n",
       "      <td>0.006065</td>\n",
       "      <td>0.029762</td>\n",
       "      <td>-0.005685</td>\n",
       "      <td>-0.016046</td>\n",
       "      <td>0.013766</td>\n",
       "      <td>0.049053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10183974</td>\n",
       "      <td>51</td>\n",
       "      <td>1</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>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.818150</td>\n",
       "      <td>0.047619</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.152381</td>\n",
       "      <td>0.129301</td>\n",
       "      <td>0.043028</td>\n",
       "      <td>0.129604</td>\n",
       "      <td>0.087156</td>\n",
       "      <td>0.160391</td>\n",
       "      <td>0.188929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>14401875</td>\n",
       "      <td>30</td>\n",
       "      <td>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>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.217736</td>\n",
       "      <td>0.419247</td>\n",
       "      <td>0.201511</td>\n",
       "      <td>0.285727</td>\n",
       "      <td>0.047389</td>\n",
       "      <td>0.273543</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.304641</td>\n",
       "      <td>0.366050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>503944</td>\n",
       "      <td>11</td>\n",
       "      <td>1</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>0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.043550</td>\n",
       "      <td>0.030979</td>\n",
       "      <td>0.061438</td>\n",
       "      <td>0.030459</td>\n",
       "      <td>0.044067</td>\n",
       "      <td>0.007709</td>\n",
       "      <td>0.043695</td>\n",
       "      <td>0.037783</td>\n",
       "      <td>0.048827</td>\n",
       "      <td>0.050398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>744702</td>\n",
       "      <td>11</td>\n",
       "      <td>1</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>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.306976</td>\n",
       "      <td>0.003455</td>\n",
       "      <td>0.044706</td>\n",
       "      <td>0.041250</td>\n",
       "      <td>0.024256</td>\n",
       "      <td>0.010481</td>\n",
       "      <td>0.022871</td>\n",
       "      <td>0.019670</td>\n",
       "      <td>0.031963</td>\n",
       "      <td>0.038491</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1047260</td>\n",
       "      <td>11</td>\n",
       "      <td>1</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>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3.976100</td>\n",
       "      <td>0.005629</td>\n",
       "      <td>0.108815</td>\n",
       "      <td>0.103187</td>\n",
       "      <td>0.045228</td>\n",
       "      <td>0.021409</td>\n",
       "      <td>0.042983</td>\n",
       "      <td>0.028133</td>\n",
       "      <td>0.051715</td>\n",
       "      <td>0.068363</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 72 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        seg  label  prop_11  prop_13  prop_14  prop_20  prop_30  prop_31  \\\n",
       "0   2279587     11        1        0        0        0        0        0   \n",
       "1   3167671     11        1        0        0        0        0        0   \n",
       "2   6739654     11        1        0        0        0        0        0   \n",
       "3   7665608     11        1        0        0        0        0        0   \n",
       "4   9449848     11        1        0        0        0        0        0   \n",
       "5  10183974     51        1        0        0        0        0        0   \n",
       "6  14401875     30        0        0        0        1        0        0   \n",
       "7    503944     11        1        0        0        0        0        0   \n",
       "8    744702     11        1        0        0        0        0        0   \n",
       "9   1047260     11        1        0        0        0        0        0   \n",
       "\n",
       "   prop_41  prop_51      ...       ndsm_perc_90  ndvi_min  ndvi_max  \\\n",
       "0        0        0      ...           8.208470  0.085793  0.143600   \n",
       "1        0        0      ...           6.175200  0.042071  0.166833   \n",
       "2        0        0      ...           3.488740  0.020408  0.070211   \n",
       "3        0        0      ...           5.381320  0.043326  0.083444   \n",
       "4        0        0      ...           0.000000 -0.028898  0.069866   \n",
       "5        0        0      ...           2.818150  0.047619  0.200000   \n",
       "6        0        0      ...           0.000000  0.217736  0.419247   \n",
       "7        0        0      ...           1.043550  0.030979  0.061438   \n",
       "8        0        0      ...           0.306976  0.003455  0.044706   \n",
       "9        0        0      ...           3.976100  0.005629  0.108815   \n",
       "\n",
       "   ndvi_range  ndvi_mean  ndvi_stddev  ndvi_median  ndvi_first_quart  \\\n",
       "0    0.057807   0.118692     0.011966     0.118252          0.110083   \n",
       "1    0.124762   0.116146     0.039958     0.136159          0.071031   \n",
       "2    0.049803   0.046398     0.013263     0.048702          0.035842   \n",
       "3    0.040118   0.060147     0.010970     0.059570          0.049482   \n",
       "4    0.098764   0.006065     0.029762    -0.005685         -0.016046   \n",
       "5    0.152381   0.129301     0.043028     0.129604          0.087156   \n",
       "6    0.201511   0.285727     0.047389     0.273543          0.250000   \n",
       "7    0.030459   0.044067     0.007709     0.043695          0.037783   \n",
       "8    0.041250   0.024256     0.010481     0.022871          0.019670   \n",
       "9    0.103187   0.045228     0.021409     0.042983          0.028133   \n",
       "\n",
       "   ndvi_third_quart  ndvi_perc_90  \n",
       "0          0.127182      0.134959  \n",
       "1          0.142066      0.152216  \n",
       "2          0.054671      0.062069  \n",
       "3          0.068301      0.075518  \n",
       "4          0.013766      0.049053  \n",
       "5          0.160391      0.188929  \n",
       "6          0.304641      0.366050  \n",
       "7          0.048827      0.050398  \n",
       "8          0.031963      0.038491  \n",
       "9          0.051715      0.068363  \n",
       "\n",
       "[10 rows x 72 columns]"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display_header(db, postclass_schema, \"pivot_final\", nrow=10, subcolumns=\"*\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Drop intermediate tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def drop_intermediate_tables(db, cur, postclass_schema):\n",
    "    # Queries to exectue\n",
    "    queries=[]\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.tmp_a\".format(schema=postclass_schema))\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.tmp_b\".format(schema=postclass_schema))\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.tmp_c\".format(schema=postclass_schema))\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.tmp_d\".format(schema=postclass_schema))\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.pivot_labeled\".format(schema=postclass_schema))\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.pivot_prop_label\".format(schema=postclass_schema))\n",
    "    #queries.append(\"DROP TABLE IF EXISTS {schema}.rmatrix\".format(schema=postclass_schema))\n",
    "    #queries.append(\"DROP TABLE IF EXISTS {schema}.seglabel\".format(schema=postclass_schema))\n",
    "    #queries.append(\"DROP TABLE IF EXISTS {schema}.segstats\".format(schema=postclass_schema))\n",
    "    # Execute the queries \n",
    "    cur.execute(\";\".join(queries))\n",
    "    # Make the changes to the database persistent\n",
    "    db.commit()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Function executed in 5.5 seconds'"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "# Drop intermediate tables\n",
    "drop_intermediate_tables(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Close cursor and communication with the database\n",
    "cur.close()\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Postclassification using neighborhood information"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Set label of different classes**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Set the label of the 'Building' class\n",
    "building='11'\n",
    "# Set the label of the 'Asphalt' class\n",
    "asphalt='13'\n",
    "# Set the label of the 'Swimmingpools' class\n",
    "swimming='14'\n",
    "# Set the label of the 'baresoils' class\n",
    "baresoil='20'\n",
    "# Set the label of the 'lowvegetation' class\n",
    "lowveg='30'\n",
    "# Set the label of the 'Trees' class\n",
    "tree='31'\n",
    "# Set the label of the 'Water' class\n",
    "water='41'\n",
    "# Set the label of the 'Shadow' class\n",
    "shadow='51'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Define color rules for the classification maps**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Define color table. Replace with the RGB values of wanted colors of each class\n",
    "color_rule=\"11  227:26:28\"+\"\\n\"\n",
    "color_rule+=\"13  94:221:227\"+\"\\n\"\n",
    "color_rule+=\"14  102:102:102\"+\"\\n\"\n",
    "color_rule+=\"20  211:217:173\"+\"\\n\"\n",
    "color_rule+=\"30  29:220:0\"+\"\\n\"\n",
    "color_rule+=\"31  0:128:0\"+\"\\n\"\n",
    "color_rule+=\"41  30:30:192\"+\"\\n\"\n",
    "color_rule+=\"51  0:0:0\"+\"\\n\"\n",
    "\n",
    "## Create a temporary 'color_table.txt' file\n",
    "color_table=gscript.tempfile()+\"color_table\" # Define the csv output file name\n",
    "f = open(color_table, 'w')\n",
    "f.write(color_rule)\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Display average and stddev of several features for each label "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Those informations could be interesting to find differenciating factors between classes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to an existing database\n",
    "db=pg.connect(database=dbname, user=dbuser, password=dbpassword, host=host)\n",
    "# Open a cursor to perform database operations\n",
    "cur=db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>label</th>\n",
       "      <th>avg_ndvi_mean</th>\n",
       "      <th>stddev_ndvi_mean</th>\n",
       "      <th>avg_ndsm_mean</th>\n",
       "      <th>stddev_ndsm_mean</th>\n",
       "      <th>avg_area</th>\n",
       "      <th>stddev_area</th>\n",
       "      <th>avg_compact_circle</th>\n",
       "      <th>stddev_compact_circle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>0.079585</td>\n",
       "      <td>0.059265</td>\n",
       "      <td>1.880391</td>\n",
       "      <td>2.333930</td>\n",
       "      <td>69.170468</td>\n",
       "      <td>132.840903</td>\n",
       "      <td>1.833690</td>\n",
       "      <td>0.475243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13</td>\n",
       "      <td>0.098284</td>\n",
       "      <td>0.111601</td>\n",
       "      <td>2.182515</td>\n",
       "      <td>4.248495</td>\n",
       "      <td>36.101360</td>\n",
       "      <td>48.056340</td>\n",
       "      <td>1.813988</td>\n",
       "      <td>0.395350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14</td>\n",
       "      <td>0.102496</td>\n",
       "      <td>0.021776</td>\n",
       "      <td>0.884265</td>\n",
       "      <td>1.152726</td>\n",
       "      <td>5236.574111</td>\n",
       "      <td>12596.652857</td>\n",
       "      <td>5.797828</td>\n",
       "      <td>3.130823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20</td>\n",
       "      <td>0.134389</td>\n",
       "      <td>0.029387</td>\n",
       "      <td>0.229702</td>\n",
       "      <td>0.733738</td>\n",
       "      <td>353.405330</td>\n",
       "      <td>3275.885308</td>\n",
       "      <td>2.220613</td>\n",
       "      <td>1.344611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30</td>\n",
       "      <td>0.304110</td>\n",
       "      <td>0.102666</td>\n",
       "      <td>0.323107</td>\n",
       "      <td>1.973740</td>\n",
       "      <td>212.267874</td>\n",
       "      <td>1953.683808</td>\n",
       "      <td>2.061421</td>\n",
       "      <td>0.856451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>31</td>\n",
       "      <td>0.499104</td>\n",
       "      <td>0.073196</td>\n",
       "      <td>1.319287</td>\n",
       "      <td>2.644986</td>\n",
       "      <td>115.049884</td>\n",
       "      <td>1084.784538</td>\n",
       "      <td>1.855181</td>\n",
       "      <td>0.657884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>41</td>\n",
       "      <td>0.012363</td>\n",
       "      <td>0.136272</td>\n",
       "      <td>0.291869</td>\n",
       "      <td>2.430213</td>\n",
       "      <td>263.203473</td>\n",
       "      <td>4547.273678</td>\n",
       "      <td>2.152730</td>\n",
       "      <td>0.886278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>51</td>\n",
       "      <td>0.288338</td>\n",
       "      <td>0.107886</td>\n",
       "      <td>1.238033</td>\n",
       "      <td>1.608353</td>\n",
       "      <td>41.710799</td>\n",
       "      <td>49.332363</td>\n",
       "      <td>2.020866</td>\n",
       "      <td>0.517381</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   label  avg_ndvi_mean  stddev_ndvi_mean  avg_ndsm_mean  stddev_ndsm_mean  \\\n",
       "0     11       0.079585          0.059265       1.880391          2.333930   \n",
       "1     13       0.098284          0.111601       2.182515          4.248495   \n",
       "2     14       0.102496          0.021776       0.884265          1.152726   \n",
       "3     20       0.134389          0.029387       0.229702          0.733738   \n",
       "4     30       0.304110          0.102666       0.323107          1.973740   \n",
       "5     31       0.499104          0.073196       1.319287          2.644986   \n",
       "6     41       0.012363          0.136272       0.291869          2.430213   \n",
       "7     51       0.288338          0.107886       1.238033          1.608353   \n",
       "\n",
       "      avg_area   stddev_area  avg_compact_circle  stddev_compact_circle  \n",
       "0    69.170468    132.840903            1.833690               0.475243  \n",
       "1    36.101360     48.056340            1.813988               0.395350  \n",
       "2  5236.574111  12596.652857            5.797828               3.130823  \n",
       "3   353.405330   3275.885308            2.220613               1.344611  \n",
       "4   212.267874   1953.683808            2.061421               0.856451  \n",
       "5   115.049884   1084.784538            1.855181               0.657884  \n",
       "6   263.203473   4547.273678            2.152730               0.886278  \n",
       "7    41.710799     49.332363            2.020866               0.517381  "
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Query\n",
    "query=\"SELECT label, avg(ndvi_mean) AS avg_ndvi_mean, stddev(ndvi_mean) AS stddev_ndvi_mean, \\\n",
    "avg(ndsm_mean) AS avg_ndsm_mean, stddev(ndsm_mean) AS stddev_ndsm_mean, \\\n",
    "avg(area) AS avg_area, stddev(area) AS stddev_area, \\\n",
    "avg(compact_circle) AS avg_compact_circle, stddev(compact_circle) AS stddev_compact_circle \\\n",
    "FROM {schema}.pivot_final GROUP BY label ORDER BY label\".format(schema=postclass_schema)\n",
    "# Execute query through panda\n",
    "df=pd.read_sql(query, db)\n",
    "# Show dataframe\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define functions for postclassifiction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def create_postclassif_labels_table(db, cur, postclass_schema):\n",
    "    # Queries to exectue\n",
    "    queries=[]\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.postclassif_labels\".format(schema=postclass_schema))\n",
    "    queries.append(\"CREATE TABLE {schema}.postclassif_labels AS(\\\n",
    "    SELECT * FROM {schema}.pivot_final)\".format(schema=postclass_schema))\n",
    "    queries.append(\"ALTER TABLE {schema}.postclassif_labels ADD COLUMN reclass_label integer\".format(schema=postclass_schema))\n",
    "    # Execute the queries \n",
    "    cur.execute(\";\".join(queries))\n",
    "    # Make the changes to the database persistent\n",
    "    db.commit()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def fill_nonupdated_labels(db, cur, postclass_schema):\n",
    "    # For segments which have not been reclassified yet, attribute their original label (no reclassification)\n",
    "    query=\"UPDATE {schema}.postclassif_labels \\\n",
    "    SET reclass_label=label \\\n",
    "    WHERE reclass_label IS NULL\".format(schema=postclass_schema)\n",
    "    # Execute the queries \n",
    "    cur.execute(query)\n",
    "    # Make the changes to the database persistent\n",
    "    db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def update_seglabel_with_postclassif_labels(db, cur, postclass_schema):\n",
    "    ## Saving current time for processing time management\n",
    "    begintime=time.time()\n",
    "    # Queries to exectue\n",
    "    queries=[]\n",
    "    queries.append(\"DROP TABLE IF EXISTS {schema}.seglabel\".format(schema=postclass_schema))\n",
    "    queries.append(\"CREATE TABLE {schema}.seglabel AS(\\\n",
    "    SELECT seg, reclass_label AS label FROM {schema}.postclassif_labels)\".format(schema=postclass_schema))\n",
    "    # Execute the queries \n",
    "    cur.execute(\";\".join(queries))\n",
    "    # Make the changes to the database persistent\n",
    "    db.commit()\n",
    "    # Print processing time\n",
    "    print print_processing_time(begintime, \"Table <seglabel> updated with new values in \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def create_reclassified_maps(db, cur, postclass_schema, postclassif_map, colorfile):\n",
    "    #### Save 'seg_id' and the corresponding label for in a list\n",
    "    query=\"SELECT seg AS seg_id, label  FROM %s.seglabel\"%postclass_schema\n",
    "    df=pd.read_sql(query, db)\n",
    "    # Save seg_id of objects in a list\n",
    "    listsegid=list(df['seg_id'])\n",
    "    # Save predictions at level_2 in a list\n",
    "    listpredict=list(df['label'])\n",
    "    if len(listsegid) <> len(listpredict):\n",
    "        sys.exit('WARNING: lenght of lists containing segid and prediction are not the same. Please check before continue')\n",
    "    else:\n",
    "        nrows=len(listsegid)\n",
    "        print \"Start reclassifying %s segment\"%nrows\n",
    "    \n",
    "    ##### Reclassify raster\n",
    "    ## Create a temporary 'reclass_rule' file\n",
    "    temprulecsv=gscript.tempfile()+\"_reclass_rules\" # Define the csv output file name\n",
    "    f = open(temprulecsv, 'w')\n",
    "    # Write rules in the csv file\n",
    "    [f.write(\"%s=%s\\n\"%(listsegid[i],listpredict[i])) for i in range(0,nrows-1)]\n",
    "    f.write(\"*\")\n",
    "    f.write(\"=\")\n",
    "    f.write(\"NULL\")\n",
    "    f.close()\n",
    "    print \"Reclass rule file created\"\n",
    "    \n",
    "    ## Reclass segments raster layer\n",
    "    gscript.run_command('g.region', overwrite=True, raster=segment_layer)\n",
    "    gscript.run_command('r.reclass', overwrite=True, input=segment_layer, \n",
    "                      output=postclassif_map, rules=temprulecsv)\n",
    "    os.remove(temprulecsv)\n",
    "    print \"Temporary reclassified map created\"\n",
    "\n",
    "    #### Making hard copy of the reclassified map\n",
    "    # Create the same raster with r.mapcalc\n",
    "    formula=\"{mapname}_temp={mapname}\".format(mapname=postclassif_map)\n",
    "    gscript.mapcalc(formula, overwrite=True)\n",
    "    # Rename the new raster with the name of the original one (will be overwrited)\n",
    "    renameformula=\"{mapname}_temp,{mapname}\".format(mapname=postclassif_map)\n",
    "    gscript.run_command('g.rename', overwrite=True, raster=renameformula)\n",
    "    print \"'Hard copy' of the reclassified map created\"\n",
    "    ## Apply the new color table\n",
    "    gscript.run_command('r.colors', map=postclassif_map, rules=colorfile)\n",
    "    print \"Color scheme applyied to the reclassified map\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def create_diffmap(classification_layer, postclassif_map, class_value):\n",
    "    diff_map_name=\"diff_%s\"%class_value\n",
    "    if \"%s@%s\"%(diff_map_name,gscript.gisenv()['MAPSET']) in gscript.list_strings('rast'):\n",
    "        diff_map_name=\"diff_%s_2\"%class_value\n",
    "    formula=\"{diff_map_name}=if({map1}=={class_value} && {map2}!={class_value},1,0)\".format(diff_map_name=diff_map_name,\n",
    "                                                                                            class_value=class_value,\n",
    "                                                                                            map1=classification_layer,\n",
    "                                                                                            map2=postclassif_map)\n",
    "    gscript.mapcalc(formula, overwrite=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 1 - Reclassifying  'Buildings' false-positive"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Isolated buildings segments (which do not touch any other building segment)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to an existing database\n",
    "db=pg.connect(database=dbname, user=dbuser, password=dbpassword, host=host)\n",
    "# Open a cursor to perform database operations\n",
    "cur=db.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a table for reclassification"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Function executed in 5 minutes and 57.6 seconds'"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Create table for postclassification\n",
    "create_postclassif_labels_table(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reclassification rules"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "\n",
    "# Query  \n",
    "query=\"UPDATE {schema}.postclassif_labels \\\n",
    "SET reclass_label=(CASE \\\n",
    "WHEN area<25 THEN \\\n",
    "    CASE \\\n",
    "    WHEN first_label!={shadow} THEN first_label \\\n",
    "    ELSE \\\n",
    "        CASE \\\n",
    "        WHEN second_label IS NULL THEN first_label \\\n",
    "        ELSE second_label \\\n",
    "        END \\\n",
    "    END \\\n",
    "ELSE \\\n",
    "    CASE \\\n",
    "    WHEN prop_{shadow}=0 AND ndvi_mean>0.2 THEN first_label \\\n",
    "    END \\\n",
    "END) \\\n",
    "WHERE label={building} AND prop_{building}=0\".format(schema=postclass_schema,building=building,\n",
    "                                                     shadow=shadow)\n",
    "\n",
    "# Execute the queries \n",
    "cur.execute(query)\n",
    "# Make the changes to the database persistent\n",
    "db.commit()  \n",
    "\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Queries executed in 7 minutes and 32.6 seconds'"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "\n",
    "# Query  \n",
    "query=\"UPDATE {schema}.postclassif_labels \\\n",
    "SET reclass_label=(CASE \\\n",
    "WHEN area<25 THEN \\\n",
    "    CASE \\\n",
    "    WHEN first_label NOT IN ({shadow},{water}) THEN first_label \\\n",
    "    ELSE \\\n",
    "        CASE \\\n",
    "        WHEN second_label NOT IN ({shadow},{water}) THEN second_label \\\n",
    "        ELSE \\\n",
    "            CASE \\\n",
    "            WHEN third_label IS NOT NULL THEN third_label \\\n",
    "            ELSE \\\n",
    "                CASE \\\n",
    "                WHEN ndvi_mean>0.30 THEN \\\n",
    "                                        CASE \\\n",
    "                                        WHEN ndsm_mean<1.5 THEN {lowveg} \\\n",
    "                                        ELSE {tree} \\\n",
    "                                        END \\\n",
    "                ELSE {baresoil} \\\n",
    "                END \\\n",
    "            END \\\n",
    "        END \\\n",
    "    END \\\n",
    "ELSE \\\n",
    "    CASE \\\n",
    "    WHEN prop_{shadow}=0 AND prop_{water}=0 AND ndvi_mean>0.2 THEN first_label \\\n",
    "    END \\\n",
    "END) \\\n",
    "WHERE label={building} AND prop_{building}=0\".format(schema=postclass_schema,building=building,\n",
    "                                                     shadow=shadow,water=water,lowveg=lowveg,\n",
    "                                                     tree=tree,baresoil=baresoil)\n",
    "\n",
    "# Execute the queries \n",
    "cur.execute(query)\n",
    "# Make the changes to the database persistent\n",
    "db.commit()  \n",
    "\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Attribute current label to segment which didn't get reclassified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Queries executed in 20 minutes and 9.0 seconds'"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Fill non updated labels with original label\n",
    "fill_nonupdated_labels(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Update the segment labels (seglabel table) with the updated values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table <seglabel> updated with new values in 4 minutes and 27.5 seconds\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Queries executed in 4 minutes and 27.5 seconds'"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Update the labels with new labels\n",
    "update_seglabel_with_postclassif_labels(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reclassify the segmentation raster to get the new map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Start reclassifying 15775526 segment\n",
      "Reclass rule file created\n",
      "Temporary reclassified map created\n",
      "'Hard copy' of the reclassified map created\n",
      "Color scheme applyied to the reclassified map\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Reclassified map created in 4 minutes and 23.2 seconds'"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Reclassify the segmentation raster\n",
    "create_reclassified_maps(db, cur, postclass_schema, \"postclassif_map_1_fp_buildings\", color_table)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Reclassified map created in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create difference map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Will enable quick highlight of segment whose label have changed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Create difference map between two classified raster\n",
    "create_diffmap(classification_layer, \"postclassif_map_1_fp_buildings\", building)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 2 - Reclassifying 'Shadows'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to an existing database\n",
    "db=pg.connect(database=dbname, user=dbuser, password=dbpassword, host=host)\n",
    "# Open a cursor to perform database operations\n",
    "cur=db.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Re-compute the neighboring information again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Function executed in 43 minutes and 26.9 seconds\n",
      "Currently the labels are 11,13,14,20,30,31,41,51\n"
     ]
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Create table with neighbors label and border count\n",
    "neighbors_label_bordercount(db, cur, postclass_schema)\n",
    "## Create table with neighbors label and cumulated border count (total for each type of neighboring label)\n",
    "neighbors_label_cumulatedbordercount(db, cur, postclass_schema)\n",
    "## Create pivot table with, for each segment, the shared proportion of border with each class\n",
    "pivoted_proportion_label(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print print_processing_time(begintime, \"Function executed in \")\n",
    "# Print the current labels\n",
    "print \"Currently the labels are %s\"%','.join([str(x) for x in labels_list])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Function executed in 2 hours and 8 minutes and 58.9 seconds'"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Join the label of each segment with the proportion of shared neighbors\n",
    "join_segment_label(db, cur, postclass_schema)\n",
    "## Add largest neighboring labels\n",
    "add_larger_neighboring_labels(db, cur, postclass_schema)\n",
    "## Join segment statistics (ndvi, etc)\n",
    "join_segment_stats(db, cur, postclass_schema)\n",
    "# Drop intermediate tables\n",
    "drop_intermediate_tables(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a table for reclassification"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Function executed in 5 minutes and 38.5 seconds'"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Create table for postclassification\n",
    "create_postclassif_labels_table(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reclassification rules"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "\n",
    "# Shadows reclassification \n",
    "query=\"UPDATE {schema}.postclassif_labels \\\n",
    "SET reclass_label=(CASE \\\n",
    "WHEN ndvi_mean>0.30 THEN \\\n",
    "    CASE \\\n",
    "    WHEN ndsm_mean<1.5 THEN {lowveg} \\\n",
    "    ELSE {tree} \\\n",
    "    END \\\n",
    "ELSE {baresoil} \\\n",
    "END) \\\n",
    "WHERE label={shadow}\".format(schema=postclass_schema,shadow=shadow,\n",
    "                             lowveg=lowveg,tree=tree,baresoil=baresoil)\n",
    "\n",
    "# Execute the queries \n",
    "cur.execute(query)\n",
    "# Make the changes to the database persistent\n",
    "db.commit()  \n",
    "\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Queries executed in 12 minutes and 49.1 seconds'"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "\n",
    "# Shadows reclassification \n",
    "query=\"UPDATE {schema}.postclassif_labels \\\n",
    "SET reclass_label=(CASE \\\n",
    "WHEN ndvi_mean>0.30 THEN \\\n",
    "    CASE \\\n",
    "    WHEN ndsm_mean<1.5 THEN {lowveg} \\\n",
    "    ELSE {tree} \\\n",
    "    END \\\n",
    "ELSE \\\n",
    "    CASE \\\n",
    "    WHEN prop_{water}=1 THEN {water} \\\n",
    "    ELSE \\\n",
    "        CASE \\\n",
    "        WHEN third_label IS NULL THEN \\\n",
    "                                    CASE \\\n",
    "                                    WHEN (first_label={water} AND second_label={shadow}) \\\n",
    "                                        OR (first_label={shadow} AND second_label={water}) THEN {water} \\\n",
    "                                    ELSE {baresoil} \\\n",
    "                                    END \\\n",
    "        ELSE {baresoil} \\\n",
    "        END \\\n",
    "    END \\\n",
    " \\\n",
    "END) \\\n",
    "WHERE label={shadow}\".format(schema=postclass_schema,shadow=shadow,\n",
    "                             lowveg=lowveg,tree=tree,baresoil=baresoil,water=water)\n",
    "\n",
    "# Execute the queries \n",
    "cur.execute(query)\n",
    "# Make the changes to the database persistent\n",
    "db.commit()  \n",
    "\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Attribute current label to segment which didn't get reclassified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Queries executed in 14 minutes and 58.0 seconds'"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Fill non updated labels with original label\n",
    "fill_nonupdated_labels(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Update the segment labels (seglabel table) with the updated values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table <seglabel> updated with new values in 6 minutes and 44.5 seconds\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Queries executed in 6 minutes and 44.5 seconds'"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Update the labels with new labels\n",
    "update_seglabel_with_postclassif_labels(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reclassify the segmentation raster to get the new map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Start reclassifying 15775526 segment\n",
      "Reclass rule file created\n",
      "Temporary reclassified map created\n",
      "'Hard copy' of the reclassified map created\n",
      "Color scheme applyied to the reclassified map\n",
      "Reclassified map created in 4 minutes and 18.9 seconds\n"
     ]
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Reclassify the segmentation raster\n",
    "create_reclassified_maps(db, cur, postclass_schema, \"postclassif_map_2_shadow\", color_table)\n",
    "# Print processing time\n",
    "print print_processing_time(begintime, \"Reclassified map created in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create difference map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Will enable quick highlight of segment whose label have changed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Create difference map between two classified raster\n",
    "create_diffmap(classification_layer, \"postclassif_map_2_shadow\", shadow)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 3 - Reclassifying 'Water' false-positive"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Re-compute the neighboring information again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Function executed in 37 minutes and 29.3 seconds\n",
      "Currently the labels are 11,13,14,20,30,31,41\n"
     ]
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Create table with neighbors label and border count\n",
    "neighbors_label_bordercount(db, cur, postclass_schema)\n",
    "## Create table with neighbors label and cumulated border count (total for each type of neighboring label)\n",
    "neighbors_label_cumulatedbordercount(db, cur, postclass_schema)\n",
    "## Create pivot table with, for each segment, the shared proportion of border with each class\n",
    "pivoted_proportion_label(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print print_processing_time(begintime, \"Function executed in \")\n",
    "# Print the current labels\n",
    "print \"Currently the labels are %s\"%','.join([str(x) for x in labels_list])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Function executed in 2 hours and 7 minutes and 58.8 seconds\n"
     ]
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Join the label of each segment with the proportion of shared neighbors\n",
    "join_segment_label(db, cur, postclass_schema)\n",
    "## Add largest neighboring labels\n",
    "add_larger_neighboring_labels(db, cur, postclass_schema)\n",
    "## Join segment statistics (ndvi, etc)\n",
    "join_segment_stats(db, cur, postclass_schema)\n",
    "# Drop intermediate tables\n",
    "drop_intermediate_tables(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a table for reclassification"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to an existing database\n",
    "db=pg.connect(database=dbname, user=dbuser, password=dbpassword, host=host)\n",
    "# Open a cursor to perform database operations\n",
    "cur=db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Function executed in 6 minutes and 37.0 seconds'"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Create table for postclassification\n",
    "create_postclassif_labels_table(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reclassification rules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Queries executed in 3 minutes and 3.3 seconds'"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "\n",
    "# Query  \n",
    "query=\"UPDATE {schema}.postclassif_labels \\\n",
    "SET reclass_label=(\\\n",
    "CASE \\\n",
    "WHEN first_label NOT IN ({building},{water}) THEN first_label \\\n",
    "ELSE \\\n",
    "    CASE \\\n",
    "    WHEN second_label IS NOT NULL AND second_label NOT IN ({building},{water}) THEN second_label\\\n",
    "    ELSE \\\n",
    "        CASE \\\n",
    "        WHEN ndvi_mean <=0.05 THEN {building} \\\n",
    "        WHEN ndvi_mean >0.05 AND ndvi_mean <=0.3 THEN {baresoil} \\\n",
    "        WHEN ndvi_mean >0.3 THEN {lowveg} \\\n",
    "        END\\\n",
    "    END \\\n",
    "END) \\\n",
    "WHERE label={water} AND (\\\n",
    "                        (area<=300 AND prop_{water}=0) \\\n",
    "                        OR \\\n",
    "                        (area<100 AND prop_{water}<1))\".format(schema=postclass_schema,building=building,\n",
    "                                                             baresoil=baresoil,lowveg=lowveg,water=water)\n",
    "\n",
    "# Execute the queries \n",
    "cur.execute(query)\n",
    "# Make the changes to the database persistent\n",
    "db.commit()  \n",
    "\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Attribute current label to segment which didn't get reclassified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Queries executed in 21 minutes and 14.3 seconds'"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Fill non updated labels with original label\n",
    "fill_nonupdated_labels(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Update the segment labels (seglabel table) with the updated values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table <seglabel> updated with new values in 4 minutes and 1.2 seconds\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Queries executed in 4 minutes and 1.2 seconds'"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Update the labels with new labels\n",
    "update_seglabel_with_postclassif_labels(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reclassify the segmentation raster to get the new map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Start reclassifying 15775526 segment\n",
      "Reclass rule file created\n",
      "Temporary reclassified map created\n",
      "'Hard copy' of the reclassified map created\n",
      "Color scheme applyied to the reclassified map\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Reclassified map created in 4 minutes and 36.6 seconds'"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Reclassify the segmentation raster\n",
    "create_reclassified_maps(db, cur, postclass_schema, \"postclassif_map_3_water\", color_table)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Reclassified map created in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create difference map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Will enable quick highlight of segment whose label have changed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Create difference map between two classified raster\n",
    "create_diffmap(classification_layer, \"postclassif_map_3_water\", water)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 4 - Reclassifying 'Buildings' false-positive touching water"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Re-compute the neighboring information again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Function executed in 39 minutes and 27.0 seconds\n",
      "Currently the labels are 11,13,14,20,30,31,41\n"
     ]
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Create table with neighbors label and border count\n",
    "neighbors_label_bordercount(db, cur, postclass_schema)\n",
    "## Create table with neighbors label and cumulated border count (total for each type of neighboring label)\n",
    "neighbors_label_cumulatedbordercount(db, cur, postclass_schema)\n",
    "## Create pivot table with, for each segment, the shared proportion of border with each class\n",
    "pivoted_proportion_label(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print print_processing_time(begintime, \"Function executed in \")\n",
    "# Print the current labels\n",
    "print \"Currently the labels are %s\"%','.join([str(x) for x in labels_list])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Function executed in 2 hours and 22 minutes and 26.0 seconds'"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Join the label of each segment with the proportion of shared neighbors\n",
    "join_segment_label(db, cur, postclass_schema)\n",
    "## Add largest neighboring labels\n",
    "add_larger_neighboring_labels(db, cur, postclass_schema)\n",
    "## Join segment statistics (ndvi, etc)\n",
    "join_segment_stats(db, cur, postclass_schema)\n",
    "# Drop intermediate tables\n",
    "drop_intermediate_tables(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a table for reclassification"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to an existing database\n",
    "db=pg.connect(database=dbname, user=dbuser, password=dbpassword, host=host)\n",
    "# Open a cursor to perform database operations\n",
    "cur=db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Function executed in 5 minutes and 32.6 seconds'"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Create table for postclassification\n",
    "create_postclassif_labels_table(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reclassification rules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Queries executed in 26.9 seconds'"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "\n",
    "# Query  \n",
    "query=\"UPDATE {schema}.postclassif_labels \\\n",
    "SET reclass_label={baresoil} \\\n",
    "WHERE label={building} AND (\\\n",
    "                        first_label={water} \\\n",
    "                        OR \\\n",
    "                        second_label={water})\".format(schema=postclass_schema,building=building,\n",
    "                                                             baresoil=baresoil,water=water)\n",
    "\n",
    "# Execute the queries \n",
    "cur.execute(query)\n",
    "# Make the changes to the database persistent\n",
    "db.commit()  \n",
    "\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Attribute current label to segment which didn't get reclassified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Queries executed in 18 minutes and 51.6 seconds'"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Fill non updated labels with original label\n",
    "fill_nonupdated_labels(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Update the segment labels (seglabel table) with the updated values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table <seglabel> updated with new values in 3 minutes and 12.5 seconds\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Queries executed in 3 minutes and 12.5 seconds'"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Update the labels with new labels\n",
    "update_seglabel_with_postclassif_labels(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reclassify the segmentation raster to get the new map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Start reclassifying 15775526 segment\n",
      "Reclass rule file created\n",
      "Temporary reclassified map created\n",
      "'Hard copy' of the reclassified map created\n",
      "Color scheme applyied to the reclassified map\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Reclassified map created in 4 minutes and 28.9 seconds'"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Reclassify the segmentation raster\n",
    "create_reclassified_maps(db, cur, postclass_schema, \"postclassif_map_4_buildingsbaresoil\", color_table)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Reclassified map created in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create difference map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Will enable quick highlight of segment whose label have changed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "## Create difference map between two classified raster\n",
    "create_diffmap(classification_layer, \"postclassif_map_4_buildingsbaresoil\", building)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 5 - Thematic refinement of 'Buildings' class into 'Low elevated building' and 'High elevated building'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Re-compute the neighboring information again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Function executed in 41 minutes and 50.9 seconds\n",
      "Currently the labels are 11,13,14,20,30,31,41\n"
     ]
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Create table with neighbors label and border count\n",
    "neighbors_label_bordercount(db, cur, postclass_schema)\n",
    "## Create table with neighbors label and cumulated border count (total for each type of neighboring label)\n",
    "neighbors_label_cumulatedbordercount(db, cur, postclass_schema)\n",
    "## Create pivot table with, for each segment, the shared proportion of border with each class\n",
    "pivoted_proportion_label(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print print_processing_time(begintime, \"Function executed in \")\n",
    "# Print the current labels\n",
    "print \"Currently the labels are %s\"%','.join([str(x) for x in labels_list])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Function executed in 2 hours and 11 minutes and 58.3 seconds'"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Join the label of each segment with the proportion of shared neighbors\n",
    "join_segment_label(db, cur, postclass_schema)\n",
    "## Add largest neighboring labels\n",
    "add_larger_neighboring_labels(db, cur, postclass_schema)\n",
    "## Join segment statistics (ndvi, etc)\n",
    "join_segment_stats(db, cur, postclass_schema)\n",
    "# Drop intermediate tables\n",
    "drop_intermediate_tables(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a table for reclassification"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to an existing database\n",
    "db=pg.connect(database=dbname, user=dbuser, password=dbpassword, host=host)\n",
    "# Open a cursor to perform database operations\n",
    "cur=db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Function executed in 5 minutes and 17.7 seconds'"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Create table for postclassification\n",
    "create_postclassif_labels_table(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reclassification rules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Queries executed in 11 minutes and 28.7 seconds'"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "\n",
    "# Query  \n",
    "query=\"UPDATE {schema}.postclassif_labels \\\n",
    "SET reclass_label=(\\\n",
    "CASE WHEN ndsm_mean < 3 THEN 112 ELSE 111 END) \\\n",
    "WHERE label={building}\".format(schema=postclass_schema,building=building)\n",
    "\n",
    "# Execute the queries \n",
    "cur.execute(query)\n",
    "# Make the changes to the database persistent\n",
    "db.commit()  \n",
    "\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Attribute current label to segment which didn't get reclassified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Queries executed in 12 minutes and 47.5 seconds'"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Fill non updated labels with original label\n",
    "fill_nonupdated_labels(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Update the segment labels (seglabel table) with the updated values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table <seglabel> updated with new values in 5 minutes and 44.2 seconds\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Queries executed in 5 minutes and 44.2 seconds'"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Update the labels with new labels\n",
    "update_seglabel_with_postclassif_labels(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Queries executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reclassify the segmentation raster to get the new map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Define color rules for the classification maps**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Define color table. Replace with the RGB values of wanted colors of each class\n",
    "color_rule=\"13  94:221:227\"+\"\\n\"\n",
    "color_rule+=\"14  102:102:102\"+\"\\n\"\n",
    "color_rule+=\"20  211:217:173\"+\"\\n\"\n",
    "color_rule+=\"30  29:220:0\"+\"\\n\"\n",
    "color_rule+=\"31  0:128:0\"+\"\\n\"\n",
    "color_rule+=\"41  30:30:192\"+\"\\n\"\n",
    "color_rule+=\"51  0:0:0\"+\"\\n\"\n",
    "color_rule+=\"111  227:26:28\"+\"\\n\"\n",
    "color_rule+=\"112  255:132:56\"+\"\\n\"\n",
    "\n",
    "## Create a temporary 'color_table.txt' file\n",
    "color_table2=gscript.tempfile()+\"color_table2\" # Define the csv output file name\n",
    "f = open(color_table2, 'w')\n",
    "f.write(color_rule)\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Reclassify**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Start reclassifying 15775526 segment\n",
      "Reclass rule file created\n",
      "Temporary reclassified map created\n",
      "'Hard copy' of the reclassified map created\n",
      "Color scheme applyied to the reclassified map\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Reclassified map created in 4 minutes and 38.0 seconds'"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Reclassify the segmentation raster\n",
    "create_reclassified_maps(db, cur, postclass_schema, \"postclassif_map_5_slitbuildings\", color_table2)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Reclassified map created in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Apply modal filter to smooth map rendering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**3x3 modal moving window filtering**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Define window size\n",
    "winsize=3\n",
    "## Define name prefix for outputmap\n",
    "prefix=\"postclassif_map_final_modalfilter\"\n",
    "## Define computational region\n",
    "gscript.run_command('g.region', overwrite=True, raster=classification_layer)\n",
    "## Apply modal filter in 3x3 moving window\n",
    "gscript.run_command('r.neighbors', overwrite=True, \n",
    "                    input=\"postclassif_map_5_slitbuildings\", output=prefix+str(winsize), \n",
    "                    method='mode', size=winsize)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**5x5 modal moving window filtering**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Define window size\n",
    "winsize=5\n",
    "## Define name prefix for outputmap\n",
    "prefix=\"postclassif_map_final_modalfilter\"\n",
    "## Define computational region\n",
    "gscript.run_command('g.region', overwrite=True, raster=classification_layer)\n",
    "## Apply modal filter in 3x3 moving window\n",
    "gscript.run_command('r.neighbors', overwrite=True, \n",
    "                    input=\"postclassif_map_5_slitbuildings\", output=prefix+str(winsize), \n",
    "                    method='mode', size=winsize)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import geometry of segments from GRASS into PostGIS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run the following cell only if you want to import the geometry of segments in PostGIS. Please notice it could be very time-consuming ! Please set up a limited computational region to be sure the number of object is not more than 10.000 !"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Define a maximum number of segment to be exported\n",
    "maxnumber=250000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Check how many segments are in the current computational region\n",
    "id_list=gscript.read_command('r.describe', flags='n1d', map=segment_layer).split(\"\\n\")[:-1]\n",
    "print len(id_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to an existing database\n",
    "db=pg.connect(database=dbname, user=dbuser, password=dbpassword, host=host)\n",
    "# Open a cursor to perform database operations\n",
    "cur=db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Get a list with segment id\n",
    "id_list=gscript.read_command('r.describe', flags='n1d', map=segment_layer).split(\"\\n\")[:-1]\n",
    "\n",
    "# Export in PostGIS if limited number of segments\n",
    "if len(id_list)<maxnumber:\n",
    "    print \"Going to import %s segments in PostGis database <%s>\"%(len(id_list),dbname)\n",
    "    begintime_1=time.time()\n",
    "    # Convert raster to vector (no attribute table created to make it faster)\n",
    "    gscript.run_command('r.to.vect', overwrite=True, flags='vt', \n",
    "                        input=segment_layer, output='segment_geom', type='area')\n",
    "    print print_processing_time(begintime_1, \"Segmentation raster converted in vector layer in \")\n",
    "    # Export vector geometry to Postgis -Use flag -a to append to existing table\n",
    "    begintime_2=time.time()\n",
    "    gscript.run_command('v.out.ogr', overwrite=True, flags='m', input='segment_geom',\n",
    "                        output=\"PG:dbname=%s\"%dbname, output_layer=\"%s.segment_geom\"%postclass_schema, \n",
    "                       format=\"PostgreSQL\")    \n",
    "    print print_processing_time(begintime_2, \"Geometry of segments imported in PostGIS in \")\n",
    "    # Remove the vector layer\n",
    "    gscript.run_command('g.remove', flags='f', type='vector', name='segment_geom@%s'%gscript.gisenv()['MAPSET'])\n",
    "    # Print total processing time\n",
    "    print print_processing_time(begintime_1, \"Total time for import : \")\n",
    "\n",
    "else:\n",
    "    print \"Number of segments (%s) too important in the current computational region\"%len(id_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Re-compute the neighboring information again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Create table with neighbors label and border count\n",
    "neighbors_label_bordercount(db, cur, postclass_schema)\n",
    "## Create table with neighbors label and cumulated border count (total for each type of neighboring label)\n",
    "neighbors_label_cumulatedbordercount(db, cur, postclass_schema)\n",
    "## Create pivot table with, for each segment, the shared proportion of border with each class\n",
    "pivoted_proportion_label(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")\n",
    "# Print the current labels\n",
    "print \"Currently the labels are %s\"%','.join([str(x) for x in labels_list])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Join the label of each segment with the proportion of shared neighbors\n",
    "join_segment_label(db, cur, postclass_schema)\n",
    "## Add largest neighboring labels\n",
    "add_larger_neighboring_labels(db, cur, postclass_schema)\n",
    "## Join segment statistics (ndvi, etc)\n",
    "join_segment_stats(db, cur, postclass_schema)\n",
    "# Drop intermediate tables\n",
    "drop_intermediate_tables(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a table for reclassification"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Connect to an existing database\n",
    "db=pg.connect(database=dbname, user=dbuser, password=dbpassword, host=host)\n",
    "# Open a cursor to perform database operations\n",
    "cur=db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "## Saving current time for processing time management\n",
    "begintime=time.time()\n",
    "## Create table for postclassification\n",
    "create_postclassif_labels_table(db, cur, postclass_schema)\n",
    "# Print processing time\n",
    "print_processing_time(begintime, \"Function executed in \")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Join geometry with statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#### Join the segment geometry to the postclassification labels\n",
    "# Queries to exectue\n",
    "queries=[]\n",
    "queries.append(\"DROP TABLE IF EXISTS {schema}.segment_reclass\".format(schema=postclass_schema))\n",
    "queries.append(\"CREATE TABLE {schema}.segment_reclass AS(\\\n",
    "SELECT cat AS seg_id, wkb_geometry AS the_geom, b.* \\\n",
    "FROM {schema}.segment_geom AS a LEFT JOIN {schema}.postclassif_labels AS b \\\n",
    "ON a.cat=b.seg)\".format(schema=postclass_schema))\n",
    "queries.append(\"ALTER TABLE {schema}.segment_reclass DROP COLUMN IF EXISTS seg\".format(schema=postclass_schema))\n",
    "queries.append(\"ALTER TABLE {schema}.segment_reclass DROP CONSTRAINT IF EXISTS segment_reclass_pkey\".format(schema=postclass_schema))\n",
    "queries.append(\"ALTER TABLE {schema}.segment_reclass ADD PRIMARY KEY (seg_id)\".format(schema=postclass_schema))\n",
    "\n",
    "# Execute the queries \n",
    "cur.execute(\";\".join(queries))\n",
    "# Make the changes to the database persistent\n",
    "db.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Close cursor and communication with the database\n",
    "cur.close()\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export classifications in .tif format and .zip archive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## Define output folder \n",
    "outputfolder=\"/media/tais/My_Book_1/MAUPP/Traitement/Ouagadougou/Segmentation_fullAOI_localapproach/Results/POSTCLASSIF\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Raster layer(s) to be exported: \n",
      "\n",
      "rf_classif_l2b@classif_E\n",
      "postclassif_map_1_fp_buildings@postclassification\n",
      "postclassif_map_2_shadow@postclassification\n",
      "postclassif_map_3_water@postclassification\n",
      "postclassif_map_4_buildingsbaresoil@postclassification\n",
      "postclassif_map_5_slitbuildings@postclassification\n",
      "postclassif_map_final_modalfilter3@postclassification\n",
      "postclassif_map_final_modalfilter5@postclassification\n"
     ]
    }
   ],
   "source": [
    "## Define list of raster to be exported\n",
    "exportrasterlist=[]\n",
    "exportrasterlist.append(classification_layer)\n",
    "[exportrasterlist.append(a) for a in gscript.list_strings('rast', pattern='postclassif_*')]\n",
    "print \"Raster layer(s) to be exported: \\n\\n\"+\"\\n\".join(exportrasterlist)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "for classif in exportrasterlist:\n",
    "    gscript.run_command('g.region', raster=classif)\n",
    "    outputfile=os.path.join(outputfolder,classif.split(\"@\")[0]+\".tif\")\n",
    "    outputzip=os.path.join(outputfolder,classif.split(\"@\")[0]+\".zip\")\n",
    "    gscript.run_command('r.out.gdal', overwrite=True, input=classif, \n",
    "                      output=outputfile, format='GTiff')\n",
    "    try:\n",
    "        with zipfile.ZipFile(outputzip, 'w', zipfile.ZIP_DEFLATED) as myzip:\n",
    "            myzip.write(outputfile,basename(outputfile))\n",
    "            myzip.close()\n",
    "    except zipfile.LargeZipFile:\n",
    "        with zipfile.ZipFile(outputzip, 'w', zipfile.ZIP_DEFLATED, allowZip64=True) as myzip:\n",
    "            myzip.write(outputfile,basename(outputfile))\n",
    "            myzip.close()   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Export color tables for archive**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with zipfile.ZipFile(os.path.join(outputfolder,\"color_table_classif_ouaga.zip\"), 'w',zipfile.ZIP_DEFLATED) as myzip:\n",
    "    myzip.write(color_table,basename(color_table))\n",
    "    myzip.write(color_table2,basename(color_table2))\n",
    "    myzip.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-**"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}