\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, 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\\with ADQL and Topcat}
\author{Kristin Riebe\thanks{Leibniz-Insitute for Astrophysics Potsdam (AIP); email: kriebe@aip.de}, GAVO}
\date{July 05, 2016}

\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 and using their TAP/UWS interface, if available.
This tutorial shows a few example use cases for extracting the mass function of a simulation, particles for a halo, the mass growth history and progenitors of a halo as well as a slice of the density field. \\
This tutorial version will use \textbf{AIP's DaCHS} instance, which contains only a small part of the data for testing and demonstration purposes. The full data is released at the \CosmoSim\footnote{\url{https://www.cosmosim.org/}} webpage, tutorials for this web interface can be found at the GAVO tutorials webpage\footnote{\url{http://www.g-vo.org/pmwiki/Documents/Tutorials}}; some more demos, examples and tutorials are available at the CosmoSim webpage.
\end{abstract}

\usedsoftware{\Topcat}

\section{A first TAP query}
\begin{itemize}
    \item \key{Start \Topcat}, e.g. using \entry{java -jar topcat-*.jar} from the command line in Linux.
\end{itemize}
    \begin{minipage}[c]{0.6\linewidth}
        
\begin{itemize}
    \item Go to \menu{VO} \goto\ \key{Table Access Protocol (TAP) query} \icon{topcat-tapquery} .
    \item Write following TAP URL in the bottom line:\\
          \entry{http://gavo.aip.de/tap}\\
          and press \key{Use Service}. Topcat will switch automatically to the next tab and load all the table metadata.\\
    \item For this tutorial, we will only use data from the MDR1 and Bolshoi simulation, located in tables \key{mdr1} and \key{bolshoi}. Click on one of their tables (e.g. mdr1.fof) in the left tree view and browse the table metadata and columns on the right side. Note, that indexed columns are also marked, which is useful to know since queries on indexed columns will be fast.
\end{itemize}
    \end{minipage}
    \hfill
    \begin{minipage}[c]{0.35\linewidth}
        \includegraphics[width=\linewidth]{\dir/Screenshot-topcat-adql-mdr1fof}
    \end{minipage}
    
\begin{itemize}
	\item In the bottom text area (\key{ADQL Text}) enter following ADQL query:
   		\begin{lstlisting}
SELECT TOP 100 * FROM `mdr1`.`fof`
WHERE snapnum=85
ORDER BY mass DESC
		\end{lstlisting}
		This query will retrieve the 100 most massive clusters (ordered by
mass in descending order) for redshift 0 (= snapshot number 85) from the MDR1-simulation.

		\item Click the \key{Run Query} button below the ADQL text area to submit the query.
		\item In Topcat's main window, a new table appears, which stores the result of your query. Rename the table to \entry{FOF-mostmassive} or similar by editing the \menu{Label} field on the right side at \menu{Current Table Properties}. Confirm by pressing the \entry{Enter} key.
		\item You can browse the result table at \menu{Views} \goto\ \menu{Table data} \icon{topcat-tablebrowser}. Check, if the mass-column is really sorted in descending order. 

\end{itemize}

\pagebreak[3]
% === Mass function ===
\section{Mass function for FOF groups}
In this section we plot the mass function for dark matter clusters of a simulation.
\begin{enumerate}
    \item Get the mass function
    \begin{itemize}
 	    \item Switch back to the TAP query window of Topcat. 
        \item A mass function gives the number of objects for a selected mass bin, which can be done directly in ADQL. Let's try this for the MultiDark Run 1 (\sql{MDR1}) simulation, using the \sql{fof} table. It contains dark matter clusters found with a Friends-of-Friends (FOF) algorithm. Type the following query into the ADQL form:
            \begin{lstlisting}
SELECT ROUND(LOG10(mass)) AS logmass,
COUNT(*) AS num
FROM `mdr1`.`fof`
WHERE snapnum=85
GROUP BY logmass
ORDER BY logmass
            \end{lstlisting}
            This will sort the FOF clusters 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 Change the \key{Mode} of your ADQL query to \key{Asynchronous}. This is important since the following queries may take a couple of minutes. using asynchronous mode means that the query will keep running even if the connection to the server times out.
        \item Click \key{Run Query}, wait a while and view the resulting table data \icon{topcat-tablebrowser} after the query finished.    
        \item Refine the bins by using steps of 0.25, i.e. enter following query:
        \begin{lstlisting}
SELECT 0.25*(ROUND(LOG10(mass)/0.25)) AS logmass, 
COUNT(*) AS num
FROM `mdr1`.`fof`
WHERE snapnum=85
GROUP BY logmass
ORDER BY logmass
         \end{lstlisting}
         \key{Submit} again and check the \key{Results Table}: There are more rows now (factor 4).
    \end{itemize}

    \pagebreak
    
    \item View the result
    
    \begin{minipage}[c]{0.65\linewidth}
    \begin{itemize}
        \item In Topcat's main window, choose \menu{Graphics} \goto\ \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} for connecting 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} to make the y-axis logarithmic. 
        \item Instead of changing axis properties, you can also add the logarithm into your ADQL query or Topcat's Plane Plot window, at Positions, enter \entry{log10(num)} for the Y-axis.
        \item Now you got your first mass function plot of FOF dark matter clusters for the MDR1 simulation!
    \end{itemize}
    \end{minipage}
    \hfill
    \begin{minipage}[c]{0.30\linewidth}
        \includegraphics[width=\linewidth]{\dir/Screenshot-topcat-massfunction}
    \end{minipage}
    \begin{itemize}
     	\item If there are more simulations or halo catalogs available (e.g. from the CosmoSim database), you can load them into Topcat and directly compare the different mass functions with each other.
    \end{itemize}
   
   
\end{enumerate}


\section{3D distribution of dark matter objects}\label{sec:3dhalos}
 
\begin{itemize}
	\item Go to Topcat's TAP Query interface \icon{topcat-tapquery}.
	\item Change \key{Max Rows} (just above the ADQL text area) to the maximum, because we will exceed the default limit of 2000 rows.
	\item Enter following new ADQL query (escape the column name \sql{size} with double quotes, since it is a reserved keyword):

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

	\begin{lstlisting}
SELECT x, y, z, mass, "size" FROM `bolshoi`.`fof`
WHERE snapnum=416 AND mass >= 1.e10
AND RAND() < 0.01
ORDER BY mass DESC
	\end{lstlisting}

	 This randomly selects 1\% of all halos above the given mass threshold and sorts them by mass. 
The query retrieves $\sim$\,3 MB of data and may take a minute to complete. The results will look even nicer, if you change the fraction from 0.01 to 0.10; but this 
may slow down Topcat's 3D view substantially. If you already have performance problems with Topcat,
add \entry{TOP 2000} at the beginning of your query.

\item Make sure to select \menu{Mode:} \entry{Asynchronous}, click \key{Run Query} and watch the new table being loaded into Topcat.

\item In Topcat's main window, select the new table, then choose \key{Graphics} \goto\ \key{Cube Plot} \icon{topcat-cubeplot}. This opens a new window for 3D plotting.

\item Choose for \key{positions}: \menu{X}: \entry{x}, \menu{Y}: \entry{y}, \menu{Z}: \entry{z}.


\end{minipage}
\hfill    
\begin{minipage}[c]{0.3\linewidth}
\includegraphics[width=\linewidth]{\dir/Screenshot-topcat-fof3d}\\
\end{minipage}

\item Switch to the \key{Form} tab. Increase the window size for better visibility. Select \menu{Shading, Mode}: \entry{density}.

\item Scroll down to \menu{Global Style} and set the dot \key{Size} to \entry{0} instead of the default 1. 
Use the left mouse button to rotate the 3D plot. More help on navigation is
    available by clicking the small question mark at the bottom left of the window. The distribution of FOF objects already gives an impression of the 3D distribution of matter in the universe.

%\item Scale your dots by the cluster size: In the Cube Plot window, \menu{Form}-tab, click \menu{Add new Size form} \icon{topcat-addnewsizeform}. Set at \menu{Coordinates}, \menu{Size} to \entry{size} and move the \menu{Scale} slider until the plot looks nice. The biggest objects appear now as bigger blobs.

\end{itemize}


% === Halo particles ===
\label{sec:particles}
\section{Particles of a dark matter halo}
For a few simulations, CosmoSim also provides the snapshots of selected timesteps; an extract of the this data is also available with the AIP DaCHS interface which we use here. This makes it possible to extract particles for a given position or a halo. In this section, the particles of an object 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.

\begin{itemize}
    \item In Topcat's TAP Query interface, 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 Click \key{Run Query} to submit the query.   

    \begin{minipage}[c]{0.65\linewidth} 
    \item In Topcat's main window, select the new table, then choose \menu{Graphics} \goto \menu{Cube Plot} \icon{topcat-cubeplot}. This again 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. But we can color the dots 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 at \menu{Density Shader}. Use \entry{Pastel}, \entry{log} scaling and check \entry{Flip} to get the same results as on the screenshot.
    
    \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.
     
    \end{minipage}
    \hfill
    \begin{minipage}[c]{0.30\linewidth}
        \includegraphics[width=\linewidth]{\dir/Screenshot-topcat-particles}
    \end{minipage}

  \item If you look close enough, you can even detect small lumps of higher density that could represent small subhalos. You could check if halo finders have detected them as well by extracting the positions and sizes of dark matter halos in this region (use the \sql{bolshoi.fof} table) and overplotting them here (see more extensive tutorials at the CosmoSim webpage or GAVO Tutorials page).

\end{itemize}



% === Mass growth, merger tree ===
\section{Mass growth of a halo}\label{sec:massgrowth}
Dark matter halos grow with time, because they accrete loose material ("smooth accretion") as well as other dark matter halos.
This can be detected when plotting the mass growth of a halo with time.
%A plot of the mass growth can give first hints, whether the merging history of a halo was rather quite and smooth or violent with many major mergers.


\begin{itemize}

\item Go to the Topcat TAP Query interface and enter following query:

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

\begin{lstlisting}
SELECT p.fofTreeId, p.treeSnapnum, p.mass, p.size
FROM `mdr1`.`fofmtree` AS p, 
(SELECT fofTreeId, mainLeafId FROM `mdr1`.`fofmtree` 
  WHERE fofId = 85000000001) AS m
WHERE p.fofTreeId BETWEEN m.fofTreeId AND m.mainLeafId
ORDER BY p.treeSnapnum
\end{lstlisting}

\item[] This query selects all direct progenitors of the given halo (so-called \emph{main branch}). Adjust the \texttt{fofId}, if you want to get the mass growth of a different halo, but note that only values between 85000000000 and 85000000010 will yield results, because the test dataset at AIP's DaCHS instance does not contain the complete data set.

\item Click \key{Run Query} and when the new table is loaded, select table in Topcat's main window.

\item In Topcat's main window, choose \key{Graphics} \goto\ \key{Plane Plot} \icon{topcat-planeplot}.
\item In the \key{Position} tab, choose following axes: \menu{X}: \entry{p.treeSnapnum}, \menu{Y}: \entry{p.mass}.
	The \texttt{treeSnapnum} is the time axis here: 0 is a snapshot closer to the beginning and 39 at the end of the simulation.

    \end{minipage}
    \hfill
    \begin{minipage}[c]{0.30\linewidth}
        \includegraphics[width=\linewidth]{\dir/Screenshot-topcat-massgrowth-1}
    \end{minipage}

\item Switch to the \key{Form}-tab and add a new line form \icon{topcat-addnewlineform} for connecting the points. 
Steep increases in the mass from one timestep to another indicate the accretion of a lot of mass at once, i.e. merging events with other dark matter halos. Sharp decreasing mass can give a hint that two clusters of dark matter particles are close enough to be considered as one object at a certain timestep, but due to fluctuations in the particle distribution they may be considered as separate objects again in the next timestep.

\end{itemize}
   

\section{Merger tree: progenitors of a halo}\label{sec:mergertree}
Let us now find all the halos which have merged into one dark matter halo at the end of the simulation.

\begin{itemize}

	\item Go to the Topcat TAP Query interface and enter following query (again adjust the \texttt{fofId}, if you want):

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

\begin{lstlisting}
SELECT p.fofTreeId, p.treeSnapnum, p.x, p.y, p.z,p."size"
FROM `mdr1`.`fofmtree` AS p, 
(SELECT fofTreeId, lastProgId FROM `mdr1`.`fofmtree` 
  WHERE fofId = 85000000001) AS m
WHERE p.fofTreeId BETWEEN m.fofTreeId AND m.lastProgId
ORDER BY treeSnapnum
\end{lstlisting}
    
	\item Click on \key{Run Query} and select the result table in Topcat.
    \item In Topcat's main window, go to \key{Graphics} \goto\ \key{Cube Plot} \icon{topcat-cubeplot}. 
    \item Select following \key{axes}:
    \menu{X}: \entry{p.x}, \menu{Y}: \entry{p.y} and \menu{Z}: \entry{p.z}.
    
    \item Switch to the \key{Form}-tab and add a new size form \icon{topcat-addnewsizeform}. Set the \menu{Size} to \entry{size} for scaling the dots with the average halo radius.
    
    \item Apply more color by selecting at \menu{Shading}, the \menu{Mode}: \entry{aux} and entering \menu{Aux}: \entry{treeSnapnum}. Adjust the \menu{Opaque limit} to your liking.

    \end{minipage} 
    \hfill 
    \begin{minipage}[c]{0.3\linewidth} 
        \includegraphics[width=\linewidth]{\dir/Screenshot-topcat-mtree-1}
    \end{minipage}

    \item Now halos from the beginning of the simulation are colored black-purple, more recent progenitors have yellow-red colors. 
    This nicely shows how the progenitor halos have merged with each other at earlier times to form the final big halo (red) at the center.


\end{itemize}








\newpage
% === Cosmic Web example ===
\section{The density field with {\sc TOPCAT}}

\begin{itemize}
	\item Go to the Topcat TAP Query interface and enter following query in order to extract a slice of the density from the Bolshoi simulation at the location of one of the most massive dark matter halos:
			\begin{lstlisting}
SELECT ix,iy,iz,dens FROM `bolshoi`.`dens256` 
WHERE snapnum=416 AND iz = 94
ORDER BY ix, iy
			\end{lstlisting}
           
           We use here the table \sql{Dens256}, which contains a $256^3$ grid, and for
   each grid cell the density value. Since we only fix the \sql{iz}-value, we will get a $256\times256$ slice in x-y plane returned. 
	
    \item Choose \menu{Mode}: \entry{Asynchronous} and click \key{Run Query}. This may take a while.
\end{itemize}


\begin{itemize}

    \begin{minipage}[c]{0.66\linewidth} 
	\item In Topcat's main window, select your table and choose \key{Graphics} \goto\ \key{Plane Plot} \icon{topcat-planeplot} top open the plane plot window.

	\item In the \key{Position} tab, choose following axes: \menu{X}: \entry{ix}, \menu{Y}: \entry{iy}.

	\item Select the \key{Form} tab next to \menu{Position} and \menu{Subsets}. Increase the size of your window (click and drag with left mouse button) or switch to full screen mode in order to see all the possible options.

	\item At \menu{Shading} choose the \menu{Mode}: \entry{aux}; and for \menu{Aux}: \entry{dens+1}. This adds density coloring to your dots, but currently your image looks nearly black. We add +1 here, because the density table contains overdensities with minimum values starting at -1, but we want to plot the overdensities here. 

	\item Select the \menu{Aux Axis} from the left menu to adjust its properties. Change here the \menu{Scaling} to \entry{log} instead of linear. This will apply a logarithmic scaling for the colors (since the logarithm is undefined for negative values, we had added +1 to \texttt{dens} beforehand). The web like structure should be visible now. 

	\item At \menu{Aux Shader} choose a color table you like, e.g. \entry{Cold} for blue shades.

	\item Switch to the \menu{Range} tab to fix the range of your density values:\\
\menu{Minimum Aux:} \entry{0.01}, \menu{Maximum Aux:} \entry{100} 
	This will make the structures more prominent.
    \end{minipage} 
    \hfill %
	\begin{minipage}[c]{0.3\linewidth}
    \includegraphics[width=\linewidth]{\dir/Screenshot-topcat-dens256-aux}\\
	\end{minipage}


	\item Inspect the same slice region at different redshifts, e.g. for snapnum 243 (z=1) or 36 (z=10.9). Just replace the snapnum in the query above, submit the query and view the results with Topcat. Can you see how the large scale structures formed from small overdensities in the much more homogenous early universe?

\end{itemize}


\end{document}
