start-here.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "485e0973",
   "metadata": {},
   "source": [
    "# Efficiently Donating Excess COVID Vaccine Supplies\n",
    "### Exploring logistics optimizations in the context of COVID vaccination donations\n",
    "by Kaan Karamente; Saif Ahmed; Matt Brown; Kyle Sutton; Chad Meley (info@kinetica.com)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22e4ca53",
   "metadata": {},
   "source": [
    "We've all heard of the Traveling Salesperson Problem<sup>(1)</sup>, it is one of the most common Graph problems and has numerous implementations. In the practice, however, problems are much more complicated. In the world of logistics, a more realistic setup is typicall the Multiple Supply Demand Chain Optimization (MSDO), where there are multiple sources and sinks and we're looking for the most optimal delivery routes. A generic overview can be seen here: https://www.kinetica.com/blog/kinetica-graph-analytics-multiple-supply-demand-chain-optimization-msdo-graph-solver/\n",
    "\n",
    "To demonstrate Multiple Supply Demand Chain Optimization (MSDO) with an immediate challenge we face globally, we've modeled the challenge of vaccine donations. The US has excess vaccine supply which expires over time. If supply is expiring, it is better to donate it abroad before expiration, as quickly as possible. The White House has been doing this<sup>(2)</sup>. But can it be done more efficiently?\n",
    "\n",
    "This is a complex problem:\n",
    "* We have multiple supply sites (each state or region) which can feed into major international airports\n",
    "* We have multiple demand sites (many nations), many of which desperately require more vaccine supply\n",
    "* We have time constraints since accumulation, transport, and distribution need to be faster than expiration timelines\n",
    "* Everything above is dynamic -- the supply and demand constantly changes with broad usage and infection trends\n",
    "\n",
    "We have a Multiple Supply Demand Chain Optimization (MSDO) problem! We've modeled the supply, demand, routes, and have everything ready to run on a database (to respond to daily changes in global supply and demand.) The setup documentation can be seen at https://docs.kinetica.com/7.1/guides/match_graph_dc_multi_supply_demand/ but it will be more instructive to run it yourself below. Everything below will run on the Developer Edition (https://www.kinetica.com/try/) or on Kinetica Cloud (https://www.kinetica.com/kinetica-as-a-service-on-azure/)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5448b269",
   "metadata": {},
   "source": [
    "The CDC publishes extensive daily data on vaccine delivery, availability, and administration here: https://covid.cdc.gov/covid-data-tracker/#vaccinations_vacc-total-admin-rate-total (see button \"View Historic Vaccination Data\".) From this, an approximate \"excess\" supply can be calculated at the state level for each state in the US. Further, from standard data on vaccine expirations, we can calculate excess supply which is at risk of expiring. \n",
    "\n",
    "How would we efficiently get the vaccines to the destinations given tight vaccine expiration timeframes? We can use commercial ariline networks! All data on airline networks and routes are public and we can create a graph from this."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d400139a",
   "metadata": {},
   "source": [
    "#### Required Data\n",
    "To achieve this logistical optimization, we'll need several datasets. The eight key datasets are listed below:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4954ca4",
   "metadata": {},
   "source": [
    "* Global Airports and Air Routes Network Data\n",
    "\n",
    "    * Airlines, IATA Codes, and Callsigns\n",
    "    https://kinetica-community.s3.amazonaws.com/vaccine-distro/airlines.csv\n",
    "\n",
    "\n",
    "\n",
    "* Airport Metadata for Supply and Demand Aggregations\n",
    "\n",
    "    * Airport IATA Codes\n",
    "    https://kinetica-community.s3.amazonaws.com/vaccine-distro/airports.csv\n",
    "\n",
    "    * US Vaccine Statistics -- Availability, Usage (to calculate excess)\n",
    "    https://kinetica-community.s3.amazonaws.com/vaccine-distro/vaccine-us.csv\n",
    "\n",
    "    * Map of Country ISO Alpha-2 to Alpha-3 for Cross-Dataset Mapping\n",
    "    https://kinetica-community.s3.amazonaws.com/vaccine-distro/map_iso_alpha2_alpha3.csv\n",
    "\n",
    "    * Airports, Countries serviced, and Geo-coordinates\n",
    "    https://kinetica-community.s3.amazonaws.com/vaccine-distro/airport-to-country-map.csv\n",
    "* COVID Incidence and Vaccine Utilization by Jurisdiction\n",
    "\n",
    "    * Global COVID Statistics (to calculate need/demand for vaccine) \n",
    "    https://kinetica-community.s3.amazonaws.com/vaccine-distro/owid-covid-data.csv\n",
    "\n",
    "    * Airport-to-Airport Routes and Flight Map\n",
    "    https://kinetica-community.s3.amazonaws.com/vaccine-distro/routes.csv\n",
    "\n",
    "    * List of US Airports (to segment \"excess supply\" locations)\n",
    "    https://kinetica-community.s3.amazonaws.com/vaccine-distro/airport_metadata_usa.csv    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05d9f386",
   "metadata": {},
   "source": [
    "We are now going to load a number of files into the database, but first you'll need to get them onto the database machine. Grab all the files referenced above and either put them into the /mnt/persist folder of Kinetica Dev Edition, or load them into a directory of your choice (e.g., /vaccine) on Kinetica Cloud via the KIFS upload screen. You can find more information here: https://docs.kinetica.com/7.1/tools/kifs/"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10c03c7a",
   "metadata": {},
   "source": [
    "We took all the files above and loaded them onto the Kinetica file system under the /vaccine folder:\n",
    "* kifs://vaccine/airlines.csv\n",
    "* kifs://vaccine/airport-to-country-map.csv\n",
    "* kifs://vaccine/vaccine-us.csv\n",
    "* kifs://vaccine/map_iso_alpha2_alpha3.csv\n",
    "* kifs://vaccine/owid-covid-data.csv\n",
    "* kifs://vaccine/airport_metadata_usa.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "571525ed",
   "metadata": {},
   "source": [
    "Both the supply and demand datasets are highly dynamic, changing at least once a day. The above dataset will provide you a year's worth of data, but if you wish to run an as-of-now optmization, it is best to ingest current vaccine Supply and Demand from Kinetica Confluent Cloud topics:\n",
    "* Broker: pkc-ep9mm.us-east-2.aws.confluent.cloud:9092\n",
    "* Topics: vaccine-distro-us-states; vaccine-distro-countries-global"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "127d6960",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import csv\n",
    "import gpudb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe414a16",
   "metadata": {},
   "source": [
    "We'll be interacting with Kinetica along the way, loading data, setting up graph optimziations, and viewing results. Ensure to export these environment variables (or override them below): **KINETICA_HOST, KINETICA_USER, KINETICA_PASS**. All the code below is Python, but many are SQL commands excuted via Python directly against the database you connect to below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4abc365b",
   "metadata": {},
   "outputs": [],
   "source": [
    "KINETICA_HOST = os.getenv('KINETICA_HOST', \"localhost:9191\")\n",
    "KINETICA_USER = os.getenv('KINETICA_USER', \"admin\")\n",
    "KINETICA_PASS = os.getenv('KINETICA_PASS')\n",
    "db = gpudb.GPUdb(host=KINETICA_HOST, username=KINETICA_USER, password=KINETICA_PASS)              "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb6837e2",
   "metadata": {},
   "source": [
    "First, we'll take the airports and airline routes data above and create a graph from it. More information about creating graphs is available at https://docs.kinetica.com/7.1/graph_solver/network_graph_solver/. Below we work through it step-by-step. Start by downloading the airports and routes file referenced above (we put it into a \"./data\" directory.) Our goal will be to create nodes and edges files so we can create a graph which will drive logistics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "5f4f478d",
   "metadata": {},
   "outputs": [],
   "source": [
    "INPUT_FILE_AIRPORTS = \"data_originals/airports.csv\"\n",
    "INPUT_FILE_ROUTES = \"data_originals/routes.csv\"\n",
    "\n",
    "OUTPUT_FILE_NODES = \"out_nodes.csv\"\n",
    "OUTPUT_FILE_EDGES = \"out_edges.csv\"\n",
    "\n",
    "FIELDS_NODES = [\"NODE_ID\",\n",
    "                \"NODE_X\",\n",
    "                \"NODE_Y\",\n",
    "                \"NODE_NAME\",\n",
    "                \"NODE_WKTPOINT\",\n",
    "                \"NODE_LABEL\",\n",
    "                \"IATA\",\n",
    "                \"ICAO\",\n",
    "                \"CITY\",\n",
    "                \"COUNTRY\"]\n",
    "\n",
    "FIELDS_EDGES = [\"EDGE_ID\",\n",
    "                \"EDGE_NODE1_ID\",\n",
    "                \"EDGE_NODE2_ID\",\n",
    "                \"EDGE_WKTLINE\",\n",
    "                \"EDGE_NODE1_X\",\n",
    "                \"EDGE_NODE1_Y\",\n",
    "                \"EDGE_NODE2_X\",\n",
    "                \"EDGE_NODE2_Y\",\n",
    "                \"EDGE_NODE1_WKTPOINT\",\n",
    "                \"EDGE_NODE2_WKTPOINT\",\n",
    "                \"EDGE_NODE1_NAME\",\n",
    "                \"EDGE_NODE2_NAME\",\n",
    "                \"EDGE_DIRECTION\",\n",
    "                \"EDGE_LABEL\",\n",
    "                \"EDGE_WEIGHT_VALUESPECIFIED\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "cfa3cd08",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Helper function to simplify strings\n",
    "def cleanse(in_str):\n",
    "    out_str = in_str\n",
    "    out_str.replace(\",\", \"\")\n",
    "    out_str.replace(\"'\", \"\")\n",
    "    return out_str\n",
    "\n",
    "# TODO: this is just a rough measure, a stand-in for now\n",
    "# https://stackoverflow.com/questions/19412462/getting-distance-between-two-points-based-on-latitude-longitude\n",
    "def rough_distance(slon, slat, dlon, dlat):\n",
    "    from math import sin, cos, sqrt, atan2, radians\n",
    "    # approximate radius of earth in km\n",
    "    R = 6373.0\n",
    "    lat1 = radians(float(slat))\n",
    "    lon1 = radians(float(slon))\n",
    "    lat2 = radians(float(dlat))\n",
    "    lon2 = radians(float(dlon))\n",
    "    dlon = lon2 - lon1\n",
    "    dlat = lat2 - lat1\n",
    "    a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2\n",
    "    c = 2 * atan2(sqrt(a), sqrt(1 - a))\n",
    "    distance = R * c\n",
    "    return distance    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "e63bfc83",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing 34710 rows\n"
     ]
    }
   ],
   "source": [
    "lookup_airport = {}\n",
    "airport_nodes = []\n",
    "\n",
    "## ------------------------------------------------------------------------\n",
    "## Create Lookup table for Edge Enrichment\n",
    "\n",
    "input_file = csv.DictReader(open(INPUT_FILE_AIRPORTS))\n",
    "for row in input_file:\n",
    "    lookup_airport[row['AIRPORT_ID']] = {\n",
    "        'AIRPORT_ID': row['AIRPORT_ID'],\n",
    "        'NAME': cleanse(row['NAME']),\n",
    "        'CITY': cleanse(row['CITY']),\n",
    "        'COUNTRY': cleanse(row['COUNTRY']),\n",
    "        'IATA': row['IATA'],\n",
    "        'ICAO': row['ICAO'],\n",
    "        'LATITUDE': row['LATITUDE'],\n",
    "        'LONGITUDE': row['LONGITUDE']\n",
    "    }\n",
    "\n",
    "    ## ------------------------------------------------------------------------\n",
    "    ## Create Nodes\n",
    "\n",
    "    if row['AIRPORT_ID'] == \"\\\\N\":\n",
    "        continue\n",
    "    nlon = row['LONGITUDE']\n",
    "    nlat = row['LATITUDE']\n",
    "    if row['IATA']==\"\\\\N\":\n",
    "        row['IATA']=None\n",
    "        nodename = f\"{row['ICAO']}: {cleanse(row['NAME'])}\"\n",
    "        nodelabel = f\"{row['ICAO']}: {cleanse(row['NAME'])}; {cleanse(row['CITY'])}, {cleanse(row['COUNTRY'])}\"\n",
    "    else:\n",
    "        nodename = f\"{row['IATA']}: {cleanse(row['NAME'])}\"\n",
    "        nodelabel = f\"{row['IATA']}: {cleanse(row['NAME'])}; {cleanse(row['CITY'])}, {cleanse(row['COUNTRY'])}\"\n",
    "\n",
    "    persistable = {\n",
    "        \"NODE_ID\": row['AIRPORT_ID'],\n",
    "        \"NODE_X\": nlon,\n",
    "        \"NODE_Y\": nlat,\n",
    "        \"NODE_NAME\": nodename,\n",
    "        \"NODE_WKTPOINT\": f\"POINT({nlon} {nlat})\",\n",
    "        \"NODE_LABEL\": nodelabel,\n",
    "        \"IATA\": row['IATA'],\n",
    "        \"ICAO\": row['ICAO'],\n",
    "        \"CITY\": cleanse(row['CITY']),\n",
    "        \"COUNTRY\": cleanse(row['COUNTRY'])\n",
    "    }\n",
    "    airport_nodes.append(persistable)\n",
    "    #print(f\"Adding node {persistable['NODE_LABEL']}\")\n",
    "\n",
    "#print(f\"Writing {len(airport_nodes)} rows\")\n",
    "\n",
    "with open(OUTPUT_FILE_NODES, 'w', newline='\\n') as csvfile:        \n",
    "    writer = csv.DictWriter(csvfile, fieldnames=FIELDS_NODES)\n",
    "    writer.writeheader()\n",
    "    for n in airport_nodes:\n",
    "        writer.writerow(n)\n",
    "\n",
    "## ------------------------------------------------------------------------\n",
    "## Create Edges\n",
    "\n",
    "inter_airport_network_edges = []\n",
    "edge_id = 10000\n",
    "\n",
    "input_file = csv.DictReader(open(INPUT_FILE_ROUTES))\n",
    "for row in input_file:\n",
    "    edge_id = edge_id + 1\n",
    "    if row['SOURCE_AIRPORT_ID'] == \"\\\\N\":\n",
    "        #print(f\"Warn source airport {row['SOURCE_AIRPORT_ID']} is Null, skipping...\")\n",
    "        continue\n",
    "    if row['DEST_AIRPORT_ID'] == \"\\\\N\":\n",
    "        #print(f\"Warn source airport {row['DEST_AIRPORT_ID']} is Null, skipping...\")\n",
    "        continue\n",
    "    if str(row['SOURCE_AIRPORT_ID']) not in lookup_airport:\n",
    "        #print(f\"Warn source airport {row['SOURCE_AIRPORT_ID']} not found in Airports lookup table, skipping...\")\n",
    "        continue\n",
    "    if str(row['DEST_AIRPORT_ID']) not in lookup_airport:\n",
    "        #print(f\"Warn destination airport {row['SOURCE_AIRPORT_ID']} not found in Airports lookup table, skipping...\")\n",
    "        continue\n",
    "    if lookup_airport[row['SOURCE_AIRPORT_ID']]['COUNTRY'] == lookup_airport[row['DEST_AIRPORT_ID']]['COUNTRY']:\n",
    "        # skipping domestic flight\n",
    "        continue\n",
    "    slon = lookup_airport[row['SOURCE_AIRPORT_ID']]['LONGITUDE']\n",
    "    slat = lookup_airport[row['SOURCE_AIRPORT_ID']]['LATITUDE']\n",
    "    dlon = lookup_airport[row['DEST_AIRPORT_ID']]['LONGITUDE']\n",
    "    dlat = lookup_airport[row['DEST_AIRPORT_ID']]['LATITUDE']\n",
    "    persistable = {\n",
    "        \"EDGE_ID\": edge_id,\n",
    "        \"EDGE_NODE1_ID\": row['SOURCE_AIRPORT_ID'],\n",
    "        \"EDGE_NODE2_ID\": row['DEST_AIRPORT_ID'],\n",
    "        \"EDGE_WKTLINE\": f\"LINESTRING({slon} {slat}, {dlon} {dlat})\",\n",
    "        \"EDGE_NODE1_X\": slon,\n",
    "        \"EDGE_NODE1_Y\": slat,\n",
    "        \"EDGE_NODE2_X\": dlon,\n",
    "        \"EDGE_NODE2_Y\": dlat,\n",
    "        \"EDGE_NODE1_WKTPOINT\": f\"POINT({slon} {slat})\",\n",
    "        \"EDGE_NODE2_WKTPOINT\": f\"POINT({dlon} {dlat})\",\n",
    "        \"EDGE_NODE1_NAME\": f\"{lookup_airport[row['SOURCE_AIRPORT_ID']]['IATA']}: {lookup_airport[row['SOURCE_AIRPORT_ID']]['NAME']}\",\n",
    "        \"EDGE_NODE2_NAME\": f\"{lookup_airport[row['DEST_AIRPORT_ID']]['IATA']}: {lookup_airport[row['DEST_AIRPORT_ID']]['NAME']}\",\n",
    "        \"EDGE_DIRECTION\": \"0\",\n",
    "        \"EDGE_LABEL\": f\"'{row['AIRLINE']} {row['AIRLINE_ID']} from {lookup_airport[row['SOURCE_AIRPORT_ID']]['IATA']} --> {lookup_airport[row['DEST_AIRPORT_ID']]['IATA']}'\",\n",
    "        \"EDGE_WEIGHT_VALUESPECIFIED\": rough_distance(slon, slat, dlon, dlat)\n",
    "    }\n",
    "    inter_airport_network_edges.append(persistable)\n",
    "    #print(f\"Adding edge {persistable['EDGE_LABEL']}\")\n",
    "\n",
    "print(f\"Writing {len(inter_airport_network_edges)} rows\")\n",
    "\n",
    "with open(OUTPUT_FILE_EDGES, 'w', newline='\\n') as csvfile:        \n",
    "    writer = csv.DictWriter(csvfile, fieldnames=FIELDS_EDGES)\n",
    "    writer.writeheader()\n",
    "    for i in inter_airport_network_edges:\n",
    "        writer.writerow(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c21ba1b0",
   "metadata": {},
   "source": [
    "In real life, data is always dirty, especially when joining data from different sources. While the above code looks extensive, we're just throwing out bad (e.g., airline routes mismatching the airport list, airports or routes with null data, etc.) At the end of the process, we end up with graph nodes and edges, ready to load into Kinetica:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "390f0363",
   "metadata": {},
   "source": [
    "     -rw-r--r--   1 saif  staff   1397234 Aug  2 10:30 out_nodes.csv\n",
    "     -rw-r--r--   1 saif  staff  11438747 Aug  2 10:30 out_edges.csv\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8a61177",
   "metadata": {},
   "source": [
    "Now that we have the graph set up, we'll also need supply and demand figures. Of course, \"supply\" and \"demand\" are subjective figures -- we will need to derive these figures from high-order public data and personal judgements on which health data should drive demand (positivity rate, age distribution, vaccination rate, deaths, etc.) We present a simple approach but encourage the community consider this deeply and propose better or more fair mechanisms to calculate supply/demand. Lets start by loading the raw data:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a6aea7a",
   "metadata": {},
   "source": [
    "Lets create a schema to keep all our work in one place:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9567a373",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'ERROR',\n",
       " 'message': \"Error: 'Attempt to create a schema that already exists: DEMO_Vaccine_Distro (E/Ec:47) (S/SDc:1493); error in Job process'\",\n",
       " 'data_type': 'none',\n",
       " 'response_time': 0.00324}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_result = db.execute_sql(\"CREATE SCHEMA DEMO_Vaccine_Distro;\")\n",
    "exec_result['status_info']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fc89787",
   "metadata": {},
   "source": [
    "Lets load in the nodes and edges files we just created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "442ee17d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'OK',\n",
       " 'message': '',\n",
       " 'data_type': 'execute_sql_response',\n",
       " 'response_time': 0.86593}"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_result = db.execute_sql(\"\"\"\n",
    "    CREATE OR REPLACE MATERIALIZED EXTERNAL TABLE \"DEMO_Vaccine_Distro\".\"airport_nodes\"\n",
    "    (\n",
    "       \"NODE_ID\" SMALLINT NOT NULL,\n",
    "       \"NODE_X\" DOUBLE NOT NULL,\n",
    "       \"NODE_Y\" DOUBLE NOT NULL,\n",
    "       \"NODE_NAME\" VARCHAR (128) NOT NULL,\n",
    "       \"NODE_WKTPOINT\" GEOMETRY NOT NULL,\n",
    "       \"NODE_LABEL\" VARCHAR (128) NOT NULL,\n",
    "       \"IATA\" VARCHAR (4) NOT NULL,\n",
    "       \"ICAO\" VARCHAR (4) NOT NULL,\n",
    "       \"CITY\" VARCHAR (64),\n",
    "       \"COUNTRY\" VARCHAR (32, dict) NOT NULL\n",
    "    )\n",
    "    FILE PATHS 'kifs://vaccine/out_nodes.csv'\n",
    "    FORMAT DELIMITED TEXT;\n",
    "    \"\"\")\n",
    "exec_result['status_info']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "3f6bccb3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'OK',\n",
       " 'message': '',\n",
       " 'data_type': 'execute_sql_response',\n",
       " 'response_time': 1.1005}"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_result = db.execute_sql(\"\"\"\n",
    "    CREATE or REPLACE MATERIALIZED EXTERNAL TABLE \"DEMO_Vaccine_Distro\".\"airport_routes\"\n",
    "    (\n",
    "       \"EDGE_ID\" INTEGER NOT NULL,\n",
    "       \"EDGE_NODE1_ID\" INTEGER NOT NULL,\n",
    "       \"EDGE_NODE2_ID\" INTEGER NOT NULL,\n",
    "       \"EDGE_WKTLINE\" GEOMETRY NOT NULL,\n",
    "       \"EDGE_NODE1_X\" DOUBLE NOT NULL,\n",
    "       \"EDGE_NODE1_Y\" DOUBLE NOT NULL,\n",
    "       \"EDGE_NODE2_X\" DOUBLE NOT NULL,\n",
    "       \"EDGE_NODE2_Y\" DOUBLE NOT NULL,\n",
    "       \"EDGE_NODE1_WKTPOINT\" GEOMETRY NOT NULL,\n",
    "       \"EDGE_NODE2_WKTPOINT\" GEOMETRY NOT NULL,\n",
    "       \"EDGE_NODE1_NAME\" VARCHAR (128, dict) NOT NULL,\n",
    "       \"EDGE_NODE2_NAME\" VARCHAR (128, dict) NOT NULL,\n",
    "       \"EDGE_DIRECTION\" INTEGER NOT NULL,\n",
    "       \"EDGE_LABEL\" VARCHAR (32) NOT NULL,\n",
    "       \"EDGE_WEIGHT_VALUESPECIFIED\" DOUBLE NOT NULL\n",
    "    )\n",
    "    FILE PATHS 'kifs://vaccine/out_edges.csv'\n",
    "    FORMAT DELIMITED TEXT;\n",
    "    \"\"\")\n",
    "exec_result['status_info']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d774b65f",
   "metadata": {},
   "source": [
    "### Load Demand Data\n",
    "\n",
    "Supply and Demand data needs to be loaded to drive the optimization. The data changes daily, so you can re-run the optimization daily for the most up-to-date logistical recommendations. To load the data, we recommend Kinetica External Tables. https://docs.kinetica.com/7.1/concepts/external_tables/\n",
    "\n",
    "The External Tables funcitonality will allow easy loads, schema inference, and re-loads. Depending on the data source (which can sometimes be 'rough', inferred schemas occasionally require tweaks. Below we load the data using an inferred schema tweaked slighty. We encourage you to explore fully automated inference as well, if curious. The original data can be sourced from Our World in Data (https://ourworldindata.org/coronavirus) if desired."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "d92d3e1e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'OK',\n",
       " 'message': '',\n",
       " 'data_type': 'execute_sql_response',\n",
       " 'response_time': 1.04394}"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_result = db.execute_sql(\"\"\"\n",
    "    CREATE or REPLACE MATERIALIZED EXTERNAL TABLE \"DEMO_Vaccine_Distro\".\"global_covid_statistics\"\n",
    "    (\n",
    "       \"iso_code\" VARCHAR (8, dict) NOT NULL,\n",
    "       \"continent\" VARCHAR (16, dict),\n",
    "       \"location\" VARCHAR (32, dict) NOT NULL,\n",
    "       \"date\" DATE (dict) NOT NULL,\n",
    "       \"total_cases\" DECIMAL(18,4),\n",
    "       \"new_cases\" DECIMAL(18,4),\n",
    "       \"new_cases_smoothed\" DECIMAL(18,4),\n",
    "       \"total_deaths\" DECIMAL(18,4),\n",
    "       \"new_deaths\" DECIMAL(18,4),\n",
    "       \"new_deaths_smoothed\" DECIMAL(18,4),\n",
    "       \"total_cases_per_million\" DECIMAL(18,4),\n",
    "       \"new_cases_per_million\" DECIMAL(18,4),\n",
    "       \"new_cases_smoothed_per_million\" DECIMAL(18,4),\n",
    "       \"total_deaths_per_million\" DECIMAL(18,4),\n",
    "       \"new_deaths_per_million\" DECIMAL(18,4),\n",
    "       \"new_deaths_smoothed_per_million\" DECIMAL(18,4),\n",
    "       \"reproduction_rate\" DECIMAL(18,4),\n",
    "       \"icu_patients\" DECIMAL(18,4),\n",
    "       \"icu_patients_per_million\" DECIMAL(18,4),\n",
    "       \"hosp_patients\" DECIMAL(18,4),\n",
    "       \"hosp_patients_per_million\" DECIMAL(18,4),\n",
    "       \"weekly_icu_admissions\" DECIMAL(18,4),\n",
    "       \"weekly_icu_admissions_per_million\" DECIMAL(18,4),\n",
    "       \"weekly_hosp_admissions\" DECIMAL(18,4),\n",
    "       \"weekly_hosp_admissions_per_million\" DECIMAL(18,4),\n",
    "       \"new_tests\" DECIMAL(18,4),\n",
    "       \"total_tests\" DECIMAL(18,4),\n",
    "       \"total_tests_per_thousand\" DECIMAL(18,4),\n",
    "       \"new_tests_per_thousand\" DECIMAL(18,4),\n",
    "       \"new_tests_smoothed\" DECIMAL(18,4),\n",
    "       \"new_tests_smoothed_per_thousand\" DECIMAL(18,4),\n",
    "       \"positive_rate\" DECIMAL(18,4),\n",
    "       \"tests_per_case\" DECIMAL(18,4),\n",
    "       \"tests_units\" VARCHAR (16, dict),\n",
    "       \"total_vaccinations\" DECIMAL(18,4),\n",
    "       \"people_vaccinated\" DECIMAL(18,4),\n",
    "       \"people_fully_vaccinated\" DECIMAL(18,4),\n",
    "       \"new_vaccinations\" DECIMAL(18,4),\n",
    "       \"new_vaccinations_smoothed\" DECIMAL(18,4),\n",
    "       \"total_vaccinations_per_hundred\" DECIMAL(18,4),\n",
    "       \"people_vaccinated_per_hundred\" DECIMAL(18,4),\n",
    "       \"people_fully_vaccinated_per_hundred\" DECIMAL(18,4),\n",
    "       \"new_vaccinations_smoothed_per_million\" DECIMAL(18,4),\n",
    "       \"stringency_index\" DECIMAL(18,4),\n",
    "       \"population\" DECIMAL(18,4),\n",
    "       \"population_density\" DECIMAL(18,4),\n",
    "       \"median_age\" DECIMAL(18,4),\n",
    "       \"aged_65_older\" DECIMAL(18,4),\n",
    "       \"aged_70_older\" DECIMAL(18,4),\n",
    "       \"gdp_per_capita\" DECIMAL(18,4),\n",
    "       \"extreme_poverty\" DECIMAL(18,4),\n",
    "       \"cardiovasc_death_rate\" DECIMAL(18,4),\n",
    "       \"diabetes_prevalence\" DECIMAL(18,4),\n",
    "       \"female_smokers\" DECIMAL(18,4),\n",
    "       \"male_smokers\" DECIMAL(18,4),\n",
    "       \"handwashing_facilities\" DECIMAL(18,4),\n",
    "       \"hospital_beds_per_thousand\" DECIMAL(18,4),\n",
    "       \"life_expectancy\" DECIMAL(18,4),\n",
    "       \"human_development_index\" DECIMAL(18,4),\n",
    "       \"excess_mortality\" DECIMAL(18,4)\n",
    "    )\n",
    "    FILE PATHS 'kifs://vaccine/owid-covid-data.csv'\n",
    "    FORMAT DELIMITED TEXT;\n",
    "    \"\"\")\n",
    "exec_result['status_info']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "695da629",
   "metadata": {},
   "source": [
    "### Load Supply Data\n",
    "\n",
    "Just like with demand data, we'll load supply data using External Tables. Fresh data can be sourced daily from https://covid.cdc.gov/covid-data-tracker/#vaccinations\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "2677c0aa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'OK',\n",
       " 'message': '',\n",
       " 'data_type': 'execute_sql_response',\n",
       " 'response_time': 0.88918}"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_result = db.execute_sql(\"\"\"\n",
    "    CREATE OR REPLACE MATERIALIZED EXTERNAL TABLE \"DEMO_Vaccine_Distro\".\"vaccine_utilization_usa\"\n",
    "    (\n",
    "       \"State_Territory_Federal_Entity\" VARCHAR (32),\n",
    "       \"Total_Doses_Delivered\" INTEGER,\n",
    "       \"Doses_Delivered_per_100K\" INTEGER,\n",
    "       \"18plus_Doses_Delivered_per_100K\" INTEGER,\n",
    "       \"Total_Doses_Administered_by_State_where_Administered\" INTEGER,\n",
    "       \"Doses_Administered_per_100k_by_State_where_Administered\" INTEGER,\n",
    "       \"18plus_Doses_Administered_by_State_where_Administered\" INTEGER,\n",
    "       \"18plus_Doses_Administered_per_100K_by_State_where_Administered\" INTEGER,\n",
    "       \"People_with_at_least_One_Dose_by_State_of_Residence\" INTEGER,\n",
    "       \"Percent_of_Total_Pop_with_at_least_One_Dose_by_State_of_Residence\" DECIMAL(18,4),\n",
    "       \"People_18plus_with_at_least_One_Dose_by_State_of_Residence\" INTEGER,\n",
    "       \"Percent_of_18plus_Pop_with_at_least_One_Dose_by_State_of_Residence\" DECIMAL(18,4),\n",
    "       \"People_Fully_Vaccinated_by_State_of_Residence\" INTEGER,\n",
    "       \"Percent_of_Total_Pop_Fully_Vaccinated_by_State_of_Residence\" DECIMAL(18,4),\n",
    "       \"People_18plus_Fully_Vaccinated_by_State_of_Residence\" INTEGER,\n",
    "       \"Percent_of_18plus_Pop_Fully_Vaccinated_by_State_of_Residence\" DECIMAL(18,4),\n",
    "       \"Total_Number_of_Pfizer_doses_delivered\" INTEGER,\n",
    "       \"Total_Number_of_Moderna_doses_delivered\" INTEGER,\n",
    "       \"Total_Number_of_Janssen_doses_delivered\" INTEGER,\n",
    "       \"Total_Number_of_doses_from_unknown_manufacturer_delivered\" TINYINT,\n",
    "       \"Total_Number_of_Janssen_doses_administered\" INTEGER,\n",
    "       \"Total_Number_of_Moderna_doses_administered\" INTEGER,\n",
    "       \"Total_Number_of_Pfizer_doses_adminstered\" INTEGER,\n",
    "       \"Total_Number_of_doses_from_unknown_manufacturer_administered\" INTEGER,\n",
    "       \"People_Fully_Vaccinated_Moderna_Resident\" INTEGER,\n",
    "       \"People_Fully_Vaccinated_Pfizer_Resident\" INTEGER,\n",
    "       \"People_Fully_Vaccinated_Janssen_Resident\" INTEGER,\n",
    "       \"People_Fully_Vaccinated_Unknown_2_dose_manufacturer_Resident\" SMALLINT,\n",
    "       \"People_18plus_Fully_Vaccinated_Moderna_Resident\" INTEGER,\n",
    "       \"People_18plus_Fully_Vaccinated_Pfizer_Resident\" INTEGER,\n",
    "       \"People_18plus_Fully_Vaccinated_Janssen_Resident\" INTEGER,\n",
    "       \"People_18plus_Fully_Vaccinated_Unknown_2_dose_manufacturer_Resident\" SMALLINT,\n",
    "       \"People_with_2_Doses_by_State_of_Residence\" INTEGER,\n",
    "       \"Percent_of_Total_Pop_with_1plus_Doses_by_State_of_Residence\" DECIMAL(18,4),\n",
    "       \"People_18plus_with_1plus_Doses_by_State_of_Residence\" INTEGER,\n",
    "       \"Percent_of_18plus_Pop_with_1plus_Doses_by_State_of_Residence\" DECIMAL(18,4),\n",
    "       \"Percent_of_Total_Pop_with_2_Doses_by_State_of_Residence\" DECIMAL(18,4),\n",
    "       \"People_18plus_with_2_Doses_by_State_of_Residence\" INTEGER,\n",
    "       \"Percent_of_18plus_Pop_with_2_Doses_by_State_of_Residence\" DECIMAL(18,4),\n",
    "       \"People_with_1plus_Doses_by_State_of_Residence\" INTEGER,\n",
    "       \"People_65plus_with_at_least_One_Dose_by_State_of_Residence\" INTEGER,\n",
    "       \"Percent_of_65plus_Pop_with_at_least_One_Dose_by_State_of_Residence\" DECIMAL(18,4),\n",
    "       \"People_65plus_Fully_Vaccinated_by_State_of_Residence\" INTEGER,\n",
    "       \"Percent_of_65plus_Pop_Fully_Vaccinated_by_State_of_Residence\" DECIMAL(18,4),\n",
    "       \"People_65plus_Fully_Vaccinated_Moderna_Resident\" INTEGER,\n",
    "       \"People_65plus_Fully_Vaccinated_Pfizer_Resident\" INTEGER,\n",
    "       \"People_65plus_Fully_Vaccinated_Janssen_Resident\" INTEGER,\n",
    "       \"People_65plus_Fully_Vaccinated_Unknown_2_dose_Manuf_Resident\" SMALLINT,\n",
    "       \"65plus_Doses_Administered_by_State_where_Administered\" INTEGER,\n",
    "       \"Doses_Administered_per_100k_of_65plus_pop_by_State_where_Administered\" INTEGER,\n",
    "       \"Doses_Delivered_per_100k_of_65plus_pop\" INTEGER,\n",
    "       \"People_12plus_with_at_least_One_Dose_by_State_of_Residence\" INTEGER,\n",
    "       \"Percent_of_12plus_Pop_with_at_least_One_Dose_by_State_of_Residence\" DECIMAL(18,4),\n",
    "       \"People_12plus_Fully_Vaccinated_by_State_of_Residence\" INTEGER,\n",
    "       \"Percent_of_12plus_Pop_Fully_Vaccinated_by_State_of_Residence\" DECIMAL(18,4),\n",
    "       \"People_12plus_Fully_Vaccinated_Moderna_Resident\" INTEGER,\n",
    "       \"People_12plus_Fully_Vaccinated_Pfizer_Resident\" INTEGER,\n",
    "       \"People_12plus_Fully_Vaccinated_Janssen_Resident\" INTEGER,\n",
    "       \"People_12plus_Fully_Vaccinated_Unknown_2_dose_Manuf_Resident\" SMALLINT,\n",
    "       \"12plus_Doses_Administered_by_State_where_Administered\" INTEGER,\n",
    "       \"Doses_Administered_per_100k_of_12plus_pop_by_State_where_Administered\" INTEGER,\n",
    "       \"Doses_Delivered_per_100k_of_12plus_pop\" INTEGER\n",
    "    )\n",
    "    FILE PATHS 'kifs://vaccine/vaccine-us.csv'\n",
    "    FORMAT DELIMITED TEXT;\n",
    "    \"\"\")\n",
    "exec_result['status_info']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57617ac5",
   "metadata": {},
   "source": [
    "Once again, we present a simple approach to supply/demand -- but encourage the community consider this deeply and propose better or more fair mechanisms to calculate supply/demand. Your supply/demand mechanism just needs to output a supply and demand figure, source by source and destination by destination. We do this via two *Materialized Views.* We encourage you to read about these here: https://docs.kinetica.com/7.1/concepts/materialized_views/. They can be setup to automatically or periodically refresh based on underlying data changes, which is great for our use case."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c557a68d",
   "metadata": {},
   "source": [
    "### Supply"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "912119aa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'OK',\n",
       " 'message': '',\n",
       " 'data_type': 'execute_sql_response',\n",
       " 'response_time': 0.03353}"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_result = db.execute_sql(\"\"\"\n",
    "    CREATE OR REPLACE materialized VIEW DEMO_Vaccine_Distro.vaccine_supply_usa refresh ON change AS\n",
    "    (\n",
    "    select \n",
    "        Total_Doses_Delivered,\n",
    "        Total_Doses_Administered_by_State_where_Administered,\n",
    "        People_Fully_Vaccinated_by_State_of_Residence,\n",
    "        People_18plus_Fully_Vaccinated_by_State_of_Residence,\n",
    "        Total_Doses_Delivered - Total_Doses_Administered_by_State_where_Administered as CURRENT_VACCINE_INVENTORY,\n",
    "        int(0.23 * (Total_Doses_Delivered - Total_Doses_Administered_by_State_where_Administered)) as \"Donateable\" /* PER BIDEN DONATION ESTIMATES */\n",
    "    from DEMO_Vaccine_Distro.vaccine_utilization_usa\n",
    "    ) \n",
    "\"\"\")\n",
    "\n",
    "exec_result['status_info']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f420fe15",
   "metadata": {},
   "source": [
    "### Demand"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "b3596cfb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'ERROR',\n",
       " 'message': \"Error: 'SqlEngine: Object 'map_iso_alpha23' not found within 'DEMO_Vaccine_Distro' (S/SDc:1280); error in Job process'\",\n",
       " 'data_type': 'none',\n",
       " 'response_time': 0.01211}"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_result = db.execute_sql(\"\"\"\n",
    "    CREATE OR REPLACE materialized VIEW DEMO_Vaccine_Distro.vaccine_demand_intl refresh ON change AS\n",
    "    (\n",
    "    SELECT iso_code,\n",
    "                  location,\n",
    "                  countrymap.ISO_ALPHA_2 as country_iso2,\n",
    "                  countrymap.ISO_ALPHA_3 as country_iso3,\n",
    "                  int(population) as \"total_population\",\n",
    "                  int(people_fully_vaccinated) as \"fully_vaccinated_population\",\n",
    "                  int(new_cases_per_million) as \"new_cases_per_1mm\",\n",
    "                  int(total_deaths_per_million) as \"total_deaths_per_1mm\",\n",
    "                  int(population-people_fully_vaccinated) AS \"unvaccinated\",\n",
    "                  int(100*(population-people_fully_vaccinated)/population) AS \"unvaccinated_pct\",\n",
    "                  (select int(avg(100*(population-people_fully_vaccinated)/population)) from DEMO_Vaccine_Distro.global_covid_statistics) as \"avg_global_unvaccinated_pct\",\n",
    "                  if (people_fully_vaccinated , int(population-people_fully_vaccinated), int(0.92 * population) ) AS \"vaccinate_doses_required\"\n",
    "           FROM   DEMO_Vaccine_Distro.global_covid_statistics gstat,\n",
    "                  DEMO_Vaccine_Distro.map_iso_alpha23 countrymap\n",
    "           WHERE  date='2021-06-01'\n",
    "           AND    trim(countrymap.ISO_ALPHA_3) = trim(iso_code)\n",
    "\n",
    "    ) \n",
    "\"\"\")\n",
    "\n",
    "exec_result['status_info']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "77a95622",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'ERROR',\n",
       " 'message': \"Error: 'Failed to create external table: DEMO_Vaccine_Distro.airport_metadata_usa, Unable to load any files:  (E/Ec:285) (S/SDc:1493); error in Job process'\",\n",
       " 'data_type': 'none',\n",
       " 'response_time': 0.09932}"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_result = db.execute_sql(\"\"\"\n",
    "    CREATE MATERIALIZED EXTERNAL TABLE \"DEMO_Vaccine_Distro\".\"airport_metadata_usa\"\n",
    "    (\n",
    "       \"id\" INTEGER,\n",
    "       \"ident\" VARCHAR (8),\n",
    "       \"type\" VARCHAR (16, dict),\n",
    "       \"name\" VARCHAR (128),\n",
    "       \"latitude_deg\" DOUBLE,\n",
    "       \"longitude_deg\" DOUBLE,\n",
    "       \"elevation_ft\" SMALLINT,\n",
    "       \"continent\" VARCHAR (2),\n",
    "       \"country_name\" VARCHAR (16, dict),\n",
    "       \"iso_country\" VARCHAR (2),\n",
    "       \"region_name\" VARCHAR (32, dict),\n",
    "       \"iso_region\" VARCHAR (8, dict),\n",
    "       \"local_region\" VARCHAR (2),\n",
    "       \"municipality\" VARCHAR (64),\n",
    "       \"scheduled_service\" TINYINT,\n",
    "       \"gps_code\" VARCHAR (4),\n",
    "       \"iata_code\" VARCHAR (4),\n",
    "       \"local_code\" VARCHAR (8),\n",
    "       \"home_link\" VARCHAR (128),\n",
    "       \"wikipedia_link\" VARCHAR (128),\n",
    "       \"keywords\" VARCHAR (256),\n",
    "       \"score\" INTEGER,\n",
    "       \"last_updated\" VARCHAR (32)\n",
    "    )\n",
    "    FILE PATHS 'kifs://vaccine/airport_metadata_usa.csv'\n",
    "    FORMAT DELIMITED TEXT;\n",
    "    \"\"\")\n",
    "exec_result['status_info']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "cad0aeea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'ERROR',\n",
       " 'message': \"Error: 'Failed to create external table: DEMO_Vaccine_Distro.airport-to-country-map, Unable to load any files:  (E/Ec:285) (S/SDc:1493); error in Job process'\",\n",
       " 'data_type': 'none',\n",
       " 'response_time': 0.08992}"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_result = db.execute_sql(\"\"\"\n",
    "    CREATE MATERIALIZED EXTERNAL TABLE \"DEMO_Vaccine_Distro\".\"airport-to-country-map\"\n",
    "    (\n",
    "       \"ident\" VARCHAR (8) NOT NULL,\n",
    "       \"type\" VARCHAR (16, dict) NOT NULL,\n",
    "       \"name\" VARCHAR (128) NOT NULL,\n",
    "       \"elevation_ft\" SMALLINT,\n",
    "       \"continent\" VARCHAR (2) NOT NULL,\n",
    "       \"iso_country\" VARCHAR (2) NOT NULL,\n",
    "       \"iso_region\" VARCHAR (8, dict) NOT NULL,\n",
    "       \"municipality\" VARCHAR (64),\n",
    "       \"gps_code\" VARCHAR (4),\n",
    "       \"iata_code\" VARCHAR (4),\n",
    "       \"local_code\" VARCHAR (8),\n",
    "       \"coordinates\" VARCHAR (64) NOT NULL\n",
    "    )\n",
    "    FILE PATHS 'kifs://vaccine/airport-to-country-map.csv'\n",
    "    FORMAT DELIMITED TEXT;\n",
    "    \"\"\")\n",
    "exec_result['status_info']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad19a90c",
   "metadata": {},
   "source": [
    "After all the above loads, we should have all the setup we need for graph optimizations. When viewing GAdmin, it should look somewhat like this:\n",
    "\n",
    "![caption](images/gadmin.png) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fc2afd4",
   "metadata": {},
   "source": [
    "It would be too complicated to go from every single airport in the US to overseas airports, so it makes sense to consolidate supply within the US into, perhaps into several major regional airports. We start with a simple model where Washington DC Dulles International Airport serves as an East cost logistics hub (dont worrry, we'll iteratively expand this below to make it more realistic.) We create this graph with the following declaration:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "1cae4534",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'OK',\n",
       " 'message': '',\n",
       " 'data_type': 'execute_sql_response',\n",
       " 'response_time': 0.0351}"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_result = db.execute_sql(\"\"\"\n",
    "    SELECT\n",
    "        (dist)/(800*1000/3600) AS edge_weight,\n",
    "        geo AS edge_wktline\n",
    "    FROM\n",
    "\t(\n",
    "\t\tSELECT length/(ST_nPoints(geo)-1) AS dist, geo\n",
    "\t\tFROM\n",
    "\t\t\t(\n",
    "\t\t\t\tSELECT\n",
    "\t\t\t\t\tST_length(EDGE_WKTLINE,1) AS length,\n",
    "\t\t\t\t\tST_segmentize(EDGE_WKTLINE, 1000000, 1) AS geo\n",
    "\t\t\t\tFROM DEMO_Vaccine_Distro.airport_routes\n",
    "\t\t\t\tWHERE EDGE_NODE1_ID = 3714 and EDGE_NODE2_ID = 1382\n",
    "\t\t\t)\n",
    "\t) \"\"\")\n",
    "\n",
    "exec_result['status_info']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "a555984e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'OK',\n",
       " 'message': '',\n",
       " 'data_type': 'execute_sql_response',\n",
       " 'response_time': 1.00518}"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_result = db.execute_sql(\"\"\"\n",
    "    CREATE OR REPLACE DIRECTED GRAPH airport\n",
    "    (\n",
    "\tNODES => INPUT_TABLES\n",
    "    (\n",
    "        (\n",
    "            SELECT NODE_NAME, NODE_WKTPOINT\n",
    "            FROM DEMO_Vaccine_Distro.airport_nodes\n",
    "        ),\n",
    "        (\n",
    "            SELECT NODE_ID, NODE_WKTPOINT\n",
    "            FROM DEMO_Vaccine_Distro.airport_nodes\n",
    "        )\n",
    "    ),\n",
    "\tEDGES => INPUT_TABLE\n",
    "\t(\n",
    "\t\tSELECT EDGE_ID, EDGE_WKTLINE, EDGE_DIRECTION, EDGE_WEIGHT_VALUESPECIFIED\n",
    "\t\tFROM DEMO_Vaccine_Distro.airport_routes\n",
    "\t),\n",
    "\tOPTIONS => KV_PAIRS\n",
    "\t(\n",
    "\t\t'enable_graph_draw' = 'true',\n",
    "\t\t'graph_table' = 'DEMO_Vaccine_Distro.airport_graph'\n",
    "\t)\n",
    "    ) \"\"\")\n",
    "\n",
    "exec_result['status_info']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a05e9c71",
   "metadata": {},
   "source": [
    "And we then solve the graph with:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "b28847d1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'ERROR',\n",
       " 'message': \"Error: 'Solution table 'DEMO_Vaccine_Distro.airport_sssp' exists. Please remove it. (E/Ec:78) (S/SDc:1493); error in Job process'\",\n",
       " 'data_type': 'none',\n",
       " 'response_time': 0.00857}"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_result = db.execute_sql(\"\"\"\n",
    "    EXECUTE FUNCTION SOLVE_GRAPH\n",
    "    (\n",
    "        GRAPH => 'airport',\n",
    "        SOLVER_TYPE => 'SHORTEST_PATH',\n",
    "        SOURCE_NODES => INPUT_TABLE\n",
    "        (\n",
    "            SELECT 'IAD: Washington Dulles International Airport' AS NODE_NAME\n",
    "        ),\n",
    "        SOLUTION_TABLE => 'DEMO_Vaccine_Distro.airport_sssp',\n",
    "        OPTIONS => KV_PAIRS\n",
    "        (\n",
    "            'min_solution_radius' = '10000',\n",
    "            'max_solution_radius' = '12000',\n",
    "            'max_solution_targets' = '10',\n",
    "            'accurate_snaps' = 'false',\n",
    "            'output_edge_path' = 'true',\n",
    "            'output_wkt_path' = 'true'\n",
    "        )\n",
    "    )\n",
    "     \"\"\")\n",
    "\n",
    "exec_result['status_info']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c13dcfc0",
   "metadata": {},
   "source": [
    "![caption](images/pix1.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "263eeb47",
   "metadata": {},
   "source": [
    "![caption](images/pix2.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78ef35f9",
   "metadata": {},
   "source": [
    "Finally, we plot out the full route we would use for the round-trip:\n",
    "    \n",
    "![caption](images/path_iad.png)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33bb66bb",
   "metadata": {},
   "source": [
    "### Refining the Setup - Multiple US Supply Sites\n",
    "That was good, but lets continue to refine this. We can add a second departure hub in the US to route vaccine supply from the south, lets set up both IAD (Washington Dulles Airport) and DFW (Dallas Forth Worth Airport.)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10f46647",
   "metadata": {},
   "source": [
    "As before, we'll have a graph setup, and a graph solve. We set up as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ff2f6952",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'OK',\n",
       " 'message': '',\n",
       " 'data_type': 'execute_sql_response',\n",
       " 'response_time': 1.02343}"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_result = db.execute_sql(\"\"\"\n",
    "\n",
    "    CREATE OR REPLACE DIRECTED GRAPH airport\n",
    "    (\n",
    "        NODES => INPUT_TABLES\n",
    "        (\n",
    "            (\n",
    "                SELECT NODE_NAME, NODE_WKTPOINT\n",
    "                FROM DEMO_Vaccine_Distro.airport_nodes\n",
    "            ),\n",
    "            (\n",
    "                SELECT NODE_ID, NODE_WKTPOINT\n",
    "                FROM DEMO_Vaccine_Distro.airport_nodes\n",
    "            )\n",
    "        ),\n",
    "        EDGES => INPUT_TABLE\n",
    "        (\n",
    "            SELECT EDGE_ID, EDGE_WKTLINE, EDGE_DIRECTION, EDGE_WEIGHT_VALUESPECIFIED\n",
    "            FROM DEMO_Vaccine_Distro.airport_routes\n",
    "        ),\n",
    "        OPTIONS => KV_PAIRS\n",
    "        (\n",
    "            'merge_tolerance' = '0.0001',\n",
    "            'use_rtree' = 'false',\n",
    "            'min_x' = '-180',\n",
    "            'max_x' = '180',\n",
    "            'min_y' = '-90',\n",
    "            'max_y' = '90',\n",
    "            'export_create_results' = 'false',\n",
    "            'enable_graph_draw' = 'true',\n",
    "            'save_persist' = 'false',\n",
    "            'sync_db' = 'false',\n",
    "            'add_table_monitor' = 'false',\n",
    "            'graph_table' = 'DEMO_Vaccine_Distro.airport_graph',\n",
    "            'add_turns' = 'false',\n",
    "            'turn_angle' = '60.0',\n",
    "            'is_partitioned' = 'false'\n",
    "        )\n",
    "    )\n",
    "\n",
    "     \"\"\")\n",
    "\n",
    "exec_result['status_info']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be317b48",
   "metadata": {},
   "source": [
    "And we solve the graph as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ccac809e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'status': 'ERROR',\n",
       " 'message': \"Error: 'SqlEngine: Object 'airport_demand' not found within 'DEMO_Vaccine_Distro' (S/SDc:1280); error in Job process'\",\n",
       " 'data_type': 'none',\n",
       " 'response_time': 0.01753}"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_result = db.execute_sql(\"\"\"\n",
    "\n",
    "    EXECUTE FUNCTION MATCH_GRAPH\n",
    "    (\n",
    "        GRAPH => 'airport',\n",
    "        SOLVE_METHOD => 'MATCH_SUPPLY_DEMAND',\n",
    "        SAMPLE_POINTS => INPUT_TABLES\n",
    "        (\n",
    "            (\n",
    "                SELECT\n",
    "                    demand_id AS SAMPLE_DEMAND_ID,\n",
    "                    demand_wkt AS SAMPLE_DEMAND_WKTPOINT,\n",
    "                    demand_size AS SAMPLE_DEMAND_SIZE,\n",
    "                    supply_id AS SAMPLE_DEMAND_DEPOT_ID\n",
    "                FROM DEMO_Vaccine_Distro.airport_demand\n",
    "            ),\n",
    "            (\n",
    "                SELECT\n",
    "                    supply_id AS SAMPLE_SUPPLY_DEPOT_ID,\n",
    "                    supply_wkt AS SAMPLE_SUPPLY_WKTPOINT,\n",
    "                    supply_craft AS SAMPLE_SUPPLY_TRUCK_ID,\n",
    "                    supply_size AS SAMPLE_SUPPLY_TRUCK_SIZE\n",
    "                FROM DEMO_Vaccine_Distro.airport_supply\n",
    "            )\n",
    "        ),\n",
    "        SOLUTION_TABLE => 'DEMO_Vaccine_Distro.airport_msdo_multi1',\n",
    "        OPTIONS => KV_PAIRS\n",
    "        (\n",
    "            'partial_loading' = 'true',\n",
    "            'max_combinations' = '10000',\n",
    "            'aggregated_output' = 'true',\n",
    "            'output_tracks' = 'false',\n",
    "            'max_trip_cost' = '0.0',\n",
    "            'unit_unloading_cost' = '0.0',\n",
    "            'truck_service_limit' = '0.0',\n",
    "            'max_truck_stops' = '0',\n",
    "            'enable_truck_reuse' = 'false',\n",
    "            'left_turn_penalty' = '0.0',\n",
    "            'right_turn_penalty' = '0.0',\n",
    "            'intersection_penalty' = '0.0',\n",
    "            'sharp_turn_penalty' = '0.0'\n",
    "        )\n",
    "    )\n",
    "\n",
    "     \"\"\")\n",
    "\n",
    "exec_result['status_info']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81d8bd9b",
   "metadata": {},
   "source": [
    "### Taking this further\n",
    "\n",
    "Real life is complex. To make predictions or decisions, we create models to represent reality the best we can balancing trade-offs between correctness with complexity. So far, we've made a number of simplifying assumptions, but we can continue to refine the model to introduce more features and address nuances. Some reasonable next steps would be:\n",
    "\n",
    "* Come up with a more advanced supply model using CDC data but with more nuance around vaccine expirations, expected usage before expiration, etc.\n",
    "* Come up with a more advanced demand model using OWID data, but with your thoughts on prioriization schems -- do we prioritize the elderly population (more at risk), or prioritize the young (more likely to be outside and spread disease), or prioritize countries with the most deaths, or prioritize the countries with the least access to vaccination?\n",
    "* Add several more regional US centers, perhps one for the midwest (Chicago ORD) and one for the west (San Francisco SFO)\n",
    "* Consider that multi-hop drop-offs themselves require time, and thus, consider how many days to expiration are required on vaccines beyond which we should avoid transporting them"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a67d142",
   "metadata": {},
   "source": [
    "### References\n",
    "1. Travelling Salesperson Problem - https://en.wikipedia.org/wiki/Travelling_salesman_problem\n",
    "2. White House Fact Sheet: President Biden Announces Major Milestone in Administration’s Global Vaccination Efforts: More Than 100 Million U.S. COVID-⁠19 Vaccine Doses Donated and Shipped Abroad - https://www.whitehouse.gov/briefing-room/statements-releases/2021/08/03/fact-sheet-president-biden-announces-major-milestone-in-administrations-global-vaccination-efforts-more-than-100-million-u-s-covid-19-vaccine-doses-donated-and-shipped-abroad/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a383d54f",
   "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.6.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}