API for Planck TOI Dataset

The API for searching the Planck TOI's is a subset of the IVOA ADQL 2.0 standard. This is an SQL-like calling syntax. There are 4 keywords needed for a query

The keywords can be supplied in the URL or uploaded with a post. All keywords are case insensitive.

LANG

This must be set to 'ADQL'.

REQUEST

The only supported value for this parameter is 'REQUEST'.

FORMAT

This keyword specifies the output format. The supported output formats are

QUERY

This keyword specifies the query. The basic structure of a simple query will be something like
SQL_STATEMENT="select {columns} from {table} where {geometric constraint} and ({sql constraint})

{table}

The {table} in the SQL statement will be in the form planck_toi_freq. For example:
select * from planck_toi_100
will select everything from the 100 GHz detectors.

{columns}

The {column} names will be one of: You can also apply functions to the columns. For example
select round(mjd,0) from planck_toi_100
will round off the date to the nearest day. These functions are delegated to an Sqlite3 database, so the only supported functions are those that are both defined in ADQL 2.0 and implemented by Sqlite3.

{geometric constraint}

There are three supported geometric types.

Circle

select * from planck_toi_100 where CONTAINS(POINT('J2000',ra,dec),CIRCLE('J2000',121.17440,-21.57294,1.0))=1

Box

select * from planck_toi_100 where CONTAINS(POINT('J2000',ra,dec),BOX('J2000',121.17440,-21.57294,1.0,1.0))=1

Polygon

select * from planck_toi_100 where CONTAINS(POINT('J2000',ra,dec),POLYGON('J2000',121.17440,-21.57294,121.17440,-22.57294,122.17440,-22.57294,))=1
All angles are in degrees. In the previous three examples, the coordinates are all in J2000. To input in galactic coordinates, use 'GALACTIC' instead of 'J2000' in the shape. For example, the previous circle query using galactic coordinates would be
select * from planck_toi_100 where CONTAINS(POINT('J2000',ra,dec),CIRCLE('GALACTIC',0.05296281,-0.06192036,1.0))=1

{sql constraint}

The {sql constraint} can be any constraint expressible in ADQL 2.0, with the restriction that functions must be supported by the Sqlite backend. So, for example, this query will filter the results to a time range.
select * from planck_toi_100 where (mjd>=55550.0 and mjd<=65650.5)

USER_METADATA

This keyword allows you to insert arbitrary metadata into the resulting tables. The format is JSON5. For example, if you want to replace the OBJECT metadata with something more understandable (e.g. m101 instead of 210.80227, 54.34895), then you could use the string
{OBJECT:"m101"}

Optimizing Queries

Two types of queries are very common and have been optimized in the current implementation.

Histograms

If the query is of the form
select round(mjd,0) AS rmjd, count(mjd) AS counter from {table} where {geometric_constraint} and (sso='0') group by rmjd
then a special histogram routine will be used which is significantly faster. This is one example.

Simple Query

If the query is simple enough such that
  • {sql_constraint} is empty
  • There are no GROUP BY, ORDER BY, or HAVING statements
  • DISTINCT is not used, though ALL is allowed
  • Columns have no expressions in them, though AS statements are allowed
  • then the table is generated directly instead of going through any database engine. This can be a factor of two faster. This is one example.

    HDF5

    In addition, HDF5 files are generated faster than other formats. Fits comes in second, with the text based formats being significantly slower.






    URL Examples:


    These examples all incorporate a 1 degree cone search at (121.17440,-21.57294).

    1. Get all columns within a time range
      https://irsa.ipac.caltech.edu/TAP/sync?LANG=ADQL&REQUEST=doQuery&QUERY=SELECT+*+FROM+planck_toi_044+WHERE+CONTAINS(POINT('J2000',ra,dec),CIRCLE('J2000',121.17440,-21.57294,1.0))=1+and+(mjd>=55550.0+and+mjd<=65650.5)&format=ipac_table
    2. Get only column "mjd" within a time range in fits format
      https://irsa.ipac.caltech.edu/TAP/sync?LANG=ADQL&REQUEST=doQuery&QUERY=SELECT+mjd+FROM+planck_toi_044+WHERE+CONTAINS(POINT('J2000',ra,dec),CIRCLE('J2000',121.17440,-21.57294,1.0))=1+and+(mjd>=55550.0+and+mjd<=65650.5)&format=fits
    3. Get columns "ra", "dec", "mjd", and "detector" from the "24M" and "24S" detectors
      https://irsa.ipac.caltech.edu/TAP/sync?LANG=ADQL&REQUEST=doQuery&QUERY=SELECT+ra,dec,mjd,detector+FROM+planck_toi_044+WHERE+CONTAINS(POINT('J2000',ra,dec),CIRCLE('J2000',121.17440,-21.57294,1.0))=1+and+(detector='24M'+or+detector='24S')&format=ipac_table
    4. Rename the OBJECT metadata in the result table to 'My favorite object'. This query is simple enough to be generated directly instead of going through a database backend.
      https://irsa.ipac.caltech.edu/TAP/sync?LANG=ADQL&REQUEST=doQuery&QUERY=SELECT+ra,dec,mjd,detector+FROM+planck_toi_044+WHERE+CONTAINS(POINT('J2000',ra,dec),CIRCLE('J2000',121.17440,-21.57294,1.0))=1&format=ipac_table&user_metadata={OBJECT:'My favorite object'}
    5. Get which days and how many times on that day the telescope observed the search position using round(,) and group by. This query will use the optimized histogram routines.
      https://irsa.ipac.caltech.edu/TAP/sync?LANG=ADQL&REQUEST=doQuery&QUERY=SELECT+round(mjd,0)+as+rmjd,count(mjd)+FROM+planck_toi_044+WHERE+CONTAINS(POINT('J2000',ra,dec),CIRCLE('J2000',121.17440,-21.57294,1.0))=1+group+by+rmjd&format=ipac_table
    These examples demonstrates different geometries. They return all columns from all detectors. These queries are simple enough to be generated directly instead of going through a database backend.
    1. 1 degree square box search at (121.17440,-21.57294).
      https://irsa.ipac.caltech.edu/TAP/sync?LANG=ADQL&REQUEST=doQuery&QUERY=SELECT+*+FROM+planck_toi_044+WHERE+CONTAINS(POINT('J2000',ra,dec),BOX('J2000',121.17440,-21.57294,1.0,1.0))=1&format=ipac_table
    2. 1 degree square box search at (0.05296281,-0.06192036) Galactic.
      https://irsa.ipac.caltech.edu/TAP/sync?LANG=ADQL&REQUEST=doQuery&QUERY=SELECT+*+FROM+planck_toi_044+WHERE+CONTAINS(POINT('J2000',ra,dec),BOX('GALACTIC',0.05296281,-0.06192036,1.0,1.0))=1&format=ipac_table
    3. Polygon search (triangle) with one corner at (121.17440,-21.57294).
      https://irsa.ipac.caltech.edu/TAP/sync?LANG=ADQL&REQUEST=doQuery&QUERY=SELECT+*+FROM+planck_toi_044+WHERE+CONTAINS(POINT('J2000',ra,dec),POLYGON('J2000',121.17440,-21.57294,122.17440,-21.57294,121.17440,-22.57294))=1&format=ipac_table