HoneyBee Database Creation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Honeybee Database\n",
    "\n",
    "This is an attempt to create a honeybee database from scratch to then build an API.\n",
    "\n",
    "[SQL Alchemy <3 Postgres](http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "\n",
    "# SQL connection data\n",
    "user = \"postgres\"\n",
    "pword = \"supersecretpassword\"\n",
    "server_ip = \"localhost\"\n",
    "port = \"5432\"\n",
    "\n",
    "# Empty database creation data\n",
    "database_name = \"ladybugtools\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Python <=> PostgreSQL communication engine\n",
    "engine = sqlalchemy.create_engine(\"postgresql://{user}:{pword}@{server_ip}:{port}\" \\\n",
    "                                  .format(user=user, pword=pword, server_ip=server_ip, port=port))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to server and create empty database\n",
    "\n",
    "# conn = engine.connect()\n",
    "# conn.execute(\"commit\")\n",
    "# conn.execute(\"create database {database_name}\".format(database_name=database_name))\n",
    "# conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create new engine that connects to the newly created database\n",
    "engine = sqlalchemy.create_engine(\"postgresql://{user}:{pword}@{server_ip}:{port}/{database_name}\" \\\n",
    "                                  .format(user=user, pword=pword, server_ip=server_ip, port=port, database_name=database_name))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing postgres sepcific stuff that will be used\n",
    "# Data Types\n",
    "from sqlalchemy.dialects.postgresql import JSONB, UUID\n",
    "\n",
    "from sqlalchemy.types import String, Enum, Float, Integer, DateTime\n",
    "\n",
    "# Column utilities\n",
    "from sqlalchemy.dialects.postgresql import INT4RANGE\n",
    "from sqlalchemy import ForeignKey\n",
    "\n",
    "import enum\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "pf_precision = 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import Table, Column, MetaData\n",
    "\n",
    "for table in reversed(list(meta.tables.keys())):\n",
    "    meta.tables[table].drop()\n",
    "    \n",
    "meta = MetaData(engine)\n",
    "\n",
    "\n",
    "\n",
    "# try:\n",
    "#     daylight_factor_recipe.drop()\n",
    "#     simulation_join_analysis_grid.drop()\n",
    "    \n",
    "#     simulations.drop()\n",
    "    \n",
    "#     surface_groups_join_honeybee_surface.drop()\n",
    "#     surface_groups.drop()\n",
    "#     window_groups.drop()\n",
    "#     analysis_grid_join_point.drop()\n",
    "\n",
    "#     analysis_grids.drop()\n",
    "#     grid_points.drop()\n",
    "\n",
    "#     honeybee_child_surfaces.drop()\n",
    "#     honeybee_surfaces.drop()\n",
    "#     analysis_surfaces.drop()\n",
    "\n",
    "#     materials.drop()\n",
    "#     surface_vertices.drop()\n",
    "\n",
    "# except:\n",
    "#     pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Materials and Surfaces\n",
    "\n",
    "The first section of the database to create is that containing materials, geometry and then more complex constructs such as surfaces, surface states, child/parent surfaces and surface groups."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "class MaterialTypes(enum.Enum):\n",
    "    bsdf = 'bsdf'\n",
    "    light_source = 'light source'\n",
    "    opaque = 'opaque'\n",
    "    translucent = 'translucent'\n",
    "\n",
    "materials = Table('materials', meta,\n",
    "                  Column('id', UUID, primary_key=True, nullable=False),\n",
    "                  Column('type', Enum(MaterialTypes), nullable=False),\n",
    "                  Column('data', JSONB, nullable=False)\n",
    "                 )\n",
    "\n",
    "materials.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [],
   "source": [
    "surface_vertices = Table('surface_vertices', meta,\n",
    "                        Column('id', UUID, primary_key=True, nullable=False),\n",
    "                        Column('x1', Float(precision=pf_precision), nullable=False),\n",
    "                        Column('y1', Float(precision=pf_precision), nullable=False),\n",
    "                        Column('z1', Float(precision=pf_precision), nullable=False),\n",
    "                        Column('x2', Float(precision=pf_precision), nullable=False),\n",
    "                        Column('y2', Float(precision=pf_precision), nullable=False),\n",
    "                        Column('z2', Float(precision=pf_precision), nullable=False),\n",
    "                        Column('x3', Float(precision=pf_precision), nullable=False),\n",
    "                        Column('y3', Float(precision=pf_precision), nullable=False),\n",
    "                        Column('z3', Float(precision=pf_precision), nullable=False),\n",
    "                        Column('x4', Float(precision=pf_precision)),\n",
    "                        Column('y4', Float(precision=pf_precision)),\n",
    "                        Column('z4', Float(precision=pf_precision))\n",
    "                        )\n",
    "                \n",
    "surface_vertices.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [],
   "source": [
    "class SurfaceTypes(enum.Enum):\n",
    "    wall = 'wall'\n",
    "    underground_wall = 'underground wall'\n",
    "    roof = 'roof'\n",
    "    underground_ceiling = 'underground ceiling'\n",
    "    floor = 'floor'\n",
    "    slab_on_grade = 'slab on grade'\n",
    "    exposed_floor = 'exposed floor'\n",
    "    ceiling = 'ceiling'\n",
    "    window = 'window'\n",
    "    context = 'context'\n",
    "\n",
    "    \n",
    "# Add condition so that surface_state_name cannot be shared by two surfaces that have the same vertices Foreign Key\n",
    "    \n",
    "analysis_surfaces = Table('analysis_surfaces', meta,\n",
    "                         Column('id', UUID, primary_key=True, nullable=False),\n",
    "                         Column('vertices', UUID, ForeignKey('surface_vertices.id'), nullable=False),\n",
    "                         Column('surface_name', String),\n",
    "                         Column('surface_state_name', String, nullable=False),\n",
    "                         Column('type', Enum(SurfaceTypes)),\n",
    "                         Column('radiance_material', UUID, ForeignKey('materials.id'), nullable=False)\n",
    "                         )\n",
    "\n",
    "analysis_surfaces.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [],
   "source": [
    "honeybee_surfaces = Table('honeybee_surfaces', meta,\n",
    "                         Column('id', UUID, primary_key=True, nullable=False),\n",
    "                         Column('parent_surface', UUID, ForeignKey('analysis_surfaces.id'), nullable=False)\n",
    "                         )\n",
    "\n",
    "honeybee_surfaces.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Might be worth adding checks for geometry (as in child surface is contained within parent surface)\n",
    "\n",
    "honeybee_child_surfaces = Table('honeybee_child_surfaces', meta,\n",
    "                                Column('id', UUID, primary_key=True),\n",
    "                                Column('parent_id', UUID, ForeignKey('honeybee_surfaces.id'), nullable=False),\n",
    "                                Column('child_id', UUID, ForeignKey('surface_vertices.id'), nullable=False)\n",
    "                               )\n",
    "\n",
    "honeybee_child_surfaces.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [],
   "source": [
    "surface_groups = Table('surface_groups', meta,\n",
    "                      Column('id', UUID, primary_key=True),\n",
    "                      Column('name', String, nullable=False),\n",
    "                      Column('description', String)\n",
    "                      )\n",
    "\n",
    "surface_groups.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {},
   "outputs": [],
   "source": [
    "surface_groups_join_honeybee_surface = Table('surface_groups_join_honeybee_surfaces', meta,\n",
    "                                            Column('id', Integer, primary_key=True),\n",
    "                                            Column('surface_group', UUID, ForeignKey('surface_groups.id')),\n",
    "                                            Column('honeybee_surface', UUID, ForeignKey('honeybee_surfaces.id'))\n",
    "                                            )\n",
    "\n",
    "surface_groups_join_honeybee_surface.create()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Analysis Grids\n",
    "\n",
    "Analysis grids are created from groups of analysis points. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [],
   "source": [
    "grid_points = Table('grid_points', meta,\n",
    "                   Column('id', UUID, primary_key=True, nullable=False),\n",
    "                   Column('x', Float(precision=pf_precision), nullable=False),\n",
    "                   Column('y', Float(precision=pf_precision), nullable=False),\n",
    "                   Column('z', Float(precision=pf_precision), nullable=False),\n",
    "                   Column('vx', Float(precision=pf_precision), nullable=False),\n",
    "                   Column('vy', Float(precision=pf_precision), nullable=False),\n",
    "                   Column('vz', Float(precision=pf_precision), nullable=False),\n",
    "                   )\n",
    "\n",
    "grid_points.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "analysis_grids = Table('analysis_grids', meta,\n",
    "                     Column('id', UUID, primary_key=True, nullable=False),\n",
    "                     Column('name', String)\n",
    "                     )\n",
    "\n",
    "analysis_grids.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Need to set foreign key constraints (if point is deleted also delete grid for eg)\n",
    "\n",
    "analysis_grid_join_point = Table('analysis_grid_join_point', meta,\n",
    "                                Column('id', Integer, primary_key=True),\n",
    "                                Column('analysis_grid', UUID, ForeignKey('analysis_grids.id')),\n",
    "                                Column('grid_point', UUID, ForeignKey('grid_points.id'))\n",
    "                                )\n",
    "\n",
    "analysis_grid_join_point.create()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.5 Window Groups\n",
    "\n",
    "Window groups are constructed by joining windows to analysis grids. This allows Honeybee to calculate the contributions from each window (state) seperately and facilitate post processing. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [],
   "source": [
    "window_groups = Table('window_groups', meta,\n",
    "                     Column('id', Integer, primary_key=True),\n",
    "                     Column('analysis_grid', UUID, ForeignKey('analysis_grids.id')),\n",
    "                     Column('honeybee_child_surface', UUID, ForeignKey('honeybee_child_surfaces.id'))\n",
    "                     )\n",
    "\n",
    "window_groups.create()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Weather and Location\n",
    "\n",
    "Most simulation require weather or location information in order to run properly. This information is usually provided by parsing an EPW file and outputing certain contents. As studying weather information is also useful for comfort it is easier to store a parsed EPW file and WEAs in order to trace origin of data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [],
   "source": [
    "epws = Table('epws', meta,\n",
    "             Column('id', UUID, primary_key=True),\n",
    "            Column('city', String),\n",
    "            Column('country', String),\n",
    "            Column('source', String, nullable=False),\n",
    "            Column('station_id', String, nullable=False),\n",
    "            Column('latitude', Float(precision=2), nullable=False),\n",
    "            Column('longitude', Float(precision=2), nullable=False),\n",
    "            Column('time_zone', String),\n",
    "            Column('elevation', Float(precision=2), nullable=False)\n",
    "            )\n",
    "\n",
    "epws.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [],
   "source": [
    "epw_data = Table('epw_data', meta,\n",
    "            Column('id', Integer, primary_key=True),\n",
    "            Column('epw', UUID, ForeignKey('epws.id'), nullable=False),\n",
    "            Column('date_time',DateTime, nullable=False),\n",
    "            Column('years', Integer),\n",
    "            Column('dry_bulb_temperature', Float(precision=2)),\n",
    "            Column('dew_point_temperature', Float(precision=2)),\n",
    "            Column('relative_humidity', Float(precision=2)),\n",
    "            Column('atmospheric_station_pressure', Float(precision=2)),\n",
    "            Column('extraterrestrial_horizontal_radiation', Float(precision=2)),\n",
    "            Column('extraterrestrial_direct_normal_radiation', Float(precision=2)),\n",
    "            Column('horizontal_infrared_radiation_intensity', Float(precision=2)),\n",
    "            Column('global_horizontal_radiation', Float(precision=2)),\n",
    "            Column('direct_normal_radiation', Float(precision=2)),\n",
    "            Column('diffuse_horizontal_radiation', Float(precision=2)),\n",
    "            Column('global_horizontal_illuminance', Float(precision=2)),\n",
    "            Column('direct_normal_illuminance', Float(precision=2)),\n",
    "            Column('diffuse_horizontal_illuminance', Float(precision=2)),\n",
    "            Column('zenith_luminance', Float(precision=2)),\n",
    "            Column('wind_direction', Float(precision=2)),\n",
    "            Column('wind_speed', Float(precision=2)),\n",
    "            Column('total_sky_cover', Float(precision=2)),\n",
    "            Column('opaque_sky_cover', Float(precision=2)),\n",
    "            Column('visibility', Float(precision=2)),\n",
    "            Column('ceiling_height', Float(precision=2)),\n",
    "            Column('present_weather_observation', Float(precision=2)),\n",
    "            Column('present_weather_codes', Float(precision=2)),\n",
    "            Column('precipitable_water', Float(precision=2)),\n",
    "            Column('aerosol_optical_depth', Float(precision=2)),\n",
    "            Column('snow_depth', Float(precision=2)),\n",
    "            Column('days_since_last_snowfall', Float(precision=2)),\n",
    "            Column('albedo', Float(precision=2)),\n",
    "            Column('liquid_precipitation_depth', Float(precision=2)),\n",
    "            Column('liquid_precipitation_quantity', Float(precision=2))\n",
    "                )\n",
    "\n",
    "epw_data.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [],
   "source": [
    "weas = Table('weas', meta,\n",
    "            Column('id', UUID, primary_key=True),\n",
    "            Column('epw', UUID, ForeignKey('epws.id'), nullable=False))\n",
    "weas.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [],
   "source": [
    "wea_data = Table('wea_data', meta,\n",
    "                Column('id', Integer, primary_key=True),\n",
    "                Column('wea', UUID, ForeignKey('weas.id'), nullable=False),\n",
    "                Column('date_time',DateTime, nullable=False),\n",
    "                Column('direct_normal_radiation', Float(precision=2)),\n",
    "                Column('diffuse_horizontal_radiation', Float(precision=2))\n",
    "                )\n",
    "\n",
    "wea_data.create()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Simulations and Recipes\n",
    "\n",
    "A recipe is an object composed of some or all of the following:\n",
    "* simulation type (mandatory)\n",
    "* radiance parameters (mandatory)\n",
    "* weather information\n",
    "* location information\n",
    "* sun vectors\n",
    "\n",
    "Note that it does not contain information on surfaces or analysis grids. That is because a recipe is designed to be recycled so options can be properly benchmarked according to the same standard. As such it is reccomended a recipe be given a helpful name.\n",
    "\n",
    "A simulation is the combination of a recipe with the surfaces and analysis grids that enable the modelling of daylight in a space. These objects also contain state information (`waiting`, `running`, `finished`, `failed`) and other metadata (`start time`, `end time`, `number of cores used`, `cost`(if running on a cloud server?))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [],
   "source": [
    "class RecipeTypes(enum.Enum):\n",
    "    annual ='annual'\n",
    "    daylight_factor = 'daylight factor'\n",
    "    direct_reflection = 'direct reflection',\n",
    "    three_phase = 'three phase',\n",
    "    five_phase = 'five phase',\n",
    "    point_in_time = 'point in time',\n",
    "    radiation = 'radiation',\n",
    "    solar_access = 'solar access'\n",
    "    \n",
    "class RecipeBase(enum.Enum):\n",
    "    grid = 'gridbased'\n",
    "    image = 'imagebased'\n",
    "    \n",
    "# class RecipeStatus(enum.Enum):\n",
    "    \n",
    "simulations = Table('simulations', meta,\n",
    "               Column('id', UUID, primary_key=True),\n",
    "               Column('type', Enum(RecipeTypes), nullable=False),\n",
    "               Column('base', Enum(RecipeBase), nullable=False),\n",
    "               Column('status', String), # Whether the recipe is running/failed etc...\n",
    "               Column('surfaces', UUID, ForeignKey('surface_groups.id')),\n",
    "               extend_existing=True\n",
    "               )\n",
    "\n",
    "simulations.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [],
   "source": [
    "simulation_join_analysis_grid = Table('simulation_join_analysis_grid', meta,\n",
    "                                 Column('id', Integer, primary_key=True),\n",
    "                                 Column('simulation', UUID, ForeignKey('simulations.id')),\n",
    "                                 Column('analysis_grid', UUID, ForeignKey('analysis_grids.id'))\n",
    "                                     )\n",
    "\n",
    "simulation_join_analysis_grid.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [],
   "source": [
    "daylight_factor_recipe = Table('daylight_factor_recipes', meta,\n",
    "                              Column('id', UUID, primary_key=True),\n",
    "                              Column('simulation', UUID, ForeignKey('simulations.id'), nullable=False),\n",
    "                              Column('radiance_parameters', JSONB, nullable=False)\n",
    "                              )\n",
    "\n",
    "daylight_factor_recipe.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [],
   "source": [
    "annual_recipe = Table('annual_recipes', meta, \n",
    "                     Column('id', UUID, primary_key=True),\n",
    "                     Column('simulation', UUID, ForeignKey('simulations.id'), nullable=False),\n",
    "                     Column('radiance_parameters', JSONB, nullable=False),\n",
    "                     Column('sky_mtx', UUID, ForeignKey('weas.id'), nullable=False))\n",
    "annual_recipe.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [],
   "source": [
    "radiation_recipe = Table('radiation_recipes', meta, \n",
    "                     Column('id', UUID, primary_key=True),\n",
    "                     Column('simulation', UUID, ForeignKey('simulations.id'), nullable=False),\n",
    "                     Column('radiance_parameters', JSONB, nullable=False),\n",
    "                     Column('sky_mtx', UUID, ForeignKey('weas.id'), nullable=False))\n",
    "radiation_recipe.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [],
   "source": [
    "direct_reflection_recipe = Table('direct_reflection_recipes', meta, \n",
    "                     Column('id', UUID, primary_key=True),\n",
    "                     Column('epw', UUID, ForeignKey('epws.id'), nullable=False),\n",
    "                     Column('simulation', UUID, ForeignKey('simulations.id'), nullable=False),\n",
    "                     Column('latitude', Float(precision=2), nullable=False),\n",
    "                     Column('longitude', Float(precision=2), nullable=False))\n",
    "\n",
    "direct_reflection_recipe.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [],
   "source": [
    "solar_access_recipe = Table('solar_access_recipes', meta, \n",
    "                     Column('id', UUID, primary_key=True),\n",
    "                     Column('epw', UUID, ForeignKey('epws.id'), nullable=False),\n",
    "                     Column('simulation', UUID, ForeignKey('simulations.id'), nullable=False),\n",
    "                     Column('latitude', Float(precision=2), nullable=False),\n",
    "                     Column('longitude', Float(precision=2), nullable=False))\n",
    "\n",
    "solar_access_recipe.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [],
   "source": [
    "three_phase_recipe = Table('three_phase_recipes', meta,\n",
    "                     Column('id', UUID, primary_key=True),\n",
    "                     Column('simulation', UUID, ForeignKey('simulations.id'), nullable=False),\n",
    "                     Column('view_mtx_parameters', JSONB, nullable=False),\n",
    "                     Column('daylight_mtx_parameters', JSONB, nullable=False),     \n",
    "                     Column('sky_mtx', UUID, ForeignKey('weas.id'), nullable=False))\n",
    "three_phase_recipe.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [],
   "source": [
    "five_phase_recipe = Table('five_phase_recipes', meta,\n",
    "                     Column('id', UUID, primary_key=True),\n",
    "                     Column('simulation', UUID, ForeignKey('simulations.id'), nullable=False),\n",
    "                     Column('view_mtx_parameters', JSONB, nullable=False),\n",
    "                     Column('daylight_mtx_parameters', JSONB, nullable=False),     \n",
    "                     Column('sky_mtx', UUID, ForeignKey('weas.id'), nullable=False))\n",
    "five_phase_recipe.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}