Special functions

We have written some user-defined 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 Peano-Hilbert key for the space-filling 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 Peano-Hilbert 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 Peano-Hilbert 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 Peano-Hilbert 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 x-coordinate of first point
pointA_y y-coordinate of first point
pointA_z z-coordinate of first point
pointB_x x-coordinate of second point
pointB_y y-coordinate of second point
pointB_z z-coordinate 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

Proudly powered by Daiquiri
©2016 The CosmoSim databaseImprint and Data Protection Statement