heasarc_TAP_queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Accessing  HEASARC tables through the TAP with ADQL\n",
    "\n",
    "We have used the __[Table Access Protocol](http://www.ivoa.net/documents/TAP/)__ (TAP) protocol in several other notebooks for basic queries.  Here, we expand on its usage and that of the __[Astronomical Data Query Language](http://www.ivoa.net/documents/latest/ADQL.html)__ (ADQL) that it uses.  \n",
    "\n",
    "* [1. Basic](#basic) Table Access Protocol queries\n",
    "* [2. Cross-correlating](#cc) our own catalog with a HEASARC catalog\n",
    "* [3. Combining](#combo) data from multiple catalogs and cross-correlating\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import numpy\n",
    "## There are a number of relatively unimportant warnings that \n",
    "## show up, so for now, suppress them:\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "## The main HTTP request tool we will use:\n",
    "import requests\n",
    "## For simple astropy tables\n",
    "import astropy, io\n",
    "\n",
    "## For handling ordinary astropy Tables\n",
    "from astropy.table import Table\n",
    "\n",
    "## For handling VO table type objects\n",
    "from astropy.io import votable as apvot"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"basic\"></a>\n",
    "\n",
    "# 1. Basic Table Access Protocol queries\n",
    "\n",
    "A TAP query is the most powerful way to search a catalog.  \n",
    "\n",
    "A Simple Cone Search only allows you to ask for a position and radius:  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<SkyCoord (ICRS): (ra, dec) in deg\n",
      "    (202.484167, 47.230556)>\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "&lt;Table masked=True length=10&gt;\n",
       "<table id=\"table4377489136\" class=\"table-striped table-bordered table-condensed\">\n",
       "<thead><tr><th>name</th><th>ra</th><th>dec</th><th>bmag</th><th>radial_velocity</th><th>radial_velocity_error</th><th>redshift</th><th>class</th><th>Search_Offset</th></tr></thead>\n",
       "<thead><tr><th></th><th>deg</th><th>deg</th><th></th><th>km / s</th><th>km / s</th><th></th><th></th><th></th></tr></thead>\n",
       "<thead><tr><th>object</th><th>float64</th><th>float64</th><th>float32</th><th>int32</th><th>int32</th><th>float64</th><th>int32</th><th>float64</th></tr></thead>\n",
       "<tr><td>13267+4631</td><td>202.20650</td><td>46.25850</td><td>15.10</td><td>0</td><td>0</td><td>--</td><td>9999</td><td>59.4301</td></tr>\n",
       "<tr><td>N5173</td><td>202.10529</td><td>46.59082</td><td>14.12</td><td>2467</td><td>26</td><td>--</td><td>6300</td><td>41.4067</td></tr>\n",
       "<tr><td>N5198</td><td>202.54745</td><td>46.66996</td><td>13.30</td><td>2569</td><td>25</td><td>--</td><td>6300</td><td>33.7355</td></tr>\n",
       "<tr><td>N5169</td><td>202.04237</td><td>46.67154</td><td>14.70</td><td>2482</td><td>40</td><td>--</td><td>6200</td><td>38.1107</td></tr>\n",
       "<tr><td>I4263</td><td>202.13883</td><td>46.92671</td><td>15.40</td><td>2663</td><td>25</td><td>--</td><td>6200</td><td>23.0534</td></tr>\n",
       "<tr><td>N5194</td><td>202.46823</td><td>47.19815</td><td>9.03</td><td>474</td><td>23</td><td>--</td><td>6200</td><td>2.0500</td></tr>\n",
       "<tr><td>N5195</td><td>202.49491</td><td>47.26792</td><td>10.94</td><td>558</td><td>23</td><td>--</td><td>6200</td><td>2.2841</td></tr>\n",
       "<tr><td>1325+4754</td><td>201.87771</td><td>47.64124</td><td>--</td><td>18107</td><td>179</td><td>--</td><td>9999</td><td>34.8278</td></tr>\n",
       "<tr><td>1325+4754</td><td>201.95234</td><td>47.64138</td><td>--</td><td>18296</td><td>11</td><td>--</td><td>9999</td><td>32.7636</td></tr>\n",
       "<tr><td>N5229</td><td>203.51178</td><td>47.91539</td><td>14.60</td><td>365</td><td>10</td><td>--</td><td>6200</td><td>58.4686</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "<Table masked=True length=10>\n",
       "   name        ra      dec      bmag  ... redshift class Search_Offset\n",
       "              deg      deg            ...                             \n",
       "  object    float64  float64  float32 ... float64  int32    float64   \n",
       "---------- --------- -------- ------- ... -------- ----- -------------\n",
       "13267+4631 202.20650 46.25850   15.10 ...       --  9999       59.4301\n",
       "     N5173 202.10529 46.59082   14.12 ...       --  6300       41.4067\n",
       "     N5198 202.54745 46.66996   13.30 ...       --  6300       33.7355\n",
       "     N5169 202.04237 46.67154   14.70 ...       --  6200       38.1107\n",
       "     I4263 202.13883 46.92671   15.40 ...       --  6200       23.0534\n",
       "     N5194 202.46823 47.19815    9.03 ...       --  6200        2.0500\n",
       "     N5195 202.49491 47.26792   10.94 ...       --  6200        2.2841\n",
       " 1325+4754 201.87771 47.64124      -- ...       --  9999       34.8278\n",
       " 1325+4754 201.95234 47.64138      -- ...       --  9999       32.7636\n",
       "     N5229 203.51178 47.91539   14.60 ...       --  6200       58.4686"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import astropy.coordinates as coord\n",
    "coord=coord.SkyCoord.from_name(\"m51\")\n",
    "print(coord)\n",
    "params = {'table': 'zcat', 'RA':coord.ra.deg , 'DEC':coord.dec.deg, 'SR':1}\n",
    "r = requests.get('https://heasarc.gsfc.nasa.gov/cgi-bin/vo/cone/coneGet.pl', params=params)\n",
    "table=Table.read(io.BytesIO(r.content))\n",
    "table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the TAP, you can refine the search based on any other attribute in the given catalog.  \n",
    "\n",
    "The basics of ADQL:\n",
    "\n",
    "* *SELECT &ast; FROM catalog as cat* says you want all (\"&ast;\") columns from the catalog called \"catalog\", which you will refer to below by the more compact name of \"cat\", \n",
    "* *WHERE cat.bmag < 14* says that you want to retrieve only those entries in the catalog whose bmag column has a value less than 14\n",
    "\n",
    "There are many other options.  Instead of returning all columns, you can *SELECT cat.RA, cat.DEC, cat.bmag from catalog as cat...* to only return the columns you're interested in.\n",
    "\n",
    "You can also append *ORDER by cat.bmag* to return the result sorted ascending by one of the columns, adding *DESC* to the end for descending. \n",
    "\n",
    "A few special functions in the ADQL allow you to query regions:\n",
    "\n",
    "* *WHERE contains( point('ICRS', cat.ra, cat.dec), circle('ICRS', 210.5, -6.5, 0.5))=1*\n",
    "\n",
    "is how you would ask for any catalog entries whose RA,DEC lie within a circular region defined by RA,DEC 210.5,-6.5 and a radius of 0.5 (all in degrees).  The 'ICRS' specifies the coordinate system.  \n",
    "\n",
    "See the ADQL documentation for more.\n",
    "\n",
    "With these basics, we do the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "&lt;Table masked=True length=3&gt;\n",
       "<table id=\"table4377485608\" class=\"table-striped table-bordered table-condensed\">\n",
       "<thead><tr><th>ra</th><th>dec</th><th>radial_velocity</th></tr></thead>\n",
       "<thead><tr><th>float64</th><th>float64</th><th>int32</th></tr></thead>\n",
       "<tr><td>202.46823226999999</td><td>47.198148719999999</td><td>474</td></tr>\n",
       "<tr><td>202.49490508</td><td>47.267920400000001</td><td>558</td></tr>\n",
       "<tr><td>202.54744757</td><td>46.669958979999997</td><td>2569</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "<Table masked=True length=3>\n",
       "        ra                dec         radial_velocity\n",
       "     float64            float64            int32     \n",
       "------------------ ------------------ ---------------\n",
       "202.46823226999999 47.198148719999999             474\n",
       "      202.49490508 47.267920400000001             558\n",
       "      202.54744757 46.669958979999997            2569"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## These parameters are defined in the TAP standard.  \n",
    "tap_params = {\n",
    "    \"request\":\"doQuery\",  # for requests, specify the request type\n",
    "    \"lang\":\"ADQL\",        # the language\n",
    "    \"query\":              # and the query expressed in that language\n",
    "    \"\"\"SELECT ra, dec, Radial_Velocity FROM zcat as cat where \n",
    "    contains(point('ICRS',cat.ra,cat.dec),circle('ICRS',{0},{1},{2}))=1 and\n",
    "    cat.bmag < 14\n",
    "    order by cat.radial_velocity_error\n",
    "    \"\"\".format(coord.ra.deg,coord.dec.deg,1.0)\n",
    "    }\n",
    "r = requests.get('https://heasarc.gsfc.nasa.gov/xamin/vo/tap/sync', params=tap_params)\n",
    "#r.content\n",
    "table=Table.read(io.BytesIO(r.content))\n",
    "table\n",
    "#print(tap_params[\"query\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you aren't sure what columns are available, get all attributes of one row:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "&lt;Table masked=True length=1&gt;\n",
       "<table id=\"table4397022848\" class=\"table-striped table-bordered table-condensed\">\n",
       "<thead><tr><th>__row</th><th>name</th><th>ra</th><th>dec</th><th>lii</th><th>bii</th><th>bmag</th><th>radial_velocity</th><th>radial_velocity_error</th><th>ref_bmag</th><th>ref_radial_velocity</th><th>morph_type</th><th>bar_type</th><th>luminosity_class</th><th>structure</th><th>diameter_1</th><th>diameter_2</th><th>bt_mag</th><th>ugc_or_eso</th><th>distance</th><th>rfn_number</th><th>comments</th><th>redshift</th><th>ref_redshift</th><th>notes</th><th>class</th><th>__x_ra_dec</th><th>__y_ra_dec</th><th>__z_ra_dec</th></tr></thead>\n",
       "<thead><tr><th>int32</th><th>object</th><th>float64</th><th>float64</th><th>float64</th><th>float64</th><th>float32</th><th>int32</th><th>int32</th><th>object</th><th>int32</th><th>int32</th><th>object</th><th>int32</th><th>object</th><th>float64</th><th>float64</th><th>float64</th><th>object</th><th>float64</th><th>object</th><th>object</th><th>float64</th><th>int32</th><th>object</th><th>int32</th><th>float64</th><th>float64</th><th>float64</th></tr></thead>\n",
       "<tr><td>1</td><td>N2573</td><td>25.406756309999999</td><td>-89.335155760000006</td><td>302.76867620000002</td><td>-27.77712257</td><td>--</td><td>2294</td><td>-1</td><td></td><td>3100</td><td>4</td><td></td><td>-1</td><td></td><td>2.2999999999999998</td><td>1.0</td><td>--</td><td>001001</td><td>--</td><td></td><td></td><td>--</td><td>-1</td><td></td><td>6200</td><td>0.0049783679012001701</td><td>0.010481228506409901</td><td>-0.99993267758486504</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "<Table masked=True length=1>\n",
       "__row  name          ra         ...      __y_ra_dec           __z_ra_dec     \n",
       "int32 object      float64       ...       float64              float64       \n",
       "----- ------ ------------------ ... -------------------- --------------------\n",
       "    1  N2573 25.406756309999999 ... 0.010481228506409901 -0.99993267758486504"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## These parameters are defined in the TAP standard.  \n",
    "tap_params = {\n",
    "    \"request\":\"doQuery\",  # for requests, specify the request type\n",
    "    \"lang\":\"ADQL\",        # the language\n",
    "    \"query\":              # and the query expressed in that language\n",
    "    \"\"\"SELECT top 1 * FROM zcat\n",
    "    \"\"\".format(coord.ra.deg,coord.dec.deg,1.0)\n",
    "    }\n",
    "r = requests.get('https://heasarc.gsfc.nasa.gov/xamin/vo/tap/sync', params=tap_params)\n",
    "#r.content\n",
    "table=Table.read(io.BytesIO(r.content))\n",
    "table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"cc\"></a>\n",
    "# 2. TAP:  Using the TAP to cross-correlate our objects with a catalog"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now to search all of our sources in one go, we need to upload our own table and do a 'cross-correlation' with the *zcat* table. For more on creating VOTable objects, see that notebook.  Here, we just read one in:  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(This takes a good 20 seconds.) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "&lt;Table masked=True length=39&gt;\n",
       "<table id=\"table4397020664\" class=\"table-striped table-bordered table-condensed\">\n",
       "<thead><tr><th>ra</th><th>dec</th><th>radial_velocity</th></tr></thead>\n",
       "<thead><tr><th>float64</th><th>float64</th><th>int32</th></tr></thead>\n",
       "<tr><td>19.068407270000002</td><td>46.740033570000001</td><td>5081</td></tr>\n",
       "<tr><td>19.086435210000001</td><td>46.747227780000003</td><td>5188</td></tr>\n",
       "<tr><td>20.334799289999999</td><td>40.487715479999999</td><td>5859</td></tr>\n",
       "<tr><td>125.90444401000001</td><td>21.338192790000001</td><td>5219</td></tr>\n",
       "<tr><td>125.90444401000001</td><td>21.338192790000001</td><td>5364</td></tr>\n",
       "<tr><td>135.99423805999999</td><td>21.90099584</td><td>3157</td></tr>\n",
       "<tr><td>136.00074370999999</td><td>21.96791867</td><td>3093</td></tr>\n",
       "<tr><td>141.09150181000001</td><td>40.683784680000002</td><td>8267</td></tr>\n",
       "<tr><td>146.70334308</td><td>22.018272169999999</td><td>7446</td></tr>\n",
       "<tr><td>146.70334308</td><td>22.018272169999999</td><td>7597</td></tr>\n",
       "<tr><td>...</td><td>...</td><td>...</td></tr>\n",
       "<tr><td>237.58156191</td><td>20.762982279999999</td><td>10926</td></tr>\n",
       "<tr><td>241.52278516999999</td><td>20.78278156</td><td>4584</td></tr>\n",
       "<tr><td>241.52278516999999</td><td>20.78278156</td><td>4602</td></tr>\n",
       "<tr><td>241.52278516999999</td><td>20.78278156</td><td>4798</td></tr>\n",
       "<tr><td>317.10346952999998</td><td>18.202987799999999</td><td>4826</td></tr>\n",
       "<tr><td>317.11477339999999</td><td>18.191914189999999</td><td>5054</td></tr>\n",
       "<tr><td>329.24927524999998</td><td>6.6551501899999996</td><td>7857</td></tr>\n",
       "<tr><td>333.82169725</td><td>37.299221410000001</td><td>5207</td></tr>\n",
       "<tr><td>333.84683658</td><td>37.282608979999999</td><td>5984</td></tr>\n",
       "<tr><td>333.91491775999998</td><td>37.300255790000001</td><td>5684</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "<Table masked=True length=39>\n",
       "        ra                dec         radial_velocity\n",
       "     float64            float64            int32     \n",
       "------------------ ------------------ ---------------\n",
       "19.068407270000002 46.740033570000001            5081\n",
       "19.086435210000001 46.747227780000003            5188\n",
       "20.334799289999999 40.487715479999999            5859\n",
       "125.90444401000001 21.338192790000001            5219\n",
       "125.90444401000001 21.338192790000001            5364\n",
       "135.99423805999999        21.90099584            3157\n",
       "136.00074370999999        21.96791867            3093\n",
       "141.09150181000001 40.683784680000002            8267\n",
       "      146.70334308 22.018272169999999            7446\n",
       "      146.70334308 22.018272169999999            7597\n",
       "               ...                ...             ...\n",
       "      237.58156191 20.762982279999999           10926\n",
       "241.52278516999999        20.78278156            4584\n",
       "241.52278516999999        20.78278156            4602\n",
       "241.52278516999999        20.78278156            4798\n",
       "317.10346952999998 18.202987799999999            4826\n",
       "317.11477339999999 18.191914189999999            5054\n",
       "329.24927524999998 6.6551501899999996            7857\n",
       "      333.82169725 37.299221410000001            5207\n",
       "      333.84683658 37.282608979999999            5984\n",
       "333.91491775999998 37.300255790000001            5684"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "files={'uplt':open('my_sources.xml', 'rb')}\n",
    "\n",
    "cc_params={\n",
    "    'lang': 'ADQL', \n",
    "    'request': 'doQuery',\n",
    "    'upload':'mysources,param:uplt'\n",
    "    }\n",
    "\n",
    "cc_params[\"query\"]=\"\"\"\n",
    "    SELECT cat.ra, cat.dec, Radial_Velocity \n",
    "    FROM zcat cat, tap_upload.mysources mt \n",
    "    WHERE\n",
    "    contains(point('ICRS',cat.ra,cat.dec),circle('ICRS',mt.ra,mt.dec,0.1))=1\n",
    "    and Radial_Velocity > 0\n",
    "    ORDER by cat.ra\"\"\"\n",
    "r = requests.post('https://heasarc.gsfc.nasa.gov/xamin/vo/tap/sync',data=cc_params,stream=True,files=files)\n",
    "mytable=Table.read(io.BytesIO(r.content))\n",
    "mytable"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"combo\"></a>\n",
    "\n",
    "# 3.  Combining data from different catalogs and cross-correlating\n",
    "Now we'd like to take the redshift information (above, as a radial velocity) and determine a search radius to use for each galaxy based on its distance, so that we are are searching within a given physical distance. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "&lt;Table masked=True length=39&gt;\n",
       "<table id=\"table4397020664\" class=\"table-striped table-bordered table-condensed\">\n",
       "<thead><tr><th>ra</th><th>dec</th><th>radial_velocity</th><th>redshift</th><th>angDdeg</th></tr></thead>\n",
       "<thead><tr><th></th><th></th><th></th><th></th><th>deg</th></tr></thead>\n",
       "<thead><tr><th>float64</th><th>float64</th><th>int32</th><th>float64</th><th>float64</th></tr></thead>\n",
       "<tr><td>19.068407270000002</td><td>46.740033570000001</td><td>5081</td><td>0.0169366666667</td><td>0.000681042538686</td></tr>\n",
       "<tr><td>19.086435210000001</td><td>46.747227780000003</td><td>5188</td><td>0.0172933333333</td><td>0.000667286051816</td></tr>\n",
       "<tr><td>20.334799289999999</td><td>40.487715479999999</td><td>5859</td><td>0.01953</td><td>0.000592475054061</td></tr>\n",
       "<tr><td>125.90444401000001</td><td>21.338192790000001</td><td>5219</td><td>0.0173966666667</td><td>0.000663405922543</td></tr>\n",
       "<tr><td>125.90444401000001</td><td>21.338192790000001</td><td>5364</td><td>0.01788</td><td>0.00064585248346</td></tr>\n",
       "<tr><td>135.99423805999999</td><td>21.90099584</td><td>3157</td><td>0.0105233333333</td><td>0.00108755212566</td></tr>\n",
       "<tr><td>136.00074370999999</td><td>21.96791867</td><td>3093</td><td>0.01031</td><td>0.0011097660328</td></tr>\n",
       "<tr><td>141.09150181000001</td><td>40.683784680000002</td><td>8267</td><td>0.0275566666667</td><td>0.000424005225996</td></tr>\n",
       "<tr><td>146.70334308</td><td>22.018272169999999</td><td>7446</td><td>0.02482</td><td>0.000469199967618</td></tr>\n",
       "<tr><td>...</td><td>...</td><td>...</td><td>...</td><td>...</td></tr>\n",
       "<tr><td>237.58156191</td><td>20.762982279999999</td><td>10926</td><td>0.03642</td><td>0.000324263272089</td></tr>\n",
       "<tr><td>241.52278516999999</td><td>20.78278156</td><td>4584</td><td>0.01528</td><td>0.000753359548568</td></tr>\n",
       "<tr><td>241.52278516999999</td><td>20.78278156</td><td>4602</td><td>0.01534</td><td>0.000750467785564</td></tr>\n",
       "<tr><td>241.52278516999999</td><td>20.78278156</td><td>4798</td><td>0.0159933333333</td><td>0.000720384238428</td></tr>\n",
       "<tr><td>317.10346952999998</td><td>18.202987799999999</td><td>4826</td><td>0.0160866666667</td><td>0.000716286081667</td></tr>\n",
       "<tr><td>317.11477339999999</td><td>18.191914189999999</td><td>5054</td><td>0.0168466666667</td><td>0.000684605846616</td></tr>\n",
       "<tr><td>329.24927524999998</td><td>6.6551501899999996</td><td>7857</td><td>0.02619</td><td>0.000445394169968</td></tr>\n",
       "<tr><td>333.82169725</td><td>37.299221410000001</td><td>5207</td><td>0.0173566666667</td><td>0.00066490242687</td></tr>\n",
       "<tr><td>333.84683658</td><td>37.282608979999999</td><td>5984</td><td>0.0199466666667</td><td>0.000580392638572</td></tr>\n",
       "<tr><td>333.91491775999998</td><td>37.300255790000001</td><td>5684</td><td>0.0189466666667</td><td>0.000610283341926</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "<Table masked=True length=39>\n",
       "        ra                dec         ...     redshift         angDdeg     \n",
       "                                      ...                        deg       \n",
       "     float64            float64       ...     float64          float64     \n",
       "------------------ ------------------ ... --------------- -----------------\n",
       "19.068407270000002 46.740033570000001 ... 0.0169366666667 0.000681042538686\n",
       "19.086435210000001 46.747227780000003 ... 0.0172933333333 0.000667286051816\n",
       "20.334799289999999 40.487715479999999 ...         0.01953 0.000592475054061\n",
       "125.90444401000001 21.338192790000001 ... 0.0173966666667 0.000663405922543\n",
       "125.90444401000001 21.338192790000001 ...         0.01788  0.00064585248346\n",
       "135.99423805999999        21.90099584 ... 0.0105233333333  0.00108755212566\n",
       "136.00074370999999        21.96791867 ...         0.01031   0.0011097660328\n",
       "141.09150181000001 40.683784680000002 ... 0.0275566666667 0.000424005225996\n",
       "      146.70334308 22.018272169999999 ...         0.02482 0.000469199967618\n",
       "               ...                ... ...             ...               ...\n",
       "      237.58156191 20.762982279999999 ...         0.03642 0.000324263272089\n",
       "241.52278516999999        20.78278156 ...         0.01528 0.000753359548568\n",
       "241.52278516999999        20.78278156 ...         0.01534 0.000750467785564\n",
       "241.52278516999999        20.78278156 ... 0.0159933333333 0.000720384238428\n",
       "317.10346952999998 18.202987799999999 ... 0.0160866666667 0.000716286081667\n",
       "317.11477339999999 18.191914189999999 ... 0.0168466666667 0.000684605846616\n",
       "329.24927524999998 6.6551501899999996 ...         0.02619 0.000445394169968\n",
       "      333.82169725 37.299221410000001 ... 0.0173566666667  0.00066490242687\n",
       "      333.84683658 37.282608979999999 ... 0.0199466666667 0.000580392638572\n",
       "333.91491775999998 37.300255790000001 ... 0.0189466666667 0.000610283341926"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## The radial_velocity is in km/s, and this is just c*z, so\n",
    "c=3.0e5 # km/s\n",
    "redshifts=mytable['radial_velocity'].filled(0.)/c  # Filling masked values with zero\n",
    "mytable['redshift']=redshifts\n",
    "from astropy import units\n",
    "physdist=0.05*units.Mpc # 50 kpc physical distance\n",
    "\n",
    "## This needs scipy.  \n",
    "from astropy.cosmology import Planck15\n",
    "angDdist=Planck15.angular_diameter_distance(mytable['redshift'])\n",
    "## angDdist is returned from the astropy.cosmology module as a Quantity object, \n",
    "##  i.e. a value and a unit.  Arctan is smart enough not to operate on quantities\n",
    "##  that aren't unitless.  So angDdist.value to just get the value.\n",
    "angDrad=numpy.arctan(physdist/angDdist)\n",
    "angDdeg=angDrad*units.deg/units.rad\n",
    "mytable['angDdeg']=angDdeg\n",
    "mytable"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This time, rather than write the table to disk, we'll keep it in memory and give requests a \"file-like\" object using io.BytesIO():"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<TableColumns names=('ra','dec','radial_velocity','redshift','angDdeg')>\n"
     ]
    }
   ],
   "source": [
    "## In memory only, use an IO stream. \n",
    "vot_obj=io.BytesIO()\n",
    "print(mytable.columns)\n",
    "apvot.writeto(apvot.from_table(mytable),vot_obj)\n",
    "## (Reset the \"file-like\" object to the beginning.)\n",
    "vot_obj.seek(0)\n",
    "## 'uplt' is what we'll call it (for 'upload table') \n",
    "##   in the requests parameters below, or what you will:\n",
    "files={'uplt':vot_obj}\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This takes half a minute:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "&lt;Table masked=True length=49&gt;\n",
       "<table id=\"table4396932680\" class=\"table-striped table-bordered table-condensed\">\n",
       "<thead><tr><th>ra</th><th>dec</th><th>radial_velocity</th></tr></thead>\n",
       "<thead><tr><th>float64</th><th>float64</th><th>int32</th></tr></thead>\n",
       "<tr><td>19.068407270000002</td><td>46.740033570000001</td><td>5081</td></tr>\n",
       "<tr><td>19.086435210000001</td><td>46.747227780000003</td><td>5188</td></tr>\n",
       "<tr><td>20.334799289999999</td><td>40.487715479999999</td><td>5859</td></tr>\n",
       "<tr><td>125.90444401000001</td><td>21.338192790000001</td><td>5219</td></tr>\n",
       "<tr><td>125.90444401000001</td><td>21.338192790000001</td><td>5219</td></tr>\n",
       "<tr><td>125.90444401000001</td><td>21.338192790000001</td><td>5364</td></tr>\n",
       "<tr><td>125.90444401000001</td><td>21.338192790000001</td><td>5364</td></tr>\n",
       "<tr><td>135.99423805999999</td><td>21.90099584</td><td>3157</td></tr>\n",
       "<tr><td>136.00074370999999</td><td>21.96791867</td><td>3093</td></tr>\n",
       "<tr><td>141.09150181000001</td><td>40.683784680000002</td><td>8267</td></tr>\n",
       "<tr><td>...</td><td>...</td><td>...</td></tr>\n",
       "<tr><td>241.52278516999999</td><td>20.78278156</td><td>4602</td></tr>\n",
       "<tr><td>241.52278516999999</td><td>20.78278156</td><td>4798</td></tr>\n",
       "<tr><td>241.52278516999999</td><td>20.78278156</td><td>4798</td></tr>\n",
       "<tr><td>241.52278516999999</td><td>20.78278156</td><td>4798</td></tr>\n",
       "<tr><td>317.10346952999998</td><td>18.202987799999999</td><td>4826</td></tr>\n",
       "<tr><td>317.11477339999999</td><td>18.191914189999999</td><td>5054</td></tr>\n",
       "<tr><td>329.24927524999998</td><td>6.6551501899999996</td><td>7857</td></tr>\n",
       "<tr><td>333.82169725</td><td>37.299221410000001</td><td>5207</td></tr>\n",
       "<tr><td>333.84683658</td><td>37.282608979999999</td><td>5984</td></tr>\n",
       "<tr><td>333.91491775999998</td><td>37.300255790000001</td><td>5684</td></tr>\n",
       "</table>"
      ],
      "text/plain": [
       "<Table masked=True length=49>\n",
       "        ra                dec         radial_velocity\n",
       "     float64            float64            int32     \n",
       "------------------ ------------------ ---------------\n",
       "19.068407270000002 46.740033570000001            5081\n",
       "19.086435210000001 46.747227780000003            5188\n",
       "20.334799289999999 40.487715479999999            5859\n",
       "125.90444401000001 21.338192790000001            5219\n",
       "125.90444401000001 21.338192790000001            5219\n",
       "125.90444401000001 21.338192790000001            5364\n",
       "125.90444401000001 21.338192790000001            5364\n",
       "135.99423805999999        21.90099584            3157\n",
       "136.00074370999999        21.96791867            3093\n",
       "141.09150181000001 40.683784680000002            8267\n",
       "               ...                ...             ...\n",
       "241.52278516999999        20.78278156            4602\n",
       "241.52278516999999        20.78278156            4798\n",
       "241.52278516999999        20.78278156            4798\n",
       "241.52278516999999        20.78278156            4798\n",
       "317.10346952999998 18.202987799999999            4826\n",
       "317.11477339999999 18.191914189999999            5054\n",
       "329.24927524999998 6.6551501899999996            7857\n",
       "      333.82169725 37.299221410000001            5207\n",
       "      333.84683658 37.282608979999999            5984\n",
       "333.91491775999998 37.300255790000001            5684"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cc_params={\n",
    "    'lang': 'ADQL', \n",
    "    'request': 'doQuery',\n",
    "    'upload':'mytable,param:uplt'\n",
    "    }\n",
    "## This is your ADQL query, where \"mytable\" here has to \n",
    "##  match what you specified in the upload parameter above\n",
    "cc_params[\"query\"]=\"\"\"\n",
    "    SELECT cat.ra, cat.dec, cat.Radial_Velocity \n",
    "    FROM zcat cat, tap_upload.mytable mt \n",
    "    WHERE\n",
    "    contains(point('ICRS',cat.ra,cat.dec),circle('ICRS',mt.ra,mt.dec,mt.angDdeg))=1\n",
    "    and cat.Radial_Velocity > 0\n",
    "    ORDER by cat.ra\"\"\"\n",
    "## The name you give here (tab1) matches what's in the cc_params\n",
    "r = requests.post('https://heasarc.gsfc.nasa.gov/xamin/vo/tap/sync',data=cc_params,stream=True,files=files)\n",
    "mytable=Table.read(io.BytesIO(r.content))\n",
    "mytable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}