ObsCore Examples from Heidelberg

Finding images by time and place

Suppose you read in an old amateur observer's log there was an unexpected object on the night sky in the cold winter nights of the week between January 12th and 18th, 1903 – and now you would like to see whether there could be an observation of such a thing.

When you look for observational products, it's a good idea to see if a service has the ↗ivoa.obscore table; its columns are standardised by IVOA, so the the following query can be run everywhere this table exists:

SELECT s_ra, s_dec, t_min FROM ivoa.obscore
  WHERE t_min BETWEEN gavo_to_mjd('1903-01-12')
      AND gavo_to_mjd('1903-01-19')

In ivoa.obscore, as in many other tables including the one our records come from (:taptable:lswscans.plates), dates are stored as modified julian dates; this is convenient for many purposes, but for most people not on input. This is why DaCHS (the software behind this data center) defines a "user defined function" (meaning: you won't have that on most other servers) gavo_to_mjd. Just pass an ISO date string to it and the rest is automatic.

Say you start looking for hints as to where the object might have been. Eventually, you figure out it was "near Aldebaran". You could use Simbad to get its position and then query like this (we've taken the liberty to illustrate that you can also enter dates in Julian days):

SELECT access_url, t_exptime, t_min FROM ivoa.obscore
  WHERE
    t_min BETWEEN gavo_to_mjd('J2416128.5')
      AND gavo_to_mjd('J2416133.5') AND
    1=CONTAINS(POINT('ICRS', s_ra, s_dec),
      CIRCLE('ICRS', 69, 16, 15))

to see if any plate center is 15 degrees around Aldebaran's (rough ICRS) position.

There is also a shortcut via user defined functions. As an extension to regular ADQL, DaCHS lets you write gavo_simbadpoint('object') and replaces the result with a position obtained from simbad, like this:

SELECT access_url, t_exptime, t_min FROM ivoa.obscore
  WHERE
    t_min BETWEEN gavo_to_mjd('J2416128.5')
      AND gavo_to_mjd('J2416133.5') AND
    1=CONTAINS(gavo_simbadpoint('Aldebaran'),
      CIRCLE('ICRS', s_ra, s_dec, 15))

Note how we needed to switch around the roles of Aldebaran's position and the positions we got from the database. There currently is no way to say "get a circle around a Simbad position".

Also note that you cannot pull a name from the database and try to resolve it via Simbad. For many reasons we would be very reluctant to add such a functionality.

Query Images by both Spatial and Spectral Attributes

Problem: Get me the metadata of all images in ↗ ivoa.obscore with a spatial resolution better than 1 arc second, within 10 degrees of α,δ = 157, 15 degrees, and containing light somewhere between 400 and 500 nm.

In contrast to the analogous example from the Obscore spec, we use a spatial cone search (which is much faster than separate constraints on RA and Dec on most services), and we use the ivo_interval_overlaps UDF on the spectral axis. This latter UDF is not avaliable on all TAP services.

SELECT * FROM ivoa.obscore 
WHERE 
	dataproduct_type='image' 
	AND s_resolution < 1 
	AND distance(s_ra, s_dec, 157, 15) < 10
	AND 1=ivo_interval_overlaps(em_min, em_max, 400e-9, 500e-9)

Query and Retrieve Spectral Data in Your Units

Every branch of astrophysics has their own preferred sets of spectral units, and Obscore's choice of meters of wavelength is inconvenient for just about everyone. To mitigate that, DaCHS servers have the gavo_specconv UDF that accepts a spectral expression and the target unit. A second variant, intended for when the server cannot infer the unit, accepts a value and the source and target units.

Here is how you can use it to query in, say, Picometers and retrieve the spectral bounds in keV.

SELECT TOP 30
  obs_title, dataproduct_type, access_url, 
  gavo_specconv(em_min, 'keV') as e_max, 
  gavo_specconv(em_max, 'keV') as e_min
FROM ivoa.obscore 
WHERE 
  1=ivo_interval_overlaps(em_min, em_max,
    gavo_specconv(500, 'pm', 'm'), gavo_specconv(600, 'pm', 'm'))

Note that when you constrain energies or frequencies, you will have to swap minimum and maximum with respect to wavelengths.

Find Data Collections Within the Table

You may want to see what the valid values in the obs_collection column are, as there really is no a priori way of knowing that – and this works the same way for other enumerated columns like dataproduct_subtype, target_name, or target_class (though that should one day use values from the vocabulary of object types). This works like this

SELECT DISTINCT obs_collection
FROM (SELECT TOP 30 * FROM ivoa.obscore) AS q

– except that you want to replace the subquery with just ivoa.obscore. The subquery is here because on larger sites, this query can take long (a minute or so, requiring async mode), and we don't want robots to blindly run this. The same goes for obtaining things like minimum and maximum on float-valued columns.

A faster way to obtain statistics is to inspect pre-computed column statistics. These are not standardised in the VO yet, but may be available as extensions via VOSI tables.

Query With a Geometry

The s_region column contains an ADQL “geometry“ (in practice, at the moment: a polygon, though it may become a MOC in the future) for most rows; beware, though, that some data sets may have s_ra and s_dec but no s_region, so going through s_region may miss, in particular, some spectra).

You can use this with the usual ADQL geometry functions. To find images overlapping some circle, you would say:

SELECT TOP 3 s_region, access_url
FROM ivoa.obscore
WHERE 
	dataproduct_type='image'
	AND 1 = INTERSECTS(
		s_region,
		CIRCLE(142, -30, 1))

Please report errors and problems to the site operators. Thanks.