\documentclass[twoside]{article}[12pt]

% include packages; if you don't like the big default margins, 
% then set the geometry BEFORE including the votutorial-style
\usepackage[a4paper, top=2cm, bottom=2cm, left=2cm, right=2cm]{geometry}

%\usepackage{latex-votut/votutorial}
\usepackage[gavo]{latex-votut/votutorial}
%\usepackage[eurovo]{latex-votut/votutorial}
%\usepackage[ivoa]{latex-votut/votutorial}
%\usepackage[customlogo]{latex-votut/votutorial}
\usepackage[utf8]{inputenc}

% for syntax highlighting:
\usepackage{listings}
\usepackage{textcomp}

% more tool-commands:
\newcommand{\CosmoSim}{%
\link{CosmoSim}{http://www.cosmosim.org/}%
}

% image directory for screenshots, further icons:
\def\dir{images}

% overwrite some defaults:
% -- icons
\renewcommand{\icon}[1]{%
  % parameter: file name
  \raisebox{-1ex}{\includegraphics[scale=\iconscale]{\icondir/#1}}%
}

\fancyhead[LE,RO]{\leftmark}


% -- styles
\renewcommand{\menu}[1]{{\color{darkblue}  \tt $\,$#1$\,$}}
%\renewcommand{\url}[1]{ \href{#1}{#1} } --- that's the default anyway
%\newcommand{\menu}[1]{\textcolor{darkgreen}{ \sffamily{#1}}}


% -- adjust listings style for syntax highlighting
\definecolor{sqlkey}{HTML}{770088}
\definecolor{sqlnum}{HTML}{116644}
\definecolor{sqltable}{HTML}{0055aa}
\definecolor{sqlstring}{HTML}{aa1111}
\definecolor{sqlcomment}{HTML}{aa5500}

\lstdefinestyle{daiquiri}{%
   	frame=single,%
  	rulecolor=\color{sqlborder},%
   	backgroundcolor=\color{sqlbg},%
   	%aboveskip=0pt,%
   	%belowskip=0pt,%
	basewidth=0.5em,%
	lineskip=-0.01pt,%
   	showspaces=false,
   	showtabs=false,
   	upquote=true,
	basicstyle={\normalsize \tt \color{darkgrey}},%
	keywordstyle=\color{sqlkey},% for language specific keywords
	keywordstyle={[2]\color{sqltable}},% for table and column names, manually enclose with ` in LaTeX-source
	keywordstyle={[3]\color{sqlnum}},% for numbers in LaTeX-source
	stringstyle=\color{sqlstring},
	commentstyle=\color{sqlcomment},
}

\lstdefinelanguage{SQL}{%
	morekeywords={SELECT, FROM, WHERE, ORDER, BY, LIMIT, TOP, LIKE, AS, COUNT, GROUP BY, FLOOR, AND, OR, BETWEEN},%
	morekeywords={[3],1,2,3,4,5,6,7,8,9,0},% ... not working, coloring numbers is not supported ...
	sensitive=false,%
	breaklines=true, % wrap lines, if not fitting
	numbers=none, % no line numbers
	morecomment=[l]{--},%
	morestring=[s]{'}{'},%
	moredelim=[is][keywordstyle2]{`}{`},%
	%moredelim=*[s][\itshape]{/*}{*/}
}


\lstset{style=daiquiri, language=SQL}



% maketitle options
\title{Introduction to simulation databases\\ using CosmoSim}
\author{Kristin Riebe\thanks{Leibniz-Insitute for Astrophysics Potsdam (AIP); email: kriebe@aip.de}, GAVO}
\date{June 27, 2016}
\titlepicture{\dir/titlepicture}

\def\abstractname{Introduction}

\begin{document}
\maketitle

%{\color{red} \logooptions} 

\begin{abstract}
Simulation databases like the Millennium Database
%\footnote{\url{http://gavo.mpa-garching.mpg.de/Millennium/}}
or CosmoSim
%\footnote{\url{https://www.cosmosim.org}} 
contain data sets from cosmological simulations. Each user has access to
catalogues of dark matter halos and their history (merger trees), supplemented by (mock)
galaxy catalogues and information about the density field or tables with
the simulation particles themselves and other data sets. Access to these 
data is possible via a web interface directly with the
browser; for larger data it is more convenient to access the data via
\Topcat, wget or using their TAP/UWS interface, if available.
This tutorial gives a quick overview on the CosmoSim web
interface and shows an example use case for extracting the mass function of a simulation, particles for a halo and a halo's merger tree.
More demos, examples and tutorials can be found at the \CosmoSim\ webpage.
\end{abstract}

\usedsoftware{\CosmoSim\ web interface, \Topcat}

\section{The web interface}
%This section shows how to make queries with the \CosmoSim\ web interface.
\begin{enumerate}
    \item Open \url{https://www.cosmosim.org} in a web browser. This webpage provides access to simulations from different projects, with data sets like dark
matter halo catalogues and merger trees. The available simulations and data are described in the \key{Simulations} section of the documentation. 
There are also many documentation pages on the database and table structures, example queries and a demo video\footnote{\url{https://www.cosmosim.org/cms/documentation/demos-and-tutorials/first-steps-with-cosmosim/}}. 
You don't need to look at this now, just be aware that there is extensive documentation (and it may even be more up-to-date than this tutorial).

\pagebreak[2]

    \item \textbf{Registration:} 
    	\begin{itemize}
    	    	\item For gaining access to all simulations and full functionality
    	    	 of the web application, it is required to register at
\url{https://www.cosmosim.org/auth/registration/register} beforehand.
The registration is free and open to everyone. (Some data is public for guests as well, but functionality is limited.)
		\item During tutorials and hands-on sessions a demo user account may be enabled
for testing purposes. Please ask your tutors for the current demo user's credentials.
    	\end{itemize}


    \item \textbf{Query interface:}
    	\begin{itemize}

		\item Login with your username and password (or as the demo user/guest) and navigate to the \key{Query interface} (\url{https://www.cosmosim.org/query}).\\

    \begin{minipage}[c]{0.45\linewidth}   

	    %\centerline{\includegraphics[width=80mm]{\dir/Screenshot-CosmoSim-QueryForm}}

	    \item SQL queries are written directly into the main text area. The dialect used here is MySQL.
	     
	    Write the following text into the form:	    
   		\begin{lstlisting}
SELECT * FROM `MDR1`.`FOF`
WHERE snapnum=85
ORDER BY mass DESC LIMIT 100
		\end{lstlisting}
		This query will retrieve the 100 most massive clusters (ordered by
mass) for redshift 0 (= snapshot number 85) from the MDR1-simulation.


    \end{minipage}
    \hfill
    \begin{minipage}[c]{0.5\linewidth}
        \includegraphics[width=\linewidth]{\dir/Screenshot-CosmoSim-QueryForm}
    \end{minipage}

		\item Enter a new \key{name} for the result table below the SQL area. This name must be unique, if you leave the field empty, 
		the web application will generate a unique name for you. 
		
		\item Select \key{Short queue} and click on \key{Submit new SQL query}.
		
		\item Each query returns results in a (private) result table, the names of these tables are listed in the \key{Jobs list} on the left side. Click on your job to see its details.
		
		\item Once your job is finished (check-mark), click the job name again, then choose the tab \key{Results Table}. Browse the table to view the properties of the most massive halos.
		\item Above the form on the right side is the \key{Examples} link. Click on it to view the available example queries that you may also wish to try for yourself. Just double-click on one of them and the corresponding SQL query will be inserted into the SQL form.
	    
	    \item Browse the available simulations and tables by clicking at \key{Database Browser} on the left side above the query form. 

	    \item If you have long running queries, be sure to select \key{Long queue} before submitting, since the short queue times out after 30 seconds. 
	    It is also wise to restrict your query to use only the columns you are really interested in. This saves query time and disk space. 

	\item In principle, a \key{basic SQL query} consists of the following parts:\\
	\begin{tabular}{ll}
	\sql{SELECT} & followed by a list of columns, e.g. \sql{x,Rvir,npart} or \sql{*} for all\\
	\sql{FROM} & followed by a list of tables (possible join)\\
	\sql{Bolshoi.BDMV} & database and table name\\
	\sql{[WHERE]} & followed by a list of restrictions (filter), e.g. snapnum=85\\
	\sql{[ORDER]} & followed by a (list of) columns, e.g. BY MASS DESC\\
	\sql{[LIMIT]} & followed by a number to restrict the number of returned rows
	\end{tabular}

    \end{itemize}

    \item Consult the \key{documentation} (Database Structure \goto\ Tables) or the database browser above the form to get to know which table provides which data.

\end{enumerate}

\pagebreak[3]
% === Mass function ===
\section{Mass function for FOF groups}
In this chapter we plot the mass function for dark matter clusters of a simulation and learn about the \key{SAMP} interface to plot the results with \key{Topcat}.
\begin{enumerate}
    \item Pick a simulation and redshift
    \begin{itemize}
        \item Open \url{https://www.cosmosim.org} in a web browser. Login and go to the \key{Query} form. 
        \item Choose a simulation from the \key{Database Browser} above the query form, e.g. the \entry{MDR1} simulation.
        \item Halo tables contain the column \sql{snapnum}, which can be mapped to redshift via the \sql{Redshifts}-table of the corresponding simulation database. For getting the snapnum for redshift $z=0$, put the following query into the query form:
        \begin{lstlisting}
SELECT DISTINCT zred, snapnum FROM `MDR1`.`Redshifts`
WHERE zred<0.01
ORDER BY zred
        \end{lstlisting}
        \item Give the result table a \key{name}, e.g. \entry{mdr1-redshifts}, and \key{Submit} your query.
        \item Once the query is finished (check-mark symbol in the job list), click on the result name in the \key{job list} and choose the \key{Result Table} tab. The snapshot number (\sql{snapnum}) for $z=0$ is 85 for this simulation. This value is needed in the next step. 
    \end{itemize}
    
    \item Get the mass function
    \begin{itemize}
        \item A mass function gives the number of halos for a selected mass bin. To get this, click the \key{SQL query} link above the job list to switch back to the interface and enter this new query:
            \begin{lstlisting}
SELECT 0.5+FLOOR(LOG10(mass)) AS logmass,
COUNT(*) AS num
FROM `MDR1`.`FOF`
WHERE snapnum=85
GROUP BY FLOOR(LOG10(mass))
ORDER BY logmass
            \end{lstlisting}
            This uses the \sql{FOF} table of the \sql{MDR1} simulation, which contains dark matter clusters found with a Friends-of-Friends algorithm. The clusters are sorted into logarithmic mass bins using the \sql{FLOOR}-function. The combination of \sql{COUNT} and \sql{GROUP BY} allows to count the number of clusters per bin.
        \item \key{Submit} the query and view the \key{Results Table} after it finished.
     
    
        \item Refine the bins by using steps of 0.25:
        \begin{lstlisting}
SELECT 0.25*(0.5+FLOOR(LOG10(mass)/0.25)) AS logmass, 
COUNT(*) AS num
FROM `MDR1`.`FOF`
WHERE snapnum=85
GROUP BY FLOOR(LOG10(mass)/0.25)
ORDER BY logmass
         \end{lstlisting}
         \key{Submit} again and check the \key{Results Table}: There are more rows now (factor 4).
    \end{itemize}
     
    \item View the result 
    \begin{itemize}
        \item \key{Start \Topcat}, e.g. using \entry{java -jar topcat-*.jar} from the command line in Linux.
        \item Go back to the browser window, choose the \key{SAMP} tab of your latest result and click \key{Register with a local SAMP Hub}. You may need to add a security exception in your browser first (see instructions on web page). Authorize the SAMP connection with "Yes" in the pop-up window.
        
        If you cannot see the SAMP-link, be sure that you are logged in. Guest users cannot use the SAMP interface. If you don't have an account or SAMP does not work, you can still download the table via the \menu{Download} tab (choose e.g. ASCII VO-table) and open the file in Topcat manually.
        \item Click \key{Send table to topcat} in the Topcat-row. This sends the result table to Topcat; the table name should appear there at \menu{Tables}.
        \item In \key{Topcat}, choose the \key{Plane Plot} \icon{topcat-planeplot}. This opens a new window. In the \menu{Position} tab, choose following axes: \menu{X}: \entry{logmass}, \menu{Y}: \entry{num}.
        \item Select the \menu{Form} tab next to \menu{Position} and \menu{Subsets} and click the button \key{Add new Line form} \icon{topcat-addnewlineform}. This connects the points. 
        \item Choose \menu{Axes} in the left side to get access to the axes-properties. At tab \menu{Coords} (that's the default) tick \menu{Y Log}. This makes the y-axis logarithmic. This is your mass function of FOF dark matter halos for the MDR1 simulation!
     
    \end{itemize}
    
   
    \begin{minipage}[c]{0.6\linewidth}   

    \item Experiment with mass functions for other simulations:
         How does the MDR1-mass function compare to MDPL or Bolshoi at the same redshift? How do mass functions change with the halo catalogue (use \sql{BDMV} instead of \sql{FOF} with \sql{Mvir} for the mass) and redshift?
    Here are some tips:
    \begin{itemize}
        \item Take care to use the correct snapnum for each simulation to get the same redshift for each of them (e.g. from \sql{Redshifts}-table:  MDR1: 85, Bolshoi: 416, MDPL2: 125 for \sql{zred=0}).
        \item In order to plot two tables in one \menu{Plane Plot} window in Topcat, 
        click the button \menu{Add new positional plot control} \icon{topcat-addnewpositionalplotcontrol.png}. Then select a table and enter x- and y-axis.
        \item For adjusting the main color and label for each table, select a table at the left side, then choose the \menu{Subsets}-tab. Here you can enter your label text at \menu{Label} and choose the \menu{Color}.

    \end{itemize}

    \end{minipage}
    \hfill
    \begin{minipage}[c]{0.35\linewidth}
        \includegraphics[width=\linewidth]{\dir/Screenshot-topcat-massfunctions}
    \end{minipage}
   
   
\end{enumerate}

\pagebreak[3]

\newpage
% === Halo particles ===
\label{sec:particles}
\section{Particles of a dark matter halo}
For a few simulations, CosmoSim also provides the snapshots of selected timesteps. Thus it is possible to extract particles for a given position or a halo. In this section, the particles of a halo from the FOF-catalogue of the MDR1-simulation will be extracted and plotted with Topcat. Please note, that we use here the term \emph{halo}, though actually, the FOF catalogue returns only groups of connected particles and makes no statement if they are truly gravitationally bound or not. Thus it would be more correct to speak of \emph{FOF groups} instead, but let's keep it simple.

\subsection{Extracting the particles}
\begin{itemize}
    \item Open \url{https://www.cosmosim.org} in a web browser. Login and go to the \key{Query} interface. 
    \item Enter following query:
    \begin{lstlisting}
SELECT p.x,p.y,p.z 
FROM 
  `MDR1`.`Particles85` AS p,
  (SELECT particleId from `MDR1`.`FOFParticles` 
   WHERE fofId = 85000001014) AS fp 
WHERE fp.particleId = p.particleId
    \end{lstlisting}
    This query first selects the ids of all particles for the FOF-halo 85000001014 and then uses them to extract their positions from the snapshot stored in table \sql{MDR1.Particles85}.
    
    
    \item \key{Submit} the query.
    \item When the query is finished, \key{click on the job} in the \menu{Job list} and go to the \key{Plot-tab}. Choose \entry{p.x} and \entry{p.y} as X and Y axis and click 
     \key{Create Plot}. This plots the particle positions in x-y-plane and is a nice simple feature for getting a first impression of the results.
    \item Make sure that \key{Topcat} is still running. 
    
    
    \begin{minipage}[c]{0.65\linewidth} 
    \item In the browser, switch to the \key{Results Table-tab}. Scroll down below the table. You should still be registered with SAMP -- if not, then click again \menu{Register with a local SAMP Hub}. Click \key{Send table to Topcat}. 

    \item In Topcat, select the new table, then choose \menu{Graphics} \goto \menu{Cube Plot} \icon{topcat-cubeplot}. This opens a new window for \key{3D plotting}.
    
    \item Within the \menu{Cube Plot} window of Topcat, choose following axes:
    \menu{X}: \entry{p.x}, \menu{Y}: \entry{p.y} and \menu{Z}: \entry{p.z}
    
    There are so many particles, that the true shape is somewhat obscured. Fortunately, since Topcat version 4, the dots can be colored by projected density: choose the \menu{Form}-tab (next to \menu{Positions} and \menu{Subsets}) and select at \menu{Shading} the \menu{Mode}: \entry{density}. Select a colormap to your liking as well. 
    
   
     
    \end{minipage}
    \hfill
    \begin{minipage}[c]{0.30\linewidth}
        \includegraphics[width=\linewidth]{\dir/Screenshot-topcat-particles}
    \end{minipage}


 \item You can navigate round the 3D plot using the mouse:
    drag to rotate, use the mouse wheel to zoom in and out, and right-click
    (or CTRL-click) to re-center.  More help on navigation is
    available by clicking the small question mark at the bottom
    left of the window.

  \item If you look close enough, you can even detect small lumps of higher density that could represent small subhalos. If you are further interested in them, have a look at the next section.

\end{itemize}

\newpage

% --- Substructures [extra] ---
\subsection{Searching for substructures [extra]}
Curious about substructures? Let's take the example halo from the previous section and search for subgroups. 
\begin{itemize}
    \item Go again to CosmoSim's \key{Query} interface and enter following query:
    \begin{lstlisting}
SELECT s.fofId, s.x,s.y,s.z, s.np
FROM `MDR1`.`FOFSub` AS s,
(SELECT fofSubId, lastSubId from `MDR1`.`FOFSub` WHERE fofId = 85000001014) AS si
WHERE s.fofSubId between si.fofSubId and si.lastSubId
    \end{lstlisting}
    This query uses the \sql{FOFSub}-table which contains the subhalo relations of the FOF groups, i.e. which group is contained in another one. We first need the \sql{fofSubId} of our halo and its \sql{lastSubId}, which are extracted with the subquery. See the documentation on substrucures at the CosmoSim website\footnote{\url{http://www.cosmosim.org/cms/documentation/database-structure/substructures/}} to learn more about these identifiers.
    They are constructed such that the query above retrieves all the subhalos (actually: subgroups) of the given halo. 

    \item \key{Submit} the query. After the job is finished, \key{send the table to Topcat via SAMP} (at \menu{SAMP}-tab).
    
    \item In \key{Topcat's Cube Plot}-window of the previous section, add the subgroup-table by clicking the \menu{Add new positional plot control}-icon \icon{topcat-addnewpositionalplotcontrol}. 
    
    \item Select the new table and choose following axes: \menu{X}: \entry{p.x}, \menu{Y}: \entry{p.y} and \menu{Z}: \entry{p.z}.
    
    This plots the centers of all retrieved subgroups. Note that these are only groups of particles which are close together (closer than a given linking length), there was no bounding-criterion applied. This means that these subgroups are not necessarily true \emph{subhalos}. 
    
      
    \item There are much more subgroup centers marked than were detectable by eye in the density plot, so let's get rid of the very small ones. We could rerun the query with an additional filter like \sql{WHERE np > 100}, but we can also \key{filter results in Topcat}. So let's do this: 
    \begin{itemize}
        \item Select Topcat's main window, choose the subgroups-table and open the \key{Row Subsets}-window (\menu{Views} \goto \menu{Row Subsets}, \icon{topcat-rowsubsets}). Click on the button with the green plus sign \icon{topcat-definenewsubset} to define a new row subset. Enter a \menu{Subset Name}, e.g. \entry{np100} and an algebraic \menu{Expression}: \entry{\$6\textless =100}. This will filter column \$6, which happens to be the number of particles \sql{s.np} and put those rows with obey \$6\textless =100 into a new subset. 

        \begin{minipage}[c]{0.6\linewidth} 
        \includegraphics[width=7.5cm]{\dir/Screenshot-topcat-subsets}%
        \end{minipage} 
        \hfill %
        \begin{minipage}[c]{0.37\linewidth} 
        \includegraphics[width=5cm]{\dir/Screenshot-topcat-particlesubgroupsscaled}
        \end{minipage}
%
        \item In the \key{Cube Plot} window, choose the subgroups-table, then switch to the \menu{Subsets} tab. Check \entry{np100} instead of \menu{All}. There are much less subgroup centers marked now.
   
       \item Let's use one more new feature of Topcat: \key{scale the subgroup-marks}! 
        
        Select the subgroups-table and switch to the \menu{Form}-tab. Add a new size form \icon{topcat-addnewsizeform} and choose for \menu{Coordinates}, \menu{Size}: \entry{s.np} or \entry{ln(\$6)}. This scales the marker size with the number of particles.
   
   \end{itemize}
   
   \item If you want to get more information on a particular subgroup, then first go to Topcat's main window, choose the subgroup-table and open the table browser \icon{topcat-tablebrowser}. This opens the \menu{Table Browser} window which shows the complete result table. 
   
   Now find your prefered subgroup in the 3D plot, hide the particle distribution there (just uncheck the particle-table below the image) and click on the subgroup of interest. This highlights the corresponding row in the \menu{Table Browser}. You can now use the \sql{fofId} given there to extract all information about this subgroup from the corresponding FOF-catalogue, e.g. with a query like this in CosmoSim's query interface:
   \begin{lstlisting}
SELECT * FROM `MDR1`.`FOF2`
WHERE fofId=85200391320
   \end{lstlisting}
   In this example, we used \sql{fofId=85200391320}, the digit right after the snapnum is 2 in our case, so we need to look into the \sql{FOF2}-table. Just replace it with your own choice and explore the properties.
         

\end{itemize}

\subsection{Extract particles of a BDM halo [extra]}
There are no links between BDM halos and their particles stored in the database. But one can search for particles within a given distance from the halo's center. 
Let's start with finding the BDM halo that matches to the FOF group of the previous sections.

\begin{enumerate}
\item Find BDM halo for given FOF group:
\begin{itemize}
    \item Go to the CosmoSim \key{query interface} and enter following query:
    \begin{lstlisting}
SELECT b.bdmId, b.hostFlag, b.x,b.y,b.z, b.Mvir, b.Rvir, b.np 
FROM `MDR1`.`BDMV` b, 
    (SELECT fofId, x,y,z, mass, size, np FROM `MDR1`.`FOF` 
     WHERE fofId = 85000001014) AS f 
WHERE SQRT( POWER(f.x-b.x,2)+POWER(f.y-b.y,2)+POWER(f.z-b.z,2) ) < 0.2 
    AND snapnum=85 
ORDER BY b.np
    \end{lstlisting}
    This query first selects the position of our FOF group and then joins this with the \sql{BDM}-table 
    based on matching positions. We choose $<$ 0.2 Mpc/$h$ here.
    \item \key{Submit} the query and after your job has finished, view the \key{Results Table}.
    It contains two BDM halos: the first one is only a subhalo, but the second one is a distinct halo with the appropriate mass range. Let's take this halo then and remember its id (\sql{bdmId = 8501268794}).
\end{itemize}

\item Get particles for the BDM halo:
\begin{itemize}
    \item Enter following query into the \key{query form}:
    \begin{lstlisting}
SELECT p.x AS x, p.y AS y, p.z AS z, p.particleId AS particleId 
FROM `MDR1`.`Particles85` AS p, 
    (SELECT x,y,z, Rvir FROM `MDR1`.`BDMV` WHERE bdmId = 8501268794) AS b 
WHERE SQRT( POWER(b.x-p.x,2)+POWER(b.y-p.y,2)+POWER(b.z-p.z,2) ) < 1.*b.Rvir
    \end{lstlisting}
    
    This extracts all particles with a distance smaller than the virial radius to the center of the BDM halo. 
    For the MDR1-simulation, positions and radius have the same unit, for other simulations please first check 
    the units via the online documentation and adjust the factor before \sql{b.Rvir} where appropriate.
    
    \item \key{Submit} the query and after your job has finished, view the \key{Results Table}.
    You can view the particle distribution in Topcat by again switching to the \menu{SAMP}-tab, clicking \key{Send table to topcat} and plotting the data in the same 
    way as in the previous section. You will see now a spherical particle cut-out of the same 
    region as before. 
\end{itemize}

\end{enumerate}

\pagebreak[3]

\newpage
% === Merger tree ===
\section{Merger tree of a halo}
The merging history of halos is stored using the same database structure as was used for the Millennium database. This includes assigning unique identifiers to each halo of the merger tree, sorting them in a depth-first order and by the most massive progenitor. In this way, it is quite straightforward to retrieve all progenitors of a halo or just its main branch, as shown below. More details are given in the documentation of the CosmoSim website\footnote{\url{https://www.cosmosim.org/cms/documentation/database-structure/merger-trees/}}.

\begin{itemize}
    \item Open \url{https://www.cosmosim.org} in a web browser. Login and go to the \key{Query} interface. 
    \item Enter following query:
    \begin{lstlisting}
SELECT x,y,z,treeSnapnum FROM `MDR1`.`FOFMtree`
WHERE fofTreeId BETWEEN 100000000 AND
  (SELECT lastProgId FROM `MDR1`.`FOFMtree`
   WHERE fofTreeId = 100000000) 
AND np>200
    \end{lstlisting}
    This query first selects the \sql{lastProgId} for the given halo in order to extract the positions of all the progenitors which consist of more than 200 particles.
    
    
    \begin{minipage}[c]{0.6\linewidth} 
    
    \item Enter a result table name (e.g. \entry{mtree}) and \key{Submit} the query.
             
    \item When the query is finished, \key{click on the job} in the \menu{Jobs} list and go to the \key{SAMP-tab}. Scroll down below the table. 
    If you are not still registered with SAMP, make sure that Tocat is open, then click again \menu{Register with a local SAMP Hub}. Otherwise you can directly click \key{Send table to topcat}. 

    \item In Topcat, go to \menu{Graphics} \goto \menu{Cube Plot} \icon{topcat-cubeplot}. 
    \item Within the \menu{Cube Plot} window, select following axes:
    \menu{X}: \entry{x}, \menu{Y}: \entry{y} and \menu{Z}: \entry{z}.
    
    \item Let's apply more color! Switch to the \menu{Form}-tab, at \menu{Shading}, choose \menu{Mode}: \entry{aux} and enter for \menu{Aux}: \entry{treeSnapnum}. The \sql{treeSnapnum} is a snapshot number, i.e. it corresponds to a time axis. Halos from the beginning of the simulation are colored black-purple, more recent progenitors have yellow-red colors. 
    
    Adjust the \menu{Opaque limit} and \menu{Shape}/\menu{Size} to your liking, your plot should look similar to the figure here.
    This nicely shows how the progenitor halos have merged with each other at earlier times to form the final halo (red) at the center. 
    
    \end{minipage} 
    \hfill %
    \begin{minipage}[c]{0.37\linewidth} 
        \includegraphics[width=6cm]{\dir/Screenshot-topcat-mtree}
    \end{minipage}

\end{itemize}


%[TODO: add more sections for e.g. mass accretion history??? What about halo profiles??]


\pagebreak[3]


\newpage
% === Cosmic Web example ===
\section{The Cosmic Web with {\sc TOPCAT}}
\subsection{Load a slice (x-y plane) of the cosmic web for the Bolshoi simulation}
\begin{itemize}
    \item Before actually downloading some data, let's first check where the \key{most massive halo}
is located, using the \key{BDMV halo} catalogue:
   	Enter following query into the \key{Query Form} at CosmoSim\footnote{\url{http://www.cosmosim.org/query}}:
	\begin{lstlisting}
SELECT ix,iy,iz,x,y,z,Mvir FROM `Bolshoi`.`BDMV`
WHERE snapnum=416 ORDER BY Mvir DESC LIMIT 1
	\end{lstlisting}
	This selects some properties of the most massive halo for snapshot number 416 (corresponds to
 $z=0$ for this simulation).
	\item \key{Submit} the query. 
	\item When the query is finished, \key{click on the job} in the \menu{Jobs} list and switch to the 
	    \key{Results Table} tab. 
        We can see now the position and mass \sql{Mvir} of the halo as well as the indizes of a $1024^3$ grid cell, in which the halo sits (\sql{ix},\sql{iy},\sql{iz}). The size of the grid is given in the documentation.
	\item One can \key{convert these numbers} into the corresponding indizes of a coarser
 grid by dividing them by \key{factors of 2}.
E.g. a grid cell with \sql{ix=200} in a $1024^3$ grid corresponds to \sql{ix' = floor(ix/2.) =
100} in a $512^3$ grid, and \sql{ix'' = 50} for a $256^3$ grid. 

    \item Switch back to the \key{query interface} and enter a query to extract a slice of the cosmic web at the location of the most massive BDM halo:
      		\begin{lstlisting}
SELECT ix,iy,iz,phkey,eigen1,eigen2,eigen3 FROM `Bolshoi`.`Tweb256` WHERE iz = floor(377/4.)
			\end{lstlisting}
           
           We use here the table \sql{Tweb256}, which contains a $256^3$ grid, and for
   each grid cell the eigenvalues and eigenvectors for the gravitational
  tidal tensor. To match with the $z$-position of the most massive halo we need the conversion factor due to the different grid sizes, as explained above. Since we only fix the \sql{iz}-value, we will get a $256\times256$ slice in x-y plane returned. 
	
    \item \key{Submit} to submit the query and after it finished switch to the \menu{SAMP} tab. Make sure that \key{Topcat} is still open before clicking \key{Send table to topcat}. 
\end{itemize}


\subsection{View the cosmic web}
\begin{itemize}



    \begin{minipage}[c]{0.66\linewidth} 
    \item Have a look at the data in Topcat with the \key{Table Browser} \icon{topcat-tablebrowser}
which you can access from \Topcat's main window. It displays your data as a table. The column names have also been imported.

    \item \key{View your data} by clicking on the \key{Plane Plot} icon \icon{topcat-planeplot}.
		Choose: \menu{X}: \entry{ix}, \menu{Y}: \entry{iy}
    \item Add some \key{color} by choosing the \menu{Form} tab and at \menu{Shading} select \menu{Mode}: \entry{aux}. 
		Set \menu{Aux} to one of the eigenvalues, e.g. \entry{eigen1}, for coloring the dots.
	    Adjust the point size at \menu{Global Style}, \menu{Size} to be at least 1.

    \item Step through \key{different columns for \menu{Aux}} to get an impression of the other
eigenvalues as well. Also look at the {phkey}-column: These are Peano-Hilbert keys,
describing a space-filling curve. They are very useful for spatial queries.

	
		
    \end{minipage} 
    \hfill %
    \begin{minipage}[c]{0.29\linewidth} 
\includegraphics[width=\linewidth]{\dir/Screenshot-topcat-webslice.png}
    \end{minipage}

    \item Just visualising one of the eigenvalues does not yet give much information. Instead, we need to count
the number of eigenvalues above a certain threshold. This number will allow us to
assign each cell with following classification:

	\vspace{0.5em}
	\begin{tabular}{l}
		0 = cosmic void\\
		1 = sheet-like structure\\
		2 = filament\\
		3 = knot (= location of galaxy clusters)\\
	\end{tabular}

    \item We can get this number by adding a \key{computed column} to our data in Topcat:
        \begin{itemize}
        	\item In Topcat's main window, click on \menu{Display Column Metadata} \icon{topcat-colmeta}
        	\item Click on the green plus \icon{topcat-plus} to add a new column to your table.
		\item In the new dialogue, enter a \menu{name} for your new column, e.g. \entry{num\_th0.4}
		\item Enter the \menu{expression} to compute the column:
		
		\begin{lstlisting}
(eigen1$>=$0.4 ? 1:0) + (eigen2$>=$0.4 ? 1:0) + (eigen3$>=$0.4 ? 1:0)
		\end{lstlisting}

		This expression checks, if an eigenvalue is greater than or equal to the
threshold value (e.g. 0.4) and adds 1, if this is the case, otherwise 0.
		\item Click OK. You can still edit the name and expression of your column by
 double clicking on it in the \menu{Column Metadata} dialogue.
		
        \end{itemize}
		\item Switch back to the \key{Scatter Plot Window} and choose the new computed
 column as \menu{Aux} axis.
 		\item Now you should see some clustered structures (red knots) and filaments (green). Note that this is just a cut
 through the $z$-direction, so perpendicular sheets appear as filaments and
filaments may appear as knots.
		\item By the way: instead of calculating the additional column with Topcat, you
 could already do it within the SQL query itself:

		\begin{lstlisting}
SELECT ix,iy,iz,phkey,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
FROM `Bolshoi`.`Tweb256` 
WHERE iz = floor(377/4.)
    \end{lstlisting}

This method only has the disadvantage that you would need to query
the database again for each new threshold value that you are testing.


\end{itemize}

\subsection{Explore more data [extra]}
\begin{enumerate}
   \begin{minipage}[c]{0.6\linewidth}   
\item Repeat the \key{same steps} for loading a slice of the same region from the \key{Vweb256} table.
\key{Compare the differences:} the Vweb should give a much better resolution of
structures than the Tweb. (Look in the documentation at the webpage and the linked papers to learn
about the physical reasons behind this.)
\item Experiment with \key{different threshold values} by adding more computed columns.
   \end{minipage}
   \hfill
   \begin{minipage}[c]{0.37\linewidth}
   \includegraphics[width=\linewidth]{\dir/cosmicweb-compare}
   \end{minipage}
  
   \begin{minipage}[c]{0.6\linewidth}   
\item  Experiment with \key{slices at different positions}. You can also download several $z$-slices
   and plot them in 3D.\\
   For example, do following query:
   \begin{lstlisting}
SELECT ix,iy,iz,phkey,eigen1,eigen2,eigen3
FROM `Bolshoi`.`Vweb256`
WHERE iz BETWEEN 92 AND 96
   \end{lstlisting}
   
   and send the result to Topcat.
   We will use the old 3D plot feature of Topcat now, because for this case it gives a nicer visual impression. 
   \begin{itemize}
   \item Choose \menu{Graphics} \goto \menu{3D Plot (old)} and adjust the columns: \menu{X}: \entry{ix}, \menu{Y}: \entry{iy}, \menu{Z}: \entry{iz}.
   \end{itemize}
   
   \end{minipage}
   \hfill
   \begin{minipage}[c]{0.37\linewidth}
   \centerline{\includegraphics[width=0.8\linewidth]{\dir/Screenshot-topcat-cosmicwebplanes}}
   \end{minipage}
   
   
   \begin{itemize}
   \item Add a new auxiliary axis by clicking the icon \icon{topcat-auxaxis}. Set \menu{Aux 1} to one of the eigenvalues or add again the sum of eigenvalues above a given threshold value to color by this value.
   \end{itemize}
   
   
 
\item Load a \key{finer grid} by using the \key{Vweb512} table and compare with the coarser version.
Note, that you need to \key{convert the \sql{iz}-value properly} if you want to get matching
slices:
   \begin{lstlisting}
SELECT ix,iy,iz,phkey,eigen1,eigen2,eigen3
FROM `Bolshoi`.`Vweb512`
WHERE iz = 188
   \end{lstlisting}
   
   If your job times out, select \key{Long queue} below the query form before submission.
\end{enumerate}

% --- halos in cells ---
\subsection{Find halos in cells}
\begin{itemize}
\item In Topcat, open the \key{Table browser} and a \key{Plane plot window} for the same data table (e.g. for \sql{Tweb256}, from our first cosmic web query).
\item \key{Click on one of the knots} in your plot and check to which cell it belongs: the
corresponding \key{row} in the table browser is \key{highlighted} now.
\item Go back to CosmoSim's query interface in the web browser and enter following query to retrieve all halos inside a given  cell, sorted by mass, e.g.:
  \begin{lstlisting}
SELECT bdmId, x,y,z, Mvir, spin FROM `Bolshoi`.`BDMV`
WHERE snapnum=416
AND FLOOR(0.25*ix) = 101
AND FLOOR(0.25*iy) = 138
AND FLOOR(0.25*iz) = 94
ORDER BY Mvir DESC
  \end{lstlisting}
Replace \entry{101}, \entry{138} and \entry{94} by the values for your cell.
\item \key{Submit} the query and send the results to Topcat. 

\item In Topcat, select the previous \key{Scatter plot window} and click on the icon for adding a new data set \icon{topcat-addnewpositionalplotcontrol.png}. Choose the halos-table you just downloaded and set the axes: 
\menu{X}: \entry{x}, \menu{Y}: \entry{y}.
\item Adjust the point size and color in the \menu{Form} or \menu{Subsets} tab, if needed. You can also adjust the size of points depending on the halo's mass. 
    
\item \key{Zoom} into the cell where the halos are located using the mouse-wheel. Check which is the most massive halo in that region and if it lies at the center of the knot (as expected) or farther away. 
\end{itemize}

\pagebreak[3]


\newpage
% === UWS example ===
\section{Command-line access via UWS}
The web application behind CosmoSim also provides job handling via UWS, which allows users to script the submission and deletion of jobs. This is a very useful feature for long running jobs (e.g. when downloading particles for many halos) which need to be split up into smaller jobs due to the time limit per query.

In order to talk to the web application, one needs to send POST/GET request to a specified URL. This can be done with different tools. We're going to show three different tools and how to do the most basic operations: create/submit a job, get the job's status/info, get the job list, retrieve results and delete a job. 
Since it is all directed from the command line, this makes it easy to integrate results from the CosmoSim database into your work environment.

Please note that UWS only works for registered users, if you didn't register yet, please get an account (for free!) at the CosmoSim webpage.

The basic URL for CosmoSim's UWS service is:
\url{https://www.cosmosim.org/uws/query}.

\subsection{UWS with httpie}
If you don't have \cmd{httpie} installed, download it from \url{https://github.com/jkbr/httpie} and install it.
Please replace \cmd{<username>} and \cmd{<password>} in the examples below with your own credentials.

\begin{enumerate}
\item \textbf{Create and submit a job}

Write following lines in the terminal to \key{create a job} for retrieving the particles of a given FOF group, like in section \ref{sec:particles}. We will limit the result here to 20 rows for now.

   \begin{lstlisting}
http --auth <username>:<password> --form --follow POST \
http://www.cosmosim.org/uws/query \
query="SELECT p.x,p.y,p.z FROM MDR1.Particles85 AS p, (SELECT particleId \
from MDR1.FOFParticles WHERE fofId = 85000001014) AS fp WHERE \
fp.particleId = p.particleId LIMIT 20" \
table="demoparticles" queue="short"
   \end{lstlisting}

The tablename must be unique, you can also omit this parameter and a unique tablename will be created automatically.
When you send this, you will get a response in xml containing the job description. The job is now in \menu{pending} phase and still waiting for you to really start it. Search for the \cmd{jobid} in the output. This jobid is needed to refer to this job in the next steps. Be sure to replace the example jobid below with your own value.  

\key{Submit the job} by sending \cmd{phase=run} for this job identified by its jobid from the previous step.

   \begin{lstlisting}
http --auth <username>:<password> --form --follow POST \
http://www.cosmosim.org/uws/query/360912133673751 phase=run
   \end{lstlisting}


\item \textbf{Get the job's details}

Check the \key{job phase}:
   \begin{lstlisting}
\begin{verbatim}
http --auth <username>:<password> --print b GET \
http://www.cosmosim.org/uws/query/360912133673751/phase
\end{verbatim}
   \end{lstlisting}
   This should return the phase \cmd{QUEUED}, \cmd{EXECUTING} or \cmd{COMPLETED}, if nothing went wrong.
   
Get the complete \key{job info}:
   \begin{lstlisting}
http --auth <username>:<password> --print b GET \
http://www.cosmosim.org/uws/query/360912133673751
   \end{lstlisting}

When the job is finished (\cmd{COMPLETED} phase), you can get information about its \cmd{result url}  like this:
   \begin{lstlisting}
http --auth <username>:<password> --print b GET \
http://www.cosmosim.org/uws/query/360912133673751/results
   \end{lstlisting}
   Actually, this outputs only that part of the complete job info from above that contains information about the result. Look here
   for the tag \cmd{<uws:result ....>}. The url at \cmd{xlink:href} given here (it contains the tablename, if you provided one) can be used to retrieve the results in the next step.
   
\item \textbf{Retrieve the results}

 Download the results directly to the terminal:
     \begin{lstlisting}
\begin{verbatim}
http --auth <username>:<password> --print b GET \
http://www.cosmosim.org/query/download/stream/table/demoparticles/format/vodump-csv
\end{verbatim}
     \end{lstlisting}
     
     Or download your results into a \key{file} \entry{</path/file.csv>}:
     \begin{lstlisting}
\begin{verbatim}
http --auth <username>:<password> --download --output </path/file.csv>  GET \
http://www.cosmosim.org/query/download/stream/table/demoparticles/format/vodump-csv
\end{verbatim}
     \end{lstlisting}
     
     If you prefer votables, that's also supported. Just change the format into \sql{votable}:
      \begin{lstlisting}
http --auth <username>:<password> --download --output </path/file.csv>  GET \
http://www.cosmosim.org/query/download/stream/table/demoparticles/format/votable
     \end{lstlisting}
    

\item \textbf{List all jobs}
    \begin{lstlisting}
http --auth <username>:<password> --print b GET http://www.cosmosim.org/uws/query
    \end{lstlisting}
    This prints all your jobs to the terminal.


\item \textbf{Delete a job}
    \begin{lstlisting}
http --auth <username>:<password> --follow DELETE \
http://www.cosmosim.org/uws/query/360912133673751
    \end{lstlisting}
    This deletes the job identified by its jobid. The details of the job may still be archived on the server, but the results will definitely be deleted.

\end{enumerate}

% --- uws-client ---
\subsection{Using the Python UWS client}
Download the UWS-client from GitHub: \url{https://github.com/adrpar/uws-client} and install it.
We will use here the script \cmd{uws-client/bin/uws} and short-cut it as \cmd{uws} in the commands below.

\begin{enumerate}
\item \textbf{Create and submit a job}

Write following lines in the terminal to \key{create a job} for retrieving the particles of a given FOF group, like in section \ref{sec:particles}. We will limit the result here to 20 rows for now.

   \begin{lstlisting}
uws --host www.cosmosim.org/uws/query --user <username> --password <password> \
job new \
query="SELECT p.x,p.y,p.z FROM MDR1.Particles85 AS p, (SELECT particleId \
from MDR1.FOFParticles WHERE fofId = 85000001014) AS fp WHERE \
fp.particleId = p.particleId LIMIT 20" \
table="demoparticles2" queue="short" 
   \end{lstlisting}

Again the tablename must be unique, you can also omit this parameter and a unique tablename will be created automatically.
After sending this, you'll get some job information back, including the job id to refer to this job in the next steps.
 Be sure to replace the example jobid below with your own value.  

\key{Submit the job} identified by its jobid from the previous step.
   \begin{lstlisting}
uws --host www.cosmosim.org/uws/query --user <username> --password <password> \
job run 360912697344392
   \end{lstlisting}


\item \textbf{Get the job's details}
   \begin{lstlisting}
uws --host www.cosmosim.org/uws/query --user <username> --password <password> \
job show 360912697344392
   \end{lstlisting}

   The phase of your job should be \cmd{QUEUED}, \cmd{EXECUTING} or \cmd{COMPLETED}, if nothing went wrong.
   
   The \key{result-url} is given in the last row. You can use this directly (e.g. with wget or in a browser) or 
   follow the next step.
      
\item \textbf{Retrieve the results}
   Download the results in a file directly into the directory from which you call uws:
   \begin{lstlisting}
\begin{verbatim}
uws --host www.cosmosim.org/uws/query --user <username> --password <password> \
job results 360912697344392
\end{verbatim}
   \end{lstlisting}
   
\item \textbf{List all jobs}
    \begin{lstlisting}
uws --host www.cosmosim.org/uws/query --user <username> --password <password> \
list
    \end{lstlisting}
    This prints all your jobs to the terminal in a nicely formatted list. You can even filter by phase by adding one of the following optional arguments: \cmd{--pending}, \cmd{--queued}, \cmd{--executing}, \cmd{--completed}, \cmd{--error}, \cmd{--aborted}.
    E.g. for listing only pending jobs, use:
\begin{lstlisting}
uws --host www.cosmosim.org/uws/query --user <username> --password <password> \
list --pending
\end{lstlisting}
    


\item \textbf{Delete a job}
   \begin{lstlisting}
uws --host www.cosmosim.org/uws/query --user <username> --password <password> \
job delete 360912697344392
    \end{lstlisting}
    This deletes the job identified by its jobid. The details of the job may still be archived on the server, but the results will definitely be deleted.

\end{enumerate}



\subsection{Querying with astroquery.cosmosim}
%We will show here some commands for an interactive Python session, but of course you can put them all into a script as well. 
Download astroquery from \url{https://github.com/astropy/astroquery} or install it via \cmd{pip install astroquery}.
Since the cosmosim package is still under heavy development, we refer to the online documentation at\\
\url{http://astroquery.readthedocs.org/en/latest/cosmosim/cosmosim.html} for the moment. \\
You can also checkout CosmoSim's documentation on UWS access at\\
 \url{http://www.cosmosim.org/cms/documentation/data-access/access-via-uws/}.


\end{document}
