4. SQL schema, SQLite and gama-local

The input data for a local geodetic network adjustment (program gama-local) can be strored in SQLite 3 database file. The general information about SQLite can be found at

http://www.sqlite.org/

Input data (points, observations and other related information) are stored in SQLite database file. Native SQLite C/C++ API is used for reading SQLite database file. It is described at

http://www.sqlite.org/c3ref/intro.html

Please note if you compile GNU Gama as described in Install and SQLite library is not installed on your system, GNU Gama would be compiled without SQLite support.

SQL schema (CREATE statements) is in gama-local-schema.sql file which is part of GNU Gama distribution and is in the xml directory.

All tables for gama-local are prefixed with gnu_gama_local_. In the documentation table names are referred without this prefix. For example table gnu_gama_local_points is referred as points.

Database scheme used for SQLite database is also valid in other SQL database systems. Almost every column has some constraint to ensure correctness.

You can convert existing XML input file to SQL commands with program gama-local-xml2sql, for example

$ gama-local-xml2sql geodet-pc geodet-pc-123.gkf geodet-pc.sql

4.1 Working with SQLite database

First of all you have to create tables for GNU Gama in SQLite database file (here with db extension, but you can choose your own, e.g. sqlite).

$ sqlite3 gama.db < gama-local-schema.sql

You can check created tables by following commands (fist in command line, second in SQLite command line).

$ sqlite3 gama.db
sqlite> .tables

Output should look like this:

gnu_gama_local_clusters        gnu_gama_local_descriptions
gnu_gama_local_configurations  gnu_gama_local_obs
gnu_gama_local_coordinates     gnu_gama_local_points
gnu_gama_local_covmat          gnu_gama_local_vectors

When you have created tables you can import data. One way is to process file with SQL statements.

$ sqlite3 gama.db < geodet-pc.sql

Another way can be filing database file in another program.

For using sqlite3 command you need a command line interface for SQLite 3 installed on your system (e.g. sqlite3 package).

4.2 Units in SQL tables

In the gama-local SQLite database, distances are given in meters and their standard deviations (rms errors) in millimeters. Angular values are given in radians as well as their standard deviations.

Conversions between radians, gons and degrees:

    rad = gon * pi / 200
    rad = deg * pi / 180
    gon = rad * 200 / pi
    deg = rad * 180 / pi

4.3 Network SQL definition

Network definitions are stored in the configurations table. This table contains all parameters for each network such as value of a priori reference standard deviation or orientation of the xy orthogonal coordinate system axes.

It is obvious that in one database file can be stored more networks (configurations).

Configuration descriptions (annotation or comments) are stored separately in table descriptions. The description is split to many records because of compatibility with various databases (not all databases implements type TEXT).

Field (attribute) conf_id identifies a configuration in the database. Field conf_name is used to identify configuration outside the database (e.g. parameter in command-line when reading data from database to gama-local).

Table configurations contains all parameters specified in tag <parameters /> (see section Network parameters) and also gama-local command line parameters (see section Program gama-local). The list of all table attributes (parameters) follows.

• sigma_apr value of a priori reference standard deviation—square root of reference variance (default value 10)
• conf_pr confidence probability used in statistical tests (dafault value 0.95)
• tol_abs tolerance for identification of gross absolute terms in project equations (default value 1000 mm)
• sigma_act actual type of reference standard deviation use in statistical tests (aposteriori | apriori); default value is aposteriori
• update_cc enables user to control if coordinates of constrained points are updated in iterative adjustment. If test on linerarization fails (see section Test on linearization), Gama tries to improve approximate coordinates of adjusted points and repeats the whole adjustment. Coordinates of constrained points are implicitly not changed during iterations. Acceptable values are yes, no, default value is yes.
• axes_xy orientation of axes x and y; value ne implies that axis x is oriented north and axis y is oriented east. Acceptable values are ne, sw, es, wn for left-handed coordinate systems and en, nw, se, ws for right-handed coordinate systems (default value is ne).
• angles right-handed defines counterclockwise observed angles and/or directions, value left-handed defines clockwise observed angles and/or directions (default value is left-handed).
• epoch is measurement epoch. It is floating point number (default value is 0.0).
• algorithm specifies numerical method used for solution of the adjustment. For Singular Value Decomposition set value to svd. Value gso stands for block matrix algorithm GSO by Frantisek Charamza based on Gram-Schmidt orthogonalization, value cholesky for Cholesky decomposition of semidefinite matrix of normal equations and value envelope for a Cholesky decomposition with envelope reduction of the sparse matrix. Default value is svd.
• ang_units Angular units of angles in gama-local output. Value 400 stands for gons and value 360 for degrees (default value is 400). Note that this doesn’t effect units of angles in database. For further information about angular units see Angular units.
• latitude is mean latitude in network area. Default value is 50 (gons).
• ellipsoid is name of ellipsoid (see section Supported ellipsoids).

All fields are mandatory except ellipsoid field. For additional information about handling geodetic systems in gama-local see Tags <gama-local> and <network>.

Example (configuration table contents):

conf_id|conf_name|sigma_apr|conf_pr|tol_abs|sigma_act  |update_cc|...
---------------------------------------------------------------------
1      |geodet-pc|10.0     |0.95   |1000.0 |aposteriori|no       |...

... axes_xy|angles      |epoch|algorithm|ang_units|latitude|ellipsoid
---------------------------------------------------------------------
... ne     |left-handed|0.0  |svd      |400      |50.0    |

The list of description table attributes follows.

• conf_id is id of configuration which description (text) belongs to.
• id identifies text in a database.
• text is part of configuration description. Its SQL type is VARCHAR(1000).

There can be more than one text for one configuration. All texts related to one configuration are concatenated to one description.

Example (description table contents):

conf_id|indx|text
-----------------------------------------------
1      |1   |Frantisek Charamza: GEODET/PC, ...

4.4 Table points

• conf_id is id of configuration which points belongs to.
• id identifies point in a database and also in an output. It is mandatory and it is character string (SQL type is VARCHAR(80)). Point id has to be unique within one configuration. In documentation it is referred as point identification or point id.
• x, y and z coordinates of a point. Coordinate z is considered as height.
• txy and tz specify the type of coordinates x, y and z. Acceptable values are fixed, adjusted and constrained (there is no default value). For details see Points.

Example (table contents):

conf_id|id |x       |y      |z|txy     |tz
------------------------------------------
1      |201|78594.91|9498.26| |fixed   |
1      |205|78907.88|7206.65| |fixed   |
1      |206|76701.57|6633.27| |fixed   |
1      |207|        |       | |adjusted|

4.5 Table clusters

The cluster is a group of observations with the common covariance matrix. The covariance matrix allows to express any combination of correlations among observations in cluster (including uncorrelated observations, where covariance matrix is diagonal). For explanation see Observation data and points.

In the database observations are stored in three tables: obs, coordinates and vectors. Cluster’s covariance matrix is stored in table covmat. Every observation, vector or coordinate in database has to be in some cluster.

• conf_id is id of configuration which cluster belongs to.
• ccluster identifies a cluster within one configuration.
• dim and band specify dimension and bandwidth of covariance matrix. The bandwidth of the diagonal matrix is equal to 0 and a fully-populated covariance matrix has a bandwidth of dim-1 (band maximum possible value is dim-1).
• tag specifies type of observations in cluster which also implies the table where they are stored in. obs and height-differences stand for obs table, coordinates and vectors stand for coordinates table and vectors table respectively.

Observations, vectors and coordinates are identified by configuration id (conf_id), cluster id ccluster and theirs index (indx). Observation index (indx) has to be unique within observations of one cluster (which belongs to one configuration). The same applies for vectors and coordinates.

See also Set of observations.

Example (table contents):

conf_id|ccluster|dim|band|tag
-----------------------------
1      |1       |3  |0   |obs
1      |4       |4  |0   |obs

4.6 Table covmat

Values of cluster covariance matrix are stored in covmat table. Attributes conf_id, ccluster identifies covariance matrix. Value position in matrix is specified by rind and cind fields.

• conf_id is id of configuration which cluster belongs to.
• ccluster is id of cluster which matrix belongs to.
• rind is row number in covariance matrix
• cind is column number covariance matrix
• val is value itself (variance or covariance).

Values rind and cind have to respect dim and band specified in table clusters. If value in covariance matrix is not specified (record is missing), it is considered to be zero.

Example (table contents):

conf_id|ccluster|rind|cind|val
--------------------------------
1      |1       |1   |1   |400.0
1      |1       |2   |2   |400.0
1      |1       |3   |3   |400.0
1      |4       |1   |1   |400.0
1      |4       |2   |2   |400.0
1      |4       |3   |3   |400.0
1      |4       |4   |4   |400.0

4.7 Table obs

Table obs contains simple observations like direction or distance.

• conf_id is id of configuration which cluster belongs to.
• ccluster is id of cluster which observation belongs to.
• indx identifies observation within cluster. It has to be positive integer.
• tag specifies a type of an observation. Allowed tags follows.
  • direction for directions.
  • distance for horizontal distances.
  • angle for angles.
  • s-distance for slope distances (space distances).
  • z-angle for zenith angles.
  • azimuth for azimuth angles.
  • dh for leveling height differences.
• from_id is stand point identification. It is mandatory and it must not differ within one cluster for observations with tag = 'direction' .
• to_id is target identification (mandatory).
• to_id2 is second target identification. It is valid and mandatory only for angles (tag = 'angle').
• val is observation value. It is mandatory for all observation types.
• stdev is value of standard deviation. It is used when variance in covariance matrix is not specified.
• from_dh is value of instrument height (optional).
• to_dh is value of reflector/target height (optional).
• to_dh2 is value of second reflector/target height (optional). It is valid only for angles.
• dist is distance of leveling section. It is valid only for height-differences (tag = 'dh').
• rejected specifies whether observation is rejected (passive) or not. Value 0 stand for not rejected, value 1 for rejected. It is mandatory. Default value is 0.

Example (table contents without empty columns):

conf_id|ccluster|indx|tag      |from_id|to_id|val           |rejected
---------------------------------------------------------------------
1      |1       |1   |direction|201    |202  |0.0           |0
1      |1       |2   |direction|201    |207  |0.817750284544|0
1      |1       |3   |direction|201    |205  |2.020073921388|0

4.8 Table coordinates

Table coordinates contains control (known) coordinates.

• conf_id is id of configuration which cluster belongs to.
• ccluster is id of cluster which coordinates belongs to.
• indx identifies coordinates within cluster. It has to be positive integer.
• id is point identification.
• x, y and z are coordinates.
• rejected specifies whether observation is rejected (passive) or not. Value 0 stand for not rejected, value 1 for rejected. Default value is 0.

See also Control coordinates.

4.9 Table vectors

Table vectors contains coordinate differences (vectors).

• conf_id is id of configuration which cluster belongs to.
• ccluster is id of cluster which vector belongs to.
• indx identifies vector within cluster. It has to be positive integer.
• from_id is point identification. It identifies initial point.
• to_id is point identification. It identifies terminal point.
• dx, dy and dz are coordinate differences.
• from_dh is value of initial point height. It is optional.
• to_dh is value of terminal point height. It is optional.
• rejected integer default 0 not null,

See also Coordinate differences (vectors).

4.10 Example of local geodetic network in SQL

Providing complete example would be reasonable because of its extent. However, you can obtain example by following these instructions:

Create a file with XML representation of network by copy and paste example from Example of local geodetic network to a new file. Note that file should start with <?xml version="1.0" ?> (no whitespace). Alternatively you can use existing XML file from collection of sample networks (see Download). Then you can convert your XML file (here example_network.xml) to SQL statements by program gama-local-xml2sql (the path depends on your Gama installation).

$ gama-local-xml2sql example_net example_network.xml example_network.sql

Now you have example network (configuration example_net) in the form of SQL INSERT statements in the file example_network.sql.

Another representations you can create and fill SQLite database (for details see Working with SQLite database):

$ sqlite3 examples.db < gama-local-schema.sql
$ sqlite3 examples.db < example_network.sql
$ sqlite3 examples.db

Once you have SQLite database, you can work with it from SQLite command line. You can get nice output by executing following commands.

sqlite> .mode column
sqlite> .nullvalue NULL
sqlite> SELECT * FROM gnu_gama_local_configurations;
sqlite> SELECT * FROM gnu_gama_local_points;
sqlite> SELECT * FROM gnu_gama_local_clusters;
sqlite> SELECT * FROM gnu_gama_local_covmat;
sqlite> SELECT * FROM gnu_gama_local_obs;

Or you can get database dump (CREATE and INSERT statements) by

sqlite> .dump

If it is not enough for you, you can try one of GUI tools for SQLite.

This document was generated on February 17, 2024 using texi2html 1.82.