Survey database
To run this function: Choose File > New > Survey database, or File > Open > Survey database, or Survey > Survey database > New/Open, or...
Database Name
Enter the name of the database that you wish to access. If this database exists, the database is opened.
If the database does not already exist, the CREATE DEFINITION FOR NEW SURVEY DATABASE form is displayed.
Messages
Database definition file filename not found
Name
The name of the survey database definition file (.sdb) is displayed.
Choose Cancel to return to the SELECT DATABASE form to enter another database name or choose Apply to confirm the creation of the database and display the CHOOSE TYPE OF SURVEY DATABASE form.
Database Name
The name of the current database is displayed.
Database Type
Enter the type of database that you wish to create.
If you wish to access an existing relational database management system you must enter the name of the database driver. Valid entries are:
- access, access97, access2000, access 2007, access 2010
an Access compatible database - paradox, paradox3.5, paradox4.5, paradox5.0,
a Paradox compatible database - dbase, a DBASE IV compatible database
- odbc, an odbc compatible database
Note:All database drivers are not necessarily available on all platforms. This is due to the varying requirements for different database types amongst users of GEOVIA products. If the database type that you wish to use does not appear to be supported please contact the nearest GEOVIA support office.
ODBC connect string
If you are using ODBC to access data stored and maintained by some third party database management systems, you must enter the ODBC connection string here.
The minimum connection string must be of the form:
DSN=<data set name>
where <data set name> is the name of the ODBC data source which you have previously set up using the ODBC manager.
For more details, please refer to your DBMS documentation and also to the section describing the ODBC connect string in more detail.
Complete the CHOOSE TYPE OF SURVEY DATABASE form, and choose Apply to display the CHOOSE OPTIONAL TABLES FOR NEW DATABASE form.
The survey database has two mandatory tables, survey_stations and styles. You can also create an optional stations errors table to set up a QA regime for new stations entering the database, see here for more details of this table. If you wish to conduct a stability monitoring program you may wish to create one or more optional prism tables to store the prism monitoring data for subsequent use in the Stability Monitoring module.
The following field descriptions correspond to the Station errors optional table area of the form:
Create a station errors table?
Tick the box to create a station errors table, untick the box to create a database without a station errors table. The station errors table allows you to set up a QA regime for new stations entering the database. See here for more details of this table.
Populate station errors table with example values?
If you have chosen to create a station errors table, this field gives you the option to fill the table with some simple example values to help you become acquainted with the way the table works. It is strongly recommended that you change these values in the table to be your own values before you use the table in a production environment. See here for notes on how to edit the station errors table. Note: When you validate the example values in the station errors table (see here for notes on validating the table), you will find that you get warnings that there are gaps above some of the "value to" values. This is done deliberately so that the default order will be used for these values.
The following field descriptions correspond to the Prism monitoring optional tables area of the form:
Table name
Enter the names of the optional prism tables that you wish to create. Note: The maximum number of tables you may have in a survey database is specific to the type of survey database you are creating.
Table type
At this time the only type of optional table that may be entered in this field is prism type. At some future time more optional table types may be permitted.
Time dependent
You must tick the box in order to use the Stability Monitoring module.
The prism table MUST be created for storing time dependent data, i.e. a date or time field used to distinguish between measurements recorded at different times to the same prism.
Complete the CHOOSE OPTIONAL TABLES FOR NEW DATABASE form and choose Apply to display the DEFINE ALL FIELDS FOR ALL TABLES form.
The first tab on the DEFINE ALL FIELDS FOR ALL TABLES form contains information on the survey_stations table.
The table which is created to store the stations will be called 'survey_stations' and it will have the following mandatory fields:
| Field Name | Description |
|---|---|
| station_id | The station identifier. This field cannot be null. |
| date_stamp | The time (usually taken from the computer clock, when the station has been created by a Surpac application) at which this instance of the station was inserted into the database. The date_stamp must be entered in the form YYYY-MM-DD HH:MM:SS, although the HH:MM:SS part is optional, eg 2003-10-23 10:20:33 and 2003-10-23 are both acceptable date_stamp values. The combination of the station_id and date_stamp allows a history to be recorded for each station. For a particular station_id only the instance with the latest date_stamp value will be used in calculations. This field cannot be null. |
| level_area | The mine level or area to which the station_id belongs. The level_area field is an identifier which can be used for grouping the stations to simplify the task of subsequent analysis (for example, displaying all the stations in a certain area). This field cannot be null. |
| y | The northing of the station. This field cannot be null. |
| x | The easting of the station. This field cannot be null. |
| z | The elevation of the station. In an underground situation this will be the elevation of the station at the back of the drive. This field cannot be null. |
| station_type | This is either SURF or UG. UG is an underground type station, where the station from which the station_id was surveyed is stored for checking purposes. SURF is a surface type station, where this checking information is not stored. This field cannot be null. |
| station_fr | The station from which the station_id was surveyed. This field must be null if the station_type is SURF. If the station_type is UG this field will in general be non-null, although a null is allowed in this case. |
| z_floor | The elevation of the station at the floor of the drive. This is a secondary elevation and is recorded for reference only. It is not used to calculate new station elevations. This field must be null if the station_type is SURF. If the station_type is UG this field must be non-null. |
| elev_dif | If you use the double elevations method for determining the elevations of new stations then the difference between the two elevations is recorded in this field. This field must be null if the station_type is SURF. If the station_type is UG this field must be non-null (if you are entering this field by hand, a safe default value would be 0.0). |
| rev_brng | The bearing from a station to the station from which it was surveyed (station_id to station_fr) is stored here. This angle is stored in the database in radians. This field must be null if the station_type is SURF. If the station_type is UG this field must be non-null. |
| surveyor | The name of the person who created the station. In general this will be filled automatically by Surpac from data entered on forms. This field is not used in calculations, it is just stored for audit puposes. Null values are allowed in this field. |
| survey_date | The date the station was surveyed. In general this will be filled automatically by Surpac from data entered on forms. The survey_date must be entered in the form YYYY-MM-DD HH:MM:SS, although the HH:MM:SS part is optional, eg 2003-10-23 10:20:33 and 2003-10-23 are both acceptable survey_date values. This field is not used in calculations, it is just stored for audit puposes. Null values are allowed in this field. |
| checked_by | The name of the person who checked the station survey. In general this will be filled automatically by Surpac from data entered on forms. This field is not used in calculations, it is just stored for audit puposes. Null values are allowed in this field. |
| field_book | The name of the field book where the station survey observations were recorded. In general this will be filled automatically by Surpac from data entered on forms. This field is not used in calculations, it is just stored for audit puposes. Null values are allowed in this field. |
| pages | The pages in the field_book where the station survey observations were recorded. In general this will be filled automatically by Surpac from data entered on forms. This field is not used in calculations, it is just stored for audit puposes. Null values are allowed in this field. |
| survey_method | This field is in general filled automatically by Surpac when a new station is created by a Surpac application. It is a code that gives a record of how the station was created. See here for a list of the survey_method codes currently used by Surpac. This field is not used in calculations, it is just stored for audit puposes. Null values are allowed in this field. |
| station_order | This field is used in conjunction with the station errors table, which is an optional table in the survey database used to set up a QA regime for new stations entering the survey database. If you are using the station errors table the station_order field will contain an identifier that represents the order or "quality" of a station, for example, 1st, 2nd or 3rd etc. If you have not created a station errors table in your survey database, this field should be left null. If you have created a station errors table, then any station that is to be used as a fixed station (for example, as a backsight station) in the creation of a new station in the database should have a station_order value that has been defined in the station errors table. See here for more details of the station errors table. |
Here is a list of codes for the survey_method field that are currently used in Surpac:
| Code | Description |
|---|---|
| DR_FILE_SF | Data recorder single face observations to new station. |
| DR_FILE_MF | Data recorder multiple face observations to new station. |
| RESECTION_SF | Single face resection. |
| RESECTION_DF | Double face resection. |
| EDM_SRV | EDM survey. |
| STADIA_SRV | Stadia survey. |
| TWO_WIRE_STADIA_SRV | Two wire stadia survey. |
| RDS_SRV | RDS survey. |
| SRV_NETWORK_3D_ADJ | Survey network 3D adjustment. |
| SRV_NETWORK_2D_ADJ | Survey network 2D adjustment. |
| SURF_TRAV_HRI_VF | Surface traverse with horizontal reiteration angles and vertical forward angles. |
| SURF_TRAV_HRI_VFR | Surface traverse with horizontal reiteration angles and vertical forward and reverse angles. |
| SURF_TRAV_HRP_VF | Surface traverse with horizontal repetition angles and vertical forward angles. |
| SURF_TRAV_HRP_VFR | Surface traverse with horizontal repetition angles and vertical forward and reverse angles. |
| UG_TRAV_HRI_V1T | Underground traverse with horizontal reiteration angles and vertical angles to one target. |
| UG_TRAV_HRI_V1T_RV1T | Underground traverse with horizontal reiteration angles, vertical angles to one target and reverse vertical angles to one target. |
| UG_TRAV_HRI_V1T_RV2T | Underground traverse with horizontal reiteration angles, vertical angles to one target and reverse vertical angles to two targets. |
| UG_TRAV_HRI_V2T | Underground traverse with horizontal reiteration angles and vertical angles to two targets. |
| UG_TRAV_HRI_V2T_RV1T | Underground traverse with horizontal reiteration angles, vertical angles to two targets and reverse vertical angles to one target. |
| UG_TRAV_HRI_V2T_RV2T | Underground traverse with horizontal reiteration angles, vertical angles to two targets and reverse vertical angles to two targets. |
| UG_TRAV_HRP_V1T | Underground traverse with horizontal repetition angles and vertical angles to one target. |
| UG_TRAV_HRP_V1T_RV1T | Underground traverse with horizontal repetition angles, vertical angles to one target and reverse vertical angles to one target. |
| UG_TRAV_HRP_V1T_RV2T | Underground traverse with horizontal repetition angles, vertical angles to one target and reverse vertical angles to two targets. |
| UG_TRAV_HRP_V2T | Underground traverse with horizontal repetition angles and vertical angles to two targets. |
| UG_TRAV_HRP_V2T_RV1T | Underground traverse with horizontal repetition angles, vertical angles to two targets and reverse vertical angles to one target. |
| UG_TRAV_HRP_V2T_RV2T | Underground traverse with horizontal repetition angles, vertical angles to two targets and reverse vertical angles to two targets. |
The second tab on the DEFINE ALL FIELDS FOR ALL TABLES form contains information on the styles table. These fields are used to store the styles used to display survey stations in graphics, and are only of internal use to the software.
Any prism table information will be displayed in the subsequent tabs on the DEFINE ALL FIELDS FOR ALL TABLES form. If you choose to create a prism table, the table is created with the following mandatory fields:
| Field Name | Description |
|---|---|
| prism_id | A unique identifier for the monitoring prism. |
| prism_area | An identifier which can be used for grouping the prisms to simplify the task of subsequent analysis. This field is indexed and duplicate values are permitted those making it possible to group prisms by area. |
| bs_stn | The name of the backsight station which was used when the observations were taken to the monitoring prisms. This is inlcuded for future use when enhancements to check for movement of the setup station by comparing changes in the distance to the backsight station will be made. |
| bs_height | The height of the backsight station at the time of recording the measurement to the monitoring prism. |
| setup_stn | The name of the setup station which was used when the measurements were made to the monitoring prisms. |
| setup_height | The height instrument at the setup station at the time of the observations to the monitoring prisms. |
| bs_slope_dist | The slope distance which was observed to the backsight station at the time of the observations to the monitoring prisms. |
| bs_vert_angle | The vertical angle to the backsight station. |
| slope_dist | The slope distance from the setup station to the monitoring prism. |
| temperature | The temperature at the time of the observations. |
| pressure | The atmospheric pressure at the time of the observations. The temperature and pressure are included so that measurements taken under extreme atmospheric conditions may be ignored if appropriate due to the potential for inaccuraccy in these measurements. |
| horiz_angle | The observed horizontal angle from the backsight station to the monitoring prism at the setup station. |
| vert_angle | The observed vertical angle from the setup station to the monitoring prism. |
| y | The Y co-ordinate of the monitoring prism. |
| x | The X co-ordinate of the monitoring prism. |
| z | The Z co-ordinate of the monitoring prism. |
| prism_date | The date/time of the measurement to the monitoring prism. This value is expressed in the format YYY-MM-DD HH:MM:SS |
The prism table is typically populated with data by using one of the Data Recorder interface functions. These functions accept prism monitoring observations, therefore simplifying the processing of large numbers of monitoring prisms and subsequent analysis of the results. The various fields denoting the backsight and setup stations and other related data are all populated with data by the data recorder interface functions.
Other techniques of stability monitoring may involve measurements of monitoring locations using level traverses, or by simply repeated measurements, over time, between monitoring pins inserted on either side of developing cracks in the pit walls.
These monitoring techniques can also be catered for by creating optional fields for storing the various measurements which might be recorded. The graphing functions can graph any numeric field in the prism
These forms permit you to define the characteristics for all the fields in the database which you are about to create. A number of scrolling regions are displayed to permit you to define the necessary inputs. During the course of entering the data you will find it necessary to scroll up and down through the form to make the necessary entries.
There are two scrolling regions for making entries. The first scrolling region is for defining the mandatory field characteristics and the second scrolling region is for defining the optional field characteristics. Each of these scrolling regions and the entries which must be made are described in more detail in the following pages.
Define mandatory fields for (table name)
The mandatory fields for each table in the database are already partially defined and meaningful defaults are provided for each of the mandatory fields. The field names and field types for the mandatory fields cannot be changed. The inputs over which you have control are:
Nulls
Enter 'Y' if null values are to allowed to be stored in a field otherwise enter 'N'.
Index
Define what type of index is required for this field. All mandatory indexes are created automatically for the database.
Length
This is the length of the field in which the data is to be stored, and should be large enough to include the decimal point and decimal places when the field type is real.
No Dec
This is the number of decimal places to be stored and is only necessary if the field type is real.
Low Bound
For numeric fields enter the minimum value which may be entered.
High Bound
For numeric fields enter the maximum value which may be entered.
Define optional fields for (table name)
The optional fields in the database must be defined completely, this includes the field name, field type and also the parameters which have already been defined for the mandatory fields.
Field Name
Enter the name of the field which is to be created. Names must start with a letter, and contain only letters, numbers and underscore characters.
Type
Enter the field type for the data which is to be stored in the field. Valid entries are:
- boolean - only TRUE and FALSE values may be stored
- character - character data
- datetime - date and time to be entered in the format yyyy-mm-dd hh:mm:ss
- duration - a duration to be entered in the format yyyy-mm-dd hh:mm:ss
- integer - integer data (numeric data with no decimal places)
- real - real data (numeric data with decimal places)
Nulls
Enter 'Y' if null values are to allowed to be stored in a field otherwise enter 'N'.
Length
This is the length of the field in which the data is to be stored, and should be large enough to include the decimal point and decimal places when the field type is real.
No Dec
This is the number of decimal places to be stored and is only necessary if the field type is real.
Case
This is the case of characters which are required for the field. This is only necessary if the field type is character.
Valid entries are:
- mixed - mixed case characters may be entered
- upper - only upper case characters may be entered
- lower - only lower case characters may be entered
Low Bound
For numeric fields enter the minimum value which may be entered.
High Bound
For numeric fields enter the maximum value which may be entered.
Valid Entries
You may want to limit the character data to be entered into a field to a predefined set of values which you may enter here. Each separate value must be preceded by a semi-colon e.g. OVB;GRA
Phys, Virt or Exp
You must indicate whether there will be physical data stored in this field, or whether the field is a virtual field which derives its value when a query is performed. Permitted entries are:
physical
The field is used to store data in the table. This is the most common usage.
virtual
Choosing this option and entering a field name in the Reference field column of the data entry tables will define the special translation function that is performed whenever a value is retrieve from this field of the database table.
expression
This option permits you to define a free format algebraic expression using other fields in the same database table to determine how the value for this field is derived when a value is retrieved from it. The algebraic expression uses the general expression toolkit, details of which can be found here. The variables used in the algebraic expression must by the names of other columns in the same database table.
Reference field or expression
If the field is a virtual field then you must enter the name of the reference or physical field from which values are to be extracted prior to translation by the function associated with this field.
If the field is an expression field then you must enter the algebraic expression in the Reference column of the data entry table. Due to the sequence of events it is not possible to validate the entered algebraic expression while the database is being defined.
When you have completely defined the database the database definition file is created and then loaded. During this process the database tables and fields are created. After creation of the database the expression is validated. If an error in the expression is encountered this message is displayed:
The algebraic expression
<the algebraic expression>
defined for the field <fieldname> in the table <tablename> is invalid.
You should correct this problem before proceeding.
If this error message is displayed you will not be able to successfully retrieve values from the expression column in question. To correct the error use the Edit database definition function. Complete the DEFINE ALL FIELDS FOR ALL TABLES form and choose Apply to display the CONFIRM FURTHER PROCESSING form.
Choose Apply to create the database files.
Messages
A message to indicate successful creation of the database will appear in the message window.
New Database filename created OK
Definition file filename opened OK
Database filename connected OK
Result
A file named dbname.SDB is created after successful creation of the database. Various other files are created to contain the data for the database. The files for storing this data are very dependent upon the type of database.
Once you have completed all the fields for all mandatory and optional tables, choose Create Database. The message:
will be displayed. Choose Apply to continue. You have now created, and are connected to, the new survey database.