Examples for GAVO Dataset-specific TAP Examples

Query for CALIFA object properties

This example shows how to combine the ↗califadr3.objects table of properties of CALIFA target galaxies with the ↗califadr3.cubes table of data cubes to select bright, early-type spirals:

SELECT target_name, accref, hubtyp, magg
FROM califadr3.cubes
  NATURAL JOIN califadr3.objects
WHERE hubtyp in ('S d', 'S cd', 'S c')
  AND magg<13

Make a color map from CALIFA cubes

Here is a query showing how to use the ↗califadr3.fluxv500 (or ↗califadr3.fluxv1200) tables to obtain maps of color indices:

SELECT TOP 20000 xindex, yindex, flux3807-flux3839 as color
FROM (
SELECT califaid, xindex, yindex, flux as flux3807
FROM califadr3.fluxv500
WHERE lambda=3807) AS t1
NATURAL JOIN (
SELECT califaid, xindex, yindex, flux as flux3839
FROM califadr3.fluxv500
WHERE lambda=3839) AS t2
WHERE califaid=124

Here, we (virtually) create two tables that have single-lambda maps of fluxes for a wavelength each. Note how we use column aliasing here to allow natural joining the tables and to have a nice expression in the select clause.

Note that it's fairly easy to turn this into "broadband" colors; you'll just have to adjust the inner selects, e.g., like this:

SELECT obsid, SUM(flux)/count(*) AS flux3839
  FROM califadr3.fluxv500
  WHERE lambda BETWEEN 3850 AND 3900
  GROUP BY obsid

Note that queries like this can run for quite a while, which means you probably will want to run them in async mode. Further note that NULL values in flux tend to make such images "spotty". To make things more robust, you could filter by WHERE flux IS NOT NULL in yet another inner select.

The result is in pixel coordinates. To turn them into real coordinates, you can use ↗califadr3.spectra, which as physical coordinates for triples of xindex, yindex, and califaid; while we're at it, we add links to the spectra, too, so you can easily check out interesting points:

select raj2000, dej2000, color, accref
from califadr3.spectra
join tap_upload.t1
using (xindex, yindex)
where califaid=124

We've worked with califaids here, because they're more natural for these low-level tables. To turn object names or positions to califaids, use ↗califadr3.objects.

Katkat bibliography (or other freetext matching)

To search for title (or other) words in ↗katkat.katkat's source field or in some other sort of bibliographic query, use the ivo_hasword "user defined function" (an ADQL extension which, in this case is also used by the relational registry). This basically works a bit like you'd expect from search engines: case-insensitive, and oblivious to any context.

SELECT *
FROM katkat.katkat
WHERE 1=ivo_hasword(source, 'variable')
  AND minEpoch<1920

Apply ICRS corrections to astrometry from USNO-B or UCAC3

The tables ↗ppmxl.usnocorr and ↗ucac3.icrscorr give mean corrections in 1-degree-sized squares in RA and Dec each to bring positions (and possibly proper motions) based on USNO-B1 and UCAC3 (closer) to ICRS. Consider the following query:

SELECT
   u.raj2000+d_alpha+d_pmalpha/cos(radians(u.dej2000))*(u.epoch-2000)
     AS ra_icrs,
   u.dej2000+d_delta+d_pmdelta*(u.epoch-2000) AS de_cicrs,
   u.pmra+d_pmalpha AS pmra_icrs,
   u.pmde+d_pmdelta AS pmde_icrs,
   u.*
FROM
  TAP_UPLOAD.T1 AS u
  JOIN ucac3.icrscorr AS c
  ON (c.alpha=FLOOR(u.raj2000)+0.5 and c.delta=FLOOR(u.dej2000)+0.5)

This (approximately; in reality, the corrections would need to be applied in the tangetial plane) applies corrections to a table you upload (i.e., a VOTable or something you can load into TOPCAT). As you can see, we assume the positions and proper motions in your table are in the raj2000, dej2000, pmra, and pmde columns, and epoch contains the epoch the data has been reduced to using the proper motions, in Julian years.

You will also have to adjust the string after TAP_UPLOAD according to whatever name you give to your upload. The query given would work when your table is the first table within TOPCAT.

The equations are taken from the service info; again, they are not exact and will fail miserably near the poles, for epochs far away from J2000., and they do not take into account crossing the RA=0 line.