\metaAuthor{Markus Demleitner}
\metaContactAddress{msdemlei@ari.uni-heidelberg.de}
\metaTitle{ADQL}
\metaLang{en}
\metaCopyright{/usr/local/share/copyright.html}
\htmlTitlepage{
Here is a HTML rendition of the notes for a short course on ADQL and
TAP.

It's allowed to cut-and-paste the queries\dots

Also, there's a \link{twoup.pdf}{PDF version} that you can print and
keep as a makeshift reference.

The queries and problems in the text are supposed to be run against
the server \dott{ivo://uni-heidelberg.de/gaia/tap} with the access URL
\dott{http://gaia.ari.uni-heidelberg.de/tap} (unless something else is specified).
}


\startpage
\title{Gaia Data Queries with ADQL}
\startline{1}{0.2}
\startbox{1}
\centerline{Markus Demleitner \doit{(msdemlei@ari.uni-heidelberg.de)}}
\centerline{Hendrik Heinl \doit{(heinl@ari.uni-heidelberg.de)}}
\endbox
\endline
\startline{1}{0.5}
\startbox{0.5}
\smallhead{Agenda}
\startul
\ulitem Why bother?
\ulitem A first query
\ulitem ADQL
\ulitem The finer points of TAP
\endul

\endbox

\startbox{0.4}
T(able) A(ccess) P(rotocol)

A(stronomical) D(ata) Q(uery) L(anguage)
\endbox
\endline

\startline{1}{0.1}
\startbox{1}
Open a browser on \dott{http://docs.g-vo.org/adql-gaia/html}
\endbox
\endline
\endpage

\startpage
\title{Data Intensive Science}
\startline{1}{0.9}
\startbox{1}
Data-intensive science means:

\startol
\olitem Using many data collections
\olitem Using large data collections
\endol

Point (1) requires standard formats and access protocols to the data,
point (2) means moving the data to your box and operating on it with
FORTRAN and grep becomes infeasible.
\bigskip
The Virtual Observatory (VO) in general is about solving problem (1),
TAP/ADQL in particular about (2).
\endbox
\endline
\endpage

\startpage
\title{A First Query}
\startline{1}{0.85}
\startbox{1}
To follow the examples, start TOPCAT and select TAP in the VO menu.

In TAP URL: at the bottom of the window, enter
\dott{http://gaia.ari.uni-heidelberg.de/tap} and click ''Use Service''.

At the bottom of the form, at Mode: check ``Synchronous'' and enter
\medskip
\startex
SELECT TOP 1 1+1 AS result FROM gaiadr1.tgas_source
|endex


in the text box, then click ``Ok''.  \startlongonly  This should give 
you a table with a single 2 in it.  If that hasn't worked
	complain.\endlongonly Copying and Pasting from
\link{http://docs.g-vo.org/adql-gaia}{http://docs.g-vo.org/adql-gaia} is legal.


\startlongonly
Note that in the top part of the dialog there's metadata on the tables
exposed by the service (in particular, the names of the tables and the
descriptions, units, etc., of the columns).  Use that when you construct queries later.
\endlongonly

%\startlongonly
%There  are other TAP clients than TOPCAT -- after all, we're talking
%about a standard protocol.  For example, there's
%\link{http://soft.g-vo.org/}{tapsh} that emulates a normal, command-line
%database shell; most of the queries here assume you're querying against the
%server with the IVOA id 
%
%\dott{ivo://org.gavo.dc/\_\_system\_\_/tap/run}.
%
%To get that, typing \dott{server ivo://org.g} and then completing with
%Tab should be sufficient.
%
%You can also use \link{http://saada.u-strasbg.fr/taphandle/}{TAPHandle},
%which runs entirely in your browser.
%
%More TAP clients can be found on the
%\link{http://www.ivoa.net/astronomers/applications.html}{IVOA
%applications page}.
%\endlongonly

\endbox
\endline
\endpage


\startpage
\title{Why SQL?}
\startline{1}{0.9}
\startbox{1}
The SELECT statement is written in ADQL, a dialect of SQL (''sequel'').
Such queries make up quite a bit of the science within the VO.

SQL has been chosen as a base because

\startul
\ulitem Solid theory behind it (relational algebra)
\ulitem Lots of high-quality engines available
\ulitem Not Turing-complete, i.e., automated reasoning on ''programs''  is not very hard
\endul
\endbox
\endline
\endpage

\startpage
\title{Relational Algebra}
\startline{1}{0.85}
\startbox{1}
At the basis of relational data bases is the relational algebra, an algebra on sets of tuples
(``relations'') defining six operators:
\startul
\ulitem unary \doit{select} \startlongonly -- select tuples matching to some
condition\endlongonly
\ulitem unary \doit{project} \startlongonly -- make a set of sub-tuples of all
tuples (i.e., have less columns)\endlongonly
\ulitem unary \doit{rename} \startlongonly -- change the name of a
relation (this is a rather technical operation)\endlongonly
\ulitem binary \doit{cartesian product} \startlongonly -- the
usual cartesian product, except that the tuples are concatenated rather
than just put into a pair; this, of course, is not usually actually
computed but rather used as a formal step.\endlongonly
\ulitem binary \doit{union} \startlongonly -- simple union of sets.
This is only defined for ''compatible''  relations; the technical points
don't matter here\endlongonly
\ulitem binary \doit{set difference} \startlongonly as for union; you
could have used intersection and complementing as well, but
complementing is harder to specify in the context of relational
algebra 
\endlongonly
\endul

\dobf{Good News:} You don't \doit{need} to know any of this.
\startlongonly But it's reassuring to know that there's a solid theory
behind all of this.\endlongonly
\endbox
\endline
\endpage

\startpage
\title{SELECT for real}
\startline{1}{0.85}
\startbox{1}
ADQL defines just one statement, the \dott{SELECT} statement, which lets
you write down expressions of relational algebra.  Roughly, it looks
like this:

\dott{SELECT} [\dott{TOP} \doit{setLimit}] \doit{selectList} \dott{FROM}
\doit{fromClause} [\dott{WHERE} \doit{conditions}] [\dott{GROUP BY}
\doit{columns}] [\dott{ORDER BY} \doit{columns}]

\startlongonly
In reality, there are yet a few more things you can write, but what's
shown covers most things you'll want to do.  The real magic is in
\doit{selectList}, \doit{fromClause} (in particular), and
\doit{conditions}.
\endlongonly

\smallhead{TOP}

\doit{setLimit}: just an integer giving how many rows you want
returned.
\startex
SELECT TOP 5 * FROM gaiadr1.tgas_source
|endex
\startex
SELECT TOP 10 * FROM gaiadr1.tgas_source
|endex
\endbox
\endline
\endpage

\startpage
\title{SELECT: ORDER BY}
\startline{1}{0.8}
\startbox{1}

\dott{ORDER BY} takes \doit{columns}: a list of column names (or
expressions), and you can add \dott{ASC} (the default) or \dott{DESC}
(descending order):
\startex
SELECT TOP 5 source_id, parallax
FROM gaiadr1.tgas_source 
ORDER BY parallax
|endex

\startex
SELECT TOP 5 source_id, parallax
FROM gaiadr1.tgas_source 
ORDER BY parallax DESC
|endex

\startex
SELECT TOP 5 source_id, phot_g_mean_mag , parallax
FROM gaiadr1.tgas_source
ORDER BY phot_g_mean_mag, parallax
|endex

Note that ordering is outside of the relational model.
\startlongonly 
That sometimes matters because it may mess up query planning (a
rearrangement of relational expressions done by the database engine to
make them run faster)\endlongonly
\endbox
\endline

\startueb
\startprob
\bqu
Select the source id and visual magnitude of the 20 brightest
stars in the table tgas\_source.
\equ
\bans
\startverbatim
SELECT TOP 20 
source_id, phot_g_mean_mag 
FROM gaiadr1.tgas_source
ORDER BY phot_g_mean_mag
|endverbatim
@eans
\endprob
\endueb
\endpage

\startpage
\title{SELECT: what?}
\startline{1}{0.85}
\startbox{1}
The select list has column names or expressions involving columns.

SQL expressions are not very different from those of other programming
languages. 
\startex

SELECT TOP 10 
  source_id,
  SQRT(POWER(pmdec_error,2)+POWER(pmra_error,2)) AS pm_errTot
FROM gaiadr1.tgas_source
|endex


\startlongonly 
The value literals are as usual:
\startul
\ulitem Only decimal integers are supported (no hex or such)
\ulitem Floating point values are written like 4.5e-8
\ulitem Strings use single quotes (\dott{'abc'}).  Double quotes mean
something completely different for ADQL (they are "`delimited
identifiers"').  
\endul

The usual arithmetic, comparison, and logical operators work as expected:

\startul
\ulitem $+$, $-$, \dott{*}, \dott{/}; as in C, there is no power
operator in ADQL.  Use the \dott{POWER} function instead.
\ulitem \dott{=} (\doit{not} \dott{==}), \dott{<}, \dott{>}, \dott{<=}, \dott{>=}
\ulitem \dott{AND}, \dott{OR}, \dott{NOT}
\ulitem String concatenation is done using the \dott{||} operator.
Strings also support \dott{LIKE} that supports patterns.  \dott{\%} is
``zero or more arbitrary characters'', \dott{\_} ``exactly one arbitrary
character'' (like \dott{*} and \dott{?} in shell patterns).
\endul


Here's a list of ADQL functions:

\startul
\ulitem Trigonometric functions, arguments/results in rad:
\dott{ACOS}, \dott{ASIN}, \dott{ATAN}, \dott{ATAN2},
\dott{COS}, \dott{SIN}, \dott{TAN}; ${\rm atan2}(y,x)$ returns the inverse
tangent in the right quadrant and thus avoids the degeneracy of
${\rm atan}(y/x)$.
\ulitem Exponentiation and logarithms: \dott{EXP}, \dott{LOG} (natural
logarithm), \dott{LOG10}
\ulitem Truncating and rounding: \dott{FLOOR(x)} (largest integer
smaller than x), \dott{CEILING(x)} (smallest integer larger than $x$),
\dott{ROUND(x)} (commercial rounding to the next integer),
\dott{ROUND(x, n)} (like the one-argument round, but round to $n$
decimal places), \dott{TRUNCATE(x)}, \dott{TRUNCATE(x,n)} (like ROUND,
but just discard unwanted digits).
\ulitem Angle conversion:  \dott{DEGREES(rads)},  \dott{RADIANS(degs)}
(turn radians to degrees and vice versa)
\ulitem Random numbers: \dott{RAND()} (return a random number between 0
and 1), \dott{RAND(seed)} (as without arguments, but seed the the random
number generator with an integer)
\ulitem Operator-like functions: \dott{MOD(x,y)} (the remainder of $x/y$,
i.e., \dott{x\%y} in C), \dott{POWER(x,y)}
\ulitem Power shortcuts: \dott{SQRT(x)} (shortcut for \dott{POWER(x,
0.5)}), \dott{SQUARE(x)} (shortcut for \dott{POWER(x, 2)})
\ulitem Misc: \dott{ABS(x)} (absolute value), \dott{PI()}
\endul

Note that all names in SQL (column names, table names, etc) are
case-insensitive (i.e., \dott{VAR} and \dott{var} denote the same
thing).  You can force case-sensitivity (and use SQL reserved words as
identifiers) by putting the identifiers in double quotes (that's called
delimited identifiers).  Don't do that if you can help it, since the
full rules for how delimited identifiers interact with normal ones are
difficult and confusing.

Also note how I used AS to rename a column.  You can use the names
assigned in this way in, e.g., \dott{ORDER BY}:
\startex
SELECT TOP 10 
  source_id,
  SQRT(POWER(pmdec_error,2)+POWER(pmra_error,2)) AS pm_errTot
FROM gaiadr1.tgas_source
ORDER BY pm_errTot
|endex

To select all columns, use \dott{*}
\startex
SELECT TOP 10 * FROM gaiadr1.tgas_source
|endex
\endlongonly

Use \dott{COUNT(*)} to figure out how many items there are.
\startex

SELECT count(*) AS numEntries 
FROM gaiadr1.tgas_source 

|endex

\startlongonly
\dott{COUNT} is what's called an aggregate function in SQL: A function
taking a set of values and returning a single value.  The other
aggregate functions in ADQL are (all these take an expression as
argument; count is special with its asterisk):

\startul
\ulitem \dott{MAX}, \dott{MIN}
\ulitem \dott{SUM}
\ulitem \dott{AVG} (arithmetic mean)
\endul
\endlongonly
\endbox
\endline

\startueb
\startprob
\bqu
	Select the source id, position, proper motion in arcsec/yr and mag g for the 20 fastest stars in tgas\_source.
\equ
\bans
\startverbatim
SELECT TOP 20 
source_id, ra, dec,
SQRT(POWER(pmra,2)+POWER(pmdec,2))/1000 AS pmtot,
phot_g_mean_mag
FROM gaiadr1.tgas_source
ORDER BY pmtot DESC
|endverbatim
@eans
\endprob
\endueb
	
\endpage

\startpage
\title{SELECT: WHERE clause}
\startline{1}{0.8}
\startbox{1}

Behind the WHERE is a logical expression; these are similar to other
languages as well, with operators \dott{AND}, \dott{OR}, and \dott{NOT}.
\startex
SELECT source_id, ra, dec
  FROM gaiadr1.tgas_source
  WHERE
  phot_g_mean_flux > 13
  AND parallax < 0.2
|endex
\endbox
\endline
\startueb
\startprob
\bqu
Select the absolute magnitude, position and the source id for the 20 stars with
the largest observed apparent magnitude in the g band from the table tgas\_source (in case you don't remember: The absolute magnitude is $M=5+5\log\pi+m$ with the
parallax in arcsec $\pi$ and the apparent magnitude $m$ (check the
units!). This will fail. Try to fix it. 
\equ
\bans
The right solution would look like:
\startverbatim
SELECT TOP 20 source_id, ra, dec, phot_g_mean_mag,
5+5*log10(parallax/1000)+phot_g_mean_mag as absmag
FROM gaiadr1.tgas_source
WHERE parallax > 1
ORDER BY phot_g_mean_mag DESC
|endverbatim
Notice, that the WHERE clause is necessary because the log of a negativ
number must fail. 
@eans
\endprob
\endueb





%\startueb
%\startprob
%\bqu
%Select the source\_id, position and absolute magnitude for 
%the 20 stars with the greatest visual magnitude. Order by visual
%magnitude. This will fail for reasons that should tell you
%something about the value of Bayesian statistics.  Make the query work.
%\equ
%\bans
%Just add a \dott{WHERE parallax>0}.  In reality, one would of course need to
%be more careful.
%@eans
%\endprob
%\endueb
\endpage


\startpage
\title{SELECT: Grouping}
\startline{1}{0.85}
\startbox{1}
For histogram-like functionality, you can compute factor sets, i.e.,
subsets that have identical values for one or more columns, and you can
compute aggregate functions for them.
\startex
SELECT COUNT(*) AS n,
  ROUND(phot_g_mean_mag) AS bin, 
  AVG(parallax) AS parallax_mean
FROM gaiadr1.tgas_source
GROUP BY bin
ORDER BY bin
|endex

\startlongonly
Note how the aggregate functions interact with grouping (they compute
values for each group).

Also note the renaming using AS.  You can do that for columns (so your
expressions are more compact) as well as for tables (this becomes handy
with joins).
\endlongonly

For simple \dott{GROUP} applications, you can shortcut using
\dott{DISTINCT} (which basically computes the ``domain'').
\startex
SELECT DISTINCT
ROUND(phot_g_mean_mag), ROUND(parallax) 
FROM gaiadr1.tgas_source
|endex

% \startlongonly
% A common operation is trying some statistical qualification over the
% entire sky or a significant part of it.  Since healpixes have equal
% areas and are well-beheaved at the poles and across the stitching line
% of a spherical coordinate system, they are particularly well suited for
% work like this.  An introduction to this with sample queries is given 
% \link{http://www.star.bris.ac.uk/~mbt/papers/adassXXVI-P1-31-poster.pdf}{on
% a poster by Mark Taylor}.  Not all services support the necessary
% functions (in TOPCAT, you can check in the ``service'' tab).

% While for large catalogs, such queries will have long runtimes, you can
% try it for smallish catalogs even in a course situation, for instance:

% \startex
% select ivo_healpix_index(5, raj2000, dej2000) as bin,
%   count(*) as n,
%  avg(rv) as meanrv,
%  max(rv)-avg(rv) as updev,
%  avg(rv)-min(rv) as lowdev
% from rave.main
% where e_rv<20
% group by bin
% having count(*)>5
% |endex

% Plot this in TOPCAT using the sky plot, Layers/Add Healpix Control.  Use
% bin as Healpix index, set the healpix level to 5, and the select what
% you want to see plotted.  As annotation for healpix columns improves,
% plotting these things should involve less manual work.
% \endlongonly
\endbox
\endline

\startueb
\startprob
\bqu
Get the averages for the total proper motion from tgas\_source in bins
of one mag in mag g mean each.  Let the output table contain the number of objects in each bin, too.
\equ
\bans
\startverbatim
SELECT 
  ROUND(phot_g_mean_mag) AS bin, 
  COUNT(*) AS n,
  AVG(SQRT(POWER(pmra,2)+POWER(pmdec,2))) AS pmavg 
FROM gaiadr1.tgas_source
GROUP BY bin 
ORDER BY bin


|endverbatim
@eans
\endprob
\endueb
\endpage



\startpage
\title{SELECT: JOIN USING}
\startline{1}{0.85}
\startbox{1}
The tricky point in ADQL is the \dott{FROM} clause.  So far, we had a
single table.  Things get interesting when you add more tables: JOIN.
\startex
SELECT TOP 10 h1.ra, h1.dec, h1.hip, t1.hip
FROM hipparcos AS h1
JOIN tycho2 AS t1
USING (hip)
|endex
\startlongonly
Check the tables in the Table Metadata shown by TOPCAT: astroref is from
hipparcos, hp\_mag from hipparcos\_newreduction; both tables have a hip column.
\endlongonly

\dott{JOIN} is a combination of cartesian product and a select. 


\startverbatim
FROM hipparcos AS h1
JOIN tycho2 AS t1
USING (hip)
|endverbatim

yields the cartesian product of the hipparcos and tycho2
tables but only retains the rows in which the hip columns in both tables
agree.

\startlongonly
Note that while the hip column we're joining on is in both tables but only occurs once in the
joined table.
\endlongonly
\endbox
\endline
\endpage


\startpage
\title{SELECT: JOIN ON}
\startline{1}{0.9}
\startbox{1}
If your join criteria are more complex, you can join \dott{ON}:

\startex

SELECT TOP 20 source_id, h.hip
FROM gaiadr1.tgas_source AS tgas
LEFT OUTER JOIN hipparcos as h ON (tgas.phot_g_mean_mag BETWEEN
h.hpmag -0.05 AND h.hpmag+0.05)
|endex

\startlongonly
This particular query gives, for each source\_id in tgas\_source, all ids from
ucas4 belonging to stars having about the same aperture magnitude as the
mean g magnitude given in tgas\_source.  This doesn't make any sense, but you may get the idea.

There are various kinds of joins, depending on what elements of the
cartesian product are being retained.  First note that in a normal join,
rows from either table that have no ``match'' in the other table get
dropped.  Since that's not always what you want, there are join variants
that let you keep certain rows.  In short (you'll probably have to
read up on this):
\endlongonly


\startul
\ulitem \dott{t1 INNER JOIN t2} \startlongonly(\dott{INNER} is the default and is usually omitted): Keep
all elements in the cartesian product that satisfy the join
condition.\endlongonly
\ulitem \dott{t1 LEFT OUTER JOIN t2}\startlongonly: as \dott{INNER}, but in addition 
for all rows of \dott{t1} that would vanish in the result (i.e., that
have no match in \dott{t2}) add a result row consisting of the row in
\dott{t1} with NULL values where the row from \dott{t2} would
be.\endlongonly
\ulitem \dott{t1 RIGHT OUTER JOIN t2}\startlongonly: as \dott{LEFT OUTER}, but this time
all rows from \dott{t2} are retained.\endlongonly
\ulitem \dott{t1 FULL OUTER JOIN t2}\startlongonly: as \dott{LEFT OUTER} and \dott{RIGHT
OUTER} performed in sequence.\endlongonly
\endul
\endbox
\endline
\endpage



% \startpage
% \title{Crossmatch Tables}
% \startline{1}{0.8}
% \startbox{1}
% 
% Crossmatches of large catalogs with other large catalogs are
% necessarily computationally fairly expensive.  Also, providers may
% want to be smart about the crossmatches, taking into account
% additional information (proper motions, magnitudes, etc.;
% a Bayesian test is actually a good model for what's really going on
% when properly crossmatching).
% 
% For considerations like these, ESA on their TAP service provide
% pre-computed crossmatches between a few catalogs
% (``*\_best\_neighbour'').
% 
% Find their service with ``Select Service'', Keywords \dott{esa gaia}.
% 
% \startlongonly
% The crossmatch tables essentially correlate ids (``primary keys'' in
% relational jargon) from the two tables.  See for yourself:
% 
% \startverbatim
% select top 30 * from tmass_best_neighbour
% |endverbatim
% 
% \endlongonly
% 
% To use them, do a three-way join -- first, add the ``right'' id using a
% join with the ``left'', and then join on that.  A catalog with tgas
% astrometry and 2MASS photometry:
% 
% \startex
% SELECT TOP 200 
% tgas.ra, tgas.dec, pmra, pmdec, j_m, h_m, ks_m
% FROM tmass_original_valid AS tmov
% JOIN tmass_best_neighbour USING (tmass_oid)
% JOIN tgas_source AS tgas USING (source_id)
% |endex
% \endbox
% \endline
% 
% \startueb
% \startprob
% \bqu
% Using the pattern just discussed, make a catalog of proper motions and
% 2MASS photometry for TGAS objects with a G magnitude between 8 and 8.5.
% Make a few plots correlating proper motions and photometric quantities
% (perhaps: A colour-magnitude diagram with color-coded absolute proper
% motions).  Can you see something?
% \equ
% \bans
% The main insight from this problem should have been that you can add
% arbitrary constraints to the full join.  In this case:
% 
% \startverbatim
% SELECT
% tgas.ra, tgas.dec, pmra, pmdec, j_m, h_m, ks_m
% FROM tmass_original_valid AS tmov
% JOIN tmass_best_neighbour USING (tmass_oid)
% JOIN tgas_source AS tgas USING (source_id)
% WHERE phot_g_mean_mag BETWEEN 8 AND 8.5
% |endverbatim
% 
% As to what this catalog allows in terms of astrophysical insights: Well,
% statistically, proper motion is a handle for the distance.  So, by
% making suitable proper motion bins you should see a systematics in the
% colors (since they're different for dwarfs and giants).  Just \doit{how}
% you do that is a TOPCAT problem, really\dots
% @eans
% \endprob
% \endueb
% 
% \endpage
% 


\startpage
\title{Geometries}
\startline{1}{0.85}
\startbox{1}
The main extension of ADQL wrt SQL is addition of geometric
functions.  
\startlongonly Unfortunately, these were not particularly well
 designed, but if you don't expect too much, they'll do their
 job.\endlongonly

Keep the crossmatch pattern somewhere
handy (everything is in degrees):

\startex
SELECT TOP 5 
  source_id, tgas.ra, tgas.dec, tm.raj2000, 
	tm.dej2000, hmag, e_hmag
FROM gaiadr1.tgas_source as tgas
JOIN twomass AS tm
ON 1=CONTAINS (
     POINT('ICRS', tm.raj2000, tm.dej2000),
     CIRCLE('ICRS', tgas.ra, tgas.dec, 1.5/3600))
|endex

In theory, you could use reference systems other than
ICRS (e.g., GALACTIC, FK4) and hope the
server converts the positions, but I'd avoid constructions with multiple
systems -- even if the server implements the stuff correctly, it's most
likely going to be slow.

When catalogs are on different epochs, you may need to account for
proper motions to match faster stars.  You should, however, \doit{not}
apply the proper motions in the primary selection.  If you do that, the
index cannot be used, and your query will waste a lot of CPU and disk
bandwidth.  Instead, decide about the maximum proper motion your objects
might have (to get an idea, of the statistics, try selecting the fastest
stars from ppmx.data -- apart from the fact that the catalog got the
fastest stars pretty wrong with two copies of some fast stars, there's
only a handful stars moving faster than four arcsecs per year).

Then multiply this with your epoch difference and make that your initial
crossmatch radius.  Then filter out the spurious matches with an extra
where clause taking into account the proper motions.  For moderate epoch
differences, don't worry about going into the tangential plane to apply
proper motions and, for now, say something like

\startex
SELECT
   TOP 30
   *
   FROM ppmxl AS m
   JOIN gaiadr2.gaia_source AS g
   ON 1=CONTAINS(POINT('ICRS', m.raj2000, m.dej2000),
                 CIRCLE('ICRS', g.ra, g.dec, 30./3600.))
WHERE 1=CONTAINS(POINT('ICRS', 
		m.raj2000+m.pmra*COS(RADIANS(m.dej2000))*15, 
		m.dej2000+m.pmde*15),
  CIRCLE('ICRS', g.ra, g.dec, 0.5/3600.))

|endex

The 15 is because Gaia DR1 is on J2015, whereas PPMXL is on J2000.
Also, be careful with the units -- in many catalogs, positions and
proper motions are given in different units.

Also note how the outer PM-based filter is just a WHERE-clause.  Since
JOIN is a combination of operators of the relational algebra, the result
of a join is a relation again and thus can be treated like any other
table.
\endbox
\endline

\startueb
\startprob
\bqu
Compare the total proper motions of the top 5000 stars in hipparcos and
tycho2, together with the respective identifiers (hip for hipparcos, id
for tycho2). Use a positional crossmatch with, say, a couple of arcsecs.
\equ
\bans
\startverbatim
SELECT TOP 5000 hipp.hip, id,
 SQRT(POWER(hipp.pmra,2)+POWER(hipp.pmdec,2))/1000 AS pmtothip,
 SQRT(POWER(t2.pmra,2)+POWER(t2.pmde,2))/1000 AS pmtott2
FROM hipparcos as hipp 
JOIN tycho2 AS t2 ON (
  1=CONTAINS(
    POINT('ICRS', hipp.ra, hipp.dec), 
    CIRCLE('ICRS', t2.ra, t2.dec, 0.001)))
|endverbatim
@eans
\endprob
\endueb

\endpage


\startpage
\title{Subqueries}
\startline{1}{0.8}
\startbox{1}
One of the more powerful features of SQL is that you can have subqueries
instead of tables within FROM.  Just put them in parentheses and give
them a name using AS.
This is particularly convenient when you first want to try some 
query on a subset of a big table:


\startex
SELECT count(*) as n, round((hmag-jmag)*2) as bin 
FROM (
  SELECT TOP 4000 * FROM twomass) AS q 
GROUP BY bin 
ORDER BY bin

|endex

% \startlongonly
% Another use of subqueries is in the connection with EXISTS, which is an
% operator on queries that's true when a query result is not empty.
% 
% Beware -- people coming from other languages have a tendency to use
% EXISTS when they should be using JOIN (which typically is easier to
% optimize for the database engine).  On the other hand, EXISTS frequently
% is the simpler and more robust solution.
% 
% As an example, to get arihip stars that happen to be in RAVE DR3, you
% could write both
% 
% \startex
% SELECT TOP 10 *
% FROM arihip.main as a
% WHERE
%   EXISTS (
%     SELECT 1 
%     FROM rave.dr3 as r
%     WHERE 1=CONTAINS(
%       POINT('ICRS', a.raj2000, a.dej2000),
%       CIRCLE('ICRS', r.raj2000, r.dej2000, 1/3600.)))
% |endex
% 
% or
% 
% \startex
% SELECT TOP 10 a.*
% FROM arihip.main as a
% JOIN rave.dr3 as r
% ON 1=CONTAINS(
%       POINT('ICRS', a.raj2000, a.dej2000),
%       CIRCLE('ICRS', r.raj2000, r.dej2000, 1/3600.))
% |endex
% 
% (but see the execise to this problem before making a pattern out of
% this).
% \endlongonly
\endbox
\endline
 
% \startueb
% \startprob
% \bqu
% Sit back for a minute and think whether the JOIN and the EXIST solution
% are actually equivalent  (Hint: They're not).  Think of how you'd need
% to change the queries to show that non-equivalence.
% \equ
% \endprob
% \endueb
% 
\endpage


\startpage
\title{TAP: Uploads}
\startline{1}{0.9}
\startbox{1}
TAP lets you upload your own tables into the server for the duration of
the query.

\startlongonly
Note that not all servers already support uploads.  If one doesn't,
politely ask the operators for it.
\endlongonly

Example: Take a subset of tgas\_source with positions and proper motions
and crossmatch it with sdss to get colors. First we make the subset
with:
\startex
SELECT TOP 200 
source_id, ra, dec, pmra, pmdec
FROM gaiadr1.tgas_source
WHERE 1=CONTAINS(POINT('ICRS', raj2000, dej2000),
      CIRCLE('ICRS', 18.02, 9.281, 4.0 ))
|endex

\endbox
\endline
\endpage



\startpage
\title{TAP: Uploads 2}
\startline{1}{0.9}
\startbox{1}


Then we change the TAP Service to
\dott{http://dc.zah.uni-heidelberg.de/tap} and perform the following
query:

\startex
SELECT TOP 100
   tgas.*, sdss.u, sdss.i, sdss.r, sdss.g
   FROM sdssdr7.sources AS sdss
   JOIN TAP_UPLOAD.t1 AS tgas
   ON 1=CONTAINS(
         POINT('ICRS', sdss.ra, sdss.dec),
         CIRCLE('ICRS', tgas.ra, tgas.dec, 3./3600.))  
|endex

\startlongonly
You must replace the 1 in \dott{tap\_upload.t1} with the index of the
table you want to match.

You may also need to adjust the column names of RA and Dec for your
table, and the match radius.

\endlongonly
\endbox

\endline

%\startueb
%\startprob
%\bqu
%If you have some data of your own, try getting it into TOPCAT and try
%this with it (but that's really more of a TOPCAT problem).
%\equ
%\endprob
%\endueb
%
%\startattach
%\attach{ex.vot}{ex.vot}{A sample VOTable for trying a crossmatch on an
%upload}
%\endattach
\endpage

\bye
