0  :  Index

1  :  Website overview

This website comes in two flavours, a public and a protected one. The public web site gives access to all the information about the Millennium database products, but only gives query access to a small version of this simulation, the so-called milli-Millennium (millimil). The protected site requires an account. How to acquire such an account see here.

To gain access to the rest of this documentation follow the buttons or go to the Table of Contents.

1.1  :  Registration

To register for an account allowing access to the protected part of the Millennium database please send an email to j.c.helly at durham.ac.uk.
Please provide us with some information about you, especially about your affiliation and your intended use.

The reasons we protect the main database from public access are various. First is simply a question of resources. It is very easy for users, even if experienced ones, to submit queries that use up all the resources of the database server for a long time. This is especially true for the main database, which is about 512 times larger than the milli-Millennium database to which the public site gives access.
For this same reason we have as yet not set up an automated registration mechanism. On the one hand we want to prevent the other users from frivolous usage by some, which is why we would like some indication of affiliation. But we also hope to gain experience with the usage patterns by external users not directed associated to the Virgo project. This may allow us to tune the database to those potentially very different requests.

1.2  :  Credits

You are welcome to use the simulation data in these pages for your own scientific purposes without further consultation with the Virgo Consortium or the scientists who generated them. If you do so we would appreciate it if you include a reference to the paper announcing the database:

• G. Lemson & the Virgo Consortium 2006, arXiv:astro-ph/0608019

• The Millennium Simulation: V. Springel et al. 2005, Nature 435, 629
• The Millennium-II Simulation: M. Boylan-Kolchin et al. 2009, MNRAS 398, 1150
• The Millennium-WMAP7 Simulation: Guo et al. 2013, MNRAS 428, 1351

If you use galaxy data, then you should in addition cite the papers which describe the specific model underlying these data. For the MPA models these papers are:

• De Lucia G. & Blaizot J. 2007 (MNRAS 375, 2; also Croton D. et al. 2006 MNRAS 365, 11) for the DeLucia2006a and DeLucia2006a_SDSS2MASS tables in both the millimil and MPAGalaxies databases,
• Bertone, De Lucia and Thomas 2007 (MNRAS 379, 1143) for the Bertone2007a table in the MPAGalaxies database.
• Guo etal 2011 (MNRAS 413, 101) for all the tables in the Guo2010a database.
• Guo et al. 2013, (MNRAS 428, 1351) for all the tables in the Guo2013a database and the MR7, MRscWMAP7 and MRIIscWMAP7 tables in the MPAHalotrees database.

If you use mock galaxy catalogues in MPAMocks or Henriques2012a, please look at the documentation for the specific datasets for possible additional credits.

In order to acknowledge GAVO's support in constructing this database facility, we would appreciate inclusion of the following sentence in the `Acknowledgements' section of papers which make significant use of our databases:

"The Millennium Simulation databases used in this paper and the web application providing online access to them were constructed as part of the activities of the German Astrophysical Virtual Observatory (GAVO)."

(replace "Millennium" with "Millennium-II" or add "Millennium-II" if appropriate)

The particular structure of the database design which allows efficient querying for merger trees is described in:
Lemson G. & Springel V. 2006, Astronomical Data Analysis Software and Systems XV, ASP Conference Series, Vol. 351, 212, C. Gabriel, C. Arviset, D. Ponz and E. Solano, eds.

1.3  :  Latest news

2013-02-26: Upgrade Millennium database hardware and new data sets

2012-12-21: Slides form Millennium workshop

2012-08-29: 500 publications using Millennium data

2012-08-09: Migration databases

We have completed the migration of the Millennium databases and the web applications to their new location. Note that the URL to the Millennium database will NOT change, it still is http://gavo.mpa-garching.mpg.de/MyMillennium

We will keep a version of the old web site+database available under http://gavo.mpa-garching.mpg.de/MyMillennium_old Note that your mydb will remain read-only on the old site.

You may wish to compare the two sites, in particular the private databases that you have access to. We have decided not to migrate all these databases. If you think some are missing on the new site (but still exist on MyMillennium_old) and you would like to keep accessing these please send me an email. Also if you think that the contents of your mydb-s has changed please let us know. We have attempted to copy all the non-empty databases, but recreated the empty ones.

2012-01-17: New databases with lightcones

The new database will be named Henriques2012a. After the update is complete you will see the new database listed under the "Public databases" section on the left menu bar of the web page. The link leads to the documentation pages for this database. Note that not all individual tables will be listed in the menu, as there are ~100 of them. Please let me know if the documentation is not clear.

We have also taken this opportunity to move the web application to a newer server and to rename it back from MyMillennium3 to MyMillennium. The old address will be usable for a little while longer, but will redirect you to the new web app. The full address will therefore be: http://gavo.mpa-garching.mpg.de/MyMillennium

20??-??-??: missing news items

2011-03-18: New addresses for Millennium databases

20??-??-??: missing news items

2009-05-01: New release of Millennium website and databases

• Millennium-II database
As announced here the FOF groups, subhalo-s and subhalo merger trees from the Millennium-II simulation are now available in the MillenniumII database.
• Change to the comma-separated values format of streaming queries
We have made a change to the CSV format that is returned by the "Query (stream)" button on the web page, and which also it the data format that will be returned when using wget. In the old version of the web site the end of the file was either an error message in case a timeout happened, or simply the last row of data. The problem with this is that in case the timeout is not captured by the web server, for example because a connection between the client and the web server times out or breaks, there is no guarantee that the result is complete.
If a result is completely returned without problems there will be an extra row at the end of the result that reads

 #OK 

If this line is not there, and also no block of text starting with

#ERROR

this indicates that users can not be assured that the result is complete.
Note that this may cause some problems for client code that was built to deal with the old result type. In particular the TOPCAT visualisation tool may have to be upgraded. The TOPCAT programmer has produced a version that handles this correctly, though then it will always give a warning with the old web sites.
• New MyDB-s have been created for all registered users.
They have been notified of ways to retrieve the data from the old web sites.
• FAQ
Pages with frequently asked question have been added. Please feel free to ask us any question that is not yet asked or adequately anseewered there!

2009-03-18: Preparations for Millennium-II Simulation data

2008-04-029: Fix to Delucia2006_sdss2mass table

2007-07-05b: Fixes and updates to the documentation

• On page, the unit of np is M_sun/h, not M_sun.
• The page on spatial indexing has been extended with a more complete description of how to use the spatial "zone" indexes, ix/iy/iz in the various tables.

2007-07-05a: updates to the database

2007-02-05b: Tutorial on Millennium database

2007-02-05a: Mock all-sky SDSS/2MASS catalogues corrected

The Blaizot 2006, all-sky SDSS & 2MASS catalogues in the MPAMocks database have been updated and corrected for a mistake in the parameter file used to create them. The cosmological parameters differed slightly from those used in the Millennium simulation itself ( namely Ω_λ/Ω_m/h = 0.7/0.3/0.7 instead of 0.75/0.25/0.73). This latter mistake leads to a significant change in the mocks (mostly in terms of counts and redshift distribution). Please discard results obtained with the previous versions.

Note also that the unique "ObjID" of galaxies in the new mocks are now assigned in a random way and can be used to select random sub- samples efficiently (e.g. as with the "random" field from the table DeLucia2006a).

2007-01-23b: TOPCAT plug-in available

2007-01-23a: MPAGalaxies and Durham back online

2007-1-13b: New mock catalogues in MPAMocks.

We have added new mock catalogues in the MPAmocks database. This dataset contains 6 all-sky mock catalogues (tables Blaizot_AllSky_RT_x, with x = 1-5 and Blaizot_AllSky_PT_1). They are all limited at an apparent AB magnitude of 18 in the r filter from SDSS, in an attempt to reproduce (with some margin) the SDSS spectroscopic selection. The catalogues include apparent magnitudes in the 8 filters from both SDSS and 2MASS (namely u,g,r,i,z,J,H, and K). For a further description of this data set and some example queries see the documentation.

Note, these catalogues have been corrected for the peculiar velocity errors noted in news item 2007-1-12. This is true both for the line-of-sight velocity and apparend redshift of the observed galaxy, and for the velocities of the underlying galaxy. However, this correction was performed after the catalogues were created. This implies that the K-corrections used in determining the apparent luminosities were performed with the old, wrong values for the line-of-sight velocities. This effect is estimated to be very small, but these catalogues will be replaced with the rigorously correct luminosities as soon as possible.

2007-1-13a: Problems with peculiar velocities for type 2 galaxies corrected

2007-1-12: Problems with peculiar velocities for type 2 galaxies

An error has been found regarding peculiar velocities for type 2 galaxies in the MPAGalaxies.DeLucia2006a. table, and the corresponding table in the millimil database. For these tables, the peculiar velocities (velX, velY and velZ) for galaxies with type=2 should be multiplied by a factor 1/sqrt(1+redshift) to get the correct values. This problem also affects the Kitzbichler catalogues in MPAMocks, though in a less direct manner (apparent redshifts and (very) slightly different k-corrections).

We will update the affected databases, but will need to bring them offline. We will add a news item when this has been fixed, until then it will be simple to correct for this in the DeLucia2006a tables in the indicated manner.

We appologise for this error.

2006-10-31: New: mock catalogues in database.

2006-10-20: Fixes in the database and web site.

2006-09-26: SDSS and 2MASS magnitudes for MPAGalaxies.

2  :  Data sets

The table on this page lists all the databases and tables available to registered users. A short description of their contents and links to pages with more detailed information are provided.

2.1  :  Simulations

This site makes the results of a suite of simulations available to the public. The first of these is the Millennium Simulation, performed by Volker Springel (MPA) using a specially customized version of the GADGET-2 simulation code. See Springel etal (2005). The second is the Millennium-II Simulation, perfomed by Mike Boylan-Kolchin (MPA) using Volker Springel's GADGET-3 code. See Boylan-Kolchin etal (2009). Both are pure dark matter simulations in a periodic cube using 10,077,696,000 simulation particles. The main differences between the two are the size of the box (500 Mpc/h for Millennium, 100 Mpc/h for Millennium-II), the force resolution (Plummer-equivalent softening of 5 kpc/h for Millennium, 1 kpc/h for Millennium-II), and the particle mass (8.6 x 10⁸ M_sun/h for Millennium, 6.9 x 10⁶ M_sun/h for Millennium-II).

A smaller version of the Millennium Run, the milli-Millennium Simulation, is also available on this site. This simulation used the same cosmology and resolution as the Millennium Simulation but in a 62.5 Mpc/h box with 19,683,000 particles. A smaller version of the Millennium-II, the mini-Millennium-II was run as well. It had the box size of the Millennium-II, the mass resolution of the Millennium and used the same initial condiftions Forier modes as Millennium-II.

Peter Thomas has run a WMAP7 version of the Millennium run, which will be referred to as MR7 in much of this web site. Details of this simulation are described in Guo etal (2013) where is is refered to by the name MS-W7. The linear phases used for the MR7 initial conditions are taken from Panphasia - the public multi-scale Gaussian white noise field described in Jenkins 2013. The phases for the MR7 simulation are published in table 6 of Jenkins 2013, where the name MW7 is used.

The parameters for the various simulations are listed in the first table below. Note that we add a "Code" name for each simulation that is used for table names in various places. For example halo merger trees derived form the simulations and stored in the database MPAHalotrees uses these shorthand names. Similarly for galaxy catalogues derived from these in Guo2013a.

The second table contains entries that do not represent real simulations, but are scaled versions of a real simulation indicated by its code in the "Original simulation" column. Using the scaling algorithm from Angulo & White (2010) one can approximate catalogues as they would have been obtained had the simulation been run with a different cosmology.

2.2  :  Semi Analytical Models

The galaxy catalogues that are stored in the Millennium database are the products of two separate, independent semi-analytical algorithms for galaxy formation: L-galaxies at the MPA and GalFormat the ICC Durham. The pages linked to in the list above summarise these algorithms, paying particular attention to the the different definitions of the halo merger trees used as the sekeletoin on which the algorithms work. These differences are reflected in the distinct data models to store them and special care must be taken to make comparisons between them as will be discussed elsewhere.

For more details about the semi-analytical galaxy formation algorithms used for the published catalogues and their results following main references: For L-Galaxies: Croton2006a and DeLucia2006b, For GalForm: Bower2006.

2.2.1  :  SAM at MPA: L-Galaxies

The merger tree structure of halos extracted from the raw simulation results, is used as the skeleton for semi-analytical models (SAMs) of galaxy formation, from which synthetic galaxy catalogues are constructed. Here the SAGF algorithm is described in terms of the actual, file-based, simulation products.

The postprocessing of the Millennium simulation data proceeds in several stages. The first step is the determination of friends-of-friends (FOF) groups, which is done by the simulation code itself. The second step involves running an algorithm that decomposes each FOF group in a set of gravitationally bound (sub)halos and determines physical properties for them. These halos are the actual objects that are then used to construct merging trees.

The merger tree construction itself involves two steps. The first step is a determination of a descendant halo for each halo. The descendant always lies in the future, in >99.9% of the cases in the subsequent output, but in certain situations, an output may also be skipped. The descendant information already uniquely defines the merger tree of the whole simulation. However, in order to simplify further processing, the merger tree is reorganized in a further step such that distinct pieces of the tree (corresponding to merger trees of halos found at the final time) are stored separately, in a form that makes it convenient to walk these trees. These trees also contain all the physical properties of the corresponding halos, such that they form the only input required to run the semi-analytic galaxy formation code on them.

The construction of the galaxies is done by walking the merger tree more or less in a depth-first fashion by calling the function construct_galaxies() recursively. This function forms the backbone of the program. It first constructs the galaxies of all the progentitors of the present halo. These galaxies are then combined (join_galaxies_of_progenitors())and evolved forward in time by the function evolve_galaxies(), such that once the function finishes, we will have obtained the galaxies for the halo that was passed as an argument. All the details of the modelling of the galaxy formation process are contained in the two functions join_galaxies_of_progenitors() and evolve_galaxies(). The walk of the merger tree encapsulated in construct_galaxies() should be pretty generic and essentially independent of any detail of the galaxy formation model itself.

Note again that in all of the above, the word "halo" refers to a generalized concept of `subhalo', i.e. physically a subhalo may represent an embedded substructure within a larger halo, or a `background halo' that hosts substructures itself. When we follow subhalos over time it is not important to know these geometrical differences. All that matters is that each subhalo carries one or several galaxies, and that these galaxies should be associated with the descendant subhalo at the future output. So subhalos can be treated all on the same footing, and its perhaps best to think of them as a generic object, a "halo".

There is however one exception to this. Certain subhalos are given a special meaning, based on a secondary grouping criterion. This grouping makes reference to the two-stage group identification that we do: We first decompose the particle set into FOF groups, and then each FOF group is decomposed into its constituent subhalos. As explained above, the merger trees are built for the total set of subhalos found in this way, without making any reference to the FOF groups. However, in the physical modelling of galaxy formation, we use the FOF information at one place: We assume that only the biggest subhalo in a FOF group receives fresh cool gas dropping out due to radiative cooling.

This basically means that in our merger tree we somehow need to know which halos belong to the same FOF-group, so that we can select the biggest one of them as the "main halo" of the group - this is then the one which will receive the cooling gas.

The above then introduces a complication in the recursive construction of the galaxies. Because the physical model for the cooling rate of gas (needed when the galaxy population of a halo is evolved in time) starts out with an inventory of all the baryons in a given FOF-group, one needs to know the present state of all the galaxies in a given FOF group to compute this... This means that one cannot simply construct the galaxies just based on the merger tree of a given subhalo, because for working out the cooling rates, one may also need information from halos that are only linked to the present tree by a weaker relation of the form that they are part of some of the FOF halos under consideration. One therefore needs to ensure that galaxies of all halos in a given FOF group are already constructed when it comes done to computing the cooling rates.

The above issues are resolved by walking the tree in a slightly more complicated way, and by temporarily storing all the galaxies that have been constructed for a given halo in memory. The recursive walk to construct the galaxies is augmented by the following additional check. After we call construct_galaxies() for all progenitors of a given halo, we check whether all the galaxies have already been constructed for the FOF group. If not, we loop over all the subhalos in the same FOF group and call construct_galaxies() for them if they have not already been processed. That way, we are sure that before join_galaxies_of_progenitors() is called for the present halo, the galaxies for its main halo have already been constructed.

2.2.2  :  SAM at Durham: The Bower et al Model

Halos and Merger Trees

The halo merger trees for the Bower et al model are contained in the DHalo and DSubhalo tables. The merger trees are built using the Friends of Friends (b=0.2) groups output by L-Gadget2 and the subgroups identified by SubFind. In the description below 'subhalo' means a set of particles grouped together by SubFind, which looks for gravitationally bound local density maxima. A 'halo' is a collection of subhalos grouped together in the following way:

Initially subhalos are grouped into halos by looking at the FoF groups - a halo just consists of all the subhalos belonging to one FoF group. Particles in the FoF group which do not belong to any subhalo are discarded. Note that SubFind identifies the background mass distribution of the halo as a separate subhalo, so each halo will normally contain one large subhalo (with most of the mass of the halo) and a set of smaller "satellite" subhalos.

The Friends of Friends algorithm often links together objects which should probably be treated as separate halos as far as the merger trees are concerned. So under certain conditions a subgroup may be split from its parent halo and considered to be a halo in its own right. This is done if 1) the subhalo is outside twice the half mass radius of the parent halo or 2) the subhalo has retained 75% of the mass it had at the last output time where it was an independant halo. This second condition is based on the assumption that a halo which has fallen into a more massive halo will rapidly be stripped of its outer layers, whereas a halo which has been artificially linked to another halo will retain its mass. When a subhalo is split off, any less massive subhalos within twice its half mass radius are also split off and become part of the new halo.

The descendant of a subhalo is found by following the most bound 10% of its mass or the 10 most bound particles, whichever is the greater mass. The descendant is the subhalo which contains the largest number of these particles. The descendant of a halo is the halo which contains the descendant of the most massive subhalo in the halo.

We refer to the halos defined in this way as "DHalo"s to differentiate them from the halos used in the Munich version of the merger trees.

WARNING: there is rather conflicting terminology between the two versions of the merger trees. The bound groups of particles identified by SubFind are refered to as subhalos in the Durham version of the merger trees described here, but are referred to as halos in the Munich version.

Galaxies

The galaxy catalogues are stored in the DGalaxy table. They were produced by using the GALFORM semi-analytic code to populate the N-body halos from the Millennium simulation with galaxies. The galaxy formation model is decsribed in detail in the Bower et al (2006) paper. The properties available for each galaxy include position, velocity, stellar mass and magnitudes in various bands.

Within a single galaxy merger tree, the galaxies in the DGalaxy table are assigned ID numbers in depth first order so that all progenitors of a particular galaxy may be easily located. The ID of the descendant of each galaxy is also provided.

2.3  :  Links

• Virgo project at MPA.
• Virgo UK web site.
• MPA home page.
• GAVO web site.
• GalICS project web site.
  A French website offering amongst other services a similar SQL query service on a simulation result as the current webapp.

2.4  :  References

• Millennium Simulation:
  V. Springel, S. D. M. White, A. Jenkins, C. S. Frenk, N. Yoshida, L. Gao, J. Navarro, R. Thacker, D. Croton, J. Helly, J. A. Peacock, S. Cole, P. Thomas, H. Couchman, A. Evrard, J. Colberg, F. Pearce (2005)
  Simulations of the formation, evolution and clustering of galaxies and quasars
  Nature 435, 629
  


• Millennium-II Simulation:
  M. Boylan-Kolchin, V. Springel, S. D. M. White, A. Jenkins, G. Lemson (2009)
  Resolving cosmic structure formation with the Millennium-II Simulation
  Mon. Not. R. Astron. Soc. 398, 1150

• Bower, R. G., Benson, A. J., Malbon, R., Helly, J. C., Frenk, C. S., Baugh, C. M., Cole, S., Lacey, C. G. (2006),
  Breaking the hierarchy of galaxy formation,
  Mon. Not. R. Astron. Soc., 659
• Croton, D. J., Springel, V., White, S. D. M., De Lucia, G., Frenk, C. S., Gao, L., Jenkins, A., Kauffmann, G., Navarro, J. F., Yoshida, N. (2006b),
  Erratum: The many lives of active galactic nuclei: cooling flows, black holes and the luminosities and colours of galaxies,
  Mon. Not. R. Astron. Soc., 367, 864
• Croton, D. J., Springel, V., White, S. D. M., De Lucia, G., Frenk, C. S., Gao, L., Jenkins, A., Kauffmann, G., Navarro, J. F., Yoshida, N. (2006a),
  The many lives of active galactic nuclei: cooling flows, black holes and the luminosities and colours of galaxies,
  Mon. Not. R. Astron. Soc., 365, 11
• De Lucia, G., Blaizot, J. (2007),
  The hierarchical formation of the brightest cluster galaxies,
  Mon. Not. R. Astron. Soc., 375, 2
• De Lucia, G., Springel, V., White, S. D. M., Croton, D., Kauffmann, G. (2006),
  The formation history of elliptical galaxies,
  Mon. Not. R. Astron. Soc., 366, 499
• Springel, V. (2005),
  The cosmological simulation code GADGET-2,
  Mon. Not. R. Astron. Soc., 364, 1105
• Springel, V., Frenk, C. S., White, S. D. M. (2006),
  The large-scale structure of the Universe,
  Nature, 440, 1137
• Springel, V., White, S. D. M., Tormen, G., Kauffmann, G. (2001),
  Populating a cluster of galaxies - I. Results at z=0,
  Mon. Not. R. Astron. Soc., 328, 726

3  :  Databases

The complexity of the post processing data products of cosmological simulations like the Millennium Run lead one to consider relational database for their storage and analysis. In the following sections we give a summary description of the concepts behind relational databases and in more detail how we stored the Millennium data products.

3.1  :  Relational Database Concepts

The subject of relational databases and their query language SQL is huge and here we will only give a very short summary. For more information see the references below.

Tables and columns

This web site gives access to multiple 'databases'. Each 'database' contains individual data sets that somehow belong together. In a relational database, such data sets are stored in tables (originally called "relations", hence the name). such as the one illustrated in the following figure.

Data about individual objects/items are stored in rows in the table. The information inside of a row is distributed over columns. These columns are defined at the table level, i.e. all rows in a given table have the same columns, though they need not all have values. Columns have a name and a fixed data type. In the documentation of the tables available through the web site, we list all columns with their data type a description and some other parameters. For an example see the description of the DeLucia2006a table in the millimil database in the public Millennium web site here. The definition of a table in terms of its columns is often called the 'schema' of the table.

In the query language SQL described later one can query data from one or more tables. To refer to a table, one must use the name of the table. However the plain name of the table only has meaning in the context of the database that contains it. As the web site gives access to multiple databases one must qualify the name of the table. First one must specify the name of the database. In some cases the database is furthemore subdivided in so called 'schemas', a kind of sub folder, that allows one to have multiple tables of the same name in the same database. So the fully qualified name of the table is

[databasename].[schemaname].[tablename]

[databasename..[tablename]

millimil.dbo.DeLucia2006a

millimil..DeLucia2006a

millimil.dbo.snapshots

millimil..snapshots

Primary keys

In the design of the Millennium database we apply various special purpose algorithms for assigning values to the different primary keys. Some should facilitate querying for tree structures, others give information on the snapshot an object belongs to. Details about the various algorithms can be found here.

Foreign keys

The problem with this is that there is a lot of redundancy. As individual FOF groups can contain multiple subhalos, and individual subhalos can contain multiple galaxies, to store all information in a single table means repeating subhalo information for all galaxies in the same subhalo, and even worse for the FOF group information.

Instead when designing the structure of a relational database one generally aims to "normalize" the data model. Here one stores each individual data set in its own table and creates links between the tables representing the relationships. The following figure shows the normalized database design for the redundant table above:

In relational databases these links are referred to as foreign keys. They consist of one or more columns that identify an element in the target table. That element is generally identified through its primary key.

In the example in the figure above, the Galaxy table has a column, haloId, that acts as the foreign key to the SubHalo table. The value in this column should correspond to a value in the SubHalo's primary key column, also called haloId. In SQL one can now join the two tables for example as follows:

select ...
  from Galaxy g
    join Subhalo sh
      on g.haloId = sh.haloId

select ...
  from Galaxy g
  ,    Subhalo sh
 where g.haloId = sh.haloId

References

• For an online simple introduction to SQL see http://www.w3schools.com/sql/default.asp
• The SDSS SkyServer site offers an introduction to SQL here
• Finally Microsoft has online documentation for SQLServer and its SQL dialect T-SQL here

3.1.1  :  SQL: An Overview

For the user, the most important feature of relational databases is the existence of a high-level query language called SQL (=Sequential Query Language), sometimes pronounced as "sequel". Over time we may describe some features of this language, but as there are many excellent texts online available we rather refer to some of these below.

Overview

SELECT [TOP ...] ...
  FROM ...
 WHERE ...
 [GROUP BY ... ]
 [ORDER BY ...]

SELECT

The SELECT determines the form of the result by indicating precisely which columns are returned, and possibly under what name.

SELECT *
  FROM snapshots

SELECT snapnum, lookbackTime
  FROM snapshots

SELECT snapnum as snapshot
,      redshift as z
  FROM snapshots

FROM

WHERE

GROUP BY

ORDER BY

Common Table Expressions

3.1.2  :  Performance and Indexes

It is a goal of databases to run queries efficiently. But to do so so work is often required, first from the database designer, sometimes also from the user who can aim to write queries in more optimal ways. From the designer's point of view it often is necessary to tune databases in a particular way to assist the database engine in constructing optimal execution plans for a particular query. The main technique here is the definition of appropriate table indexes. We describe this very important concept in some more detail on this page.
Note that this is a field that requires quite some more understanding of database functionality than most first time users will not possess. Whenever you have questions about badly performing queries, please mail us the SQL at we will try to improve it if possible.

An index can be seen as a kind of table that contains a subset of the columns of the main table, ordered in a particular way. Each row in such an index points back to the row in the main table from which it was extracted. If a query contains a constraint on a subset of columns that is contained in such an index, the query engine may first find the rows obeying the constraint in the index and then retrieve the required data form the main table. As the index is ordered one can use fast algorithms to find the requested data, often in "logarithmic time". This is to be compared to a linear scan over a table of O(1 billion) rows, with total size ~400GB which may take minutes.

The user does not need to use the indexes explicitly in her queries. The database "knows" of the definition of the indexes and their relation to the table and will use this knowledge in the compilation of a user query into an execution plan. It is however often useful to know which indexes exist on a table to write a query in such a way that indexes can be used. In our databases a typical example is that users should use the "snapnum" column rather then the "redshift" column that are often both available in the tables. Whenever we created indexes that allow more efficient querying for objects at a certain snapshot, we used the integer snapnum column, rather than the floating-point redshift. (See the discussion on this page for some more details on the relation between the snapnum and redshift columns.)

Indexes are often added to tables while it is up an running, hence a static documentation of them is soon out of date. On the main web page though there is a button labelled "Show Indexes" that can help one find all indexes for a given table. Clicking that button will produce the following query in the query window:

use [database-name] 
select t.name as table_name 
,      ind.name as index_name 
,      col.name as column_name 
,      ic.index_column_id as column_rank 
  from sys.indexes ind 
  ,    sys.index_columns ic 
  ,    sys.columns col 
  ,    sys.tables t 
 where ind.object_id = ic.object_id 
   and ind.index_id = ic.index_id 
   and ind.is_primary_key = 0 
   and ind.is_unique = 0 
   and ind.is_unique_constraint = 0 
   and t.is_ms_shipped = 0 
   and ic.object_id = col.object_id 
   and ic.column_id = col.column_id 
   and ind.object_id = t.object_id 
order by t.name, ind.name, ic.index_column_id 

After substituting the name of a particular database for the [database-name] and clicking "Query (browser)" button a result will appear like the following example below which shows some indexes defined on tables in the Guo2010a database. The table shows first the table on which an index has been defined, then the name of the index. Then the columns in the index shown in the order in which they have been defined in the index, sepcified by the column_rank. This is important. The index is ordered by columns in the order of their definition.

For example the index ix_guo2010a_mr_snapnum_sampling defined on Guo2012a..MR contains columns (snapnum, fofID, centralMvir, type, stellarmass,...) in this order, which implies that it is ordered first by snapnum, than fofID, then centralMvir and so on. This helps when a query has a WHERE_cluase that include constraints on on snapnum and fofId. Now a binary search (for example, DB will use more optimal algorithms) can very quickly find the range of rows that fulfills the constraint. But when the query only constrains stellarMass the whole index will still have to scanned before the apporpriate rows are found. (Though note that the reduced width of the index compared will lead to some improvement compared to scanning the whole table).

It is further important to realize that when a query requests only columns that all appear in the index, this can lead to a further performance improvement. In this case once the DB engine has found the rows obeying the constraints, all the required data can be read off from the rows in the index itself. I.e. it is not necessary to do a lookup in the main table itself. This so called "bookmark lookup" (please ask Google for more details) can kill the performance improvement the use of an index, especially if large amounts of rows obey the constraints.

For this purpose many of our indexes contain more columns than could be useful in a sorting algorithm. For example the centralMvir in the index discussed above, being a floating point value, will rarely have values shared by many rows. Hence its ordering will not produce intervals of constant values that can be further ordered by subsequent values such as the stellarMass. However if one wants to obtain the stellarMass of galaxies in a given snapshot and a given fofGroup, one needs look no further than this index. A bookmark lookup is not required.

NB the index under discussion is itself not optimal. For it contains both fofId and snapnum. But a fofID identifies a single friends-of-friends group which is always contained in a single snapnum. I.e. the snapnum is actually constrained by the fofID. Hence including the snapnum is only of interest if one has NO constraint on fofId.

3.1.3  :  Views, functions etc

Views, functions and stored procedures

3.1.4  :  MyDB

Here we define various concepts that are important when working with a private database and describe various scenarios how the user might use this in the particular implementation provided by GAVO and the Virgo consortium.

The MyDB concept

Context

select ... into ...

select top 1000 haloid, np, x,y,z 
  from millimil..mpahalo
 where snapnum=63
  order by np desc

create table massiveHalos
(
  haloid bigint not null,
  np integer not null,
  x real not null,
  y real not null,
  z real not null
)

insert into massiveHalos
select top 1000 haloid, np, x,y,z 
  from millimil..mpahalo
 where snapnum=63
  order by np desc

select top 1000 haloid, np, x,y,z
  into massiveHalos 
  from millimil..mpahalo
 where snapnum=63
  order by np desc

Views

create view mymmhalo as
select * from millimil..mpahalo

Indexes

create index ix_mympahalo_haloid on mympahalo(haloId)

Deleting objects

drop table mympahalo

drop view mymmhalo

drop index ix_mympahalo_haloid

3.2  :  Millennium Database Design

3.2.1  :  Science Questions

In analogy with the procedure followed in the design of the SDSS database (see http://arxiv.org/abs/cs.DB/0202014 for a description) we have asked associated theoretical astrophysicists for a set of typical questions they would want to be able to "ask" of the system. The goal is that the design of the database and the supporting periferal software should support the translation of these questions into SQL. The original set of questions is the following:

1. Return the galaxies residing in halos of mass between 10^13 and 10^14 solar masses.
2. Return the galaxy content at z=3 of the progenitors of a halo identified at z=0
3. Return all the galaxies within a sphere of radius 3Mpc around a particular halo
4. Return the complete halo merger tree for a halo identified at z=0
5. Find positions and velocities for all galaxies at redshift zero with B-luminosity, colour and bulge-to-disk ratio within given intervals.
6. Find properties of all galaxies in haloes of mass 10**14 at redshift 1 which have had a major merger (mass-ratio < 4:1) since redshift 1.5.
7. Find all the z=3 progenitors of z=0 red ellipticals (i.e. B-V>0.8 B/T > 0.5)
8. Find the descendents at z=1 of all LBG's (i.e. galaxies with SFR>10 Msun/yr) at z=3
9. Make a list of all haloes at z=3 which contain a galaxy of mass >10**9 Msun which is a progenitor of BCG's in z=0 cluster of mass >10**14.5
10. Find all z=3 galaxies which have NO z=0 descendent.
11. Return the complete galaxy merging history for a given z=0 galaxy.
12. Find all the z=2 galaxies which were within 1Mpc of a LBG (i.e. SFR>10Msun/yr) at some previous redshift.

Some of these queries can be answered using basic SQL on a straightforward database schema. For example, demo queries Halo 1 and Galaxy 1 below are examples of questions for properties of galaxies or halos such as question 1 and 6. The more tricky questions are those where the evolution/history of the galaxy or halo formation process is involved or where spatial relations are required. To deal with those more complex questions using not overly involved queries, whilst maintaining acceptable response times we have made some special design choices that will be described in the following two paragraphs.

3.2.2  :  Data Models

The structure of the Millennium database is illustrated in the following diagram

In this figure reactangles stand for tables, directed lines for foreign key relations between tables. The names next to the foreign keys correspond to the prefix for the columns in the table from which the foreignk key is pointing. In generel, the target of the foreign key is the primary key of the target table (indicated by the PK prefix). The dashed boxes around the various tables indicate which tables exist in which database. This model has the names corresponding to the full Millennium Run. The same model is reproduced in the milli-Millennium database, though the MPA tables there have an extra "M" in front of their name.

3.2.3  :  Storing Merger Trees

Many of the science questions that were used in the design of thhe database require retrieval of merger histories for dark matter halos and/or galaxies. A straightforward way to store a tree structure such as the merger trees here, where each halo has at most one descendant, is to create a foreign key from each halo to its descendant. Though this is the most explicit way to store the trees as well as the most efficient in terms of space requirements, the problem is that one requires recursive methods to retrieve information about a complete tree. While some database systems have explicit support for recursive queries, for example DB2, it will still be very inefficient, when the trees are deep, to retrieve them using such queries.

To support efficient retrieval of complete trees rooted in a halo at the final output time, one might¹ add a foreign key to each halo pointing to the final descendant in its tree in addition to the direct descendant. This however does not help us to retrieve trees rooted in halos at other timesteps, which is something we also want to be able to do. And it becomes very expensive to add foreign keys to each descendant at later times for all halos.

Instead we have implemented an alternative model which provides very quick retrieval of complete trees, rooted in any halo, using completely standard SQL. The central idea is to store the nodes of a tree in the order they are visited in a depth-first search, starting with the root of a tree and following the descendant edges in opposite direction. The nodes of the tree (halos or galaxies) get unique integer identifiers that give the rank in the resulting sort added to the identifier of the root. This is illustrated in the following figure:

Every halo gets, in addition to the descendant foreign key, a foreign key pointing to the "last progenitor". The last progenitor is that progenitor that comes last in the sort. It is easy to see that this implies that the tree rooted in a certain node contains precisely those nodes with identifier values BETWEEN (in the SQL sense) the identifier of the root and that of the last progenitor. Query H2 on the query web page is an example of such a query:

select PROG.*
  from millimil..MPAHalo PROG
  ,    millimil..MPAHalo DES
 where DES.haloId = 1
   and PROG.haloId between DES.haloId and DES.lastprogenitorId

To improve the efficiency of these retrievals we define an index on the identifier and order the table on disk acording to this index. This implies that the merger tree rooted in a certain tree node is located on disk directly following the node itself, which ensures very fast retrieval times, requiring only a few consecutive page reads.

Especially in the Millennium-II simulation there are merger trees that have millions of nodes. To ask for the complete merger tree rooted in some node may therefore give very large results. Also, one is often not interested in all progenitors, but in "THE progenitor" of a halo/galaxy at an earlier time step. The merger trees extracted from the simulations often provide each object with one special progenitor, referred to as the "main" or "first" progenitor. Often this is the most massive progenitor, but sometimes the assignment is somewhat more complex². In most merger trees stored in these databases this most important progenitor is indicated by a column named firstProgenitorId. When one follows the main progenitor pointers until the end, one traces the "main branch". The halos on the main branch can be considered as "the progenitor" of a certain halo.

In the following figure main branches are outlined by the dashed lines. To find all the halos on a main branch between a halo and the branche's leaf we give each halo a pointer to the leaf on the branch it is on, indicated by the blue arrow in the figure. The column storing this pointer is generally called mainLeafId. It turns out that to find all main progenitors of a given halo, one needs precisely those halos with id between its haloId and its mainLeafId. This works because when assigning the halo identifiers using the depth first ordering described above, we have made sure that we always first follow the pointer to the main progenitor, before recursing to the other progenitors.

This feature is not yet implemented for the millimil merger trees. But with a little rewriting, the following query will give us all the main progenitors of the halo with id=1, including the halo itself:

select PROG.*
  from MPAHaloTrees..MR PROG
  ,    MPAHaloTrees..MR DES
 where DES.haloId = 1
   and PROG.haloId between DES.haloId and DES.mainLeafId

select des.haloid, min(PROG.lastProgenitorId) as mainLeafId
  from millimil..MPAHalo PROG
  ,    millimil..MPAHalo DES
 where DES.haloId = 1
   and PROG.haloId between DES.haloId and DES.mainLeafId
 group by des.haloid

Some more details of merger trees can be found in this page.

3.2.3.1  :  More about tree structures in database

A feature that may be useful deals with the status of halos as center or satellite in a FOF group. In the abundance matching approach to predict the distribution of galaxies¹, one rank orders halos in mass. One then selects galaxies randomly from a given empirical galaxy mass function, rank orders those and assigns them to the halo of corresponding rank. It turns out that when applying this to subhalos, the instantaneous mass of the halos is not the optimal one to use in the ordering. This is because in the simulations generally satellite subhalos get stripped of their mass once they enter another FOF group. It is deemed better to use the mass of the halos just before it became a satellite, or possibly the highest massthe halo ever had.

The process is illustrated in the following figures. Red ellipses indicate FOF groups, red filled circles the central subhalos, black open circles satellite halos. The green arrows in the first figure point from each subhalo to the last time a subhalo on the main branch was a central subhalo. For central subhalos this obviously is a pointer to itself. In the tables this is generally stored in a column lastCentralID. Some properties of this "last central progenitor" such as its vmax or mass are also stored.

In the second picture (where the size of the circles indicates the mass of the subhalo) the green arrow points to the most massive central progenito. This is not necessarily the last time a halo was a central subhalo, maybe mass has been stripped in an earlier fly-by. Also for this we have added an explicit pointer in the database, named peakMassId.

3.2.4  :  Identifiers

All tables have columns that contains a unique identifier for the objects stored in the table. For example MPAHaloTrees..MHalo has a haloId column, DGalaxies..Bower2006a has a GalaxyID column and MillenniumII..FOF a fofId. These columns are also the "primary key" of the tables, which for our database means that the rows in the tables are sorted on this column's values. Pointers into the table, or "foreign keys" as they are called in relational database parlance, in general use the primary key to identify the referenced object. Thus for example the MillenniumII..SubHalo table has a fofId column that is used to identify the FOF group stored in the MillenniumII..FOF table that the subhalo belongs to.

For purposes of identification alone we might have used arbitrary algorithms to allocate values to these primary key columns as long as the resulting values were unique in the table. However we have added extra structure on most of these column that allow for certain interesting queries to be phrased in terms of these values alone, which, combined with the ordering of the columns, often can greatly speed up their execution. The case of the primary key columns on the tables containing merger trees has been described already elsewhere. Here we describe this and other algorithms together with ways to use these.

MField

MField..FOF

fofId = 1012*snapnum+108*<file-index>+<rank-in-file>

select *
  from MField..FOF
 where fofId between 50e12 and 51e12-1

select *
  from MField..FOF
 where snapnum=50

select *
  from MField..FOF
 where fofId between 50e12+:FILENR*1e8 and 50e12+(:FILENR+:STEP)*1e8-1 

MField..FOFSubHalo

 
subhaloId = 1012*snapnum+108*<file-index>+<rank-in-file>

MField..MField

MPAHaloTrees..MHalo

haloId = 1012*<file-index>+106*<tree-index>+<depth-first-index>

select *
  from MPAHalotrees..MHalo
 where haloId between :FILENR*1e12 and (:FILENR+:STEP)*1e12-1 
   and ...

MPAGalaxies

galaxyId = 1012*<file-index>+106*<tree-index>+<depth-first-index>

DHaloTrees..DHalo

DHaloID = 1012*<file-index>+106*<tree-index>+<depth-first-index>

DGalaxies..Bower2006a

GalaxyID = 1014*lt&;file-index> + 108*<tree-index>+<depth-first-index>

MillenniumII

FOF

fofId = 1010*snapnum+106*<file-index>+<rank-in-file>

SubHalo

subhaloFileId = 1010*snapnum+106*<file-index>+<rank-in-file>

subhaloId = 106 * fofID + <rank in FOF group> 

          = 1016*snapnum+1012*<file-index>+106<fof-rank-in-file> + <rank-in-FOF-group>

select *
  from MillenniumII..SubHalo
 where subhaloId between 670020005352*1000000 and (670020005352+1)*1000000-1

HaloTree

haloId = 1015*<file-index>+109*<tree-index>+<depth-first-index>

3.2.5  :  Spatial Indexes

A class of queries that are of interest and that are not trivial to treat efficiently with standard relational database design are those that aim to find objects that are spatially close to each other. A typical question is:

Find all halos within a distance of :R> Mpc from a given position (:X,:Y,:Z)

SELECT *
  FROM MPAHaloTrees..MHalo H
 WHERE SQRT((H.X-:X)*(H.X-:X)+(H.Y-:Y)*(H.Y-:Y)+(H.Z-:Z)*(H.Z-:Z)) <= :R

There are some techniques though that can speed up these queries. These require special data structures tht map better form 3D to 1D. We have incorporated a number of these in the Millennium database and describe these now.

Zone index

SELECT *
  FROM MPAHaloTrees..MHalo H
 WHERE SNAPNUM = 63
   AND X between 10 and 20
   AND Y BETWEEN 20 AND 30
   AND Z BETWEEN 220 AND 230

This implies that all objects with the same values for ix, iy and iz will be in the same cube of size L. An index is created on these columns which now implies that queries that include these columns explicitly can in general be executed much more quickly. The index contains the following columns in order.

snapsnum, ix,iy,iz,x,y,z,haloid/galaxyid 

SELECT *
  FROM MPAHaloTrees..MHalo H
 WHERE SNAPNUM = 63
   AND IX = 1
   AND IY = 2
   AND IZ = 22

SELECT *
  INTO MYSUBVOLUME_IDS
  FROM MPAHaloTrees..MHalo H
 WHERE SNAPNUM = 63
   AND IX = 1
   AND IY = 2
   AND IZ = 22

create clustered index ix_MYSUBVOLUME_IDS on MYSUBVOLUME_IDS(haloID)

select h.*
 from (
  select top 50000 haloid 
    from MYSUBVOLUME_IDS
    order by haloid ) mt
  ,    mpahalotrees..mhalo h
 where mt.haloid = h.haloid

select h.*
 from (
  select top 50000 haloid 
    from MYSUBVOLUME_IDS
   where haloid > :LASTHALOID
    order by haloid ) mt
  ,    mpahalotrees..mhalo h
 where mt.haloid = h.haloid

phKey

phkey

For this purpose the simulation volume of the Millennium run has been subdivided in 256³ grid cells and to each cell a value has been assigned corresponding to the step number along such a curve.

---

References

For an interesting general discussion of this problem and various approaches solutions see the PhD thesis by Volker Markl and references therein.

A very general and comprehensive reference to spatial indexes is the following book.
Hana Samet, 2006, Foundations of Multidimentsional and Metric Data Structures
Morgan Kaufman, ISBN 13:978-0-12-369446-1

3.2.6  :  Random Sampling

For many science questions it is strictly speaking not necessary to use the full Millennium database. And for performance reasons it would often be preferrable to use a smaller dataset that is in some way a good representation of the complete set. The milli-Millennium could be used for these purposes, however the problem with it is that it misses the long wavelength modes of the initial perturbation field and does therefore not correspond to a random volume of the same size from the full Millennium. It's use is mainly in the opportunity it offers to test SQL queries that will work on the full Millennium database, while giving much faster return times.

To offer an easy option of drawing proper random samples from the Millennium run we have added a column to some of the datasets which contains an integer random number generated using the java.lang.Math.random() function. For the Millennium datasets the number ranges between 0 and 1000000, for the milli-Millennium data between 0 and 1000. For example, to choose a roughly "1 in 1000" sample of all galaxies at the final snaphot one may choose to have all galaxies with this random number in the interval [0,1000]. Note that this does not directly offer the an option to have a random sample of a prescribed size, but as the example below shows it will be not hard to do so either. Currently (2006-08-01) this column is only added to MPAGalaxies..MGalaxy and millimil..MMGalaxy. The following queries show how one might use this feature.

The first takes a random sample that returns roughly 1% of all the galaxies from the MPA Millennium galaxy catalogue at the final snapshot. This should roughly produce 200000 galaxies:

select *
  from mpagalaxies..mgalaxy
 where snapnum=63
   and random between 0 and 10000

The following query picks a random sample of exactly 1000 entries:

select top 1000 *
  from mpagalaxies..mgalaxy
 where snapnum=63
   and random between 0 and 100

3.3  :  Millennium Databases

The links above lead to pages giving more details about the databases that have been made available top the public through this web site.

3.3.1  :  millimil

This database is a small version of the full Millennium run results. It contains versions of all the tables also found in the main database, both the MPA and the Durham results.

3.3.1.1  :  Snapshots

This table stores some housekeeping information of the milli-Millennium simulation. In particular, it links redshifts and lookback times to the integer index of the snapshot. Almost all other tables in the millimil database have a snapnum column that corresponds to the one in this table.

3.3.1.2  :  MMField

This table stored the dark matter density field put on a 32³ density grid. It stores both simple count in cells and also the values of the density field smoothed with a Gaussian kernel of various sizes.

3.3.1.3  :  FOF

The table stores all the friend-of-friends (FOF) groups extracted from the raw output from the milli-Millennium simulation. These FOF groups form the basis that the SUBFIND algorithm uses to detect the subhalos.

3.3.1.4  :  FofSubHalo

The table stores all the subhalos identified with the SUBFIND algorithm in the friends-of-friends groups that are stored in the FOF table in this same database.

3.3.1.5  :  MPAHalo

The table stores all the halos from the milli-Millennium simulation in a representation that allows efficient querying for merger histories. For description how these merger trees were constructed see Springel2005a and DeLucia2006b.

3.3.1.6  :  DeLucia2006a

This table contains the result of an L-Galaxies run on the milli-Millennium merger trees with the same parameters as used in the full Millennium run described in De Lucia and Blaizot 2006.
Note that galaxies without stars have been assigned the value 99 for all their magnitudes.

3.3.1.7  :  DeLucia2006a_SDSS2MASS

This table contains SDSS and 2MASS observer frame magnitudes for the galaxy catalogue in the DeLucia2006a table in this same database.

3.3.1.8  :  DHalo

This table contains the catalogue of halos used to construct the merger trees used in the Bower et al (2006) galaxy formation model. Each DHalo is a collection of SubFind subhalos grouped together to make a halo. Note that the objects referred to as subhalos here (SubFind groups) are equivalent to the objects listed in the MHalo table.

3.3.1.9  :  DSubHalo

The DSubhalo table specifies the parent DHalo for each subhalo. It may be used to determine which subhalos belong to each DHalo. The subhaloID column corresponds to the subHaloID column in the MPAHalo and the FOFSubhhalo tables in the millimil database, so additional information about a subhalo may be obtained by joining to these tables.

3.3.1.10  :  Bower2006a

This table contains the semi-analytic galaxy catalogues of Bower et al (2006). All magnitudes include dust extinction and assume that H0 = 100km/sec/Mpc.

3.3.1.11  :  Guo2010a

The table Guo2010a stores the results of running the L-Galaxies code version described in Guo etal (2010) on the halo merger trees in the milli-Millennium simulation. This table is a copy of the table mMR in the database Guo2010a, available for registered users only.

3.3.2  :  Snapshots

Most tables available through this web site have an integer "snapnum" column. This column indexes the output snapshots from the different simulations described here. Each snapshot corresponds to a different redshift. And though the redshifts are generally also stored in the catalogues, it is in general MUCH more efficient to base queries for specific snapshots on the snapnum column. When indexes have been defined for speeding up requests for particular snapshots, almost always the snapnum column has been used.

The relation between snapnum and redshift varies between simulations, and also between original versions of the catalogues and their counterparts scaled to different cosmologies. This database contains the following tables describing these relations:

• MR: snapshots from original Millennium and milli-Millennium simulations. Equivalent to millimil..Snapshots and MField..Snapshots
• MRII: snapshots from original Millennium-II and mini-Millennium-II simulations. Equivalent to MillenniumII..Snapshots
• MR7: snapshots from WMAP7-Millennium simulation
• MRscWMAP7: snapshots from Millennium scaled to WMAP7 cosmology. NOTE the last snapshot (63) corresponds to negative redshift, i.e. is in the future! Redshift 0 corresponds to snapshot 53.
• MRIIscWMAP7: snapshots from Millennium-II scaled to WMAP7 cosmology. NOTE the last snapshot (67) corresponds to negative redshift, i.e. is in the future! Redshift 0 corresponds to snapshot 57.

To find the relation between redshift and snapnum simply run a query like the following:

select *
  from snapshots..MRscWMAP7

3.3.3  :  MMSnapshots

This database contains tables with the original dark matter particles from the milli-Millennium simulation. There is also a table linking the particles to particular FOF groups and/or sub-halos they belong to.

This database also contains a number of so called User Defined Functions (UDF-s) that can be used to efficiently create spatial subsets of the particle distribution. To call a function you should add the "dbo." schema name in front of the function name. And the databas ename itself should also be included.

• select *
    from mmsnapshots.dbo.mmsnapshotpointsinbox(snapnum,xmin,ymin,zmin,xmax,ymax,zmax)
  

returns a table with particles [x,y,z,vx,vy,vz,id] in the box given by the xmin..zmax parameters at the given snapnum. (Of course numerical values should be substituted for the parameters). This function takes periodicity of the simulation box into account. I.e. the box may cross the boundary of the box.

• select *
    from mmsnapshots.dbo.mmsnapshotpointsinsphere(snapnum,x,y,z,radius)
  

returns a table with particles [r,x,y,z,vx,vy,vz,id] in the sphere given by the center and radius at the given snapnum. This function takes periodicity of the simulation box into account. I.e. the sphere may cross the boundary of the box. The r column gives the distance to the center of the sphere.

3.3.3.1  :  MillimilSnapshots

This table contains the dark-matter particles of the milli-Millennium simulation. Ordered by Snapnum and phKey. This is used in a number of functions that can query the particles in subvolumes (sphere and boxes) of the simulation volume.

3.3.3.2  :  MillimilSnapshotIDs

This table allows one to find the particles belonging to a given FOF group or sub-halo.

3.3.4  :  MField

The MField database stores the dark matter density fluctuation field smoothed with various smoothing radii (in the MField table) and information about the snapshots of the simulation. The goal of the MField table is to give a simplistic way to find out information about the environment of an object. To this end each grid cell is identified by its Penao-Hilbert key. This same key has been assigned to all objects (halos and galaxies), in that case identifying the cell they reside in. For a given object the environment is now a simple lookup of the corresponding cell in the density field table.

3.3.4.1  :  Snapshots

3.3.4.2  :  MField

3.3.4.3  :  FOF

The table stores all the friend-of-friends (FOF) groups extracted from the raw output from Millennium simulation. These FOF groups form the basis that the SUBFIND algorithm uses to detect the subhalos.

3.3.4.4  :  FofSubHalo

The table stores all the subhalos identified with the SUBFIND algorithm in the friends-of-friends groups that are stored in the FOF table in this same database.

3.3.4.5  :  Spatial

This table stores coordinates and indexes for cells in the MField table. It allows one to translate between the Peano-HIlbert index (phkey) of a cell and its discrete cartesian coordinates (ix,iy,iz).

select s2.phkey
  from mfield..spatial s1
  ,    mfield..spatial s2
 where s1.phkey=123456
   and s1.ix between 0 and 255
   and s1.iy between 0 and 255
   and s1.iz between 0 and 255
   and s2.ix between s1.ix-1 and s1.ix+1
   and s2.iy between s1.iy-1 and s1.iy+1
   and s2.iz between s1.iz-1 and s1.iz+1

3.3.5  :  MPAHaloTrees

This database contains the results of the merger tree determination ala MPA. See here for details of this algorithm. For details about the storage of these trees in the database see this page.

3.3.5.1  :  Tables

The database MPAHalotrees contains various tables storing sub-halo merging trees in a representation that allows efficient querying for merger histories. For a description how these merger trees were constructed see Springel2005a and DeLucia2006b. For a description of how the merger trees are stored in these tables to allow efficient querying of their structure and example queries, see the discussion on this page.

There are currently 5 tables stored in this database, all of them with the same structure documented in the table below.
The tables MR, MRII, MR7 store the subhalos extracted from the Millennium, Millennium-II and Millennium-WMAP7 simulations. (See here for the description of the simulations).
The tables MRscWMAP7 and MRIIscWMAP7 contain the merger trees from MR and MRII respectively, but scaled to WMAP 7 cosmology according to the algorithm presented in Angulo & White (2010).

Note, before the update to the database from 2013-02-26, the table currently named MR was represented by a table named MHalo. This table is not exactly the same as the current version, but for backwards compatibility we make MR available also as MHalo. Similarly, for the time being we left the original version of the Millennium-II merger trees in the table MillenniumII..HaloTree.

For queries aiming to retrieve halos at a particular snapshot one should use the snapnum column to select the precise snapshot. That column is always used in table indexes Note that the relation between the snapnum and redshift values varies between the different tables. See the tables in the Snapshots database for tables with the different snaphot lists for the precise relations.

3.3.6  :  MPAGalaxies

This database stores the results of different L-Galaxies semi-analytical galaxy formation runs on the Millenium simulation's MPA halo merger trees.

3.3.6.1  :  DeLucia2006a

This table contains the full result of the L-Galaxies run used in De Lucia and Blaizot 2006.
Note that galaxies without stars have been assigned the value 99 for all their magnitudes.

3.3.6.2  :  DeLucia2006a_SDSS2MASS

This table contains SDSS and 2MASS observer frame magnitudes for the galaxy catalogue in the DeLucia2006a table in this same database.

3.3.6.3  :  Bertone2007a

This table contains the full result of the L-Galaxies run used in Bertone, De Lucia and Thomas 2007 (ADS).
Note that galaxies without stars have been assigned the value 99 for all their magnitudes.
Wind properties are assigned a value of zero when a galaxy is not blowing a wind (Bubble=0).

3.3.7  :  DGalaxies

This database contains galaxy catalogues created in Durham.

3.3.7.1  :  Bower2006a

This table contains the semi-analytic galaxy catalogues of Bower et al (2006). All magnitudes include dust extinction and assume that H0 = 100km/sec/Mpc.

3.3.8  :  DHaloTrees

This database contains the halo and subhalo catalogues used in the Durham model for galaxy fomation.

3.3.8.1  :  DHalo

This table contains the catalogue of halos used to construct the merger trees used in the Bower et al (2006) galaxy formation model. Each DHalo is a collection of SubFind subhalos grouped together to make a halo. Note that the objects referred to as subhalos here (SubFind groups) are equivalent to the objects listed in the MHalo table.

3.3.8.2  :  DSubHalo

The DSubhalo table specifies the parent DHalo for each subhalo. It may be used to determine which subhalos belong to each DHalo. The SubhaloID column corresponds to the subHaloID column in the MFiels..FOFSubhalo table, and to the subhaloId column in the MPAHalotrees..MHalo table. So additional information about a subhalo may be obtained by joining to these tables.

3.3.9  :  MPAMocks

This database contains mock galaxy catalogues created by "virtual observations" of the semi-analytical galaxy catalogues in the various other databases. These (will) range from small pencilbeams to slices to full mock-SDSS catalogues. The different algorithms used are documented in the links above.

3.3.9.1  :  Kitzbichler2006abcdef

Kitzbichler & White (2007)

This dataset contains six pencil beam mock catalogues of a deep field of 1.4 times 1.4 square degrees. These catalogues were created from the galaxy catalogue in MPAGalaxies.DeLucia2006a. For a description of the "virtual observation" algorithm see Kitzbichler M. & White S. D. M. 2007. If you use the data from this dataset, please cite this paper, as well as the relevant papers mentioned in the general credits page, Springel V. et al. 2005 Nature 435, 629 and De Lucia G. & Blaizot J. 2006 astro-ph/0606519.

The data set is distributed over two times 6 tables. Containing Johnson magnitudes plus some physical information and SDSS magnitudes respectively. There are furthermore 6 views defined that merge the observational properties of these tables together. The following are some example queries:

select floor(redshift_obs/.1)*.1 as z,count(*) as n 
  from mpamocks..kitzbichler2006a_johnson    
 where k_j < 22 
 group by floor(redshift_obs/.1)*.1 
 order by z

select floor(b_j/.1)*.1 as mag_B,count(*) as n 
  from mpamocks..kitzbichler2006a_johnson
 group by floor(b_j/.1)*.1 
 order by mag_B

3.3.9.1.1  :  Kitzbichler2006abcdef_Johnson

The tables named Kitzbichler2006a_Johnson - Kitzbichler2006f_Johnson contain positional information, some physical properties and various Johnson magniudes for the Kitzbichler and White (2006) datasets. All have the following schema:

3.3.9.1.2  :  Kitzbichler2006abcdef_SDSS

The tables named Kitzbichler2006a_SDSS - Kitzbichler2006f_SDSS contain SDSS magniudes for the Kitzbichler and White (2006) datasets. These tables complement the corresponding tables with Johnson magnitudes (Kitzbichler2006a_Johnson - Kitzbichler2006f_Johnson) in this same database. They are linked to these by the common objectId column. All have the following schema:

3.3.9.1.3  :  Kitzbichler2006abcdef_Obs

These six views named Kitzbichler2006a_Obs - Kitzbichler2006f_Obs combine the observables from the Kitzbichler & White (2006) Johnson and SDSS tables. The sql creating these views was

 create view Kitzbichler2006a_Obs as 
  select j.objectId, j.galaxyId, j.ra,j.dec, j.redshift_obs,j.cos_inc
  ,      j.M_B_stellar/j.M_stellar  as bulge2total
  ,      j.B_J,j.V_J,j.R_J,j.I_J,j.K_J,s.g_SDSS,s.r_SDSS,s.i_SDSS,s.z_SDSS 
    from MPAMocks..Kitzbichler2006a_Johnson j
    ,    MPAMocks..Kitzbichler2006a_SDSS s 
   where j.objectId = s.objectId
 

All have the following schema:

3.3.9.2  :  Blaizot2006_AllSky

This dataset contains 6 all-sky mock catalogues (tables Blaizot_AllSky_RT_x, with x = 1-5 and Blaizot_AllSky_PT_1). They are all limited at an apparent AB magnitude of 18 in the r filter from SDSS, in an attempt to reproduce (with some margin) the SDSS spectroscopic selection. The catalogues include apparent magnitudes in the 8 filters from both SDSS and 2MASS (namely u,g,r,i,z,J,H, and K).

These light-cones were made using the MoMaF code (see Blaizot et al. 2005, MNRAS 360, 159) and the semi-analytic model presented in De Lucia & Blaizot 2006 (astro-ph/0606519), the results of which are stored in the MPAGalaxies..DeLucia2006a table.

Light-cones 'Blaizot_AllSky_RT_1' to 'Blaizot_AllSky_RT_5' use the random tiling technique described in Blaizot et al. (2005), and each of these mock catalogues was generated with a different seed for the random number generator. In practice, the main effect of this here is that the "observer" sits in different parts of the simulation volume for each light-cone, thus allowing estimates of cosmic variance in the very local universe.

The light-cone 'Blaizot_AllSky_PT_1' preserves the periodicity of the density field (i.e. it does not use *random* tiling). The price to pay is that replications do introduce spatial correlations on very large scales. The gain is that the density field is indeed almost continuous across the whole light-cone volume.

The tables contain not only the "observable" information for the mock catalogues, but also all the attributes(columns) of the underlying galaxy obtained from DeLucia2006a. This information could in principle be obtained using a join on the galaxyId column, however for large subsets this can easily cause a degradation in query speed to beyond the current timeout limits. Hence we have added this information for you already.

Credits : on top of the normal credits for using the Millennium database (see here), please also refer to the MoMaF paper (Blaizot et al. 2005, MNRAS 360, 159) if you use these mock catalogues for publication.

Example queries :

redshift distribution

select floor(cosmo_redshift*500)/500 as z 
,      count(*) as n 
  from mpamocks..blaizot2006_allsky_rt_1 
 group by floor(cosmo_redshift*500)/500 
 order by z 

mass distribution of a random subsample

select floor(log10(stellarmass)*10)/10 as lmstar
,      count(*) as n
  from mpamocks..Blaizot2006_AllSky_RT_1
group by floor(log10(stellarmass)*10)/10
  order by lmstar