**3. Method**

This section consolidates the processes used to analyze geological-geotechnical profile, through pre-existing soil data obtained via SPT reports, applied in the studies of [19, 20]. The method includes data collection, digitization and treatment (pre-processing), in order to compose a geotechnical database, further processed in GIS environment. The steps are schematically shown in a diagram (**Figure 1**) and detailed in the sequence.

### **3.1. Data collection**

The first step of the method is the data collection. They can be obtained from different sources, such as official agencies, companies, previous publications and, when the required data is not available, it can be collected by the researcher. The accuracy of the results is directly related to the initial data input; therefore, data collection becomes an essential step for optimal results.

The process of data collection, together with the data treatment (next step), might be one of the most time-consuming steps, and it requires an advance planning. It is interesting to elaborate list of contacts, schedule the activities and establish goals for assisting in this step.

#### **3.2. Data treatment**

After gathering all the necessary data, the data treatment step begins. As shown in **Figure 1**, for the presented case studies (Section 4), the data treatments (pre-processing) are mainly done on the graphical data and on the SPT data, which are detailed hereafter.

#### *3.2.1. Graphic and vector data*

Using secondary data, a careful data treatment may be necessary. Data processing allows a fine-tuning of the data according to the required results. Much effort can be applied in this step, particularly when data obtained is not prepared to be handled in a GIS environment.

The routine previously published [22] guides the compatibility of the DGN or DWG (vectorial extension used by AutoCAD®) formats for SHP (extension used by QGIS® and ArcGIS®). For the presented cases, the following adjustments can be performed:

Geological-Geotechnical Database from Standard Penetration Test Investigations Using… http://dx.doi.org/10.5772/intechopen.74208 249

**Figure 1.** Work method diagram.

The methods and results of these two studies will be detailed as cases in this chapter. While

Considering the above, it is verified that studies related to geotechnical investigation associated with geospatial analyses have been carried out worldwide and have been aroused interest in practical and academic scope. A fact that encourages the growth of the works is the existence of numerous investigation reports already carried out in urban areas, becoming a potential subsoil characteristic database [21]. So, in order to stimulate further studies in the field, this chapter intends to clarify some ways to take advantage of pre-existing geotechnical data (SPT reports) and the methods of mapping and data processing, exemplifying the results by means

This section consolidates the processes used to analyze geological-geotechnical profile, through pre-existing soil data obtained via SPT reports, applied in the studies of [19, 20]. The method includes data collection, digitization and treatment (pre-processing), in order to compose a geotechnical database, further processed in GIS environment. The steps are schematically

The first step of the method is the data collection. They can be obtained from different sources, such as official agencies, companies, previous publications and, when the required data is not available, it can be collected by the researcher. The accuracy of the results is directly related to the initial data input; therefore, data collection becomes an essential step for optimal results. The process of data collection, together with the data treatment (next step), might be one of the most time-consuming steps, and it requires an advance planning. It is interesting to elaborate list of contacts, schedule the activities and establish goals for assisting in this step.

After gathering all the necessary data, the data treatment step begins. As shown in **Figure 1**, for the presented case studies (Section 4), the data treatments (pre-processing) are mainly done on

Using secondary data, a careful data treatment may be necessary. Data processing allows a fine-tuning of the data according to the required results. Much effort can be applied in this step, particularly when data obtained is not prepared to be handled in a GIS environment.

The routine previously published [22] guides the compatibility of the DGN or DWG (vectorial extension used by AutoCAD®) formats for SHP (extension used by QGIS® and ArcGIS®). For

the graphical data and on the SPT data, which are detailed hereafter.

the presented cases, the following adjustments can be performed:

[19] is focused on a large-scale analysis, the [20] is applied to a smaller one.

shown in a diagram (**Figure 1**) and detailed in the sequence.

of two cases.

248 Management of Information Systems

**3. Method**

**3.1. Data collection**

**3.2. Data treatment**

*3.2.1. Graphic and vector data*


Parallel to the treatment of graphic data, SPT reports are digitized and organized in a database, as described in the following subsections.

#### *3.2.2. SPT report data*

The composition of SPT reports database depends directly on the required results. Thus, this chapter emphasizes the data treatment processing for some applications of SPT report data

regarding groundwater level, impenetrable depth to SPT percussion, soil resistance and types of soil according to the particle size, used in the case studies.

All information available in the SPT report can be utilized. However, some inputs are important for basic analyses. **Table 1** lists the information available in the SPT report associated with some possible applications, mostly used in the case studies (Section 4).

Since SPT reports are often available in individual notes (usually printed or in PDF format), normally the data digitalization is required. Furthermore, specifically for the SPT data on the cases, it was performed a preliminary treatment prior to the database composition. This preprocessing was proceeded for each individual SPT borehole.

The location of the SPT boreholes (i) was not available directly on the report. The georeferencing of each SPT borehole was carried out using Google Earth®, through the indirect information of the reports (such as address, sketch, name of the enterprise). This information was used as a reference to obtain the most probable location of the borehole. The Google Earth® timeline tool, combined with the date of the SPT investigation, can also be useful. Since it was obtained indirectly, this SPT borehole georeferencing was not as accurate as a topographical georeferencing, however good enough to proceed some analyses.


N-value: abbreviation of the standard penetration resistance, the determination is given by the corresponding number of hits to driving 30 cm of standard sampler after an initial driving of 15 cm [5]

**Table 1.** SPT report data and its applications.

Borehole depth (ii), groundwater depth (iii), N-value of each depth (iv), geotechnical investigation date (v) and soil stratigraphy (vi) had their raw data digitized in electronic spreadsheets, along with the coordinates of each borehole (**Figure 2**), without preliminary treatments or filters. Spurious data can be removed during this process, according to the researcher's interpretation.

The SPT digitization and database organization aims to structure geological-geotechnical information in order to enable processing and data interpolation in GIS environment. In this sense, for the propose of all SPT borehole are considered in the interpolations, an N-value equal to 50 was inserted for the depths below the impenetrable. The idea is to establish a high N-value below of the SPT borehole last layer (the impenetrable layer) to prevent empty data, which could undermine the numerical modeling. As a simplification, the name rock was inserted after the impenetrable layer.

Subsequently, considering that reliable data are fundamental for database composing, the reports are analyzed to identify discrepancies in their information. A dry borehole near a water body or a shallow impenetrable depth near a deeper one is examples of possible inconsistencies. Furthermore, depending on the spacing between the SPT boreholes and the data application, it can be necessary and recommended to filter the SPT boreholes, aiming to save time and processing resources. In the case of [19], for example, to benefit data interpolation since clusters of SPT boreholes does not favor numerical modeling, for each cluster of SPT borehole it was selected one single borehole to represent the terrain. Thus, in order to obtain a better spatial distribution of SPT boreholes, the selection of the representative borehole of each cluster occurred in a more conservative scenario, considering the critical information in terms of foundation. The filtering was defined according to the steps [19]:

Step 1. Select the borehole with the greatest impenetrable depth;

Step 2. In the case of similarity of impenetrable depth, from the boreholes with the greatest impenetrable depth, select the hole with lower groundwater depth;

Step 3. In case of similarity of the greatest depth of impenetrable and lower groundwater depth, select the borehole with the lowest sum of N-value along the length of the borehole.


**Figure 2.** Geological-geotechnical database.

regarding groundwater level, impenetrable depth to SPT percussion, soil resistance and types

All information available in the SPT report can be utilized. However, some inputs are important for basic analyses. **Table 1** lists the information available in the SPT report associated with

Since SPT reports are often available in individual notes (usually printed or in PDF format), normally the data digitalization is required. Furthermore, specifically for the SPT data on the cases, it was performed a preliminary treatment prior to the database composition. This pre-

The location of the SPT boreholes (i) was not available directly on the report. The georeferencing of each SPT borehole was carried out using Google Earth®, through the indirect information of the reports (such as address, sketch, name of the enterprise). This information was used as a reference to obtain the most probable location of the borehole. The Google Earth® timeline tool, combined with the date of the SPT investigation, can also be useful. Since it was obtained indirectly, this SPT borehole georeferencing was not as accurate as a topographical georeferencing,

(i) Location of the SPT borehole The location of the SPT borehole is essential to proceed a geospatial

(ii) Borehole depth The depth reached by the SPT borehole up to the impenetrable is essential

potentiometric map, for example

lacks of other data sources (iv) N-value of each depth For more detailed analyses, such as foundation maps or iso-stress maps, it

(v) Geotechnical investigation date For a temporal analysis, the date of the SPT report is required. For

(vi) Soil stratigraphy Soil characteristics by depth provide information such as type (clay,

N-value: abbreviation of the standard penetration resistance, the determination is given by the corresponding

level

number of hits to driving 30 cm of standard sampler after an initial driving of 15 cm [5]

**Table 1.** SPT report data and its applications.

(iii) Groundwater depth The groundwater depth is used to obtain the groundwater level. This

analysis of all SPT report data. This step always includes the use of a coordinate reference system, to be chosen by the researcher, and the

information allows understanding the behavior of the water table among the studied region and, associated with the topography, to understand the directions of the groundwater flow. It can be used to construct a

It can be associated with other information and/or can help to understand

is necessary to have the N-value resistance for each meter of depth

instance, it can be used to get a seasonal variation of the groundwater

silt, sand), color, compactness and consistency. This information can be associated with empirical correlations which allow associating new information and new analysis for each layer of soil, such as: to simulate shallow and deep foundations for each type of soil; to associate a specific

weight to the soil, to analyze the stress according to soil type

identification of the horizontal location of each borehole

to develop the impenetrable to SPT percussion surfaces

of soil according to the particle size, used in the case studies.

250 Management of Information Systems

processing was proceeded for each individual SPT borehole.

however good enough to proceed some analyses.

**SPT borehole data Applications**

some possible applications, mostly used in the case studies (Section 4).

With the structured data, it is possible to start the data processing in a GIS environment, by consolidating and georeferencing the acquired data (graphical and SPT data), developing thematic maps and a geodatabase (associated with the boreholes location).

### **3.3. GIS processing**

In this step, based on the graphic and vector data, the objects referring to each layer (administrative limit and hydrography, for example) are isolated in their own layer and georeferenced. Graphic and vector data and SPT borehole locations are imported to a GIS environment. All data must be handled to the same coordinate reference system and imported to the same GIS environment.

Through georeferenced data, thematic maps are developed for the understanding of the characteristics of the study area, which will assist in the geotechnical analyses and data validation. As an example of thematic maps, **Figure 3** presents the Digital Elevation Model (DEM), the slope map, and the hydrographic map produced by [20].

These maps are a basis for the characterization of the topography. In addition, they contribute to the identification of the soil type in each borehole analyzed (sedimentary or residual, for example). The hydrographic map, in turn, is used to aid the consistency analyses of the SPT groundwater level and can improve the groundwater data, as performed in [19, 20], who incorporated fictitious SPT groundwater data along the main river of the study area.

The geological-geotechnical database developed (**Figure 2**) outside of the GIS environment can be incorporated into the SPT borehole already in GIS, using the ID of each borehole as a reference. Thus, each row of the database, which refers to a single SPT borehole, begins to contemplate the information of its report and location.

When the altimetry information is not available on the STP report, since the database is georeferenced, the elevation of each borehole can also be added by crossing the altimetry information arranged in the DEM with the location of the points. Additional columns can be created

**Figure 3.** Thematic maps [20].

in the database for calculating the impenetrable and groundwater elevation, subtracting the altimetry value from the respective depths of impenetrable and groundwater. Likewise, other information can be added to the database, depending on the available data and analysis interest, such as the terrain steepness (from a slope map) and the geotechnical unit (from the geotechnical engineering map).

The database can be handled to create secondary information. For instance, to generate admissible stress (σa) surface from N-value, [23] performed the analysis soil stress with an empirical model. The admissible soil stress for the shallow foundation is defined in [24] as:

$$
\sigma\_{\iota} = 0.02 \times \text{N-value (MPa)}\tag{1}
$$

Valid for natural soils with 5 ≤ N-value ≤ 20.

With the structured data, it is possible to start the data processing in a GIS environment, by consolidating and georeferencing the acquired data (graphical and SPT data), developing the-

In this step, based on the graphic and vector data, the objects referring to each layer (administrative limit and hydrography, for example) are isolated in their own layer and georeferenced. Graphic and vector data and SPT borehole locations are imported to a GIS environment. All data must be handled to the same coordinate reference system and imported to the same GIS

Through georeferenced data, thematic maps are developed for the understanding of the characteristics of the study area, which will assist in the geotechnical analyses and data validation. As an example of thematic maps, **Figure 3** presents the Digital Elevation Model (DEM), the

These maps are a basis for the characterization of the topography. In addition, they contribute to the identification of the soil type in each borehole analyzed (sedimentary or residual, for example). The hydrographic map, in turn, is used to aid the consistency analyses of the SPT groundwater level and can improve the groundwater data, as performed in [19, 20], who

The geological-geotechnical database developed (**Figure 2**) outside of the GIS environment can be incorporated into the SPT borehole already in GIS, using the ID of each borehole as a reference. Thus, each row of the database, which refers to a single SPT borehole, begins to

When the altimetry information is not available on the STP report, since the database is georeferenced, the elevation of each borehole can also be added by crossing the altimetry information arranged in the DEM with the location of the points. Additional columns can be created

incorporated fictitious SPT groundwater data along the main river of the study area.

matic maps and a geodatabase (associated with the boreholes location).

slope map, and the hydrographic map produced by [20].

contemplate the information of its report and location.

**3.3. GIS processing**

252 Management of Information Systems

environment.

**Figure 3.** Thematic maps [20].

It is necessary to prepare the database for creating an elevation map of specific admissible stress: setting up the N-value corresponding to the desired stress and identifying the depth of each borehole where this N-value is found. Since coordinates are associated with these depths, a spatial interpolation can be carried out to obtain the elevation of the desired stress (isobaric).

Another possibility is to generate the surfaces of admissible stress isovalues. In order to do it, for each meter of depth, the values of admissible stress are calculated according to Eq. (1). Since the equation is valid for a certain range, the isobaric of 0.4 MPa (4 kgf/cm<sup>2</sup> or 400 kN/m<sup>2</sup> ) is adopted as the upper limit of admissible stress, and 0.1 MPa (1 kgf/cm<sup>2</sup> or 100 kN/m<sup>2</sup> ) as the lower limit.

Also handling the database, [19, 20] used the N-value to create an orientation map of the foundation type (shallow or deep). The authors proposed criteria to guide the foundation type algorithm based on the precepts of economic feasibility exposed in [25] for shallow foundations and the limitations of the Eq. (1). The adopted criteria were:


Finally, with the database consolidated in the GIS environment, the information generated can be used for management and analyze of the registered data. In this context, geostatistics is indicated to transform a database into maps that enable the geological-geotechnical characterization of the study area. Geostatistics is distinguished from the conventional statistics because it considers the spatial or spatiotemporal location of the data in the analyses. The cases presented in the chapter made use of interpolations, applying ordinary kriging processes since it is a consolidated method in the soil science literature [26]. However, for each type of map elaborated, it is necessary to verify the semivariogram that best conforms to the data, i. e., the one which results in a smaller average error.

## **3.4. Result validation**

This section intends to clarify briefly some ways to validate the results and to understand their coverage. The first approach for validating the results is through numerical analyses. It can be done using a subset (training set) to build the map, and a test set to analyze it by comparing the predicted results with the test set. This holdout concept is highly applied in machine learning and can be used to geotechnical data as well. The holdout method is shown in **Figure 4**.

Some maps can be validated through on-site information collection. In addition, the foundation suitability map and a study about foundation characteristics, such as pile lengths, can be validated by analyzing the foundations executed in the study area.

[19] used on-site information to validate the soil units of Blumenau with experts of the city hall, while [20] carried out validations through the analysis of the executed foundation reports provided by the University. Based on the type and length of the foundations executed in 20 buildings distributed along the study area, the foundation suitability and the pile maximum length maps could be validated [20].

Another possibility is defining the number of the dataset and its distribution according to the spatial resolution required for the study. However, when working with pre-existing data, usually it is not possible to choose the dataset. In this case, the spatial resolution analysis can help to understand the coverage of the dataset.

In [27] (as cited in [28]), the spatial resolution of georeferenced data is defined as the content of the geometric domain divided by the number of observations, as shown in the Eq. (2). area <sup>⁄</sup>number of observations (2)

$$\mathcal{R} = \sqrt{^{\text{area}}\!/\_{\text{number of observations}}}\tag{2}$$

Where *R* is the spatial resolution.

**Figure 4.** Data validation—numerical analyses using the holdout approach.

[19] applied this spatial resolution approach to visualize the data distribution and coverage and to calculate, through literature comparison, the scale of the developed maps (**Figure 5**).

processes since it is a consolidated method in the soil science literature [26]. However, for each type of map elaborated, it is necessary to verify the semivariogram that best conforms to the

This section intends to clarify briefly some ways to validate the results and to understand their coverage. The first approach for validating the results is through numerical analyses. It can be done using a subset (training set) to build the map, and a test set to analyze it by comparing the predicted results with the test set. This holdout concept is highly applied in machine learning and can be used to geotechnical data as well. The holdout method is shown in **Figure 4**. Some maps can be validated through on-site information collection. In addition, the foundation suitability map and a study about foundation characteristics, such as pile lengths, can be

[19] used on-site information to validate the soil units of Blumenau with experts of the city hall, while [20] carried out validations through the analysis of the executed foundation reports provided by the University. Based on the type and length of the foundations executed in 20 buildings distributed along the study area, the foundation suitability and the pile maximum

Another possibility is defining the number of the dataset and its distribution according to the spatial resolution required for the study. However, when working with pre-existing data, usually it is not possible to choose the dataset. In this case, the spatial resolution analysis can

In [27] (as cited in [28]), the spatial resolution of georeferenced data is defined as the content of the geometric domain divided by the number of observations, as shown in the Eq. (2).

\_\_\_\_\_\_\_\_\_\_\_\_\_

area <sup>⁄</sup>number of observations (2)

data, i. e., the one which results in a smaller average error.

validated by analyzing the foundations executed in the study area.

**3.4. Result validation**

254 Management of Information Systems

length maps could be validated [20].

help to understand the coverage of the dataset.

**Figure 4.** Data validation—numerical analyses using the holdout approach.

*R* = √

Where *R* is the spatial resolution.

An analysis based on the background knowledge and visual inspection is also effective to understand the coverage of the dataset qualitatively. The resulting information is increasingly closer to reality when in possession of a robust database, that means a larger number of SPT boreholes distributed throughout the study area associated with a validation process. As part

**Figure 5.** Coverage of the dataset—spatial resolution [19].

of the visual analysis, [19] exemplifies the comparison between the elevation surface modeled from SPT reports (**Figure 6b**, developed with limited dataset) and from one modeled from contour lines (**Figure 6a**, developed with a rich dataset).

**Figure 6.** 3D Digital Elevation Model. Surfaces modeled from contour lines (a) and from SPT borehole coordinates (b) [19].

The data validation is an essential step because it helps to improve the results and prevent the elaboration of maps with unreal results. This stage can provide a better comprehension of the solution developed, and clarify the strengths and limitations of the study.
