The AKARI Catalogue Archive Server (AKARI-CAS) provides interface to access AKARI FIS and IRC all-sky survey catalog data. The data are stored in the RDBMS (Relational Data Base Management System) dedicated for AKARI-CAS. In CAS tools, you can use SQL (Structured Query Language) statements to request necessary information from the database. This process is called "SQL query", and it enables you to effectively search data with complex criteria. To use SQL query, you must prepare an SQL statement to give the RDBMS your own search criteria or search keys. Once the RDBMS receives the SQL statement, the RDBMS searches the records stored in it, and returns the information which meets your criteria. We prepared this tutorial page so that you can write your own SQL statements.
When you want to access AKARI-CAS using SQL statements, you can use the following Search Tools;
When you get into SQL Search, you can write your own SQL statements in the textarea. And, if you want to use Cross Identifications , you may modify each default SQL statement in the textarea.
Simple Query may include the SELECT block, the FROM block, and the WHERE block. The SELECT block is used to select columns of the database tables. An example of Simple Query is shown below;
-- Example of A Simple Query SELECT objID, objName, ra, dec FROM FisObjAll WHERE fQual_90 = 3 AND fQual_140 = 3 LIMIT 10
The SELECT block is used to specify the column names in a table. In the example shown below, objId, objName, ra, and dec are the column names of a table in the AKARI-CAS RDBMS. You can use "*" character as a wild card indicating all the column names in the table.
-- Example of the SELECT Block SELECT objID, objName, ra, dec -- Example of a Wild Card Usage in the SELECT Block SELECT *
The FROM block refers to a table name for the SQL search. You must write the table name after "FROM". A usage of the FROM block is shown in the example below. In that example, FisObjAll is the specified table name for the SQL query;
-- Example of the FROM Block FROM FisObjAll
You may write your own search conditions in the WHERE block. For example, you can set some parameter constraints after "WHERE", as your own search conditions. The equality (e.g. fQual_90 = 3) and the inequality (e.g. 270 < ra) can be written in the WHERE block. To specify multiple search conditions, each search condition should be connected with "AND" or "OR".
-- Example of the WHERE Block WHERE fQual_90 = 3 AND fQual_140 = 3
-- Example of LIMIT : fetch first 10 rows LIMIT 10
You can define any temporary names for tables, views, or function references. That temporary name is called alias. The alias must be created in the FROM block. You must write "FROM reference_(table, view, or function name) AS alias" or "FROM reference_(table, view, or function name) alias" to create an alias. Once an alias is created, each alias can be connected with column name by ".", and used in the SQL statement.
The following example, the alias o means FisObjAll.
-- Example of Alias : Table Alias SELECT o.objID, o.objName, o.ra, o.dec FROM FisObjAll o WHERE o.fQual_90 = 3 AND o.fQual_140 = 3 LIMIT 10
An alias is also assigned to a selected column reference. That alias must be defined in the SELECT block. You can write like "SELECT a AS value, b + c AS sum FROM ..." to create a column alias. In that SELECT block, value is the alias of the selected column reference a, and sum is the alias of the selected column reference b + c. In the following example, The lon and lat are actual names of the columns which contain the outputs of ra and dec, respectively.
-- Example of Alias : Column Alias SELECT ra as lon, dec as lat FROM FisObj LIMIT 10
The AKARI-CAS provides several tables and Views for users. Tables include FisObjAll and IrcObjAll. The full version of FIS/IRC source catalog records are stored in them. You can obtain FIS or IRC data from them, using SQL statements.
Views are virtual tables with column data selected from the original Tables. The AKARI-CAS has two Views, called FisObj and IrcObj. FisObj is created from the original table FisObjAll. IrcObj is created from the original table IrcObjAll. FisObj has column data selected as the representative columns from FisObjAll. IrcObj also has column data selected as the representative columns from IrcObjAll. So, you can understand the organization from AKARI FIS or AKARI IRC catalog records easily, when you see FisObj or IrcObj. You can use CAS tools through the procedures described as follows in order to display the results from View instead of Table:
You can find three types of SQL calles, on SQL Search. They are Basic Calls, Radial Search, and Rectangular Search. If you click the All button in Basic Calls, the Table FisObjAll (or IrcObjAll) is set to FROM block:
SELECT * FROM FisObjAll WHERE fQual_65 = 3 AND fQual_90 = 3 AND fQual_140 = 3 AND fQual_160 = 3 LIMIT 10
If you click the Digest button in Basic Calls, the View FisObj (or IrcObj) is set to FROM block:
SELECT * FROM FisObj WHERE fQual_65 = 3 AND fQual_90 = 3 AND fQual_140 = 3 AND fQual_160 = 3 LIMIT 10
More samples of the SQL statement are presented below. They give you some instructions to write an SQL statement.
This first sample shows how to use the equality and inequality signs in an SQL statement. This SQL statement is a query to return the first 10 rows of FIS catalog using a flux cut. This sample also includes some equality and inequality signs such as ">" and "<=".
SELECT * FROM FisObj WHERE 19.5 < flux_90 AND flux_90 <= 20.0 LIMIT 10
In the second sample, you can find how to use the logical operators in an SQL statement. This SQL statement is a query to return the first 10 rows of FIS catalog using 'AND' and 'OR' logical operators in the WHERE block.
SELECT objID,objName,ra,dec, flux_65,flux_90,flux_140,flux_160, fQual_65,fQual_90,fQual_140,fQual_160 FROM FisObjALL WHERE ( 19.5 < flux_90 AND flux_90 <= 20.0 ) AND ( fQual_90 = 3 OR fQual_140 = 3 ) LIMIT 10
To obtain simple statistical result of a table, Aggregate Functions are prepared. This example gets minimum, maximum, average values of flux_90 column and number of rows in FisObjALL table:
SELECT min(flux_90), max(flux_90), avg(flux_90), count(*) FROM FisObjALL
You can use
in your SQL statements.
Next example uses "
%" and "
&" mathematical operators.
Each operator gives remainder or binary AND.
In addition, "
objID % 2" can be replaced with "
The mod() function is one of the built-in functions of SQL.
SQL has a number of functions similar to C language.
pages, for further reference.
SELECT * FROM FisObjALL WHERE objID % 2 = 0 AND (flags_90 & INT4(b'00000001')) != 0 LIMIT 10
The final sample presents you an instructive SQL statement how to use AKARI-CAS built-in functions. In the sample below, fLonStr2Deg() is one of general functions in the AKARI-CAS, and fGetNearbyObjCel() is one of AKARI-specific functions. This SQL statement is for a query for Radial Search of FIS catalog using equatorial J2000 coordinate, with the conditions (ra, dec) = ('12:02:00.00', '+02:30:00.0') and radius = 300 arcmins.
SELECT o.* FROM fGetNearbyObjCel('Fis', 'j2000', fLonStr2Deg('12:02:00.00'), fLatStr2Deg('+02:30:00.0'), 300.0 ) n, FisObj o WHERE n.objID = o.objID ORDER BY n.distance LIMIT 10
The ORDER BY block can be written in an SQL statement, when you need a table data with a sorted column. You must write a column name in a table after the "ORDER BY" in order to obtain the sorted table. In the following example, the requested records will be sorted by flux_90 values.
-- Example of ORDER BY SELECT objID,objName,ra,dec, flux_65,flux_90,flux_140,flux_160 FROM FisObjALL WHERE 19.5 < flux_90 AND flux_90 <= 20.0 ORDER BY flux_90
If you need random selection of objects, use ORDER BY random() with LIMIT keyword. Following example randomly selects 20 objects with a flux cut.
-- Example of random selection SELECT objID,objName,ra,dec, flux_65,flux_90,flux_140,flux_160, fQual_65,fQual_90,fQual_140,fQual_160 FROM FisObjALL WHERE 18.0 < flux_90 AND flux_90 <= 20.0 ORDER BY random() LIMIT 20
You can use a variety of AKARI-CAS built-in Functions in your SQL statement. Two categories of the functions, the general functions and the AKARI-specific functions, can be used in the SQL statement. When you need converted values (such as converted angles, or converted units), you can use general functions. An example SQL statement using a general function is shown below;
-- Example of the General Function -- function fDeg2LonStr() and fDeg2LatStr() convert -- a numerical value in degree into string of `xx:xx:xx.xx' style. SELECT ra, dec, fDeg2LonStr(ra) as ra_hms, fDeg2LatStr(dec) as dec_dms FROM FisObj LIMIT 10
When you need a query under some constraints, you should use AKARI-specific functions in your SQL statement. An example SQL statement is shown below;
-- Example of the AKARI-specific functions -- Given a point (@ra,@dec) of Ecliptic coordinate, -- this function fGetNearbyObjCel returns a table holding a record of -- FIS or IRC object nearest from the point within -- @r arcmins of the point. SELECT o.*, n.distance FROM fGetNearbyObjCel('Fis', 'ecl', 195.5, 2.5, 300) n, FisObj o WHERE n.objID = o.objID
Functions described above are all listed on Functions page, where you can obtain detailed information about the general functions and the AKARI-specific functions.