Special functions
We have written some userdefined functions (UDF) for the database which help to make specific queries possible or easier to handle. They are shortly described here and available to all users.
List of available functions:
hilbertKey
Calculate the PeanoHilbert key for the spacefilling curve of specified dimension and order for the given box size and coordinates.
Usage:
hilbertKey(hilbert order, box size, num dim, x, y, z, ... )
hilbert order

order of the Hilbert curve, MDPL: 10, BolshoiP: 8, CLUES: 7 
box size

size of cosmological box, same units as for coordinates, float 
num dim

number of dimension, in our case for spatial curves: 3 
x,y,z

coordinates x,y,z, same units as for box size, if > 3 dimensions, they would be added here 
Example:
SELECT hilbertKey(10,1000.0,3,123.432,24.123,890.30)
coordFromHilbertKey
Function for retrieving the coordinates from a given PeanoHilbert key. Returns the smallest position of the cell (lower left corner), since we start counting cells with 0.
Usage:
coordFromHilbertKey(hilbert order, box size, num dim, hilbert key, current dimension (separate call for each dimension))
hilbert order

order of the Hilbert curve, MDPL: 10, BolshoiP: 8, CLUES: 7 
box size

size of cosmological box, same units as for coordinates, float 
num dim

number of dimension, in our case for spatial curves: 3 
hilbert key

PeanoHilbert key as was calculated by function hilbertKey, phkey column of the database tables (only works for MDPL, BolshoiP and CLUES at the moment)

The size of each PeanoHilbert curve cell (per dimension) can be calculated like this:
cellsize = box size
/ 2^num dim
That means, each cell extends from the coordinates given by coordFromHilbertKey()
up to +cellsize in each dimension.
Example:
SELECT coordFromHilbertKey(10,1000.0,3,1021836460,0) as x, coordFromHilbertKey(10,1000.0,3,1021836460,1) as y, coordFromHilbertKey(10,1000.0,3,1021836460,2) as z
SELECT bdmId, x,y,z,phkey, hilbertKey(10,1000.0,3,x,y,z) as xyzphkey, coordFromHilbertKey(10,1000.0,3,phkey,0) as phx, coordFromHilbertKey(10,1000.0,3,phkey,1) as phy, coordFromHilbertKey(10,1000.0,3,phkey,2) as phz FROM MDPL.BDMW WHERE snapnum=88 ORDER BY Mvir DESC LIMIT 10
sprng_dbl
Function for returning a random number between 0 and 1, using an implementation of the SPRNG library. See mysql_sprng for the exact implementation of the plugin.
Usage:
sprng_dbl(iseed), sprng_dbl(iseed, type)
iseed

seed for random number generator; integer 
type

type of the random number generator, one of ‘LFG’, ‘LCG’, ‘LCG64’, ‘CMRG’, ‘MLFG’; string 
Example:
SELECT sprng_dbl(123), sprng_dbl(123), sprng_dbl(123);
SELECT fofId, x,y,z FROM MDR1.FOF WHERE snapnum=85 AND sprng_dbl(123) < 0.0001
Be careful: providing the same seed for multiple queries does not mean that the sequence of the random numbers is the same. The seed is used to initialize a sequence of random numbers on each node, but the number of calls to sprng_dbl() is not stored globally and not reset after a query is finished.
pdist
This function calculates the distance between two points in a box with cosmological boundary conditions. It returns the shorted distance between these points as a float number.
Usage:
pdist(box size, pointA_x, pointA_y, pointA_z, pointB_x, pointB_y, pointB_z)
box size

size of cosmological box, same units as for coordinates, float 
pointA_x

xcoordinate of first point 
pointA_y

ycoordinate of first point 
pointA_z

zcoordinate of first point 
pointB_x

xcoordinate of second point 
pointB_y

ycoordinate of second point 
pointB_z

zcoordinate of second point 
Example:
SELECT pdist(1000., 10.,11.,12., 11.,11.,12.), pdist(1000., 10.,11.,12., 1011.,11.,12.), pdist(1000., 10.,11.,12., 990,11.,12.);
SELECT p.bdmId, p.x,p.y,p.z, p.Rvir, s.bdmId, s.hostFlag, s.x,s.y,s.z, s.Rvir, pdist(1000., s.x,s.y,s.z, p.x,p.y,p.z) as dist FROM MDPL.BDMW AS s, (SELECT bdmId, x,y,z,Rvir FROM MDPL.BDMW WHERE bdmId = 8800068215607) AS p WHERE s.snapnum=88 AND s.hostFlag = p.bdmId