LearnTaQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Learn TaQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Of: Heb geen hekel aan TaQL*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a tutorial on TaQL. Parts of it require the latest version of Casacore, which you can install from https://github.com/casacore/casacore. You can follow this tutorial without installing casacore on your computer, since this tutorial runs on a web server."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "TaQL is part of [casacore](https://github.com/casacore/casacore), a set of libraries for radio astronomy data processing. TaQL stands for \"Table Query Language\", but it can be used also without tables."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Why this tutorial"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The official TaQL documentation is at http://casacore.github.io/casacore-notes/199.html. This tutorial assumes you don't know any TaQL yet, and guides you through its features."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After going through this tutorial, you will be able to:\n",
    " * Convert directions from J2000 to Azimuth-Elevation, or any other frame.\n",
    " * Compute the elevation of bright sources during the time of your observation.\n",
    " * Automatically flag bad data.\n",
    " * Save a subset of a data set for debugging.\n",
    "\n",
    "Since you will better understand the format of your data set (Measurement Set), you will be able to write better scripts in e.g. Python. So let's get started!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to suggest improvements to this notebook, you can do so through the [source on Github](https://github.com/tammojan/taql-jupyter/blob/master/LearnTaQL.ipynb) (or just send a mail to [Tammo Jan](mailto:dijkema@astron.nl))."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using this notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook is written in Jupyter, but it contains a binding to the TaQL kernel. If you highlight a code cell, you can press **Shift-Enter** to evaluate it in TaQL. If you press **Alt-Enter**, the cell will evaluate and a new cell is inserted below it.\n",
    "\n",
    "You can evaluate all the TaQL commands already present in this notebook. To understand the commands, you can try to predict the outcome of a statement before evaluating it. Also, you are encouraged to change the commands: you can enter any valid TaQL-statement in this notebook.\n",
    "\n",
    "Navigation in Jupyter notebooks can be tricky. If you are in *command mode* (not editing a cell), lots of keyboard shortcuts are active, like \"`j`\" and \"`k`\" for scrolling, \"`a`\" for inserting a cell, or \"`dd`\" for deleting one (these should be familiar if you know `vi`). If you want to type in a cell, make sure to be in *edit mode* by checking you see a blinking cursor. \n",
    "\n",
    "  * To go to edit mode, press Enter or double click a cell.\n",
    "  * To go back to command mode, press Esc or single click another cell."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise1\"></a>**Exercise 1**: what will happen if you type \"`add`\" in command mode, and try it to see if you're right.</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise2\"></a>**Exercise 2**: Evaluate the cell below, and verify that TaQL will produce the expected output.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "25+17"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Where to use TaQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TaQL is part of casacore, and can be used in many places where casacore is used."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The first place where you'll find TaQL is in the command-line client `taql`. This is a good place for interactively fiddling with measurement sets."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also, you can view the result or TaQL statements in `casaviewer`, which gives a nice GUI for inspecting data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TaQL can be used easily from `python-casacore`, formerly `pyrap`. The following code shows a brief example (you cannot execute this code, since this tutorial is only about TaQL, not about python-casacore).\n",
    "```\n",
    "from casacore import tables as pt\n",
    "\n",
    "# Select rows with many flags\n",
    "t=pt.taql('SELECT * FROM demodata.MS WHERE ntrue(FLAG)>100')\n",
    "\n",
    "# Now t is a table object which can be used in the usual python-casacore way:\n",
    "for row in t:\n",
    "    print t.getcol('ANTENNA1')\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TaQL can also be used from `casapy`. To do a query within `casapy`, the table must first be opened:\n",
    "```\n",
    "tb.open('demodata.MS')\n",
    "res = tb.taql('SELECT * FROM demodata.MS WHERE ntrue(FLAG)>100')\n",
    "flaggedants = res.getcol('ANTENNA1')\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TaQL as a calculator"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Although it's not the main intended use, you can use TaQL like a regular calculator:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "6*7"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Exponentiation is done using `**` (as in Python). The operator `^` is a bitwise *xor* \u2013 if you don't know what that is, just don't use `^`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "5^3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "5**3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TaQL can do complex numbers as well, where the complex unit can be given as `i` or `j`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "(28+29i)/(8-1j)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Most functions you expect to work are actually there:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sin(pi()/2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise3\"></a>**Exercise 3**: try some functions and see if they work. Does TaQL respect operator precedence?.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"newin21\">A full list of functions can be shown by typing `help functions`. This feature is new in casacore 2.2.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "help functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Arrays and indexing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unlike for example SQL, TaQL supports lists (or actually arrays):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "[10,34,21,0,-3.4,8]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A lot of functions exist specifically for lists:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "mean([10,34,21,0,-3.4,8])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "stddev([10,34,21,0,-3.4,8])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise4\"></a>**Exercise 4**: use the function `sumsqr` (sum of squares) to compute the length of the vector *(3, 4)*.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Arrays can be created using the python way of creating **ranges**. `0:5` creates a range `0,1,2,3,4` (note that the end is *exclusive*). You can use a third part to specify the step size: `0:16:5` creates the range `0,5,10,15`. To make an array from a range, enclose it in square brackets."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise5\"></a>**Exercise 5**: create the array `[13, 17, 21, \u2026 45, 49]` and compute its average.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "Multiple ranges can be combined:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "[1:5, 10, 15:25:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Indexing** arrays also works with ranges. The start defaults to 0, the end defaults to the end of the array. For example, `a[4:13:3]` takes the elements with index 4, 7 and 10. Again, note that the end is *exclusive*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "[10,34,21,0,-3.4,8][::2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"newin21\">Higher dimensional arrays are supported, by nesting brackets. This is a new feature in version 2.2.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "[[37,  1,  3], [34,  5,  7]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In earlier versions of TaQL, higher dimensional arrays must be specified by giving the data and the shape separately:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "array([37,1,3,34,5,7], 2, 3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you see, this yields an array of two rows and three columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note**: the command line version `taql` prints this as\n",
    "```\n",
    "Axis Lengths: [3, 2]  (NB: Matrix in Row/Column order)\n",
    "[37, 34\n",
    " 1, 5\n",
    " 3, 7]\n",
    "```\n",
    "In this notebook, it is printed in Column/Row order, just like in python and C."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Indexing higher arrays works with `[\u2026, \u2026]`. Leaving out an indexing expression (but including the comma) selects the entire axis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise6\"></a>**Exercise 6**: add an indexing expression in the array below to select the row with values *(37, 1, 3)*. Afterwards, change the indexing expression to select the column with values *(1, 5)*.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "[[37,  1,  3], [34,  5,  7]][ *** ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To take the mean over a subset of the axes of a higher dimensional array, use `mean`**`s`** (just like in NumPy):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "means(array([37,1,3,34,5,7], 2, 3), 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "means(array([37,1,3,34,5,7], 2, 3), 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Most operators and functions act sensibly when you apply them to an array:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "3 + [1,2,3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sin([pi()/2, pi()/4, pi()/3, pi()/6])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sqrt(2)/2 == sin(pi()/4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Why isn't `sin(pi()/4)` equal to `sqrt(2)/2`? The answer is floating point precision: computers don't know exact numbers."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TaQL has a function `near` to compare if to numbers are relatively near, with a default tolerance of *10<sup>-13</sup>*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "near(sqrt(2)/2, sin(pi()/4), 1.e-13)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For testing with a relative tolerance of *10<sup>-5</sup>* (useful for single precision numbers), you can also use the shorthand operator `~=` (which resembles \u2245):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sqrt(2)/2 ~= sin(pi()/4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sets and intervals"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To test whether a value is in some set, you can use the keyword `in`, where the set is specified like a range, or an array:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "4 in 4:10:3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "7 in [4:10:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also test whether a number is in a continuous interval. For open intervals (start and end are exclusive), use `<` and `>`, for closed intervals use `{` and `}` (start and end are inclusive)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The TaQL version of *4 \u2208 (3,4]* is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "4 in <3,4}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The TaQL version of *4 \u2208 (3,4)* is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "4 in <3,4>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One of the sides of the interval can be omitted to mean it can be infinity. The TaQL version of *4 \u2208 (3,\u221e)* is"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "4 in <3,>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The left-hand side can be an array as well:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "[2:15:3] in <1,5>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pretty-printing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The function `str` exists to format anything nicely into a string. The optional second argument to this function specifies the formatting. You can specify how wide the printed string should be and how many digits should be printed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "str(pi(), 20.8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you speak C, you can also use `printf`-style formatting:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "str(pi(), \"%20.8f\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "str((19+9j)/7, \"%.3f + %.3fi\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The function `str()` can also be used to format dates, times and angles. We'll handle examples in the sections on dates, times and angles, respectively."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Strings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As in almost any languages, strings are made with either single or double quotes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\"spam\" == 'spam'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some string functions are `len()`, `substr()`, `upper()`, `lower()`, `capitalize()` and `trim()`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " <div class=\"newin21\">You can view all string functions with `help functions string`:</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "help functions string"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise7\"></a>**Exercise 7**: Predict the output of the following function and verify that you're right.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "capitalize(\n",
    "  substr(\n",
    "    trim(\"   the quick brown fox jumps over the lazy dog    \"), \n",
    "    0, 20\n",
    "  )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Matching strings (checking whether a string matches a pattern) can be done with the `~` operator, followed by the pattern. The pattern can be of three types:\n",
    " * `p/pattern/` Unix pattern-like syntax, with `*`, `?` and `{}`\n",
    " * `m/pattern/` Partial regular expression\n",
    " * `f/pattern/` Full regular expression\n",
    " \n",
    "After the slash the suffix `i` can be given to allow a case-insensitive match. If you're looking for a `/` character, use `#` or `%` around the pattern instead (you cannot escape it).\n",
    " \n",
    "For most queries, the `p/pattern/` style should be sufficient."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To see whether a string starts with `3C` or `3c`, the following statements are equivalent:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\"3C196\" ~ p/3c*/i"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\"3c196\" ~ m/^3c/i"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\"3c196\" ~ f/3c.*/i"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Units"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TaQL has basic support for units, even Imperial ones."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "4m + 3in"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SI prefixes `p`, `n`, `u` (for \u00b5), `m`, `c`, `d`, `da`, `h`, `k`, `M`, `G`, `T`, `P` (and lots more) can be used.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise8\"></a>**Exercise 8**: Evaluate with taql if a *millifoot* is in the open interval between 100 and 200 *nano-mile* (this is an accidental feature of TaQL).</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If parsing runs into trouble, add quotes around the unit. Simple unit conversions can be done as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "(1'pc/h') 'km/s'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Units are case sensitive: `1AU` (one astronomical unit) is quite different from `1au` (one *atto* atomical mass)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In expressions with different units, the units are converted to conform:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "(1AU/c()) min"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some checking of units is performed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sqrt(3km)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sqrt(9'km.km')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Non-given units are assumed to be the same as the first given unit:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "1+2deg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "[1,2,3m,4]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise9\"></a>**Exercise 9**: Compute with TaQL how far (in meters) you go if you travel at 20km/h for 18 seconds.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"newin21\">All units can be printed with the following command:</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "show units"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Angles"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Angles can be given in `hmd` or `dms` format, or in radians or degrees:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "4h56m03.5 + 4d12m43.7 + 1 deg - 0.3 rad"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To format an angle in hours, minutes and seconds, use the function `hms()`. Similarly, to format it in degrees, minutes, seconds, use `dms()`. To format an array with RA-DEC values, use `hdms()`, which formats even elements with `hms()` and odd elements with `dms()`.\n",
    "\n",
    "For even more control over angle formatting (for example, for setting the precision), you can use `str()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "str(0.3 rad, \"ANGLE|15\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise10\"></a>**Exercise 10**: put the coordinates of Westerbork, *(6.60417\u00b0, 52.91692\u00b0)* in an array, and format it in the conventional RA-DEC notation.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Functions for calculations with angles are built in, for example for computing the angular distance between two positions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "angdist([6.60417, 52.91692] deg, [0, 90] deg) deg"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Literal dates can be entered directly into TaQL, for example using the [ISO 8601](https://xkcd.com/1179/) standard (which was introduced after the first version of casacore)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "1981-04-01"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Warning: the leading zeros are essential here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "1981-4-1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Values can be converted to dates with the function `date()` or `datetime()`. Without arguments, this gives the current date (or date + time)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "date(0.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can guess from the above, dates are internally stored as modified Julian Date."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To convert a date to a pretty-printed date, you can use `cdate()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cdate(date(0.))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarly for showing times there is `ctime()`, and for showing both date and time there is `cdatetime()`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For total control over date formatting, use `str()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "str(date(), \"DMY|DAY\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculations on dates work like you would expect:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "date() - 1981-01-04"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise11\"></a>**Exercise 11**: when were you 10.000 days old?</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Times"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The function `time()` gives the time (current time if no arguments given) in *radians*. This makes it possible to write times in the same way as angles: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "time() > 12h38m"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise12\"></a>**Exercise 12**: check that `datetime() - date()` (which gives a result in days) is consistent with `time()`.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To convert a time to a string, use the function `ctime()` (remember it as \"*see time*\"), or `cdatetime()` to include the date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ctime(5000 s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Measures"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The prefix `meas.` is for functions linking to CasaCore's *measures* library. These functions make it possible to convert measures like directions, epochs, and positions from one reference frame to another."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Times"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To do really accurate computations with times, one should use Measures. When you specify a time, it is interpreted with respect to the `UTC` frame (Coordinated Universal Time). To convert to a different frame, e.g. `TAI` (International Atomic Time), use `meas.epoch`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cdatetime(meas.epoch(\"TAI\", 2016-01-28 15:00:00, \"UTC\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the default time frame is `UTC`, it may be omitted."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you see, there is a discrepancy between `UTC` and `TAI`. This is due to leap seconds.  These leap seconds are announced only half a year before (for example, here's the [announcement](ftp://hpiers.obspm.fr/iers/bul/bulc/bulletinc.49) for 2015's leap second). This is one of the reasons that you sometimes get warnings if your casacore data directory is out of date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "meas.epoch(\"TAI\",\"30-Jun-2015\")-meas.epoch(\"UTC\",\"30-Jun-2015\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you see, a leap second was inserted in `UTC` after June 2015. Leap seconds are not applied in the `TAI` standard, otherwise the standards are the same."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise13\"></a>**Exercise 13**: calculate the number of seconds between `1997-01-01 00:00 UTC` and `2000-01-01 00:00 UTC`, and explain why the answer is *not* `94608000 s`.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"newin21\">Available time frames are shown with:</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "show meastypes epoch"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some coordinate frames require a position, which can be given as an extra argument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "meas.epoch(\"TAI\", datetime(), \"UTC\", \"WSRT\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Positions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Positions on Earth must be given with respect to a reference frame. Two important reference frames are `WGS84` and `ITRF`. Positions can be converted between reference frames with the function `meas.position` (or `meas.pos`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "meas.position(\"ITRF\", [6.60417, 52.91692] deg, \"WGS\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since `WGS` is the default, it may be omitted."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The positions of most radio telescopes are predefined:\n",
    "\"`ALMA`\", \"`ARECIBO`\", \"`ATCA`\", \"`BIMA`\", \"`CLRO`\", \"`DRAO`\", \"`DWL`\", \"`GB`\", \"`GBT`\", \"`GMRT`\", \"`IRAM PDB`\", \"`IRAM_PDB`\", \"`JCMT`\", \"`MOPRA`\", \"`MOST`\", \"`NRAO12M`\", \"`NRAO_GBT`\", \"`PKS`\", \"`SAO SMA`\", \"`SMA`\", \"`VLA`\", \"`VLBA`\", \"`WSRT`\", \"`ATF`\", \"`ATA`\", \"`CARMA`\", \"`ACA`\", \"`OSF`\", \"`OVRO_MMA`\", \"`EVLA`\", \"`ASKAP`\", \"`APEX`\", \"`SMT`\", \"`NRO`\", \"`ASTE`\", \"`LOFAR`\", \"`MeerKAT`\", \"`KAT-7`\", \"`EVN`\", \"`LWA1`\", \"`PAPER_SA`\", \"`PAPER_GB`\", \"`e-MERLIN`\", \"`MERLIN2`\", \"`Effelsberg`\", \"`MWA32T`\", \"`AMI-LA`\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The output of meas.position defaults to be in meters from the origin. By appending `LL` to the code for the frame, you get it in long/lat."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "meas.position(\"WGSLL\", \"WSRT\") deg"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise14\"></a>**Exercise 14**: compute the angular distance between ALMA and MeerKAT.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Directions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Casacore knows a lot of reference frames. Conversions between them are done with `meas.dir`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "meas.dir('B1950', [2rad, 1rad], 'J2000')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since `J2000` is the default, it may be omitted."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Several directions have been predefined, like all the planets, the sun and the moon, and standard sources (\"`CasA`\", \"`CygA`\", \"`TauA`\", \"`VirA`\", \"`HerA`\", \"`HydA`\", \"`PerA`\")."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to convert to a coordinate frame which is tied to the Earth, it is necessary to also specify a time and a position (in that order):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "meas.dir(\"AZEL\", \"Jupiter\", datetime(), \"WSRT\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The frame of the date and time can be given explicitly (and should be if they are not `UTC` and `ITRF`, respectively):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "meas.dir(\"AZEL\", \"Jupiter\", 2000-01-01 00:00, \"TAI\", \n",
    "         [3826577.110, 461022.900, 5064892.758] m, \"ITRF\") deg"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "<div class=\"newin21\">Supported reference frames for direction can be shown with:</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "show meastypes direction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For most direction types, an abbreviated function exists. For example, `meas.azel(\u2026)` is the same as `meas.dir(\"AZEL\", \u2026)`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise15\"></a>**Exercise 15**: What is the current elevation of the sun, as seen from `ALMA`?</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is a special function to see when a source will be visible on a given day:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "meas.riseset(\"SUN\", date(), [6.60417, 52.91692] deg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise16\"></a>**Exercise 16**: when will Cassiopeia A rise tomorrow?</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To select data from a table, use the `SELECT \u2026 FROM \u2026` command. In the working directory of this notebook, we copied the table `Observatories`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT FROM Observatories"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The command above selects all rows in the Observatories table, but shows no output. To show some output, we need to specify which columns to show. `Name` and `Source` are columns in the table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT Name, Source FROM Observatories"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To limit the amount of rows shown, use `LIMIT`, followed by the number of rows you want to see:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT Name, Source FROM Observatories LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Like in SQL, you can give an offset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT Name, Source FROM Observatories LIMIT 2 OFFSET 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Instead of the explicit names of the columns, you can also show all of them, by giving an asterisk (`*`):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "SELECT * FROM Observatories LIMIT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"newin21\">With `SHOW TABLE \u2026`, you can see the columns that are present in a table.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SHOW TABLE Observatories"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise17\"></a>**Exercise 17**: show the name and latitude of the sixth observatory.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also computations can be done on columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise18\"></a>**Exercise 18**: use `cdate()` to format the `MJD` column in the Observatories table. Use `LIMIT 1` to prevent your screen filling up.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To change the column name, you can use `AS`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT cdate(MJD) AS mydate FROM Observatories LIMIT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Usually you don't want to see the entire table, but only some rows that are of interest to you. To make such a selection, use `WHERE`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT * FROM Observatories WHERE Name in [\"LOFAR\", \"WSRT\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise19\"></a>**Exercise 19**: select all observatories with latitude above *45\u00b0*.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "Ordering of a table is done by adding an `ORDER BY \u2026` part, optionally followed by `DESC` to specify descending order."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To show the top-3 lowest telescopes in this table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT Name, Height FROM Observatories WHERE Height>1 ORDER BY Height LIMIT 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Measurement sets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The most important use case of casacore tables are in the Measurement Set, the common data format for radio astronomy observations. The full format is described in http://casacore.github.io/casacore-notes/229 and can have telescope-specific extensions. The full format for LOFAR measurement sets is described at [astron.nl](http://www.lofar.org/operations/lib/exe/fetch.php?media=public:documents:ms2_description_for_lofar_2.08.00.pdf)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook, the measurement set `demodata.MS` is available to play with."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT * FROM demodata.MS LIMIT 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each row of this table contains the visibilities for a pair of antennas at a given time slot. The visibilities are an array, containing all polarizations and all frequencies:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT ANTENNA1, ANTENNA2, TIME, DATA FROM demodata.MS LIMIT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise20\"></a>**Exercise 20**: How many channels does `demodata.MS` have? You can use the function `shape()` to show this.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More specifically, each row contains the data of a single baseline and timeslot (and band/field):\n",
    " * `DATA`: 2D Complex array (nfreq*4); visibilities (XX, XY, YX, YY)\n",
    " * `FLAG`: 2D Bool array; flag per visibility (`True`=bad)\n",
    " * `UVW`: U, V, W coordinate (meters)\n",
    " * `WEIGHT_SPECTRUM`: weight per visibility\n",
    " * `TIME`: time in MJD (seconds)\n",
    " * `ANTENNA1`: first station in baseline\n",
    " * `ANTENNA2`: second station in baseline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To compute the mean absolute value (over the channels-axis) of the XX-correlation of the first baseline that is not an autocorrelation, you could use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT mean(abs(DATA[,0])) FROM demodata.MS WHERE ANTENNA1!=ANTENNA2 LIMIT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we have not used the flags here, so also flagged data is part of this average. We will get back to this."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise21\"></a>**Exercise 21**: How many rows contain no more than 8 flags? (Use the function `ntrue()`.)</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise22\"></a>**Exercise 22**: Does this measurement set contain any weights that are bigger than `1.0`? (Use the function `any()`.)</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The special command `DISTINCT` exists to omit duplicate rows. This can be used to show all unique time slots."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT DISTINCT TIME FROM demodata.MS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise23\"></a>**Exercise 23**: Compute the elevation of `CygA` during the observation in `demodata.MS`, as viewed from the central `LOFAR` position. Use `meas.AZEL(`*direction*, *time*, *position*`)` and fill in the `TIME` from the measurement set. Use `LIMIT 3`, because the conversion may take some time.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the section on MSCal, a much easier way is shown to do this."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise24\"></a>**Exercise 24**: For the first time slot in `demodata.MS`, compute the mean (`gmean`) (over baseline) of the XX and YY correlation of `DATA`, by taking `0.5*SUM(DATA[,::3])`. Note that for linearly polarized data, this gives the source Stokes I flux at the phase center.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the example above neglects flags, we'll get back to that."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The output of a query is a table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The output of a query is itself a table. This statement is very powerful, so it deserves a section of its own."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since the output of a query is a table, you can use this to suppress the actual output and only show the number of rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT FROM (SELECT DISTINCT TIME FROM demodata.MS)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise25\"></a>**Exercise 25**: How many different baselines were there in this observation? (Hint: a baseline is a distinct combination of `ANTENNA1`, `ANTENNA2`.)</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also save the resulting table from a query, using the `GIVING` keyword:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT FROM demodata.MS WHERE TIME == 29-Mar-2013/10:03:30.002781 GIVING onetime.MS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This results in a *reference table* in your working directory, which only contains links to the original table. You can also create a new standalone table by specifying `AS PLAIN`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT FROM demodata.MS WHERE TIME == 29-Mar-2013/10:03:30.002781\n",
    "GIVING onetime-plain.MS AS PLAIN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The output of a query is a set"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The output of a query with one column can act like a set. So wherever you can add a set, you can add a query which gives one column. A useful example of this is:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT FROM demodata.MS WHERE TIME IN [SELECT DISTINCT TIME FROM demodata.MS LIMIT 3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise26\"></a>**Exercise 26**: Extract the first 5 distinct time slots from `demodata.MS` and save them as `first5.MS` (so that you could send this data with an e-mail, for example for debugging).</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Grouping"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Aggregating data can be done with `GROUP BY \u2026` expression. Many aggregate functions exist, whose name is generally the same as the function name for arrays, but prefixed with a `g`. Examples of aggregate functions are `gntrue()`, `gsum()`, `gmean()`, `gcount()`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To count the number of flags per time slot:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT TIME, gntrue(FLAG) FROM demodata.MS GROUP BY TIME"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise27\"></a>**Exercise 27**: For all baselines (identified with `ANTENNA1`, `ANTENNA2`), compute the number of flagged visibilities (use `gntrue`). Exclude autocorrelations.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will get to the proper way of selecting baselines in the section [Baseline Selection Syntax](#Baseline-selection-syntax).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To select only groups which satisfy a certain criterium, use the `HAVING` keyword after the `GROUP BY` part."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following query will select baselines where the mean of the absolute values of visibilities is large:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT ANTENNA1, ANTENNA2, gmean(abs(DATA)) AS mean FROM demodata.MS \n",
    "  GROUP BY ANTENNA1, ANTENNA2 HAVING mean>30.0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise28\"></a>**Exercise 28**: Select all time slots where more than 10000 visibilities are flagged.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of all aggregate function, a function with `s` appended exists to aggregate over only a selected number of axes. These functions only aggregate over the row axis. For example, `gntrues` does not aggregate over the polarization and channel axis:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT gntrues(FLAG) FROM demodata.MS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that when using an aggregate function, leaving out the `GROUP BY` part will return the aggregate over the entire table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise29\"></a>**Exercise 29**: use `sums(gntrues, \u2026)` to get the total amount of flags per channel.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting from multiple tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Selecting from multiple tables can be done by putting them next to each other. This only works if they have the same number of rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT gcount() FROM demodata.MS as a0, demodata.MS as a1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, you can concatenate two tables, so put them 'below each other'. This only works if they have the same columns. As you can see in the following query, the number of rows doubles if you select a table twice:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT gcount() FROM [demodata.MS, demodata.MS]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Advanced**: Counting the number of flags per antenna is a bit involved, since the antenna is sometimes in `ANTENNA1` and sometimes in `ANTENNA2`. This can be circumvented by doubling the table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT ANTENNA, gsum(numflags) AS numflags FROM \n",
    "  [\n",
    "     [ SELECT ANTENNA1 AS ANTENNA, ntrue(FLAG) AS numflags FROM demodata.MS ],\n",
    "     [ SELECT ANTENNA2 AS ANTENNA, ntrue(FLAG) AS numflags FROM demodata.MS ]\n",
    "  ]\n",
    "GROUP BY ANTENNA"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Masked data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"newin21\">This feature is new in casacore 2.2.</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Like in numpy, TaQL has a concept of *masked array*. This is particularly useful for working with flagged data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To compute the sum of non-flagged data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT gmean(abs(DATA[FLAG])) FROM demodata.MS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise30\"></a>**Exercise 30**: For the entire `demodata.MS`, compute the mean (`gmean`) (over baseline and polarization) of the XX and YY correlation of `DATA`, by using `gmeans(DATA[FLAG][::3])`. Note that for linearly polarized data, this gives the source Stokes I flux at the phase center.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The weighted mean can be computed as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT gsum((DATA*WEIGHT_SPECTRUM)[FLAG])/gsum(WEIGHT_SPECTRUM[FLAG]) FROM demodata.MS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To handle the case where the denominator in the query above is zero, you can use the function `iif()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT gsum((DATA*WEIGHT_SPECTRUM)[FLAG]) / \n",
    "       iif(gsum(WEIGHT_SPECTRUM[FLAG])==0, 1, gsum(WEIGHT_SPECTRUM[FLAG]))\n",
    "FROM demodata.MS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Advanced**: Note that in the query above, the expression `gsum(WEIGHT_SPECTRUM[FLAG])` is evaluated twice. This can be a bit slow. The following query with subqueries circumvents this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT t1.sd / iif(t2.sw==0, 1, t2.sw) FROM\n",
    "  [ SELECT gsum((DATA*WEIGHT_SPECTRUM)[FLAG]) AS sd FROM demodata.MS] t1,\n",
    "  [ SELECT gsum(WEIGHT_SPECTRUM[FLAG])        AS sw FROM demodata.MS] t2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Subtables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The columns `ANTENNA1` and `ANTENNA2` contain numbers, not antenna names. All information about the antennas is stored in the **subtable** `ANTENNA`. You can see the subtables of a table already in a terminal, by `cd`-ing into the measurement set and doing `ls`. In TaQL, a subtable is separated by double colons:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT * FROM demodata.MS::ANTENNA LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise31\"></a>**Exercise 31**: show the name (`NAME`) of all core stations that are in this measurement set (their name starts with \"`CS`\").</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can show visibilities with the antenna names of the two antennas of the baseline by using a subquery (the subquery is one of those queries that returns one column, so acts like a list). Don't panic when seeing this query, if you look five lines down you'll see an easier way to get the same result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT [SELECT NAME FROM ::ANTENNA][ANTENNA1],\n",
    "       [SELECT NAME FROM ::ANTENNA][ANTENNA2],\n",
    "       DATA\n",
    "FROM demodata.MS LIMIT 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## MSCal"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Selecting the name of an antenna is something that you may want to do more often than you want to type the full subquery from the example above.\n",
    "\n",
    "A set of functions specific for measurement sets is in the function group `mscal`. For example, to show the antenna name like above, you can use `ant1name()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT mscal.ant1name(), mscal.ant2name() FROM demodata.MS limit 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"newin21\">All functions are shown with:</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "show functions mscal"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise32\"></a>**Exercise 32**: Show the total number of flags where the name of antenna 1 is `CS001HBA0` or the name of antenna2 is `CS001HBA0`. Use `mscal.ant1name()`.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise33\"></a>**Exercise 33**: Select the RR polarization of the `DATA` using `mscal.stokes(DATA, 'RR')`.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Getting back to Exercise 23, we promised an easy way to show the elevation of `CygA` over the time range of a measurement set. Since MSCal knows about the metadata like position and times, the following statement suffices:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT mscal.azel(\"CygA\") FROM demodata.MS LIMIT 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Baseline selection syntax"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The casa [baseline selection syntax](http://casacore.github.io/casacore-notes/263) is implemented in the function `mscal.baseline()`. It takes as argument a string with the selection query, and yields true or false."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To select all cross correlations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT FROM demodata.MS WHERE mscal.baseline(\"*&\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or to select all baselines which do not contain `CS004*` or `CS001*`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT FROM demodata.MS WHERE mscal.baseline(\"!CS004*,CS001*&&*\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To validate that the syntax you're trying is doing what you want, you check whether there are any rows yet with antenna names `CS004*` or `CS001*` (the result should be 0 rows, because `mscal.baseline` filters them out):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT DISTINCT mscal.ant1name(), mscal.ant2name() \n",
    "FROM demodata.MS WHERE mscal.baseline(\"!CS004*,CS001*&&*\") \n",
    "AND (mscal.ant1name() ~ p/CS004*/ OR mscal.ant1name() ~ p/CS001*/) limit 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Updating tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "The `UPDATE` statement exists to update the data in a table. Before we start ruining our demo data set, let's back it up:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT FROM demodata.MS GIVING backup.MS AS PLAIN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"newin21\">The syntax of the `UPDATE` command is shown by:</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "show command update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "The following `UPDATE` command clears all flags:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "UPDATE demodata.MS SET FLAG=False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can restore the flags from the backup with a `FROM` clause in the `UPDATE` command:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "UPDATE demodata.MS SET FLAG=t1.FLAG FROM backup.MS AS t1 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Check that this has worked (the number of flags should be greater than zero)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "SELECT gntrue(FLAG) FROM demodata.MS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The update command is usually handy for updating the `FLAG` column, but also updating columns is often used:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "UPDATE demodata.MS SET CORRECTED_DATA = DATA - MODEL_DATA"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise34\"></a>**Exercise 34**: Recompute the `UVW` column by setting it to the output of `mscal.NEWUVW()`.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When updating flags, be sure to use the `||` (\"or\") operator to keep the existing flags:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "UPDATE demodata.MS SET FLAG = FLAG || (abs(DATA)>100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, you can use the masked version of the `UPDATE` statement, which only updates the values where the flag is True. Since we only want to update non-flagged values, we invert `FLAG`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "UPDATE demodata.MS SET FLAG[!FLAG] = abs(DATA)>100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise35\"></a>**Exercise 35**: use `UPDATE \u2026 WHERE TIME IN (\u2026)` to flag all timeslots with more than 10000 flags. See exercise 28.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inserting data, logging what you did"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Usually, inserting data into a measurement set quickly invalidates the structure of the MS. But a very important subtable of the MS should be updated: the `HISTORY` subtable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "INSERT INTO demodata.MS::HISTORY (APPLICATION, MESSAGE,TIME) VALUES ('TaQL', 'Flagged some data', mjd())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"exercise\"><a name=\"exercise36\"></a>**Exercise 36**: Insert a message into the history subtable, and `SELECT` some rows from the history table to check that your message landed there.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Alter table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<div class=\"newin21\">In recent versions of TaQL, the command `ALTER TABLE` was added.</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "help command ALTER TABLE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add a column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ALTER TABLE demodata.MS ADD COLUMN CORR_DATA2 Complex [shape=[4,9]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rename a column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ALTER TABLE demodata.MS RENAME COLUMN CORRECTED_DATA TO MY_DATA"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Where to go from here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Congratulations on reaching the end of this tutorial (I assume you actually followed the tutorial and not just scrolled down). You now know the basics of TaQL, but there is more."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A good way to start exploring is firing up the `taql` executable in your terminal, and typing `help`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "help"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On CEP3, the newest `taql` is not yet installed; you can use `source ~dijkema/casacore/initcasacore.sh` instead. After that, you'll have the newest `taql` available."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The official TaQL documentation is at http://casacore.github.io/casacore-notes/199 (in [html](http://casacore.github.io/casacore-notes/199.html) or [pdf](http://casacore.github.io/casacore-notes/199.pdf))."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also, some slides from Ger van Diepen are available at his website http://www.astron.nl/~gvd"
   ]
  }
 ],
 "metadata": {
  "git": {
   "suppress_outputs": true
  },
  "kernelspec": {
   "display_name": "TaQL",
   "language": "",
   "name": "taql"
  },
  "language_info": {
   "mimetype": "text/plain",
   "name": "taql"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}