procname: The name of the procedure.paramN: The name of a parameter.typeN: The type of paramN.retval: The return value.rettype: The type of retval.description.
Examples will appear when you click on the “Example” button.
In addition to the PostgreSQL standard aggregate functions, the following aggregate functions are available.
skewness returns κ3/κ23/2,
where κi denotes the unbiased estimator of the i-th cumulant.
Note that the skewness such defined is biased.kurtosis returns κ4/κ22,
where κi denotes the unbiased estimator of the i-th cumulant.
Note that the kurtosis such defined is biased.-- 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 pdr3_dud.forced WHERE isprimary;
There are binary functions of the same type as above:
weighted_mean returns the weighted mean of the values, namely
∑ivaluei×weighti / ∑iweighti.
avg calculates (∑value/stddev2)
/ (∑1/stddev2).chi2_per_freedom calculates χ2 per degree of freedom,
which is defined as
(N-1)-1
∑ (value - ⟨value⟩)2 / stddev2.-- calculate weighted mean of sinc magnitudes weighted by errors SELECT avg(i_cmodel_mag, i_cmodel_magerr) FILTER (WHERE c_isfinite(c_divide(i_cmodel_mag, i_cmodel_magerr^2)) != 0) FROM pdr3_dud.forced WHERE isprimary; ;
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 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).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].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).
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 pdr3_dud.forced LIMIT 10;
date2mjd converts a Gregorian date 'YYYY-MM-DD' (e.g. '2014-09-20')
to a modified Julian day.mjd2date converts a modified Julian day
to a Gregorian date 'YYYY-MM-DD' (e.g. '2014-09-20').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 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 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 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').
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);
-- 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 pdr3_dud.forced LIMIT 10;
radec_to_coord().radec_to_coord().ra1 and ra2 in degrees
and between the two parallels dec1 and dec2 in degrees.-- Find frames (CCD images) around (ra, dec) = (150, 2) SELECT visit, ccd FROM pdr3_wide.frame WHERE areacube && cube_from_radecrange(149.9, 150.1, 1.9, 2.1);
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 converts galactic coordinates (l, b)
to equatorial coordinates (ra, dec).
All the angles are in degrees.equ2gal converts equatorial coordinates (ra, dec)
to galactic coordinates (l, b).
All the angles are in degrees.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 pdr3_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) ;
HpxAng2pixNest converts (ra, dec)
to a HEAPix pixel number in the NESTED scheme, with the given tessellation order.HpxPix2angNest converts a HEALPix pixel number to (ra, dec),
in the NESTED scheme with the given tessellation order.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.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 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 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.visitCcd2frameId converts a pair of a visit number and a ccd ID
(visit, ccd) to the corresponding frame ID (e.g. 'HSCA00000000').frameId2visitCcd converts a frame ID (e.g. 'HSCA00000000')
to (visit, ccd).
SELECT visitccd2frameid(2, 27);
--return 'HSCA00000301'
SELECT frameid2visitccd('HSCA00000301');
--return (2,27)
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 casts a double precision value x to a single precision value
without under/overflow errors.c_multiply computes x*y without under/overflow errors.c_divide computes x/y without under/overflow errors.These search functions are used in the WHERE-clause of a query. See examples.
coord is within radius arcseconds
from (ra, dec).
The sky coordinates are in degrees. Radius is in arcseconds.
For the type cube, see document of cube module.
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].
object_id is in the tract tract.
object_id is in one of the tracts
from tract1 to tract2 inclusive.
SELECT
object_id, ra, dec, i_kronflux_mag
FROM
pdr3_dud.forced JOIN pdr3_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
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.
-- List image patches that contain the point (ra, dec) = (35, -4)
SELECT
mosaic.tract, mosaic.patch, mosaic.filter01
FROM
pdr3_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');