Return to the home page | PDR1 | PDR2

Procedure List (PDR2)

Table of contents

Legend

procname(param1: type1, param2: type2) -> retval: retype
Types may be omitted.
Example
description.

Examples will appear when you click on the “Example” button.

Statistical functions

In addition to the PostgreSQL standard aggregate functions, the following aggregate functions are available.

skewness(value: double) -> skewness: double
skewness returns κ323/2, where κi denotes the unbiased estimator of the i-th cumulant. Note that the skewness such defined is biased.
kurtosis(value: double) -> kurtosis: double
kurtosis returns κ422, where κi denotes the unbiased estimator of the i-th cumulant. Note that the kurtosis such defined is biased.
Example
-- calculate the skewness of i_cmodel_mag that are valid
SELECT
  skewness(i_cmodel_mag) FILTER (WHERE -100 < i_cmodel_mag AND i_cmodel_mag < 100)
FROM
  pdr2_dud.forced
WHERE
  isprimary;

There are binary functions of the same type as above:

weighted_mean(value: double, weight: double) -> mean: double
weighted_mean returns the weighted mean of the values, namely ∑ivaluei×weighti / ∑iweighti.
avg(value: double, stddev: double) -> mean: double
avg calculates (∑value/stddev2) / (∑1/stddev2).
chi2_per_freedom(value: double, stddev: double) -> chi2_per_freedom: double
chi2_per_freedom calculates χ2 per degree of freedom, which is defined as (N-1)-1 ∑ (value - ⟨value⟩)2 / stddev2.
Example
-- calculate weighted mean of sinc magnitudes weighted by errors
SELECT
  avg(i_cmodel_mag, i_cmodel_magsigma) FILTER (WHERE c_isfinite(c_divide(i_cmodel_mag, i_cmodel_magsigma^2)) != 0)
FROM
  pdr2_dud.forced
WHERE
  isprimary;
;

Angle representation

dms2deg(dms: text) -> degrees: double
dms2deg converts a string in the format '+DD:MM:SS.SS' (e.g. '-87:65:43.21') to an angle in degrees. The angle ∈ [-90, 90].
hms2deg(hms: text) -> degrees: double
hms2deg converts a string in the format 'HH:MM:SS.SSS' (e.g. '12:34:56.789') to an angle in degrees. The input hour is multiplied by 15 to be the output angle. The hour ∈ [0, 24).
deg2dms(degrees: double) -> dms: text
dms2deg converts an angle in degrees to a string in the format '+DD:MM:SS.SS' (e.g. '-87:65:43.21'). The angle ∈ [-90, 90].
deg2hms(degrees: double) -> hms: text
hms2deg converts an angle in degrees to a string in the format 'HH:MM:SS.SSS' (e.g. '12:34:56.789'). The input angle is divided by 15 to be the output hour. The angle ∈ [0, 360).
Example
SELECT dms2deg('-01:00:12.00');
    --return -1.00333333333333

SELECT hms2deg('12:12:12.345');
    --return 183.0514375
SELECT deg2dms(83.0514375);
    --return +83:03:05.18

SELECT deg2hms(183.051416666667);
    --return 12:12:12.34
-- Get coordinates in hh:mm:ss.sss and +/-dd:mm:ss.ss rather than degrees:
SELECT
  deg2hms(ra ) AS ra,
  deg2dms(dec) AS dec
FROM
  pdr2_dud.forced
LIMIT 10;

Date & time representation

date2mjd(date: text) -> mjd: int
date2mjd converts a Gregorian date 'YYYY-MM-DD' (e.g. '2014-09-20') to a modified Julian day.
mjd2date(mjd: int) -> date: text
mjd2date converts a modified Julian day to a Gregorian date 'YYYY-MM-DD' (e.g. '2014-09-20').
datetime2mjd(datetime: text) -> mjd: double
datetime2mjd converts a Gregorian date followed by a time 'YYYY-MM-DDThh:mm:ss.sss' (e.g. '2014-09-20T12:34:56.789') to a modified Julian day.
datetime2mjd(date: text, time: text) -> mjd: double
datetime2mjd converts a Gregorian date 'YYYY-MM-DD' (e.g. '2014-09-20') and a time 'hh:mm:ss.sss' (e.g. '12:34:56.789') to a modified Julian day.
mjd2datetime(mjd: double) -> datetime: text
mjd2datetime converts a modified Julian day to a Gregorian date followed by a time 'YYYY-MM-DDThh:mm:ss.sss' (e.g. '2014-09-20T12:34:56.789').
mjd2datetime2(mjd: double) -> date: text, time: text
mjd2datetime2 converts a modified Julian day to a Gregorian date 'YYYY-MM-DD' (e.g. '2014-09-20') and a time 'hh:mm:ss.sss' (e.g. '12:34:56.789').
Example
SELECT date2mjd('2014-07-17');

SELECT mjd2date(56855);
SELECT datetime2mjd('2014-07-17T12:12:12.000');

SELECT datetime2mjd('2014-07-17', '12:12:12.000');

SELECT mjd2datetime(56855.5084722222);

SELECT mjd2datetime2(56855.5084722222);

Flux and magnitude

flux_to_mag(flux_nJy: real) -> mag: real
Convert flux (in nano-jansky) to AB magnitude.
mag_to_flux(mag: real) -> flux_nJy: real
Convert AB magnitude to flux (in nano-jansky)
flux_to_magsigma(flux_nJy: real, fluxsigma_nJy: real) -> magsigma: real
Convert flux error (in nano-jansky) to AB magnitude.
mag_to_fluxsigma(mag: real, magsigma: real) -> fluxsigma_nJy: real
Convert AB magnitude error to flux (in nano-jansky).
Example
-- Compare flux/magnitude in DB with those computed by the
-- conversion functions
SELECT i_cmodel_flux, mag_to_flux(i_cmodel_mag), i_cmodel_mag, flux_to_mag(i_cmodel_flux)
  FROM pdr2_dud.forced
  LIMIT 10;

Galactic coordinates

The following are conversion functions from the galactic coordinates to the equatorial coordinates (J2000), and vice versa. The functions are based on SLALIB 2.5-4.

gal2equ(l: double, b: double) -> ra: double, dec: double
gal2equ converts galactic coordinates (l, b) to equatorial coordinates (ra, dec). All the angles are in degrees.
equ2gal(ra: double, dec: double) -> l: double, b: double
equ2gal converts equatorial coordinates (ra, dec) to galactic coordinates (l, b). All the angles are in degrees.
Example
SELECT gal2equ(230.0, 20.0);

SELECT equ2gal(120.0, 30.0);
-- Get galactic coordinates of objects which are
-- within 60 arcsec from the center at (gal-lon, gal-lat) =
-- (237, 42) in the forced catalog.

SELECT
  object_id, gal.lon, gal.lat, i_cmodel_mag
FROM
  pdr2_dud.forced
  CROSS JOIN gal2equ(237, 42) AS equ(ra0, dec0)
  CROSS JOIN equ2gal(ra, dec) AS gal(lon, lat)
WHERE
  isprimary
  AND coneSearch(coord, equ.ra0, equ.dec0, 60)
;

Healpix functions

HpxAng2pixNest(order: int, ra: double, dec: double) -> pixel: bigint
HpxAng2pixNest converts (ra, dec) to a HEAPix pixel number in the NESTED scheme, with the given tessellation order.
HpxPix2angNest(order: int, pixel: bigint) -> ra: double, dec: double
HpxPix2angNest converts a HEALPix pixel number to (ra, dec), in the NESTED scheme with the given tessellation order.
HpxQueryDiscNest(order: int, ra: double, dec: double, radius: double) -> SET OF pixel: bigint
HpxQueryDiscNest queries HEALPix pixels within a circle with the radius degrees centered at (ra, dec) degrees. The pixel numbering scheme is NESTED with the given tessellation order. This function calls Healpix_Base2::query_disc_inclusive internally.
HpxQueryBoxNest(order: int, ra: double, dec: double, width: double, height: double) -> SET OF pixel: bigint
HpxQueryDiscNest queries HEALPix pixels within a box with the size width degrees ×height degrees centered at (ra, dec) degrees. The pixel numbering scheme is NESTED with the given tessellation order. This function calls Healpix_Base2::query_polygon_inclusive internally.
HpxCoverCircleXyzNest(order: int, x: double, y: double, z: double, radius: double) -> SET OF IDStart: bigint, IDEnd: bigint
HpxCoverCircleXyzNest queries HEALPix pixels within a circle with the radius degrees centered at (x, y, z). The pixel numbering scheme is NESTED with the given tessellation order, shifted up to the 20th order. The pixels are returned in a set of spans — pixel ∈ i[IDStarti, IDEndi). This function calls Healpix_Base2::query_disc_inclusive internally.
HpxCoverCircleEqNest(order: int, ra: double, dec: double, radius: double) -> SET OF IDStart: bigint, IDEnd: bigint
HpxCoverCircleEqNest queries HEALPix pixels within a circle with the radius degrees centered at (ra, dec) degrees. The pixel numbering scheme is NESTED with the given tessellation order, shifted up to the 20th order. The pixels are returned in a set of spans — pixel ∈ i[IDStarti, IDEndi). This function calls Healpix_Base2::query_disc_inclusive internally.

HSC specific functions

visitCcd2frameId(visit: int, ccd: int) -> frameId: text
visitCcd2frameId converts a pair of a visit number and a ccd ID (visit, ccd) to the corresponding frame ID (e.g. 'HSCA00000000').
frameId2visitCcd(frameId: text) -> visit: int, ccd: int
frameId2visitCcd converts a frame ID (e.g. 'HSCA00000000') to (visit, ccd).
Example
SELECT visitccd2frameid(2, 27);
    --return 'HSCA00000301'

SELECT frameid2visitccd('HSCA00000301');
    --return (2,27)

Math functions

All math functions of the C programming language (functions in <math.h> of C11) are imported into the database, except for long double versions of functions (sinl(x), expl(x), etc.) They can be called with the prefix c_, e.g.

SELECT c_log10(-10), c_log10(0), c_log10(10);

c_log10(x), unlike the PostgreSQL native function log(x), does not raise error when x ≤ 0. Instead, c_log10(x) returns NaN or -Infinity in such cases.

Another example:

SELECT c_exp(1e100);

c_exp(x), unlike the PostgreSQL native function exp(x), does not raise error when the return value is overflowing. Instead, c_exp(x) returns +Infinity in such cases.

Single precision versions of functions are also available:

SELECT c_sinf(0.1), c_cosf(0.1);

Rather newly specified functions are available:

SELECT c_erf(1), c_tgamma(5), c_nextafter(0, 'infinity');

If a function has output parameters, a tuple is returned.

SELECT * FROM c_remquo(13, 5);
/* ret_value | quo
  -----------+-----
          -2 |   3
*/

(Note: double remquo(double x, double y, int *quo);

The first element of such a tuple is always named ret_value and the return value is stored in it.

The other elements are the output parameters, and are named the same way as they are in the C11 standard document.

Together with C11 functions, the following functions are available:

c_float(x: double) -> ret_value: real
c_float casts a double precision value x to a single precision value without under/overflow errors.
c_multiply(x: double, y: double) -> ret_value: double
c_multiply computes x*y without under/overflow errors.
c_divide(x: double, y: double) -> ret_value: double
c_divide computes x/y without under/overflow errors.

Searches

These search functions are used in the WHERE-clause of a query. See examples.

coneSearch(coord: earth, ra: double, dec: double, radius: double) -> bool
Returns true if coord is within radius arcseconds from (ra, dec). The sky coordinates are in degrees. Radius is in arcseconds. For the type earth, see document of earthdistance module.
boxSearch(coord: earth, ra1: double, ra2: double, dec1: double, dec2: double) -> bool
Returns true if coord is in a box [ra1, ra2] × [dec1, dec2]. (Units are degrees). Note that boxSearch(coord, 350, 370, dec1, dec2) is different from boxSearch(coord, 350, 10, dec1, dec2). In the former, ra ∈ [350, 360] ∪ [0, 10]; while in the latter, ra ∈ [10, 350].
tractSearch(object_id: bigint, tract: int) -> bool
Returns true if the object object_id is in the tract tract.
tractSearch(object_id: bigint, tract1: int, tract2: int) -> bool
Returns true if the object object_id is in one of the tracts from tract1 to tract2 inclusive.
Example
SELECT
    object_id, ra, dec, i_kronflux_mag
FROM
    pdr2_dud.forced JOIN pdr2_dud.forced2 USING (object_id)
WHERE
    boxSearch(coord, 34.0, 36.0, -5.0, -4.5)
      /* is equivalent to
             ra  BETWEEN 34.0 AND 36.0
         AND dec BETWEEN -5.0 AND -4.5
         but boxSearch() is much faster
      */
    AND i_kronflux_mag < 25.5
LIMIT 10
;

The following function is used in searching image metadata tables

patch_contains(area: cube, wcs: coaddwcs, ra: double, dec: double) -> bool
Returns true if the image patch that has the pair (area, wcs) contains the point (ra, dec). The sky coordinates are in degrees. To get the pair (area, wcs), join public.skymap table. There is not a document for public.skymap. Query SELECT * FROM help('public.skymap'); instead. For the type cube, see document of cube module. The type coaddwcs is an opaque data type for this database only.
Example
-- List image patches that contain the point (ra, dec) = (35, -4)
SELECT
    mosaic.tract, mosaic.patch, mosaic.filter01
FROM
    pdr2_wide.mosaic JOIN public.skymap USING (skymap_id)
WHERE
    patch_contains(patch_area, wcs, 35, -4)
;

-- See the definition of public.skymap
SELECT * FROM help('public.skymap');