Specific table query examples
Here you can find specific query examples for most of the tables found in simulations
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
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
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
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.
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.
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.
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<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 10243 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.
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.
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
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
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
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).
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
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.
Get the full table with levels and linking lengths:
SELECT * FROM mdr1.linklength ORDER BY `level`
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
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
SELECT * FROM mdr1.redshifts ORDER BY snapnum DESC
This query selects the complete redshifts-table.
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
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).
This query selects the complete table.
SELECT * FROM mdr1.treesnapnums
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.