Retrieving Exoplanet Archive Data With Table Access Protocol

Skip to:

Synchronous and Asynchronous Queries
Constructing Synchronous TAP Queries
Using Spatial Constraints in Queries
Retrieving Table Schema
Best Practices
More Examples

The NASA Exoplanet Archive's TAP service allows users to programmatically access and retrieve data in the Planetary Systems (PS) table as an alternative to the interactive web interface. To programmatically retrieve data from other archive tables, such as the Kepler Objects of Interest or Microlensing tables, please use the archive's existing application programming interface (API). The Exoplanet Archive will be transitioning the APIs to these tables to the TAP over the coming months.

Table Access Protocol (TAP) is a recommended standard developed by the Virtual Astronomy community and now maintained by the International Virtual Astronomy Alliance (IVOA). The TAP technical specification is on the IVOA website (http://www.ivoa.net/documents/TAP).

The archive's TAP service uses Structured Query Language (SQL), and the output can be formatted in VOTable, comma-separated values (CSV), or tab-separated values (TSV). You may also select output columns and perform functions on the results.

For a complete list of Planetary Systems table data columns and their definitions, see the Planetary System Data Column Definitions document.


Top

Synchronous and Asynchronous Queries

In a nutshell, a synchronous query runs until it completes, and then streams the results back. An asynchronous query runs in the background and gives you a place to check whether your job is done, rather than streaming results back directly.

The archive's TAP service supports both synchronous and asynchronous queries, but the PS table is small enough that synchronous queries should be adequate. However, we do recommend using a TAP client when downloading large data sets (e.g., the entire Planetary Systems table) in the default VOTable format because some web browsers may struggle to display all of the data.


Top

Constructing Synchronous TAP Queries

TAP is a URL-based HTTP web service, so the query can be entered directly into a web browser. TAP-compatible clients and software like TOPCAT and PyVO, and TAP+ all work with the Exoplanet Archive TAP service. A list of valid queries are given at the end of this document.


Regardless of TAP client, all queries must have valid ADQL, which takes the form:
SELECT <column list> FROM <table> WHERE <constraints>



To construct a synchronous TAP query:

(Required) Step 1: Start with the base service URL.

  • For web browsers: https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query=
  • For clients such as TOPCAT and pyVO: https://exoplanetarchive.ipac.caltech.edu/TAP

(Required) Step 2: Next, add the query argument using the required select and from ADQL statements (in this order).

  • select specifies which columns to return. An asterisk (*) tells the system to return all columns.
  • from specifies the database table.

In the following example, the entire Planetary Systems table is returned in VOTable format:

https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query=select+*+from+ps

Alternatively, you can name specific columns (separated with commas, no spaces) to return if you don't want the entire table. Here is a revised example that returns only data in the Planet Name (pl_name), Planet Mass (pl_masse), Right Ascension (ra) and Declination (dec) columns:.

https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query=select+pl_name,pl_masse,ra,dec+from+ps

Note that for encoding purposes, all spaces in the SQL should be replaced with a plus (+) symbol.


(Optional) Step 3: Add more constraints using the where clause.

The where clause, though optional, allows you to include more complex constraints in your query by using AND, OR, and parentheses. Here are some examples:

Enter this...To specify this...
dec > 0. Planets that have a declination greater than 0 degrees
pl_masse between 0.5 and 2.0 Planets that have a mass between 0.5 and 2.0 Earth masses
discoverymethod = 'Radial Velocity' Planets that were discovered by the radial velocity method

lower(soltype) like 'conf%'

upper(soltype) like 'CONF%'

All planets listed as confirmed in lower-case in the soltype column of the database table

All planets listed as confirmed in upper-case in the soltype column of the database table

default_flag=1 Show only the default solution
pl_rade < = 2 Planets with Earth radii less than or equal to 2 Rearth
pl_orbper > = 1000 Planets with orbital periods greater than or equal to 1000 days

So, our example query that returns the planet names, masses, and coordinates for all confirmed planets of about Earth size would read:

https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query=
select+pl_name,pl_masse,ra,dec+from+ps
+where+upper(soltype)+like+'CONF%'+and+pl_masse+between+0.5+and+2.0


(Optional) Step 4 : Specify the output file format.

Unless otherwise specified, query results are automatically returned as a VOTable. You may use the format parameter to specify CSV, as in &format=csv, or TSV, as in &format=tsv.

So, returning to our example from Step 3, we append the query to specify CSV format:

https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query=select+pl_name,pl_masse,ra,dec+from+ps
+where+soltype+like+'CONFIRMED'+and+pl_masse+between+0.5+and+2.0
&format=csv


Top

Using Spatial Constraints In Queries

TAP also allows you to include ADQL-style spatial constraints using custom functions in the WHERE clause to define a circle, box, or polygon region.

In the following example, the query directs TAP to find and return all records in the Planetary Systems table where the point defined by the ra and dec columns in the database table for a circle, box, and polygon area based on the coordinates of Proxima Centauri (217.42896,-62.67947).

For this shape... Define the area as... Using this query...
Circle Inside a 0.1-degree circle https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query=select+*+from+ps
+where+contains(point('icrs',ra,dec),circle('icrs',217.42896,-62.67947,0.1))=1
Box Same center as a circle, and a size of 0.1 x 0.1 degrees https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query=select+pl_name,ra,dec+from+ps+where+contains(point('icrs',ra,dec),box('icrs',217.42896,-62.67947,0.1,0.1))=1
Polygon Give the coordinates of four corners, rather than the center https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query=select+pl_name,ra,dec+from+ps+where+contains(point('icrs',ra,dec),polygon('icrs',217.,-62.,218.,-62.,218.,-63.,217.,-63))=1

The spatial constraints act like any other other constraint (e.g., dec > 0.) in that they can appear mixed in with the regular relational constraints and inside AND/OR and parenthesis constructs. For more information about ADQL geometric constraints, see the IVOA document.

Top

Retrieving Table Schema

The TAP service provides information about the tables being served, including table names, column names, and data types. To compose a query, you need to know the table names, column names, and types. The TAP_SCHEMA can be queried like the other tables in order to return information about the data tables.

Some examples:

Use This Query... To Return This...

https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query= select+schema_name+from+TAP_SCHEMA.schemas

Lists of available schemas (sets of tables) through TAP
https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query= select+schema_name,table_name+from+TAP_SCHEMA.tables A list of available tables
https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query= select+*+from+TAP_SCHEMA.columns+%20where+table_name+like+ %27ps%27 Returns basic information on the Planetary Systems table, such as column names, column type, width, format, etc.

Note: The PS Table Data Column Definitions Document also provides this information.

More information about the TAP_SCHEMA specification is in this IVOA document.

Top

Best Practices

  • Always specify which columns to return, and from which table, i.e., select+*+from+ps

  • Encode special characters in web queries, including plus signs (+) as %2B, %27 for a single quotation mark ( ' ), and %20 for a space. Note that most web browsers will automatically encode queries entered into their search field and some utility programs, such as wget (but not curl), will do so as well.

  • Queries must be on a single line. Line breaks in the examples given in this document are for display purposes only.

  • Be aware of case-sensitivity: ADQL and Oracle table and column names are not case-sensitive. However, values like CONFIRMED or CANDIDATE are case-sensitive, as well as object names (e.g., "eps CrB b"). Query the TAP schema to verify capitalization, or use the upper() and lower() functions and wildcards (%) if you're unsure about capitalization.

  • Use a TAP client when returning large data sets. If you're downloading the entire Planetary Systems table in VOTable format, your web browser may struggle to display all of the results. Try using a client like TOPCAT, pyVO, or TAP+.

More Examples

All confirmed planets found by TESS:

https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query=select+*+from+ps+where+disc_facility+=+'Transiting Exoplanet Survey Satellite (TESS)'

Confirmed planets, listed alphabetically, that have been detected by transit method:

https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query=select+distinct(pl_name)+from+ps+where+tran_flag+=+1+order+by+pl_name+desc+&format=csv

All Earth-sized planets (R<=1.8 Rearth) that have a mass measurement (pl_masse > 0.):

https://exoplanetarchive.ipac.caltech.edu/TAP/sync?query=select+hostname,pl_name,pl_rade,pl_masse+from+ps+where+pl_rade+<+=+1.8+and+pl_masse+>+0

Last updated: 13 May 2020