Table information for 'dfbsspec.raw_spectra'

Locate Spectra by Features

While ADQL support for array operations is rather weak, you can subscript arrays. Because of the fixed bins, you can therefore select by flux ratios (never use absolute numbers here; they are meaningless). For instance, to select objects with a high (apparent) Halpha emission (656 nm, corresponding to array index 3), you might so something like:

select * from dfbsspec.spectra
where
  flux[3]/(flux[40]+flux[41]+flux[42])>30
  and sp_class='OK'

Since the table needs to be sequentially scanned for this, it will take a minute or so. Combine with an object selection (see below) or other criteria if possible.

Get Average Spectra

You cannot currently use the ADQL aggregate function AVG with arrays (which should be fixed at some time in the future). Meanwhile, you can work around this with a clumsy construction like this (this query will give you average spectra by magnitude bin; don't run it just for fun, it'll take a while):

select round(magb) as bin, avg(flux[1]) as col1, avg(flux[2]) as col2,
  avg(flux[3]) as col3, avg(flux[4]) as col4, avg(flux[5]) as col5,
  avg(flux[6]) as col6, avg(flux[7]) as col7, avg(flux[8]) as col8,
  avg(flux[9]) as col9, avg(flux[10]) as col10, avg(flux[11]) as col11,
  avg(flux[12]) as col12, avg(flux[13]) as col13, avg(flux[14]) as col14,
  avg(flux[15]) as col15, avg(flux[16]) as col16, avg(flux[17]) as col17,
  avg(flux[18]) as col18, avg(flux[19]) as col19, avg(flux[20]) as col20,
  avg(flux[21]) as col21, avg(flux[22]) as col22, avg(flux[23]) as col23,
  avg(flux[24]) as col24, avg(flux[25]) as col25, avg(flux[26]) as col26,
  avg(flux[27]) as col27, avg(flux[28]) as col28, avg(flux[29]) as col29,
  avg(flux[30]) as col30, avg(flux[31]) as col31, avg(flux[32]) as col32,
  avg(flux[33]) as col33, avg(flux[34]) as col34, avg(flux[35]) as col35,
  avg(flux[36]) as col36, avg(flux[37]) as col37, avg(flux[38]) as col38,
  avg(flux[39]) as col39, avg(flux[40]) as col40, avg(flux[41]) as col41,
  avg(flux[42]) as col42, avg(flux[43]) as col43, avg(flux[44]) as col44,
  avg(flux[45]) as col45, avg(flux[46]) as col46, avg(flux[47]) as col47,
  avg(flux[48]) as col48, avg(flux[49]) as col49, avg(flux[50]) as col50,
  avg(flux[51]) as col51, avg(flux[52]) as col52, avg(flux[53]) as col53,
  avg(flux[54]) as col54, avg(flux[55]) as col55, avg(flux[56]) as col56,
  avg(flux[57]) as col57, avg(flux[58]) as col58, avg(flux[59]) as col59
from dfbsspec.spectra
where sp_class='OK'
group by bin

To map col<n> to wavelenghts, see the contents of (any) spectral column.

Build Templates

To compute an average spectrum for a class of objects, we suggest to pull positions of such objects from SIMBAD and then fetch the associate spectra from this database. Since the response function of the photographic plates had a strong magnitude dependence, restrict the objects to a small magnitude range, for instance:

select
    otype, ra, dec, flux
from basic
join flux
on (oid=oidref)
where
    otype='HS*'
    and dec>-15
    and filter='G'
    and flux between 12.5 and 13.5

(to be executed on SIMBAD's TAP service, see also SIMBAD object types).

With the resulting table, go do this service and execute a query like:

SELECT
specid, spectral, flux
FROM dfbsspec.spectra AS db
JOIN TAP_UPLOAD.t1 AS tc
ON DISTANCE(tc.ra, tc.dec, db.ra, db.dec)<5./3600.
WHERE sp_class='OK'

(adjust t1 according to your client's rules; in TOPCAT, that's t plus the table number from the control window).