source: trunk/tutorial/model_tutorial.tex @ 13

Revision 13, 8.0 KB checked in by lucsch, 12 years ago (diff)

Updating tutorial first version describing how to prepare user data

Line 
1%
2%  HOW TO BUILD TOOLMAP DATAMODEL
3%
4%  Created by Lucien Schreiber on 2013-02-19.
5%  Copyright (c) 2013. All rights reserved.
6%
7
8\documentclass[a4paper, 12pt]{article}
9\usepackage{crealp-report}
10\usepackage{upquote} %to force Latex not substitute ' by `
11\usepackage{tocbibind}
12\usepackage{mdwlist}
13%\usepackage{natbib}
14
15\begin{document}
16\crealptitle {Tutorial} {How to create a ToolMap datamodel using TmDmCreator} {Lucien Schreiber} {lucien.schreiber@crealp.vs.ch}
17\tableofcontents
18\pagebreak
19
20\section{Introduction}
21This tutorial explains how to create a ToolMap project manually. This approach has the following advantages:
22\begin{enumerate*}
23  \item It ensures the IDs used
24  \item It generates a multilingual model
25  \item It allows better monitoring of model changes
26\end{enumerate*}
27The 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.
28
29
30\section{Conceptual Workflow}
31The 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:
32\begin{enumerate*}
33  \item a SQL file defining the project (output 1)
34  \item a ToolMap project (output 2)
35\end{enumerate*}
36
37\begin{figure} [htbp]
38        \centering
39    \includegraphics[width=1\textwidth]{img/workflow.pdf}
40    \caption{Conceptual workflow}
41    \label{fig:conceptual-workflow}
42\end{figure}
43
44
45
46\section{Data needed}
47In order to produce a ToolMap project, TmDmCreator needs the following files:
48%\begin{itemize}
49    \begin{description*}
50  \item[base\_structure.sql]\hfill \\ contains the necessary SQL code base for all ToolMap projects. This file should normally not be edited by users
51  \item[user\_structure.sql]\hfill \\ contains the SQL structure describing the layers attributes
52  \item[user\_content.txt]\hfill \\ Is a tabular file (editable in Excel for example) containing the definition of layers, objects, and attribute values.
53\end{description*}
54%\end{itemize}
55The recommended way to work with user\_structure.sql and user\_content.txt is described below
56
57
58\section{Preparing user data}
59
60\subsection{Layers}
61Open 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}):
62\begin{description*}
63  \item [LAYER\_INDEX] unique identifier of the layer
64  \item [TYPE\_CD] layer spatial type as follow
65    \begin{description*}
66      \item [0] = Line
67      \item [1] = Point
68      \item [2] = Polygon
69    \end{description*}
70  \item [LAYER\_NAME] the layer name. This name will be given to the SHP file when exporting
71\end{description*}
72
73\begin{figure} [hbp]
74        \centering
75    \includegraphics[width=.6\textwidth]{img/layers.png}
76    \caption{List of layers as shown in user\_content.txt}
77    \label{fig:layers}
78\end{figure}
79
80\subsection{Objects}
81\label{sec:objects}
82
83Edit the file user\_content.txt to add objects. They must have the following structure (See figure:~\ref{fig:objects}):
84\begin{description*}
85  \item [OBJECT\_ID] object unique ID.
86  \item [OBJECT\_CD] object code, should not necessarily be unique
87  \item [OBJECT\_TYPE\_CD] object spatial type, uses same values as those described above for TYPE\_CD in thematic\_layers
88  \item [THEMATIC\_LAYERS\_LAYER\_INDEX] the index of the layer that the object refers to. The value 1 shown in the example (Figure 2) therefore relates to the theme Boreholes\_PT.
89  \item [OBJECT\_DESC\_0,1,2,3,4,5] object description in up to 5 languages.
90  \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.
91  \item [SYMBOL\_CD] leave empty
92  \item [RANK] leave empty
93  \item [REMARK] leave empty
94\end{description*}
95
96\begin{figure} [hbp]
97        \centering
98    \includegraphics[height=.9\textheight]{img/objects.png}
99    \caption{Objects structure as described in user\_content.txt}
100    \label{fig:objects}
101\end{figure}
102
103
104\subsection{Attributes structure}
105Edit the file user\_structure.sql with Notepad (or even better with Notepad + +). For each topic containing attributes, there must be a SQL code of the type:
106
107\crealplisting{SQL}
108\begin{lstlisting}
109-- layer_at1 --
110CREATE TABLE `layer_at1` (
111  `OBJECT_ID` int(10) unsigned NOT NULL,
112  -- add user attributes here --
113  PRIMARY KEY (`OBJECT_ID`),
114  KEY `LAYER_ATX_FKIndex1` (`OBJECT_ID`)
115) ENGINE=MyISAM DEFAULT CHARSET=utf8
116\end{lstlisting}
117This 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.
118Below are described the five attributes that can be used in a ToolMap data model as well as the corresponding SQL code
119\subsubsection {Enumeration} 
120    \crealplisting{SQL}
121    \begin{lstlisting}
122    `D_C_UNDERG` int(11) DEFAULT NULL COMMENT 'ENUMERATION',
123    \end{lstlisting}
124    If you add such fields, then you must also fill the list of supported values (see section~\ref{sec:attribute-values}).
125    \subsubsection {Text}
126    \begin{lstlisting}
127    `DESCRIPT` varchar(255) DEFAULT NULL,
128    \end{lstlisting}
129    The number next to the keyword varchar indicates the maximum text length.
130    \subsubsection {Integer}
131    \begin{lstlisting}
132    `NUM_REF` int(11) DEFAULT NULL,       
133    \end{lstlisting}
134    There is no special option for integer fields
135  \subsubsection {Float}
136
137    \begin{lstlisting}
138    `TEMP` decimal(5,2) DEFAULT NULL,
139    \end{lstlisting}
140    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.
141    \subsubsection {Date}
142
143    \begin{lstlisting}
144    `REF_DATE` date DEFAULT NULL,
145    \end{lstlisting}
146    There is no special option for date fields
147
148
149\subsection{Attributes values}
150\label{sec:attribute-values}
151For 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.
152
153\begin{description*}
154  \item [ATTRIBUT\_ID] attribute unique ID.
155  \item [LAYER\_INDEX] the index of the layer that the attribute refers to. The value 1 shown in the example (Figure 4, row 42 and 43) therefore relates to the theme Boreholes\_PT.
156  \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} 
157  \item [CATALOG\_ID] attribute value unique ID
158  \item [CODE] attribute value code, should not necessarily be unique
159  \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}).
160
161\end{description*}
162
163\begin{figure} [htbp]
164        \centering
165    \includegraphics[width=.9\textwidth]{img/attributs.png}
166    \caption{Attributes section structure}
167    \label{fig:attributs}
168\end{figure}
169
170
171
172\end{document}
Note: See TracBrowser for help on using the repository browser.