sql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "# reset defalult plotting values\n",
    "plt.rcParams['figure.figsize'] = (10, 7)\n",
    "plt.rc('font', family='sans-serif')\n",
    "plt.rc('axes', labelsize=14)\n",
    "plt.rc('axes', labelweight='bold')\n",
    "plt.rc('axes', titlesize=16)\n",
    "plt.rc('axes', titleweight='bold')\n",
    "plt.rc('axes', linewidth=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# SQL\n",
    "## The language of relational databases\n",
    "\n",
    "![](https://wiki.postgresql.org/images/3/30/PostgreSQL_logo.3colors.120x120.png)\n",
    "\n",
    "\n",
    "### Prof. Robert Quimby\n",
    "&copy; 2019 Robert Quimby"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## In this tutorial you will..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- hear about the role of databases\n",
    "- learn how to retrieve records (data) from a database\n",
    "- run basic SQL queries on an example database\n",
    "- use ADQL to query the Gaia database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Databases"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](http://www.sqlitetutorial.net/wp-content/uploads/2015/12/RDBMS-Client-Server-Architecture.jpg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Structured Query Language (SQL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Resources\n",
    "  - [SQL tutorial](https://www.w3schools.com/sql/default.asp) from w3schools.com"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Basic grammar for SQL queries\n",
    "\n",
    "- **SELECT** [comma separated list of values to select] \n",
    "  - **FROM** [names of the tables to select values from]\n",
    "  - **WHERE** [conditions on what values to select]\n",
    "  - **ORDER BY** [options to sort the selected values]\n",
    "  - **LIMIT** [option to limit the number of records retrieved]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## SQLite"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "https://www.sqlite.org/index.html\n",
    "\n",
    "![](http://www.sqlitetutorial.net/wp-content/uploads/2015/12/What-is-SQLite.jpg)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## SQLite database queries in python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# connect to a database\n",
    "import sqlite3\n",
    "conn = sqlite3.connect(????)\n",
    "\n",
    "# data science package to work with query results\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Schema of tables in the example database ('example.db')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **stars** table holds data for 9110 stars in the Yale Bright Star Catalog. The table has the following columns:\n",
    "- **id** - (integer) a unique number for each star in the table\n",
    "- **name** - (text) name of the star (if any)\n",
    "- **ra** - (real) right ascension in degrees \n",
    "- **dec** - (real) declination in degrees \n",
    "- **vmag** - (real) V-band magnitude of the star\n",
    "- **sp_type** - (text) spectral type of the star"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "The **spec_class** table holds data for (some of the) different spectral types including:\n",
    "- **sp_class** - Morgan–Keenan (Yerkes) spectral class (OBAFGKM)\n",
    "- **lum_class** - luminosity class (V=Main Sequence; III=Giant; I=Super Giant)\n",
    "- **temperature** - typical photospheric temperature\n",
    "- **abs_mag** - typical absolute magnitude"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## SELECTing data from a database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# load everything from the stars table\n",
    "pd.read_sql_query(????, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# load just the ra and dec of stars in the stars table\n",
    "pd.read_sql_query(\"SELECT ???? FROM stars\", conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# load everything from the spec_class table\n",
    "pd.read_sql_query(\"SELECT * FROM spec_class\", conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Using the WHERE clause"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "pd.read_sql_query(\"SELECT * FROM stars WHERE ????\", conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Combining conditions with AND"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT ra, dec\n",
    "FROM stars\n",
    "WHERE vmag < 2.5\n",
    "  AND ra > 65\n",
    "  AND ra < 95\n",
    "  AND dec > -10\n",
    "  AND dec < 10\n",
    "\"\"\"\n",
    "stars = pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# plot the R.A., Dec. of the selected stars\n",
    "plt.axes(aspect='equal')\n",
    "plt.plot(stars['ra'], stars['dec'], 'ro')\n",
    "plt.gca().invert_xaxis()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# select by luminosity class\n",
    "query = \"SELECT * FROM spec_class WHERE lum_class='{}'\"\n",
    "ms = pd.read_sql_query(query.format('V'), conn)\n",
    "g = pd.read_sql_query(query.format('III'), conn)\n",
    "sg = pd.read_sql_query(query.format('I'), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# plot an HR diagram\n",
    "plt.plot(ms['temperature'], ms['abs_mag'], 'go')\n",
    "plt.plot(g['temperature'], g['abs_mag'], 'ro')\n",
    "plt.plot(sg['temperature'], sg['abs_mag'], 'bo')\n",
    "plt.gca().invert_xaxis()\n",
    "plt.gca().invert_yaxis()\n",
    "plt.xscale('log')\n",
    "plt.xlabel('Temperature (K)')\n",
    "plt.ylabel('Absolute V-band Magnitude');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Computing values in queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# use SQL as a calculator\n",
    "pd.read_sql_query(\"SELECT ????\", conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# retrieve computed values\n",
    "pd.read_sql_query(\"SELECT ???? FROM stars \", conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Joining tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT name, ra, dec, vmag, temperature, abs_mag\n",
    "FROM stars, spec_class\n",
    "WHERE \n",
    "  ????\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Relation between observed mag, absolute mag, and distance"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "$$D = 10^{ (m - M + 5)/5 }$$"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Adding functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "# raising a number to some power in numpy\n",
    "????"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# add the POWER function to queries\n",
    "conn.create_function(\"power\", ????, ????)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT name, ra, dec, vmag, abs_mag, POWER(10, (vmag - abs_mag + 5) / 5) AS dist\n",
    "FROM stars, spec_class\n",
    "WHERE \n",
    "  sp_type = sp_class || lum_class\n",
    "ORDER BY dist \n",
    "LIMIT 10\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Astronomical Data Query Language (ADQL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Based on SQL but designed for astronomers\n",
    "\n",
    "[Gaia query web interface](http://gea.esac.esa.int/archive/)\n",
    "- [Basic ADQL syntax](https://gea.esac.esa.int/archive-help/adql/index.html) from Gaia web pages\n",
    "- [Example ADQL queries](https://gea.esac.esa.int/archive-help/adql/examples/index.html) from Gaia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from astroquery.gaia import Gaia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# query Gaia database to select stars in the Gaia DR2 catalog \n",
    "# near (R.A, Dec.) = (0, 0)\n",
    "# near the North Celestial Pole\n",
    "\n",
    "query = \"\"\"SELECT ra, dec\n",
    "FROM gaiadr2.gaia_source\n",
    "WHERE 1=CONTAINS(\n",
    "  POINT('ICRS', ra, dec),\n",
    "  CIRCLE('ICRS', 0, 0, 0.25))\n",
    "  AND phot_bp_mean_mag < 15\n",
    "\"\"\"\n",
    "job = Gaia.launch_job_async(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# get the search results\n",
    "gaia = job.get_results()\n",
    "gaia"
   ]
  }
 ],
 "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.5.4"
  },
  "livereveal": {
   "scroll": "True",
   "start_slideshow_at": "selected"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}