DARTS/Astro Query System User Guide

[ English | Japanese ]


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

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, input ADQL statements are converted into the SQL statements which can be interpreted with PostgreSQL and Pgspere, and sent to the database.

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 powerd 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, database items to search are specified (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. Here, AND has a higher priority than OR; so 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 end of a SQL statement. This is a required item, but in DARTS/Astro Query System, this can be omitted. You may not specify two or more SQL statements separated by " ; " at the same time in DARTS/Astro Query System. Please input a single SQL statement at a time.

In addition, please find SQL tutorial and SQL general reference in DARTS/AKARI CAS page.

To the Top of the Page ⏏


Examples and explanations of SQL / ADQL

Regarding of typical search conditions and often use search conditions of Astronomy, we prepare example sentences witten by SQL and ADQL. To push for each "Call example", it displays example sentences of input screen of "SQL Query" or "ADQL Query" and these explanation displays below input screen.Based on this, you can edit tables or search conditions that you want to search.

We easily list commentary here.

Search category Realization methid and SQL example
Ranges Search You can use the following operators for searching by the comparison of value.
  • = ... equal to
  • != ... not equal to
  • > ... greater than
  • < ... less than
  • >= ... greater than or equal to
  • <= ... less than or equal to
And, Or When you want to search in combination various cinditions, you connect conditions in AND or OR.
  • a AND b : condition a and b
  • a OR b : condition a or b
Because of AND has a higher priority to OR, if you want to give a higher priority to OR, use parentheses, ().
Mutiple match When you search data corresponding or not with either in various values, it is convenient using IN clause. The sentence structure is as follows.
  • column_x IN (a, b) : The data item column_x is value a or b.
  • column_x NOT IN (a, b) : The data item column_x is not value a and b.
As for the above, it is same meaning as follows of using = and OR, or != and AND.
  • column_x = a OR column_x = b : The data item column_x is value a or b.
  • column_x != a AND column_x != b : The data item column_x is not value a and b
Pattern match To implement pattern matching, you use LIKE operaters.
  • LIKE column_x = 'a%' : Character string that starting a of data item column_x.
  • LIKE column_x = 'a_' : Character string that corresponding pattern of "a + any one character" of data item column_x.
You can use below character as wild card.
  • % ... Any latter of any number
  • _ ... A any latter
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 and continue to any two character", e.g.'M31'

When you want to treat '%' or '_' as a normal letter, not as a wild card, please add backslash "\" as an escape character before a sign.
e.g. '10\%', 'STAR\_A'

Radial Search

To implement radial search, it is convenient to use function of PgSphere. Data types have the followinng things.

  • Point ... spoint(coordinateL, coordinateB)
  • Line ... spath(spoint(), spoint(), ...)
  • Circle ... scircle(spoint(), radius)
  • Rectangle ... spoly(spoint(), spoint(), ...)
Operators have the following things.
  • a @ b = '1' ... a is included in b
  • a !@ b = '1' ... a isn't included in b
  • a && b = '1' ... a overlaps with b each other
  • a !&& b = '1' ... a doesn't overlaps with b each other
If you specify value of each coordinate, please use coordinate value of each coordinate which stored in our database as an object of search condition. For example, if you want to search around center of the galaxy, it is easier to use Galactic coordinate attached "_galactic_lon" or "_galactic_lat" on the end of column name.

There is same function in ADQL Search. Data types have the following things.

  • Point ... POINT('',coordinateL, coordinateB)
  • Circle ... CIRCLE(POINT(), radius)
  • Rectangle ... POLYGON(POINT(), POINT(), ...)
Operators have the following things.
  • CONTAINS(a, b) = 1 ... a is included in b
  • CONTAINS(a, b) = 0 ... a isn't included in b
  • INTERSECTS(a,b) = 1 ... a overlaps with b each other
  • INTERSECTS(a, b) = 0 ... a doesn't overlaps with b each other

Multipul Radial Search If you want to search data about multiple objects at a time, you connect each circle search condition using OR, and you can search data included in either of multiple circular regions. On the contrary, you want to narrow it down to data included in all of multiple circle regions, you connect each circular search condition using AND.
Rectanglar Search

If you search data included in rectangle region which specified a point with center of observation point, it is easy to specify range of values about each of latitude and longtitude.
In the case of specifying range of values, you can use relational operator :
>, <, >=, <=, etc.

  • column_x >= a AND column_x <= b :Data item column_x is more than the value of a and less than the value of b.
  • column_x < a OR column_x > b :Data item columm_x is less than the value of b or more than the value of b.
Also, above expressions are written by using BETWEEN syntax.
  • column_x BTWEEN a AND b : Data item column_x is included in between a and b.
  • column_x NOT BTWEEN a AND b Data item column_x isn't included in a and b.
However, when you use BETWEEN syntax, please careful whether is included in threshold value.

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

We can output the form of HTML or CSV that search results in SQL Search or ADQL Search. Please specify whether html or csv using "Format".

To the Top of the Page ⏏


Showing the used SQL query

If you click a link "Show the used SQL", the SQL sentence which you implement inside the system is displayed in popup. This SQL sentence copies on input colum "SQL query" of SQL Search, you can search to change condition and output.

To the Top of the Page ⏏


Downloading the data

If you click a link "Download Wgetscript", you can download Wget script. To implement this script on your computer, you can all the data which made a hit for your search.

To the Top of the Page ⏏

Last Modified: 28 August 2018