SQL Schema: Functions
Important announcements
The database for AKARI CAS was restructured into multiple databases.
The 'cas' database is available as a new database instead of 'DR1'.
Due to the reconfiguration of the database, the name of some tables and views had changed.
If you keep the previously created SQL and want to use again, please do substitution of the name.
And also, the usage of some functions had changed.
The correspondence between the new name and the old name is written in
Tables and
Views.
In Functions, please specify the catalogue names ('akari_fis_bsc_1' or 'akari_irc_psc_1')
instead of the instrument ('Fis' or 'Irc').
We appreciate in your inconvenience.
fABMag2Jy | This function converts AB magnitude into flux density in Jansky. |
fDeg2LatStr | This function converts numerical latitude in degree into string of `±dd:mm:ss.s' style. |
fDeg2LonStr | This function converts numerical longitude in degree into string of `hh:mm:ss.ss' style. |
fDistanceArcMinCel | Returns distance (arc minutes) between two points (lon1,lat1) and (lon2,lat2) of J2000, B1950, Ecliptic or Galactic coordinate. |
fDistanceArcMinEq | Returns distance (arc minutes) between two points (ra1,dec1) and (ra2,dec2) of J2000 coordinate. |
fDistanceArcMinXYZ | Returns distance (arc minutes) between two points (x1,y1,z1) and (x2,y2,z2). |
fEq2XYZ | This function converts J2000 R.A. and Dec. into unit vector (x,y,z). |
fFixLat | This function fixes the range of latitude given as an argument so that returned latitude has the range between -90 and 90. |
fFixLon | This function fixes the range of longitude given as an argument so that returned longitude has the range between 0 and 360. |
fJy2ABMag | This function converts flux density in Jansky into AB magnitude. |
fLatStr2Deg | This function converts latitude string of `[±]dd:mm:ss.s' or `[±]dd mm ss.s' style into a numeric value in degree. A simple numeric string in degree is also acceptable. |
fLonStr2Deg | This function converts longitude string of `hh:mm:ss.ss' or `hh mm ss.ss' style into a numeric value in degree. A simple numeric string in degree is also acceptable. |
fArcMin2Rad | This function converts a numerical value in arcmin into a numerical value in radian. |
fMin2Sec | This function cpnverts a numerical value in arcmin into a numerical value in arcsec. |
fRad2ArcMin | This function converts a numerical value in radian into a numerical value in arcmin. |
fSec2Min | This function cpnverts a numerical value in arcsec into a numerical value in arcmin. |
Index of AKARI-specific functions:
fGetNearbyObjCel | Given a point (@lon,@lat), returns table of AKARI FIS, AKARI IRC, RC3, Iras, IrasFsc or Twomass objects within @r arcmins of the point. Coordinate system can be selected from J2000, B1950, Ecliptic or Galactic. |
fGetNearbyObjEq | Given a point (@ra,@dec) of J2000 coordinate, returns table of AKARI FIS, AKARI IRC, RC3, Iras, IrasFsc or Twomass objects within @r arcmins of the point. |
fGetNearestObjCel | Given a point (@lon,@lat), returns table holding a record of AKARI FIS, AKARI IRC, RC3, Iras, IrasFsc or Twomass object nearest from the point within @r arcmins of the point. Coordinate system can be selected from J2000, B1950, Ecliptic or Galactic. |
fGetNearestObjEq | Given a point (@ra,@dec) of J2000 coordinate, returns table holding a record of AKARI FIS, AKARI IRC, RC3, Iras, IrasFsc or Twomass object nearest from the point within @r arcmins of the point. |
fGetNearestObjIDEq | Given a point (@ra,@dec) of J2000 coordinate, returns an ObjID (INT4) of AKARI FIS, AKARI IRC, RC3, Iras, IrasFsc or Twomass object nearest from the point within @r arcmins of the point. |
fGetObjFromRectCel | Returns table of FIS or IRC objects inside a rectangle defined by two ra,dec pairs of J2000, B1950, ecliptic or galactic coordinate. Note the order of parameters: @lon1, @lon2, @lat1, @lat2 |
fGetObjFromRectEq | Returns table of AKARI FIS, AKARI IRC, RC3, Iras, IrasFsc or Twomass objects inside a rectangle defined by two ra,dec pairs of J2000 coordinate. Note the order of parameters: @ra1, @ra2, @dec1, @dec2 |
fGetCrossIdResultEq | Special function for the Cross-ID tool. This function cannot be used directly in your SQL statement. |
FUNCTION fABMag2Jy
This function converts AB magnitude into flux density in Jansky. |
Sample call:
SELECT ra, dec, flux_90, fJy2ABMag(flux_90) as mag_90
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@mag | FLOAT8 | input | 1 |
@- | FLOAT8 | output | 1 |
FUNCTION fDeg2LatStr
This function converts numerical latitude in degree into string of `±dd:mm:ss.s' style. |
Sample call:
SELECT ra, dec, fDeg2LonStr(ra) as ra_hms, fDeg2LatStr(dec) as dec_dms
FROM akari_fis_bsc_1_digest
LIMIT 10;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@lat | FLOAT8 | input | 1 |
@- | TEXT | output | 1 |
FUNCTION fDeg2LonStr
This function converts numerical longitude in degree into string of `hh:mm:ss.ss' style. |
Sample call:
SELECT ra, dec, fDeg2LonStr(ra) as ra_hms, fDeg2LatStr(dec) as dec_hms
FROM akari_fis_bsc_1_digest
LIMIT 10;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@lon | FLOAT8 | input | 1 |
@- | TEXT | output | 1 |
FUNCTION fDistanceArcMinCel
Returns distance (arc minutes) between two points (lon1,lat1) and (lon2,lat2) of celestial coordinate. |
Sample call:
SELECT objID, fDistanceArcMinCel(186,1, lambda,beta) as distance FROM akari_fis_bsc_1 LIMIT 10;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@lon1 | FLOAT8 | input | 1 |
@lat1 | FLOAT8 | input | 2 |
@lon2 | FLOAT8 | input | 3 |
@lat2 | FLOAT8 | input | 4 |
@- | FLOAT8 | output | 1 |
FUNCTION fDistanceArcMinEq
Returns distance (arc minutes) between two points (ra1,dec1) and (ra2,dec2) of celestial coordinate. This function is an alias of fDistanceArcMinCel(). |
Sample call:
SELECT objID, fDistanceArcMinEq(186,1,ra,dec) as distance FROM akari_fis_bsc_1 LIMIT 10;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@ra1 | FLOAT8 | input | 1 |
@dec1 | FLOAT8 | input | 2 |
@ra2 | FLOAT8 | input | 3 |
@dec2 | FLOAT8 | input | 4 |
@- | FLOAT8 | output | 1 |
FUNCTION fDistanceArcMinXYZ
Returns distance (arc minutes) between two points (x1, y1, z2) and (x2, y2, z2). |
Sample call:
SELECT objID, fDistanceArcMinXYZ(1,0,0, cx,cy,cz) as distance FROM akari_fis_bsc_1 LIMIT 10;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@x1 | FLOAT8 | input | 1 |
@y1 | FLOAT8 | input | 2 |
@z1 | FLOAT8 | input | 3 |
@x2 | FLOAT8 | input | 4 |
@y2 | FLOAT8 | input | 5 |
@z2 | FLOAT8 | input | 6 |
@- | FLOAT8 | output | 1 |
FUNCTION fEq2Xyz
This function converts J2000 R.A. and Dec. into unit vector (x,y,z). |
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@ra | FLOAT8 | input | 1 |
@dec | FLOAT8 | input | 2 |
@x | FLOAT8 | output | 1 |
@y | FLOAT8 | output | 2 |
@z | FLOAT8 | output | 3 |
FUNCTION fFixLat
This function fixes the range of latitude given as an argument so that returned latitude has the range between -90 and 90. |
Sample call:
SELECT objID, objName, ra, dec
FROM akari_fis_bsc_1_digest
WHERE fFixLat(102.8) < dec AND dec < fFixLat(92.8)
LIMIT 10;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@lat | FLOAT8 | input | 1 |
@- | FLOAT8 | output | 1 |
FUNCTION fFixLon
This function fixes the range of longitude given as an argument so that returned longitude has the range between 0 and 360. |
Sample call:
SELECT objID, objName, ra, dec
FROM akari_fis_bsc_1_digest
WHERE fFixLon(379.12) < ra AND ra < fFixLon(389.12)
LIMIT 10;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@lon | FLOAT8 | input | 1 |
@- | FLOAT8 | output | 1 |
FUNCTION fJy2ABMag
This function converts flux density in Jansky into AB magnitude. |
Sample call:
SELECT ra, dec, flux_90, fJy2ABMag(flux_90) as mag_90
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@flux | FLOAT8 | input | 1 |
@- | FLOAT8 | output | 1 |
FUNCTION fLatStr2Deg
This function converts latitude string of `[±]dd:mm:ss.s' or `[±]dd mm ss.s' style into a numeric value in degree. A simple numeric string in degree is also acceptable. |
Sample call:
SELECT o.*, n.distance
FROM fGetNearbyObjEq('akari_fis_bsc_1', fLonStr2Deg('12:34:56.999'), fLatStr2Deg('-65:43:21.111'), 50) n, akari_fis_bsc_1_digest o
WHERE n.objID = o.objID;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@latstr | TEXT | input | 1 |
@- | FLOAT8 | output | 1 |
FUNCTION fLonStr2Deg
This function converts longitude string of `hh:mm:ss.ss' or `hh mm ss.ss' style into a numeric value in degree. A simple numeric string in degree is also acceptable. |
Sample call:
SELECT o.*, n.distance
FROM fGetNearbyObjEq('akari_fis_bsc_1', fLonStr2Deg('12:34:56.999'), fLatStr2Deg('-65:43:21.111'), 50) n, akari_fis_bsc_1_digest o
WHERE n.objID = o.objID;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@lonstr | TEXT | input | 1 |
@- | FLOAT8 | output | 1 |
FUNCTION fArcMin2Rad
This function converts a numerical value in arcmin into a numerical value in radian. |
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@v | FLOAT8 | input | 1 |
@- | FLOAT8 | output | 1 |
FUNCTION fMin2Sec
This function cpnverts a numerical value in arcmin into a numerical value in arcsec. |
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@v | FLOAT8 | input | 1 |
@- | FLOAT8 | output | 1 |
FUNCTION fRad2ArcMin
This function converts a numerical value in radian into a numerical value in arcmin. |
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@v | FLOAT8 | input | 1 |
@- | FLOAT8 | output | 1 |
FUNCTION fSec2Min
This function cpnverts a numerical value in arcsec into a numerical value in arcmin. |
Sample call:
SELECT o.*, n.distance FROM fGetNearbyObjEq('akari_fis_bsc_1', 266.0, -28.0, fSec2Min(600)) n, akari_fis_bsc_1_digest o WHERE n.objID = o.objID;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@v | FLOAT8 | input | 1 |
@- | FLOAT8 | output | 1 |
FUNCTION fGetNearbyObjCel
Given a point (@lon,@lat), returns table of AKARI FIS, AKARI IRC, RC3, Iras, IrasFsc or Twomass objects within @r arcmins of the point. Set 'j2000', 'b1950', 'ecl' or 'gal' to 2nd argument @sys to specify coordinate system. |
Returned field:
- objID INT4, -- id of the object.
- cx FLOAT8, -- unit vector for ra+dec.
- cy FLOAT8, -- unit vector for ra+dec.
- cz FLOAT8, -- unit vector for ra+dec.
- distance FLOAT8, -- distance in arcmins to this object from the ra,dec.
SELECT o.*, n.distance FROM fGetNearbyObjCel('akari_fis_bsc_1', 'gal', 195.5, 2.5, 150) n, akari_fis_bsc_1_digest o WHERE n.objID = o.objID;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@catalogue | TEXT | input | 1 |
@sys | TEXT | input | 2 |
@lon | FLOAT8 | input | 3 |
@lat | FLOAT8 | input | 4 |
@r | FLOAT8 | input | 5 |
@objID | INT4 | output | 1 |
@cx | FLOAT8 | output | 2 |
@cy | FLOAT8 | output | 3 |
@cz | FLOAT8 | output | 4 |
@distance | FLOAT8 | output | 5 |
FUNCTION fGetNearbyObjEq
Given a point (@ra,@dec) of J2000 coordinate, returns table of AKARI FIS, AKARI IRC, RC3, Iras, IrasFsc or Twomass objects within @r arcmins of the point. |
Returned field:
- objID INT4, -- id of the object.
- cx FLOAT8, -- unit vector for ra+dec.
- cy FLOAT8, -- unit vector for ra+dec.
- cz FLOAT8, -- unit vector for ra+dec.
- distance FLOAT8, -- distance in arcmins to this object from the ra,dec.
SELECT o.*, n.distance FROM fGetNearbyObjEq('akari_fis_bsc_1', 195.5, 2.5, 300) n, akari_fis_bsc_1_digest o WHERE n.objID = o.objID;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@catalogue | TEXT | input | 1 |
@ra | FLOAT8 | input | 2 |
@dec | FLOAT8 | input | 3 |
@r | FLOAT8 | input | 4 |
@objID | INT4 | output | 1 |
@cx | FLOAT8 | output | 2 |
@cy | FLOAT8 | output | 3 |
@cz | FLOAT8 | output | 4 |
@distance | FLOAT8 | output | 5 |
FUNCTION fGetNearestObjCel
Given a point (@lon,@lat), returns table holding a record of AKARI FIS, AKARI IRC, RC3, Iras, IrasFsc or Twomass object nearest from the point within @r arcmins of the point. Set 'j2000', 'b1950', 'ecl' or 'gal' to 2nd argument @sys to specify coordinate system. |
Returned field:
- objID INT4, -- id of the object.
- cx FLOAT8, -- unit vector for ra+dec.
- cy FLOAT8, -- unit vector for ra+dec.
- cz FLOAT8, -- unit vector for ra+dec.
- distance FLOAT8, -- distance in arcmins to this object from the ra,dec.
- objCount INT4, -- number of objects in FOV of @r arcmins.
SELECT o.*, n.distance FROM fGetNearestObjCel('akari_fis_bsc_1', 'ecl', 195.5, 2.5, 100) n, akari_fis_bsc_1_digest o WHERE n.objID = o.objID;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@catalogue | TEXT | input | 1 |
@sys | TEXT | input | 2 |
@lon | FLOAT8 | input | 3 |
@lat | FLOAT8 | input | 4 |
@r | FLOAT8 | input | 5 |
@objID | INT4 | output | 1 |
@cx | FLOAT8 | output | 2 |
@cy | FLOAT8 | output | 3 |
@cz | FLOAT8 | output | 4 |
@distance | FLOAT8 | output | 5 |
@objCount | INT4 | output | 6 |
FUNCTION fGetNearestObjEq
Given a point (@ra,@dec) of J2000 coordinate, returns table holding a record of AKARI FIS, AKARI IRC, RC3, Iras, IrasFsc or Twomass object nearest from the point within @r arcmins of the point. |
Returned field:
- objID INT4, -- id of the object.
- cx FLOAT8, -- unit vector for ra+dec.
- cy FLOAT8, -- unit vector for ra+dec.
- cz FLOAT8, -- unit vector for ra+dec.
- distance FLOAT8, -- distance in arcmins to this object from the ra,dec.
- objCount INT4, -- number of objects in FOV of @r arcmins.
SELECT o.*, n.distance FROM fGetNearestObjEq('akari_fis_bsc_1', 195.5, 2.5, 300) n, akari_fis_bsc_1_digest o WHERE n.objID = o.objID;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@catalogue | TEXT | input | 1 |
@ra | FLOAT8 | input | 2 |
@dec | FLOAT8 | input | 3 |
@r | FLOAT8 | input | 4 |
@objID | INT4 | output | 1 |
@cx | FLOAT8 | output | 2 |
@cy | FLOAT8 | output | 3 |
@cz | FLOAT8 | output | 4 |
@distance | FLOAT8 | output | 5 |
@objCount | INT4 | output | 6 |
FUNCTION fGetNearestObjIDEq
Given a point (@ra,@dec) of J2000 coordinate, returns an ObjID (INT4) of AKARI FIS, AKARI IRC, RC3, Iras, IrasFsc or Twomass object nearest from the point within @r arcmins of the point. |
Returned field:
- objID INT4, -- id of the object.
SELECT fGetNearestObjIDEq('akari_fis_bsc_1', 266.0, -28.0, 10);
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@catalogue | TEXT | input | 1 |
@ra | FLOAT8 | input | 2 |
@dec | FLOAT8 | input | 3 |
@r | FLOAT8 | input | 4 |
@- | INT4 | output | 1 |
FUNCTION fGetObjFromRectCel
Returns table of FIS or IRC objects inside a rectangle defined by two lon,lat pairs. Set 'j2000', 'b1950', 'ecl' or 'gal' to 2nd argument @sys to specify coordinate system. Note the order of parameters: @lon1, @lon2, @lat1, @lat2 |
Returned field:
- objID INT4, -- id of the object.
- lon FLOAT8, -- longitude in degree.
- lat FLOAT8, -- latitude in degree.
SELECT o.* FROM fGetObjFromRectCel('akari_fis_bsc_1', 'gal', 276.0, 322.0, 61.8, 65.3) n, akari_fis_bsc_1_digest o WHERE n.objID = o.objID LIMIT 10;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@catalogue | TEXT | input | 1 |
@sys | TEXT | input | 2 |
@lon1 | FLOAT8 | input | 3 |
@lon2 | FLOAT8 | input | 4 |
@lat1 | FLOAT8 | input | 5 |
@lat2 | FLOAT8 | input | 6 |
objID | INT4 | output | 1 |
lon | FLOAT8 | output | 2 |
lat | FLOAT8 | output | 3 |
FUNCTION fGetObjFromRectEq
Returns table of AKARI FIS, AKARI IRC, RC3, Iras, IrasFsc or Twomass objects inside a rectangle defined by two ra,dec pairs of J2000 coordinate. Note the order of parameters: @ra1, @ra2, @dec1, @dec2 |
Returned field:
- objID INT4, -- id of the object.
- lon FLOAT8, -- longitude in degree.
- lat FLOAT8, -- latitude in degree.
SELECT o.* FROM fGetObjFromRectEq('akari_fis_bsc_1', 180.5, 200.7, 1.5, 3.6) n, akari_fis_bsc_1_digest o WHERE n.objID = o.objID;
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@catalogue | TEXT | input | 1 |
@ra1 | FLOAT8 | input | 2 |
@ra2 | FLOAT8 | input | 3 |
@dec1 | FLOAT8 | input | 4 |
@dec2 | FLOAT8 | input | 5 |
objID | INT4 | output | 1 |
lon | FLOAT8 | output | 2 |
lat | FLOAT8 | output | 3 |
FUNCTION fGetCrossIdResultEq
This is a special function for Cross-ID tool.
In that tools, the `#result' argument in SQL statement is replaced
with fGetCrossIdResultEq() function which will perform the cross
identification and return a table of result. This function cannot be used directly in your SQL statement. Returned table includes following information. |
Returned field:
- id_x INT4, -- Sequential ID of given target position.
- name_x VARCHAR, -- Name of given target position.
- ra_x FLOAT8, -- R.A.(J2000) of given target position.
- dec_x FLOAT8, -- Dec.(J2000) of given target position.
- seqNo INT4, -- Sequential number of given target having found object(s). SeqNo of last row indicates total number of target positions which have found object(s).
- objCount INT4, -- Count of found object(s) for each given target position.
- distance FLOAT8, -- Distance (arcmins) between (ra_x, dec_x) and found object.
- objID INT4, -- Object ID of found object.
SELECT n.name_x, n.seqNo, n.objCount, n.distance, o.objID, o.objName, o.ra, o.dec, o.flux_65,
o.flux_90, o.flux_140, o.flux_160
FROM fGetCrossIdResultEq('akari_fis_bsc_1','select * from tmptbl',3.0,false) n, akari_fis_bsc_1 o WHERE n.objID = o.objID ORDER BY n.id_x, n.objCount
|
Input and output parameters.
name | type | I/O | pnum |
---|---|---|---|
@catalogue | TEXT | input | 1 |
@sql_statement | TEXT | input | 2 |
@rad | FLOAT8 | input | 3 |
@do_search | BOOLEAN | input | 4 |
id_x | INT4 | output | 1 |
name_x | VARCHAR | output | 2 |
ra_x | FLOAT8 | output | 3 |
dec_x | FLOAT8 | output | 4 |
seqNo | INT4 | output | 5 |
objCount | INT4 | output | 6 |
distance | FLOAT8 | output | 7 |
objID | INT4 | output | 8 |