How to choose a geological relational database management system

In my previous post a design of geological (drillhole) database was commented. In this post I am sharing with you comments on how to choose the right geological relational database management system, emphasizing on the spatial data object support.

Relational database management systems (RDBMS)

A relational database management system (RDBMS) is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data. Well-known open source RDBMSs include MySQL, PostgreSQL and SQLite; the most popular proprietary options are Microsoft SQL Server (MSSQL) and Oracle database.

The most complete and mature open source RDBMS is PostgreSQL, it is comparable in speed and reliability to Microsoft SQL Server and Oracle databases and it is really good compressing the database files. MySQL is probably the most widely used RDBMS, it is installed by default in many web host and it stores the information that you see in most webpages. Despite its popularity MySQL is less reliable than PostgreSQL. Rob Conery provides a demonstration about the superiority of PostgreSQL over MySQL in terms of consistency and comments about advantages of PostgreSQL over Oracle or Microsoft SQL server.

SQLite is small, simple, light but is not a good choice to store databases used by more than one user at the time. The commercial alternative of SQLite would be Microsoft Access (with Microsoft Jet Database Engine). Examples of applications of these client-side databases are:

  • Micromine stores some drillhole data in sqlite files (with extension *.dhdb)
  • Gems (before Gemcom) stores local databases in Microsoft Access format.

You can install and work with server base databases and RDBMS locally in your personal computer.

Ignoring the commercial packages, the selection of a RDBMS for a company geological database would be between MySQL and PostgreSQL

PostgreSQL MySQL
The world’s most advanced open source database The world’s most popular open source database
More reliable, more ACID,SQL compliant, Multiversion Concurrency Control (MVCC) Not fully SQL compliant, some ACID problems
More standardized, less popular, more difficult to find experts(?). Third-party tools and libraries required to make working with PostgreSQL easier for beginners Easy to use and popular, well know by many ITs, easier to find a Database Administrator

PostgreSQL and MySQL are different in many practical and implementation aspects, you may find difficult the migration from one database to the other. These two RDMS have features non compatibles.

Adding spatial data

Many of the actual versions of RDBMS include support for spatial data consisting in:

  • geographical objects types
  • spatial functions and queries

The spatial support allows for:

  • direct use of our geological database from GIS software or Map (web and desktop based) applications
  • to execute spatial queries and commands or functions directly in the database, which is useful to support exploration, grade control and resource estimation without need of specialized software (ex. generalized mining packages)

The spatial objects supported by PostGIS (the spatial extension of PostgreSQL) are the OpenGIS Consortium (OGC) and ISO standards define 2D (x/y), 3D (x/y/z, x/y/m) and 4D (x/y/z/m) variants of points, lines, polygons, curved features, polyhedra, and TINS. PostGIS also supports a raster type and a topology model. Similar objects are implemented in SpatiaLite (the spatial extension of SQLite). MySQL supports some spatial objects, but only 2D.

Most spatial functions in RDBMSs are designed for 2D operation, although some data models are defined in 3D and 4D. For example, the selection of points withing a closed solid (TIM) is not implemented in any of the spatial database extension mentioned above.

Lets show a simple example of spatial data in a drillhole database using PostGIS

--  Example of PotGIS database

-- first create a desurveyed drillhole table
DROP VIEW IF EXISTS trace;
DROP TABLE IF EXISTS dhole;
CREATE TABLE dhole (
  bhid INTEGER NOT NULL,
  "From" FLOAT NOT NULL,
  "To" FLOAT NOT NULL,
  xmid FLOAT NOT NULL,
  ymid FLOAT NOT NULL,
  zmid FLOAT NOT NULL,
  grade FLOAT NOT NULL,
  point geometry(POINTZ),     -- 3D points
PRIMARY KEY(BHID, "From"));

-- adding some values (not all included in this post)
INSERT INTO dhole (bhid,  "From", "To", xmid,
                   ymid, zmid, grade)
VALUES  (1,   0,0.95,69.26,70.18,59.63, 7.35),
	(1,0.95, 1.9,69.26,70.19,59.62, 3.30),
	(1, 1.9,2.85,69.26,70.19,59.61,12.29),
	(1,2.85, 3.8,69.26,70.19,59.60,22.52),
         ...
	(1,141.09,142.1,69.29,70.66,58.30,23.44),
	(1,142.09,143.1,69.29,70.66,58.29, 4.91);

-- Now adding points data
UPDATE dhole set point=ST_MakePoint(dhole.xmid,
                        dhole.ymid, dhole.zmid); 

-- converting points into line per dhole
-- this is a view... the trace will change if you change dhole.point
CREATE VIEW trace AS
   (SELECT st_makeline(dhole.point) as dhtrace,
    dhole.bhid FROM dhole GROUP BY dhole.bhid);

in the example above we used the geometry(POINTZ) to define geographic points in 3D and the spatial functions ST_MakePoint and ST_MakeLine to define spatial data. The spatial objects are recognized directly as spatial layers by QGIS, as shown in the figure below.

PostGIS example with QGIS
PostGIS data plotted with QGIS

Note that QGIS also support connections with Spatialite and Microsoft SQL.

The next example shows a basic triangulation of a topographic surface from drillhole collar points, using PostGIS.

--  Example of PotGIS database showing traces and TIM

-- Collar table (to create topo)
CREATE TABLE collar (
  bhid INTEGER NOT NULL,
  x FLOAT NOT NULL,
  y FLOAT NOT NULL,
  z FLOAT NOT NULL,
  point geometry(POINTZ),
PRIMARY KEY(BHID));

-- Desurvey drillhole table
CREATE TABLE dhole (
  bhid INTEGER NOT NULL,
  "From" FLOAT NOT NULL,
  "To" FLOAT NOT NULL,
  xmid FLOAT NOT NULL,
  ymid FLOAT NOT NULL,
  zmid FLOAT NOT NULL,
  grade FLOAT NOT NULL,
  point geometry(POINTZ),
PRIMARY KEY(BHID, "From"));

-- adding some data
INSERT INTO dhole (bhid,  "From", "To", xmid, ymid, zmid, grade)
VALUES  (1,0,1,0,0,-0.5,2.4),
	(1,9,10,0,0,-9.5,0.4),
        (2,0,1,10,0,-0.5,2.4),
	(2,9,10,10,0,-9.5,0.4),
        (3,0,1,0,10,-0.5,2.4),
	(3,9,10,0,10,-9.5,0.4),
        (4,0,1,10,10,-0.5,2.4),
	(4,9,10,10,10,-9.5,0.4);

INSERT INTO  collar (bhid,  x, y , z)
VALUES  (1,0,0,0),
	(2,10,0,0.5),
        (3,0,10,0),
	(4,10,10,0.2);

-- Now adding points data
UPDATE dhole set point=ST_MakePoint(xmid, ymid, zmid);
UPDATE collar set point=ST_MakePoint(x, y, z); 

-- converting points into line per dhole (traces)
CREATE OR REPLACE VIEW  trace AS
   (SELECT st_makeline(point) as dhtrace,
           dhole.bhid FROM dhole
    GROUP BY dhole.bhid);

-- create topo (wireframe)
CREATE OR REPLACE VIEW topo AS
   (SELECT ST_DelaunayTriangles(ST_Union(point),10,2)
                             as ttopo FROM collar);

The spatial object plotted in the 3D viewer provided by SFCGAL looks like this

3D database objects plotted with SFCGAL-Viewer
3D database objects plotted with SFCGAL-Viewer

Adding real 3D operations support

About two year ago (2013) Oslandia and IGN developed SFCGAL, a wrapper of the Computational Geometry Algorithms Library (CGAL). SFCGAL was then added to PostGIS to enhance 3D operations.

SFCGAL adds the following functions to PostGIS

  • postgis_sfcgal_version — Returns the version of SFCGAL in use
  • ST_Extrude — Extrude a surface to a related volume
  • ST_StraightSkeleton — Compute a straight skeleton from a geometry
  • ST_Orientation — Determine surface orientation
  • ST_ForceLHR — Force LHR orientation
  • ST_MinkowskiSum — Performs Minkowski sum
  • ST_3DIntersection — Perform 3D intersection
  • ST_3DArea — Computes area of 3D geometries
  • ST_Tesselate — Perform surface Tesselation

These functions are only a small subset CGAL, which is one of the most advanced open source computational geometry libraries. Examples of functions or algorithms non-implemented yet are advance delaunay triangulations, mesh generation and my favorite… the spatial sorting and searching algorithms

This video provides a demo from Oslandia, showing true spatial 3D operations like wireframe solids unions, differences and intersections.

Oslandia also provides a plugin to visualize 3D data from QGIS named Horao. An explanation of the plugin Horao and SFCGAL can be seen in this Oslandia’s video.

The project PostGIS with SFCGAL binding is in an early stage. Binary distributions are not available yet, which makes difficult and tedious the installation process. Despite these limitations this project brings to us the opportunity to fully implement in PostgreSQL applications for resource estimation and grade control.

PostgreSQL with PostGIS is the actual best option in terms of spatial object support and third-party software availability (like QGIS, MapServer, ArcGIS, MapInfo).

PostgreSQL MySQL
Best known support for spatial object and operation, with potential for real 3D operations. 2D and 3D objects implemented Basic implementation of spatial objects in 2D+

Conclusions

PostgreSQL with PostGIS is probably the best option of RDBMS to implement geological database. It provides performance, consistence and extensive support of spatial objects at zero cost in terms of software license. In addition connections from many third-party software.

This link shows some “Converting from other Databases to PostgreSQL” tips and tools

Print Friendly

One thought on “How to choose a geological relational database management system

Comments are closed.