Examples for GAVO UDF 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