## 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
```