[ English | Japanese ]

DARTS/Astro Query System User Guide

How to use "SQL Search" and "ADQL Search"

In SQL Search and ADQL Search, you can search database directly by entering SQL queries to specify search conditions. You can create your own search conditions and/or sort the results in any orders.

Basic syntax of SQL / ADQL
Examples and explanations of SQL / ADQL
Formats of the search results
Showing the used SQL query
Downloading the data

Basic syntax of SQL / ADQL

In Darts/Astro Query System, we adopt PostgreSQL for the RDBMS, as well as PgSphere as an extension. You can use any functions defined in these systems.

Standard of ADQL, which is an extension of the standard SQL92, is defined in IVOA Recommendation. In ADQL search, the input ADQL statements are converted into the SQL statements which are interpreted with PostgreSQL and PgSphere.

Here is the basic syntax of SQL and ADQL, where "{value1|value2}" means either of "value1" or "value2", and "[phrase]" means that the "phrase" may be omitted:

Syntax of SQL powered by PostgreSQL:


  SELECT  {*|column1,column2 column3,...}
  FROM table_name
  [ WHERE conditions ]
  [ ORDER BY column1, column4, ... ]
  [ LIMIT {number|ALL} [ OFFSET number ]
  ;

Syntax of ADQL：


  SELECT
  TOP number
  {*|column1,column2,column3,...}
  FROM table_name
  [ WHERE conditions ]
  [ ORDER BY column1, column4, ... ]
  ;

SELECT clause

From the tables specified with FROM clause, the database items used to search are specified with SELECT (comma-separated). This is a required item. To specify all the items in the tables, use "*". Please refer to the following for database items in all the tables.

FROM clause

Specify tables to search within. This is a required item.

WHERE clause

Specify search conditions. You can combine two or more search conditions using AND or OR, where AND has a higher priority than OR. If you want to give a higher priority to OR, use parentheses, ( ).

ORDER BY clause

Specify database items based on which the search results are ordered. The output is displayed in the ascending order if ASC is put after the database item, or descending order if DESC is put. Default is the ascending order when ASC is omitted. If two or more database items separated by "," are specified, the second (third, fourth, and so on) database item is used to judge the order when the first (second, third, and so on) item has the same order.

LIMIT / TOP clause

LIMIT / TOP specifies the maximum number of search results, as well as the number to skip. While this is an optional item, we would recommend to specify a reasonable upper-limit, because response of the browser can be very slow when a large amount of the data are accidentally hit.

These specifications are not clearly established in the Standard SQL, and so that the implementation varies in different database systems. In PostgreSQL, LIMIT is used, while TOP is adopted in ADQL. Accordingly, we use LIMIT in SQL Search and TOP in ADQL Search.

The clause in SQL Search is as follows;

LIMIT {number|ALL} [ OFFSET {number} ]

OFFSET specifies the number of lines to skip, where "OFFSET 0" is identical to omitting OFFSET. When OFFSET is omitted, the number specified with LIMIT is returned from the beginning. If both OFFSET and LIMIT are specified, the first number of lines specified with OFFSET are skipped, and output the results up to the number specified by LIMIT.

The clause in ADQL Search is as follows;

TOP number

This returns the results from the begging to the number specified.

Ending character " ; "

Ending character " ; " tells the end of an SQL statement. In DARTS/Astro Query System, the ending character may be omitted, but you may not specify two or more SQL statements separated by " ; ". Please input a single SQL statement at a time.

Also, please refer to SQL tutorial and SQL general reference in DARTS/AKARI CAS page.

To the Top of the Page ⏏

Examples and explanations of SQL / ADQL

In SQL Search and ADQL Search, we provide examples of typical query statements in SQL and ADQL. Clicking each of the "Call example", an exemplary SQL statement is put into the "SQL Query" or "ADQL Query" text box, and explanation of the statement is shown. You can edit the statement in the text box.

Search types	Explanation and example
Ranges Search	You can use the following operators to compare values: `=` ... equal to `!=` ... not equal to `>` ... greater than `<` ... less than `>=` ... greater than or equal to `<=` ... less than or equal to
And, Or	When you combine various conditions, you can use `AND` or `OR`: `aaa AND bbb` : condition aaa and bbb `aaa OR bbb` : condition aaa or bbb Because `AND` has a higher priority than `OR`, if you want to give a higher priority to `OR`, use parentheses, `()`.
Multiple match	When searching the data matching one of various values or not-matching any of the values, `IN` clause is convenient: `column_x IN (aaa, bbb)` : column_x value is either aaa or bbb. `column_x NOT IN (aaa, bbb)` : column_x value is neither aaa nor bbb. Above statements are equivalent to the following: `column_x = aaa OR column_x = bbb` : column_x value is aaa or column_x value is bbb. `column_x != aaa AND column_x != bbb` : column_x value is not aaa and column_x value is not bbb
Pattern match	For pattern matching, you may use `LIKE` operator: `LIKE column_x = 'a%'` : character string starting with the character "a". `LIKE column_x = 'a_'` : character string "a + any single character". You can use wild cards: `%` ... Any letters of any number `_` ... Any single letter For example, if you input "10%", you can search data which "begin with 10". Also, if you input "M__", you can search data which "begin with M followed by any two characters", e.g. 'M31'. If you want to treat '%' or '_' as a normal letter, please put "\" before the character, e.g. '10\%', 'STAR\_A'.
Radial Search	In radial search, it is convenient to use functions of PgSphere to define geometrical shape or region on the celestial sphere: Point ... `spoint(coordinateL, coordinateB)` Line ... `spath(spoint(), spoint(), ...)` Circle ... `scircle(spoint(), radius)` Rectangle ... `spoly(spoint(), spoint(), ...)` Following operators are available to compare geometrical shapes or regions: `aaa @ bbb = '1'` ... aaa is included in bbb `aaa !@ bbb = '1'` ... aaa is not included in bbb `aaa && bbb = '1'` ... aaa and bbb overlap each other `aaa !&& bbb = '1'` ... aaa and bbb do not overlap You may use any coordinate systems adopted in our database. For example, if you want to search around the Galactic center, it is convenient to use Galactic coordinates ("_galactic_lon" or "_galactic_lat" at the ends of column names). There are similar geometrical functions in ADQL Search: Point ... `POINT('',coordinateL, coordinateB)` Circle ... `CIRCLE(POINT(), radius)` Rectangle ... `POLYGON(POINT(), POINT(), ...)` Following operators are available: `CONTAINS(aaa, bbb) = 1` ... aaa is included in bbb `CONTAINS(aaa, bbb) = 0` ... aaa is not included in bbb `INTERSECTS(aaa,bbb) = 1` ... aaa and bbb overlap each other `INTERSECTS(aaa, bbb) = 0` ... aaa and bbb do not overlap
Multiple Radial Search	You can combine multiple radial search conditions using `OR` or `AND` operators.
Rectangular Search	You may carry out rectangular search using operators, `>`, `<`, `>=`, `<=`, etc.: `column_x >= aaa AND column_x <= bbb` ：column_x value is larger than aaa and less than bbb. `column_x < aaa OR column_x > bbb` ： column_x value is smaller than aaa or larger than bbb. Equivalent expressions are written by using `BETWEEN` syntax: `column_x BETWEEN a AND b` : Data item column_x is included in between a and b. `column_x NOT BETWEEN a AND b` Data item column_x isn't included in a and b. When you use `BETWEEN` syntax, please be careful if the boundaries are inclusive or not. To search the piled up region specifying rectangle region of which region such as observation fields, it is convenient to use function of PgSphere, like Radial Search. Please refer to Radial Search in above this page.

To the Top of the Page ⏏

Formats of the search results

You can choose either html or CSV for the output format of the search results. Please specify from the "Format" pull-down menu.

To the Top of the Page ⏏

Showing the SQL statement used

If you click "Show the used SQL", the SQL statement used in the search is displayed as a pop-up. This SQL sentence may be copied into the "SQL query" text box of SQL Search.

To the Top of the Page ⏏

Downloading the data

If you click "Download Wget script", you can obtain a Wget script to download the data your selected.

To the Top of the Page ⏏