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.

The summary is below:

Index of general functions:

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
FROM akari_fis_bsc_1_digest
WHERE fABMag2Jy(5.5) <= flux_90 AND flux_90 <= fABMag2Jy(5.0)
 
Input and output parameters.
name type I/O pnum
@magFLOAT8input1
@-FLOAT8output1

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
@latFLOAT8input1
@-TEXToutput1

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
@lonFLOAT8input1
@-TEXToutput1

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
@lon1FLOAT8input1
@lat1FLOAT8input2
@lon2FLOAT8input3
@lat2FLOAT8input4
@-FLOAT8output1

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
@ra1FLOAT8input1
@dec1FLOAT8input2
@ra2FLOAT8input3
@dec2FLOAT8input4
@-FLOAT8output1

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
@x1FLOAT8input1
@y1FLOAT8input2
@z1FLOAT8input3
@x2FLOAT8input4
@y2FLOAT8input5
@z2FLOAT8input6
@-FLOAT8output1

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
@raFLOAT8input1
@decFLOAT8input2
@xFLOAT8output1
@yFLOAT8output2
@zFLOAT8output3
 

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
@latFLOAT8input1
@-FLOAT8output1

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
@lonFLOAT8input1
@-FLOAT8output1

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
FROM akari_fis_bsc_1_digest
WHERE 19.0 <= flux_90 AND flux_90 <= 20.0
 
Input and output parameters.
name type I/O pnum
@fluxFLOAT8input1
@-FLOAT8output1

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
@latstrTEXTinput1
@-FLOAT8output1

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
@lonstrTEXTinput1
@-FLOAT8output1

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
@vFLOAT8input1
@-FLOAT8output1

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
@vFLOAT8input1
@-FLOAT8output1

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
@vFLOAT8input1
@-FLOAT8output1

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
@vFLOAT8input1
@-FLOAT8output1

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:
Sample call:
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
@catalogueTEXTinput1
@sysTEXTinput2
@lonFLOAT8input3
@latFLOAT8input4
@rFLOAT8input5
@objIDINT4output1
@cxFLOAT8output2
@cyFLOAT8output3
@czFLOAT8output4
@distanceFLOAT8output5

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:
Sample call:
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
@catalogueTEXTinput1
@raFLOAT8input2
@decFLOAT8input3
@rFLOAT8input4
@objIDINT4output1
@cxFLOAT8output2
@cyFLOAT8output3
@czFLOAT8output4
@distanceFLOAT8output5

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:
Sample call:
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
@catalogueTEXTinput1
@sysTEXTinput2
@lonFLOAT8input3
@latFLOAT8input4
@rFLOAT8input5
@objIDINT4output1
@cxFLOAT8output2
@cyFLOAT8output3
@czFLOAT8output4
@distanceFLOAT8output5
@objCountINT4output6

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:
Sample call:
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
@catalogueTEXTinput1
@raFLOAT8input2
@decFLOAT8input3
@rFLOAT8input4
@objIDINT4output1
@cxFLOAT8output2
@cyFLOAT8output3
@czFLOAT8output4
@distanceFLOAT8output5
@objCountINT4output6

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:
Sample call:
SELECT fGetNearestObjIDEq('akari_fis_bsc_1', 266.0, -28.0, 10);
 
Input and output parameters.
name type I/O pnum
@catalogueTEXTinput1
@raFLOAT8input2
@decFLOAT8input3
@rFLOAT8input4
@-INT4output1

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:
Sample call:
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
@catalogueTEXTinput1
@sysTEXTinput2
@lon1FLOAT8input3
@lon2FLOAT8input4
@lat1FLOAT8input5
@lat2FLOAT8input6
objIDINT4output1
lonFLOAT8output2
latFLOAT8output3

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:
Sample call:
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
@catalogueTEXTinput1
@ra1FLOAT8input2
@ra2FLOAT8input3
@dec1FLOAT8input4
@dec2FLOAT8input5
objIDINT4output1
lonFLOAT8output2
latFLOAT8output3

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:
Sample call:
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
@catalogueTEXTinput1
@sql_statementTEXTinput2
@radFLOAT8input3
@do_searchBOOLEANinput4
id_xINT4output1
name_xVARCHARoutput2
ra_xFLOAT8output3
dec_xFLOAT8output4
seqNoINT4output5
objCountINT4output6
distanceFLOAT8output7
objIDINT4output8

Last Modified: 19 November 2018