source: trunk/tutorial/model_tutorial.tex @ 35

%
%  HOW TO BUILD TOOLMAP DATAMODEL
%
%  Created by Lucien Schreiber on 2013-02-19.
%  Copyright (c) 2013. All rights reserved.
%

\documentclass[a4paper, 12pt]{article}
\usepackage{crealp-report}
\usepackage{upquote} %to force Latex not substitute ' by `
\usepackage{tocbibind}
\usepackage{mdwlist}
%\usepackage{natbib}

\begin{document}
\crealptitle {Tutorial} {How to create a ToolMap datamodel using TmDmCreator} {Lucien Schreiber} {lucien.schreiber@crealp.vs.ch}
\tableofcontents
\pagebreak

\section{Introduction}
This tutorial explains how to create a ToolMap project manually. This approach has the following advantages:
\begin{enumerate*}
  \item It ensures that the ID remain consistent
  \item It could generate a multilingual model
  \item It allows better monitoring of model changes
\end{enumerate*}
The main disadvantage of this approach is the lack of user interface as well as the need for the user to have some knowledge of SQL. Finally, this approach has been developed to meet the need for rigor in the management of the Swiss geological data model.


\section{Conceptual Workflow}
The diagram shown in figure~\ref{fig:conceptual-workflow} illustrates the proposed workflow. User edits the user\_structure.sql and user\_content.txt files. These files as well as base\_structure.sql are used by the software TmDmCreator to produces either:
\begin{enumerate*}
  \item a SQL file defining the project (output 1)
  \item a ToolMap project (output 2)
\end{enumerate*}

\begin{figure} [htbp]
  \centering
  \includegraphics[width=1\textwidth]{img/workflow.pdf}
  \caption{Conceptual workflow}
  \label{fig:conceptual-workflow}
\end{figure}



\section{Data needed}
For proper operation,  TmDmCreator requires the following files:
%\begin{itemize}
\begin{description*}
  \item[base\_structure.sql]\hfill \\ contains the necessary SQL code base for all ToolMap projects. This file should normally not be edited by users
  \item[user\_structure.sql]\hfill \\ contains the SQL structure describing the layers attributes
  \item[user\_content.txt]\hfill \\ Is a tabular file (editable in Excel for example) containing the definition of layers, objects, and attribute values.
\end{description*}
%\end{itemize}
The recommended way to work with user\_structure.sql and user\_content.txt is described below

\clearpage
\section{Preparing user data}

\subsection{Layers}
Open user\_content.txt using a spreadsheet and edit the thematic\_layers part. Each of the layers that we want to export should appear here. The structure is as follows (see figure~\ref{fig:layers}):
\begin{description*}
  \item [LAYER\_INDEX] unique identifier of the layer
  \item [TYPE\_CD] layer spatial type as follow
    \begin{description*}
      \item [0] = Line
      \item [1] = Point
      \item [2] = Polygon
    \end{description*}
  \item [LAYER\_NAME] the layer name. This name will be given to the SHP file when exporting
\end{description*}

Make sure you choose an Unicode format (Unicode Text (*.txt) or UTF-16 Unicode Text (*.txt)) when saving from the spreadsheet.

\begin{figure} [hbp]
  \centering
  \includegraphics[width=.6\textwidth]{img/layers.png}
  \caption{List of layers as shown in user\_content.txt}
  \label{fig:layers}
\end{figure}

\subsection{Objects}
\label{sec:objects}

Edit the file user\_content.txt to add objects. They must have the following structure (See figure:~\ref{fig:objects}):
\begin{description*}
  \item [OBJECT\_ID] object unique ID.
  \item [OBJECT\_CD] object code, should not necessarily be unique
  \item [OBJECT\_TYPE\_CD] object spatial type, uses same values as those described above for TYPE\_CD in thematic\_layers
  \item [THEMATIC\_LAYERS\_LAYER\_INDEX] the index of the layer that the object refers to. The value 1 shown in the example (Figure~\ref{fig:objects}) therefore relates to the theme Boreholes\_PT.
  \item [OBJECT\_DESC\_0,1,2,3,4,5] object description in up to 5 languages.
  \item [OBJECT\_ISFREQ] Set to 1 for frequent objects and 0 otherwise. This parameter is only taken into account for line type objects. Set to 0 for all point or polygon objects.
  \item [SYMBOL\_CD] leave empty
  \item [RANK] leave empty
  \item [REMARK] leave empty
\end{description*}

\begin{figure} [hbp]
  \centering
  \includegraphics[height=.9\textheight]{img/objects.png}
  \caption{Objects structure as described in user\_content.txt}
  \label{fig:objects}
\end{figure}


\subsection{Attributes structure}
Edit the file user\_structure.sql with Notepad (or even better with Notepad + +). For each layer containing attributes, there must be a SQL code of the type:

\crealplisting{SQL}
\begin{lstlisting}
-- layer_at1 --
CREATE TABLE `layer_at1` (
`OBJECT_ID` int(10) unsigned NOT NULL,
-- add user attributes here --
PRIMARY KEY (`OBJECT_ID`),
KEY `LAYER_ATX_FKIndex1` (`OBJECT_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
\end{lstlisting}
This code is the basic template for creating an attribute table. The number after layer\_at (see line 2) indicates the layer index and refers to the LAYER\_INDEX column in user\_content.txt. In our example layer\_at1 describe the attributes for the layer Boreholes\_PT. User attributes can then be added on line 4 of this template.
Below are described the five attributes that can be used in a ToolMap data model as well as the corresponding SQL code


\subsubsection {Enumeration}
\label{sec:enumeration}
\crealplisting{SQL}
\begin{lstlisting}
`D_C_UNDERG` int(11) DEFAULT NULL COMMENT 'ENUMERATION',
\end{lstlisting}
If you add such fields, then you must also fill the list of supported values (see section~\ref{sec:attribute-values}).
\subsubsection {Text}
\begin{lstlisting}
`DESCRIPT` varchar(255) DEFAULT NULL,
\end{lstlisting}
The number next to the keyword varchar indicates the maximum text length. 
\subsubsection {Integer}
\begin{lstlisting}
`NUM_REF` int(11) DEFAULT NULL,       
\end{lstlisting}
There is no special option for integer fields


\subsubsection {Float}

\begin{lstlisting}
`TEMP` decimal(5,2) DEFAULT NULL,
\end{lstlisting}
The two digits next to the keyword decimal indicate the field precision and scale. In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point. In this case, values that can be stored range from -999.99 to 999.99.
\subsubsection {Date}

\begin{lstlisting}
`REF_DATE` date DEFAULT NULL,
\end{lstlisting}
There is no special option for date fields


\subsection{Attributes values}
\label{sec:attribute-values}
For each enumeration field  previously added in the user\_structure.sql file, it is necessary to define the allowed values. Therefore it is necessary to edit the attributes section of user\_content.txt. The structure of the attributes section is shown in Figure~\ref{fig:attributs}. This table is divided into two parts, the first three columns describe the attribute fields, the remaining columns describe the values supported by these fields. Below is a description of each column.

\begin{description*}
  \item [ATTRIBUT\_ID] attribute unique ID.
  \item [LAYER\_INDEX] the index of the layer that the attribute refers to. The value 1 shown in the example (Figure~\ref{fig:attributs}, row 42 and 43) therefore relates to the theme Boreholes\_PT.
  \item [ATTRIBUT\_NAME] attribute name. This name will be used as the column name in the exported SHP. Some limitations apply to SHP format for column names, for more information you can refer to \url{http://en.wikipedia.org/wiki/Shapefile#Shapefile\_attribute\_format\_.28.dbf.29} or \url{http://www.gdal.org/ogr/drv\_shapefile.html} 
  \item [CATALOG\_ID] attribute value unique ID
  \item [CODE] attribute value code, should not necessarily be unique
  \item [DESCRIPTION\_0,1,2,3,4,5] attribute value description in up to 5 languages. The order of language is not important, but it must be identical to the one chosen for the objects (see~\ref{sec:objects}).

\end{description*}

\begin{figure} [htbp]
  \centering
  \includegraphics[width=.9\textwidth]{img/attributs.png}
  \caption{Attributes section structure}
  \label{fig:attributs}
\end{figure}

\section{Running TmDmCreator}

TmDmCreator is a command-line utility. As an input, it takes the 3 files described in details above (base structure.sql, user\_structure.sql, user\_content.txt) and produces a resulting SQL file. Its behavior may be controlled using different parameters described bellow (see figure~\ref{fig:command-line-output}).


\subsection{Optional parameters}
\begin{description*}
  \item [--verbose] Be more verbose, specifically when an error occur. 
  \item [--toolmap] Write output directly into a ToolMap project instead of a SQL file (not implemented actually). 
  \item [--overwrite] when specified, the output file will be erased if existing. 
  \item [--language =<num>] specify the language column to use. Column numbering starts at 0. This option allows multilingual support.
\end{description*}

\subsection{Mandatory parameters}
\begin{description*}
  \item [base structure sql file] base\_structure.sql file name
  \item [user structure sql file] user\_structure file name
  \item [user content txt file] user\_content.txt file name
  \item [result file] either a sql file name for SQL output or a directory name for a ToolMap output.
\end{description*}

\subsection{Sample}
A typical TmDmCreator command like will look like the following: 

\crealplisting{bash}
\begin{lstlisting}
TmDmCreator --verbose --language=0 base_structure.sql user_structure.sql user_content.txt result.sql
\end{lstlisting}

\begin{figure} [htbp]
        \centering
    \includegraphics[width=1\textwidth]{img/command-line-tmdmcreator.png}
    \caption{Command line output from TmDmCreator}
    \label{fig:command-line-output}
\end{figure}




\end{document}