FAQ/Very useful queries

Some often asked questions are answered below. Please also have a look at the CosmoSim blog. You can also use the Search field at the top of the table of contents on the right side to find articles/pages for a specific topic.
A number of example queries dealing with specific tables can also be found at each table type documentation, see Database Tables.
And if you cannot find what you are looking for, please send us a message via the Contact form.

0) Error messages

Please have a look at this blog entry: Known database query errors and issues. Some errors and their sources/workarounds are explained there.

1) How to get redshifts and snapshot numbers for a simulation?

For each simulation, there is a table called Redshifts. This tables contains redshifts (zred) with their scale factor (aexp) and snapshot number (snapnum) of the simulation.
A query would look like this:

SELECT DISTINCT zred, snapnum, aexp FROM MDR1.Redshifts 
ORDER BY snapnum DESC

The DISTINCT keyword is used to avoid duplicate results, because the table is replicated on each node.

If you want to know which data is available for each redshift, then consult the AvailHalos table. It contains a table with the snapshot numbers, redshifts and available halo data, like FOF- and BDM-catalogue, halo profiles etc. Consult the corresponding documentation for the possible columns.

SELECT DISTINCT snapnum, FOF FROM MDPL.AvailHalos
WHERE FOF IS NOT NULL ORDER BY snapnum
2) How to retrieve a random selection of particles?
SELECT particleId,x,y,z,vx,vy,vz FROM MDR1.Particles85 
WHERE sprng_dbl(154321) <= 0.0001

This query retrieves roughly 0.01% of the particles (~ 860,000) from MDR1 at z=0 (Particles85-table). The seed given as argument to sprng_dbl() is an arbitrary number. sprng_dbl() is a user-defined function, written as plugin for MySQL. See Special functions and the instructions/source code at Github - mysql_sprng for more details.

Do not forget to use the Long queue for a query like this, since it involves a full table scan which takes about 10 minutes (per node) for this table.

NOTE: Do not use the built-in function RAND() here with a given seed, because then the same seed would be used on each of our nodes.

If you need an exact number of particles numpart, then calculate the required fraction using:
frac = numpart/ntot,
where ntot is the total number of particles in the simulation.
For example, using the MDR1-simulation and selecting 500,000 particles, you would get:
frac = 500000/(2048^3) = 5.82077e-5.

Please do not use LIMIT in this query. This would cut away a number of particles and can introduce a spatial bias, since particles on the nodes may be presorted spatially in some way. Always limit your particle numbers by choosing the correct fraction.

If you then need to choose an even smaller fraction than before, say, 5000 particles, you could query your own result table from the previous query to make things faster:

SELECT particleId,x,y,z,vx,vy,vz 
FROM cosmosim_user_<username>.<resulttable>
WHERE sprng_dbl(14324) <= 0.01

Replace <username> by your username and <resulttable> by the name of your result from the previous query.

You could also calculate a random number for each particle

SELECT particleId,x,y,z,vx,vy,vz, sprng_dbl(1221) AS randnum 
FROM cosmosim_user_<username>.<resulttable>

and then select from the resulting table ordering by randnum. This way you can also safely use the LIMIT keyword:

SELECT particleId,x,y,z,vx,vy,vz, randnum 
FROM cosmosim_user_<username>.<resulttable2>
ORDER BY randnum LIMIT 5000;
3) How to find the main branch of one FOF halo?
SELECT prog.* FROM MDR1.FOFMtree AS prog, 
( SELECT fofTreeId, mainLeafId FROM MDR1.FOFMtree WHERE fofId = 85000000000  ) AS descend  
WHERE prog.fofTreeId BETWEEN descend.fofTreeId AND descend.mainLeafId
ORDER BY prog.fofTreeId ASC

Replace the fofId 85000000000 by the fofId of the halo you are interested in (choose from the MDR1.FOF-table). See Merger trees for more information on the structure of merger trees in the database.

4) How to extract progenitors of BDM halos?

Unfortunately, it is not possible to do this directly, since there are no merger tree tables for BDM-catalogues available in the database.
The halos' bdmIds of different timesteps are NOT related with each other, that link can only be established with a merger tree. Since we only have merger trees for FOF and Rockstar catalogues in the database, you could use e.g. the FOF merger tree and match BDM halos with their closest FOF halo.

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