Example of DOV search methods for boreholes (boringen)

Use cases explained below

  • Get boreholes in a bounding box
  • Get boreholes with specific properties
  • Get boreholes in a bounding box based on specific properties
  • Select boreholes in a municipality and return depth
  • Get boreholes based on fields not available in the standard output dataframe
  • Get borehole data, returning fields not available in the standard output dataframe
  • Get boreholes in a municipality and where groundwater related data are available

In [1]:
%matplotlib inline
import inspect, sys
import warnings; warnings.simplefilter('ignore')

In [2]:
# check pydov path
import pydov

Get information about the datatype 'Boring'


In [3]:
from pydov.search.boring import BoringSearch
boring = BoringSearch()

A description is provided for the 'Boring' datatype:


In [4]:
boring.get_description()


Out[4]:
'In de DOV-databank is elke waarneming van grondlagen een boring. Bij de meeste boringen wordt er met een boortoestel een gat gemaakt in de ondergrond om de verschillende grondlagen te kunnen beschrijven. Aan de hand van een boring krijg je een beeld van het materiaal in de ondergrond met toenemende diepte. Afhankelijk van het doel waarvoor de boring geplaatst wordt, zal men een geschikte boormethode toepassen. Boringen worden geplaatst voor verkennend bodemonderzoek, monstername van het sediment en/of grondwater, bepaling van bodemfysische parameters, milieuhygiënisch onderzoek,… Afhankelijk van de diepte, soort materiaal, en het al dan niet boren tot onder de grondwatertafel kan men kiezen uit verscheidene systemen voor handmatig of machinaal te boren. Het bodemmateriaal dat vrijkomt, kan gebruikt worden om een profiel van de ondergrond op te stellen of om er grondmonsters van te nemen om verdere analyses op uit te voeren. Vaak is het de bedoeling een put uit te bouwen zodat water kan gewonnen worden (zie ook grondwatermeetnet en grondwatervergunningen). Soms worden boringen uitgevoerd om een aantal geotechnische karakteristieken te bepalen of om wetenschappelijk onderzoek uit te voeren. Oppervlakkige waarnemingen van de ondergrond noemen we ook boringen. Vooral rond 1900 beschreven een aantal geologen vaak de oppervlakkige lagen. In de databank staan er dan ook verschillende boringen met een diepte van 0 meter. Het gaat vooral om weginsnijdingen of om zichtbare lithologische kenmerken langs de oppervlakte.'

The different fields that are available for objects of the 'Boring' datatype can be requested with the get_fields() method:


In [5]:
fields = boring.get_fields()

# print available fields
for f in fields.values():
    print(f['name'])


id
boornummer
pkey_boring
rapport
diepte_boring_tot
datum_aanvang
namen
putnummer
x
y
start_boring_mtaw
gemeente
uitvoerder
doel
methode
erkenning
opdrachtgever
informele_stratigrafie
formele_stratigrafie
lithologische_beschrijving
gecodeerde_lithologie
hydrogeologische_stratigrafie
quartaire_stratigrafie
geotechnische_codering
informele_hydrostratigrafie
doorheen_quartair
dikte_quartair
tertiair_onder_quartair
opdrachten
mv_mtaw
diepte_boring_van
boorgatmeting
diepte_methode_van
diepte_methode_tot
boormethode

You can get more information of a field by requesting it from the fields dictionary:

  • name: name of the field
  • definition: definition of this field
  • cost: currently this is either 1 or 10, depending on the datasource of the field. It is an indication of the expected time it will take to retrieve this field in the output dataframe.
  • notnull: whether the field is mandatory or not
  • type: datatype of the values of this field

In [6]:
fields['diepte_boring_tot']


Out[6]:
{'name': 'diepte_boring_tot',
 'definition': 'Maximumdiepte van de boring ten opzichte van het aanvangspeil, in meter.',
 'type': 'float',
 'notnull': False,
 'query': True,
 'cost': 1}

Optionally, if the values of the field have a specific domain the possible values are listed as values:


In [7]:
fields['methode']['values']


Out[7]:
{'Meerdere technieken': None,
 'avegaarboring': None,
 'droge boring': None,
 'edelmanboring': None,
 'geen boring': None,
 'gestoken boring': None,
 'graafmachine': None,
 'handboring': None,
 'kernboring': None,
 'lansen': None,
 'lepelboring': None,
 'luchthamer': None,
 'luchthevelboren of air-lift boren': None,
 'meerdere technieken': None,
 'omgek. spoelboring': None,
 'onbekend': None,
 'pulsboring': None,
 'ramkernboring': None,
 'rollerbit': None,
 'slagboring': None,
 'spade': None,
 'spiraalboring': None,
 'spoelboring': None,
 'steenboring': None,
 'trilboring': None,
 'zuigboring': None}

Example use cases

Get boreholes in a bounding box

Get data for all the boreholes that are geographically located within the bounds of the specified box.

The coordinates are in the Belgian Lambert72 (EPSG:31370) coordinate system and are given in the order of lower left x, lower left y, upper right x, upper right y.


In [8]:
from pydov.util.location import Within, Box

df = boring.search(location=Within(Box(153145, 206930, 153150, 206935)))
df.head()


[000/001] c
Out[8]:
pkey_boring boornummer x y mv_mtaw start_boring_mtaw gemeente diepte_boring_van diepte_boring_tot datum_aanvang uitvoerder boorgatmeting diepte_methode_van diepte_methode_tot boormethode
0 https://www.dov.vlaanderen.be/data/boring/1974... GEO-74/254-b1 153147.0 206931.0 14.12 14.12 Antwerpen 0.0 14.05 1974-07-02 Rijksinstituut voor Grondmechanica False 0.0 1.30 lepelboring
1 https://www.dov.vlaanderen.be/data/boring/1974... GEO-74/254-b1 153147.0 206931.0 14.12 14.12 Antwerpen 0.0 14.05 1974-07-02 Rijksinstituut voor Grondmechanica False 1.3 13.50 pulsboring
2 https://www.dov.vlaanderen.be/data/boring/1974... GEO-74/254-b1 153147.0 206931.0 14.12 14.12 Antwerpen 0.0 14.05 1974-07-02 Rijksinstituut voor Grondmechanica False 13.5 14.05 lepelboring

The dataframe contains one borehole where three methods ('boormethode') were applied for its construction. The available data are flattened to represent unique attributes per row of the dataframe.

Using the pkey_boring field one can request the details of this borehole in a webbrowser:


In [9]:
for pkey_boring in set(df.pkey_boring):
    print(pkey_boring)


https://www.dov.vlaanderen.be/data/boring/1974-010351

Get boreholes with specific properties

Next to querying boreholes based on their geographic location within a bounding box, we can also search for boreholes matching a specific set of properties. For this we can build a query using a combination of the 'Boring' fields and operators provided by the WFS protocol.

A list of possible operators can be found below:


In [10]:
[i for i,j in inspect.getmembers(sys.modules['owslib.fes'], inspect.isclass) if 'Property' in i]


Out[10]:
['PropertyIsBetween',
 'PropertyIsEqualTo',
 'PropertyIsGreaterThan',
 'PropertyIsGreaterThanOrEqualTo',
 'PropertyIsLessThan',
 'PropertyIsLessThanOrEqualTo',
 'PropertyIsLike',
 'PropertyIsNotEqualTo',
 'PropertyIsNull',
 'SortProperty']

In this example we build a query using the PropertyIsEqualTo operator to find all boreholes that are within the community (gemeente) of 'Herstappe':


In [11]:
from owslib.fes import PropertyIsEqualTo

query = PropertyIsEqualTo(propertyname='gemeente',
                          literal='Herstappe')
df = boring.search(query=query)

df.head()


[000/002] cc
Out[11]:
pkey_boring boornummer x y mv_mtaw start_boring_mtaw gemeente diepte_boring_van diepte_boring_tot datum_aanvang uitvoerder boorgatmeting diepte_methode_van diepte_methode_tot boormethode
0 https://www.dov.vlaanderen.be/data/boring/2016... kb33d106e-B236 224687.4 158191.0 124.0 124.0 Herstappe 0.0 6.0 NaN Belgische Geologische Dienst (BGD) False 0.0 6.0 gestoken boring
1 https://www.dov.vlaanderen.be/data/boring/1993... kb41d120e-B1027 224820.0 157794.0 132.0 132.0 Herstappe 0.0 50.0 1993-09-02 Peeters-Ramsel False 0.0 50.0 onbekend

Once again we can use the pkey_boring as a permanent link to the information of these boreholes:


In [12]:
for pkey_boring in set(df.pkey_boring):
    print(pkey_boring)


https://www.dov.vlaanderen.be/data/boring/2016-125511
https://www.dov.vlaanderen.be/data/boring/1993-096210

Get boreholes in a bounding box based on specific properties

We can combine a query on attributes with a query on geographic location to get the boreholes within a bounding box that have specific properties.

The following example requests the boreholes with a depth greater than or equal to 2000 meters within the given bounding box.

(Note that the datatype of the literal parameter should be a string, regardless of the datatype of this field in the output dataframe.)


In [13]:
from owslib.fes import PropertyIsGreaterThanOrEqualTo

query = PropertyIsGreaterThanOrEqualTo(
            propertyname='diepte_boring_tot',
            literal='2000')

df = boring.search(
    location=Within(Box(200000, 211000, 205000, 214000)),
    query=query
    )

df.head()


[000/006] cccccc
Out[13]:
pkey_boring boornummer x y mv_mtaw start_boring_mtaw gemeente diepte_boring_van diepte_boring_tot datum_aanvang uitvoerder boorgatmeting diepte_methode_van diepte_methode_tot boormethode
0 https://www.dov.vlaanderen.be/data/boring/2016... B/1-102782 201775.5 212960.0 25.0 25.0 Mol 0.0 3600.0 NaN NaN False 0.0 0.0 onbekend
1 https://www.dov.vlaanderen.be/data/boring/2016... B/1-102783 201798.0 212963.0 25.0 25.0 Mol 0.0 3600.0 NaN NaN False 0.0 0.0 onbekend
2 https://www.dov.vlaanderen.be/data/boring/2016... B/1-102784 201768.0 212959.0 25.0 25.0 Mol 0.0 4905.0 2017-12-13 THV Daldrup - Smet True 0.0 207.0 zuigboring
3 https://www.dov.vlaanderen.be/data/boring/2016... B/1-102784 201768.0 212959.0 25.0 25.0 Mol 0.0 4905.0 2017-12-13 THV Daldrup - Smet True 207.0 4905.0 spoelboring
4 https://www.dov.vlaanderen.be/data/boring/2016... B/1-102785 201790.5 212962.0 25.0 25.0 Mol 0.0 4341.0 2016-03-02 THV Daldrup - Smet True 0.0 4341.0 spoelboring

We can look at one of the boreholes in a webbrowser using its pkey_boring:


In [14]:
for pkey_boring in set(df.pkey_boring):
    print(pkey_boring)


https://www.dov.vlaanderen.be/data/boring/2016-148767
https://www.dov.vlaanderen.be/data/boring/2016-148770
https://www.dov.vlaanderen.be/data/boring/2016-148765
https://www.dov.vlaanderen.be/data/boring/2016-148766
https://www.dov.vlaanderen.be/data/boring/2016-148764
https://www.dov.vlaanderen.be/data/boring/2016-148763

Select boreholes in a municipality and return depth

We can limit the columns in the output dataframe by specifying the return_fields parameter in our search.

In this example we query all the boreholes in the city of Ghent and return their depth:


In [15]:
query = PropertyIsEqualTo(propertyname='gemeente',
                          literal='Gent')
df = boring.search(query=query,
                   return_fields=('diepte_boring_tot',))
df.head()


Out[15]:
diepte_boring_tot
0 42.0
1 32.0
2 59.0
3 11.0
4 27.0

In [16]:
df.describe()


Out[16]:
diepte_boring_tot
count 2825.000000
mean 15.704354
std 31.988491
min 0.000000
25% 1.000000
50% 6.300000
75% 20.000000
max 660.000000

By discarding the boreholes with a depth of 0 m, we get a different result:


In [17]:
df[df.diepte_boring_tot != 0].describe()


Out[17]:
diepte_boring_tot
count 2169.000000
mean 20.454034
std 35.152394
min 0.100000
25% 5.000000
50% 11.000000
75% 22.000000
max 660.000000

In [19]:
ax = df[df.diepte_boring_tot != 0].boxplot()
ax.set_ylabel("Depth (m)");
ax.set_title("Distribution borehole depth Gent");


Get boreholes based on fields not available in the standard output dataframe

To keep the output dataframe size acceptable, not all available WFS fields are included in the standard output. However, one can use this information to select boreholes as illustrated below.

For example, make a selection of the boreholes in municipality the of Antwerp, for which a hydrogeological interpretation was performed:


In [52]:
from owslib.fes import And

query = And([PropertyIsEqualTo(propertyname='gemeente',
                               literal='Antwerpen'),
             PropertyIsEqualTo(propertyname='hydrogeologische_stratigrafie', 
                               literal='True')]
            )
df = boring.search(query=query,
                   return_fields=('pkey_boring', 'boornummer', 'x', 'y', 'diepte_boring_tot', 'datum_aanvang'))
df.head()


Out[52]:
pkey_boring boornummer x y diepte_boring_tot datum_aanvang
0 https://www.dov.vlaanderen.be/data/boring/1937... kb7d14e-B82 145457.0 224973.5 10.0 1937-01-01
1 https://www.dov.vlaanderen.be/data/boring/1969... kb7d14e-B110 143700.4 228086.3 12.0 1969-01-01
2 https://www.dov.vlaanderen.be/data/boring/1966... kb7d14e-B115 143067.0 227707.6 66.0 1966-01-01
3 https://www.dov.vlaanderen.be/data/boring/1966... kb7d14e-B117 144770.5 223430.6 49.0 1966-01-01
4 https://www.dov.vlaanderen.be/data/boring/1970... kb7d14e-B121 143597.8 223498.6 11.9 1970-01-01

Get borehole data, returning fields not available in the standard output dataframe

As denoted in the previous example, not all available fields are available in the default output frame to keep its size limited. However, you can request any available field by including it in the return_fields parameter of the search:


In [53]:
query = PropertyIsGreaterThanOrEqualTo(
            propertyname='diepte_boring_tot',
            literal='2000')

df = boring.search(query=query,
                   return_fields=('pkey_boring', 'boornummer', 'diepte_boring_tot',
                                  'informele_stratigrafie', 'formele_stratigrafie', 'lithologische_beschrijving',
                                  'gecodeerde_lithologie', 'hydrogeologische_stratigrafie', 'quartaire_stratigrafie',
                                  'geotechnische_codering', 'informele_hydrostratigrafie'))

df.head()


Out[53]:
pkey_boring boornummer diepte_boring_tot informele_stratigrafie formele_stratigrafie lithologische_beschrijving gecodeerde_lithologie hydrogeologische_stratigrafie quartaire_stratigrafie geotechnische_codering informele_hydrostratigrafie
0 https://www.dov.vlaanderen.be/data/boring/1981... kb8d7e-B224 2513.0 True True True False False False False False
1 https://www.dov.vlaanderen.be/data/boring/1952... kb8d17e-B272 2705.0 True True True True True False False False
2 https://www.dov.vlaanderen.be/data/boring/1935... kb17d31e-B197 2034.0 True True False True True False False False
3 https://www.dov.vlaanderen.be/data/boring/2016... B/1-102782 3600.0 False False False False False False False False
4 https://www.dov.vlaanderen.be/data/boring/2016... B/1-102783 3600.0 False False False False False False False False

The following full example return all boreholes where gemeente is 'Antwerpen' and either putnummer is not empty or doel starts with 'Grondwater' or erkenning is '2. Andere grondwaterwinningen'.


In [54]:
from owslib.fes import PropertyIsLike
from owslib.fes import PropertyIsNull
from owslib.fes import Or
from owslib.fes import Not

query = And([PropertyIsEqualTo(propertyname='gemeente',
                               literal='Antwerpen'),
             Or([Not([PropertyIsNull(propertyname='putnummer')]),
                PropertyIsLike(propertyname='doel',
                               literal='Grondwater%'),
                PropertyIsEqualTo(propertyname='erkenning', 
                                  literal='2. Andere grondwaterwinningen')]
               )]
           )
df = boring.search(query=query)
df.head()


[000/027] ccccccccccccccccccccccccccc
Out[54]:
pkey_boring boornummer x y mv_mtaw start_boring_mtaw gemeente diepte_boring_van diepte_boring_tot datum_aanvang uitvoerder boorgatmeting diepte_methode_van diepte_methode_tot boormethode
0 https://www.dov.vlaanderen.be/data/boring/1997... kb15d28e-B741 154825.0 212445.0 5.00 5.00 Antwerpen 0.0 36.5 1996-12-31 Meysen Dammekens (Dammekens)-Hoboken False 0.0 36.5 onbekend
1 https://www.dov.vlaanderen.be/data/boring/1996... kb15d43w-B393 151150.0 206550.0 18.09 18.09 Antwerpen 0.0 104.0 1996-06-26 Meysen Dammekens (Dammekens)-Hoboken False 0.0 104.0 spoelboring
2 https://www.dov.vlaanderen.be/data/boring/2016... B/1-102776 153099.0 213577.0 -1.00 -1.00 Antwerpen 0.0 20.0 NaN NaN False 0.0 0.0 onbekend
3 https://www.dov.vlaanderen.be/data/boring/2017... 1434-BG000106-9 149486.0 221697.0 5.98 5.98 Antwerpen 0.0 50.0 2017-02-09 SMET GWT EUROPE False 0.0 50.0 spoelboring
4 https://www.dov.vlaanderen.be/data/boring/2017... B/1-103025 152397.0 212058.0 -1.00 -1.00 Antwerpen 0.0 19.0 NaN NaN False 0.0 0.0 onbekend

Visualize results

Using Folium, we can display the results of our search on a map.


In [55]:
# import the necessary modules (not included in the requirements of pydov!)
import folium
from folium.plugins import MarkerCluster
from pyproj import Transformer

In [57]:
# convert the coordinates to lat/lon for folium
def convert_latlon(x1, y1):
    transformer = Transformer.from_crs("epsg:31370", "epsg:4326", always_xy=True)
    x2,y2 = transformer.transform(x1, y1)
    return x2, y2

df['lon'], df['lat'] = zip(*map(convert_latlon, df['x'], df['y'])) 
# convert to list
loclist = df[['lat', 'lon']].values.tolist()

In [58]:
# initialize the Folium map on the centre of the selected locations, play with the zoom until ok
fmap = folium.Map(location=[df['lat'].mean(), df['lon'].mean()], zoom_start=12)
marker_cluster = MarkerCluster().add_to(fmap)
for loc in range(0, len(loclist)):
    folium.Marker(loclist[loc], popup=df['boornummer'][loc]).add_to(marker_cluster)
fmap


Out[58]:
Make this Notebook Trusted to load map: File -> Trust Notebook