With therion you can export the survey data in a script with which you can create and populate a database [thbook 49]. The database export is used mainly to compute aggregate informations on the survey data, through SQL queries. Therion does not interface with a DBM, mostly because the cave data are written in therion files (with the therion syntax) and therion does not rely on an external database to store this information, but it organizes it with an in-memory database at every run.

The command in the configuration file is

   export database -output "filename.sql"

The outcome is a file with SQL commands to create the tables of the database, and commands to populate them with the survey data.

In particular the following tables are created

• "SURVEY", for the individual surveys;
  • id
  • parent_id
  • name, full_name
  • title
• "CENTRELINE", for the centerlines;
  • id
  • survey_id
  • title
  • topo_date, explo_date
  • length, surface_length, duplicate_length
• "PERSON", for the people;
  • id
  • name
  • surname
• "EXPLO", for the relations person-centerline (as explorers);
  • person_id
  • centerline_id
• "TOPO", for the relations person-centerline (as surveyors);
  • person_id
  • centerline_id
• "STATION", for the stations;
  • id
  • name
  • survey_id
  • X, Y, Z
• "STATION_FLAG", for station flags;
  • station_id
  • flag
• "SHOT", for the centerline shots (the legs);
  • id
  • from_id, to_id
  • centreline_id
  • length, bearing, gradient
  • adj_length, adj_bearing, adl_gradient
  • err_length, err_bearing, err_gradient
• "SHOT_FLAG", for shot flags.
  • shot_id
  • flag

Notice that you cannot use the spelling "CENTERLINE" as an alias for "CENTRELINE" as in therion. Similarly you have to use the strict names of the columns.

The structure of the database is rather simple as can be seen from the figure above. Nevertheless it is possible to make interesting queries. For example the length that has been surveyed in a given period:

   select sum(LENGTH) from SHOT S, CENTRELINE C 
      where S.CENTRELINE_ID = C.ID
         and C.TOPO_DATE between '2004-01-01 and '2004-12-31';

For this example we use "SQlite" http://www.sqlite.org . SQlite is not fully SQL compliant, in particular it used to implement the operator "like" as "equal". The command to create the database is

   sqlite3 filename.db \lt filename.sql

Alternately you can start sqlite3 on an empty database (a file that does not exist) and load the data with the command ".read filename.sql". If you close the database session, you do not need to recreate the database, and can reopen it just by typing

  sqlite3 filename.db

When you are in an open session with the database, you can write your queries at the "sqlite3" prompt and see the result:

   sqlite> select sum(LENGTH) from SHOT S, CENTRELINE C ... ;
   215.22
   sqlite>.quit

There is a problem if you want to import the data of a survey in an existing database (created with data form other surveys). This occurs when you need to add a survey to the set of surveys of a cave or a system.

At present the command "export database" does not have an option to inhibit the generation of the SQL code that creates the tables. Therefore you need to edit the sql file and delete the first lines, namely those that start with the word "CREATE". The remaining lines populate the database with the survey data. Next you can execute the sql file as before: "sqlite3 filename.db < filename.sql".

Be careful not to populate twice the database with the same SQL file, otherwise the data are inserted twice, and counted twice. In other words the DB manager does not check that the table records are not repeated.

This workaround is not a solution. There might be name conflicts between the data of the sql script file and the data already stored in the database. If this is the case, the database will end up in a state that is probably not what you expect.

This problem arises when you want to construct the database for the many caves of an area. You have the option to make a big therion project with all the caves or to export each cave by itself and insert in the database one cave at a time. Both options have shortcomings. In the first case, the maintenance is done by therion, but the project may become huge, and you have to plan its structure well, in advance. Also it might take long for each run. In the second case, you may have to correct mistakes by yourself with SQL queries.

A. Atchinson has written a set of php pages to interface to the database through a web browser. They require a web server with support for php and a database server. The pages have been developped using apache2 with MySQL. To install WebDatabase, even locally on a PC, one need to know how something about a database server and a http server.

It is possible to query

• what a person has done:
  • the surveys in a certain period of time,
  • the passages surveyed or explored
• the length surveyed or explored between two dates

The query is

  select full_name, sum(length)
    from survey join centreline on survey.id=survey_id 
    where topo and explo and alike
    group by survey_id;
  where clauses:
    topo  = centreline.topo_date between from_date and to_date;
    explo = centreline.explo_date between from_date and to_date;
    alike = full_name like name
  for example
    centreline.topo_date between "2000.01.01" and "2001.12.31"
    full_name like "..."

Direction polar diagrams can be displayed, for the azimuth or the gradient. The diagram is followed by a table with the data. For the "gradient" there is a column "vertical up". The options are

• for the azimuth, whether to use either the length or that projected in the horizontal plane
• whether to consider all measurements individually or adding together reciprocal ones
• whether the size of the segments is controlled by taking the radius proportional to the length, or the area proportional to the length
• the number of segments for the diagram
• the size, in pixel, of the diagram

A sample query is

  select sum(length) as L, 30*floor( bearing / 30 ) as G from shot 
    left join shot_flag on shot.id = shot_flag.shot_id
    where shot_flag.flag is NULL
    group by G

This is a query on the azimuth. Other conditions may involve the inclination (gradient).

Histograms of survey legs mean attitude can also be generated. The histograms includes also duplicate and surface legs. The options are,

• minimum and maximum altitude. These allow to set the bounds of the histogram. The bouns should not fall inside those of the stations in the database.
• Number of bars. This sets the size of the histogram bins.
• Whether the histogram should be horizontal or vertical.

The query is

  create table range as select id, z from station where z > min and z < max;
  select sum(length), min + bin*floor((((s1.z_s2.z)/2)-min)/bin) as G 
    from shot, range as s1, range as s2 
    where shot.from_id = s1.id and shot.to_id = s1.id;
  Notes:
    min = minimum altitude
    max = maximum altitude
    num = number of bars
    bin = (max-min)/num

The possible queries are limited to those that have been implemented. The structure of the code allows to add additional queries, but a knowledge of php, HTML and SQL is necessary. The project is at an early stage (Feb. 2011); it is possible that further queries will be added in the future.

The therion database output is a SQL script without geometry columns. The station table however contains the geographic coordinates X,Y,Z. It is therefore natural to add to it a geometry column and set its value with the geographical point of the station.

To this aim i will use "spatialite" a geographical database based on SQLite (see. app. 5b). Create an empty database with spatialite and execute the SQL script generated by therion to define tables and populate them. Next add a geometry column to the "station" table (here 3003 is the SRID of the geographical coordinates, use a code appropriate to your data)

  ALTER TABLE station ADD (geom POINT);
  INSERT INTO geometry_columns VALUES ('station', 'geom', 'POINT', 2, 3003, 0);

Save and exit from it{spatialite}. Open the database with plain sqlite3 and type the following commands

  sqlite> .separator ' '
  sqlite> .output "/tmp/geo.sql"
  sqlite> select "update station set geom = GeomFromText('POINT(", X, Y, ")') 
  ...> where id =", id, ";" from station;
  sqlite> .quit

Reopen the database with spatialite and execute the SQL file "/tmp/geo.sql" you just created (it will take a while if you have many stations). Now you can connect your GIS (for example, QGis) to the database and use the table "station".