| Rusty Horseshoe | Find a cave
Examples | Complex Queries and New Data Insertion | Units | Real Tables

Perhaps, this page will contribute to the systematization and processing the data on caves and caves systems.

Caves

A file system and a database are mutually used as a data storage. Currently, the data on the caves and on the cave systems are collected in 3 tables ("Areas", "Entrances", and "Systems") which are created at a small MySQL server. The "Areas" table is one for storing such data on speleological areas as name, geographical coordinates of the bounding polygon, reference, etc. The areas are identified by unique identification number (UIN). Beside UIN the area record may contain an identifier of the area it is included in ("parent area"). As it is easy to guess, the "Entrances" table stores the data on the entrances. Beside UIN the entrance record may contain the identifier of the smallest area the entrance belongs to, the difference between the elevation (above sea level) of the entrance and the elevation of the cave system bottom, the identifier of the cave system the entrance belongs to, geographical coordinates, etc. The "Systems" table contains the data which characterize common cave systems parameters such as volume, maximum drain, vertical range (V.R., difference between the elevations of top and bottom points), depth, etc. The contents of some columns of the "Systems" table are compiled utilizing the data of other tables in the database. For instance, the data in "Entrances number" column are inserted or updated by counting the respective records of the "Entrances" table.

Examples

Although there is no user manual for the query form, several examples are given below in a manner allowing us to imaging, more or less, how to fill the form in.

The records for the caves with vertical range (V.R.)  ≥ 1000 m ordered descending by V.R. Example Result
10 records for the caves or systems with greatest values of V.R. The ordering is descending by V.R. Example Result
The records for the caves of Arabika Massif with V.R. ≥ 1000 m, ordered descending by V.R. Example Result
The records for the Caves of Karaby Plateau and Arabika Massif with "V" character at the beginings of their names. Example Result
Empty form (is not example). Query Result

The server is adjusted in a manner that maximum number of rows in its response is 256 – there is a reason to make query parameters as exact as possible.

Complex Queries and New Data Insertion

It is not planned to open any port for direct SQL-queries in the near future. However, if your query can not be formulated using the form and you think this query to be important, you may try to formulate it in a message and send to e-mail box given at the top and at the bottom of this page. Probably you will get an answer.

The data of this database are mainly on the caves of former USSR, however any cave or system may be added. The new data you send may be added to the database after possible discussion in Russian Cavers Mailing List (CML) (unfortunately, there is no published page in English on how to subscribe this list although English is allowed there, but we can send respective instructions). You can also send your data to e-mails given at the top and at the bottom of this page, then these data will be reposted to CML, and then new recors will probably be added to the database tables.

Units

Mainly, SI (MKS) is used. For instance, "Maximum drain" is measured in m3/s. The average temperature is measured in °C. At the same time, the angles are recorded in degrees, and it is still not chosen what the "difficulty" is measured in.

Real Tables

Below the database tables description is given.

mysql> describe areas; describe entrances; describe systems;
+---------------+-----------------------+------+-----+---------+----------------+
| Field         | Type                  | Null | Key | Default | Extra          |
+---------------+-----------------------+------+-----+---------+----------------+
| id            | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| parent_id     | mediumint(8) unsigned | YES  |     | NULL    |                |
| area_name     | varchar(2044)         | YES  |     | NULL    |                |
| area_type     | varchar(2044)         | YES  |     | NULL    |                |
| area_name_rus | varchar(2044)         | YES  |     | NULL    |                |
| area_type_rus | varchar(2044)         | YES  |     | NULL    |                |
| longitude     | double                | YES  |     | NULL    |                |
| latitude      | double                | YES  |     | NULL    |                |
| elevation     | double                | YES  |     | NULL    |                |
| area_value    | double                | YES  |     | NULL    |                |
| bounds        | varchar(20476)        | YES  |     | NULL    |                |
| reference     | varchar(2044)         | YES  |     | NULL    |                |
| reference_rus | varchar(2044)         | YES  |     | NULL    |                |
+---------------+-----------------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)

+----------------+-----------------------+------+-----+---------+----------------+
| Field          | Type                  | Null | Key | Default | Extra          |
+----------------+-----------------------+------+-----+---------+----------------+
| id             | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| sys_id         | int(10) unsigned      | YES  |     | NULL    |                |
| area_id        | mediumint(8) unsigned | YES  |     | NULL    |                |
| entr_name      | varchar(2044)         | YES  |     | NULL    |                |
| entr_name_rus  | varchar(2044)         | YES  |     | NULL    |                |
| entr_type      | varchar(2044)         | YES  |     | NULL    |                |
| from_bottom    | double                | YES  |     | NULL    |                |
| longitude      | double                | YES  |     | NULL    |                |
| latitude       | double                | YES  |     | NULL    |                |
| elevation      | double                | YES  |     | NULL    |                |
| discovery_date | date                  | YES  |     | NULL    |                |
| reference      | varchar(2044)         | YES  |     | NULL    |                |
| reference_rus  | varchar(2044)         | YES  |     | NULL    |                |
+----------------+-----------------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)

+-----------------------+------------------+------+-----+---------+----------------+
| Field                 | Type             | Null | Key | Default | Extra          |
+-----------------------+------------------+------+-----+---------+----------------+
| id                    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| sys_name              | varchar(2044)    | YES  |     | NULL    |                |
| sys_type              | varchar(2044)    | YES  |     | NULL    |                |
| sys_name_rus          | varchar(2044)    | YES  |     | NULL    |                |
| sys_type_rus          | varchar(2044)    | YES  |     | NULL    |                |
| list_of_entrances     | varchar(2044)    | YES  |     | NULL    |                |
| list_of_entrances_rus | varchar(2044)    | YES  |     | NULL    |                |
| entrances_num         | mediumint(8)     | YES  |     | NULL    |                |
| area_path             | varchar(2044)    | YES  |     | NULL    |                |
| path_ref              | varchar(2044)    | YES  |     | NULL    |                |
| area_path_rus         | varchar(2044)    | YES  |     | NULL    |                |
| path_ref_rus          | varchar(2044)    | YES  |     | NULL    |                |
| volume                | double           | YES  |     | NULL    |                |
| area_value            | double           | YES  |     | NULL    |                |
| sys_length            | double           | YES  |     | NULL    |                |
| proj_length           | double           | YES  |     | NULL    |                |
| vertical_range        | double           | YES  |     | NULL    |                |
| sys_depth             | double           | YES  |     | NULL    |                |
| elevation             | double           | YES  |     | NULL    |                |
| max_drain             | double           | YES  |     | NULL    |                |
| difficulty            | double           | YES  |     | NULL    |                |
| temperature           | double           | YES  |     | NULL    |                |
| discovery_date        | date             | YES  |     | NULL    |                |
| reference             | varchar(2044)    | YES  |     | NULL    |                |
| reference_rus         | varchar(2044)    | YES  |     | NULL    |                |
+-----------------------+------------------+------+-----+---------+----------------+
25 rows in set (0.01 sec)

The "Systems" table looks to be degenerate (several columns store almost same data). For instance, the "area_path" column is compiled using original data of the "areas" table. However, such a degeneracy may enable us to avoid exceessive calculations when frequent users queries take place.

| Rusty Horseshoe | Find a cave