Ops MGMT.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# FireCARES ops management notebook\n",
    "\n",
    "### Using this notebook\n",
    "\n",
    "In order to use this notebook, a single production/test web node will need to be bootstrapped w/ ipython and django-shell-plus python libraries.  After bootstrapping is complete and while forwarding a local port to the port that the ipython notebook server will be running on the node, you can open the ipython notebook using the token provided in the SSH session after ipython notebook server start.\n",
    "\n",
    "#### Bootstrapping a prod/test node\n",
    "\n",
    "To bootstrap a specific node for use of this notebook, you'll need to ssh into the node and forward a local port # to localhost:8888 on the node.\n",
    "\n",
    "e.g. `ssh firecares-prod -L 8890:localhost:8888` to forward the local port 8890 to 8888 on the web node, assumes that the \"firecares-prod\" SSH config is listed w/ the correct webserver IP in your `~/.ssh/config`\n",
    "\n",
    "- `sudo chown -R firecares: /run/user/1000` as the `ubuntu` user\n",
    "- `sudo su firecares`\n",
    "- `workon firecares`\n",
    "- `pip install -r dev_requirements.txt`\n",
    "- `python manage.py shell_plus --notebook --no-browser --settings=firecares.settings.local`\n",
    "\n",
    "At this point, there will be a mention of \"The jupyter notebook is running at: http://localhost:8888/?token=XXXX\".  Copy the URL, but be sure to use the local port that you're forwarding instead for the connection vs the default of 8888 if necessary.\n",
    "\n",
    "Since the ipython notebook server supports django-shell-plus, all of the FireCARES models will automatically be imported.  From here any command that you execute in the notebook will run on the remote web node immediately."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fire department management"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Re-generate performance score for a specific fire department\n",
    "\n",
    "Useful for when a department's FDID has been corrected.  Will do the following:\n",
    "\n",
    "1. Pull NFIRS counts for the department (cached in FireCARES database)\n",
    "1. Generate fires heatmap\n",
    "1. Update department owned census tracts geom\n",
    "1. Regenerate structure hazard counts in jurisdiction\n",
    "1. Regenerate population_quartiles materialized view to get safe grades for department\n",
    "1. Re-run performance score for the department"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "from firecares.tasks import update\n",
    "from firecares.utils import dictfetchall\n",
    "from django.db import connections\n",
    "from django.conf import settings\n",
    "from django.core.management import call_command\n",
    "from IPython.display import display\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "FireCARES id: 92616\n",
      "https://firecares.org/departments/92616\n"
     ]
    }
   ],
   "source": [
    "fd = {'fdid': '18M04', 'state': 'WA'}\n",
    "nfirs = connections['nfirs']\n",
    "department = FireDepartment.objects.filter(**fd).first()\n",
    "fid = department.id\n",
    "print 'FireCARES id: %s' % fid\n",
    "print 'https://firecares.org/departments/%s' % fid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'count': 24L, 'fdid': u'18M04', 'state': u'WA', 'year': 2003.0},\n",
       " {'count': 31L, 'fdid': u'18M04', 'state': u'WA', 'year': 2004.0},\n",
       " {'count': 43L, 'fdid': u'18M04', 'state': u'WA', 'year': 2005.0},\n",
       " {'count': 52L, 'fdid': u'18M04', 'state': u'WA', 'year': 2006.0},\n",
       " {'count': 38L, 'fdid': u'18M04', 'state': u'WA', 'year': 2007.0},\n",
       " {'count': 19L, 'fdid': u'18M04', 'state': u'WA', 'year': 2008.0},\n",
       " {'count': 41L, 'fdid': u'18M04', 'state': u'WA', 'year': 2009.0},\n",
       " {'count': 36L, 'fdid': u'18M04', 'state': u'WA', 'year': 2010.0},\n",
       " {'count': 32L, 'fdid': u'18M04', 'state': u'WA', 'year': 2011.0},\n",
       " {'count': 56L, 'fdid': u'18M04', 'state': u'WA', 'year': 2014.0},\n",
       " {'count': 49L, 'fdid': u'18M04', 'state': u'WA', 'year': 2015.0}]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total fires: 421\n",
      "\n",
      "CPU times: user 8.68 ms, sys: 69 µs, total: 8.74 ms\n",
      "Wall time: 35.3 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# Get raw fire incident counts (prior to intersection with )\n",
    "\n",
    "with nfirs.cursor() as cur:\n",
    "    cur.execute(\"\"\"\n",
    "        select count(1), fdid, state, extract(year from inc_date) as year\n",
    "        from fireincident where fdid=%(fdid)s and state=%(state)s\n",
    "        group by fdid, state, year\n",
    "        order by year\"\"\", fd)\n",
    "    fire_years = dictfetchall(cur)\n",
    "    display(fire_years)\n",
    "    print 'Total fires: %s\\n' % sum([x['count'] for x in fire_years])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT count(1) as count, extract(year from a.alarm) as year, COALESCE(y.risk_category, 'N/A') as risk_level\n",
      "FROM joint_buildingfires a\n",
      "LEFT JOIN\n",
      "    (SELECT state, fdid, inc_date, inc_no, exp_no, x.parcel_id, x.risk_category\n",
      "        FROM ( SELECT *\n",
      "            FROM joint_incidentaddress a\n",
      "            LEFT JOIN parcel_risk_category_local using (parcel_id)\n",
      "        ) AS x\n",
      "    ) AS y\n",
      "USING (state, fdid, inc_date, inc_no, exp_no)\n",
      "WHERE a.state = %(state)s AND a.fdid = %(fdid)s AND extract(year FROM a.inc_date) IN %(years)s\n",
      "GROUP BY y.risk_category, extract(year from a.alarm)\n",
      "ORDER BY extract(year from a.alarm) DESC\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[{'count': 11L, 'risk_level': u'N/A', 'year': 2015.0},\n",
       " {'count': 1L, 'risk_level': u'High', 'year': 2014.0},\n",
       " {'count': 17L, 'risk_level': u'Low', 'year': 2014.0},\n",
       " {'count': 3L, 'risk_level': u'Medium', 'year': 2014.0},\n",
       " {'count': 8L, 'risk_level': u'N/A', 'year': 2014.0},\n",
       " {'count': 15L, 'risk_level': u'Low', 'year': 2011.0},\n",
       " {'count': 2L, 'risk_level': u'Medium', 'year': 2011.0},\n",
       " {'count': 2L, 'risk_level': u'N/A', 'year': 2011.0},\n",
       " {'count': 11L, 'risk_level': u'Low', 'year': 2010.0},\n",
       " {'count': 2L, 'risk_level': u'Medium', 'year': 2010.0},\n",
       " {'count': 8L, 'risk_level': u'N/A', 'year': 2010.0},\n",
       " {'count': 1L, 'risk_level': u'High', 'year': 2009.0},\n",
       " {'count': 5L, 'risk_level': u'Low', 'year': 2009.0},\n",
       " {'count': 2L, 'risk_level': u'Medium', 'year': 2009.0},\n",
       " {'count': 13L, 'risk_level': u'N/A', 'year': 2009.0},\n",
       " {'count': 5L, 'risk_level': u'Low', 'year': 2008.0},\n",
       " {'count': 3L, 'risk_level': u'N/A', 'year': 2008.0},\n",
       " {'count': 1L, 'risk_level': u'High', 'year': 2007.0},\n",
       " {'count': 12L, 'risk_level': u'Low', 'year': 2007.0},\n",
       " {'count': 4L, 'risk_level': u'Medium', 'year': 2007.0},\n",
       " {'count': 12L, 'risk_level': u'Low', 'year': 2006.0},\n",
       " {'count': 5L, 'risk_level': u'Medium', 'year': 2006.0},\n",
       " {'count': 6L, 'risk_level': u'N/A', 'year': 2006.0},\n",
       " {'count': 1L, 'risk_level': u'High', 'year': 2005.0},\n",
       " {'count': 5L, 'risk_level': u'Low', 'year': 2005.0},\n",
       " {'count': 9L, 'risk_level': u'Medium', 'year': 2005.0},\n",
       " {'count': 4L, 'risk_level': u'N/A', 'year': 2005.0},\n",
       " {'count': 5L, 'risk_level': u'Low', 'year': 2004.0},\n",
       " {'count': 6L, 'risk_level': u'Medium', 'year': 2004.0},\n",
       " {'count': 3L, 'risk_level': u'N/A', 'year': 2004.0},\n",
       " {'count': 7L, 'risk_level': u'Low', 'year': 2003.0},\n",
       " {'count': 1L, 'risk_level': u'Medium', 'year': 2003.0},\n",
       " {'count': 2L, 'risk_level': u'N/A', 'year': 2003.0}]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total geocoded fires: 192\n",
      "\n",
      "CPU times: user 6.27 ms, sys: 0 ns, total: 6.27 ms\n",
      "Wall time: 36.8 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# Get building fire counts after structure hazard level calculations\n",
    "sql = update.STRUCTURE_FIRES\n",
    "\n",
    "print sql\n",
    "\n",
    "with nfirs.cursor() as cur:\n",
    "    cur.execute(sql, dict(fd, years=tuple([x['year'] for x in fire_years])))\n",
    "    fires_by_hazard_level = dictfetchall(cur)\n",
    "    display(fires_by_hazard_level)\n",
    "    print 'Total geocoded fires: %s\\n' % sum([x['count'] for x in fires_by_hazard_level])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Completed 12.9 KiB/12.9 KiB (125.9 KiB/s) with 1 file(s) remaining\r",
      "upload: ../../../tmp/92616-building-fires.csv to s3://firecares-test/92616-building-fires.csv\r\n"
     ]
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "select alarm, a.inc_type, alarms,ff_death, oth_death, ST_X(geom) as x, st_y(geom) as y, COALESCE(y.risk_category, 'Unknown') as risk_category\n",
    "from buildingfires a\n",
    "LEFT JOIN (\n",
    "    SELECT state, fdid, inc_date, inc_no, exp_no, x.geom, x.parcel_id, x.risk_category\n",
    "    FROM (\n",
    "        SELECT * FROM incidentaddress a\n",
    "        LEFT JOIN parcel_risk_category_local using (parcel_id)\n",
    "    ) AS x\n",
    ") AS y\n",
    "    USING (state, fdid, inc_date, inc_no, exp_no)\n",
    "WHERE a.state = %(state)s and a.fdid = %(fdid)s\"\"\"\n",
    "\n",
    "with nfirs.cursor() as cur:\n",
    "    cur.execute(sql, fd)\n",
    "    rows = dictfetchall(cur)\n",
    "    \n",
    "out_name = '{id}-building-fires.csv'.format(id=fid)\n",
    "full_path = '/tmp/' + out_name\n",
    "\n",
    "with open(full_path, 'w') as f:\n",
    "    writer = csv.DictWriter(f, fieldnames=[x.name for x in cur.description])\n",
    "    writer.writeheader()\n",
    "    writer.writerows(rows)\n",
    "\n",
    "# Push building fires to S3\n",
    "!aws s3 cp $full_path s3://firecares-test/$out_name --acl=\"public-read\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "updating NFIRS counts for 92616\n",
      "...updated NFIRS counts for 92616\n"
     ]
    }
   ],
   "source": [
    "update.update_nfirs_counts(fid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No census geom - Poulsbo Fire Department (Kitsap Fire District 18) (92616)\n"
     ]
    }
   ],
   "source": [
    "update.calculate_department_census_geom(fid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "risk_level\n",
       "High       4\n",
       "Low       94\n",
       "Medium    34\n",
       "N/A       60\n",
       "Name: count, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "updating performance score for 92616\n",
      "Error updating DIST score: Traceback (most recent call last):\n",
      "  File \"firecares/tasks/update.py\", line 130, in update_performance_score\n",
      "    dist = dist_model(floor_extent=False, **counts)\n",
      "  File \"/webapps/firecares/src/fire-risk/fire_risk/models/DIST/DIST.py\", line 90, in __init__\n",
      "    raise NotEnoughRecords\n",
      "NotEnoughRecords\n",
      ".\n",
      "Error updating DIST score: Traceback (most recent call last):\n",
      "  File \"firecares/tasks/update.py\", line 130, in update_performance_score\n",
      "    dist = dist_model(floor_extent=False, **counts)\n",
      "  File \"/webapps/firecares/src/fire-risk/fire_risk/models/DIST/DIST.py\", line 425, in __init__\n",
      "    beyond, **kwargs)\n",
      "  File \"/webapps/firecares/src/fire-risk/fire_risk/models/DIST/DIST.py\", line 90, in __init__\n",
      "    raise NotEnoughRecords\n",
      "NotEnoughRecords\n",
      ".\n",
      "Error updating DIST score: Traceback (most recent call last):\n",
      "  File \"firecares/tasks/update.py\", line 130, in update_performance_score\n",
      "    dist = dist_model(floor_extent=False, **counts)\n",
      "  File \"/webapps/firecares/src/fire-risk/fire_risk/models/DIST/DIST.py\", line 90, in __init__\n",
      "    raise NotEnoughRecords\n",
      "NotEnoughRecords\n",
      ".\n",
      "clearing High level from 92616 due to missing categories in aggregation\n",
      "updating fdid: 92616 - All performance score from: 26.0 to 27.0.\n",
      "...updated performance score for 92616\n"
     ]
    }
   ],
   "source": [
    "# Fire counts by hazard level over all years, keep in mind that the performance score model will currently ONLY work\n",
    "# hazard levels w/ \n",
    "display(pd.DataFrame(fires_by_hazard_level).groupby(['risk_level']).sum()['count'])\n",
    "\n",
    "update.update_performance_score(fid)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## User management"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Whitelist"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Django Shell-Plus",
   "language": "python",
   "name": "django_extensions"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}