Examples for GAVO UDF TAP Examples

Using ivo_histogram

While you will usually use simple GROUP statements to create histograms, in some use cases it is convenient to have histograms in cells. To support this, DaCHS has an array-valued ivo_histogram(col, low, high, n) function. It will return an array, where the first and last elements count under- and overflows, respectively (i.e., in how many rows col was below low or above high). Between these, there are n bins of width (high-low)/n with the respective counts.

A simple example, in this case exploiting that Gaia's source_id (as in ↗gaia.dr2light) contains healpix numbers, would be:

SELECT
        ivo_histogram(phot_g_mean_mag, 0, 20, 20) as hist,
        ROUND(source_id/140737488355328) as hpx
FROM
        gaia.dr2light
WHERE source_id BETWEEN 281474976710656000 AND 288511851128422400
GROUP BY hpx

In comparison, the relationally-preferred (and portable) way to do the same thing would be:

SELECT
        ROUND(phot_g_mean_mag) as bin,
        ROUND(source_id/140737488355328) as hpx
FROM
        gaia.dr2light
WHERE source_id BETWEEN 281474976710656000 AND 288511851128422400
GROUP BY hpx, bin

– in this case, the aggregation by healpix needs to be done on the client side.

ivo_hasword and the katkat bibliography

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

Doing “symbolic” searches using gavo_simbadpoint

When you have an object name that Simbad understands, you can save a manual trip to Simbad by using DaCHS gavo_simbadpoint UDF. It takes a literal string (meaning: you cannot take names from the database, must type a string into the query) as an argument and returns an ADQL point. For instance, you can get basic Gaia DR3 data on the star HD 168504 with:

SELECT * FROM gaia.dr3lite
        WHERE DISTANCE(POINT(ra, dec), gavo_simbadpoint('HD 168504'))<0.001

Make a HEALPIX map for something

This service understands the the ivo_healpix_index extension function (and its inverse, ivo_healpix_center, returning the center of a given HEALPix). This is nifty for computing properties over equal-sized parts of the sky, in particular when operating over large datasets (like ↗gaia.dr3lite, ↗ppmxl.main, ↗twomass.data, ↗sdssdr16.main). Here is a simple example over a small table with large HEALPixes; for large tables, it will take this machine between 15 and 30 minutes to sift through 1 billion objects in this way:

SELECT
        MAX(parallax)/AVG(parallax) AS obs,
        ivo_healpix_index(4, ra, dec) AS hpx
FROM hipparcos.main
GROUP BY hpx

Note that you need to raise MAXREC to get back the whole sky for HEALPix orders higher than 4, and be sure to check out TOPCAT's HEALPix plot (see Graphics/Sky Plot, and then Layers/Add Healpix Control).