Traffic Accidents.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Visualizing Traffic Accidents Around LA with IBM Cloud SQL Query and PixieDust"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook contains a demonstration on how to use IBM Cloud SQL Query (SQL Query) in a Jupyter Notebook with PixieDust to visualize traffic accidents on a map throughout LA. We'll use SQL Query to search for traffic accidents occurring between 5pm and 8pm and have victims whose ages are between 20 and 35. We've taken care of uploading the data set to IBM Cloud Object Storage (COS) for you so you don't need to download and then upload the data set to your COS bucket.\n",
    "\n",
    "To use this notebook, you'll need (in this order):\n",
    "\n",
    "- IBM Cloud Object Storage - lite plan\n",
    "- IBM Cloud SQL Query\n",
    "\n",
    "Once you've provisioned the SQL Query service, click the **Open UI** button. This will generate a COS bucket for you that will store the results of your SQL queries. We'll need this bucket in order tell SQL Query where to store and retrieve the result sets. \n",
    "\n",
    "To get started, run the next cell to download the latest versions of ibmcloudsql and PixieDust."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "!pip install --upgrade ibmcloudsql\n",
    "!conda install -c conda-forge pyarrow\n", 
    "!pip install --upgrade pixiedust"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import both into the notebook:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibmcloudsql\n",
    "import pixiedust"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to keep your credentials safe, import `getpass`, which will store your IBM Cloud API Key so it's not visible to people viewing the notebook. You can get the API Key from [Manage > Security > Platform API](https://console.bluemix.net/iam/#/apikeys) Keys at the top of your IBM Cloud account.\n",
    "\n",
    "Using `getpass`, you can enter any prompt you'd like as a string. Once you run the cell, a box will appear to store your key. Paste your IBM Cloud API Key into the box and hit return. Now, all you have to do is use the variable `cloud_api_key` to use it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import getpass\n",
    "cloud_api_key = getpass.getpass('Enter your IBM Cloud API Key')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll need two more pieces of information before we can start querying the data.\n",
    "\n",
    "**1) IBM Cloud SQL Query CRN (cloud resource number)**\n",
    "\n",
    "To get the CRN, go to your SQL Query service page. Click on the **Manage** tab and under _REST API_ there is a button **Instance CRN**. Click that to copy the CRN for the service. Then add that to the variable `sql_crn`.\n",
    "\n",
    "**2) SQL Query generated COS bucket URL**\n",
    "\n",
    "We suggest you use the COS bucket generated by SQL Query to keep things simple. But, you can use any bucket in COS to store your results. To get that COS bucket URL, go to COS then look for the generated bucket. Then click the kabob menu button at the end of that bucket name. You'll have the option to view the **Bucket SQL URL**. Once that's clicked, you will see a pop-up window with the URL. Add that to the variable `sql_cos_endpoint`.\n",
    "\n",
    "We've added the suffix `/accidents` at the end of this URL. This will be the prefix of all the SQL query results that will be saved as CSV files in that bucket."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_crn = '<your_SQL_Query_CRN>' \n",
    "sql_cos_endpoint = 'cos://us-south/<your_COS_sql_bucket>/accidents'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To have access to the SQL Query API functions, you'll run `ibmcloudsql.SQLQuery` with your API Key, CRN, and COS endpoint. You'll then have access to the `run_sql` method to run your SQL queries on CSV data.  \n",
    "\n",
    "The following query gets the time, area, age (between 20-35), victim sex, and location of accidents between 5pm and 8pm. \n",
    "\n",
    "**Note:** The URL in `data_source` is the traffic collision data. We've used the same URL for subsequent queries. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sqlClient = ibmcloudsql.SQLQuery(cloud_api_key, sql_crn, sql_cos_endpoint)\n",
    "\n",
    "data_source = \"cos://us-geo/sqldata-032018/Traffic_Collision_Data_from_2010_to_Present.csv\"\n",
    "\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "    `Time Occurred` AS time, \n",
    "    `Area Name` AS area, \n",
    "    `Victim Age` AS age, \n",
    "    `Victim Sex` AS sex, \n",
    "    `Location` AS location \n",
    "FROM  {}\n",
    "WHERE \n",
    "    `Time Occurred` >= 1700 AND `Time Occurred` <= 2000 AND \n",
    "    `Victim Age` >= 20 AND `Victim Age` <= 35\n",
    "\"\"\".format(data_source)\n",
    "\n",
    "traffic_collisions = sqlClient.run_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After the query runs, we can look at a sample of the results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "traffic_collisions.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQL Query can also handle more advanced queries like CTEs (common table expressions). In the following example, the CTE formats the _location_ column of the previous query and divides the coordinates into separate latitude and longitude columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sqlClient = ibmcloudsql.SQLQuery(cloud_api_key, sql_crn, sql_cos_endpoint)\n",
    "\n",
    "data_source = \"cos://us-geo/sqldata-032018/Traffic_Collision_Data_from_2010_to_Present.csv\"\n",
    "\n",
    "query = \"\"\"\n",
    "WITH location AS ( \n",
    "    SELECT \n",
    "        id, \n",
    "        cast(split(coordinates, ',')[0] as float) as latitude, \n",
    "        cast(split(coordinates, ',')[1] as float) as longitude \n",
    "    FROM (SELECT \n",
    "            `Dr Number` as id, \n",
    "            regexp_replace(Location, '[()]', '') as coordinates \n",
    "        FROM {0}\n",
    "    ) \n",
    ") \n",
    "SELECT  \n",
    "    d.`Dr Number` as id, \n",
    "\td.`Date Occurred` as date, \n",
    "    d.`Time Occurred` AS time, \n",
    "    d.`Area Name` AS area, \n",
    "    d.`Victim Age` AS age, \n",
    "    d.`Victim Sex` AS sex, \n",
    "    l.latitude, \n",
    "    l.longitude \n",
    "FROM {0} AS d \n",
    "    JOIN \n",
    "    location AS l \n",
    "    ON l.id = d.`Dr Number` \n",
    "WHERE \n",
    "    d.`Time Occurred` >= 1700 AND \n",
    "    d.`Time Occurred` <= 2000 AND \n",
    "    d.`Victim Age` >= 20 AND \n",
    "    d.`Victim Age` <= 35 AND \n",
    "    l.latitude != 0.0000 AND \n",
    "    l.latitude != 0.0000\n",
    "\"\"\".format(data_source)\n",
    "\n",
    "traffic_location = sqlClient.run_sql(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "traffic_location.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using PixieDust's `display` feature, we can view the locations of these traffic accidents on a map.\n",
    "\n",
    "Select **Map** as chart type and **mapbox** as renderer and configure the map view options as follows:\n",
    "* **Keys**: `latitude`,  `longitude`\n",
    "* **Values**: `id`, `age`, `sex`, `date`\n",
    "    \n",
    "Hover over a marker without a number to display the age, gender and accident date. Zoom in to explore the map in more detail."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pixiedust": {
     "displayParams": {
      "handlerId": "mapView",
      "keyFields": "latitude,longitude",
      "mapboxtoken": "pk.eyJ1IjoibWFwYm94IiwiYSI6ImNpejY4M29iazA2Z2gycXA4N2pmbDZmangifQ.-g_vE53SD2WrJ6tFX7QHmA",
      "valueFields": "age,sex,date"
     }
    }
   },
   "outputs": [],
   "source": [
    "display(traffic_location)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.5 with Spark 2.1",
   "language": "python",
   "name": "python3-spark21"
  },
  "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.5.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}