CosmoSim

Specific table query examples

Here you can find specific query examples for most of the tables found in simulations

AvailHalos

Get a list of all snapshots for which FOF catalogs are available in the MDR1 simulation:

SELECT * FROM mdr1.availhalos
WHERE fof='F' ORDER BY snapnum

This example will only work for fof being not an integer.

Get the snapshots for which BDM data exists for the MDPL simulation, use bdmv or bdmw according to the column names in the given simulation:

SELECT * FROM mdpl.availhalos
WHERE bdmw IS NOT NULL ORDER BY snapnum

Get the number of available FOF1-halos for each snapshot from the MDPL simulation:

SELECT snapnum, fof1 FROM mdpl.availhalos
WHERE fof1 IS NOT NULL ORDER BY snapnum

This information is only available for simulations from MDPL on; for MDR1 and Bolshoi one needs to count the halos manually. This can be done directly in the database like this:

SELECT COUNT(*) as num FROM mdr1.fof4
GROUP BY snapnum ORDER BY snapnum

AvailParticles

Get a list of all particle tables for the MDR1 database.

SELECT * FROM mdr1.availparticles
ORDER BY snapnum

This query retrieves all the snapshots for which full particle information is available.

BDM

Get the 10 most massive BDM halos at z=0:

SELECT * FROM mdr1.bdmv WHERE snapnum = 85
ORDER BY mvir DESC
LIMIT 10

This query retrieves the first ten records from the BDMV-table for redshift 0 (snapshot number 85) after sorting them by decreasing mass and hence returns the ten most massive objects.

BDMProf

Get profile for a given BDM-halo:

SELECT * FROM mdr1.bdmvprof
WHERE bdmid = 8511186098
ORDER BY rbin

This query retrieves the radial profile for a halo from the BDMV-table, using the halo’s bdmid.

Dens

Retrieve a 3-cells layer of the density field in xy-plane, sorted by density:

SELECT * FROM mdr1.dens512_z0 WHERE iz BETWEEN 450 AND 453
ORDER BY dens

**Find BDM-halos of galaxy and cluster size in low density regions:**
SELECT d.dens,h.bdmid,h.x,h.y,h.z,h.Mvir,h.Rvir,h.hostFlag
FROM mdr1.dens512_z0 d, mdr1.bdmv h
WHERE d.dens&lt;1 AND h.snapnum=85 AND h.mvir > 1.e12
AND h.phkey/8. = d.phkey
ORDER BY d.dens

This selects grid cells from the density field and looks for halos inside. We restrict the halos to those from the catalogue at redshift 0 (snapnum=85) and with a given mass threshold. The phkeys of the halos are based on a 1024³ grid, so they have to be divided by 8 to match the 512-grid phkeys.

Get available snapnums with redshifts and scale factors for density table with multiple snapshots

SELECT d.snapnum AS snapnum, r.zred AS zred, r.aexp AS aexp
FROM
  (SELECT snapnum FROM bolshoi.dens256) AS d,
  (SELECT snapnum, zred, aexp FROM bolshoi.redshifts) AS r
WHERE r.snapnum = d.snapnum
ORDER BY snapnum

This query first selects in subqueries the available snapnums from the density-table, as well as all the redshifts.

FOF

Get the 10 most massive FOF groups at z=0 from the MDR1-database:

SELECT * FROM mdr1.fof WHERE snapnum = 85
ORDER BY mass DESC
LIMIT 10

This query retrieves the first 10 records from the FOF-table for redshift 0 (snapshot number 85) after sorting them by decreasing mass and hence returns the most massive objects.

FoFmTree

Find all progenitors with more than 1000 particles for the most massive FOF group in the MDR1 database (fofTreeId = 100000000):

SELECT * FROM MDR1.FOFMtree
WHERE fofTreeId BETWEEN 100000000 AND
  (SELECT lastProgId FROM MDR1.FOFMtree WHERE fofTreeId = 100000000)
AND np>1000

Merger trees are stored in such a format that the full tree (all progenitors) can be retrieved for a given FOF group by finding all objects with foftreeids between the group’s own foftreeid and its lastprogid.

Get the mass accretion history of the 5 most massive FOF groups:

SELECT p.foftreeid, p.treerootid, p.treesnapnum, p.mass, p.np
FROM mdr1.fofmtree AS p,
(SELECT foftreeid, mainleafid FROM mdr1.fofmtree
  WHERE treesnapnum=39 ORDER BY mass DESC LIMIT 5) AS mycl
WHERE p.foftreeid BETWEEN mycl.foftreeid AND mycl.mainleafid
ORDER BY p.treerootid, p.treesnapnum

Here we preselect the foftreeids and mainleafids of the most massive FOF groups in the merger tree table (from redshift 0, treesnapnum=39) in a subquery and then retrieve all progenitors of the main branch (i.e. with foftreeids until the mainleafid). Since we are interested in the mass accretion history, we only ask for the necessary properties (mass, particle numbers), which minimizes the data transfer and thus the total query time.

Get the shape evolution of the FOF group with fofid = 85000000000

SELECT
  prog.foftreeid, prog.treesnapnum,
  f.axis2/f.axis1 AS 'b/a', f.axis3/f.axis1 AS 'c/a'
FROM
  (SELECT foftreeid, mainleafid FROM mdr1.fofmtree
   WHERE fofid = 85000000000) AS mycl,
  mdr1.fofmtree AS prog,
  mdr1.fof AS f
WHERE
  prog.foftreeid BETWEEN mycl.foftreeid AND mycl.mainleafid
  AND prog.fofid = f.fofid
ORDER BY
  prog.treesnapnum

We preselect again foftreeid and mainleafid for our desired FOF group in a subquery and name the resulting table “mycl”. This is then joined with the FOFMtree-table for getting the fofids of all progenitor groups. These fofIds can be used to look-up the properties of these FOF groups in the FOF-table (the third join).

FoFSub

Retrieve all substructures for the most massive FOF cluster (fofSubId = 85000000000000000) above a given mass (particles) threshold:

SELECT * FROM mdr1.fofsub
WHERE fofsubid BETWEEN 85000000000000000 AND
  (SELECT lastsubid FROM mdr1.fofsub WHERE fofsubid = 85000000000000000)
  AND np>50

or alternatively:

SELECT res.* from (
  SELECT sub.* FROM mdr1.fofsub host, mdr1.fofsub sub
  WHERE host.fofsubid = 85000000000000000
  AND sub.fofsubid BETWEEN host.fofsubid AND host.lastsubid
) AS res
WHERE res.np>50

All substructures (full substructure tree) for a given FOF cluster are found by searching for all FOF groups with fofSubIds between the cluster’s own fofSubId and its lastSubId. This is done with the query above, yet limited to FOF groups with more than 50 particles. In the second version of the query, the main part is done in the sub query and only afterwards the filter np>50 is applied. One could also write it all in one query like this:

SELECT sub.* FROM mdr1.fofsub host, mdr1.fofsub sub
WHERE host.fofsubid = 85000000000000000
AND sub.fofsubid BETWEEN host.fofsubid AND host.lastsubid
AND sub.np>50

NEEDS CHECKING FOR OPTIMIZING However, if you check the PaQu query plan now (after submitting the query), you’ll realize that PaQu doesn’t know which of the WHERE conditions should be executed first and chooses our particles-filter first. This results in a very long query time, since the full table is scanned for FOF groups with the appropriate particle number (and there’s no index on that!) and only then the subhalos are extracted. We can help PaQu here by nesting the query as above and thus ensuring that the query is executed in the correct order.

Find the main branch of a FOF group, e.g. for a more accurate center determination:

SELECT * FROM mdr1.fofsub
WHERE fofsubid BETWEEN 85000000000000000 AND
  (SELECT mainleafid FROM mdr1.fofsub WHERE fofsubid = 85000000000000000)

The main branch of a substructure tree is given by all FOF groups with fofsubids between the one from the top FOF group and its mainleafid (= last FOF group of main branch). See Substructures – main branch for the structure of substructure trees.

LinkLenght

Get the full table with levels and linking lengths:

SELECT * FROM mdr1.linklength ORDER BY `level`

Mfreq

Get the 10 most luminous galaxy clusters (in range above 100 GeV) at z=0 (snapnum 85)

SELECT * FROM mdr1.bdmwmfreq WHERE snapnum=85
ORDER BY gammagev DESC
LIMIT 10

This query retrieves the first 10 records from the galaxy cluster-table after sorting them by the gammagev-luminosity and hence returns the most luminous objects.

Get the spin of galaxy clusters with gammay ray luminosity above 100 GEV, > 1040 h-2 photons/s

SELECT f.gammagev, b.spin FROM mdr1.bdmwmfreq f, mdr1.bdmw b
WHERE f.bdmid = b.bdmid
AND f.gammagev > 1.e40

By joining the galaxy cluster table with the BDMW table (using the bdmid), one can get access to all properties of the underlying dark matter halos. Examples for reproducing the figures in Zandanel, Pfrommer & Prada 2013a and Zandanel, Pfrommer & Prada 2013b

The following example queries can be used to reproduce some of the figures in the papers given above. For those figures where one curve per redshift is given, we just picked one redshift (and thus one snapnum) as example in the query.

Paper I, Figure 2, left: Relation between bolometric X ray luminosity and mass (M500)

SELECT
    LOG10(val.Ez*b.M500/0.7) AS logEzM,
    LOG10(b.LX/val.Ez/1.e44/0.7/0.7) AS logLX
FROM
    mdr1.bdmwmfreq b,
    (SELECT SQRT(0.3*POWER((1+0.2),3)+0.7) AS Ez FROM mdr1.bdmv LIMIT 1) AS val
WHERE b.snapnum=74

Here we use the trick to first calculate an expression in a subquery and name it val.ez, so that it can be used in the main query as a parameter.

We could also make it less complicated by replacing the term SQRT(0.3*POWER((1+0.2),3)+0.7) directly by its value 1.103811578:

SELECT
    LOG10(1.103811578*M500/0.7) AS logEzM,   
    LOG10(LX/1.103811578/1.e44/0.7/0.7) AS logLX
FROM mdr1.bdmwmfreq
WHERE snapnum=74

Paper I, Figure 2, right: Relation between thermal energy and mass (YX-M500)

SELECT
    LOG10(val.Ez*b.M500/0.7) AS logEzM,
    LOG10(b.YX*val.Ez*POWER(0.7,(-2.5))) AS logYX
FROM
    mdr1.bdmwmfreq b,
    (SELECT SQRT(0.3*POWER((1+0.2),3)+0.7) AS Ez FROM mdr1.bdmv LIMIT 1) AS val
WHERE b.snapnum=74

Paper I, Figure 4, left: YSZ-M relation

SELECT
    LOG10(b.M500/0.7) AS logEzM,
    LOG10(b.SZ*POWER(val.Ez,(-2/3))*POWER(0.7,-2.5)) AS logSZ
FROM
    mdr1.bdmwmfreq AS b
    (SELECT SQRT(0.3*POWER((1+0.2),3)+0.7) AS Ez FROM mdr1.bdmv LIMIT 1) AS val
WHERE snapnum=75

Paper II, Figure 4, left: Radio-to-X-ray scaling relation (L1.4-YX)

SELECT LOG10(LX/0.7/0.7) AS logLX, LOG10(radioGHz/0.7/0.7) AS logradioghz
FROM mdr1.bdmwmfreq
WHERE snapnum=74

Paper II, Figure 4, right: Radio-to-SZ scaling relation (L1.4-YSZ)

SELECT LOG10(SZ*POWER(0.7,(-2.5))) AS logSZ, LOG10(radioGHz/0.7/0.7) AS logradioghz
FROM mdr1.bdmwmfreq
WHERE snapnum=74

### Redshifts

SELECT * FROM mdr1.redshifts ORDER BY snapnum DESC

This query selects the complete redshifts-table.

Rockstar

Get 10 rows from the ROCKSTAR table

SELECT * FROM smdpl.rockstar
LIMIT 10

This query retrieves ten records (arbitrarily) from the Rockstar-table.

Get the 10 most massive ROCKSTAR-halos at z=0

SELECT rockstarid, x, y, z, mvir, rvir FROM smdpl.rockstar WHERE snapnum = 116
ORDER BY mvir DESC
LIMIT 10

This query retrieves the first ten Rockstar-halos for redshift 0 (snapshot number 116) after sorting them by decreasing mass and hence returns the ten most massive objects.

Get all progenitors of a halo

SELECT p.* FROM mdpl2.rockstar AS p,
(SELECT depthfirstid, lastprog_depthfirstid FROM mdpl2.rockstar WHERE rockstarid=...) AS r WHERE p.depthFirstId BETWEEN r.depthFirstId AND r.lastProg_depthFirstId
ORDER BY p.snapnum

This query makes use of the properties of depthfirstids: all halos with ids between the current halos id and its last progenitor id (lastprog_depthfirstid) must be progenitors.

Get the main branch, i.e. the most massive progenitors for each timestep, for e.g. extracting mass accretion history, for a halo with given rockstarId

SELECT p.* FROM MDPL2.Rockstar AS p,
(SELECT depthFirstId, mainLeaf_depthFirstId FROM MDPL2.Rockstar WHERE rockstarId=...) AS mycl WHERE p.depthfirstid BETWEEN mycl.depthfirstid AND mycl.mainleaf_depthfirstid
ORDER BY p.snapnum

This uses the mainleaf_depthfirstid which is the depthfirstid of the last leaf on the main branch, i.e. it’s the earliest found main progenitor (with mmp-flag = 1).

treeSnapnums

This query selects the complete table.

SELECT * FROM mdr1.treesnapnums

Web

Retrieve slice of the cosmic web in xy-plane, at z=314

SELECT ix,iy,iz, eigen1, eigen2, eigen3,
  (CASE WHEN eigen1 >= 0.4 THEN 1 ELSE 0 END)
+ (CASE WHEN eigen2 >= 0.4 THEN 1 ELSE 0 END)
+ (CASE WHEN eigen3 >= 0.4 THEN 1 ELSE 0 END) AS num_above_04
FROM MDR1.Vweb512
WHERE iz=315

Additionally to extracting all values for that slice, we calculate here the number of eigenvalues above or equal to) a given threshold by using the CASE-WHEN-THEN construct.

Alternatively, one can also use the SIGN-function to calculate the number more directly:

SELECT ix,iy,iz, eigen1, eigen2, eigen3,
      SIGN(SIGN(eigen1-0.4)+1)
    + SIGN(SIGN(eigen2-0.4)+1)
    + SIGN(SIGN(eigen3-0.4)+1)
        AS num_above_04
FROM mdr1.tweb512
WHERE iz = 315

This version is somewhat less readable, but a bit faster.

Retrieve a slice of the cosmic web in xy-plane, centered on the most massive FOF halo

SELECT  ix,iy,iz, eigen1, eigen2, eigen3
FROM mdr1.tweb512 AS w,
WHERE w.iz = FLOOR(0.5*
    (SELECT iz FROM mdr1.fof
     WHERE snapnum = 85 ORDER BY MASS DESC LIMIT 1)
    )

This is a nested query: first the required iz-value for the most massive FOF-halo is looked up and then used for selecting only the cosmic cells from an xy-plane that contains this halo. Note that we need to divide the FOF’s grid index by 2, since the grid of the cosmic web is 2**3 times smaller.