Some useful stored functions

Created: June 17, 2010

 

Strored functions and procedures are used in SQL queries to do calculations, usually based on the values of the column values of each row. These are some simple routines from DLS2009a which you can use. If you want to define your own functions, you will need a database which gives you the right to create objects and execute. To run these predefined functions, you just need EXECUTE privileges on the DLS2009a database.

 

float fAngularDistance

 

alphacent double, deltacent double, alpha double, delta double

 

Returns distance in degrees between the 1st and 2nd coordinates.

The first pair is treated as the center of a projection

 

float fBPZerr

magerr FLOAT

Returns an error field as it would be in BPZ, with a null standing for a very large error.

 

float fBPZmag

mag FLOAT, magerr FLOAT, ecorr FLOAT

Returns the mag in BPZ convention, with 99 for a non-detect

And -99 for unobserved.

int fCountCommas

mystring varchar(100)

 

Return the number of commas in a CSV string

tinyint fIsInsideCircle

alphacent double, deltacent double, radius FLOAT, alpha double, delta double

 

Returns true if (alpha,delta) is within radius arcseconds

of (alphac,deltac)

 

tinyint fIsInsideSquare

alphacent double, deltacent double, radius FLOAT, alpha double, delta double

Returns true if (alpha,delta) is within the bounding rectangle

Centered on (alphcent,deltacent) with side of length 2*radius arcseconds

float fMaxFloat
f1 FLOAT, f2 FLOAT

 

Return the maximum of f1,f2.

If one is null, return the other.

float fMinFloat

f1 FLOAT, f2 FLOAT

Return the minimum of f1,f2.

If one is null, return the other.

 

varchar(100) fNearbyObjectIds

objid bigint, alpha double, delta double, processmask int, rlimit FLOAT

 

Return a CSV list of object ids within rlimit arcseconds of alpha,delta.
Number of objects is limited by the length of the return variable.

 

varchar(100) fNearbyObjectIdsFromId

objid bigint, rlimit FLOAT

Return a CSV list of object ids within rlimit arcseconds of the position

Of the object with objid given.

Number of objects is limited by the length of the return variable.

 

bigint fNearestMask

alpha double, delta double, rlimit FLOAT

Get maskid of the mask nearest alpha,delta but within rlimit arcseconds.

bigint fNearestPhotoObj

alpha double, delta double, rlimit FLOAT

Get objid of the object nearest alpha,delta but within rlimit arcseconds.

 

bigint(20) fNearestSpectrum

alpha double, delta double, rlimit float

 

Get specid of the spectrum nearest alpha,delta but within rlimit

arcseconds.

char(5) fNearestSubfield

alpha double, delta double

 

Get the subfield which has its center nearest (alpha,delta)

 

 

 


Common data types:

 

alpha double synonym for ra, in decimal

delta double synonym for dec, in decimal

rlimit float distance from a center point, in arcseconds

objid bigint primary key of detection catalog

subfield char(5) DLS subfield designator

x float x position within a projection

y float y position within a projection

cellx int x cell cutout coordinate (0,,9)

celly int y cell cutout coordinate (0,,9)

number bigint position within sextractor catalog

Notes on Spatial Queries:

 

Spatial queries (such as cone searches) can be very slow in query languages. That is because query engines use indexes to help them find things, and indexes are by nature 1 dimension orderings, which spatial searches require 2 dimensions.

 

If you perform a query such as:

 

select alpha,delta from PhotoObj where fAngularDistance(30,140,alpha,delta) < (10.0/3600)

 

which should find objects within 10 arcsecs of ra=30, dec=140, you will find yourself waiting for a long time for the query to complete. That is because the query engine has to scan the entire table (6 million rows) to decide which objects lie inside your small patch of sky.

 

One solution is to use indexes to create a bounding box, limiting your search space:

 

select alpha,delta from PhotoObj

where alpha>(1400-.03208) and alpha<(1400-.03208) and delta>(30-.00278) and delta<(30+.00278)

and fAngularDistance(30,140,alpha,delta) < (10.0/3600)

 

The data base has indexes on alpha and delta, making it fast to filter down to the set satisfying the middle line. The fAngularDistance criterion is applied last, which makes the who query reasonably fast.

 

Please note: The query engine does not perform where clause components left-to-right the way that programming languages often do. Instead, it is the role of the query engine to decide on ordering, based on what it thinks will create the fastest query.

 

If this seems pretty long winded, you can formulate the query string with a script or program before submitting it.