\section{Experimental Results}\label{secao:experimentos}

In this section, we present experiments carried out in the real-world scenario described in Section~\ref{sub:applicationscenario}. We first instantiated a transactional and multidimensional trajectory schema in a relational database. Next, we implemented algorithms for constructing and populating those schemas. In the sequel, we developed trajectory aggregate functions  for both environments. We have also used an open source BI OLAP tool for building data cubes from the trajectory multidimensional schema that was instantiated. Finally, we performed some queries and analysis in both environments, using the aggregation functions to illustrate the importance of their application. 

\subsection{Implementation of the trajectory schema in Object-Relational DBMS}

Due to the complex nature of trajectory data, we decided to use a DBMS equipped with object-relational features (Oracle). The use of this DBMS helped us define and implement trajectory data types and their corresponding functions. Figure \ref{fig:or_esquema_logico} shows the logical schema implemented in Oracle DBMS based on the transactional design pattern described in Section~\ref{sub:datatype}. Note that, with this design pattern, it is possible to link application thematic information with trajectory data and its events. For example, the Region table is linked to the Travel table, which stores truck trajectories. This relationship is instantiated when a truck trajectory is inside a region. Similarly, application thematic information is associated with events. For example, the Customer table is linked to Events denoting that some events may be related to a customer (e.g. delivery event).

\begin{figure}[tb]
	\centering
		\includegraphics[width=0.85\textwidth]{imagens/or_esquema_logico2.eps}
	\caption{DBMS Object-Relational schema for the Truck Fleet Control Application }
	\label{fig:or_esquema_logico}
\end{figure}

\subsection{Trajectory DW implementation}

The multidimensional schema was also implemented in a relational database. However, the proposed multidimensional design pattern is not oriented to any specific data model. Although this design pattern is represented in the object-oriented model, its implementation can be done in whatever target data model. Indeed, the choice of a target model is a decision that concerns the application designer. In our experiments, we decided to use the object-relational model since it facilitates the mapping between the design pattern model and the implementation model.  

Figure~\ref{fig:esquema_or_trdw} depicted the logical multidimensional schema generated from the instantiation of our multidimensional design pattern for trajectories. This logical model contains application-defined hierarchies for the \textit{Moving Object} dimension (Dim\_Truck) and two links for the \textit{Time} (Dim\_Time) dimension, corresponding to the begin and end time for the travel and event facts.  The link named $trajectory$ between \textit{Event} and \textit{Travel} facts represents the trajectory degenerated dimension, illustrated in Figure~\ref{fig:designpatternDW}.

\begin{figure}[tb] 
	\centering
		\includegraphics[width=0.9\textwidth]{imagens/dw_or2.eps} 
	\caption{DW Object-Relational schema for the Truck Fleet Control Application}
	\label{fig:esquema_or_trdw}
\end{figure}

\subsection{Aggregation function implementations}

We used the generic signature for trajectory aggregation functions to implement some functions regarded as crucial to the application scenario. Since these functions have a parameter that is a complex object (i.e. \textit{TrajectoryType}), we used the \textit{ODCIAggregate} framework built in Oracle to implement them. The creation of user-defined aggregation functions requires the implementation of four routines that are mandatory by the framework interface. Some of the functions that have been created in our experiments are:

\begin{itemize}
\item TR\_AVG\_DURATION - computes the average trajectory duration 
\item TR\_AVG\_SPEED - computes the average trajectory speed
\item TR\_MAX\_DURATION (and MIN) - computes the maximum and minimum trajectory duration
\item TR\_MAX\_LENGTH (and MIN) - computes the maximum and minimum trajectory length
\item TR\_AVG\_QTD\_DURATION (LENGTH) - computes the average volume of product delivered per duration (per LENGTH)
\item TR\_AVG\_EVT\_DELIVERY - computes the average number of deliveries per trajectory
\end{itemize}

\subsection{Analysis in transactional environment}

Transactional environment is used for real-time analysis. In this application scenario, typical queries are to list where trucks are, or which deliveries they have done along the day. For example, we present below two such queries that have been submitted to the transactional schema:

\begin{itemize}
	\item for each truck, list the amount of travels done in Osasco city, its average trajectory duration and the average number of deliveries (i.e. attendance) (see Figure \ref{fig:query5});
	\item for truck 11, list its route (Figure \ref{fig:query2} \textit{a}) and the location of its deliveries that had already been accomplished in the current day (Figure \ref{fig:query2} \textit{b}). Figure \ref{fig:query2map} displays the results using Google Maps. 
\end{itemize}

\begin{figure}[tb]
	\lstinputlisting[language=SQL]{queries//query5.sql}
	\caption{Query returning the amount of trips, the average duration and the number of events for each truck within a given time period and spatial region}
	\label{fig:query5}
\end{figure}


\begin{figure}[tb]
	\lstinputlisting[language=SQL]{queries//query2.sql}
	\caption{Real time queries for retrieving (a) geometry of a truck trajectory and (b) beginning location for the events of type "Atendimento"}
	\label{fig:query2}
\end{figure}

\begin{figure}[tb]
	\centering
		\includegraphics[width=0.9\textwidth]{imagens//query2map.eps}
	\caption{Query results of Figure \ref{fig:query2} plotted on Google Maps}
	\label{fig:query2map}
\end{figure}

\subsection{Analysis in multidimensional environment}

Analysis in a multidimensional environment is useful for discovering patterns of behavior. For example, an interesting analysis is to identify the trucks exceeding the speed limit over a hundred times within "Sudeste" region. In addition, do this verification for trucks that belong to the "Company X" during the first semester (Figura \ref{fig:querydw}). We also present an example of trajectory cube created with Pentaho BI Server\footnote{\url{http://community.pentaho.com/projects/bi_platform/}} to analyse the average speed and average length of moving object trajectories that travelled inside some specific areas (Figure ~\ref{fig:pentaho_trdw}).

\begin{figure}[tb]
	\lstinputlisting[language=SQL]{queries//querydw.sql}
	\caption{DW query for recovering the average speed of trucks with more than 100 events of type "Excesso de Velocidade" within a given time interval grouped by month and truck}
	\label{fig:querydw}
\end{figure}

\begin{figure}[tb] 
	\centering	
		\includegraphics[width=0.8\textwidth]{imagens/pentaho_tr_cube.eps} 
	\caption{Truck Fleet Control Data Cube}
	\label{fig:pentaho_trdw}
\end{figure}

In summary, the multidimensional environment maintains aggregate information in data cubes, which allows speeding up query execution for trajectory aggregation functions, group-bys, cross-tabs, and subtotals whereas the transactional environment is more adequate to non-aggregate queries that retrieves small number of records at a given time.






