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
- LANG
- REQUEST
- FORMAT
- 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
- VOTABLE
- CSV
- TSV
- IPAC_TABLE
- HTML
- FITS
- HDF5
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:
- ra
- dec
- psi
- mjd
- signal
- glon
- glat
- sso
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).
- 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
- 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
- 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
- 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'}
- 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 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
- 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
- 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