%  aRT : API R - TerraLib                                                
%  Copyright (C) 2003-2008  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.

% \VignetteIndexEntry{Tables and Queries With aRT}

\documentclass{article}

\usepackage{Sweave}
\usepackage{hyperref}
\usepackage{aRT}

\title{Tables and Queries With \aRT}
\author{Pedro Ribeiro de Andrade Neto\\
Paulo Justiniano Ribeiro Júnior}
\date{\today}%September 10, 2008}

\begin{document}
\maketitle
\tableofcontents


\section{Introduction}

In \aRT\ there are seven 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}

These tables are encapsulated in the class \aRTtable, and we can query them
using \aRTtheme\ and \aRTquerier\ objects. The three classes are discussed
in this document, and we present here how to execute database queries,
selecting and joining attributes.

\begin{Schunk}
\begin{Sinput}
> library(aRT)
> conn = openConn(name = "root")
> if (any(showDbs(conn) == "tabletest")) deleteDb(conn, "tabletest", 
+     force = T)
> db = createDb(conn, db = "tabletest")
\end{Sinput}
\end{Schunk}

We will create a new layer, populate with some geometries, and, for each
type of table, 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 \aRTlayer\ objects.

\begin{Schunk}
\begin{Sinput}
> quantity = 10
> xc = round(runif(quantity), 2)
> yc = round(runif(quantity), 2)
> xy = cbind(xc, yc)
> xy.sp = SpatialPoints(xy)
> xy.spdf = SpatialPointsDataFrame(xy, data.frame(ID = paste(1:quantity)))
\end{Sinput}
\end{Schunk}

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

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 \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}

The most basic type of table in \aRT\ is \emph{static}. Static tables
store attributes with no variation in the time, for example the 
object \code{df} below:

\begin{Schunk}
\begin{Sinput}
> ID = getID(xy.spdf)
> 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)
> df
\end{Sinput}
\begin{Soutput}
   ID      norm     unif
1   1 11.984301 97.89574
2   2 19.713813 31.62978
3   3 15.566071 89.62430
4   4 21.704297 56.84160
5   5  6.500174 38.60381
6   6  6.332662 36.27251
7   7 11.063396 63.53696
8   8 32.873980 23.49168
9   9 34.684389 11.19028
10 10  7.358129 91.07037
\end{Soutput}
\end{Schunk}

To create a static table in a layer we use \code{createTable()}:

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

Table: "tstatic"
Type: static
Layer: "static"
Rows: 10
Attributes: 
    ID: character[16] (key)
\end{Soutput}
\end{Schunk}

Note that when we create a table it already has 10 rows with
the unique IDs from the geometries. To add this data to the table
we use \code{updateColumns}, and it also create new columns using
\code{colnames(df)}.

\begin{Schunk}
\begin{Sinput}
> updateColumns(tstatic, df)
> tstatic
\end{Sinput}
\begin{Soutput}
Object of class aRTtable

Table: "tstatic"
Type: static
Layer: "static"
Rows: 10
Attributes: 
    ID: character[16] (key)
    norm: numeric
    unif: numeric
\end{Soutput}
\end{Schunk}

And finally, \code{getData} is used for reading the data from the table:
\begin{Schunk}
\begin{Sinput}
> getData(tstatic)
\end{Sinput}
\begin{Soutput}
   ID   norm   unif
1   1 11.984 97.896
2  10  7.358 91.070
3   2 19.714 31.630
4   3 15.566 89.624
5   4 21.704 56.842
6   5  6.500 38.604
7   6  6.333 36.273
8   7 11.063 63.537
9   8 32.874 23.492
10  9 34.684 11.190
\end{Soutput}
\begin{Sinput}
> df2 = data.frame(ID, norm = unlist(lapply(ID, function(x) rnorm(1, 
+     20, 10))), uniff = unif)
> updateColumns(tstatic, df2)
> getData(tstatic)
\end{Sinput}
\begin{Soutput}
   ID   norm   unif  uniff
1   1 10.982 97.896 97.896
2  10 49.948 91.070 91.070
3   2  5.711 31.630 31.630
4   3 15.653 89.624 89.624
5   4 36.562 56.842 56.842
6   5 19.498 38.604 38.604
7   6 18.246 36.273 36.273
8   7  4.481 63.537 63.537
9   8 10.616 23.492 23.492
10  9 30.936 11.190 11.190
\end{Soutput}
\end{Schunk}

Note that, as \aRT\ automatically have created the rows, the order of the
rows in the result is not the same of the \code{df}.

To avoid it, we can create an empty 
table, and populate it manually. First, we need to use \code{gen=FALSE},
to avoid generate the rows of the table:

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

Table: "tstatic2"
Type: static
Layer: "static"
Rows: 0
Attributes: 
    ID: character[16] (key)
\end{Soutput}
\end{Schunk}

The argument \code{gen} indicates that the function must
create one row for each spatial object, and fill it with 
the ID of the spatial object. Now we need to create two
columns, one of integer type and other of real type, and then we
add some rows to the table.

\begin{Schunk}
\begin{Sinput}
> createColumn(tstatic2, "norm", type = "integer")
> createColumn(tstatic2, "unif", type = "numeric")
> addRows(tstatic2, df[1:5, ])
> getData(tstatic2)
\end{Sinput}
\begin{Soutput}
  ID norm   unif
1  1   12 97.896
2  2   20 31.630
3  3   16 89.624
4  4   22 56.842
5  5    7 38.604
\end{Soutput}
\begin{Sinput}
> addRows(tstatic2, df[6:10, ])
> getData(tstatic2)
\end{Sinput}
\begin{Soutput}
   ID norm   unif
1   1   12 97.896
2   2   20 31.630
3   3   16 89.624
4   4   22 56.842
5   5    7 38.604
6   6    6 36.273
7   7   11 63.537
8   8   33 23.492
9   9   35 11.190
10 10    7 91.070
\end{Soutput}
\end{Schunk}
But \code{addRows} only creates new elements in the table, it cannot change
the old elements. For example
\begin{Schunk}
\begin{Sinput}
> addRows(tstatic2, data.frame(ID = "1", norm = 2.1, unif = 0.3))
> getData(tstatic2)[1:3, ]
\end{Sinput}
\begin{Soutput}
  ID norm   unif
1  1   12 97.896
2  2   20 31.630
3  3   16 89.624
\end{Soutput}
\end{Schunk}

We can also create columns of string type, and set the maximum size of the
string, as:
\begin{Schunk}
\begin{Sinput}
> createColumn(tstatic2, "charcol", type = "character", length = 5)
> tstatic2
\end{Sinput}
\begin{Soutput}
Object of class aRTtable

Table: "tstatic2"
Type: static
Layer: "static"
Rows: 10
Attributes: 
    ID: character[16] (key)
    norm: integer
    unif: numeric
    charcol: character[5]
\end{Soutput}
\end{Schunk}

\code{updateColumns()} already calculates the type and the size of the data,
before creating the columns.

\subsection{External tables}

External tables are tables with no geometry associated. Therefore they are
created directly from the database, and we can not use \code{genID}. We
create external tables also using \code{createTable}, as in the next
example:

\begin{Schunk}
\begin{Sinput}
> texternal = createTable(db, "texternal", ID = "myid", length = 5)
> texternal
\end{Sinput}
\begin{Soutput}
Object of class aRTtable

Table: "texternal"
Type: external
Rows: 0
Attributes: 
    myid: character[5] (key)
\end{Soutput}
\end{Schunk}

Note that here we define the name of the key and also its length. It can
be defined when creating tables from layers too. As \code{texternal} is
an object of class \aRTtable\ we can use the same functions as described
for tables from layers.

\subsection{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(lstatic, type = "media")
\end{Sinput}
\end{Schunk}

A layer can have one, and only one, media table, and 
a media table does not have a name.
Also, 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: character[50] (key)
    media_name: character[255] (key)
\end{Soutput}
\end{Schunk}

A media table can be manipulated as all the other tables, but
new columns can not be created. In the next code we associate
web pages to each geometry of the layer, and we use
\code{addRows()} to fill the table.

\begin{Schunk}
\begin{Sinput}
> ID = getID(xy.spdf)
> url = "http://www.est.ufpr.br/~pedro/media/media"
> name = lapply(ID, function(x) sprintf("%s%s.html", url, x))
> name = unlist(name)
> df = data.frame(object_id = ID, media_name = 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:5, ]
\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
\end{Soutput}
\end{Schunk}

\begin{Schunk}
\begin{Sinput}
> lstatic
\end{Sinput}
\begin{Soutput}
Object of class aRTlayer

Layer: "static"
Database: "tabletest"
Number of points: 10
Projection Name: "NoProjection"
Projection Datum: "Spherical"
Tables:
    "tstatic": static
    "tstatic2": static
    "media_layer_1": media
\end{Soutput}
\end{Schunk}

\section{Attribute queries}
We can get all the data of a table using \code{getData()}.
But some operations are useful, for example selecting 
values that follows a condition, and it can be an attribute,
or spatial, or temporal condition. In the case of spatial
queries, here we only use the result to get spatial/attribute
queries. If you want to see how spatial queries work, see
\emph{Spatial Queries}.

To execute database queries, we need to create \aRTtheme\ objects.


\subsection{Join tables}

\subsection{Attribute restrictions}

\subsection{Spatial queries}


\section{Temporal Tables}

Temporal tables in \aRT\ work as static tables, noting that there are
three identifiers, instead of only one in static tables. The two others
are the initial and the final time.

These attributes are srtings, but they follows \tl\ model of dates.
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. It is a bit
different from \code{ISOdate}.

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

\subsection{Event tables}

An event table represents a temporal table which each element has a 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. We will use the same layer to create an event 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()}. The default value of this argument is
\code{"static"} when creating from layers and \code{"external"}
when creating directly from databases.

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

Table: "events"
Type: event
Layer: "static"
Rows: 0
Attributes: 
    ID: character[16] (key)
    time: date (key)
\end{Soutput}
\end{Schunk}

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}).

To fill 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 = getID(xy.spdf)
> hours = unlist(lapply(ID, function(x) round(runif(1, 0, 10), 
+     0)))
> time = unlist(lapply(hours, function(x) toDate(y = 2008, month = 3, 
+     day = 30, hour = x)))
> value = unlist(lapply(ID, function(x) runif(1, 1, 100)))
> df = data.frame(ID, value, time)
> df[1:7, ]
\end{Sinput}
\begin{Soutput}
  ID     value                time
1  1 37.292322 2008-03-30 04:00:00
2  2 13.109769 2008-03-30 05:00:00
3  3 98.482764 2008-03-30 01:00:00
4  4 32.542697 2008-03-30 03:00:00
5  5 58.776804 2008-03-30 04:00:00
6  6 80.230024 2008-03-30 02:00:00
7  7  4.076095 2008-03-30 01:00: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: 10
Attributes: 
    ID: character[16] (key)
    time: date (key)
    value: integer
\end{Soutput}
\end{Schunk}

\begin{Schunk}
\begin{Sinput}
> getData(tevent)
\end{Sinput}
\begin{Soutput}
   ID                time value
1   1 2008-03-30 04:00:00    37
2   2 2008-03-30 05:00:00    13
3   3 2008-03-30 01:00:00    98
4   4 2008-03-30 03:00:00    33
5   5 2008-03-30 04:00:00    59
6   6 2008-03-30 02:00:00    80
7   7 2008-03-30 01:00:00     4
8   8 2008-03-30 09:00:00    87
9   9 2008-03-30 06:00:00    54
10 10 2008-03-30 01:00:00    40
\end{Soutput}
\end{Schunk}

\subsection{Dynamic attribute tables}

Dynamic attribute tables work with geometries where one or more attributes
changes with the time. It works such as event tables, with the conceptual
difference that the identifier may repeat.


\subsection{Dynamic geometry tables}
(not implemented yet)

\subsection{Fully dynamic tables}
(not implemented yet)

\section{Temporal queries}

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 \aRTtheme\ object.

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

Theme: "events"
Layer: "static"
View: "events"
Tables: 
         "events"
         Attributes: "ID", "time", "value"

Attribute Restriction: ""
\end{Soutput}
\end{Schunk}
Note that the theme has two tables (\code{"static"} and \code{"events"}),
and \code{getData()} returns the join of them.


\subsection{Joining tables}


\subsection{Temporal slicing}

To slice the theme data, we need to create an \aRTquerier, with
\code{openQuerier()}. 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 \aRTquerier, we 
implement an algorithm to calculate the number of occourences in each hour, 
and the sums of \code{value}. Therefore we need an \aRTquerier\ sliced by hour.

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

Theme: "events"
Retrieves: geometry
Frames: 0/9
Elements: 0/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
\end{Sinput}
\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
