Survey data#

GeoST offers various functions for reading and parsing subsurface data into GeoST objects. In general, survey data can either be loaded from (local) files or requested from a service such as the BRO REST API. In either case, GeoST supports data from multiple sources and file formats.

Reading survey data#

By survey data, we refer to measurements (i.e., raw data) of the subsurface. These can include boreholes, CPTs, well logs, seismic or EM lines, and others (see Data structures for a more detailed description). In all cases, the data are parsed into a geost.Collection. The table below lists the currently supported data sources, associated reader functions, and resulting GeoST objects.

File format/data service

Read function

Returned GeoST object

Description

BHR-G

read_bhrg

Collection

(BRO) Geological boreholes from xml

BHR-GT

read_bhrgt

Collection

(BRO) Geotechnical boreholes from xml

BHR-GT-samples

read_bhrgt_samples

Collection

(BRO) Geotechnical boreholes - grainsize samples from xml

BHR-P

read_bhrp

Collection

(BRO) Pedological boreholes from xml

CPT

read_cpt read_gef_cpts

Collection

(BRO) Cone Penetration Tests from xml or gef

SFR

read_sfr

Collection

(BRO) Pedological soilprofile descriptions from xml

BRO REST-API

bro_api_read

Collection

BRO BHR-G, BHR-GT, BHR-GT-samples, BHR-P, CPT or SFR objects

Parquet or csv

read_table

Collection or DataFrame

Survey data stored as a table. Result of to_parquet or to_csv export methods.

NLOG excel export

read_nlog_cores

Collection or DataFrame

Reader for NLOG deep cores, see here

UU LLG cores

read_uullg_tables

Collection or DataFrame

Reader for csv distribution of Utrecht University student boreholes

BORIS XML

read_xml_boris

Collection or DataFrame

Reader for XML exports of the BORIS borehole description software

Reading data from the BRO REST-API#

Subsurface data are widely available in the Netherlands via the portal of the Basis Registratie Ondergrond (BRO). GeoST can directly load this data for an area of interest.

import geost

# Read a few BRO pedological soil cores in a small area 250 m x 500 m
boreholes = geost.bro_api_read("BHR-P", bbox=(142_000, 455_000, 142_250, 455_500))
boreholes

Collection
  header (rows, columns) : (8, 10)
  data (rows, columns)   : (40, 8)
crs: RD
vertical datum: NAP height

You can see that this loads the soil cores as a geost.Collection. The same approach is also supported for geological (BHR-G) and geotechnical (BHR-GT) boreholes, cone penetration test (CPT) data, and pedological soil profile descriptions (SFR). This facilitates the direct use of BRO data in any application.

Reading from local files#

A likely use case is using GeoST to load survey data stored in a tabular format, such as Parquet or CSV, and then using the available selection and analysis methods. For example, suppose you have survey data for multiple boreholes stored in a local Parquet file. Using geost.read_table, you can easily load it into a Collection or, if preferred, a pandas.DataFrame and use the data for further analysis:

borehole_file = geost.data.boreholes_usp(
    return_filepath=True
)  # Use the filepath instead of directly reading the borehole data
boreholes = geost.read_table(borehole_file)
print(boreholes)

Collection
  header (rows, columns) : (67, 6)
  data (rows, columns)   : (1398, 32)
crs: None
vertical datum: None

Now you can easily select the boreholes that contain peat ("V"), for example:

peat_boreholes = boreholes.select_by_values("lith", "V")
peat_boreholes

Collection
  header (rows, columns) : (32, 6)
  data (rows, columns)   : (670, 32)
crs: None
vertical datum: None

The same is possible with Pandas a DataFrame. Note that with a DataFrame, any GeoST method needs to be accessed through the .gst accessor, as shown in the Data structures section. This allows us to select the boreholes that contain peat in the same way as before. Let’s load the same borehole data, but this time as a pandas.DataFrame:

borehole_df = geost.read_table(borehole_file, as_collection=False)
borehole_df.head()
nr x y surface end top bottom lith zm zmk ... cons color lutum_pct plants shells kleibrokjes strat_1975 strat_2003 strat_inter desc
0 B31H0541 139585 456000 1.2 -9.9 0.00 0.20 K NaN NaN ... NaN ON NaN 0 0 0 NaN EC NaN [TEELAARDE#***#****#*] ..........................
1 B31H0541 139585 456000 1.2 -9.9 0.20 0.60 K NaN NaN ... NaN BR NaN 0 0 0 NaN EC NaN [KLEI#***#****#*] grysbruin.
2 B31H0541 139585 456000 1.2 -9.9 0.60 0.95 V NaN NaN ... NaN BR NaN 0 0 0 NaN NI NaN [VEEN#***#****#*] donkerbruin.
3 B31H0541 139585 456000 1.2 -9.9 0.95 2.80 Z NaN ZMFO ... NaN GR NaN 0 0 0 NaN EC NaN [ZAND#***#****#*] FYN TOT matig fyn# iets slib...
4 B31H0541 139585 456000 1.2 -9.9 2.80 4.20 Z NaN ZFC ... NaN BR NaN 0 0 0 NaN BXWI NaN [ZAND#***#****#*] fyn# grysbruin.

5 rows × 32 columns

Now we can make the same selection using the .gst accessor:

peat_df = borehole_df.gst.select_by_values("lith", "V")
peat_df
nr x y surface end top bottom lith zm zmk ... cons color lutum_pct plants shells kleibrokjes strat_1975 strat_2003 strat_inter desc
0 B31H0541 139585 456000 1.20 -9.90 0.00 0.20 K NaN NaN ... NaN ON NaN 0 0 0 NaN EC NaN [TEELAARDE#***#****#*] ..........................
1 B31H0541 139585 456000 1.20 -9.90 0.20 0.60 K NaN NaN ... NaN BR NaN 0 0 0 NaN EC NaN [KLEI#***#****#*] grysbruin.
2 B31H0541 139585 456000 1.20 -9.90 0.60 0.95 V NaN NaN ... NaN BR NaN 0 0 0 NaN NI NaN [VEEN#***#****#*] donkerbruin.
3 B31H0541 139585 456000 1.20 -9.90 0.95 2.80 Z NaN ZMFO ... NaN GR NaN 0 0 0 NaN EC NaN [ZAND#***#****#*] FYN TOT matig fyn# iets slib...
4 B31H0541 139585 456000 1.20 -9.90 2.80 4.20 Z NaN ZFC ... NaN BR NaN 0 0 0 NaN BXWI NaN [ZAND#***#****#*] fyn# grysbruin.
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
1352 B32C1800 140571 455934 2.08 -2.92 0.70 1.20 Z NaN ZMF ... NaN GR NaN 0 0 0 NaN NaN NaN BRON:Boormanager;VAN:70.0000;TOT:120.0000;H:Z;...
1353 B32C1800 140571 455934 2.08 -2.92 1.20 1.50 K NaN NaN ... NaN GR NaN 0 0 0 NaN NaN NaN BRON:Boormanager;VAN:120.0000;TOT:150.0000;H:K...
1354 B32C1800 140571 455934 2.08 -2.92 1.50 1.70 V NaN NaN ... NaN BR NaN 0 0 0 NaN NaN NaN BRON:Boormanager;VAN:150.0000;TOT:170.0000;H:V...
1355 B32C1800 140571 455934 2.08 -2.92 1.70 2.00 Z NaN ZUF ... NaN BR NaN 0 0 0 NaN NaN NaN BRON:Boormanager;VAN:170.0000;TOT:200.0000;H:Z...
1356 B32C1800 140571 455934 2.08 -2.92 2.00 5.00 Z NaN ZUF ... NaN BR NaN 0 0 0 NaN NaN NaN BRON:Boormanager;VAN:200.0000;TOT:500.0000;H:Z...

670 rows × 32 columns

Notice that the shape of peat_df is the same as before in the data table of the peat_boreholes Collection: 670 rows x 32 columns. The selection result is also the same:

peat_boreholes.data.equals(peat_df)  # Check equality of the two dataframes

True

As you can see, GeoST functionality can be used interchangeably on geost.Collection objects or DataFrame objects.

Positional columns#

GeoST requires that several data columns are present to ensure that the methods in a Collection will work. These are called “positional columns”. The required positional columns differ per type of method. For example, Collection.slice_depth_interval requires depth information about the surface level of surveys and the depth of layers while Collection.select_with_points requires a valid geometry. The presence of depth information or a valid geometry is needed for both methods to work however, both presences are optional. The method slice_depth_interval does not need a geometry to work and select_with_points does not need depth information. Therefore, their presence is only required when you want to use one of these methods. This was chosen as design to ensure the most flexibility for users with different needs.

The only mandatory presence is the positional column which identifies each individual survey (e.g. “nr”) in both the header and data table. The table below shows the required positional columns for all methods to work.

Name

dtype

Description

Mandatory

nr

int, float, string

Identification name/number/code of the point survey

Yes

x

int, float

X-, Easting- or lon-coordinate

No

y

int, float

Y-, Northing- or lat-coordinate

No

surface

int, float

Surface elevation of the point survey in m +NAP

In methods involving depth

end

int, float

End depth of a point survey in m +NAP

No

geometry

shapely.geometry.Point in case of point data

Geometry object of the survey location

In spatial methods

depth/bottom

int, float

Depth of a measurement or bottom depth of a layer with respect to the surface level

In methods involving depth

top

int, float

Top depth of a layer with respect to the surface level

No, is used in methods involving depth when the survey data is contains layered information

Note

The names for the postional columns in the table above are chosen as they are as these are well-descriptive for the type of information they provide. However, it is not mandatory that the positional columns are named exactly like the names in the table above.

GeoST automatically determines which columns to use as positional columns. These can be checked with any DataFrame by:

borehole_df.gst.positional_columns

{'nr': 'nr',
 'surface': 'surface',
 'end': 'end',
 'x': 'x',
 'y': 'y',
 'top': 'top',
 'depth': 'bottom'}

The table below shows which columns are automatically recognized as positional columns by GeoST.

Name

Recognized names

nr

“nr”, “bro_id”, “nitg_nr”, “nitg”, “boorp”

x

“x”, “x-coord”, “longitude”, “lon”, “easting”, “x_bottom_rd”, “x_rd_crd”, “x_calc_crd”

y

“y”, “y-coord”, “latitude”, “lat”, “northing”, “y_bottom_rd”, “y_rd_crd”, “y_calc_crd”

surface

“surface”, “maaiveld”, “mv”, “height_nap”, “surface_nap”

end

“end”, “einddiepte”, “einddiepte_nap”, “end_depth”, “end_depth_nap”

top

“top”, “tv_top_nap”, “top_diepte”, “top_depth”, “upperboundary”

depth

“depth”, “bottom”, tv_bottom_nap”, “basis_diepte”, “bottom_depth”, lowerboundary”

Note

geometry is not included in the positional columns as this uses the “active geometry column” attribute of a geopandas.GeoDataFrame

Note

The recognized names are case-insensitive as each name is checked in lowercase form.

To see how this works, let’s rename the “x” and “y” columns in borehole_df and check the positional columns again:

borehole_df.rename(columns={"x": "Longitude", "y": "Latitude"}, inplace=True)
borehole_df.gst.positional_columns

{'nr': 'nr',
 'surface': 'surface',
 'end': 'end',
 'x': 'Longitude',
 'y': 'Latitude',
 'top': 'top',
 'depth': 'bottom'}

You can see that the new names are automatically picked up as positional columns. If one of the positional columns is not detected, None is returned. We rename the "surface" column to an unknown name to demonstrate this:

borehole_df.rename(columns={"surface": "unknown-surface-name"}, inplace=True)
borehole_df.gst.positional_columns

{'nr': 'nr',
 'surface': None,
 'end': 'end',
 'x': 'Longitude',
 'y': 'Latitude',
 'top': 'top',
 'depth': 'bottom'}

Since "surface" is no longer recognized, trying to use an analysis method that requires depth information now raises a KeyError:

try:
    borehole_df.gst.slice_depth_interval(0, 10)
    print("Slicing successful")  # Only prints if no error is raised
except KeyError as e:
    print(e)  # Print the error message instead of actually raising the error

---------------------------------------------------------------------------
MissingDepthError                         Traceback (most recent call last)
Cell In[10], line 4
      1 try:
      2     borehole_df.gst.slice_depth_interval(0, 10)
      3     print("Slicing successful")  # Only prints if no error is raised
----> 4 except KeyError as e:
      5     print(e)  # Print the error message instead of actually raising the error

File ~/work/geost/geost/geost/validation/method_checks.py:64, in _requires_depth.<locals>.wrapper(self, *args, **kwargs)
     61     has_depth = self.has_depth_columns
     63 if not has_depth:
---> 64     raise MissingDepthError(  # TODO: Check formatting of this error message
     65         f"Method '{func.__name__}' requires depth information in the DataFrame. "
     66         "Please ensure that the DataFrame contains one of the following the "
     67         "required combinations of depth columns:"
     68         " - 'surface', 'top' and 'bottom'"
     69         " - 'surface' and 'bottom'"
     70         " - 'surface' and 'depth'"
     71     )
     72 return func(self, *args, **kwargs)

MissingDepthError: Method 'slice_depth_interval' requires depth information in the DataFrame. Please ensure that the DataFrame contains one of the following the required combinations of depth columns: - 'surface', 'top' and 'bottom' - 'surface' and 'bottom' - 'surface' and 'depth'

To solve this problem, you can rename your columns to use recognized names. Reader functions such as geost.read_table have a column_mapper parameter that takes a dictionary specifying which columns to rename and returns the data with the renamed columns. Reader functions raise a UserWarning if any of the optional positional columns cannot be found and a KeyError if the column identifying the surveys cannot be found.

A better solution is to use GeoST’s simple way of registering any column name as a positional column name, allowing all functionality to work with any kind of data:

geost.add_positional_columns({"surface": "unknown-surface-name"})
borehole_df.gst.positional_columns

Now, the previously unrecognized column name works again.

Note

Use the persist=True keyword argument to store the provided column aliases in a user-specific configuration file so they are automatically recognized in future sessions.

Use with generic GeoPandas/Pandas#

The .gst accessor also works with any GeoDataFrame or DataFrame instance, as long as it contains columns that can be recognized as positional columns, as described in the previous section. Therefore, any data that has been loaded or created without GeoST can also use the provided functionality through the accessor.

Let’s demonstrate this with a simple geopandas.GeoDataFrame containing two points:

import geopandas as gpd

gdf = gpd.GeoDataFrame(
    {"nr": [1, 2]}, geometry=gpd.points_from_xy([1, 10], [1, 20]), crs=28992
)
print(gdf)
print("\nSelection result:")
print(gdf.gst.select_within_bbox(0, 0, 2, 2))

This also works for any pandas.DataFrame:

import pandas as pd

df = pd.DataFrame(
    {"nr": ["a", "a"], "top": [0, 1], "bottom": [1, 2], "lith": ["clay", "sand"]}
)
print(df)
print("\nSelection result:")
print(df.gst.slice_by_values("lith", "clay"))

Note

GeoST also provides a convenience function geost.pandas_dataframe to create a Pandas DataFrame without explicitly having to import Pandas.