Abstract: A Web-Based Geotechnical Database Management System (GDBMS) is developed for Nigerian soils. The point database (Geo Data Nigeria) comprises the results of soil tests and observations from past soil investigations by different researchers and from diverse sources with a view to maximizing the economic and scientific benefits of such test results by ensuring their unlimited accessibility. Adequate web interface is provided without limitations to all members of the public to query, report and visualize the data contained within the database. The system is designed to be infinitely extensible as managed by authorized administrators. Appropriate data templates are therefore provided for data submission while ensuring data integrity and accuracy before inclusion in the database. The data are stored raw without any pre-analysis, giving the potential users the freedom to make their own independent deductions from them. Recommendations are made to enhance the management and continual expansion of the database.

INTRODUCTION

An undesirable situation is the lack of a readily available and comprehensive source of information on the engineering and geotechnical properties of Nigerian soils, especially with the large amount of geotechnical data that have been generated by engineers in numerous highway and geotechnical projects as well as in soils and materials investigations which have been carried out all over the country in connection with the increase in construction activities usually present in the process of infrastructure development in developing economy. Unfortunately, costly tedious and time-consuming as the acquisitions of these data were they either were forgotten in office files or enhanced only the practical experience of those directly involved in the investigations. Scientific knowledge being essentially cumulative, this has resulted in avoidable waste of money, time and efforts during the duplication of research works and has retarded the progress of soils and materials research and consequently the country’s economic growth in this area.

Ola (1983) expressed the urgent need to gather together in a single volume the numerous but scattered vital information on Nigerian soils from published and unpublished papers, bulletins, journals as well as official and unofficial reports. This view is shared by many engineers in the country and is in line with the government’s call to intensify research on locally available materials as a means of harnessing the country’s scarce resources and boosting its economy. Furthermore, Dowling (1968) noted that on completion of most road projects little further use is made of the information and experience acquired during construction. As this information could be profitably used again to reduce survey effort and guide the construction of further roads in similar terrain, many engineers are keenly aware of a need to develop methods for storing such data and experience.

It is sometimes difficult to obtain this information in developing countries because relevant information and previous experience if available at all are rarely well documented. The Transport and Road Research Laboratory (TRRL, 1978) also said that in developing countries, the data about the terrain that the planner and engineer require are often very scanty. The data that does exist is often wasted because it is not stored and classified so that it can be easily retrieved. It is of the utmost importance that developing countries store for future use all existing data so that it can be used for future planning.

Without doubt, a properly designed geotechnical database comprising historic and contemporary data would be of immense benefit (economic, scientific or otherwise) if made fully available to others. Such a database will be a source of primary information and reference for the use and guidance of consulting and practicing engineers, researchers, lecturers, students as well as foreign consultants wanting to do business in Nigeria. For example, it will be an invaluable aid to road planners, designers, engineer, contractors, etc., not only in preliminary road route location, tentative pavement design, land resource evaluation as well as regional planning of a region but also in the preparation for further detailed field work in the area of interest. It will provide without necessarily engaging in costly, time-consuming and labour-intensive field activities, a fair assessment of the nature and engineering properties of the subgrade soils to be encountered in the field.

This will be expedient and cost-effective in a developing country environment where there is a potential for rapid infrastructure development. Another area of application would be the assessment of the nature of the foundation soils in a region based on existing data from past subsoil investigations there.

Developing Geotechnical Database Management Systems (GDBMSs) is an area of research that has not been given adequate attention in Nigeria. This study has addressed this issue at length.

Geotechnical database management systems: Database management systems are already in common use in commercial and administrative sectors (Date, 1986; Walters, 1987). According to Lee et al. (1990), they have also been used in several aspects of Civil Engineering construction and practice but their application to the management of geotechnical data has been much less widespread.

The growth of database management systems have evolved from the collection and usage of data in an organized manner with a view to permitting the addition, modification, deletion and retrieval of the data. The simplest systems are the manually operated ones consisting of flat office files kept within filing cabinets or index cards.

The problem with such manual databases is that if the amount of data to be involved is sizable, it is certain that the system would be cumbersome and inefficient in data retrieval and analysis. A storage system using office stationery or index cards would need a rather complex design for the data store not to be too bulky and in such cases such data stores would not be amenable to easy reproducibility or transportability.

The efficiency and speed of data retrieval with it may also be limited, especially when data items satisfying multiple user-defined criteria are needed. For example, an indexing system for engineering information on soils using optical coincidence cards described by Lawrance (1977) seemed to be fast enough for such data stores but only because the number of data items in the store’s main library was limited by design to only 2500.

On the other hand, a computerized storage system makes for compact store on a computer hard disk (or floppy diskettes through which it becomes reproducible and transportable) as well as a capacity for an enormous number of data items. Retrieval of data (querying and reporting) is efficient and fast while the data can be easily processed and put to variable uses.

The earliest computerized storage systems for information on soils are stand-alone applications in spreadsheets or desktop database programs, often resulting in isolated databases on remote computers. One limitation of the system is that only one user can access the data at any one time.

In any case, replicating the database on different stand-alone microcomputers would be impracticable because it can easily lead to cross-contamination of the database, particularly when not all existing copies of the database are updated at the same time. Past attempts exist at development of stand-alone database management systems for soils.

A system was devised and used by Lee et al. (1990) to manage geotechnical instrument readings and test results on a project in Singapore. Okunade (1998) also developed a versatile microcomputer-based Geotechnical Database Management System (GDBMS) for the engineering subgrade soil properties (those relevant to highway design and construction) in Nigeria.

The problems involved with replicating the stand-alone databases on different microcomputers may be somewhat overcome by the installation of a network that will allow access to the database from different computers. An existing and well-tested network suitable for this purpose is the Internet. In fact, many Internet-based geotechnical database management systems now exist, made accessible by many national and international organizations and agencies.

A database containing spatial data usually consists of two main elements: a geographic (or geometric) component and an attribute data component. The geographic component holds information on the location, extent and topology of each mapping unit while the attribute data component describes the characteristics of the spatial unit. With regard to the geographic component, we distinguish between point and area databases.

Point databases involve attribute characteristics given for distinct points or representative attribute values given for areal mapping units. Both the geographic data and the attribute data are handled with a relational database management system, the location being geo-referenced through the point’s mapping coordinates (usually the longitude and latitudes of the points). The geographic component of area databases involve mapping units in the form of points, lines or surfaces and topology (shapes, neighbours and hierarchy of delineations) and these are best modelled and visualized by digitization using GIS (Geographic Information System) technologies. For area databases therefore, the relational database is integrated by the GIS in structured layers into the digitized base map to enable multi-row information for single features.

Geotechnical databases exist for units of different areal extents ranging from individual project sites to cities or municipal areas, counties, states, countries and regions consisting of several countries or whole continents. Lately, much work has been done in the sphere of compiling global databases by some multinational governmental bodies and private organizations.

For example, at the project site level, Benson et al. (1994) describes a database that contains laboratory measurements of hydraulic conductivity and associated soil properties that were extracted from construction reports for compacted soil liners conducted on a wide variety of soils from 67 landfills in North America. Also, Stanley and Hite (2006), Yoon (2005) and Yoon et al. (2006) all describe an Internet-based GDBMS involving data from two project sites (a bridge and a crossing) developed for the Virginia Department of Transportation using distributed GIS methodology for data management, archival, retrieval and analysis.

Cintron (2007) describes a geotechnical database developed for the city of Mayaguez, Puerto Rico in which the database is mainly composed of data gathered from local consulting firms and government agencies as well as geophysical fieldwork performed by various researchers. The geotechnical database was developed using a Geographic Information System (GIS) platform to build a graphical interface and to which layers are added to form an extensive database which allows the users to browse through and append additional information when available, by means of an easy and effective approach. Suwanwiwattana et al. (2001) describe a similar geotechnical database for the city of Bangkok based on existing data and using a GIS interface.

Rajesh et al. (2003) describes a GIS-based database system where the information obtained on the safe bearing capacities of the soil in the Southern part of Southern Chennai in India and subsequent generation of missing spatial data from borehole data through kriging interpolation techniques were integrated with the base map as well as the generated/interpolated thematic maps to obtain user-friendly Foundation-Suitability Maps. Johnston et al. (2003) describe the Australian Soil Resources Information System (ASRIS) database, a nationally consistent database of soil profile data, digital soil and land resources maps and climate, terrain and lithology datasets. ASRIS was compiled from the best publicly available information covering extensive soil point and soil survey map data that have been collected over >50 years by different agencies for different purposes. ASRIS was created as a national soils dataset to function as an important input into future natural resource management decisions. ASRIS is a point database.

In the case of the global databases where such did not exist initially, the recent trend and current tendency has been to combine databases compiled by various bodies into single units. Stolbovoi et al. (2001) discuss the results of an international project aimed at the development of the European Soil Geographic Database for the territories of Russia, Belarus, Moldova and Ukraine.

For the first time, unified European standards for soil description and classification have been applied for a vast territory from the western boundary of the former Soviet Union to the Far East region of Russia and a corresponding soil database has been developed. Special attention is paid to the correlation between national soil classifications, mapping units used in the unified European Soil Geographic Database and a system of soil units accepted in the world reference base for soil resources.

An example of these global databases is the Global and National Soils and Terrain (SOTER) digital databases, being administered by the International Soil Reference and Information Centre (ISRIC), based in Wageningen, the Netherlands (Batjes, 2002; ISRIC, 1991) but compiled through the collaboration of such bodies as the International Society of Soil Science (ISSS), the United Nations Environment Programme (UNEP), Food and Agriculture Organisation of the United Nations (FAO), International Food Policy Research Institute (IFPRI), International Institute for Applied Systems Analysis (IIASA) and such regional bodies as the European Economic Community (EEC), as well as many national governments and private organizations in the various continents.

The database system is map-based, in which a mapping unit can be any mappable surface feature (in effect an area, not necessarily contiguous). Representative values for the various soil attributes are thereafter recorded for each mapping (terrain) unit. The database management system is implemented with Microsoft Office Access. The structure has been developed to provide for a coverage of >230 countries on all the continents.

Another extensive geotechnical database compilation project is the one embarked upon by SoilVision Systems Ltd. of Saskatoon, Saskatchewan, Canada. (Hundeby and Fredlund, 2004; SoilVision Systems Ltd., 2010). The SoilVision database is a point database consisting of different datasets compiled from various sources from all over the world and currently contains high-quality saturated and unsaturated soil data for over 6,200 soil samples. It is in a globally standardized soils database format which is the same in Access, SQL Server and Oracle.

Its open database architecture allows complete flexibility in extracting data on any format from the database to other database software packages. It also permits easy importation of data from any table format. The project is an on-going one and the database is continually growing as more and more people and organizations around the world donate valuable soils data. There is currently data available for about 33 countries, 6 of which are African (Nigeria not included). The SoilVision database is versatile enough and is becoming quite popular among researchers and practitioners.

Design and implementation of the Geodatanigeria database: Due to the numerous advantages of the relational database model (Walters, 1987), the structure of the GeoData Nigeria database management system is based on the model. In a relational database, the data are stored in tables (called relations). Records in the database are in the form of rows or tuples within these tables. Tuples consist of sets of data called attributes which are arranged as columns in the relation. Once a sequence of attributes has been established within a relation, all tuples in that relation must appear in that sequence.

The uniformity of record format within a relation simplifies data-access procedures and is one of the advantages of the model. It is understandable that not all records would have values for all attributes and the relational model especially lends itself to maximizing the data storage requirements. The database itself is designed to be infinitely extensible, the only limitation being the capacity of the database server.

The decision to make the database web-based was mainly to provide easy and widespread accessibility. It was therefore necessary that it be written in the SQL database language, a relational database programming language well suited to web applications. The version of the SQL database language in which the database was implemented and written is the MySQL language, an open source relational DBMS software (Morris, 2003). MySQL backs quite a lot of web sites and seems the database of choice for web developers (Jorgensen, 2003). The server-side administrative interface to the database is provided through phpMyAdmin program. Both the MySQL and phpMyAdmin software are pre-installed on the web host server. The database’s portal can be found at http://www.geodatanigeria.uphero.com.

The current study is an extension of the versatile microcomputer-based Geotechnical Database Management System (GDBMS) for the engineering subgrade soil properties (those relevant to highway design and construction) in Nigeria earlier developed by Okunade (1998). The earlier GDBMS was implemented in the GW Basic programming language.

The database itself consisted of a Random Access file with provision for an unlimited total number of records, the only limitation being the capacity of the computer hard disk. However, the GDBMS was able to generate outputs consisting of records satisfying user-defined criteria (queries) in the form of sequential, tab-delimited, text files.

Such a file generated for all the records in the initial database has facilitated its conversion into other formats such as Microsoft Office Excel or Access. Before migration of the database to the web MySQL server, all occurrences of commas within it were replaced with some special character and the database converted to a comma-separated (csv) file which lends itself to easier migration to MySQL. The special characters were thereafter changed back to commas after successful migration. Thus, the 1,580 records in the earlier project became part of the current one.

Since the ultimate aim of the project is the eventual integration of the database into one or more existing or future global databases of similar or other environmental resources, especially the SoilVision database which currently does not have any dataset for and from Nigeria and which is a point database, the database was structured to be compatible with such global databases. In this regard, the structures of the databases of the SoilVision system (Hundeby and Fredlund, 2004) and the punched-card system reported by Lawrance (1977) to a great length influenced the structure of GeoData Nigeria and informed the extension of its tables to provide for many more attributes for more effective storage, administration and eventual usage of the database.

The Geo Data Nigeria database is a point database, with the sample location being geo-referenced through the point’s mapping coordinates (the longitude and latitudes of the points). However, to cater for the heterogeneity of soil over depth and to be able to accommodate borehole log data, the innovation of another reference attribute (the depth beneath the level of the natural ground surface) to form a three-dimensional coordinate system is introduced in this study. These three independent attributes form the primary key in the database. The other dependent attributes attempt to cover much of the geological and geotechnical information concerning a sample or data location, required in the Soilvision and punched-card systems referred to above. All these can be visualized in a user front-end to the database, created through php scripts which can be found at http://www.geodatanigeria.uphero.com/userinterface or in the downloadable Microsoft Office Excel and Access data templates made available there to guide data submission.

The user front-end to the database denies access to modify (insert, update or delete) records but provides unlimited access to query, report and visualize the data contained within the database. It also permits retrieval of data from the database in a variety of formats suitable for communication with or integration into other external processing tools to perform certain tasks such as calculations, statistics, modelling and visualization and eventual uploading of datasets for inclusion in the database by the administrators.

Since it is envisaged that most of the data to be entered into the database would be from external sources, data to be imported into the database would be vetted by the managing authorities for consistency, authenticity and accuracy. There are basically two methods of submitting data: electronic data transmissions and paper copy submissions.

It is however, important in both cases to authenticate the users submitting data. The former method is eminently encouraged above the latter. To this end, a file uploading feature to upload datasets in a variety of formats is included in the provided user front-end. Microsoft Access data templates of the database as well as instructions are also provided for download to guide the presentation and submission of data.

Ensuring data integrity and accuracy is of the utmost importance. To help ensure data accuracy, a number of systems, outlined by Caronna (2006) were put in place. In the design of the database were incorporated business rules that reduce the possibility of the insertion of incomplete and illogical data.

Obviously, no system can ensure that all data are completely accurate. However, by limiting data entry task to special agents, many classes of erroneous data can be prevented from being entered and others can be caught before the data are committed to the database.

Some of the systems incorporated, as outlined by Caronna (2006) include: using appropriate data types for the different categories of data enforced attribute selection through the provision of valid value lists in dropdown lists or non-editable combo boxes as the mouse cursor hovers on the attribute column; provision of valid ranges on numeric data through pop-ups and tooltips as the mouse cursor hovers on the attribute column; consistent usage of the metric system of units as the standard for data representation; removal of formatting and punctuation etc.

From the data, adequate usage of descriptive metadata; usage transparency in the form of detailed and unambiguous field labeling, on-screen documentation in the form of pop-ups and tooltips on selection or mouse-over of the items or attributes in question; data granularity whereby information especially of descriptive nature is broken up into smaller fields and using lookup lists for the comprehensive attributes defining the description and others. The data are stored raw without any pre-analysis, giving the potential users the freedom to make their own independent deductions from them.

CONCLUSION

The web-based GDBMS developed in this study completely satisfies all the conditions initially postulated. It has fulfilled the function of creating a single comprehensive national geotechnical database with access provided via a web portal.

The preliminary data comprising the database were collected mainly from reports of past soils and materials surveys carried out by various consulting firms for the federal and various state governments along various road axes over various periods of time, research data from research institutes, as well as data extracted from sundry publications and reports of past soils and materials surveys, research works and conferences on Nigerian soils.

The direct sources of most of these reports were the Federal and States Ministries of Works, Research Institutes; Highway, Geotechnical and Civil Engineering Firms, Soils and Materials Laboratories in the Universities and Polytechnics and others (Okunade, 1998). However, the database would normally require continual updating and extension.

Some problems were encountered during data collection. The first was that very scanty information could be collected from some of the states’ ministries and many of the private firms, stemming from the fact that the culture of keeping records is not yet well-developed in the country. In most cases, there had never been any comprehensive compilation or conscious storage of soil test results. Reports of soil tests conducted on consultancy basis are given out without copies being retained while other reports are utilized for whatever they are meant and immediately discarded or misplaced. Another problem was that most private consulting firms and construction companies visited refused to give out any information even where they were existing, claiming that they were ethically bound not to divulge any information on works conducted for their clients to anybody else. When informed that the information would be used for research and not for commercial purposes, their attitude did not change, asserting that they were in business for profit and were under no obligations to give information to aid research. In fact, it was strictly against the policy of a few of them to give such information, as the researcher discovered that many employees who flouted this rule had been sacked in the past.

RECOMMENDATIONS

All the attitudes towards the storage and dissemination of engineering information certainly do not augur well for technological development. The situation, though it has existed for a long time must not be allowed to continue. The web-based GeoDataNigeria database would require administering for proper maintenance. The administration and financing required for its proper maintenance would best be provided by governments or governmental agencies. For this purpose is recommended the establishment of a fully funded national Geotechnical Data Centre. The functions of the Geotechnical Data Centre would include the following:

ACKNOWLEDGEMENTS

The researcher expresses much appreciation to 000webhost (http://www.000webhost.com) for providing a free domain and the free hosting services used for the research. The user front-end to the database powered by DaDaBIK (http://www.dadabik.org) is also appreciated.