Examples for RegTAP examples

Find all TAP services; return their accessURLs.

As the capability type is in ↗rr.capability, whereas the access URL can be found from ↗rr.interface, this requires a (natural) join.

Clients communicating with a RegTAP 1.1 or later service should request the new authenticated_only column. If this is 1, the service requires some sort of authentication and should only presented to users if a client has the necessary infrastructure for the authentication system.

Hence, clients only interested in services not requiring authentication should use

SELECT ivoid, access_url
FROM rr.capability
NATURAL JOIN rr.interface
WHERE standard_id like 'ivo://ivoa.net/std/tap%'
      AND intf_role='std'
      AND authenticated_only=0

Analogous considerations apply to the other example queries

Other standard_id-s relevant here include:

• ivo://ivoa.net/std/registry for OAI-PMH services,
• ivo://ivoa.net/std/sia for SIA services,
• ivo://ivoa.net/std/conesearch for SCS services, and
• ivo://ivoa.net/std/ssa for SSA services.

Find all SIA services that might have spiral galaxies

This is somewhat tricky since it is probably hard to image a part of the sky guaranteed not to have some, possibly distant, spiral galaxy in it. However, translating the intention into “find all SIA services that mention spiral in either the subject (from ↗rr.res_subject), the description, or the title (which are in ↗rr.resource)“, the query would become:

SELECT ivoid, access_url
FROM rr.capability
  NATURAL JOIN rr.resource
  NATURAL JOIN rr.interface
  NATURAL JOIN rr.res_subject
WHERE standard_id like 'ivo://ivoa.net/std/sia%'
  AND intf_role='std'
  AND (
    1=ivo_nocasematch(res_subject, '%spiral%')
    OR 1=ivo_hasword(res_description, 'spiral')
    OR 1=ivo_hasword(res_title, 'spiral'))

Find all SIA services that provide infrared images

The waveband information in ↗rr.resource comes in hash-separated atoms (which can be terms from https://www.ivoa.net/rdf/messenger). For matching those, use the ivo_hashlist_has function as below. The access URL and the service standard come from ↗rr.interface and ↗rr.capability, respectively.

SELECT ivoid, access_url
FROM rr.capability
  NATURAL JOIN rr.resource
  NATURAL JOIN rr.interface
WHERE standard_id LIKE 'ivo://ivoa.net/std/sia%'
  AND intf_role='std'
  AND 1=ivo_hashlist_has(waveband, 'infrared')

Find all searchable catalogues that have redshifts

Metadata on columns exposed by a service are contained in ↗rr.table_column. Again, this table can be naturally joined with ↗rr.capability and ↗rr.interface.

SELECT ivoid, access_url
FROM rr.capability
  NATURAL JOIN rr.table_column
  NATURAL JOIN rr.interface
WHERE standard_id LIKE 'ivo://ivoa.net/std/conesearch%'
  AND intf_role='std'
  AND ucd='src.redshift'

Sometimes you want to match a whole set of ucds. Frequently the simple regular expressions of SQL will help, as in AND ucd LIKE 'pos.parallax\%'. When that is not enough, use boolean OR expressions.

Find all the resources published by a certain authority

An “authority” within the VO is something that hands out identifiers. You can tell what authority a record came from by looking at the “host part” of the IVO identifier, most naturally obtained from ↗rr.resource. Since ADQL cannot actually parse URIs, we make do with simple string matching:

SELECT ivoid
FROM rr.resource
WHERE ivoid LIKE 'ivo://org.gavo.dc%'

What registry records are there from a given publisher?

This uses the ↗rr.res_role table both to match names (in this case, a publisher that has “gavo” in its name) and to ascertain the named entity actually publishes the resource (rather than, e.g., just being the contact in case of trouble). The result is a list of ivoids in this case. You could join this with any other table in the relational registry to find out more about these services.

SELECT ivoid
FROM rr.res_role
WHERE 1=ivo_nocasematch(role_name, '%gavo%')
  AND base_role='publisher'

or, if the publisher actually gives its ivo-id in the registry records:

SELECT ivoid
FROM rr.res_role
WHERE role_ivoid='ivo://ned.ipac/ned'
  AND base_role='publisher'

What registry records are there originating from registry X?

This is mainly a query interesting for registry maintainers. Still, it is a nice example for joining with the ↗rr.res_detail table, in this case to first get a list of all authorities managed by the CDS registry.

SELECT ivoid FROM rr.resource
RIGHT OUTER JOIN (
  SELECT 'ivo://' || detail_value || '%' AS pat
  FROM rr.res_detail
  WHERE detail_xpath='/managedAuthority'
    AND ivoid='ivo://cds.vizier/registry')
  AS authpatterns
ON 1=ivo_nocasematch(resource.ivoid, authpatterns.pat)

Find all TAP endpoints offering the relational registry

This is the discovery query for RegTAP services themselves; note how this combines ↗rr.res_detail pairs with ↗rr.capability and ↗rr.interface to locate the desired protocol endpoints. As clients should not usally be concerned with minor versions of protocols unless they rely on additions made in later versions, this query will return endpoints supporting “version 1” rather than exactly version 1.2.

SELECT access_url
FROM rr.interface
NATURAL JOIN rr.capability
NATURAL JOIN rr.res_detail
WHERE standard_id LIKE 'ivo://ivoa.net/std/tap%'
  AND intf_role='std'
  AND detail_xpath='/capability/dataModel/@ivo-id'
  AND 1=ivo_nocasematch(detail_value,
    'ivo://ivoa.net/std/regtap#1.%')
  AND authenticated_only=0

Find all TAP services exposing a table with certain features

“Certain features” could be “have some word in their description and having a column with a certain UCD”. Either way, this kind of query fairly invariably combines the usual ↗rr.capability and ↗rr.interface for service location with ↗rr.table_column for the column metadata and ↗rr.res_table for properties of tables.

SELECT ivoid,
  name, ucd, column_description,
  access_url
FROM rr.capability
  NATURAL JOIN rr.interface
  NATURAL JOIN rr.table_column
  NATURAL JOIN rr.res_table
WHERE standard_id LIKE 'ivo://ivoa.net/std/tap%'
  AND intf_role='std'
  AND 1=ivo_hasword(table_description, 'quasar')
  AND ucd='phot.mag;em.opt.v'

Find all SSAP services that provide theoretical spectra.

The metadata required to solve this problem is found in the SSAP registry extension and is thus kept in ↗rr.res_detail:

SELECT access_url
FROM rr.res_detail
  NATURAL JOIN rr.capability
  NATURAL JOIN rr.interface
WHERE detail_xpath='/capability/dataSource'
  AND intf_role='std'
  AND standard_id LIKE 'ivo://ivoa.net/std/ssa%'
  AND detail_value='theory'

Find a contact person by access URL

This uses the ↗rr.res_role table and returns all information on it based on the IVOID of a service that in turn was obtained from ↗rr.interface. You could restrict to the actual technical contact person by requiring base_role='contact'.

SELECT DISTINCT base_role, role_name, email
FROM rr.res_role
  NATURAL JOIN rr.interface
WHERE access_url='http://dc.zah.uni-heidelberg.de/tap'

Get the capabilities of all services serving a specific resource

In the VO, data providers can register data collections either as such or with “auxiliary capabilities” that are fully described elsewhere; a practice for doing that is discussed in an Endorsed Note on discovering data collections within services.

When following this pattern, data collections records should provide an isServedBy relationship to the resources providing the access services for the data collection (like a TAP or a SIAP service).

While the access URLs can typically be established from the auxiliary capabilities themselves, several use cases require finding out more about the publishing service. To locate its metadata, inspect ↗rr.relationship and use it to select records from ↗rr.capability; this requires an explicit join condition, as in this case the capabilities are for the related record, not for the originally matched one.

SELECT *
FROM rr.relationship AS a
  JOIN rr.capability AS b
    ON (a.related_id=b.ivoid)
WHERE
  relationship_type='isservedby'
  AND a.ivoid='ivo://cds.vizier/j/a+a/649/a25'

Constraints on Space, Time, and Spectrum

Consider the example: ”Give me resources that cover M 101 (α=210.80, δ=54.35, Diameter about 0.3°) in the mid-infrared around 5μm in August 2010.

Without further database support, clients need to manually convert the spectral coordinate to energy (hc/λ ≈ 3.97 × 10^-20 J and time (August 1st, 2010 starts MJD 55409.0) to the quantities RegTAP expects.

This would yield a query like (the explicit MOC conversion is a common device to speed the query up; without it, the database would convert the circle once for each coverage, to the respective order):

SELECT ivoid
FROM rr.stc_spatial
  NATURAL JOIN rr.stc_spectral
  NATURAL JOIN rr.stc_temporal
WHERE
  1=CONTAINS(MOC(8, CIRCLE(210.80, 54.35, 0.3)), coverage)
  AND 1=ivo_interval_overlaps(time_start, time_end, 55409, 55440)
  AND 3.97e-20 between spectral_start and spectral_end

In particular when more complex geometries are desired, clients will want to pass in MOCs directly. Conversely, RegTAP services may provide the additional user-defined functions that allow specifying temporal and spectral constraints in different, perhaps human-friendlier ways. For instance, once support for the relevant UDFs is established using the TAP capabilities, the above query could also be written as (the MOC given is the circle above at order 8):

SELECT ivoid
FROM rr.stc_spatial
  NATURAL JOIN rr.stc_spectral
  NATURAL JOIN rr.stc_temporal
WHERE
  1=CONTAINS(MOC('8/182947 182950 182952-182953 182955-182956 8/'), coverage)
  AND 1=ivo_interval_overlaps(
    time_start, time_end,
    gavo_to_mjd('2010-08-01'), gavo_to_mjd('2010-08-31'))
  AND gavo_specconv(5e-6, 'm', 'J') between spectral_start and spectral_end

Query expansion in subjects

Using the gavo_vocmatch user-defined-functions, you can do what is called query expansion in information retrieval, that is: not only query for a keyword, but also for related terms.

In the relational registry, this concerns subjects, which should come from the IVOA rendering of the Unified Astronomy Thesaurus. To find resources for brown dwarfs, you would query:

SELECT ivoid, res_subject FROM rr.res_subject
WHERE res_subject='brown-dwarfs'

However, this will miss resources talking about t-dwarfs, l-dwarfs and several other concepts. To include those (“narrower”) concepts, write:

SELECT ivoid, res_subject FROM rr.res_subject
WHERE 1=gavo_vocmatch('uat', 'brown-dwarfs', res_subject)

instead. In reality, far too few data providers actually use the UAT (correctly). We hence have a local extension table, ↗rr.subject_uat, that is res_subject with legacy terms mapped to UAT concept identifiers (translated to plain English: use this table instead of ↗rr.res_subject for the time being). In there, you would do something like:

SELECT ivoid, uat_concept FROM rr.subject_uat
WHERE 1=gavo_vocmatch('uat', 'brown-dwarfs', uat_concept)

Find resources for a set of points

Several (but unfortunately by no means all) service operators define the spatial coverage of their services. This information is havested into the proposed new relational registry table ↗rr.stc_spatial. One way to use this information is to match the resources against the points in an upload and perhaps see if they are in the infrared band, like this:

SELECT ivoid FROM
rr.resource
NATURAL JOIN
(SELECT DISTINCT
 ivoid
 FROM rr.stc_spatial AS db
 JOIN TAP_UPLOAD.t1 AS tc
 ON 1=CONTAINS(POINT('ICRS', tc.ra, tc.dec), coverage)) AS spate
WHERE 1=ivo_hashlist_has(waveband, 'Infrared')

As usual, you will have to adapt the index after TAP_UPLOAD. To find out access URLs of these services, NATURAL JOIN with rr.interface. For more information, see also the RegTAP specification