On this page we document the demo queries available behind the corresponding buttons
on the main query page. The following list gives quick links to the corresponding query.
GP1
Select positions and K band magnitude for galaxies in the Gonzalez2014a model with
stellar mass grater than 1.0e8Msolar/h in an 8Mpc/h slice through the MR7 simulation box.
in the x direction.
select
stellarmass, mag_K, x, y, z
from
Gonzalez2014a..mr7
where
x between 0 and 8
and SnapNum=61
and stellarmass > 0.01

GP2
Select all progenitors of a galaxy specified by GalaxyID.
select
*
from
Gonzalez2014a..mr7 as prog,
Gonzalez2014a..mr7 as des
where
des.galaxyid = 0 and
prog.galaxyid between des.galaxyid and des.lastprogenitorid

GP3
Count galaxies in luminosity bins at z=0 in the B band
select
.2*(.5+floor(mag_b/.2)) as mag,
count(*) as num
from
Gonzalez2014a..mr7
where
snapnum=61
group by .2*(.5+floor(mag_b/.2))
order by .2*(.5+floor(mag_b/.2))

GP4
Return the HOD split by centrals and satellites of a stellar mass limited sample of a specific snapshot from the Gonzalez14a model.
Actual query uses:
stellarmass > 10^10 Msol/h , snapshot = 61 (i.e. redshift = 0 output)
select
.1*(.5+floor((log10(mdhalo)+10)/.1)) as logx,
1.*SUM(CASE WHEN stellarmass > 1 THEN 1 ELSE 0 END)/SUM(CASE WHEN type=0 THEN 1 ELSE 0 END) as HOD,
1.*SUM(CASE WHEN stellarmass > 1 AND type=0 THEN 1 ELSE 0 END)/SUM(CASE WHEN type=0 THEN 1 ELSE 0 END) as Centrals,
1.*SUM(CASE WHEN stellarmass > 1 AND type!=0 THEN 1 ELSE 0 END)/SUM(CASE WHEN type=0 THEN 1 ELSE 0 END) as Satellites
from
Gonzalez2014a..MR7
where
SnapNum = 61
group by .1*(.5+floor((log10(mdhalo)+10)/.1))
order by .1*(.5+floor((log10(mdhalo)+10)/.1))

LC1
Count the number of galaxies with SDSS r band apparent magnitudes brighter than 16.0 in the DESI_v1 Gonzalez2014a lightcone
select count(*) from DESI_v1..LC_20sqdeg_Gonzalez2014a where SDSS_r_obs_app < 16.0

LC2
Identify bright galaxies in the DESI_v1 lightcone and find their progenitors at z=3 (snapshot 25) in the semianalytic model
select
prog.*
from
DESI_v1..LC_20sqdeg_Gonzalez2014a as lc,
DESI_v1..GAL_Gonzalez2014a as des,
DESI_v1..GAL_Gonzalez2014a as prog
where
lc.SDSS_r_obs_app < 14.0 and
lc.galaxyid = des.galaxyid and
prog.galaxyid between des.galaxyid and des.lastprogenitorid and
prog.snapnum = 25

LC3
For a random sampling of all satellite galaxies of a fixed stellar mass range in a redshift interval in the GAMA equatorial fields,
the query returns the redshift, the host halo mass and the stellar mass from the last snapshot/redshift the galaxy was central.
For completeness, the query also returns the descendent and progenitor GalaxyID, the descendent UniqueGalaxyID,
redshift, host halo mass and stellar mass.
Actual query uses r<20 (GAMA limit), 0.01 0 (satellite), field < 4 (equatorial), stellarmass > 10^9 Msol/h, 1/500 sampling
Assumptions made (& true for GALFORM Gonzalez2014a model): (1) stellar mass grows with time (monotonic) ; (2) halo mass grows with time (monotonic)
With LC_SAM as
(
Select LC.GalaxyID as Gal_Id, LC.UniquegalaxyID as UGal_Id, LC.redshift_obs as z_obs,
SAM.mainLeafID as MLI, SAM.mdhalo as mdhalo, SAM.stellarmass as stellarmass,
SAM.redshift as redshift, SAM.random as rand
From GAMA_v1..LC_single_gonzalez2014a as LC,
GAMA_v1..GAL_Gonzalez2014a as SAM
Where LC.galaxyID = SAM.galaxyID and LC.redshift_obs between 0.01 and 0.13
and LC.SDSS_r_obs_app < 20.0 and LC.type > 0 and LC.field < 4
and SAM.stellarmass > 0.1 and SAM.random < 0.002
)
Select DES.UGal_Id, MAX(DES.Gal_Id) as Des_Gal_Id,
MAX(DES.z_obs) as Des_z_obs,
MAX(DES.redshift) as Des_redshift,
MAX(DES.mdhalo)*1.E+10 as Des_mdhalo,
MAX(DES.stellarmass)*1.E+10 as Des_stellarmass,
MIN(PROG.GalaxyID) as Prog_Gal_Id,
MIN(Prog.redshift) as Prog_redshift,
MAX(Prog.mdhalo)*1.E+10 as Prog_mdhalo,
MAX(Prog.stellarmass)*1.E+10 as Prog_stellarmass
from GAMA_v1..GAL_Gonzalez2014a PROG,
LC_SAM as DES
where PROG.galaxyID between DES.Gal_Id and DES.MLI
and PROG.type = 0
GROUP BY DES.UGal_Id
ORDER BY DES.UGal_Id
option(table hint(PROG, forceseek))

