bioexplorer/pythonsdk/notebooks/vasculature/BioExplorer_import_sonata_to_db.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f992ef9f",
   "metadata": {},
   "source": [
    "# Notebook to import Sonata-based files into the database\n",
    "![](../bioexplorer_vasculature_banner.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "345ec844",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import Session\n",
    "from tqdm.notebook import tqdm\n",
    "import numpy as np\n",
    "\n",
    "db_host = os.getenv('DB_HOST')\n",
    "db_name = os.getenv('DB_NAME')\n",
    "db_user = os.getenv('DB_USER')\n",
    "db_password = os.getenv('DB_PASSWORD')\n",
    "db_schema = 'vasculature'\n",
    "\n",
    "db_connection_string = 'postgresql+psycopg2://%s:%s@%s:5432/%s' % (db_user, db_password, db_host, db_name)\n",
    "print('Connection string: ' + db_connection_string + ', schema: ' + db_schema)\n",
    "\n",
    "engine = create_engine(db_connection_string)\n",
    "conn = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e0f227d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import libsonata\n",
    "data_folder = os.getenv('VASCULATURE_DATA_FOLDER')\n",
    "print(data_folder)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a834b1e2",
   "metadata": {},
   "source": [
    "## Nodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b8c6351",
   "metadata": {},
   "outputs": [],
   "source": [
    "nodes = libsonata.NodeStorage(os.path.join(data_folder, 'report_vasculature_6_entry_nodes_new.h5'))\n",
    "vasculature_population = nodes.open_population('vasculature')\n",
    "vasculature_population.attribute_names\n",
    "ids = np.arange(0, vasculature_population.size)\n",
    "start_xs = vasculature_population.get_attribute('start_x', list(ids))\n",
    "start_ys = vasculature_population.get_attribute('start_y', list(ids))\n",
    "start_zs = vasculature_population.get_attribute('start_z', list(ids))\n",
    "start_ds = vasculature_population.get_attribute('start_diameter', list(ids))\n",
    "section_ids = vasculature_population.get_attribute('section_id', list(ids))\n",
    "graph_ids = vasculature_population.get_attribute('subgraph_id', list(ids))\n",
    "entry_node_ids = vasculature_population.get_attribute('entry_edges', list(ids))\n",
    "pair_ids = vasculature_population.get_attribute('pairs', list(ids))\n",
    "# bifurcations = vasculature_population.get_attribute('edges_bifurcations', list(ids))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f4f8f43",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Nodes\n",
    "print('Deleting existing nodes')\n",
    "with Session(engine) as session:\n",
    "    session.execute('DELETE FROM %s.node' % db_schema)\n",
    "    session.commit()\n",
    "\n",
    "print('Populating nodes')\n",
    "duplicated_nodes = list()\n",
    "with Session(engine) as session:\n",
    "    for i in tqdm(range(vasculature_population.size)):\n",
    "        session.execute(\n",
    "            'INSERT INTO %s.node VALUES (:guid, :x, :y, :z, :radius, :section_guid, :sub_graph_guid, :pair_guid, :entry_node_guid)' % db_schema,\n",
    "            {\n",
    "                'guid': i,\n",
    "                'x': start_xs[i],\n",
    "                'y': start_ys[i],\n",
    "                'z': start_zs[i],\n",
    "                'radius': start_ds[i] * 0.5,\n",
    "                'section_guid': int(section_ids[i]),\n",
    "                'sub_graph_guid': int(graph_ids[i]),\n",
    "                'pair_guid': int(pair_ids[i]),\n",
    "                'entry_node_guid': int(entry_node_ids[i]),\n",
    "            }\n",
    "        )\n",
    "        if i % 1000 == 999:\n",
    "            session.commit()\n",
    "    session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ec38b28",
   "metadata": {},
   "outputs": [],
   "source": [
    "with Session(engine) as session:\n",
    "    pairs = session.execute('SELECT DISTINCT(pair_guid) FROM %s.node order BY pair_guid' % db_schema)\n",
    "    \n",
    "    for pair in pairs.all():\n",
    "        session.execute(\n",
    "            'UPDATE %s.node SET pair_guid=:pair_guid WHERE sub_graph_guid IN (SELECT sub_graph_guid FROM %s.node WHERE pair_guid=:pair_guid)' % (db_schema, db_schema),\n",
    "            {\n",
    "                'pair_guid': pair[0]\n",
    "            }\n",
    "        )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "246fd361",
   "metadata": {},
   "source": [
    "## Reports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "115b349c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def import_simulation_report(simulation_report_guid, population_guid, description, path):\n",
    "    print('Importing %s from %s' % (description, path))\n",
    "\n",
    "    reader = libsonata.ElementReportReader(path)\n",
    "    reader.get_population_names()\n",
    "    vasculature_report_population = reader['vasculature']\n",
    "\n",
    "    # Report\n",
    "    with Session(engine) as session:\n",
    "        session.execute(\n",
    "            'DELETE FROM %s.simulation_report WHERE simulation_report_guid=:simulation_report_guid' % db_schema,\n",
    "            {\n",
    "                'simulation_report_guid': simulation_report_guid\n",
    "            }\n",
    "        )\n",
    "        session.commit()\n",
    "\n",
    "    with Session(engine) as session:\n",
    "        session.execute(\n",
    "            'INSERT INTO %s.simulation_report VALUES (:simulation_report_guid, :description, :start_time, :end_time, :time_step, :time_units, :data_units)' % db_schema,\n",
    "            {\n",
    "                'simulation_report_guid': simulation_report_guid,\n",
    "                'description' : description,\n",
    "                'start_time': vasculature_report_population.times[0],\n",
    "                'end_time': vasculature_report_population.times[1],\n",
    "                'time_step': vasculature_report_population.times[2],\n",
    "                'time_units': vasculature_report_population.time_units,\n",
    "                'data_units': vasculature_report_population.data_units\n",
    "            }\n",
    "        )\n",
    "        session.commit()\n",
    "\n",
    "    # Report simulation values\n",
    "    with Session(engine) as session:\n",
    "        session.execute(\n",
    "            'DELETE FROM %s.simulation_time_series WHERE simulation_report_guid=:simulation_report_guid' % db_schema,\n",
    "            {\n",
    "                'simulation_report_guid': simulation_report_guid\n",
    "            }\n",
    "        )\n",
    "        session.commit()\n",
    "\n",
    "    data = vasculature_report_population.get()\n",
    "    for i in range(len(data.times)):\n",
    "        frame_data = data.data[i]\n",
    "    \n",
    "        buffer = np.array(frame_data, dtype=np.float32).tobytes()\n",
    "        with Session(engine) as session:\n",
    "            session.execute(\n",
    "                'INSERT INTO %s.simulation_time_series VALUES (:simulation_report_guid, :frame, :values)' % db_schema,\n",
    "                {\n",
    "                    'simulation_report_guid': simulation_report_guid,\n",
    "                    'frame': i,\n",
    "                    'values': buffer\n",
    "                }\n",
    "            )\n",
    "            session.commit()    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81e5f7f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import_simulation_report(\n",
    "    0, population_guid, 'Flows', os.path.join(data_folder, 'v2', 'report_flows.h5'))\n",
    "import_simulation_report(\n",
    "    1, population_guid, 'Pressures', os.path.join(data_folder, 'v2', 'report_pressures.h5'))\n",
    "import_simulation_report(\n",
    "    2, population_guid, 'Radii', os.path.join(data_folder, 'v2', 'report_radii.h5'))    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a62a5035",
   "metadata": {},
   "source": [
    "## Connectome"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1c47b8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "connectome_db_schema = 'connectome'\n",
    "\n",
    "glio_vascular_edges = libsonata.EdgeStorage(os.path.join(data_folder, 'gliovascular.h5'))\n",
    "glio_vascular_population = glio_vascular_edges.open_population('gliovascular')\n",
    "guids = list(np.arange(0, glio_vascular_population.size))\n",
    "endfoot_guids = glio_vascular_population.get_attribute('endfoot_id', guids)\n",
    "astrocyte_guids = glio_vascular_population.target_nodes(guids)\n",
    "astrocyte_section_guids = glio_vascular_population.get_attribute('astrocyte_section_id', guids)\n",
    "vasculature_node_guids = glio_vascular_population.source_nodes(guids)\n",
    "vasculature_section_guids = glio_vascular_population.get_attribute('vasculature_section_id', guids)\n",
    "vasculature_segment_guids = glio_vascular_population.get_attribute('vasculature_segment_id', guids)\n",
    "endfoot_compartment_lengths = glio_vascular_population.get_attribute('endfoot_compartment_length', guids)\n",
    "endfoot_compartment_diameters = glio_vascular_population.get_attribute('endfoot_compartment_diameter', guids)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72d5b2eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "populations = [\n",
    "    [0, 'astrocytes', 'NGV Astrocytes'],\n",
    "    [1, 'vasculature', 'NGV vasculature']\n",
    "]\n",
    "with Session(engine) as session:\n",
    "    for i in tqdm(range(len(populations))):\n",
    "        session.execute(\n",
    "            'INSERT INTO %s.population VALUES(:guid, :schema, :description)' % connectome_db_schema,\n",
    "            {\n",
    "                'guid': populations[i][0],\n",
    "                'schema': populations[i][1],\n",
    "                'description': populations[i][2],\n",
    "            }\n",
    "        )\n",
    "        session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97781123",
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Deleting existing glio vascular entries')\n",
    "with Session(engine) as session:\n",
    "    session.execute(\n",
    "        'DELETE FROM %s.glio_vascular WHERE astrocyte_population_guid=:astrocyte_population_guid AND vasculature_population_guid=:vasculature_population_guid' % connectome_db_schema,\n",
    "        {\n",
    "            'astrocyte_population_guid': populations[0][0],\n",
    "            'vasculature_population_guid': populations[1][0],\n",
    "        }\n",
    "    )\n",
    "    session.commit()\n",
    "\n",
    "print('Populating glio vascular entries')\n",
    "duplicated_nodes = list()\n",
    "with Session(engine) as session:\n",
    "    for i in tqdm(range(len(guids))):\n",
    "        try:\n",
    "            session.execute(\n",
    "                'INSERT INTO %s.glio_vascular VALUES (:guid, :astrocyte_population_guid, :vasculature_population_guid, :astrocyte_guid, :astrocyte_section_guid, :vasculature_node_guid, :vasculature_section_guid, :vasculature_segment_guid, :endfoot_compartment_length, :endfoot_compartment_diameter)' % connectome_db_schema,\n",
    "                {\n",
    "                    'guid': int(endfoot_guids[i]),\n",
    "                    'astrocyte_population_guid': populations[0][0],\n",
    "                    'vasculature_population_guid': populations[1][0],\n",
    "                    'astrocyte_guid': int(astrocyte_guids[i]),\n",
    "                    'astrocyte_section_guid': int(astrocyte_section_guids[i]),\n",
    "                    'vasculature_node_guid': int(vasculature_node_guids[i]),\n",
    "                    'vasculature_section_guid': int(vasculature_section_guids[i]),\n",
    "                    'vasculature_segment_guid': int(vasculature_segment_guids[i]),\n",
    "                    'endfoot_compartment_length': float(endfoot_compartment_lengths[i]),\n",
    "                    'endfoot_compartment_diameter': float(endfoot_compartment_diameters[i])\n",
    "                }\n",
    "            )\n",
    "            session.commit()\n",
    "        except Exception as e:\n",
    "            session.commit()\n",
    "            print(e)\n",
    "    session.commit()"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "ea9a5fa46eb6bad2806a8ea1d08e15bb1e255a2d4320b81e765591579963c56b"
  },
  "kernelspec": {
   "display_name": "Python 3.8.10 64-bit ('env': venv)",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}