%  aRT : API R - TerraLib                                                
%  Copyright (C) 2003-2005  LEG                                          
%                                                                        
%  This program is free software; you can redistribute it and/or modify  
%  it under the terms of the GNU General Public License as published by  
%  the Free Software Foundation; either version 2 of the License, or     
%  (at your option) any later version.                                   
%                                                                        
%  This program is distributed in the hope that it will be useful,       
%  but WITHOUT ANY WARRANTY; without even the implied warranty of        
%  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         
%  GNU General Public License for more details.                          
%                                                                        
%  You should have received a copy of the GNU Lesser General Public      
%  License along with this library.

\documentclass{article}

\usepackage{Sweave}

\newcommand{\aRT}{{\tt aRT}}
\newcommand{\aRTgeo}{{\tt aRTgeometry}}
\newcommand{\aRTtable}{{\tt aRTtable}}
\newcommand{\spp}{{\tt sp}}
\newcommand{\code}[1]{{\tt #1}}
\newcommand{\tl}{\textsf{TerraLib}}


\title{Tables and Queries With \aRT}
\author{Pedro Ribeiro de Andrade Neto\\
Paulo Justiniano Ribeiro Júnior}

\begin{document}
\maketitle
%\tableofcontents


\section{Introduction}

In \aRT\ there are six types of table, implementing the \tl\
models. They are:

\begin{itemize}
\item static,
\item external,
\item media,
\item event,
\item dynatt,
\item dyngeom, and
\item alldyn.
\end{itemize}

This tables are encapsulated in the class \aRTtable, and are discussed
in this document. We also present here how to execute database queries,
selecting and joining attributes.

\begin{Schunk}
\begin{Sinput}
> library(aRT)
\end{Sinput}
\begin{Soutput}
Loading required package: sp

-------------------------------------------------
API R-TERRALIB
aRT version 0.4-0 (2005-08-27) is now loaded
-------------------------------------------------
\end{Soutput}
\begin{Sinput}
> aRTsilent(TRUE)
\end{Sinput}
\begin{Soutput}
[1] TRUE
\end{Soutput}
\begin{Sinput}
> conn = openConn()
> if (any(showDbs(conn) == "tabletest")) deleteDb(conn, 
+     "tabletest", force = T)
> db = createDb(conn, db = "tabletest")
\end{Sinput}
\end{Schunk}

For each type of table, we create a new layer, populate with
some geometries, and then we discuss how to populate the table, and
how to make temporal and attributes queries, using the other \aRT\ classes.
Note that spatial querier can be done directly using \code{aRTlayer}
objects.

\section{Class \aRTtable\ and temporal data}
All non-geometric data in \aRT\ is stored in tables, and the way to
exchange this type of information with the database is using
\aRTtable objects. \aRTtable objects are created and
opened from \code{aRTlayer} objects, using \code{createTable} and
\code{openTable}, respectively.
Table data is read from and written to databases using 
\code{data.frame}. \code{row.names} are not used to represent IDs in
\aRT, because IDs can be unsuficient to describe temporal data.

\section{Static tables}

\begin{Schunk}
\begin{Sinput}
> quantity = 20
> xc = round(runif(quantity), 2)
> yc = round(runif(quantity), 2)
> xy = cbind(xc, yc)
> xy.sp = SpatialPoints(xy)
> xy.points.aRT = as.aRTgeometry(xy.sp)
\end{Sinput}
\end{Schunk}

\begin{Schunk}
\begin{Sinput}
> lstatic = createLayer(db, l = "static")
> addPoints(lstatic, xy.sp)
\end{Sinput}
\end{Schunk}

To create an event table, we need to set \code{type="event"}
at \code{createTable}. When an event table is created, it
already contains three attributes: \code{id}, \code{itime} and
\code{ftime}, and they are keys.
We recommend not to generate ids (\code{gen=FALSE}), because
it would also generate \code{itime} and \code{ftime}, and put
zero in all time values (\code{0000-00-00 00:00:00}).

\begin{Schunk}
\begin{Sinput}
> tstatic = createTable(lstatic, "static", gen = FALSE)
> tstatic
\end{Sinput}
\begin{Soutput}
Object of class aRTtable

Table: "static"
Type: static
Layer: "static"
Rows: 0
Attributes: 
    id: string[16] (key)
\end{Soutput}
\end{Schunk}
\begin{Schunk}
\begin{Sinput}
> id = xy.points.aRT$id
> norm = unlist(lapply(id, function(x) rnorm(1, 20, 10)))
> unif = unlist(lapply(id, function(x) runif(1, 1, 100)))
> df = data.frame(id, norm, unif)
> createColumn(tstatic, "norm", type = "i")
> createColumn(tstatic, "unif", type = "i")
> addRows(tstatic, df)
> getData(tstatic)[1:10, ]
\end{Sinput}
\begin{Soutput}
   id norm unif
1   1   40   37
2   2   33   13
3   3   17   98
4   4   23   33
5   5    7   59
6   6   11   80
7   7    6    4
8   8   16   87
9   9   37   54
10 10   19   40
\end{Soutput}
\end{Schunk}

\section{Temporal Tables}

To convert temporal dates to \aRT\ format we will use \code{toDate()}.
This function gets as arguments integer variables \code{year = 0},
\code{month = 1}, \code{day = 1}, \code{hour = 0}, \code{minute = 0} and
\code{second = 0} and returns a string describing the date:

\begin{Schunk}
\begin{Sinput}
> toDate(year = 2005, month = 8, day = 7, hour = 6, sec = 5)
\end{Sinput}
\begin{Soutput}
[1] "2005-08-07 06:00:05"
\end{Soutput}
\end{Schunk}

\subsection{Event tables}

An event table represents a temporal table which each element has static 
geometry and attributes, but it occours in a time interval.
When we are using an event table, we do not need static tables, because
each event is unique, and therefore we can put all attributes in the same
table.

\begin{Schunk}
\begin{Sinput}
> lpoints = lstatic
\end{Sinput}
\end{Schunk}

To create an event table, we need to set \code{type="event"}
at \code{createTable()}. When an event table is created, it
already contains three attributes: \code{id}, \code{itime} and
\code{ftime}, and they are keys.
We recommend not to generate ids (\code{gen=FALSE}), because
it would also generate \code{itime} and \code{ftime}, and put
zero in all time values (\code{0000-00-00 00:00:00}).

\begin{Schunk}
\begin{Sinput}
> tevent = createTable(lpoints, "events", type = "event", 
+     gen = FALSE)
> tevent
\end{Sinput}
\begin{Soutput}
Object of class aRTtable

Table: "events"
Type: event
Layer: "static"
Rows: 0
Attributes: 
    id: string[16] (key)
    itime: date (key)
    ftime: date (key)
\end{Soutput}
\end{Schunk}

To generate the event table we will generate a random attribute 
\code{value} with duration of 59 minutes, all in the same day:
\begin{Schunk}
\begin{Sinput}
> id = xy.points.aRT$id
> hours = unlist(lapply(id, function(x) round(runif(1, 
+     0, 10), 0)))
> itime = unlist(lapply(hours, function(x) toDate(y = 2005, 
+     month = 3, day = 30, hour = x)))
> ftime = unlist(lapply(hours, function(x) toDate(y = 2005, 
+     month = 3, day = 30, hour = x, minute = 59)))
> value = unlist(lapply(id, function(x) runif(1, 1, 100)))
> df = data.frame(id, value, itime, ftime)
> df[1:7, ]
\end{Sinput}
\begin{Soutput}
  id    value               itime               ftime
1  1 52.58036 2005-03-30 08:00:00 2005-03-30 08:59:00
2  2 70.41540 2005-03-30 09:00:00 2005-03-30 09:59:00
3  3 65.08989 2005-03-30 06:00:00 2005-03-30 06:59:00
4  4 97.01515 2005-03-30 03:00:00 2005-03-30 03:59:00
5  5 80.05820 2005-03-30 02:00:00 2005-03-30 02:59:00
6  6 78.30764 2005-03-30 08:00:00 2005-03-30 08:59:00
7  7 70.74230 2005-03-30 04:00:00 2005-03-30 04:59:00
\end{Soutput}
\end{Schunk}

As the table already has three attributes, we need only to create the 
column \code{value}, and then we can add the rows:
\begin{Schunk}
\begin{Sinput}
> createColumn(tevent, "value", type = "i")
> addRows(tevent, df)
> tevent
\end{Sinput}
\begin{Soutput}
Object of class aRTtable

Table: "events"
Type: event
Layer: "static"
Rows: 20
Attributes: 
    id: string[16] (key)
    itime: date (key)
    ftime: date (key)
    value: integer
\end{Soutput}
\end{Schunk}

\begin{Schunk}
\begin{Sinput}
> getData(tevent)
\end{Sinput}
\begin{Soutput}
   id               itime               ftime value
1   1 2005-03-30 08:00:00 2005-03-30 08:59:00    53
2   2 2005-03-30 09:00:00 2005-03-30 09:59:00    70
3   3 2005-03-30 06:00:00 2005-03-30 06:59:00    65
4   4 2005-03-30 03:00:00 2005-03-30 03:59:00    97
5   5 2005-03-30 02:00:00 2005-03-30 02:59:00    80
6   6 2005-03-30 08:00:00 2005-03-30 08:59:00    78
7   7 2005-03-30 04:00:00 2005-03-30 04:59:00    71
8   8 2005-03-30 08:00:00 2005-03-30 08:59:00    85
9   9 2005-03-30 09:00:00 2005-03-30 09:59:00    78
10 10 2005-03-30 05:00:00 2005-03-30 05:59:00     7
11 11 2005-03-30 00:00:00 2005-03-30 00:59:00    68
12 12 2005-03-30 09:00:00 2005-03-30 09:59:00    90
13 13 2005-03-30 07:00:00 2005-03-30 07:59:00    18
14 14 2005-03-30 00:00:00 2005-03-30 00:59:00    62
15 15 2005-03-30 09:00:00 2005-03-30 09:59:00    43
16 16 2005-03-30 09:00:00 2005-03-30 09:59:00     1
17 17 2005-03-30 09:00:00 2005-03-30 09:59:00    15
18 18 2005-03-30 10:00:00 2005-03-30 10:59:00     4
19 19 2005-03-30 06:00:00 2005-03-30 06:59:00    36
20 20 2005-03-30 09:00:00 2005-03-30 09:59:00    47
\end{Soutput}
\end{Schunk}

\subsection{Dynamic attribute tables}

\subsection{Dynamic geometry tables}

\subsection{Fully dynamic tables}

\subsection{Temporal slicing}

We can get all table data with \code{getData()}, but if it is a temporal
table, we get it sliced. To do it, we need first to create 
an \code{aRTtheme} object.

\begin{Schunk}
\begin{Sinput}
> theme = createTheme(lpoints, "events")
> theme
\end{Sinput}
\begin{Soutput}
Object of class aRTtheme

Theme: "events"
Layer: "static"
Tables: 
         "static"
         Attributes: "id", "norm", "unif"

         "events"
         Attributes: "id", "itime", "ftime", "value"

Attribute Restriction: ""
\end{Soutput}
\begin{Sinput}
> getData(theme)[1:7, ]
\end{Sinput}
\begin{Soutput}
  id norm unif id.1               itime               ftime value
1  1   40   37    1 2005-03-30 08:00:00 2005-03-30 08:59:00    53
2 10   19   40   10 2005-03-30 05:00:00 2005-03-30 05:59:00     7
3 11   18   33   11 2005-03-30 00:00:00 2005-03-30 00:59:00    68
4 12    4   80   12 2005-03-30 09:00:00 2005-03-30 09:59:00    90
5 13   11   25   13 2005-03-30 07:00:00 2005-03-30 07:59:00    18
6 14   31   16   14 2005-03-30 00:00:00 2005-03-30 00:59:00    62
7 15   50   99   15 2005-03-30 09:00:00 2005-03-30 09:59:00    43
\end{Soutput}
\end{Schunk}
Note that the theme has two tables (\code{"static"} and \code{"events"}),
and \code{getData()} returns the join of them.

To slice the theme data, we need to create an \code{aRTquerier}, with
\code{createQuerier()}. This function takes as argument \code{chronon},
representing the type of slides to be produced. It can be \code{"second"},
\code{"month"}, \code{"season"}, \code{"year"}, \code{"weekofyear"} and others,
and the default is \code{"nochronon"}. To exemplify using \code{aRTquerier}, 
 we implement an algorithm to calculate the number of occourences in
each hour, and the sums of \code{value}. Therefore we need an \code{aRTquerier}
sliced by hour.

\begin{Schunk}
\begin{Sinput}
> querier = createQuerier(theme, chronon = "hour")
> querier
\end{Sinput}
\begin{Soutput}
Object of class aRTquerier

Slides: 11
Current slide: -1
Elements of current slide: 0
\end{Soutput}
\end{Schunk}

To get data from the querier there are two functions. \code{nextSlide()}
loads the next slide, returning the number of elements of it, and 
\code{getData()} returns one of the elements of the slide, read from the
database. Both functions do not take any argument.

\begin{Schunk}
\begin{Sinput}
> qtde = summary(querier)$slides
> qtde
\end{Sinput}
\begin{Soutput}
[1] 11
\end{Soutput}
\begin{Sinput}
> time = occourrences = total.value = NULL
> for (i in 1:qtde) {
+     value = nextSlide(querier)
+     if (value != 0) {
+         occourrences = c(occourrences, value)
+         sum = 0
+         for (j in 1:value) {
+             data = getData(querier)
+             sum = sum + as.integer(data$data$value)
+         }
+         time = c(time, data$data$itime)
+         total.value = c(total.value, sum)
+     }
+ }
> data.frame(time, occourrences, total.value)
\end{Sinput}
\begin{Soutput}
                  time occourrences total.value
1  2005-03-30 00:00:00            2         130
2  2005-03-30 02:00:00            1          80
3  2005-03-30 03:00:00            1          97
4  2005-03-30 04:00:00            1          71
5  2005-03-30 05:00:00            1           7
6  2005-03-30 06:00:00            2         101
7  2005-03-30 07:00:00            1          18
8  2005-03-30 08:00:00            3         216
9  2005-03-30 09:00:00            7         344
10 2005-03-30 10:00:00            1           4
\end{Soutput}
\end{Schunk}

\section{Media tables}

Media tables are useful when building databases that will be used 
in \textsf{TerraView}, or another \tl-based GIS. It associates
a web page to a double-click in a drawn geometry. This type of
table can be created using \code{type="media"}:

\begin{Schunk}
\begin{Sinput}
> mediatable = createTable(lpoints, type = "media")
\end{Sinput}
\end{Schunk}
Each media table has two, and only two, atributes: 
\code{object\_id}, the link to geometries,
and \code{media\_name}, a web address.

\begin{Schunk}
\begin{Sinput}
> mediatable
\end{Sinput}
\begin{Soutput}
Object of class aRTtable

Type: media
Layer: static
Rows: 0
Attributes: 
    object_id: string[50] (key)
    media_name: string[255] (key)
\end{Soutput}
\end{Schunk}

Excusing creating columns, a media table can be manipulated
as all the other tables. The next code we associate
web pages to each geometry of the layer, inserting
with \code{addRows()}.

\begin{Schunk}
\begin{Sinput}
> object_id = xy.points.aRT$id
> url = "http://www.est.ufpr.br/~pedro/media/media"
> html = ".html"
> media_name = unlist(lapply(object_id, function(x) paste(url, 
+     x, html, sep = "")))
> df = data.frame(object_id, media_name)
> addRows(mediatable, df)
\end{Sinput}
\end{Schunk}

To check if it is correct, we can use \code{getData()}:
\begin{Schunk}
\begin{Sinput}
> getData(mediatable)[1:7, ]
\end{Sinput}
\begin{Soutput}
  object_id                                      media_name
1         1 http://www.est.ufpr.br/~pedro/media/media1.html
2         2 http://www.est.ufpr.br/~pedro/media/media2.html
3         3 http://www.est.ufpr.br/~pedro/media/media3.html
4         4 http://www.est.ufpr.br/~pedro/media/media4.html
5         5 http://www.est.ufpr.br/~pedro/media/media5.html
6         6 http://www.est.ufpr.br/~pedro/media/media6.html
7         7 http://www.est.ufpr.br/~pedro/media/media7.html
\end{Soutput}
\end{Schunk}

\section*{References}
\begin{description}
\item Chambers, J.M., 1998, Programming with data, a guide to the S language.
Springer, New York.
\end{description}

\end{document}

% vim:syntax=tex
