Example of DOV search methods for groundwater samples (grondwatermonsters)

Binder

Use cases:

  • Get groundwater samples in a bounding box

  • Get groundwater samples with specific properties

  • Get the coordinates of all groundwater samples in Ghent

  • Get groundwater samples based on a combination of specific properties

  • Get groundwater samples based on a selection of screens (filters)

[1]:

%matplotlib inline
import inspect, sys

[2]:

# check pydov path
import pydov

Get information about the datatype ‘GrondwaterMonster’

[3]:

from pydov.search.grondwatermonster import GrondwaterMonsterSearch
gwmonster = GrondwaterMonsterSearch()

A description is provided for the ‘GrondwaterMonster’ datatype:

[4]:

print(gwmonster.get_description())

In de Databank Ondergrond Vlaanderen zijn verschillende grondwatermeetnetten opgenomen. Deze meetnetten staan in functie van uitgebreide monitoringprogramma’s met de bedoeling een goed beeld te krijgen van de beschikbare grondwaterkwantiteit en grondwaterkwaliteit van de watervoerende lagen in Vlaanderen.

Deze kaartlaag toont alle watermonsters die in de meetnetten opgenomen zijn.

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

[5]:

fields = gwmonster.get_fields()

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

gw_id
filternummer
pkey_filter
grondwatermonsternummer
pkey_grondwatermonster
pkey_grondwaterlocatie
x
y
start_grondwaterlocatie_mtaw
gemeente
datum_monstername
kationen
anionen
zware_metalen
pesticiden_actieve_stoffen
pesticiden_relevante_metabolieten
niet_relevante_metabolieten_van_pesticiden
fysico_chemische_parameters
organische_verbindingen
chemisch_PFAS
andere_parameters
opdrachten
eerste_invoer
geom
parametergroep
parameter
detectie
waarde
eenheid
veld_labo

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

[6]:

# print information for a certain field
fields['waarde']

[6]:

{'name': 'waarde',
 'type': 'float',
 'definition': 'waarde (numeriek) van de parameter',
 'notnull': False,
 'query': False,
 'cost': 10}

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

[7]:

# if an attribute can have several values, these are listed under 'values', e.g. for 'parameter':
list(fields['parameter']['values'].items())[0:10]

[7]:

[('1112TCA', 'parameter 1,1,1,2-Tetrachloorethaan'),
 ('111TCA', 'parameter 1,1,1-trichloorethaan'),
 ('1122TCA', 'parameter 1,1,2,2-Tetrachloorethaan'),
 ('112TCA', 'parameter 1,1,2-trichloorethaan'),
 ('11DCA', 'parameter 1,1-Dichloorethaan'),
 ('11DCE', 'parameter 1,1-Dichlooretheen'),
 ('11DCPE', 'parameter 1,1-Dichloorpropeen'),
 ('123TCB', 'parameter 1,2,3-Trichloorbenzeen '),
 ('123TCPA', 'parameter 1,2,3-Trichloropropaan'),
 ('123TMB', 'parameter 1,2,3-Trimethylbenzeen')]

[8]:

fields['parameter']['values']['NH4']

[8]:

'parameter Ammonium'

Example use cases

Get groundwater samples in a bounding box

Get data for all the groundwater samples 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.

[9]:

from pydov.util.location import Within, Box

df = gwmonster.search(location=Within(Box(93378, 168009, 94246, 169873)))
df.head()

[000/001] .
[000/051] ..................................................
[050/051] .

[9]:
pkey_grondwatermonster grondwatermonsternummer pkey_grondwaterlocatie gw_id pkey_filter filternummer x y start_grondwaterlocatie_mtaw gemeente datum_monstername parametergroep parameter detectie waarde eenheid veld_labo
0 https://www.dov.vlaanderen.be/data/watermonste... SWPP023-38534 https://www.dov.vlaanderen.be/data/put/2018-00... SWPP023 https://www.dov.vlaanderen.be/data/filter/2007... 1 94053.0 169638.0 10.09 Wortegem-Petegem 2014-02-19 Kationen K NaN 2.99 mg/l LABO
1 https://www.dov.vlaanderen.be/data/watermonste... SWPP023-38534 https://www.dov.vlaanderen.be/data/put/2018-00... SWPP023 https://www.dov.vlaanderen.be/data/filter/2007... 1 94053.0 169638.0 10.09 Wortegem-Petegem 2014-02-19 Onbekend %AfwijkBalans NaN 6.20 % LABO
2 https://www.dov.vlaanderen.be/data/watermonste... SWPP023-38534 https://www.dov.vlaanderen.be/data/put/2018-00... SWPP023 https://www.dov.vlaanderen.be/data/filter/2007... 1 94053.0 169638.0 10.09 Wortegem-Petegem 2014-02-19 Anionen NO2 < 0.05 mg/l LABO
3 https://www.dov.vlaanderen.be/data/watermonste... SWPP023-38534 https://www.dov.vlaanderen.be/data/put/2018-00... SWPP023 https://www.dov.vlaanderen.be/data/filter/2007... 1 94053.0 169638.0 10.09 Wortegem-Petegem 2014-02-19 Kationen Na NaN 16.98 mg/l LABO
4 https://www.dov.vlaanderen.be/data/watermonste... SWPP023-38534 https://www.dov.vlaanderen.be/data/put/2018-00... SWPP023 https://www.dov.vlaanderen.be/data/filter/2007... 1 94053.0 169638.0 10.09 Wortegem-Petegem 2014-02-19 Fysico-chemische parameters pH NaN 7.45 Sörensen LABO

Using the pkey attributes one can request the details of the corresponding grondwatermonster in a webbrowser (only showing the first unique records):

[10]:

for pkey_grondwatermonster in df.pkey_grondwatermonster.unique()[0:5]:
    print(pkey_grondwatermonster)

https://www.dov.vlaanderen.be/data/watermonster/2014-246678
https://www.dov.vlaanderen.be/data/watermonster/1999-246645
https://www.dov.vlaanderen.be/data/watermonster/2014-246647
https://www.dov.vlaanderen.be/data/watermonster/2011-246677
https://www.dov.vlaanderen.be/data/watermonster/2000-246650

Get groundwater samples with specific properties

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

A list of possible operators can be found below:

[11]:

[i for i,j in inspect.getmembers(sys.modules['owslib.fes2'], inspect.isclass) if 'Property' in i]

[11]:

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

In this example we build a query using the PropertyIsEqualTo operator to find all groundwater samples that are within the community (gemeente) of ‘Leuven’:

[12]:

from owslib.fes2 import PropertyIsEqualTo

query = PropertyIsEqualTo(
            propertyname='gemeente',
            literal='Leuven')

df = gwmonster.search(query=query)
df.head()

[000/001] .
[000/208] ..................................................
[050/208] ..................................................
[100/208] ..................................................
[150/208] ..................................................
[200/208] ........

[12]:
pkey_grondwatermonster grondwatermonsternummer pkey_grondwaterlocatie gw_id pkey_filter filternummer x y start_grondwaterlocatie_mtaw gemeente datum_monstername parametergroep parameter detectie waarde eenheid veld_labo
0 https://www.dov.vlaanderen.be/data/watermonste... 2-0005/M2009 https://www.dov.vlaanderen.be/data/put/2017-00... 2-0005 https://www.dov.vlaanderen.be/data/filter/1974... 1 171548.64 172680.85 26.21 Leuven 2009-12-14 Fysico-chemische parameters TOC NaN 37.29 mg/l LABO
1 https://www.dov.vlaanderen.be/data/watermonste... 2-0005/M2009 https://www.dov.vlaanderen.be/data/put/2017-00... 2-0005 https://www.dov.vlaanderen.be/data/filter/1974... 1 171548.64 172680.85 26.21 Leuven 2009-12-14 Zware metalen Al < 0.02 mg/l LABO
2 https://www.dov.vlaanderen.be/data/watermonste... 2-0005/M2009 https://www.dov.vlaanderen.be/data/put/2017-00... 2-0005 https://www.dov.vlaanderen.be/data/filter/1974... 1 171548.64 172680.85 26.21 Leuven 2009-12-14 Zware metalen Ni < 5.00 µg/l LABO
3 https://www.dov.vlaanderen.be/data/watermonste... 2-0005/M2009 https://www.dov.vlaanderen.be/data/put/2017-00... 2-0005 https://www.dov.vlaanderen.be/data/filter/1974... 1 171548.64 172680.85 26.21 Leuven 2009-12-14 Fysico-chemische parameters EC(Lab.) NaN 650.00 µS/cm(20°C) LABO
4 https://www.dov.vlaanderen.be/data/watermonste... 2-0005/M2009 https://www.dov.vlaanderen.be/data/put/2017-00... 2-0005 https://www.dov.vlaanderen.be/data/filter/1974... 1 171548.64 172680.85 26.21 Leuven 2009-12-14 Kationen NH4 NaN 0.29 mg/l LABO

Once again we can use the pkey_grondwatermonster as a permanent link to the information of the groundwater samples:

[13]:

for pkey_grondwatermonster in df.pkey_grondwatermonster.unique()[0:5]:
    print(pkey_grondwatermonster)

https://www.dov.vlaanderen.be/data/watermonster/2009-001087
https://www.dov.vlaanderen.be/data/watermonster/2009-001091
https://www.dov.vlaanderen.be/data/watermonster/2006-060958
https://www.dov.vlaanderen.be/data/watermonster/2009-060960
https://www.dov.vlaanderen.be/data/watermonster/2006-060964

We can add the descriptions of the parameter values as an extra column ‘parameter_label’:

[14]:

df['parameter_label'] = df['parameter'].map(fields['parameter']['values'])
df[['pkey_grondwatermonster', 'datum_monstername', 'parameter', 'parameter_label', 'waarde', 'eenheid']].head()

[14]:
pkey_grondwatermonster datum_monstername parameter parameter_label waarde eenheid
0 https://www.dov.vlaanderen.be/data/watermonste... 2009-12-14 TOC parameter Totaal organische koolstof 37.29 mg/l
1 https://www.dov.vlaanderen.be/data/watermonste... 2009-12-14 Al parameter Aluminium 0.02 mg/l
2 https://www.dov.vlaanderen.be/data/watermonste... 2009-12-14 Ni parameter Nikkel 5.00 µg/l
3 https://www.dov.vlaanderen.be/data/watermonste... 2009-12-14 EC(Lab.) parameter Geleidbaarheid in het labo 650.00 µS/cm(20°C)
4 https://www.dov.vlaanderen.be/data/watermonste... 2009-12-14 NH4 parameter Ammonium 0.29 mg/l

Get groundwater screens based on a combination of specific properties

Get all groundwater screens in Hamme that have measurements for cations (kationen). And filter to get only Sodium values after fetching all records.

[15]:

from owslib.fes2 import Or, Not, PropertyIsNull, PropertyIsLessThanOrEqualTo, And, PropertyIsLike

query = And([PropertyIsEqualTo(propertyname='gemeente',
                               literal='Hamme'),
             PropertyIsEqualTo(propertyname='kationen',
                               literal='true')
             ])
df_hamme = gwmonster.search(query=query,
                     return_fields=('pkey_grondwatermonster', 'parameter', 'parametergroep', 'waarde', 'eenheid','datum_monstername'))
df_hamme.head()

[000/001] .
[000/318] ..................................................
[050/318] ..................................................
[100/318] ..................................................
[150/318] ..................................................
[200/318] ..................................................
[250/318] ..................................................
[300/318] ..................

[15]:
pkey_grondwatermonster parameter parametergroep waarde eenheid datum_monstername
0 https://www.dov.vlaanderen.be/data/watermonste... HCO3 Anionen 854.00 mg/l 2014-02-25
1 https://www.dov.vlaanderen.be/data/watermonste... SO4 Anionen 18.00 mg/l 2014-02-25
2 https://www.dov.vlaanderen.be/data/watermonste... O2 Fysico-chemische parameters 0.13 mg/l 2014-02-25
3 https://www.dov.vlaanderen.be/data/watermonste... Mn Kationen 0.04 mg/l 2014-02-25
4 https://www.dov.vlaanderen.be/data/watermonste... K Kationen 12.00 mg/l 2014-02-25

You should note that this initial dataframe contains all parameters (not just the cations). The filter will only make sure that only samples where any cation was analysed are in the list. If we want to filter more, we should do so in the resulting dataframe.

[16]:

df_hamme = df_hamme[df_hamme.parameter=='Na']
df_hamme.head()

[16]:
pkey_grondwatermonster parameter parametergroep waarde eenheid datum_monstername
26 https://www.dov.vlaanderen.be/data/watermonste... Na Kationen 350.00 mg/l 2014-02-25
39 https://www.dov.vlaanderen.be/data/watermonste... Na Kationen 61.20 mg/l 2009-12-23
98 https://www.dov.vlaanderen.be/data/watermonste... Na Kationen 364.64 mg/l 2011-11-04
102 https://www.dov.vlaanderen.be/data/watermonste... Na Kationen 364.40 mg/l 2009-03-13
144 https://www.dov.vlaanderen.be/data/watermonste... Na Kationen 27.50 mg/l 2006-02-20

Working with water samples

For further analysis and visualisation of the time series data, we can use the data analysis library pandas and visualisation library matplotlib.

[17]:

import pandas as pd
import matplotlib.pyplot as plt

Query the data of a specific filter using its pkey:

[18]:

query = PropertyIsEqualTo(
            propertyname='pkey_filter',
            literal='https://www.dov.vlaanderen.be/data/filter/1991-001040')

df = gwmonster.search(query=query)
df.head()

[000/001] .
[000/014] cccccccccccccc

[18]:
pkey_grondwatermonster grondwatermonsternummer pkey_grondwaterlocatie gw_id pkey_filter filternummer x y start_grondwaterlocatie_mtaw gemeente datum_monstername parametergroep parameter detectie waarde eenheid veld_labo
0 https://www.dov.vlaanderen.be/data/watermonste... 4-0076/M2006 https://www.dov.vlaanderen.be/data/put/2017-00... 4-0076 https://www.dov.vlaanderen.be/data/filter/1991... 1 133981.28 199152.75 4.3 Hamme 2006-05-24 Fysico-chemische parameters Eh° NaN 120.00 mV LABO
1 https://www.dov.vlaanderen.be/data/watermonste... 4-0076/M2006 https://www.dov.vlaanderen.be/data/put/2017-00... 4-0076 https://www.dov.vlaanderen.be/data/filter/1991... 1 133981.28 199152.75 4.3 Hamme 2006-05-24 Kationen Fe NaN 9.45 mg/l LABO
2 https://www.dov.vlaanderen.be/data/watermonste... 4-0076/M2006 https://www.dov.vlaanderen.be/data/put/2017-00... 4-0076 https://www.dov.vlaanderen.be/data/filter/1991... 1 133981.28 199152.75 4.3 Hamme 2006-05-24 Anionen Cl NaN 108.00 mg/l LABO
3 https://www.dov.vlaanderen.be/data/watermonste... 4-0076/M2006 https://www.dov.vlaanderen.be/data/put/2017-00... 4-0076 https://www.dov.vlaanderen.be/data/filter/1991... 1 133981.28 199152.75 4.3 Hamme 2006-05-24 Kationen Mg NaN 39.20 mg/l LABO
4 https://www.dov.vlaanderen.be/data/watermonste... 4-0076/M2006 https://www.dov.vlaanderen.be/data/put/2017-00... 4-0076 https://www.dov.vlaanderen.be/data/filter/1991... 1 133981.28 199152.75 4.3 Hamme 2006-05-24 Anionen HCO3 NaN 863.40 mg/l LABO

The date is still stored as a string type. Transforming to a data type using the available pandas function `to_datetime <https://pandas.pydata.org/pandas-docs/stable/generated/pandas.to_datetime.html>`__ and using these dates as row index:

[19]:

df['datum_monstername'] = pd.to_datetime(df['datum_monstername'])

For many usecases, it is useful to create a pivoted table, showing the value per parameter

[20]:

pivot = df.pivot_table(columns=df.parameter, values='waarde', index='datum_monstername')
pivot

[20]:
parameter %AfwijkBalans Al As B Br CO3 Ca Cd Cl Co ... PO4(Tot.) Pb SO4 SomAN SomKAT T TOC Zn pH pH(Lab.)
datum_monstername
2006-05-24 NaN 0.05 5.00 NaN NaN NaN 399.00 0.50 108.00 NaN ... 0.1 5.0 488.00 NaN NaN 13.90 58.00 16.00 6.70 NaN
2009-03-13 NaN NaN 5.00 NaN NaN 1.0 394.70 0.50 96.00 NaN ... NaN 10.0 431.00 NaN NaN 12.70 8.59 10.00 6.76 6.80
2009-12-23 NaN NaN 12.47 NaN NaN 1.0 447.20 0.50 105.00 NaN ... NaN 5.0 433.60 NaN NaN 10.80 8.90 24.94 7.04 6.90
2011-03-28 NaN NaN 13.00 NaN NaN 1.0 331.10 0.52 107.00 NaN ... NaN 5.0 290.10 NaN NaN 12.00 9.98 12.98 6.70 6.90
2011-11-04 NaN NaN 8.40 112.0 NaN 1.0 345.00 0.50 99.00 5.00 ... NaN 5.0 385.49 NaN NaN 12.10 8.34 10.90 6.73 7.40
2012-11-26 NaN NaN NaN NaN NaN 1.0 360.00 NaN 95.00 NaN ... NaN NaN 313.00 NaN NaN 12.70 7.78 NaN 6.61 7.18
2014-02-25 NaN NaN 2.00 100.0 NaN 1.0 330.00 0.03 91.00 0.57 ... NaN 2.0 190.00 NaN NaN 12.40 7.50 5.00 6.82 7.10
2014-09-30 NaN 0.02 2.30 100.0 NaN 1.0 280.00 0.03 87.75 0.65 ... NaN 2.0 143.20 NaN NaN 12.40 5.80 5.00 6.82 6.90
2015-09-30 -6.05 0.05 5.00 115.5 NaN 1.0 299.41 0.50 88.88 5.00 ... NaN 5.0 171.15 22.35 19.80 11.99 9.19 47.29 6.68 6.80
2016-07-12 NaN 0.02 5.00 138.7 NaN 1.0 335.52 0.40 91.58 3.00 ... NaN 5.0 224.02 22.53 22.43 12.20 8.75 16.47 7.41 6.79
2017-03-27 -7.31 0.10 5.00 97.3 NaN 1.0 289.16 0.40 76.48 3.00 ... NaN 5.0 218.41 23.02 19.89 12.30 8.00 10.00 6.76 6.77
2018-09-03 0.83 0.02 5.00 134.9 NaN 1.0 354.30 0.40 88.60 3.00 ... NaN 5.0 222.84 23.20 23.59 12.30 9.14 10.00 6.87 6.88
2019-06-13 2.06 0.03 5.00 133.2 0.62 1.0 333.99 0.40 78.67 3.00 ... NaN 5.0 187.69 21.97 22.90 14.20 9.54 10.00 6.93 6.75
2020-06-09 0.23 0.02 5.00 179.7 0.58 1.0 291.60 0.40 64.94 3.00 ... NaN 5.0 134.33 20.46 20.55 13.60 11.25 10.00 6.63 6.79

14 rows × 40 columns

Plotting

The default plotting functionality of Pandas can be used:

[21]:

parameters = ['NO3', 'NO2', 'NH4']
ax = pivot[parameters].plot.line(style='.-', figsize=(12, 5))
ax.set_xlabel('');
ax.set_ylabel('concentration (mg/l)');
ax.set_title('Concentration nitrite, nitrate and ammonium for filter id 1991-001040');
../_images/notebooks_search_grondwatermonsters_47_0.png

Combine search in filters and groundwater samples

For this example, we will first search filters, and later search all samples for this selection. We will select filters in the primary network located in Kalmthout.

[22]:

from pydov.search.grondwaterfilter import GrondwaterFilterSearch
from pydov.util.query import Join

gfs = GrondwaterFilterSearch()

gemeente = 'Kalmthout'
filter_query = And([PropertyIsLike(propertyname='meetnet',
                       literal='meetnet 1 %'),
                    PropertyIsEqualTo(propertyname='gemeente',
                       literal=gemeente)])

filters = gfs.search(query=filter_query, return_fields=['pkey_filter'])

monsters = gwmonster.search(query=Join(filters, 'pkey_filter'))
monsters.head()

[000/001] .
[000/001] .
[000/212] ..................................................
[050/212] ..................................................
[100/212] ..................................................
[150/212] ..................................................
[200/212] ............

[22]:
pkey_grondwatermonster grondwatermonsternummer pkey_grondwaterlocatie gw_id pkey_filter filternummer x y start_grondwaterlocatie_mtaw gemeente datum_monstername parametergroep parameter detectie waarde eenheid veld_labo
0 https://www.dov.vlaanderen.be/data/watermonste... 1-0171/M2009 https://www.dov.vlaanderen.be/data/put/2017-00... 1-0171 https://www.dov.vlaanderen.be/data/filter/1975... 1 159591.0 229510.0 23.0 Kalmthout 2009-11-24 Fysico-chemische parameters O2 NaN 1.34 mg/l VELD
1 https://www.dov.vlaanderen.be/data/watermonste... 1-0171/M2009 https://www.dov.vlaanderen.be/data/put/2017-00... 1-0171 https://www.dov.vlaanderen.be/data/filter/1975... 1 159591.0 229510.0 23.0 Kalmthout 2009-11-24 Kationen NH4 NaN 0.09 mg/l LABO
2 https://www.dov.vlaanderen.be/data/watermonste... 1-0171/M2009 https://www.dov.vlaanderen.be/data/put/2017-00... 1-0171 https://www.dov.vlaanderen.be/data/filter/1975... 1 159591.0 229510.0 23.0 Kalmthout 2009-11-24 Zware metalen Cu < 5.00 µg/l LABO
3 https://www.dov.vlaanderen.be/data/watermonste... 1-0171/M2009 https://www.dov.vlaanderen.be/data/put/2017-00... 1-0171 https://www.dov.vlaanderen.be/data/filter/1975... 1 159591.0 229510.0 23.0 Kalmthout 2009-11-24 Anionen HCO3 NaN 18.29 mg/l LABO
4 https://www.dov.vlaanderen.be/data/watermonste... 1-0171/M2009 https://www.dov.vlaanderen.be/data/put/2017-00... 1-0171 https://www.dov.vlaanderen.be/data/filter/1975... 1 159591.0 229510.0 23.0 Kalmthout 2009-11-24 Anionen CO3 < 1.00 mg/l LABO

We will filter out some parameters, and show trends per location.

[23]:

parameter = 'NH4'
trends_sel = monsters[(monsters.parameter==parameter) & (monsters.veld_labo=='LABO')]
trends_sel = trends_sel.set_index('datum_monstername')
trends_sel['label'] = trends_sel['gw_id'] + ' F' + trends_sel['filternummer']

# By pivoting, we get each location in a different column
trends_sel_pivot = trends_sel.pivot_table(columns='label', values='waarde', index='datum_monstername')
trends_sel_pivot.index = pd.to_datetime(trends_sel_pivot.index)

# resample to yearly values and plot data
ax = trends_sel_pivot.resample('A').median().plot.line(style='.-', figsize=(12, 5))
ax.legend(loc='center left', bbox_to_anchor=(1, 0.5))

ax.set_title(f'Long term evolution of {parameter} in {gemeente}');
ax.set_xlabel('year');
ax.set_ylabel('concentration (mg/l)');
../_images/notebooks_search_grondwatermonsters_51_0.png