\documentclass[11pt]{article}

\usepackage[gavo]{votutorial}
\usepackage{listings}
\lstset{flexiblecolumns=true}

\title{Adding catalog data to object lists using the VO}
\author{Markus Demleitner}
\titlepicture{tapmeta.png}

\begin{document}
\maketitle

\begin{abstract}
This brief tutorial shows you how to quickly add proper motions and
photometry from Gaia to (almost) any object list using the Virtual
Observatory.  The VO protocol most suited to this kind of this is TAP
(``table access protocol'') and lets you transfer data and queries to
database servers. In the example, we will be using TOPCAT as a client.
There is no lock-in to it: There are libraries and other tools allowing
an integration of TAP operations into arbitrary workflows – that's what
standards are about.  Tutorial supplements apply the techniques to
Simbad, show how to use TAP from Python, and introduce UCDs.
\end{abstract}

\usedsoftware{\Topcat, pyVO}

\section{Describe your data to TOPCAT}

Whatever you can load into TOPCAT can work here.  To play through the
worst case, let's assume you just have a load (millions are possible) of
RA/Dec values in one of the text files we astronomers (regrettably) so adore.  If you
don't have a file of your own handy, we have prepared a sample file for
you at
$$\hbox{\url{http://docs.g-vo.org/pmadd-testdata.txt}.}$$

\tutstep[Load your data] Click \menu{File/Load Table} and select the test data
(or your own) in the corresponding location.
With our test data, you may need to set \texttt{Format} to \texttt{ASCII} in TOPCAT's
load dialog manually (not in current versions, though).

With the pesky ASCII files, TOPCAT has no way to know it is
dealing with positions.  Because we later need it to understand that, we
have to help it.

\tutstep[Improving column metadata] To tell \texttt{TOPCAT} there's RA and Dec
here, bring up the columns info
window by selecting \menu{Views}$\rightarrow${Column Info} from the menu.
The easiest way to tell TOPCAT we have a position here is just editing
the column names to be \sql{ra} and \sql{dec} (at the end of this
document you'll find a better way).  Double-click into table
cells to edit them.
Close the columns info window.

\section{Select the service and table to match against}

To locate services and data in the VO, use the Registry; there are many
ways to do that, and it's
possible (and in some cases a very good idea) to use physics to
locate data (``Give me an all-sky catalog containing proper motions and
infrared photometry'').  There is a separate tutorial for
that\footnote{\url{https://www.g-vo.org/tutorials/registry-data-discovery.pdf}}.
Here, we already know the name of the data collection, which simplifies
data discovery.

\tutstep[Find a TAP service] In \texttt{TOPCAT}, select \menu{VO/TAP Query}, and in
the searchfield \key{Keywords} type ``Gaia''.  This will result in a list of services
offering at least some Gaia data.  While anything that has gaia\_source
should be
be good for what follows, we recommend using \texttt{GAVO DC TAP} for
demonstration purposes; the table name used below is specific to it.
Select it, then hit \menu{Use Service}.

\tutstep[Find your table] Some TAP services have hundreds of even
thousands of tables.  To find the metadata of yours, in the \menu{Find}
field in the \key{Metadata} pane, enter some characters you expect to
see in the table name or description.  In this case, ``dr3'' or
``source'' would work fine. Note the
extensive metadata right of the table tree. It should help you select a
pertinent table.  In this case, you should choose \texttt{gaia.dr3lite} by double-clicking
(if you didn't select GAVO DC TAP service in the previous step, you can e.g. select
 \texttt{gaiadr3.gaia\char`_source}).

\section{Write the query}

Since positional crossmatches to uploaded data are so frequent, TOPCAT
can generate you a sample query to start from:

\tutstep[Have TOPCAT help you with your query]
Hit the \menu{Examples} button (right below
the large text field near the bottom of the TAP dialog) and select
\menu{upload/Uploadjoin}. A query adapted to joining the current TOPCAT table
with the TAPtable selected appears in the query  field.
It should look pretty much like this:

\begin{verbatim}
SELECT
   TOP 1000
   *
   FROM gaia.dr3lite AS db
   JOIN TAP_UPLOAD.t1 AS tc
   ON 1=CONTAINS(POINT('ICRS', db.ra, db.dec),
                 CIRCLE('ICRS', tc.ra, tc.dec, 5./3600.))
\end{verbatim}

Don't worry if you don't understand all of it now -- that can wait a bit.
Suffice it to say that it's fairly standard SQL\footnote{``Structured
Query Language'', the de-facto standard for communicating with database
systems since the seventies.  In this incarnation, it's actually ADQL,
where AD stands for ``Astronomical Data'', but that makes no real
difference.}.

\tutstep[Customise the match limit] Edit it a bit to better fit your needs: Instead of \sql{TOP 1000}, say
\sql{TOP 5000} or something appropriate to the size of your input data
(but remember that an input line can match multiple objects, so be
generous here).  You will have to raise \menu{Max Rows} above the query
input if you want to retrieve more rows than shown there.

\tutstep[Customise the fields to retrieve]
The \sql{*} after the \sql{TOP} clause means: Get all columns.
This is more than we are interested in here. We want our columns --
 \sql{tc.*}, the \sql{tc} being the alias given in
the line starting with \sql{JOIN} --, and the proper motions and photometry
from the database. Obtain the names from the Columns pane in the TAP
Window's table browser; the best way is to control-click the entries
there and then use the little \key{Cols} button above the query input to
paste the names of the selected columns into the query.

In the end, replace the \sql{*} with something like:

\begin{verbatim}
  tc.*, pmra, pmdec,
  phot_g_mean_mag, phot_bp_mean_mag, phot_rp_mean_mag
\end{verbatim}

(you could have written \sql{db.pmra} and so on here, but as long as the
names are unique, you can skip the table name).  And in a real science
settings, you should probably get the errors and
\verb|astrometric_params_solved|, too\dots

\tutstep[Customise the match radius]
Finally, you may want to change the match radius. That's the
\sql{5./3600.}, and it's in degrees. Whether 5 arcsec is too much or too
little for your application is a matter of your scientific judgement (or
a principled analysis).

\tutstep[Run the query]
Then hit \menu{Run Query} and enjoy, plot, or devour your results.  If
you have large object sets, you may want to change \menu{Mode} to
\key{Asynchronous} before running the query; that lets you run queries
that take, potentially, hours (but if you think you need to do this,
think again and perhaps ask the operators; there are often better and
faster ways to the same result).


\section{Supplement: Simbad}


One nice thing about standards is that if you can operate one site, you can
(basically) operate all of them.  So, since you know how to crossmatch
with PPMXL on GAVO's TAP server, let's see how things work with SIMBAD.

Locating Simbad's TAP service works exactly as above:
In the TAP window, go back to the
\menu{Select Service} tab. As before, in the \key{Keywords} field, enter
``simbad'' and hit \key{Enter}.  You should receive at least one result, and you want to
double-click \key{SIMBAD TAP}.

After \menu{Use Service}, you can now browse Simbad's table metadata.
Most of the standard stuff is in \texttt{basic}. Use the search
field to find the table and select the
table.

Just for demonstration, try again what you've done above, i.e.,
\menu{Examples}, \menu{Upload Join} again, fix the search
radius as you see fit and send off the query.  You should fairly quickly
see a couple of rows identifying the ``well-known'' among the objects in
the list as (possibly just being near to) anything from an absorption
line system or a galaxy to a semi-regular variable star.

A variation of the example above (``get
proper motions when all you have is positions'') is getting positions
when all you have is a list of object names (as long as Simbad can
resolve them).  We just need to be a bit more creative with our joins,
and TOPCAT doesn't have an example (yet).

Consider a file like

\begin{verbatim}
"HR 1014"
"M 31"
"Q2237+0305"
\end{verbatim}

(the quotes just help TOPCAT to figure out that names with blanks in
them still make up a single column; if your object list doesn't have
them, add them with \verb|sed -i 's/.*/"&"/' objs.txt|).

Load that (or a similar) file into TOPCAT (manually select the CSV
format), then go back to the TAP window pointing at Simbad and run the
following query:

\begin{verbatim}
SELECT col1, ra, dec
FROM TAP_UPLOAD.t1
LEFT OUTER JOIN ident
ON (id=normId(col1))
LEFT OUTER JOIN basic
ON (oidref=oid)
\end{verbatim}

(you will probably have to change the ``t1'' after TAP\_UPLOAD to match
the index of the table with the identifiers, which in turn is the number
TOPCAT shows in the table list in the main window).

Explaining what's going on in this query is a bit beyond the scope of
this little tutorial -- please refer to
\href{http://docs.g-vo.org/adql/html/}{our ADQL course} if you want to
understand the details.

What this returns as of this writing is:

\begin{tabular}{lrr}
\noalign{\vspace{2pt}}\hline\noalign{\vspace{2pt}}
\textbf{col1}&\textbf{ra}&\textbf{dec}\cr
HR 1014    & 49.49615  & -66.92685 \\
M 31       & 10.68471  &  41.26875 \\
Q2237+0305 &340.12597  &   3.3585  \\
\noalign{\vspace{2pt}}\hline\noalign{\vspace{2pt}}
\end{tabular}

Note again that there's no problem doing this for a thousand identifiers
at a time.

\section{Supplement: Use python}

Another nice thing about standards is that you get to choose your client
(i.e., the software you use to operate the services).  To use the VO
from within Python programs, there is a (pip-installable,
Debian-packaged) package called pyVO.

If you have it, you can run our initial
example (minus the discovery part; you \emph{could} do that from within
pyVO, too, but frequently it's much better to do that part
interactively) using a program like this:

\lstinputlisting{../res/tap-query.py}

It wouldn't be hard to send the result to, say, TOPCAT rather than save
it.  See \url{http://docs.g-vo.org/pyvo} for an informal course on this.
The source above is also attached to this PDF.

\section{Supplement: Marginalia}

The metadata we added above is of course extremely shoddy. For it to be
more understandable outside of TOPCAT, you
would add UCDs\footnote{``Unified Content Descriptors'', standard labels
for physical entities; you might have seen those on VizeR} to the column
metadata: For that you'd select \menu{Display}$\rightarrow$\menu{UCD} from
the window and enter
\texttt{pos.eq.ra;meta.main} in the UCD field for the RA column and
correspondingly \texttt{pos.eq.dec;meta.main} for the Dec column.

The query as written returns one line per \emph{match}, i.e., input objects
that have no counterpart in the database will be missing in the output;
and indeed, you'll see that for the 4000 input objects (which, really,
are typically fairly weak infrared sources if you have to know), only
about 1500 output lines result.

If you want non-matching input objects in the output, with NULLs
wherever database information would be, write \sql{RIGHT OUTER JOIN}
instead of \sql{JOIN} (``right'' because your table is the right operand in
the join expression). If you used the sample data set, you'll see that
253 objects had multiple matches in PPMXL.

Again, more on TAP and ADQL in GAVO's little course at
\url{http://docs.g-vo.org/adql/html}

\vfill
\noindent DOI: 10.21938/LvJ43o1bcSnFO94vnnsqQA
\onvott
\cczero
\eject
\end{document}
