{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Retrieving and analysing BRO soil cores \n", "In this example, we will download soil cores (BRO BHR-P objects) and calculate the total\n", "thickness of sand, clay, silt and peat layers for each or the boreholes.\n", "\n", "First we import GeoST and retrieve soil cores from the area of Landgoed Oostbroek\n", "(located between Utrecht Science Park and Zeist). GeoST directly connects to the BRO\n", "REST-API service. This allows the user to download BRO objects from a spatial \n", "query and apply GeoST functionality to the loaded objects. In this case we will use \n", "a bounding box:" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "✅ Invalid surveys were dropped from the DataFrame because geost.config.validation.DROP_INVALID=True\n", "\n", "📖 See the user guide section on validation for advanced handling of validation issues: https://deltares-research.github.io/geost/user_guide/validation.html\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "C:\\src\\geost\\geost\\validation\\validate.py:77: ValidationWarning: \n", "============================================================\n", "⚠️ VALIDATION ISSUE (1/1)\n", "============================================================\n", "Column : 'surface'\n", "Message : Column 'surface' must not contain NaN values, but some values are NaN.\n", "# surveys : 2\n", "# rows : 8\n", "============================================================\n", "\n", " warnings.warn(\n", "C:\\src\\geost\\geost\\base.py:320: AlignmentWarning: Header table contains entries not present in the data. Removing entries ['BHR000000190070', 'BHR000000228773'] from the header.\n", " warnings.warn(\n" ] } ], "source": [ "import geost\n", "\n", "bhrp_cores = geost.bro_api_read(\n", " \"BHR-P\", bbox=(141_000, 455_200, 142_500, 456_000), crs=28992\n", ") # xmin, ymin, xmax, ymax" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Note that we received a validation warning because two of the boreholes (BHR000000228773 and BHR000000190070)\n", "do not have elevation data (column 'surface' has NaN values) as they are located in a water body. Objects that do not validate are dropped by default in GeoST. See more on validation and validation settings in the [User guide](../user_guide/validation.ipynb). Let's take a look now at what we have at this point:" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Collection\n", " header (rows, columns) : (85, 10)\n", " data (rows, columns) : (424, 8)\n", "crs: RD\n", "vertical datum: NAP height\n", "\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nrsurfacebegin_depthendghgglglandusexygeometry
0BHR0000000039231.980.00.480.0NaNloofbos142068.999144455571.998265POINT (142068.999 455571.998)
1BHR0000000041931.850.00.350.1NaNgraslandBlijvend142171.999332455443.998729POINT (142171.999 455443.999)
2BHR0000000087792.330.00.830.35NaNloofbos142051.999377455657.997234POINT (142051.999 455657.997)
3BHR0000000090512.260.00.760.35NaNfruitteeltGroen142243.999389455488.998061POINT (142243.999 455488.998)
4BHR0000000217852.330.00.830.5NaNloofbos141966.998934455592.99775POINT (141966.999 455592.998)
\n", "
" ], "text/plain": [ " nr surface begin_depth end ghg glg landuse \\\n", "0 BHR000000003923 1.98 0.0 0.48 0.0 NaN loofbos \n", "1 BHR000000004193 1.85 0.0 0.35 0.1 NaN graslandBlijvend \n", "2 BHR000000008779 2.33 0.0 0.83 0.35 NaN loofbos \n", "3 BHR000000009051 2.26 0.0 0.76 0.35 NaN fruitteeltGroen \n", "4 BHR000000021785 2.33 0.0 0.83 0.5 NaN loofbos \n", "\n", " x y geometry \n", "0 142068.999144 455571.998265 POINT (142068.999 455571.998) \n", "1 142171.999332 455443.998729 POINT (142171.999 455443.999) \n", "2 142051.999377 455657.997234 POINT (142051.999 455657.997) \n", "3 142243.999389 455488.998061 POINT (142243.999 455488.998) \n", "4 141966.998934 455592.99775 POINT (141966.999 455592.998) " ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "print(bhrp_cores)\n", "bhrp_cores.header.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "BRO objects come in the Rijksdriehoek coordinate reference system by default (EPSG: 28992),\n", "but the example project requires us to work in UTM 31N coordinates (EPSG: 32631). Hence we \n", "convert the CRS and check the result:" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "EPSG:32631\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nrsurfacebegin_depthendghgglglandusexygeometry
0BHR0000000039231.980.00.480.0NaNloofbos650624.2996755.773135e+06POINT (650624.3 5773135.144)
1BHR0000000041931.850.00.350.1NaNgraslandBlijvend650731.4479885.773011e+06POINT (650731.448 5773010.603)
2BHR0000000087792.330.00.830.35NaNloofbos650604.4829355.773221e+06POINT (650604.483 5773220.535)
3BHR0000000090512.260.00.760.35NaNfruitteeltGroen650801.9276955.773058e+06POINT (650801.928 5773057.944)
4BHR0000000217852.330.00.830.5NaNloofbos650521.6677805.773153e+06POINT (650521.668 5773152.779)
\n", "
" ], "text/plain": [ " nr surface begin_depth end ghg glg landuse \\\n", "0 BHR000000003923 1.98 0.0 0.48 0.0 NaN loofbos \n", "1 BHR000000004193 1.85 0.0 0.35 0.1 NaN graslandBlijvend \n", "2 BHR000000008779 2.33 0.0 0.83 0.35 NaN loofbos \n", "3 BHR000000009051 2.26 0.0 0.76 0.35 NaN fruitteeltGroen \n", "4 BHR000000021785 2.33 0.0 0.83 0.5 NaN loofbos \n", "\n", " x y geometry \n", "0 650624.299675 5.773135e+06 POINT (650624.3 5773135.144) \n", "1 650731.447988 5.773011e+06 POINT (650731.448 5773010.603) \n", "2 650604.482935 5.773221e+06 POINT (650604.483 5773220.535) \n", "3 650801.927695 5.773058e+06 POINT (650801.928 5773057.944) \n", "4 650521.667780 5.773153e+06 POINT (650521.668 5773152.779) " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bhrp_cores.to_crs(32631) # This is an in-place operation\n", "\n", "print(bhrp_cores.crs)\n", "bhrp_cores.header.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Furthermore, we'd only like to work with boreholes that have a minimum length of 1.5 m:" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Collection\n", " header (rows, columns) : (83, 10)\n", " data (rows, columns) : (414, 8)\n", "crs: WGS 84 / UTM zone 31N\n", "vertical datum: NAP height\n", "\n" ] } ], "source": [ "bhrp_cores = bhrp_cores.select_by_length(\n", " 1.5\n", ") # Not an in-place operation, so assign to new or overwrite existing variable!\n", "\n", "print(bhrp_cores)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "It looks like we ditched only two boreholes that were too short.\n", "\n", "Next, we need to get the main lithology (Zand, Leem, Klei or Veen = Sand, Silt, Clay or Peat) \n", "of each described layer in our collection of boreholes. We can use the \"standard_name\" column\n", "in the data, which is a lithological description including an indication of admixture:" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "\n", "[ 'sterkSiltigeKlei', 'matigSiltigeKlei', 'matigZandigeKlei',\n", " 'kleiigZand', 'uiterstSiltigeKlei', 'sterkZandigeKlei',\n", " 'zwakSiltigZand', 'sterkZandigeLeem', 'zwakZandigeLeem',\n", " 'mineraalarmVeen', 'matigSiltigZand', 'zwakKleiigVeen',\n", " 'sterkSiltigZand']\n", "Length: 13, dtype: str" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bhrp_cores.data[\"standardSoilName\"].unique()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Since we are only interested in the main lithology we need to simplify the names. There \n", "is no method for this provided by GeoST, so we will have to create our own. Luckily, GeoST\n", "is built on Pandas, which means that you can easily apply your own logic if\n", "built-in GeoST methods don't get you where you want. In this case, we will use a string method of a Pandas Series to use a regex (i.e. regular expression) which extracts the main lithology. This will reduce e.g. \"matigSiltigeKlei\" to \"Klei\"." ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "\n", "['Klei', 'Zand', 'Leem', 'Veen']\n", "Length: 4, dtype: str" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bhrp_cores.data[\"main_lithology\"] = bhrp_cores.data[\"standardSoilName\"].str.extract(\n", " r\"(Klei|Zand|Leem|Veen)$\",\n", " expand=False, # Find one of these words at the end of the standardSoilName\n", ")\n", "\n", "# Check and show that it worked:\n", "bhrp_cores.data[\"main_lithology\"].unique()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now we can proceed to compute the total thickness of our main lithologies by applying\n", "the built-in method \"get_cumulative_thickness\" on our just created \"main_lithology\" column." ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
nrsurfacebegin_depthendghgglglandusexygeometryKlei_thicknessZand_thicknessLeem_thicknessVeen_thickness
0BHR0000000039231.980.00.480.0NaNloofbos650624.2996755.773135e+06POINT (650624.3 5773135.144)1.50NaNNaNNaN
1BHR0000000041931.850.00.350.1NaNgraslandBlijvend650731.4479885.773011e+06POINT (650731.448 5773010.603)1.200.30NaNNaN
2BHR0000000087792.330.00.830.35NaNloofbos650604.4829355.773221e+06POINT (650604.483 5773220.535)1.50NaNNaNNaN
3BHR0000000090512.260.00.760.35NaNfruitteeltGroen650801.9276955.773058e+06POINT (650801.928 5773057.944)1.50NaNNaNNaN
4BHR0000000217852.330.00.830.5NaNloofbos650521.6677805.773153e+06POINT (650521.668 5773152.779)0.850.65NaNNaN
\n", "
" ], "text/plain": [ " nr surface begin_depth end ghg glg landuse \\\n", "0 BHR000000003923 1.98 0.0 0.48 0.0 NaN loofbos \n", "1 BHR000000004193 1.85 0.0 0.35 0.1 NaN graslandBlijvend \n", "2 BHR000000008779 2.33 0.0 0.83 0.35 NaN loofbos \n", "3 BHR000000009051 2.26 0.0 0.76 0.35 NaN fruitteeltGroen \n", "4 BHR000000021785 2.33 0.0 0.83 0.5 NaN loofbos \n", "\n", " x y geometry \\\n", "0 650624.299675 5.773135e+06 POINT (650624.3 5773135.144) \n", "1 650731.447988 5.773011e+06 POINT (650731.448 5773010.603) \n", "2 650604.482935 5.773221e+06 POINT (650604.483 5773220.535) \n", "3 650801.927695 5.773058e+06 POINT (650801.928 5773057.944) \n", "4 650521.667780 5.773153e+06 POINT (650521.668 5773152.779) \n", "\n", " Klei_thickness Zand_thickness Leem_thickness Veen_thickness \n", "0 1.50 NaN NaN NaN \n", "1 1.20 0.30 NaN NaN \n", "2 1.50 NaN NaN NaN \n", "3 1.50 NaN NaN NaN \n", "4 0.85 0.65 NaN NaN " ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# By including the \"include_in_header=True\" argument, the result will be added in-place\n", "# to the header table of the Collection. If False, the function will only return\n", "# a Pandas dataframe with the results.\n", "for lithoclass in bhrp_cores.data[\"main_lithology\"].unique():\n", " bhrp_cores.get_cumulative_thickness(\n", " \"main_lithology\", lithoclass, include_in_header=True\n", " )\n", "\n", "# Check if the new columns are present and correct in the header table\n", "bhrp_cores.header.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For the purpose of this example we will quickly plot the results on a map below. Hover your mouse\n", "over the points to display header information, including the layer thicknesses that we just added.\n", "From this point you would normally export the data or continue with further data analyses." ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
Make this Notebook Trusted to load map: File -> Trust Notebook
" ], "text/plain": [ "" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import xyzservices.providers as xyz # For accessing background map providers\n", "\n", "bhrp_cores.header.explore(\n", " tiles=xyz.nlmaps.grijs, style_kwds=dict(color=\"red\", weight=6)\n", ")" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.14.8" } }, "nbformat": 4, "nbformat_minor": 2 }