Opasnet base structure

This page is a knowledge crystal of subtype variable. The page identifier is Op_en1913
Moderator:Nobody (see all) Click here to sign up.
Upload data {{#opasnet_base_link:Op_en1913}}

Scope

Opasnet base is a storage and retrieval system for variable results and nugget data. What is the structure of Opasnet base such that it enables the following functionalities?

1. Storage of results of variables with uncertainties when necessary, and as multidimensional arrays when necessary.D↷
2. Automatic retrieval of results when called from Opasnet wiki or other platforms or modelling systems.
3. Description and handling of the dimensions that a variable may take.
4. It is possible to protect some results and data from reading by unauthorised persons.
5. If is possible to build user interfaces for easily entering observations into the Base.

Definition

Data

Software

Because Opasnet base will contain very large amounts of mostly numerical information, the state-of-the-art structure is a SQL database. Because of its flexibility, ease of use, and cost, MySQL is an optimal choice among SQL software. In addition to the database software, a variable transfer protocol is needed on top of that so that the results of variables can be retrieved and new results stored either automatically by a calculating software, or manually by the user. Fancy presenting software can be built on top of the database, but that is not the topic of this page.

Storage and retrieval of results of variables

The most important functionality is to store and retrieve the results of variables. Because variables may take very different forms (from a single value such as natural constant to an uncertain spatio-temporal concentration field over the whole Europe), the database must be very flexible. The basic solution is described in the variable page, and it is only briefly summarised here. The result is described as

  P(R|x1,x2,...) 

where P(R) is the probability distribution of the result and x₁ and x₂ are defining locations of a dimension where a particular P(R) applies. Typically locations are operationalised as discrete indices. A variable must have at least one dimension. Uncertainty about the true value of the variable is operationalised as a random sample from the probability distribution, in such a way that the samples are located along an index Sample, which is a list of integers 1,2,3...n, where n=number of samples.

Table and field names

• Old structure description

Principles:

• The names should be as short as possible: three letters if possible.
• Tables that are only connecting two substance tables (i.e. tables for making many-to-many relationships) have a name that is a combination of the two, with six letters if possible.
• Table identifiers are named id, and when they are used in other tables, the name of the field is like Obj_id for id of the Obj table.
• Substantive fields may have longer names.

In practice the tables and fields would look like this:

The table names have changed in time. This is how they have developed:

• Variable -> Obj
• Result -> Res -> Cell
• Location -> Loc
• Dimension -> Dim or Obj -> Obj
• Index -> Ind
• Rows -> Roww
• Loc_of_result -> Locres (the location of each result) -> Loccell
• Run -> Run -> Obj
• Wiki_location -> Wik

Dependencies

• Opasnet structure
• Open assessment

Result

Opasnet base is a MySQL database located at http://base.opasnet.org.

Table structure

• Roww table is no longer needed, because the same information is in the Loc table.
• We need Sams (Sample secure) and Descrs tables for secure information. All other tables are openly readable except these two. They have the same structure as Sam and Descr tables, respectively.

Obj Contains all objects FIELD TYPE EXTRA id mediumint(8) primary Ident varchar(20) unique Name varchar(200) Unit varchar(16) Typ_id tinyint(3) Page mediumint(8) Wik_id tinyint(3)	Res Result information FIELD TYPE EXTRA id int(10) primary Obj_id_v mediumint(8) Obj_id_r mediumint(8) Mean float N smallint(6)	Loc Location information FIELD TYPE EXTRA id mediumint(8) primary Obj_id_i mediumint(8) Location varchar(1000) Roww Smallint(5) Locn float Num tinyint(1) Description varchar(150)
Item add description' FIELD TYPE EXTRA id mediumint(8) primary Sett_id mediumint(8) Obj_id mediumint(8) Fail tinyint(1)	Locres Result information FIELD TYPE EXTRA id int(10) primary Res_id int(10) Loc_id mediumint(8)	Sam Result distribution (actual values) FIELD TYPE EXTRA id int(10) primary Res_id mediumint(8) Descr_id mediumint(8) Sample mediumint(8) Result float
Sett Memberships of items in sets FIELD TYPE EXTRA id mediumint(8) primary Obj_id mediumint(8) Sty_id mediumint(8)	Sty Types of set-item memberships FIELD TYPE EXTRA id tinyint(3) Stype varchar(30)	Typ Object type information FIELD TYPE EXTRA id tinyint(3) primary Type varchar(30)
Wik Wiki information FIELD TYPE EXTRA id tinyint(3) primary Url varchar(255)	Descr Additional description of the result FIELD TYPE EXTRA id mediumint(8) primary Description varchar(250)	Inf Additional information about an object FIELD TYPE EXTRA id mediumint(8) primary Begin date End date Who varchar(50) Url varchar(250)

See also

These texts could be moved to separate pages.

Opasnet Data

Opasnet Data is a database that is designed to collect observation data from studies. A study can be a traditional research study, which is documented in Opasnet Data afterwards, or it can be an Opasnet study where the data is collected on a particular page of Opasnet using a web form. There are several purposes:

• To collect observation data to be directly usable in interpretations of variables and other objects.
• To collectively collect information about specific cases, and based on these data conditionalise a generalised assessment model with data specific to a particular case.

The structure of the Opasnet Data is the same as that of Opasnet Base. Actually, they are physically in the same database. However, there are some things that should be understood:

• The object for a collection of observations is called a study, while the object of interpretations is called a variable. As an example, a study can collect information about a population group by a questionnaire and by taking a blood sample. The study identifier is the Obj.id in the Opasnet Base.
• The object may be divided into smaller pieces along one or more indices. For example, the questionnaire may have 30 questions, and therefore the questionnaire data can be indexed by an index with 30 columns (or rows, depending on which way you think), one row for each question. Each column of the study object has one cell, i.e. an answer to one question. In this study, ten blood markers will be studied, and therefore the study object will have 40 cells, and the index 40 columns (30 from the questionnaire, 10 from the blood sample). The cell identifier is the Res.id in the Opasnet Base.
• For each individual patient, there is one row of observations, each 40 cells. The observation row identifier is Sam.Sample in Opasnet Base.
• The actual result of a particular cell of a particular patient is located in Sam.Result in Opasnet Base (or in Descr.Description in the case where the result is text, i.e. non-numeric).
• Each study may be multidimensional just like a variable and have indices along e.g. space, time, or sex.
• If the data is collected using an Opasnet web form, then the timestamp and username or IP will be recorded for each entry into Descr.When and Descr.Who fields, respectively. This is not needed, if the data comes from a previously performed study (which is static data in the eyes of Opasnet).
• In some cases, it might be useful to restrict the number of entries per user to one. However, this is done only at the interpretation phase where only the last entry is counted. There are no restrictions to enter new data, and therefore a user may change his/her previous entry by simply making a new entry.

Making value-of-information analyses in Opasnet base

Value of information (VOI) is a decision analysis tool for estimating the importance of remaining uncertainty for decision-making. Result database can be used to perform a large number of VOI analyses, because all variables are in the right format for that: as random samples from uncertain variables. The analysis is done by optimising an indicator variable by adjusting a decision variable so that the variable under analysis is conditionalised to different values. All this can in theory be done in the result database by just listing the indicator, the decision variable, and the variable of interest. Practical tools should be developed for this. After that, systematic VOI analyses can be made over a wide range of environmental health issues.

Analysing the change in the quality of a variable result in Opasnet base

All results that have once been stored in the result database remain there. Old results can be very interesting for some purposes:

• The time trend of informativeness and calibration (see performance) can be evaluated for a single variable against the newest information.
• Critical pieces of information that had a major impact on the informativeness and calibration can be identified afterwards.
• Large number of variables can be assessed and e.g. following questions can be asked:
  • How much work is needed to make a variable with reasonable performance for practical applications?
  • What are the critical steps after which the variable performance is saturated, i.e., does not improve much despite additional effort?

Some useful syntax

• http://www.baycongroup.com/sql_join.htm
• Opasnet base connection.ANA for Analytica: for writing and reading variable results into and from the database. Writing requires a password. For SQL used in the model, see the model page.
• Some historical queries

List all dimensions that have indices, and the indices concatenated:

<sql-query display="1"> Select Dim_name, dim_title, dim_unit, Group_concat(Ind_name order by ind_name separator ', ') as Indices from Dimension, `Index` where Dimension.dim_id = `Index`.Dim_id group by Dim_name order by Dimension.dim_id </sql-query>

List all indices, and their locations concatenated:

<sql-query display="1">

Select Dim_name, Dim_title, Dim_unit, Ind_name, Group_concat(Location order by row_number separator ', ') as Locations 
from `Index`, Location, Rows, Dimension
where `Index`.ind_id= Rows.ind_id and Rows.loc_id = Location.loc_id and `Index`.dim_id = Dimension.dim_id
group by Ind_name
order by Dim_name, `Index`.ind_name

</sql-query>

List all variables and their runs, and also list all dimensions (concatenated) used for each variable for each run.

<sql-query display="1">

SELECT Var_id, Run_id, Var_name, Var_title, GROUP_CONCAT(Dim_name SEPARATOR ', ') as Dimensions, n, Run_method
FROM
   (SELECT Loc_of_result.Var_id, Run_list.Run_id, Var_name, Var_title, Dim_name, n, Run_method
   FROM Loc_of_result, Run_list, Run, Variable, Location, Dimension
   WHERE Loc_of_result.Result_id = Run_list.Result_id 
   AND Run_list.Run_id = Run.Run_id
   AND Loc_of_result.Var_id = Variable.Var_id
   AND Loc_of_result.Loc_id = Location.Loc_id 
   AND Location.Dim_id = Dimension.Dim_id
   GROUP BY Dimension.Dim_id, Loc_of_result.Var_id, Run_list.Run_id
   ORDER BY Loc_of_result.Var_id, Run_list.Run_id) as temp1
GROUP BY Var_id, Run_id

</sql-query>