{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Program your ABox\n",
    "\n",
    "Within this short tutorial you will extract knowledge of a real building and use this to populate your knowledge base with assertional knowledge (ABox).\n",
    "\n",
    "For this purpose we will use the [*Duplex Apartment*](https://www.nibs.org/page/bsa_commonbimfiles#project1) a building, which is frequently referred to in Building Information Modelling (BIM) research. Information on the topology of the building is provided as a spreadsheet, which is compliant to the [Construction Operations Building Information Exchange - (COBie)](https://www.nibs.org/page/bsa_cobie) standard. We will extract topological information from this file and to populate a knowledge base with instances of the BOT ontology."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The file is stored in the [data](data) folder and named *2012-03-23-Duplex-Handover.xlsx*. We will load it using the python module ```xrld``` and have a look at the buildings stored within:  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlrd\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ensure that you use the correct path to access the file on your system!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "You specify the path to the file as:  /home/jovyan/data/2012-03-23-Duplex-Handover.xlsx\n"
     ]
    }
   ],
   "source": [
    "currentPath = os.getcwd()\n",
    "pathFolderData = os.path.abspath( os.path.join( currentPath , os.pardir ) )\n",
    "pathToFile = os.path.join( pathFolderData , \"data\" , \"2012-03-23-Duplex-Handover.xlsx\" )\n",
    "print( \"You specify the path to the file as: \" , pathToFile )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This are the sheets in you file: \n",
      "['Instruction', 'Contact', 'Facility', 'Floor', 'Space', 'Zone', 'Type', 'Component', 'System', 'Assembly', 'Spare', 'Resource', 'Job', 'Impact', 'Document', 'Attribute', 'Coordinate', 'Connection', 'Issue', 'PickLists']\n"
     ]
    }
   ],
   "source": [
    "wb = xlrd.open_workbook( pathToFile )\n",
    "\n",
    "print( \"This are the sheets in you file: \" )\n",
    "print( wb.sheet_names() )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will now setup again rdflib so we can extract buildings, sites and space from the COBie spreadsheet and use this input to create instances (assertional knowledge - ABox) of BOT."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import needed components from rdflib\n",
    "from rdflib import Graph , Literal , BNode , Namespace , RDF , RDFS , OWL , URIRef\n",
    "\n",
    "# initiate triple store, i.e. Graph()\n",
    "g = Graph()\n",
    "\n",
    "# Add namespaces\n",
    "g.bind( \"owl\" , OWL )\n",
    "BOT = Namespace( \"https://w3id.org/bot#\" )\n",
    "g.bind( \"bot\" , BOT )\n",
    "NS = Namespace( \"https://example.org/DuplexApartmentAbox#\" )\n",
    "g.bind( \"\" , NS )\n",
    "\n",
    "# Initiate ontology entity\n",
    "s = URIRef( \"https://example.org/DuplexApartmentAbox\" )\n",
    "p = RDF.type\n",
    "o = OWL.ontology\n",
    "g.add( ( s , p , o ) )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now navigate to the *Facility* sheet to extract our first entities: the site and building and map it to ```bot:Site``` and ```bot:Building```:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Site IFC ID:  1xS3BCk291UvhgP2a6eflN\n",
      "Building IFC ID:  1xS3BCk291UvhgP2a6eflK\n"
     ]
    }
   ],
   "source": [
    "sh_facility = wb.sheet_by_name( \"Facility\" )\n",
    "\n",
    "# Add site\n",
    "print( \"Site IFC ID: \" , str( sh_facility.cell_value( 1 , 15 ) ) )\n",
    "siteID = str( sh_facility.cell_value( 1 , 15 ) )\n",
    "s = NS[ siteID ]\n",
    "p = RDF.type\n",
    "o = BOT[ \"Site\" ]\n",
    "g.add( ( s , p , o ) )\n",
    "\n",
    "# Add building\n",
    "print( \"Building IFC ID: \" , str( sh_facility.cell_value( 1 , 17 ) ) )\n",
    "buiID = str( sh_facility.cell_value( 1 , 17 ) )\n",
    "s = NS[ buiID ]\n",
    "p = RDF.type\n",
    "o = BOT[ \"Building\" ]\n",
    "g.add( ( s , p , o ) )\n",
    "\n",
    "# Add human readable lable\n",
    "p = RDFS.label\n",
    "o = Literal( str( sh_facility.cell_value( 1 , 0 ) ) )\n",
    "g.add( ( s , p , o ) )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now also add the fact that the building is located at the site:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "s = NS[ siteID ]\n",
    "p = BOT[ \"hasBuilding\" ]\n",
    "o = NS[ buiID ]\n",
    "g.add( ( s , p , o ) )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can add building storeys:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "BuildingStorey IFC ID:  1xS3BCk291UvhgP2dvNMKI\n",
      "BuildingStorey IFC ID:  1xS3BCk291UvhgP2dvNMQJ\n",
      "BuildingStorey IFC ID:  1xS3BCk291UvhgP2dvNtSE\n",
      "BuildingStorey IFC ID:  1xS3BCk291UvhgP2dvNsgp\n"
     ]
    }
   ],
   "source": [
    "# Navigate to building storeys sheet\n",
    "sh_BuiStor = wb.sheet_by_name( \"Floor\" )\n",
    "\n",
    "for i in range( 1 , sh_BuiStor.nrows , 1 ):\n",
    "    print( \"BuildingStorey IFC ID: \" , str( sh_BuiStor.cell_value( i , 6 ) ) )\n",
    "    \n",
    "    # create instance\n",
    "    BuiStorID = str( sh_BuiStor.cell_value( i , 6 ) )\n",
    "    s = NS[ BuiStorID ]\n",
    "    p = RDF.type\n",
    "    o = BOT[ \"Storey\" ]\n",
    "    g.add( ( s , p , o ) )\n",
    "    \n",
    "    # Link to building\n",
    "    s = NS[ buiID ]\n",
    "    p = BOT[ \"hasStorey\" ]\n",
    "    o = NS[ BuiStorID ]\n",
    "    g.add( ( s , p , o ) )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally we can extract the spaces:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dlXru\n",
      "Space name:  A104\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dl_3P\n",
      "Space name:  B104\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dlXrr\n",
      "Space name:  A101\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dl_3Q\n",
      "Space name:  B101\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dlXr$\n",
      "Space name:  A103\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dl_3S\n",
      "Space name:  B103\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dlXr2\n",
      "Space name:  A102\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dl_CZ\n",
      "Space name:  B102\n",
      "Space IFC ID:  10mjSDZJj9gPS2PrQaxa3z\n",
      "Space name:  A105\n",
      "Space IFC ID:  10mjSDZJj9gPS2PrQaxa4o\n",
      "Space name:  B105\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dlXre\n",
      "Space name:  A204\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dl_3C\n",
      "Space name:  B204\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dlXrc\n",
      "Space name:  A202\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dl_3A\n",
      "Space name:  B202\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dlXrb\n",
      "Space name:  A203\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dl_39\n",
      "Space name:  B203\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dlXri\n",
      "Space name:  A201\n",
      "Space IFC ID:  0BTBFw6f90Nfh9rP1dl_3G\n",
      "Space name:  B201\n",
      "Space IFC ID:  2gRXFgjRn2HPE$YoDLX3FC\n",
      "Space name:  B205\n",
      "Space IFC ID:  2gRXFgjRn2HPE$YoDLX3FV\n",
      "Space name:  A205\n",
      "Space IFC ID:  0pNy6pOyf7JPmXRLgxs3sW\n",
      "Space name:  R301\n"
     ]
    }
   ],
   "source": [
    "# Navigate to building spaces sheet\n",
    "sh_Space = wb.sheet_by_name( \"Space\" )\n",
    "\n",
    "for i in range( 1 , sh_Space.nrows , 1 ):\n",
    "    # Security hack\n",
    "    if str( sh_Space.cell_value( i , 8 ) ) == \"n/a\":\n",
    "        break\n",
    "    print( \"Space IFC ID: \" , str( sh_Space.cell_value( i , 8 ) ) )\n",
    "    print( \"Space name: \" , str( sh_Space.cell_value( i , 0 ) ) )\n",
    "    \n",
    "    # create instance\n",
    "    SpaceID = str( sh_Space.cell_value( i , 8 ) )\n",
    "    s = NS[ SpaceID ]\n",
    "    p = RDF.type\n",
    "    o = BOT[ \"Space\" ]\n",
    "    g.add( ( s , p , o ) )\n",
    "    \n",
    "    # Add human readable label\n",
    "    p = RDFS.label\n",
    "    o = Literal( str( sh_Space.cell_value( i , 0 ) ) )\n",
    "    g.add( ( s , p , o ) )\n",
    "    \n",
    "    # Link to building\n",
    "    s = NS[ buiID ]\n",
    "    p = BOT[ \"hasSpace\" ]\n",
    "    o = NS[ SpaceID ]\n",
    "    g.add( ( s , p , o ) )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We store the result in a file and examine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Created Abox.ttl in folder:\n",
      "/home/jovyan/Notebooks\n"
     ]
    }
   ],
   "source": [
    "g.serialize( destination = \"Abox.ttl\" , format = \"turtle\" )\n",
    "print( \"Created Abox.ttl in folder:\" )\n",
    "print( str( os.getcwd() ) )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or we query our in memory triple store for all instances of type ```bot:space```:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Found space with name:  B104\n",
      "Found space with name:  A205\n",
      "Found space with name:  B103\n",
      "Found space with name:  A202\n",
      "Found space with name:  R301\n",
      "Found space with name:  A103\n",
      "Found space with name:  B102\n",
      "Found space with name:  A203\n",
      "Found space with name:  B201\n",
      "Found space with name:  B203\n",
      "Found space with name:  B105\n",
      "Found space with name:  A102\n",
      "Found space with name:  A104\n",
      "Found space with name:  B101\n",
      "Found space with name:  A105\n",
      "Found space with name:  A201\n",
      "Found space with name:  A204\n",
      "Found space with name:  B204\n",
      "Found space with name:  B205\n",
      "Found space with name:  A101\n",
      "Found space with name:  B202\n"
     ]
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "    PREFIX bot: <https://w3id.org/bot#>\n",
    "    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>\n",
    "    PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>\n",
    "    SELECT ?space ?name\n",
    "       WHERE {\n",
    "          ?space rdf:type bot:Space .\n",
    "          ?space rdfs:label ?name .\n",
    "       }\"\"\"\n",
    "res = g.query( query )\n",
    "\n",
    "for row in res:\n",
    "    print( \"Found space with name: \" , row[1] )"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}