Examples for TOSS -- Tübingen Oscillator Strengths Service

Matching a line list

TOSS is accessible for remote database queries through TAP as ↗toss.data. You can therefore match local line lists. This example assumes you're using the TOPCAT TAP client (but any other should do as well).

Load the fictional line list from http://dc.g-vo.org/toss/q/s/static/sample.vot. Then open VO/TAP, search for TOSS and select the GAVO DC server.

To simply match lines (with a bit of a wiggle), you could use the following query:

SELECT t.* FROM toss.data AS t
        JOIN TAP_UPLOAD.t1 AS u
        ON (abs(t.wavelength-in_unit(u.lambda, 'm'))<1e-10)

Note that toss.data uses the IVOA spectral line data model and therefore has vacuum wavelengths throughout the electromagnetic spectrum.

You may have to change the t1 after TAP_UPLOAD and adapt the 1 the number the sample table has in TOPCAT's table list. If you use tables of your own, you will have to change lambda to whatever name the column has in your table.

What's happening here is that toss.data is "joined" (in effect, building a cartesian product) with the table you uploaded. There's a join condition (after the ON) which says that the two wavelengths must be within an Angstrom of each other.

Tables in the GAVO data center in general are in plain metric units to save headaches when combining them. The uploaded table has lambda in Angstrom (check the table metadata), so we use the in_unit function to turn it into meters.

Also note the table aliases: To save typing, we're assinging t and u to the database and uploaded tables, respectively, in the AS clauses. You can even use that in the FROM clause: t.* just takes all the columns from the database table.

There's quite a lot of rows coming back there. To only retrieve the 10 lines with the highest oscillator stength, add two simple ingredients:

SELECT TOP 10 t.wavelength, linename, log_gf, ga
FROM toss.data AS t
        JOIN TAP_UPLOAD.t1 AS u
        ON (abs(t.wavelength-in_unit(u.lambda, 'm'))<1e-10)
ORDER BY log_gf DESC

In addition to our last query, we have restricted the result to 10 matches and told the engine to sort by log_gf (DESCending).

Also, we now specify what columns we want in the result. In such a join, it's enough to just give the name if the column is only present in one table. Otherwise, it must be "qualified", i.e., furnished with a table name (as wavelength is in the example, though it wouldn't have been necessary here).

Aggregate functions on TOSS

To follow this example, you should prepare your TAP client as in the previous example; again, we're talking about ↗toss.data.

The sample table used there has another column, strength. Let's say that's something like a filter width and we want to know how many lines are within than many Angstroms of the line center:

SELECT u.lambda, COUNT(*) as n_lines
FROM toss.data AS t
        JOIN TAP_UPLOAD.t1 AS u
        ON (abs(t.wavelength-in_unit(u.lambda, 'm'))<strength*1e-10)
GROUP BY u.lambda

GROUP BY takes a bit of wrapping one's mind around -- essentially, it groups the result by distinct values of its argument. Clearly, the select clause can then only refer to what's constant within such a group. This can be what the table is grouped by, or it can be an "aggregate function", a function working on the elements of the group. COUNT just counts them, but there's also things like AVG and SUM:

SELECT u.lambda, sqrt(COUNT(*)) as weight,
        log(SUM(exp(log_gf))) as gfprod, AVG(cf) as cfavg
FROM toss.data AS t
        JOIN TAP_UPLOAD.t1 AS u
        ON (abs(t.wavelength-in_unit(u.lambda, 'm'))<strength*1e-10)
GROUP BY u.lambda

For this example's sake, we sum the oscillator strengths (rather than doing something sensible with them) -- this shows that the arguments of the aggregate functions can be arithmetic expressions.