Geological Database (Quarry)
In Surpac, a geological database allows you to display and edit drillholes, perform compositing, extract plans and sections, and create reports on the drillhole data. Some of the functions you can perform on geological databases are shown in the following table.
| GEOLOGICAL DATABASE - PROCESSING DATA | ||
|---|---|---|
| Display drillholes | Audit database | |
| DATA ANALYSIS | COMPOSITING DATA | EXTRACTING POINTS |
| Table Statistics | Composite By Elevation | Drillhole Layout |
| Composite Downhole | Extract Sample Data | |
| DATA MANIPULATION | Extract Samples Within Geology | |
| Field Maths | ||
| Transform Grid | ||
| Change Bearing | Database management functions | |
The following geological database concepts are described in this help topic:
- Creating and linking to a database
- Default tables
- Optional tables
- Database types
- Table types
- Field types
- Naming restrictions for table names and field names
- Memo field limitations
Creating and linking to a database
To create a new Surpac database that links to an existing database using ODBC, make sure you have an ODBC data source set up in your operating system, run Database > Database > Open/New, choose Database type ODBC, and enter an ODBC Connect Source in the format DSN=<dataSourceName>.
To create a new Surpac database that links to a (new or existing) Access database, run Database > Database > Open/New, and choose Database type Access.
Caution: After creating or linking to a database, it is very important to run the AUDIT DATABASE function, and correct any database issues reported in the Message window. Otherwise the Surpac functions for displaying and working with drillholes and other types of data stored in the database may not work correctly.
If you created a new blank database, you must enter some data before any processing of the geological data can proceed. You can enter data directly using the INSERT TABLE ROW function. This function is designed for keyboard data entry of geological data. Often data is supplied in formatted text files — you can load these using the LOAD DATABASE function.
Note: See Migrating a database from Access to SQL Server for information and Converting ODBC Databases for information about converting to ODBC to Access or from Access to ODBC.
Default tables
When you create a new database, the default tables are created. Collar and survey are mandatory tables. Collar fufills the critical role of providing the coordinates of the collar of each drillhole. Survey fulfills the critical role of providing the coordinates along the length of the hole (either as surveyed stored points, or as calculated values).
| Table | Fields |
| collar | hole_id y x z max_depth hole_path |
| survey | hole_id depth y x z dip azimuth |
| translation | table_name field_name code num_equiv description |
| styles | code field_name from_value graphics_colour graphics_pattern line_colour line_style line_weight marker_size marker_style plotting_colour plotting_pattern style_type table_name to_value |
Collar table
The collar table is a mandatory table that provides the location of drill hole collars, maximum depths of holes and whether a linear or curved algorithm is to be used when displaying hole traces.
| Field | Description |
|---|---|
| hole_id | The hole_id field is a character field that is used by Surpac as a primary key for the collar table. No duplicate values are allowed. |
| y, x, and z | y, x and z are real value fields describing collar coordinates. |
| max_depth | The max_depth field specifies the maximum depths of holes. This is the depth used to determine the maximum depth to calculate hole traces to, and the maximum depth to analyse when compositing. |
| hole_path |
Several algorithms can be used to determine hole traces between survey points. This character field outlines the algorithm to use for each hole and has possible values: "vertical", "linear" and "curved".
Vertical holes require no survey points and hence there is no lookup to the survey table when determining hole traces. They follow the same path as a linear hole with one survey point at depth 0 with dip -90 and azimuth 0. Due to the simplicity of vertical holes they are faster for many database applications. Linear hole paths assume the trace between survey points is linear.
The direction of the trace at a particular depth is equal to the dip and azimuth at the last survey.
The curved path hole trace algorithm assumes the trace between survey points is circular.
This means that the trace consists of circular arcs.
The arcs between survey points have length equal to the difference in depths of the survey points they join.
The azimuth and dip at the start of each arc is equal to the azimuth and depth of the survey point at that depth.
The azimuth and dip at the end of each arc is equal to the dip and azimuth of the survey point at the end of the arc. |
Comparison of Linear and Curved Hole Paths
The diagram below show the results produced for the linear and curved algorithms for the same set of survey points. The diagram clearly show how the algorithms selected effects the calculation of trace. Note that beyond the last survey point (indicated by the last kink in the linear hole trace) the linear algorithm produces a linear trace and the curved algorithm continues the curve from the last two survey points.
| linear trace | curved trace |
|
|
You can also create up to 54 optional fields, for example, date drilled, type of drill hole, or project name. Optional information is often used to select drill holes from the database for further processing and reporting.
Survey table
The survey table is a mandatory table that is used to calculate drill hole coordinates. Each row of the table specifies one survey for a given hole. All rows with the same hole_id value describe the trace for a hole.
For a vertical hole which has not been surveyed, the depth would be the same as the max_depth field in the collar table, the dip as -90, and the azimuth as zero. The y, x, and z fields are used to store the calculated coordinates of each survey.
| Field | Description |
|---|---|
| hole_id | Each hole_id, depth pair is used by Surpac as a primary key for this table. The hole_id field is a character field. |
| depth | depth specifies the maximum depth of a hole. This is the maximum depth of hole traces, and the maximum depth to analyze when compositing. |
|
y x |
Real value fields. Each record describes a survey position down a drillhole. They may either be stored (their values are kept within the database) or calculated (their values are calculated based on the depth, dip and azimuth fields).
|
| dip | Describes the direction of the trace at the specified depth for the hole. |
| azimuth | Describes the direction of the trace at the specified depth for the hole. The azimuth value is in decimal degrees. |
Tip: When you create the database in Surpac, you can choose Up to 53 optional fields may also exist in survey tables. Optional fields may include other information taken at the survey point, for example core orientation.
Translation table
This table is used for storing character codes. These codes may have a numerical equivalent value if they are to be applied to a numeric field or may be used to validate input into a character field.
The names of the table and fields in which the translated code values are to be stored along with the actual code values, the numeric equivalents (if applicable) and a description of the codes, are mandatory fields in this table.
The translation table is used to assist in the translation from some internal numeric representation for data to some user defined character equivalent. This requires the explanation of two new terms which are `physical field' and `virtual field'.
The physical field is the field in which you store your data. This field will contain the data loaded from your sample text files as well as any numeric values translated from codes in your sample text file during loading. If you wish to translate back from the numerical equivalent values, you will need to create additional fields called virtual fields. Virtual fields do not store data values but contain the translate function. If you nominate to extract data from a virtual field, the data values stored in the associated physical field are retrieved and, if they exist in the translation table, they are translated back to their original code values.
Example of translation
The following example demonstrates the use of the translation table for storing data in the database using numeric equivalent values translated from character codes.
A database has been created containing two fields called gold and silver in a table called samples. Both of these fields are defined as numeric type real fields and will contain the physical data including the numerical equivalent values. Two other fields, called tgold and tsilver have been created as virtual fields to translate back from the physical data, should the original character codes be required. These two fields do not actually contain any stored values, but rather a translation function to obtain the original character codes by checking if the numeric value from the associated physical field exists in the translation table.
The data files for this example contain some character codes which indicate that no sample was taken or the sample was below the detection limit. For example, for gold, `<0.01' indicates `below detection' and `NS' indicates `no sample', and for silver `TR' indicates `trace below detection'. Before loading the data file, the following records would need to be inserted into the translation table in order to have the numerical equivalent values stored in the physical fields gold and silver:
| table_name | field_name | code | num_equiv | description |
| samples | gold | <0.01 | 0.005 | below detection |
| samples | gold | NS | -1 | no sample |
| samples | silver | TR | 0.001 | below detection |
After loading the data file, the physical field gold will contain the value 0.005 for every occurrence of the code <0.01 in the data file and -1 for every occurrence of NS. The physical field silver will contain the value 0.001 for every occurrence of the code TR in the assay data file. Whenever these physical fields are selected for further processing, these stored numerical values will be retrieved in place of the original character codes. If the code values are required, e.g. for producing geological plots, then the virtual fields tgold and tsilver can be selected and the numerical values stored in gold and silver will be translated back using the records in the translation table to retrieve the original character code.
If the character codes stored in the translation fields relate to a physical field defined as type character, then they may be used to validate input into this field. For example, a database containing a table called geology contains a physical field called rocktype. This field was defined as a character field containing upper case characters of length 3. The valid codes were designated as being stored in the translation table by entering the @ symbol followed by the field name in the translation table containing the valid codes, e.g. @code, into the valid codes input form field.
Before loading the geology data file of rocktype codes, the valid codes must first be inserted into the translation table as follows:
| table_name | field_name | code | num_equiv | description |
| geology | rocktype | OVB | overburden | |
| geology | rocktype | GRA | granite |
If the geology data file is now loaded and a rocktype code does not exist in the translation table, the data record will be rejected as an invalid record.
Styles table
The styles table is used to define the rendering characteristics, colours, line styles, and marker styles, for drawing drillholes and survey stations. AUTOPLOT uses the styles table when displaying drillholes.
| Field | Description |
|---|---|
| table_name | The table name to which the style definition of the current row applies. |
| field_name | The field name to which the style definition of the current row applies. |
|
style_type |
The data type of the field:
|
| code | For character fields, you can specify a code that you want to define a drawing style for. This code is usually a value that is stored in the connected database for the field that you are defining the style for. For example SH (for shale). |
| from_value | For numeric or datetime fields, this is a numeric or datetime value to indicate the start of a range of values that you want displayed with a certain colour and pattern. |
| to_value | For numeric or datetime fields, this is a numeric or datetime value to indicate the end of a range of values that you want displayed with a certain colour and pattern. |
| graphics_colour | The colour to be displayed in Graphics to represent an interval of a particular geology code or numeric value along the trace of the drillhole or a survey station. |
| plotting_colour | The colour to be displayed in plotting to represent an interval of a particular geology code or numeric value along the trace of the drillhole or survey station. |
| graphics_pattern | The graphics pattern to be drawn for each section of the drillhole that has a particular code or numeric value within a specified range when the drillholes are drawn in Graphics. This value is not used for drawing survey stations. |
| plotting_pattern | The plotting pattern to be drawn for each section of the drillhole that has a particular code or numeric value within a specified range when the drillholes are drawn in the Plotting window. The list of plotting patterns that are selectable are taken from the patterns.ssi file. This is not used for plotting survey stations. |
Optional tables
You may define optional tables which may store interval or point data. The structure of these optional tables is:
| Table Type | Fields |
| interval e.g. down hole samples |
hole_id samp_id depth_from y_from (calculated) x_from (calculated) z_from (calculated) depth_to y_to (calculated) x_to (calculated) z_to (calculated) |
| point e.g. water_table results |
hole_id depth_to y_to (calculated) x_to (calculated) z_to (calculated) |
| discrete samples e.g. soil samples |
samp_id y x z |
The optional tables store information taken over a specified interval down the hole or at a precise point down the hole. The interval tables require the depth at the start of the interval and the depth at the end of the interval, called the depth_from and depth_to fields respectively.
The point tables require only the depth where the sample was taken, called the depth_to field. A sample identifier field is defined for interval tables but this field is not a key field and so does not require data if not available. The y, x and z fields are used to store the calculated coordinates of the sample depths.
The discrete sample tables are used for storing data for a point which has a unique samp_id. All that is required for this is the samp_id and its position in space i.e., its Y, X and Z coordinates. The discrete sample table is ideally suited for storing and later processing geochemical soil samples.
The optional fields for the optional tables are used to store the sample information. Examples of optional fields may be gold, silver, pcb, rocktype, seam_name, etc.
Database types
The software provides an interface to a number of database engines including Microsoft Access, Paradox 3.5, 4.5, and 5.0, and ODBC connectivity to other types of database.
In Surpac you work with a database definition (.ddb) file which specifies how tables and fields are mapped to tables and fields in the database engine. When you create a new database in Surpac, or map to an existing database, you then have a .ddb file with the table names and field names that the Surpac functions require for those functions to work correctly. See Database definition Files for more information.
Note: If you have SQL Server available, GEOVIA recommends using an ODBC connection to SQL Server rather than using a (direct or ODBC) connection to Microsoft Access. The help topic Database format and compatibility describes the compatibility issues that can occur when you use Microsoft Access.
ODBC
Compatibility: Compatible with Open DataBase Connectivity standard.
Required Software: Requires an appropriate ODBC driver as well as any software that is needed to connect to the native database engine. Consult your ODBC driver documentation for further information.
Note: The ODBC database type allows access to a large list of database engines such as SQL Server, dBASE, Paradox, Oracle, Informix, and Ingres.
You can also connect to databases across networks and hardware platforms depending upon the abilities of the native database engine you are connecting to.
Microsoft® Access
Compatibility: Compatible with engine databases for Microsoft Access, as used by Microsoft Access Database Management System.
Required Software: Requires Microsoft Access.
A note on sort order:
The ordering of data in an Access database follows different rules than in other databases.
In most databases, indexed data is returned in ASCII order which is: !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~
Access sorts based on a user specified Database Sort Order. The Access General Database Sort Order is: !"#$%&'()*+,-./:;<=>?@[\]^_`{|}~0123456789abcdefghijklmnopqrstuvwxyz
ABCDEFGHIJKLMNOPQRSTUVWXYZ
When sorting, Access does not discriminate between uppercase and lowercase letters. It is important to keep this sort order in mind when using the <, <=, > or >= operators on the DEFINE QUERY CONSTRAINTS form. |
Note: See Migrating a database from Access to SQL Server for information and Converting ODBC Databases for information about converting to ODBC to Access or from Access to ODBC.
Access database features such as stored queries and link tables are supported.
Table types
There are five types of tables:
- Drill hole
This table contains attributes which apply to the entire drill hole. The table requires the field hole_id which is unique, indexed and sorted in ascending order. There must exist exactly one drill hole table in each database with a table name of `collar' with additional required fields called y, x, z, max_depth and hole_path. Additional user defined attribute fields may exist.
- Point
These tables contain attributes which apply to a particular point down a drill hole. The required fields for a point table are hole_id and depth_to, y_to, x_to and z_to. These can either be stored fields or calculated. There must exist at least one Point table in each database with a table name of `survey' with additional required fields called dip and azimuth. This a special case of a points table where the unique fields are hole_id and depth rather than depth_to. Additional user defined fields may exist.
- Interval
These tables contain attributes which apply to a particular interval down a drill hole. The required fields for an interval table are hole_id, samp_id, depth_from and depth_to. Also any interval table has six mandatory fields to store the y, x and z co-ordinates. These can either be stored or calculated fields. Additional user defined fields may exist.
- Translation
This table allows for the storage of code values and their equivalent translation. Codes stored in this table can be translated to a numeric equivalent for storage in a physical numeric field. They can also be used to validate input into physical character fields.
- Styles
- Discrete samples
These tables are not 'related' to the Drill hole, Point and Interval tables in the sense that they aren't used to store drill hole data. The discrete sample table is often used where discrete samples are taken in a sampling program such as geochemical soil sampling. There may be any number of discrete sample tables (provided the maximum number of tables is not exceeded) in a database. A discrete sample table must have the fields, samp_id, y, x, z. Any number of other fields may exist in the discrete sample table provided the maximum number of fields in a table is not exceeded.
This table defines the rendering characteristics, colours, line styles, and marker styles, for drawing drillholes and survey stations.
Field types
When creating a new database, you must nominate what type of data is to be stored in each optional field that you create. The valid data types are:
| Data Type | Data Format |
| character | will accept any data and store as text |
| real | numeric data with decimal places |
| integer | numeric data with no decimal places |
| datetime | yyyy-mm-dd hh:mm:ss |
| boolean | will accept and store only TRUE or FALSE values |
| memo | A large text field. |
| duration | A time duration in the format yyyy-mm-dd hh:mm:ss. In most cases, you can use the datetime data type instead. |
Naming restrictions for table names and field names
To reduce the chance of errors when working with the database, follow these naming guidelines for tables and fields in the .ddb file:
- For mandatory tables, use the default table names and field names — do not change the names.
- For any new tables or fields use only letters, numbers, and the underscore character.
- The first character of any name must be a letter. This is a requirement for tables and fields in Surpac databases.
Hyphen and percent characters
You can also use the hyphen "-" and percent "%" characters in names although the use of these characters restricts the actions that you can perform on the database depending on which database driver is used.
When either the hyphen or percent symbol are used in a database table or field name, the low level database driver software typically has to quote (surround the name with double quote characters) when it delivers the name to the database engine for processing. While this seems to have no detrimental impact for some database drivers (Microsoft Access and Paradox), it prevents values from being modified (insert, delete and update) via the ODBC driver indeed it may even prevent the ODBC driver from successfully connecting to an ODBC database.
Therefore, if table or field names contain hyphen or percent characters AND if the database is accessed using the ODBC driver then it may only be possible to query the database but it may not even be possible to connect to the database at all. It will not be possible to modify the contents of the database.
Consequently, use of these 'special' characters is not recommended at all. The restrictions regarding the use of these characters has been relaxed only so that databases created by other software packages may be used in Surpac.
SQL reserved words
In addition to the restrictions described above, similar restrictions are present for ODBC databases if any table or field name uses an SQL reserved word, for example the word as. Names that are the same as SQL reserved words are also quoted which may permit successful query operations but prevent insert, update or delete operations from being executed successfully. There is no guarantee that databases that use reserved words for table or field names can be accessed using the ODBC driver.
GEOVIA recommends against using the hyphen or percent characters or any SQL reserved word for the name of a database object.
A list, although not necessarily complete, of SQL reserved words is shown below. Avoid using these as names for tables or fields
| abort, accept, access, add, admin, after, all, allocate, alter, analyze, and, any, archive, archivelog, array, arraylen, as, asc, assert, assign, at, audit, authorization, avg, backup, base_table, become, before, begin, between, binary_integer, block, body, boolean, by, cache, cancel, cascade, case, change, char, character, char_base, check, checkpoint, close, cluster, clusters, cobol, colauth, column, columns, comment, commit, compile, compress, connect, constant, constraint, constraints, contents, continue, controlfile, count, crash, create, current, currval, cursor, cycle, database, datafile, data_base, date, dba, debugoff, debugon, dec, decimal, declare, default, definition, delay, delete, delta, desc, digits, disable, dismount, dispose, distinct, do, double, drop, dump, each, else, elsif, enable, end, entry, escape, events, except, exception, exceptions, exception_init, exclusive, exec, execute, exists, exit, explain, extent, externally, false, fetch, file, float, flush, for, force, foreign, form, fortran, found, freelist, freelists, from, function, generic, go, goto, grant, group, groups, having, identified, if, immediate, in, including, increment, index, indexes, indicator, initial, initrans, insert, instance, int, integer, intersect, into, is, key, language, layer, level, like, limited, link, lists, lock, logfile, long, loop, manage, manual, max, maxdatafiles, maxextents, maxinstances, maxlogfiles, maxloghistory, maxlogmembers, maxtrans, maxvalue, min, minextents, minus, minvalue, mlslabel, mod, mode, modify, module, mount, natural, new, next, nextval, noarchivelog, noaudit, nocache, nocompress, nocycle, nomaxvalue, nominvalue, none, noorder, noresetlogs, normal, nosort, not, notfound, nowait, null, number, number_base, numeric, of, off, offline, old, on, online, only, open, optimal, option, or, order, others, out, own, package, parallel, partition, pctfree, pctincrease, pctused, plan, pli, positive, pragma, precision, primary, prior, private, privileges, procedure, profile, public, quota, raise, range, raw, read, real, record, recover, references, referencing, release, remr, rename, resetlogs, resource, restricted, return, reuse, reverse, revoke, role, roles, rollback, row, rowid, rowlabel, rownum, rows, rowtype, run, savepoint, schema, scn, section, segment, select, separate, sequence, session, set, share, shared, size, smallint, snapshot, some, sort, space, sql, sqlbuf, sqlcode, sqlerrm, sqlerror, sqlstate, start, statement, statement_id, statistics, stddev, stop, storage, subtype, successful, sum, switch, synonym, sysdate, system, tabauth, table, tables, tablespace, task, temporary, terminate, then, thread, time, to, tracing, transaction, trigger, triggers, true, truncate, type, uid, under, union, unique, unlimited, until, update, use, user, using, validate, values, varchar, varchar2, variance, view, views, when, whenever, where, while, with, work, write, xor |
Memo field limitations
The number of characters than you can store in a memo field in a database depends on the database system you are using (for example SQL Server, or Access). However, in Surpac, memo fields are limited to 200 characters by default.
The reason is that if the value of the memo field is displayed in a form in Surpac, the field in the form is necessarily limited to 200 characters, and if you then saved the data in the form, the memo field would then be truncated to 200 characters.
If you wish to override this limitation you can edit the .ddb file to allow more characters, up to the amount of characters supported by the database system.
Caution: If you edit a .ddb file to allow more characters in a memo field, be aware that some memo text may be lost if the memo text is displayed in a form in Surpac and then saved.