/ csc / cli / adql.html

Using the Astronomical Data Query Language to access the CSC 2.1

Introduction

The Chandra Source Catalog database can be queried to select a subset of columns and rows to retrive selected properties about the sources. The "query language" used is version 2.0 of the Astronomical Data Query Language (ADQL), which is a database-query language similar to SQL that is designed for Astronomical Database access (it is an International Virtual Observatory Alliance standard). This document is not a guide to writing ADQL but is intended to show how queries can be written to access version 2.1 of the Chandra Source Catalog.

The command-line access to the CSC page describes how these ADQL queries can be used with the CSC.

ADQL support

The Chandra Source Catalog services do not support queries using the ADQL geometrical functions such as CIRCLE. For the common case of adding a cone search to a TAP query, users can use the dbo.cone_distance function. As an example, adding the following WHERE clause

ADQL

WHERE
  ((( ( m.dec BETWEEN 12.374454815683332 AND 12.407788149016667 ) AND
      ( m.ra BETWEEN 187.6888716832049 AND 187.72300001759513 ) ) AND
      dbo.cone_distance(m.ra,m.dec,187.7059358504,12.39112148235) <= 1.0
  ))

which will apply a 1 arcminute cone search around the master-source location of RA = 187.7059358504 degrees and Declination = 12.39112148235 degrees.

For more details see the cone-search example on the ADQL for CSC page.

Tip

Questions about ADQL queries of the CSC should be submitted to the CXC Helpdesk using the subject "Chandra Source Catalog".

ADQL and CSCView

It is strongly recommended that the CSCViw application be used to prototype and test-out your ADQL queries. Once you have set up a query (column choice in the Result Set and row filter in the Search Criteria sections), the AQDL version can be seen by selecting the menu item View/Query/Show Language.

The reason for doing this is that CSCView will include all the necessary tables in the query, which is particularly important when accessing columns from different tables - such as stack- or obsid-products linked to a master source. It also will calculate the necessary syntax for cone-searches (e.g. restricting your search to a radius around a point on the sky).

ADQL syntax

An ADQL SELECT statement returns a result set of records from one or more tables of astronomical data, located by the FROM clause. The available tables are:

ADQL name	Description
master_source m	Master Sources Table
stack_source s	Stacked Observation Detections
observation_source o	Per-Observation Detections
master_stack_assoc a	Master Source/Stack Source Associations
stack_observation_assoc b	Stack Source/Observation Source Associations
detect_stack d	Detect Stack/Observation Associations
valid_stack v	Valid Stack/Per-Observation Detection Associations
likely_stack l	Likely Stack/Per-Observation Detection Associations

Some of the optional clauses of a SELECT statement include:

TOP specifies the number of rows to retrieve.
WHERE specifies which rows to retrieve, according to the search criteria.
ORDER BY specifies an order in which to return the rows.

ADQL SELECT, TOP, FROM, WHERE, ORDER BY statements may also be entered into the ADQL window in the Query tab of the data access GUI CSCview. This view is accessed by selecting the menu item View->Query->Show Language while the Query tab is open.

Examples

The following section provides example queries. The first few match some of the Standard Queries provided by CSCView when the only filter applied is the default "top 1000" results. Those queries that only reference columns from the master-source table are relatively simple, but as different tables are compbined the complexity of the query increases.

Line breaks are added to help separate up the query, and to avoid some very-long lines. Please note that some applications require the query to be written as a single line.

Master Source Basic Summary

ADQL

SELECT DISTINCT top 1000
m.name,m.ra,m.dec,m.err_ellipse_r0,m.err_ellipse_r1,m.err_ellipse_ang,
m.significance,m.likelihood_class,m.conf_flag,m.sat_src_flag,m.streak_src_flag,
m.flux_aper_b,m.flux_aper_lolim_b,m.flux_aper_hilim_b,
m.flux_aper_w,m.flux_aper_lolim_w,m.flux_aper_hilim_w
FROM master_source m
ORDER BY name ASC

Master Source Variability

ADQL

SELECT DISTINCT top 1000
m.name,m.ra,m.dec,m.err_ellipse_r0,m.err_ellipse_r1,m.err_ellipse_ang,
m.significance,m.likelihood,m.likelihood_class,m.conf_flag,
m.dither_warning_flag,m.sat_src_flag,m.streak_src_flag,m.var_flag,
m.flux_aper_b,m.flux_aper_lolim_b,m.flux_aper_hilim_b,
m.flux_aper_w,m.flux_aper_lolim_w,m.flux_aper_hilim_w,
m.var_intra_index_b,m.var_intra_index_h,m.var_intra_index_m,m.var_intra_index_s,m.var_intra_index_u,m.var_intra_index_w,
m.var_intra_prob_b,m.var_intra_prob_h,m.var_intra_prob_m,m.var_intra_prob_s,m.var_intra_prob_u,m.var_intra_prob_w,
m.var_inter_index_b,m.var_inter_index_h,m.var_inter_index_m,m.var_inter_index_s,m.var_inter_index_u,m.var_inter_index_w,
m.var_inter_prob_b,m.var_inter_prob_h,m.var_inter_prob_m,m.var_inter_prob_s,m.var_inter_prob_u,m.var_inter_prob_w
FROM master_source m
ORDER BY name ASC

Stack Source Summary

As the following query returns columns from both the master-source and stack-detections tables, the WHERE clause is more-complicated than in previous examples, as it has to ensure that the master sources and stack detections are matched.

ADQL

SELECT DISTINCT top 1000
m.name,m.ra,m.dec,m.err_ellipse_r0,m.err_ellipse_r1,m.err_ellipse_ang,m.likelihood_class,
s.detect_stack_id,s.region_id,s.theta_mean,
s.flux_significance_b,s.flux_significance_w,s.likelihood_b,s.likelihood_w,
s.conf_code,s.extent_code,s.sat_src_flag,s.streak_src_flag,s.var_flag,
s.major_axis_b,s.minor_axis_b,s.pos_angle_b,s.major_axis_w,s.minor_axis_w,s.pos_angle_w,
s.src_cnts_aper_b,s.src_cnts_aper_w,s.src_rate_aper_b,s.src_rate_aper_w,
s.flux_aper_b,s.flux_aper_lolim_b,s.flux_aper_hilim_b,
s.flux_aper_w,s.flux_aper_lolim_w,s.flux_aper_hilim_w,
s.hard_hm,s.hard_hm_lolim,s.hard_hm_hilim,s.hard_ms,s.hard_ms_lolim,s.hard_ms_hilim,
s.var_intra_index_b,s.var_intra_index_w,s.var_inter_index_b,s.var_inter_index_w
FROM master_source m , master_stack_assoc a , stack_source s
WHERE
((a.match_type = 'u')) AND
 (m.name = a.name) AND
 (s.detect_stack_id = a.detect_stack_id and s.region_id = a.region_id)
ORDER BY name ASC

Source Observation Summary

As the following query returns columns from both the master-source and per-observation detection tables, the WHERE clause now has to link master to stack to observations.

ADQL

SELECT DISTINCT top 1000
m.name,m.ra,m.dec,m.err_ellipse_r0,m.err_ellipse_r1,m.err_ellipse_ang,m.likelihood_class,
o.obsid,o.obi,o.gti_obs,o.gti_end,o.region_id,o.theta,o.phi,
o.flux_significance_b,o.flux_significance_w,o.likelihood_b,o.likelihood_w,
o.conf_code,o.extent_code,o.sat_src_flag,o.streak_src_flag,o.var_code,
o.major_axis_b,o.minor_axis_b,o.pos_angle_b,o.major_axis_w,o.minor_axis_w,o.pos_angle_w,
o.cnts_aper_b,o.cnts_aper_w,o.src_cnts_aper_b,o.src_cnts_aper_w,
o.src_rate_aper_b,o.src_rate_aper_w,
o.flux_aper_b,o.flux_aper_lolim_b,o.flux_aper_hilim_b,
o.flux_aper_w,o.flux_aper_lolim_w,o.flux_aper_hilim_w,
o.hard_hm,o.hard_hm_lolim,o.hard_hm_hilim,o.hard_ms,o.hard_ms_lolim,o.hard_ms_hilim,
o.var_index_b,o.var_index_w,
o.livetime,o.detector
FROM master_source m , master_stack_assoc a , observation_source o , stack_observation_assoc b , stack_source s
WHERE
 ((a.match_type = 'u')) AND
  (m.name = a.name) AND
  (s.detect_stack_id = a.detect_stack_id and s.region_id = a.region_id) AND
  (s.detect_stack_id = b.detect_stack_id and s.region_id = b.region_id) AND
  (o.obsid = b.obsid and o.obi = b.obi and o.region_id = b.region_id)
ORDER BY name ASC

Searching for sources near a location

Here we use a "cone search" to find all master sources that lie within 5 arcminutes of a given location (in this case M57):

ADQL

SELECT DISTINCT
dbo.separation(m.ra,m.dec,283.39589,33.02857) as separation,m.name,m.ra,m.dec,
m.significance,m.likelihood_class,m.conf_flag,m.sat_src_flag,m.streak_src_flag,
m.flux_aper_b,m.flux_aper_lolim_b,m.flux_aper_hilim_b
FROM master_source m
WHERE
 ((((m.dec BETWEEN 32.945236666666666 AND 33.11190333333334) AND
    (m.ra BETWEEN 283.29649419346146 AND 283.49528580653856)) AND
   dbo.cone_distance(m.ra,m.dec,283.39589,33.02857)<=5.0))
ORDER BY separation ASC, name ASC

The spatial filter that restricts the Right Ascension and Declination values to ranges around 283.39589,33.02857 - the BETWEEN statements - are not required but do improve the efficiency of the query by reducing the number of points that are checked against the more-computationally-intensive cone_distance calculation.

Searching for variable sources

The following query returns a subset of the Master Source Basic Summary columns, but this time returning 10 rows that match the Search for Variable Sources Standard Search Criteria.

ADQL

SELECT DISTINCT TOP 10
m.name,m.significance,m.likelihood_class,m.conf_flag,m.sat_src_flag,m.streak_src_flag
FROM master_source m , master_stack_assoc a , observation_source o , stack_observation_assoc b , stack_source s
WHERE
 (((((o.var_index_b >= 7 AND o.flux_significance_b >= 3.0) OR
     (o.var_index_h >= 7 AND o.flux_significance_h >= 3.0) OR
     (o.var_index_m >= 7 AND o.flux_significance_m >= 3.0) OR
     (o.var_index_s >= 7 AND o.flux_significance_s >= 3.0) OR
     (o.var_index_u >= 7 AND o.flux_significance_u >= 3.0) OR
     (o.var_index_w >= 7 AND o.flux_significance_w >= 3.0)) AND
    o.dither_warning_flag = 0) OR
   (m.var_inter_index_b >= 7 AND o.flux_significance_b >= 3.0) OR
   (m.var_inter_index_h >= 7 AND o.flux_significance_h >= 3.0) OR
   (m.var_inter_index_m >= 7 AND o.flux_significance_m >= 3.0) OR
   (m.var_inter_index_s >= 7 AND o.flux_significance_s >= 3.0) OR
   (m.var_inter_index_u >= 7 AND o.flux_significance_u >= 3.0) OR
   (m.var_inter_index_w >= 7 AND o.flux_significance_w >= 3.0)) AND
  (a.match_type = 'u')) AND
 (m.name = a.name) AND
 (s.detect_stack_id = a.detect_stack_id and s.region_id = a.region_id) AND
 (s.detect_stack_id = b.detect_stack_id and s.region_id = b.region_id) AND
 (o.obsid = b.obsid and o.obi = b.obi and o.region_id = b.region_id)
ORDER BY name ASC

/ csc / cli / adql.html