2_citydb/2a_buildings.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import buildings data to the 3DCityDB\n",
    "\n",
    "This notebook shows how the **IntegrCiTy Data Access Layer** (DAL) can be used to import data to the 3DCityDB. \n",
    "In this specific example, **geometric data** (2D footprints) and **energy data** (gas consumption) of buildings is **extracted** from separate sources (shapefiles, CSV data), **consolidated** and **stored** in the database.\n",
    "\n",
    "## Extracting the data\n",
    "\n",
    "In real life, collecting data is often a long and laborious task. \n",
    "And once you have all the data you need, it is typically fragmented over various sources with various formats. \n",
    "In this example, the data is spread over several files:\n",
    "\n",
    "1. **Shapefile data**: A common format for GIS-related data is the shapefile format. In this example, the shapefile contains the 2D footprints of buildings and information which type of energy carrier they use for heating.\n",
    "\n",
    "2. **CSV data**: Files containing comma-separated values (CSV) are probably the most common format for data exchange. In this example, the CSV data file contains gas consumption profiles of buildings.\n",
    "\n",
    "In this example, both data sets use the same ID to refer to the same building (e.g., '*building175*'), which makes it possible to easily link the data from both sets. \n",
    "In real-life applications it might take another pre-processing step to link the available data.\n",
    "\n",
    "### Extracting shapefile data\n",
    "\n",
    "The image below visualizes the buildings data from the shapefile (with the help of [QGIS](https://www.qgis.org/)).\n",
    "\n",
    "<img src=\"./img/buildings_sf.png\" style=\"height:12cm\">\n",
    "\n",
    "For the purpose of this example, the [Python Shapefile Library (PyShp)](https://pypi.org/project/pyshp/) is used to exatract this data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import shapefile, os\n",
    "\n",
    "file_path = os.path.join( os.getcwd(), '..', '1_data', 'shapefiles', 'buildings' )\n",
    "buildings_sf = shapefile.Reader( file_path )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Take a look which data attributes are available (compare with figure above):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('DeletionFlag', 'C', 1, 0),\n",
       " ['name', 'C', 50, 0],\n",
       " ['e_carrier', 'C', 50, 0],\n",
       " ['area', 'N', 10, 0]]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "buildings_sf.fields"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extracting CSV data\n",
    "\n",
    "The image below visualizes a subset of the buildings data from the CSV file.\n",
    "\n",
    "<img src=\"./img/buildings_df.png\" style=\"height:9cm\">\n",
    "\n",
    "For the purpose of this example, the [pandas library](https://pandas.pydata.org/) is used to extract this data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "file_path = os.path.join( os.getcwd(), '..', '1_data', 'profiles', 'gas_consumption.csv' )\n",
    "buildings_df = pd.read_csv( file_path )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Take a look at the imported data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>building2</th>\n",
       "      <th>building5</th>\n",
       "      <th>building6</th>\n",
       "      <th>building7</th>\n",
       "      <th>building9</th>\n",
       "      <th>building14</th>\n",
       "      <th>building21</th>\n",
       "      <th>building23</th>\n",
       "      <th>building24</th>\n",
       "      <th>...</th>\n",
       "      <th>building216</th>\n",
       "      <th>building223</th>\n",
       "      <th>building224</th>\n",
       "      <th>building225</th>\n",
       "      <th>building226</th>\n",
       "      <th>building228</th>\n",
       "      <th>building229</th>\n",
       "      <th>building231</th>\n",
       "      <th>building233</th>\n",
       "      <th>building234</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>10.577184</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.220092</td>\n",
       "      <td>1.447523</td>\n",
       "      <td>11.582883</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>6.376088</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>4.428656</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>2.937936</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.175237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>10.577184</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.220092</td>\n",
       "      <td>1.447523</td>\n",
       "      <td>11.582883</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>6.376088</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>4.428656</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>2.937936</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.175237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>10.577184</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.220092</td>\n",
       "      <td>1.447523</td>\n",
       "      <td>11.582883</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>6.376088</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>4.428656</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>2.937936</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.175237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>10.577184</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.444720</td>\n",
       "      <td>1.447523</td>\n",
       "      <td>11.582883</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>6.376088</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>4.428656</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>2.937936</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.175237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>10.577184</td>\n",
       "      <td>2.310804</td>\n",
       "      <td>5.054876</td>\n",
       "      <td>6.444720</td>\n",
       "      <td>1.552272</td>\n",
       "      <td>11.582883</td>\n",
       "      <td>4.476306</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>6.823016</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>4.752213</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>3.256838</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.401029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>10.577184</td>\n",
       "      <td>3.195349</td>\n",
       "      <td>4.861673</td>\n",
       "      <td>7.156042</td>\n",
       "      <td>1.447523</td>\n",
       "      <td>11.582883</td>\n",
       "      <td>4.305217</td>\n",
       "      <td>8.345579</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>7.679628</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>12.143091</td>\n",
       "      <td>5.695922</td>\n",
       "      <td>9.822669</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>3.256838</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.175237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>13.823407</td>\n",
       "      <td>2.592297</td>\n",
       "      <td>7.277939</td>\n",
       "      <td>8.616123</td>\n",
       "      <td>1.790171</td>\n",
       "      <td>15.137764</td>\n",
       "      <td>6.444922</td>\n",
       "      <td>7.362105</td>\n",
       "      <td>25.760208</td>\n",
       "      <td>...</td>\n",
       "      <td>8.387264</td>\n",
       "      <td>6.164978</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>5.776811</td>\n",
       "      <td>9.251552</td>\n",
       "      <td>13.357576</td>\n",
       "      <td>6.658457</td>\n",
       "      <td>10.689723</td>\n",
       "      <td>14.211151</td>\n",
       "      <td>4.019203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>14.435379</td>\n",
       "      <td>3.219659</td>\n",
       "      <td>7.025382</td>\n",
       "      <td>9.177693</td>\n",
       "      <td>1.568938</td>\n",
       "      <td>15.807923</td>\n",
       "      <td>6.221272</td>\n",
       "      <td>8.236287</td>\n",
       "      <td>21.939775</td>\n",
       "      <td>...</td>\n",
       "      <td>10.174976</td>\n",
       "      <td>8.467039</td>\n",
       "      <td>9.745992</td>\n",
       "      <td>7.745118</td>\n",
       "      <td>10.113868</td>\n",
       "      <td>14.403957</td>\n",
       "      <td>4.638746</td>\n",
       "      <td>12.356139</td>\n",
       "      <td>13.562530</td>\n",
       "      <td>3.454724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>11.642130</td>\n",
       "      <td>2.228998</td>\n",
       "      <td>4.771232</td>\n",
       "      <td>6.856538</td>\n",
       "      <td>1.838626</td>\n",
       "      <td>12.749086</td>\n",
       "      <td>4.225127</td>\n",
       "      <td>7.172594</td>\n",
       "      <td>24.145747</td>\n",
       "      <td>...</td>\n",
       "      <td>6.376088</td>\n",
       "      <td>7.677295</td>\n",
       "      <td>12.460829</td>\n",
       "      <td>7.259782</td>\n",
       "      <td>9.976559</td>\n",
       "      <td>14.404251</td>\n",
       "      <td>3.735191</td>\n",
       "      <td>11.687654</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.796164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>10.785251</td>\n",
       "      <td>2.256756</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.294968</td>\n",
       "      <td>1.447523</td>\n",
       "      <td>11.810733</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>7.252033</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>6.376088</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>10.554402</td>\n",
       "      <td>5.156660</td>\n",
       "      <td>9.045200</td>\n",
       "      <td>10.941232</td>\n",
       "      <td>3.256838</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.175237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>13.062057</td>\n",
       "      <td>2.421372</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.220092</td>\n",
       "      <td>1.761770</td>\n",
       "      <td>14.304023</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>6.376088</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>6.019479</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>2.937936</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.175237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>11.642130</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.220092</td>\n",
       "      <td>1.499898</td>\n",
       "      <td>12.749086</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>6.376088</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>5.075770</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>2.937936</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.175237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>10.577184</td>\n",
       "      <td>2.310804</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.220092</td>\n",
       "      <td>1.552272</td>\n",
       "      <td>11.582883</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>6.376088</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>9.441950</td>\n",
       "      <td>4.617398</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>3.416289</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.401029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>11.642130</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>5.151123</td>\n",
       "      <td>7.118604</td>\n",
       "      <td>1.447523</td>\n",
       "      <td>12.749086</td>\n",
       "      <td>4.561537</td>\n",
       "      <td>6.968715</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>6.599552</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>4.617398</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>3.309988</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.457477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>10.577184</td>\n",
       "      <td>2.310804</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.220092</td>\n",
       "      <td>1.447523</td>\n",
       "      <td>11.582883</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>7.493408</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>9.918926</td>\n",
       "      <td>4.428656</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>2.991087</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.890244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>11.109657</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>5.098244</td>\n",
       "      <td>7.567860</td>\n",
       "      <td>1.447523</td>\n",
       "      <td>12.165985</td>\n",
       "      <td>4.514710</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>7.642384</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>9.465301</td>\n",
       "      <td>4.752213</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>4.213543</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.175237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>10.648333</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.669348</td>\n",
       "      <td>1.499898</td>\n",
       "      <td>11.660797</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>18.378164</td>\n",
       "      <td>...</td>\n",
       "      <td>6.823016</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>9.601188</td>\n",
       "      <td>4.428656</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>4.054093</td>\n",
       "      <td>12.749108</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>3.078404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>10.654611</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.781662</td>\n",
       "      <td>1.447523</td>\n",
       "      <td>11.667672</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.968715</td>\n",
       "      <td>19.056703</td>\n",
       "      <td>...</td>\n",
       "      <td>6.376088</td>\n",
       "      <td>6.724081</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>4.428656</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>2.937936</td>\n",
       "      <td>13.279835</td>\n",
       "      <td>14.388060</td>\n",
       "      <td>4.075651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>10.577184</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>9.439759</td>\n",
       "      <td>1.447523</td>\n",
       "      <td>11.582883</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>20.753051</td>\n",
       "      <td>...</td>\n",
       "      <td>8.796948</td>\n",
       "      <td>8.842334</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>5.911627</td>\n",
       "      <td>8.850833</td>\n",
       "      <td>11.261875</td>\n",
       "      <td>4.027517</td>\n",
       "      <td>10.237048</td>\n",
       "      <td>12.442061</td>\n",
       "      <td>2.513925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>11.642130</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>8.316619</td>\n",
       "      <td>1.463622</td>\n",
       "      <td>12.749086</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.923081</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>7.679628</td>\n",
       "      <td>7.359557</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>4.913992</td>\n",
       "      <td>8.850833</td>\n",
       "      <td>11.261875</td>\n",
       "      <td>3.602315</td>\n",
       "      <td>10.222175</td>\n",
       "      <td>11.161790</td>\n",
       "      <td>2.175237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20</td>\n",
       "      <td>15.191948</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.294968</td>\n",
       "      <td>1.470309</td>\n",
       "      <td>16.636429</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>19.395973</td>\n",
       "      <td>...</td>\n",
       "      <td>6.599552</td>\n",
       "      <td>6.167997</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>5.291475</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>3.416289</td>\n",
       "      <td>11.687654</td>\n",
       "      <td>14.699038</td>\n",
       "      <td>2.175237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>21</td>\n",
       "      <td>10.577184</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.444720</td>\n",
       "      <td>1.499898</td>\n",
       "      <td>11.582883</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>6.934748</td>\n",
       "      <td>6.406343</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>5.075770</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>2.937936</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>12.618970</td>\n",
       "      <td>2.288133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>22</td>\n",
       "      <td>10.577184</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.220092</td>\n",
       "      <td>1.447523</td>\n",
       "      <td>11.582883</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>7.754116</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>4.428656</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>3.097387</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.175237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>23</td>\n",
       "      <td>10.577184</td>\n",
       "      <td>2.200236</td>\n",
       "      <td>4.668470</td>\n",
       "      <td>6.220092</td>\n",
       "      <td>1.447523</td>\n",
       "      <td>11.582883</td>\n",
       "      <td>4.134127</td>\n",
       "      <td>6.772020</td>\n",
       "      <td>17.360355</td>\n",
       "      <td>...</td>\n",
       "      <td>6.376088</td>\n",
       "      <td>6.088605</td>\n",
       "      <td>9.283450</td>\n",
       "      <td>4.428656</td>\n",
       "      <td>8.656466</td>\n",
       "      <td>10.426213</td>\n",
       "      <td>2.937936</td>\n",
       "      <td>10.095473</td>\n",
       "      <td>11.026789</td>\n",
       "      <td>2.175237</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>24 rows × 124 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0  building2  building5  building6  building7  building9  \\\n",
       "0            0  10.577184   2.200236   4.668470   6.220092   1.447523   \n",
       "1            1  10.577184   2.200236   4.668470   6.220092   1.447523   \n",
       "2            2  10.577184   2.200236   4.668470   6.220092   1.447523   \n",
       "3            3  10.577184   2.200236   4.668470   6.444720   1.447523   \n",
       "4            4  10.577184   2.310804   5.054876   6.444720   1.552272   \n",
       "5            5  10.577184   3.195349   4.861673   7.156042   1.447523   \n",
       "6            6  13.823407   2.592297   7.277939   8.616123   1.790171   \n",
       "7            7  14.435379   3.219659   7.025382   9.177693   1.568938   \n",
       "8            8  11.642130   2.228998   4.771232   6.856538   1.838626   \n",
       "9            9  10.785251   2.256756   4.668470   6.294968   1.447523   \n",
       "10          10  13.062057   2.421372   4.668470   6.220092   1.761770   \n",
       "11          11  11.642130   2.200236   4.668470   6.220092   1.499898   \n",
       "12          12  10.577184   2.310804   4.668470   6.220092   1.552272   \n",
       "13          13  11.642130   2.200236   5.151123   7.118604   1.447523   \n",
       "14          14  10.577184   2.310804   4.668470   6.220092   1.447523   \n",
       "15          15  11.109657   2.200236   5.098244   7.567860   1.447523   \n",
       "16          16  10.648333   2.200236   4.668470   6.669348   1.499898   \n",
       "17          17  10.654611   2.200236   4.668470   6.781662   1.447523   \n",
       "18          18  10.577184   2.200236   4.668470   9.439759   1.447523   \n",
       "19          19  11.642130   2.200236   4.668470   8.316619   1.463622   \n",
       "20          20  15.191948   2.200236   4.668470   6.294968   1.470309   \n",
       "21          21  10.577184   2.200236   4.668470   6.444720   1.499898   \n",
       "22          22  10.577184   2.200236   4.668470   6.220092   1.447523   \n",
       "23          23  10.577184   2.200236   4.668470   6.220092   1.447523   \n",
       "\n",
       "    building14  building21  building23  building24  ...  building216  \\\n",
       "0    11.582883    4.134127    6.772020   17.360355  ...     6.376088   \n",
       "1    11.582883    4.134127    6.772020   17.360355  ...     6.376088   \n",
       "2    11.582883    4.134127    6.772020   17.360355  ...     6.376088   \n",
       "3    11.582883    4.134127    6.772020   17.360355  ...     6.376088   \n",
       "4    11.582883    4.476306    6.772020   17.360355  ...     6.823016   \n",
       "5    11.582883    4.305217    8.345579   17.360355  ...     7.679628   \n",
       "6    15.137764    6.444922    7.362105   25.760208  ...     8.387264   \n",
       "7    15.807923    6.221272    8.236287   21.939775  ...    10.174976   \n",
       "8    12.749086    4.225127    7.172594   24.145747  ...     6.376088   \n",
       "9    11.810733    4.134127    7.252033   17.360355  ...     6.376088   \n",
       "10   14.304023    4.134127    6.772020   17.360355  ...     6.376088   \n",
       "11   12.749086    4.134127    6.772020   17.360355  ...     6.376088   \n",
       "12   11.582883    4.134127    6.772020   17.360355  ...     6.376088   \n",
       "13   12.749086    4.561537    6.968715   17.360355  ...     6.599552   \n",
       "14   11.582883    4.134127    6.772020   17.360355  ...     7.493408   \n",
       "15   12.165985    4.514710    6.772020   17.360355  ...     7.642384   \n",
       "16   11.660797    4.134127    6.772020   18.378164  ...     6.823016   \n",
       "17   11.667672    4.134127    6.968715   19.056703  ...     6.376088   \n",
       "18   11.582883    4.134127    6.772020   20.753051  ...     8.796948   \n",
       "19   12.749086    4.134127    6.923081   17.360355  ...     7.679628   \n",
       "20   16.636429    4.134127    6.772020   19.395973  ...     6.599552   \n",
       "21   11.582883    4.134127    6.772020   17.360355  ...     6.934748   \n",
       "22   11.582883    4.134127    6.772020   17.360355  ...     7.754116   \n",
       "23   11.582883    4.134127    6.772020   17.360355  ...     6.376088   \n",
       "\n",
       "    building223  building224  building225  building226  building228  \\\n",
       "0      6.088605     9.283450     4.428656     8.656466    10.426213   \n",
       "1      6.088605     9.283450     4.428656     8.656466    10.426213   \n",
       "2      6.088605     9.283450     4.428656     8.656466    10.426213   \n",
       "3      6.088605     9.283450     4.428656     8.656466    10.426213   \n",
       "4      6.088605     9.283450     4.752213     8.656466    10.426213   \n",
       "5      6.088605    12.143091     5.695922     9.822669    10.426213   \n",
       "6      6.164978     9.283450     5.776811     9.251552    13.357576   \n",
       "7      8.467039     9.745992     7.745118    10.113868    14.403957   \n",
       "8      7.677295    12.460829     7.259782     9.976559    14.404251   \n",
       "9      6.088605    10.554402     5.156660     9.045200    10.941232   \n",
       "10     6.088605     9.283450     6.019479     8.656466    10.426213   \n",
       "11     6.088605     9.283450     5.075770     8.656466    10.426213   \n",
       "12     6.088605     9.441950     4.617398     8.656466    10.426213   \n",
       "13     6.088605     9.283450     4.617398     8.656466    10.426213   \n",
       "14     6.088605     9.918926     4.428656     8.656466    10.426213   \n",
       "15     6.088605     9.465301     4.752213     8.656466    10.426213   \n",
       "16     6.088605     9.601188     4.428656     8.656466    10.426213   \n",
       "17     6.724081     9.283450     4.428656     8.656466    10.426213   \n",
       "18     8.842334     9.283450     5.911627     8.850833    11.261875   \n",
       "19     7.359557     9.283450     4.913992     8.850833    11.261875   \n",
       "20     6.167997     9.283450     5.291475     8.656466    10.426213   \n",
       "21     6.406343     9.283450     5.075770     8.656466    10.426213   \n",
       "22     6.088605     9.283450     4.428656     8.656466    10.426213   \n",
       "23     6.088605     9.283450     4.428656     8.656466    10.426213   \n",
       "\n",
       "    building229  building231  building233  building234  \n",
       "0      2.937936    10.095473    11.026789     2.175237  \n",
       "1      2.937936    10.095473    11.026789     2.175237  \n",
       "2      2.937936    10.095473    11.026789     2.175237  \n",
       "3      2.937936    10.095473    11.026789     2.175237  \n",
       "4      3.256838    10.095473    11.026789     2.401029  \n",
       "5      3.256838    10.095473    11.026789     2.175237  \n",
       "6      6.658457    10.689723    14.211151     4.019203  \n",
       "7      4.638746    12.356139    13.562530     3.454724  \n",
       "8      3.735191    11.687654    11.026789     2.796164  \n",
       "9      3.256838    10.095473    11.026789     2.175237  \n",
       "10     2.937936    10.095473    11.026789     2.175237  \n",
       "11     2.937936    10.095473    11.026789     2.175237  \n",
       "12     3.416289    10.095473    11.026789     2.401029  \n",
       "13     3.309988    10.095473    11.026789     2.457477  \n",
       "14     2.991087    10.095473    11.026789     2.890244  \n",
       "15     4.213543    10.095473    11.026789     2.175237  \n",
       "16     4.054093    12.749108    11.026789     3.078404  \n",
       "17     2.937936    13.279835    14.388060     4.075651  \n",
       "18     4.027517    10.237048    12.442061     2.513925  \n",
       "19     3.602315    10.222175    11.161790     2.175237  \n",
       "20     3.416289    11.687654    14.699038     2.175237  \n",
       "21     2.937936    10.095473    12.618970     2.288133  \n",
       "22     3.097387    10.095473    11.026789     2.175237  \n",
       "23     2.937936    10.095473    11.026789     2.175237  \n",
       "\n",
       "[24 rows x 124 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "buildings_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Accessing the 3DCityDB through the IntegrCiTy DAL\n",
    "\n",
    "The IntegrCiTy DAL is implemented in [Python package dblayer](https://github.com/IntegrCiTy/dblayer).\n",
    "The following lines import the core of the package (*dblayer*) and additional wrappers for 3DCityDB SQL functions (*dblayer.func*), which will be used further down in this notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "from dblayer import *\n",
    "from dblayer.func.func_citydb_view_nrg import *\n",
    "from dblayer.func.func_citydb_pkg import *\n",
    "from dblayer.func.func_postgis_geom import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Provide the connection parameters for the 3DCityDB instance with the help of class *PostgreSQLConnectionInfo*:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "connect = PostgreSQLConnectionInfo(\n",
    "    user = 'postgres',\n",
    "    pwd = 'postgres',\n",
    "    host = 'localhost',\n",
    "    port = '5432',\n",
    "    dbname = 'citydb'\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connect to the 3DCityDB with the help of class *DBAccess*. This starts an [SQLAlchemy session](https://docs.sqlalchemy.org/en/13/orm/session.html) in the background, which allows to interact with the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_access = DBAccess()\n",
    "db_access.connect_to_citydb( connect )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This tutorial is intended to work with a clean and empty 3DCityDB instance.\n",
    "Hence, the next lines delete all content from the database for this tutorial.\n",
    "If you already have a 3DCityDB instance filled with data, you should create a new, empty instance to work with!\n",
    "\n",
    "**ATTENTION: The next two lines delete all content from the connected 3DCityDB instance!**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_access.cleanup_citydb_schema()\n",
    "db_access.cleanup_simpkg_schema()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Specify the *spatial reference identifier* (SRID) used by the 3DCityDB instance.\n",
    "If you have used the [setup scripts](https://github.com/IntegrCiTy/dblayer/tree/master/scripts) for installing the extended 3DCityDB provided as part of package *dblayer*, then the default SRID is [4326](https://epsg.io/4326).\n",
    "For this tutorial, this choice does note make much sense from a geographical point of view, but it is good enough to show you how the IntegrCiTy toolchain works.\n",
    "\n",
    "If you have a 3DCityDB instance running that has been configured with a different SRID, then change it accordingly (here and in all subsequent notebooks)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "srid=4326"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Consolidating the data\n",
    "\n",
    "The next step is to merge the data from the two sources and add it to the 3DCityDB.\n",
    "Since both data sources use the same identifier for buildings, the merging is rather straight forward.\n",
    "\n",
    "For adding the appropriate CityGML and ADE objects to the 3DCityDB, the IntegrCiTy DAL provides easy access to the SQL functions defined in the respective schemas.\n",
    "They are executed by calling the DBAccess function *add_citydb_object* with the SQL function name as first parameter, followed by a list of its arguments.\n",
    "\n",
    "The following lines iterate through the extracted data and then add new objects to the 3DCityDB:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "\n",
    "# Iterate through all entries from the shapefile.\n",
    "for data in buildings_sf:\n",
    "    \n",
    "    # Retrieve the attribute 'name' and 'e_carrier' from this entry:\n",
    "    #  - 'name' is the identifier that is also used in CSV data to refer to a specific building\n",
    "    #  - 'e_carrier'  specifies which energy carrier the building uses for heating\n",
    "    building_name = data.record['name']\n",
    "    building_energy_carrier = data.record['e_carrier']\n",
    "\n",
    "    # Convert the shape points defining the buildings's 2D footprint to a list\n",
    "    # of instances of class Point2D (class provided by package dblayer).\n",
    "    geom_2d_points = [ Point2D( p[0], p[1] ) for p in data.shape.points ]\n",
    "\n",
    "    # Execute function 'geom_from_2dpolygon' to convert the list of points to a\n",
    "    # 3DCityDB geometry.\n",
    "    geom = db_access.execute_function( \n",
    "        geom_from_2dpolygon( geom_2d_points, srid )\n",
    "        )\n",
    "\n",
    "    # Add the geometry as city object to the 3DCityDB with the help of SQL function\n",
    "    # 'insert_surface_geometry' (SQL function defined in database schema 'citydb_pkg').\n",
    "    geom_id = db_access.add_citydb_object(\n",
    "        insert_surface_geometry,\n",
    "        geometry = geom\n",
    "        )\n",
    "\n",
    "    # Add the building as city object to the 3DCityDB with the help of SQL function\n",
    "    # 'insert_building' (SQL function defined in database schema 'citydb_pkg').\n",
    "    building_id = db_access.add_citydb_object(\n",
    "        insert_building,\n",
    "        name = building_name,\n",
    "        lod0_footprint_id = geom_id,\n",
    "        )\n",
    "\n",
    "    if building_energy_carrier == 'gas':\n",
    "\n",
    "        # Retrieve the gas demand profile for this building.\n",
    "        gas_consumption_profile = buildings_df[building_name].tolist()\n",
    "\n",
    "        # Add the profile as city object to the 3DCityDB with the help of SQL function\n",
    "        # 'nrg8_insert_regular_time_series' (SQL function defined in database schema 'citydb_view').\n",
    "        time_series_id = db_access.add_citydb_object(\n",
    "            insert_regular_time_series,\n",
    "            name = 'ts_gas_consumption_{}'.format( building_name ),\n",
    "            description = 'gas consumption profile of {}'.format( building_name ),\n",
    "            acquisition_method = 'Simulation',\n",
    "            interpolation_type = 'AverageInSucceedingInterval',\n",
    "            values_array = gas_consumption_profile,\n",
    "            values_unit = 'kW',\n",
    "            temporal_extent_begin = datetime.datetime( 2020, 1, 1, 0, 0, 0 ),\n",
    "            temporal_extent_end = datetime.datetime( 2020, 1, 1, 23, 0, 0 ),\n",
    "            time_interval = 1,\n",
    "            time_interval_unit = 'h'\n",
    "            )\n",
    "\n",
    "        # Add a final energy object to the 3DCityDB and link it to the time series, with \n",
    "        # the help of SQL function 'nrg8_insert_final_energy' (SQL function defined in \n",
    "        # database schema 'citydb_view').\n",
    "        final_energy_id = db_access.add_citydb_object(\n",
    "            insert_final_energy,\n",
    "            name = 'final_energy_{}'.format( building_name ),\n",
    "            description = 'gas consumption of {}'.format( building_name ),\n",
    "            nrg_car_type = 'NaturalGas',\n",
    "            time_series_id = time_series_id\n",
    "            )\n",
    "\n",
    "        # Add a boiler object to the 3DCityDB and link it to the building, with \n",
    "        # the help of SQL function 'nrg8_insert_boiler' (SQL function defined in \n",
    "        # database schema 'citydb_view').\n",
    "        boiler_id = db_access.add_citydb_object(\n",
    "            insert_boiler,\n",
    "            name = 'gas_boiler_{}'.format( building_name ),\n",
    "            description = 'gas boiler of {}'.format( building_name ),\n",
    "            inst_in_ctyobj_id = building_id\n",
    "            )\n",
    "\n",
    "        # Add an energy conversion system object to the 3DCityDB, in order to link\n",
    "        # the boiler to the final energy use. This is done with the help of SQL function \n",
    "        # 'nrg8_insert_boiler' (SQL function defined in database schema 'citydb_view').\n",
    "        db_access.add_citydb_object(\n",
    "            insert_conv_sys_to_final_nrg,\n",
    "            conv_system_id = boiler_id,\n",
    "            final_nrg_id = final_energy_id,\n",
    "            role = 'consumption'\n",
    "            )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Storing the data to the 3DCityDB\n",
    "\n",
    "Above, the data was *added* to the database session. In order to make it persistent, i.e., to store it permanently in the database, it has to be *committed* to the 3DCityDB.\n",
    "This is done via *commit_citydb_session*:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_access.commit_citydb_session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, delete the instance of class DBAccess to close the session."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "del db_access"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next up is notebook [2b_gas_network.ipynb](./2b_gas_network.ipynb), which demonstrates how to use the IntegrCiTy DAL to store a gas network to the 3DCityDB."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}