PressureType codelist - 4th cycle

The review of the pressure types codelist used in the 3rd cycle of reporting is detailed in the Annexes - Data analysis - 3rd cycle and resulted in the following changes:

• A generic option ‘unknown’ was added.

• A generic option ‘none’ was added.

• The wording of some of the options was revised (while maintaining the code structure to facilitate the mapping).

• The option ‘P7 - Anthropogenic pressure - Other’ was removed.

• The option ‘P8 - Anthropogenic pressure - Unknown’ was removed.

• The option ‘P9 - Anthropogenic pressure - Historical pollution’ was removed.

• The option ‘P1-9 - Point - Other’ was replaced by ‘P1 - Point’

• The option ‘P2-10 - Diffuse - Other’ was replaced by ‘P2 - Diffuse’

• The option ‘P3-7 - Abstraction - Other’ was replaced by ‘P3 - Abstraction’

• The option ‘P4-1-4 - Physical alteration of channel/bed/riparian area/shore - Other’ was replaced by ‘P4-1 - Longitudinal barrier’

• The option ‘P4-2-8 - Dams, barriers and locks - Other’ was replaced by ‘P4-2 - Transversal barrier’

• The option ‘P4-3-6 - Hydrological alteration - Other’ was replaced by ‘P4-3 - Hydrological alteration’

• The option ‘P4-5 - Hydromorphological alteration - Other’ was replaced by ‘P4-5 - Hydromorphological alteration’

The revised PressureType codelist is a hierarchical codelist (see Figure 86).
It provides a controlled list of values for different attributes related to pressures.
Depending on the attribute and the context, the use of some values may be restricted by the quality control. For example: options ‘P4-1%’, ‘P4-2%’ or ‘P5%’ are unlikely to apply to groundwater, options like ‘P6%’ are unlike to apply to surface water bodies.

        ---
config:
  class:
    hideEmptyMembersBox: true
  layout: dagre
  theme: neutral
  themeVariables:
    noteBkgColor: lightYellow
    noteTextColor: grey
    noteBorderColor: grey 
---
classDiagram
direction TB

class PressureType{
  <<enumeration>>

«point»
P1 - Point sources
P1-1 - Point - Urban waste water
P1-2 - Point - Storm overflows
P1-3 - Point - IED plants
P1-4 - Point - Non IED plants
P1-5 - Point - Contaminated sites or abandoned industrial sites
P1-6 - Point - Waste disposal sites
P1-7 - Point - Mining
P1-8 - Point - Aquaculture

«diffuse»
P2 - Diffuse sources
P2-1 - Diffuse - Urban run-off
P2-2 - Diffuse - Agriculture
P2-3 - Diffuse - Forestry
P2-4 - Diffuse - Transport
P2-5 - Diffuse - Contaminated sites or abandoned industrial sites
P2-6 - Diffuse - Discharges not connected to sewerage network
P2-7 - Diffuse - Atmospheric deposition
P2-8 - Diffuse - Mining
P2-9 - Diffuse - Aquaculture

«abstraction»
P3 - Abstraction
P3-1 - Abstraction - Agriculture
P3-2 - Abstraction - Public water supply
P3-3 - Abstraction - Industry
P3-4 - Abstraction - Cooling water
P3-5 - Abstraction - Hydropower
P3-6 - Abstraction - Aquaculture

«hidromorphology»
P4 - Hydromorphology
P4-1 - Longitudinal barrier
P4-1-1 - Longitudinal barrier - Flood protection
P4-1-2 - Longitudinal barrier - Agriculture
P4-1-3 - Longitudinal barrier - Navigation
P4-2 - Transversal barrier
P4-2-1 - Transversal barrier - Hydropower
P4-2-2 - Transversal barrier - Flood protection
P4-2-3 - Transversal barrier - Public water supply
P4-2-4 - Transversal barrier - Agriculture
P4-2-5 - Transversal barrier - Recreation
P4-2-6 - Transversal barrier - Industry
P4-2-7 - Transversal barrier - Navigation
P4-3 - Hydrological alteration
P4-3-1 - Hydrological alteration - Agriculture
P4-3-2 - Hydrological alteration - Transport
P4-3-3 - Hydrological alteration - Hydropower
P4-3-4 - Hydrological alteration - Public water supply
P4-3-5 - Hydrological alteration - Aquaculture

«species-and-litter»
P5 - Species and litter
P5-1 - Introduced species and diseases
P5-2 - Exploitation or removal of animals or plants
P5-3 - Litter or fly tipping

«groundwater»
P6 - Groundwater
P6-1 - Groundwater - Recharges
P6-2 - Groundwater - Alteration of water level or volume
unknown
none
}

note for PressureType "the label is not part of the code"

classDef default fill:white,stroke:#000;
classDef forFixing fill:white,stroke:#f00;

    

Figure 86 Codelist - PressureType - 4th cycle

In the pressures codelist used in the 2nd and 3rd cycle of reporting, most options combined both a pressure and a driver (see Table 74). For example: abstraction + agriculture = ‘P3-1 - Abstraction - Agriculture’.

This structure was kept in the current codelist. Regardless of the hierarchical structure, the most detailed applicable option should be selected when reporting. If the specific driver is not in the codelist, the higher level code can be used. Note that not all the drivers defined in the 3rd cycle reporting guidance (see Table 75) are listed.

Table 74 The DPSIR framework as used in the pressures and impacts analysis.

Term	Definition
Driver	An anthropogenic activity that may have an environmental effect (e.g. agriculture, industry).
Pressure	The direct effect of the driver (for example, an effect that causes a changein flow or a change in the water chemistry).
State	The condition of the water body resulting from both natural and anthropogenic factors (i.e. physical, chemical and biological characteristics).
Impact	The environmental effect of the pressure (e.g. fish killed, ecosystem modified).
Response	The measures taken to improve the state of the water body (e.g. restricting abstraction, limiting point source discharges, developing best practice guidance for agriculture).

Source: [1]

Table 75 Driver definition.

Notation	Label	Definition
agriculture	Agriculture	Includes all farming activities, agriculture and livestock
climateChange	Climate change	«to-be-provided»
energy_hydropower	Energy – hydropower	«to-be-provided»
energy_nonHydropower	Energy – non-hydropower	Including cooling activities for thermal and nuclear plants.
aquaculture	Fisheries and aquaculture	Commercial fishing, aquaculture and fish farms (excluding recreational or sports angling, which is included in the category ‘tourism’ below).
floodProtection	Flood protection	«to-be-provided»
forestry	Forestry	««to-be-provided»
industry	Industry	All kinds of industry not included under other categories.
tourism	Tourism and recreation	Includes bathing, leisure boating and sailing, sports fishing/angling. It does not include the urban development linked to tourism (under category ‘urban’).
transport	Transport	Road and rail traffic; shipping, navigation and ports; aviation.
urban	Urban development	Includes urban development linked to household, non-manufacturing commercial activities, tourism.

Source: [2].

Todo

Pressures codelist - Definition of the drivers ENV or EEA Water to complete, and define if any should be added.

The Mapping table - PressureType codelist values - 3rd cycle to 4th cycle clarifies the correspondence between the codelist values in the 3rd cycle and in the 4th cycle.

Show mapping table

Table 76 Mapping table - PressureType codelist values - 3rd cycle to 4th cycle

3rd cycle	4th cycle	Remarks	Severity level
none	none		WARNING, ERROR, BLOCKER
P1-9 - Point - Other	P1 - Point sources	Use parent code	WARNING, ERROR
P1-1 - Point - Urban waste water	P1-1 - Point - Urban waste water
P1-2 - Point - Storm overflows	P1-2 - Point - Storm overflows
P1-3 - Point - IED plants	P1-3 - Point - IED plants
P1-4 - Point - Non IED plants	P1-4 - Point - Non IED plants
P1-5 - Point - Contaminated sites or abandoned industrial sites	P1-5 - Point - Contaminated sites or abandoned industrial sites
P1-6 - Point - Waste disposal sites	P1-6 - Point - Waste disposal sites
P1-7 - Point - Mine waters	P1-7 - Point - Mining
P1-8 - Point - Aquaculture	P1-8 - Point - Aquaculture
P2-10 - Diffuse - Other	P2 - Diffuse sources	Use parent code	WARNING, ERROR
P2-1 - Diffuse - Urban run-off	P2-1 - Diffuse - Urban run-off
P2-2 - Diffuse - Agricultural	P2-2 - Diffuse - Agriculture
P2-3 - Diffuse - Forestry	P2-3 - Diffuse - Forestry
P2-4 - Diffuse - Transport	P2-4 - Diffuse - Transport
P2-5 - Diffuse - Contaminated sites or abandoned industrial sites	P2-5 - Diffuse - Contaminated sites or abandoned industrial sites
P2-6 - Diffuse - Discharges not connected to sewerage network	P2-6 - Diffuse - Discharges not connected to sewerage network
P2-7 - Diffuse - Atmospheric deposition	P2-7 - Diffuse - Atmospheric deposition	Exceptional case
P2-8 - Diffuse - Mining	P2-8 - Diffuse - Mining
P2-9 - Diffuse - Aquaculture	P2-9 - Diffuse - Aquaculture
P3-7 - Abstraction - Other	P3 - Abstraction	Use parent code	WARNING, ERROR
P3-1 - Abstraction - Agriculture	P3-1 - Abstraction - Agriculture
P3-2 - Abstraction - Public water supply	P3-2 - Abstraction - Public water supply
P3-3 - Abstraction - Industry	P3-3 - Abstraction - Industry
P3-4 - Abstraction - Cooling water	P3-4 - Abstraction - Cooling water
P3-5 - Abstraction - Hydropower	P3-5 - Abstraction - Hydropower
P3-6 - Abstraction - Fish farms	P3-6 - Abstraction - Aquaculture	Formerly “Fish farms”
P4-4 - Hydromorphological alteration - Physical loss of whole or part of the water body	P4 - Hydromorphology	Use parent code	WARNING, ERROR
P4-5 - Hydromorphological alteration - Other	P4 - Hydromorphology	Use parent code	WARNING, ERROR
P4-1-4 - Physical alteration of channel/bed/riparian area/shore - Other	P4-1 - Longitudinal barrier	Use parent code	WARNING, ERROR
P4-1-5 - Physical alteration of channel/bed/riparian area/shore - Unknown or obsolete	P4-1 - Longitudinal barrier	Use parent code	WARNING, ERROR
P4-1-1 - Physical alteration of channel/bed/riparian area/shore - Flood protection	P4-1-1 - Longitudinal barrier - Flood protection
P4-1-2 - Physical alteration of channel/bed/riparian area/shore - Agriculture	P4-1-2 - Longitudinal barrier - Agriculture
P4-1-3 - Physical alteration of channel/bed/riparian area/shore - Navigation	P4-1-3 - Longitudinal barrier - Navigation
P4-2-8 - Dams, barriers and locks - Other	P4-2 - Transversal barrier	Use parent code	WARNING, ERROR
P4-2-9 - Dams, barriers and locks - Unknown or obsolete	P4-2 - Transversal barrier	Use parent code	WARNING, ERROR
P4-2-1 - Dams, barriers and locks - Hydropower	P4-2-1 - Transversal barrier - Hydropower
P4-2-2 - Dams, barriers and locks - Flood protection	P4-2-2 - Transversal barrier - Flood protection
P4-2-3 - Dams, barriers and locks - Drinking water	P4-2-3 -Transversal barrier - Public water supply	Formerly “Drinking water”
P4-2-4 - Dams, barriers and locks - Irrigation	P4-2-4 - Transversal barrier - Agriculture	Formerly “Irrigation”
P4-2-5 - Dams, barriers and locks - Recreation	P4-2-5 - Transversal barrier - Recreation
P4-2-6 - Dams, barriers and locks - Industry	P4-2-6 - Transversal barrier - Industry
P4-2-7 - Dams, barriers and locks - Navigation	P4-2-7 - Transversal barrier - Navigation
P4-3-6 - Hydrological alteration - Other	P4-3 - Hydrological alteration	Use parent code	WARNING, ERROR
P4-3-1 - Hydrological alteration - Agriculture	P4-3-1 - Hydrological alteration - Agriculture
P4-3-2 - Hydrological alteration - Transport	P4-3-2 - Hydrological alteration - Transport
P4-3-3 - Hydrological alteration - Hydropower	P4-3-3 - Hydrological alteration - Hydropower
P4-3-4 - Hydrological alteration - Public water supply	P4-3-4 - Hydrological alteration - Public water supply
P4-3-5 - Hydrological alteration - Aquaculture	P4-3-5 - Hydrological alteration - Aquaculture
«none»	P5 - Species and litter	New parent code	WARNING, ERROR, BLOCKER
P5-1 - Introduced species and diseases	P5-1 - Introduced species and diseases
P5-2 - Exploitation or removal of animals or plants	P5-2 - Exploitation or removal of animals or plants
P5-3 - Litter or fly tipping	P5-3 - Litter or fly tipping
«none»	P6 - Groundwater	New parent code	WARNING, ERROR, BLOCKER
P6-1 - Groundwater - Recharges	P6-1 - Groundwater - Recharges
P6-2 - Groundwater - Alteration of water level or volume	P6-2 - Groundwater - Alteration of water level or volume
P7 - Anthropogenic pressure - Other	unknown		WARNING
P8 - Anthropogenic pressure - Unknown	unknown		WARNING
P9 - Anthropogenic pressure - Historical pollution	unknown		WARNING

Annexes - Data analysis - 3rd cycle

This section contains some of the exploratory data analysis that supported the revision of the data model.

It is not relevant for the understanding of the proposed model, but may be informative for data providers involved in the testing phase of the 4th cycle dataflows.

About the EEA discodata service

The example queries can be executed interactively in the EEA discodata service.
Note that some queries may timeout.
If you need to analyse the entire European dataset, download it in CSV format.
For example, the link https://discodata.eea.europa.eu/download/WISE_WFD/v2r1/GWB_GroundWaterBody downloads the data of the [WISE_WFD].[v2r1].[GWB_GroundWaterBody] table.

The codelist of significant pressures used in the 3rd cycle includes three options generic values:

• ‘P7 - Anthropogenic pressure - Other’, which then requires a textual explanation to be provided;

• ‘P8 - Anthropogenic pressure - Unknown’

• ‘P9 - Anthropogenic pressure - Historical pollution’

The options P7, P8 and P9 are problematic:

• P7 can potentially be used as a “catch-all” option, that requires human analysis to understand what is reported in the text.

• P8 was flagged has problematic by Member States. It is reasonable to have an ‘unknown’ option, if the water body status is itself unknown, but perhaps not in other circumstances.

• P9 is not informative because it does not clarify what type of historical pollution is actually being reported.

The codelist includes several options that reduce the need for the P7 option:

• ‘P1-9 - Point - Other’

• ‘P2-10 - Diffuse - Other’

• ‘P3-7 - Abstraction - Other’

• ‘P4-1-4 - Physical alteration of channel/bed/riparian area/shore - Other’

• ‘P4-2-8 - Dams, barriers and locks - Other’

• ‘P4-3-6 - Hydrological alteration - Other’

• ‘P4-5 - Hydromorphological alteration - Other’

Number of groundwater bodies by pressure type

See Number of groundwater bodies by pressure type - 3rd cycle.

Show frequency table

Table 77 Number of groundwater bodies by pressure type - 3rd cycle

PressureTypeGroup	PressureType	number
P1 - Point sources	P1-1 - Point - Urban waste water	93
P1 - Point sources	P1-2 - Point - Storm overflows	6
P1 - Point sources	P1-3 - Point - IED plants	59
P1 - Point sources	P1-4 - Point - Non IED plants	65
P1 - Point sources	P1-5 - Point - Contaminated sites or abandoned industrial sites	614
P1 - Point sources	P1-6 - Point - Waste disposal sites	177
P1 - Point sources	P1-7 - Point - Mine waters	55
P1 - Point sources	P1-9 - Point - Other	128
P2 - Diffuse sources	P2-1 - Diffuse - Urban run-off	514
P2 - Diffuse sources	P2-2 - Diffuse - Agricultural	2151
P2 - Diffuse sources	P2-3 - Diffuse - Forestry	75
P2 - Diffuse sources	P2-4 - Diffuse - Transport	113
P2 - Diffuse sources	P2-5 - Diffuse - Contaminated sites or abandoned industrial sites	78
P2 - Diffuse sources	P2-6 - Diffuse - Discharges not connected to sewerage network	350
P2 - Diffuse sources	P2-8 - Diffuse - Mining	94
P2 - Diffuse sources	P2-10 - Diffuse - Other	189
P2-7 - Diffuse - Atmospheric deposition	P2-7 - Diffuse - Atmospheric deposition	68
P3 - Abstraction	P3-1 - Abstraction - Agriculture	834
P3 - Abstraction	P3-2 - Abstraction - Public water supply	889
P3 - Abstraction	P3-3 - Abstraction - Industry	395
P3 - Abstraction	P3-4 - Abstraction - Cooling water	41
P3 - Abstraction	P3-5 - Abstraction - Hydropower	5
P3 - Abstraction	P3-6 - Abstraction - Fish farms	88
P3 - Abstraction	P3-7 - Abstraction - Other	490
P4 - Hydromorphology	P4-3-1 - Hydrological alteration - Agriculture	34
P4 - Hydromorphology	P4-3-4 - Hydrological alteration - Public water supply	22
P4 - Hydromorphology	P4-3-6 - Hydrological alteration - Other	21
P4 - Hydromorphology	P4-5 - Hydromorphological alteration - Other	14
P5 - Introduced species and litter	P5-3 - Litter or fly tipping	24
P6 - Groundwater recharge or water level	P6-1 - Groundwater - Recharges	5
P6 - Groundwater recharge or water level	P6-2 - Groundwater - Alteration of water level or volume	161
P7 - Anthropogenic pressure - Other	P7 - Anthropogenic pressure - Other	159
P8 - Anthropogenic pressure - Unknown	P8 - Anthropogenic pressure - Unknown	261
P9 - Anthropogenic pressure - Historical pollution	P9 - Anthropogenic pressure - Historical pollution	439

Show code

Example 43 Number of groundwater bodies by pressure type - 3rd cycle

SELECT [gwSignificantPressureTypeGroup]
      ,[gwSignificantPressureType]
      ,count(distinct euGroundWaterBodyCode) numberOfWaterBodies
  FROM [WISE_WFD].[v2r1].[GWB_GroundWaterBody_gwSignificantPressureType] 
  WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
  AND [gwSignificantPressureType] != 'None'
  GROUP BY [gwSignificantPressureTypeGroup]
      ,[gwSignificantPressureType]
  ORDER BY [gwSignificantPressureTypeGroup]
      ,[gwSignificantPressureType]

Groundwater bodies with ‘P7 - Anthropogenic pressure - Other’

Can the option ‘P7 - Anthropogenic pressure - Other’ be removed from the list of pressures?

• Only 5 groundwater bodies list ‘P7 - Anthropogenic pressure - Other’ as the single [gwSignificantPressureType] pressure, and those 5 water bodies are in good chemical and quantitative status.

• Proposal: The option P7 can be safely removed. The [gwSignificantPressureOther] attribute can also be removed.
  Textual descriptions of other pressures can remain the the RBMP documents.

Show code

Example 44 Groundwater bodies where ‘P7 - Anthropogenic pressure - Other’ is the single [gwSignificantPressureType] pressure - 3rd cycle

SELECT [gwSignificantPressureOther], count(DISTINCT [euGroundWaterBodyCode]) numberOfWaterBodies
FROM (
    SELECT a.* , c.numberOfPressures
    FROM (
        SELECT [cYear]
              ,[countryCode]
              ,[euRBDCode]
              ,[euGroundWaterBodyCode]
              ,[gwSignificantPressureOther]
              ,[gwQuantitativeStatusValue]
              ,[gwChemicalStatusValue]
              ,[gwSignificantPressureType]
              ,[gwSignificantPressureTypeGroup]
        FROM [WISE_WFD].[v2r1].[GWB_GroundWaterBody_gwSignificantPressureType] 
        WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
        AND [gwSignificantPressureType] != 'None'
    ) a
    JOIN (
        SELECT euGroundWaterBodyCode 
        FROM (
            SELECT [cYear]
                  ,[countryCode]
                  ,[euRBDCode]
                  ,[euGroundWaterBodyCode]
                  ,[gwSignificantPressureOther]
                  ,[gwQuantitativeStatusValue]
                  ,[gwChemicalStatusValue]
                  ,[gwSignificantPressureType]
                  ,[gwSignificantPressureTypeGroup]
            FROM [WISE_WFD].[v2r1].[GWB_GroundWaterBody_gwSignificantPressureType] 
            WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
            AND [gwSignificantPressureType] != 'None'
        ) vRelevantData
        WHERE [gwSignificantPressureType] = 'P7 - Anthropogenic pressure - Other'
    ) b ON a.euGroundWaterBodyCode = b.euGroundWaterBodyCode
    JOIN (
        SELECT euGroundWaterBodyCode, count(*) AS numberOfPressures  
        FROM (
            SELECT [cYear]
                  ,[countryCode]
                  ,[euRBDCode]
                  ,[euGroundWaterBodyCode]
                  ,[gwSignificantPressureOther]
                  ,[gwQuantitativeStatusValue]
                  ,[gwChemicalStatusValue]
                  ,[gwSignificantPressureType]
                  ,[gwSignificantPressureTypeGroup]
            FROM [WISE_WFD].[v2r1].[GWB_GroundWaterBody_gwSignificantPressureType] 
            WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
            AND [gwSignificantPressureType] != 'None'
        ) vRelevantData
        GROUP BY euGroundWaterBodyCode
    ) c ON a.euGroundWaterBodyCode = c.euGroundWaterBodyCode
    WHERE c.numberOfPressures = 1
) vOnlyOtherPressure
GROUP BY [gwSignificantPressureOther] WITH ROLLUP
ORDER BY numberOfWaterBodies DESC

Groundwater bodies with ‘P8 - Anthropogenic pressure - Unknown’

If the option ‘P7 - Anthropogenic pressure - Other’ is removed from the list of pressures, can the option ‘P8 - Anthropogenic pressure - Unknown’ also be removed?

• There are 50 groundwater bodies listing ‘P8 - Anthropogenic pressure - Unknown’ as the single [gwSignificantPressureType] pressure causing failure.

• Proposal:
  Remove the option ‘P8 - Anthropogenic pressure - Unknown’ from the list of options.
  A generic ‘unknown’ option will be available. It is mandatory to assess the pressures for water bodies not achieving good status, but Member States may still choose the option ‘unknown’ and effectively report that the pressures were not assessed.

Show code

Example 45 Groundwater bodies where ‘P8 - Anthropogenic pressure - Unknown’ is the single remaining [gwSignificantPressureType] pressure - 3rd cycle

SELECT *
FROM (
    SELECT a.* , c.numberOfPressures
    FROM (
        SELECT [cYear]
              ,[countryCode]
              ,[euRBDCode]
              ,[euGroundWaterBodyCode]
              ,[gwSignificantPressureOther]
              ,[gwQuantitativeStatusValue]
              ,[gwChemicalStatusValue]
              ,[gwSignificantPressureType]
              ,[gwSignificantPressureTypeGroup]
        FROM [WISE_WFD].[v2r1].[GWB_GroundWaterBody_gwSignificantPressureType] 
        WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
        AND [gwSignificantPressureType] NOT IN ('None','P7 - Anthropogenic pressure - Other')
    ) a
    JOIN (
        SELECT euGroundWaterBodyCode 
        FROM (
            SELECT [cYear]
                  ,[countryCode]
                  ,[euRBDCode]
                  ,[euGroundWaterBodyCode]
                  ,[gwSignificantPressureOther]
                  ,[gwQuantitativeStatusValue]
                  ,[gwChemicalStatusValue]
                  ,[gwSignificantPressureType]
                  ,[gwSignificantPressureTypeGroup]
            FROM [WISE_WFD].[v2r1].[GWB_GroundWaterBody_gwSignificantPressureType] 
            WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
            AND [gwSignificantPressureType] NOT IN ('None','P7 - Anthropogenic pressure - Other')
        ) vRelevantData
        WHERE [gwSignificantPressureType] = 'P8 - Anthropogenic pressure - Unknown'
    ) b ON a.euGroundWaterBodyCode = b.euGroundWaterBodyCode
    JOIN (
        SELECT euGroundWaterBodyCode, count(*) AS numberOfPressures  
        FROM (
            SELECT [cYear]
                  ,[countryCode]
                  ,[euRBDCode]
                  ,[euGroundWaterBodyCode]
                  ,[gwSignificantPressureOther]
                  ,[gwQuantitativeStatusValue]
                  ,[gwChemicalStatusValue]
                  ,[gwSignificantPressureType]
                  ,[gwSignificantPressureTypeGroup]
            FROM [WISE_WFD].[v2r1].[GWB_GroundWaterBody_gwSignificantPressureType] 
            WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
            AND [gwSignificantPressureType] NOT IN ('None','P7 - Anthropogenic pressure - Other')
        ) vRelevantData
        GROUP BY euGroundWaterBodyCode
    ) c ON a.euGroundWaterBodyCode = c.euGroundWaterBodyCode
    WHERE c.numberOfPressures = 1
) vOnlyUnknownPressure
WHERE NOT (gwChemicalStatusValue = '2' AND gwQuantitativeStatusValue = '2')

Groundwater bodies with ‘P9 - Anthropogenic pressure - Historical pollution’

If the option ‘P7 - Anthropogenic pressure - Other’ and the option ‘P8 - Anthropogenic pressure - Unknown’ are removed from the list of pressures, can the option ‘P9 - Anthropogenic pressure - Historical pollution’ also be removed?

• There are 102 GWB (out of 5605 not achieving good status) that list ‘P9 - Anthropogenic pressure - Historical pollution’ as the single [gwSignificantPressureType] pressure causing failure. It is important to note that 75 of those 102 GWB are in Denmark.

• Proposal:
  Remove the option ‘P9 - Anthropogenic pressure - Historical pollution’ from the list of [gwSignificantPressureType] options, because it is seldom used and not very informative.

• Inform Member States, specially those that used the P9 option in the 3rd cycle electronic reporting.

Show code

Example 46 Groundwater bodies where ‘P9 - Anthropogenic pressure - Historical pollution’ is the single remaining [gwSignificantPressureType] pressure - 3rd cycle

SELECT countryCode, count(*) n
FROM (
    SELECT a.* , c.numberOfPressures
    FROM (
        SELECT [cYear]
              ,[countryCode]
              ,[euRBDCode]
              ,[euGroundWaterBodyCode]
              ,[gwSignificantPressureOther]
              ,[gwQuantitativeStatusValue]
              ,[gwChemicalStatusValue]
              ,[gwSignificantPressureType]
              ,[gwSignificantPressureTypeGroup]
        FROM [WISE_WFD].[latest].[GWB_GroundWaterBody_gwSignificantPressureType] 
        WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
        AND [gwSignificantPressureType] 
          NOT IN ('None',
                'P7 - Anthropogenic pressure - Other',
                'P8 - Anthropogenic pressure - Unknown')
    ) a
    JOIN (
        SELECT euGroundWaterBodyCode 
        FROM (
            SELECT [cYear]
                  ,[countryCode]
                  ,[euRBDCode]
                  ,[euGroundWaterBodyCode]
                  ,[gwSignificantPressureOther]
                  ,[gwQuantitativeStatusValue]
                  ,[gwChemicalStatusValue]
                  ,[gwSignificantPressureType]
                  ,[gwSignificantPressureTypeGroup]
            FROM [WISE_WFD].[latest].[GWB_GroundWaterBody_gwSignificantPressureType] 
            WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
            AND [gwSignificantPressureType] 
              NOT IN ('None',
                    'P7 - Anthropogenic pressure - Other',
                    'P8 - Anthropogenic pressure - Unknown')
        ) vRelevantData
        WHERE [gwSignificantPressureType] = 'P9 - Anthropogenic pressure - Historical pollution'
    ) b ON a.euGroundWaterBodyCode = b.euGroundWaterBodyCode
    JOIN (
        SELECT euGroundWaterBodyCode, count(*) AS numberOfPressures  
        FROM (
            SELECT [cYear]
                  ,[countryCode]
                  ,[euRBDCode]
                  ,[euGroundWaterBodyCode]
                  ,[gwSignificantPressureOther]
                  ,[gwQuantitativeStatusValue]
                  ,[gwChemicalStatusValue]
                  ,[gwSignificantPressureType]
                  ,[gwSignificantPressureTypeGroup]
            FROM [WISE_WFD].[latest].[GWB_GroundWaterBody_gwSignificantPressureType] 
            WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
            AND [gwSignificantPressureType] 
              NOT IN ('None',
                    'P7 - Anthropogenic pressure - Other',
                    'P8 - Anthropogenic pressure - Unknown')
        ) vRelevantData
        GROUP BY euGroundWaterBodyCode
    ) c ON a.euGroundWaterBodyCode = c.euGroundWaterBodyCode
    WHERE c.numberOfPressures = 1
) vOnlyHistoricalPressure
WHERE NOT (gwChemicalStatusValue = '2' AND gwQuantitativeStatusValue = '2')
GROUP BY countryCode
ORDER BY countryCode

Number of surface bodies by pressure type

See Number of groundwater bodies by pressure type - 3rd cycle.

Show frequency table

Table 78 Number of groundwater bodies by pressure type - 3rd cycle

PressureTypeGroup	PressureType	number
P1 - Point sources	P1-1 - Point - Urban waste water	13366
P1 - Point sources	P1-2 - Point - Storm overflows	4844
P1 - Point sources	P1-3 - Point - IED plants	3727
P1 - Point sources	P1-4 - Point - Non IED plants	3183
P1 - Point sources	P1-5 - Point - Contaminated sites or abandoned industrial sites	1947
P1 - Point sources	P1-6 - Point - Waste disposal sites	1049
P1 - Point sources	P1-7 - Point - Mine waters	545
P1 - Point sources	P1-8 - Point - Aquaculture	533
P1 - Point sources	P1-9 - Point - Other	884
P2 - Diffuse sources	P2-1 - Diffuse - Urban run-off	8631
P2 - Diffuse sources	P2-2 - Diffuse - Agricultural	32977
P2 - Diffuse sources	P2-3 - Diffuse - Forestry	1746
P2 - Diffuse sources	P2-4 - Diffuse - Transport	3854
P2 - Diffuse sources	P2-5 - Diffuse - Contaminated sites or abandoned industrial sites	904
P2 - Diffuse sources	P2-6 - Diffuse - Discharges not connected to sewerage network	11112
P2-7 - Diffuse - Atmospheric deposition	P2-7 - Diffuse - Atmospheric deposition	51856
P2 - Diffuse sources	P2-8 - Diffuse - Mining	950
P2 - Diffuse sources	P2-9 - Diffuse - Aquaculture	286
P2 - Diffuse sources	P2-10 - Diffuse - Other	3757
P3 - Abstraction	P3-1 - Abstraction - Agriculture	3641
P3 - Abstraction	P3-2 - Abstraction - Public water supply	1938
P3 - Abstraction	P3-3 - Abstraction - Industry	1276
P3 - Abstraction	P3-4 - Abstraction - Cooling water	53
P3 - Abstraction	P3-5 - Abstraction - Hydropower	2080
P3 - Abstraction	P3-6 - Abstraction - Fish farms	191
P3 - Abstraction	P3-7 - Abstraction - Other	2103
P4 - Hydromorphology	P4-1-1 - Physical alteration of channel/bed/riparian area/shore - Flood protection	6616
P4 - Hydromorphology	P4-1-2 - Physical alteration of channel/bed/riparian area/shore - Agriculture	11092
P4 - Hydromorphology	P4-1-3 - Physical alteration of channel/bed/riparian area/shore - Navigation	849
P4 - Hydromorphology	P4-1-4 - Physical alteration of channel/bed/riparian area/shore - Other	10783
P4 - Hydromorphology	P4-1-5 - Physical alteration of channel/bed/riparian area/shore - Unknown or obsolete	11279
P4 - Hydromorphology	P4-2-1 - Dams, barriers and locks - Hydropower	8499
P4 - Hydromorphology	P4-2-2 - Dams, barriers and locks - Flood protection	4729
P4 - Hydromorphology	P4-2-3 - Dams, barriers and locks - Drinking water	422
P4 - Hydromorphology	P4-2-4 - Dams, barriers and locks - Irrigation	2151
P4 - Hydromorphology	P4-2-5 - Dams, barriers and locks - Recreation	555
P4 - Hydromorphology	P4-2-6 - Dams, barriers and locks - Industry	481
P4 - Hydromorphology	P4-2-7 - Dams, barriers and locks - Navigation	493
P4 - Hydromorphology	P4-2-8 - Dams, barriers and locks - Other	11986
P4 - Hydromorphology	P4-2-9 - Dams, barriers and locks - Unknown or obsolete	11679
P4 - Hydromorphology	P4-3-1 - Hydrological alteration - Agriculture	2480
P4 - Hydromorphology	P4-3-2 - Hydrological alteration - Transport	236
P4 - Hydromorphology	P4-3-3 - Hydrological alteration - Hydropower	5687
P4 - Hydromorphology	P4-3-4 - Hydrological alteration - Public water supply	332
P4 - Hydromorphology	P4-3-5 - Hydrological alteration - Aquaculture	366
P4 - Hydromorphology	P4-3-6 - Hydrological alteration - Other	8564
P4 - Hydromorphology	P4-4 - Hydromorphological alteration - Physical loss of whole or part of the water body	1054
P4 - Hydromorphology	P4-5 - Hydromorphological alteration - Other	4360
P5 - Introduced species and litter	P5-1 - Introduced species and diseases	3281
P5 - Introduced species and litter	P5-2 - Exploitation or removal of animals or plants	930
P5 - Introduced species and litter	P5-3 - Litter or fly tipping	655
P6 - Groundwater recharge or water level	P6-1 - Groundwater - Recharges	40
P7 - Anthropogenic pressure - Other	P7 - Anthropogenic pressure - Other	2185
P8 - Anthropogenic pressure - Unknown	P8 - Anthropogenic pressure - Unknown	37090
P9 - Anthropogenic pressure - Historical pollution	P9 - Anthropogenic pressure - Historical pollution	2289

Show code

Example 47 Number of surface bodies by pressure type - 3rd cycle

SELECT [swSignificantPressureTypeGroup]
      ,[swSignificantPressureType]
      ,count(distinct [euSurfaceWaterBodyCode] ) numberOfWaterBodies
  FROM [WISE_WFD].[latest].[SWB_SurfaceWaterBody_swSignificantPressureType] 
  WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
  AND [swSignificantPressureType] != 'None'
  GROUP BY [swSignificantPressureTypeGroup]
      ,[swSignificantPressureType]
  ORDER BY [swSignificantPressureTypeGroup]
      ,[swSignificantPressureType]

Surface water bodies with ‘P7 - Anthropogenic pressure - Other’

Can the option ‘P7 - Anthropogenic pressure - Other’ be removed from the list of pressures?

• Only 237 surface water bodies list ‘P7 - Anthropogenic pressure - Other’ as the single [swSignificantPressureType] pressure, of which 235 fail to achieve good status. A total of 137 of these water bodies report “gold mining activity (legal and illegal)” as the ‘other’ pressure - but there is pressure code for this situation (i.e. there is not need for a textual description).

• Proposal: Remove the option ‘P7 - Anthropogenic pressure - Other’ from the list of [swSignificantPressureType] options.
  Remove the [swSignificantPressureOther] column.
  Inform Member States, specially those that used the P7 option in the 3rd cycle electronic reporting.

Show code

Example 48 Surface water bodies where ‘P7 - Anthropogenic pressure - Other’ is the single [gwSignificantPressureType] pressure - 3rd cycle

SELECT swSignificantPressureOther, count(DISTINCT [euSurfaceWaterBodyCode]) numberOfWaterBodies
FROM (
    SELECT a.* , c.numberOfPressures, d.swSignificantPressureOther
    FROM (
        SELECT [cYear]
              ,[countryCode]
              ,[euRBDCode]
              ,[euSurfaceWaterBodyCode]
              ,[swEcologicalStatusOrPotentialValue]
              ,[swChemicalStatusValue]
              ,[swSignificantPressureType]
              ,[swSignificantPressureTypeGroup]
        FROM [WISE_WFD].[latest].[SWB_SurfaceWaterBody_swSignificantPressureType] 
        WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
        AND [swSignificantPressureType] != 'None'
    ) a
    JOIN (
        SELECT [euSurfaceWaterBodyCode] 
        FROM (
            SELECT [cYear]
                  ,[countryCode]
                  ,[euRBDCode]
                  ,[euSurfaceWaterBodyCode]
                  ,[swEcologicalStatusOrPotentialValue]
                  ,[swChemicalStatusValue]
                  ,[swSignificantPressureType]
                  ,[swSignificantPressureTypeGroup]
            FROM [WISE_WFD].[latest].[SWB_SurfaceWaterBody_swSignificantPressureType] 
            WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
            AND [swSignificantPressureType] != 'None'
        ) vRelevantData
        WHERE [swSignificantPressureType] = 'P7 - Anthropogenic pressure - Other'
    ) b ON a.[euSurfaceWaterBodyCode] = b.[euSurfaceWaterBodyCode]
    JOIN (
        SELECT [euSurfaceWaterBodyCode], count(*) AS numberOfPressures  
        FROM (
            SELECT [cYear]
                  ,[countryCode]
                  ,[euRBDCode]
                  ,[euSurfaceWaterBodyCode]
                  ,[swEcologicalStatusOrPotentialValue]
                  ,[swChemicalStatusValue]
                  ,[swSignificantPressureType]
                  ,[swSignificantPressureTypeGroup]
            FROM [WISE_WFD].[latest].[SWB_SurfaceWaterBody_swSignificantPressureType] 
            WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
            AND [swSignificantPressureType] != 'None'
        ) vRelevantData
        GROUP BY [euSurfaceWaterBodyCode]
    ) c ON a.[euSurfaceWaterBodyCode] = c.[euSurfaceWaterBodyCode]
    JOIN [WISE_WFD].[latest].[SWB_SurfaceWaterBody_swSignificantPressureOther] d
    ON a.[euSurfaceWaterBodyCode] = d.[euSurfaceWaterBodyCode]
        AND d.[cYear] = 2022
    WHERE c.numberOfPressures = 1
) vOnlyOtherPressure
WHERE NOT (swEcologicalStatusOrPotentialValue IN ('1','2') AND swChemicalStatusValue IN ('2'))
GROUP BY swSignificantPressureOther WITH ROLLUP
ORDER BY numberOfWaterBodies DESC

Surface water bodies with ‘P8 - Anthropogenic pressure - Unknown’

If the option ‘P7 - Anthropogenic pressure - Other’ is removed from the list of pressures, can the option ‘P8 - Anthropogenic pressure - Unknown’ also be removed?

• There are 7 surface water bodies listing ‘P8 - Anthropogenic pressure - Unknown’ as the single [swSignificantPressureType] pressure causing failure.

• Proposal:
  Remove the option ‘P8 - Anthropogenic pressure - Unknown’ from the list of [swSignificantPressureType] options.
  Inform Member States, specially those that used the P8 option in the 3rd cycle electronic reporting.
  A generic ‘unknown’ option will be available. It is mandatory to assess the pressures for water bodies not achieving good status, but Member States may still choose the option ‘unknown’ and effectively report that the pressures were not assessed.

Show code

Example 49 Surface water bodies where ‘P8 - Anthropogenic pressure - Unknown’ is the single remaining [gwSignificantPressureType] pressure - 3rd cycle

SELECT *
FROM (
    SELECT a.* , c.numberOfPressures, d.swSignificantPressureOther
    FROM (
        SELECT [cYear]
              ,[countryCode]
              ,[euRBDCode]
              ,[euSurfaceWaterBodyCode]
              ,[swEcologicalStatusOrPotentialValue]
              ,[swChemicalStatusValue]
              ,[swSignificantPressureType]
              ,[swSignificantPressureTypeGroup]
        FROM [WISE_WFD].[latest].[SWB_SurfaceWaterBody_swSignificantPressureType] 
        WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
        AND [swSignificantPressureType] NOT IN ('None','P7 - Anthropogenic pressure - Other')
    ) a
    JOIN (
        SELECT [euSurfaceWaterBodyCode] 
        FROM (
            SELECT [cYear]
                  ,[countryCode]
                  ,[euRBDCode]
                  ,[euSurfaceWaterBodyCode]
                  ,[swEcologicalStatusOrPotentialValue]
                  ,[swChemicalStatusValue]
                  ,[swSignificantPressureType]
                  ,[swSignificantPressureTypeGroup]
            FROM [WISE_WFD].[latest].[SWB_SurfaceWaterBody_swSignificantPressureType] 
            WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
            AND [swSignificantPressureType] NOT IN ('None','P7 - Anthropogenic pressure - Other')
        ) vRelevantData
        WHERE [swSignificantPressureType] = 'P8 - Anthropogenic pressure - Unknown'
    ) b ON a.[euSurfaceWaterBodyCode] = b.[euSurfaceWaterBodyCode]
    JOIN (
        SELECT [euSurfaceWaterBodyCode], count(*) AS numberOfPressures  
        FROM (
            SELECT [cYear]
                  ,[countryCode]
                  ,[euRBDCode]
                  ,[euSurfaceWaterBodyCode]
                  ,[swEcologicalStatusOrPotentialValue]
                  ,[swChemicalStatusValue]
                  ,[swSignificantPressureType]
                  ,[swSignificantPressureTypeGroup]
            FROM [WISE_WFD].[latest].[SWB_SurfaceWaterBody_swSignificantPressureType] 
            WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
            AND [swSignificantPressureType] NOT IN ('None','P7 - Anthropogenic pressure - Other')
        ) vRelevantData
        GROUP BY [euSurfaceWaterBodyCode]
    ) c ON a.[euSurfaceWaterBodyCode] = c.[euSurfaceWaterBodyCode]
    JOIN [WISE_WFD].[latest].[SWB_SurfaceWaterBody_swSignificantPressureOther] d
    ON a.[euSurfaceWaterBodyCode] = d.[euSurfaceWaterBodyCode]
        AND d.[cYear] = 2022
    WHERE c.numberOfPressures = 1
) vOnlyUnknownPressure
WHERE NOT (swEcologicalStatusOrPotentialValue IN ('1','2') AND swChemicalStatusValue IN ('2'))

Surface water bodies with ‘P9 - Anthropogenic pressure - Historical pollution’

If the option ‘P7 - Anthropogenic pressure - Other’ and the option ‘P8 - Anthropogenic pressure - Unknown’ are removed from the list of pressures, can the option ‘P9 - Anthropogenic pressure - Historical pollution’ also be removed?

• There are zero water bodies listing this pressure as the single pressure.

• Proposal:
  Remove the option ‘P9 - Anthropogenic pressure - Historical pollution’ from the list of [swSignificantPressureType] options, because it is seldom used and not very informative.

Show code

Example 50 Surface water bodies where ‘P9 - Anthropogenic pressure - Historical pollution’ is the single remaining [gwSignificantPressureType] pressure - 3rd cycle

SELECT *
FROM (
    SELECT a.* , c.numberOfPressures, d.swSignificantPressureOther
    FROM (
        SELECT [cYear]
              ,[countryCode]
              ,[euRBDCode]
              ,[euSurfaceWaterBodyCode]
              ,[swEcologicalStatusOrPotentialValue]
              ,[swChemicalStatusValue]
              ,[swSignificantPressureType]
              ,[swSignificantPressureTypeGroup]
        FROM [WISE_WFD].[latest].[SWB_SurfaceWaterBody_swSignificantPressureType] 
        WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
        AND [swSignificantPressureType] 
          NOT IN ('None',
                  'P7 - Anthropogenic pressure - Other',
                  'P8 - Anthropogenic pressure - Unknown')
    ) a
    JOIN (
        SELECT [euSurfaceWaterBodyCode] 
        FROM (
            SELECT [cYear]
                  ,[countryCode]
                  ,[euRBDCode]
                  ,[euSurfaceWaterBodyCode]
                  ,[swEcologicalStatusOrPotentialValue]
                  ,[swChemicalStatusValue]
                  ,[swSignificantPressureType]
                  ,[swSignificantPressureTypeGroup]
            FROM [WISE_WFD].[latest].[SWB_SurfaceWaterBody_swSignificantPressureType] 
            WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
            AND [swSignificantPressureType] 
              NOT IN ('None',
                      'P7 - Anthropogenic pressure - Other',
                      'P8 - Anthropogenic pressure - Unknown')
        ) vRelevantData
        WHERE [swSignificantPressureType] = 'P9 - Anthropogenic pressure - Historical pollution'
    ) b ON a.[euSurfaceWaterBodyCode] = b.[euSurfaceWaterBodyCode]
    JOIN (
        SELECT [euSurfaceWaterBodyCode], count(*) AS numberOfPressures  
        FROM (
            SELECT [cYear]
                  ,[countryCode]
                  ,[euRBDCode]
                  ,[euSurfaceWaterBodyCode]
                  ,[swEcologicalStatusOrPotentialValue]
                  ,[swChemicalStatusValue]
                  ,[swSignificantPressureType]
                  ,[swSignificantPressureTypeGroup]
            FROM [WISE_WFD].[latest].[SWB_SurfaceWaterBody_swSignificantPressureType] 
            WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
            AND [swSignificantPressureType] 
              NOT IN ('None',
                      'P7 - Anthropogenic pressure - Other',
                      'P8 - Anthropogenic pressure - Unknown')
        ) vRelevantData
        GROUP BY [euSurfaceWaterBodyCode]
    ) c ON a.[euSurfaceWaterBodyCode] = c.[euSurfaceWaterBodyCode]
    JOIN [WISE_WFD].[latest].[SWB_SurfaceWaterBody_swSignificantPressureOther] d
    ON a.[euSurfaceWaterBodyCode] = d.[euSurfaceWaterBodyCode]
        AND d.[cYear] = 2022
    WHERE c.numberOfPressures = 1
) vOnlyHistoricalPressure
WHERE NOT (swEcologicalStatusOrPotentialValue IN ('1','2') AND swChemicalStatusValue IN ('2'))

Surface water bodies with ‘P8 - Anthropogenic pressure - Unknown’ and unknown chemical status

This specific situation was detected during the Assessment of the 3rd RBMPs [3]:

A Member State flagged that where a water body is reported to be in unknown chemical status, a pressure must also be reported on that water body. This resulted in the Member State reporting 60% of surface water bodies to be affected by unknown anthropogenic pressures. The Member State representative said that in reality, the percentage of water bodies with an unknown pressure was approximately 10%. I.e. this resulted in a large difference between the data reported electronically, and the actual situation in the Member State.

Todo

Significant pressures - Provide link to report

ASSESSMENT OF THE THIRD RIVER BASIN MANAGEMENT PLANS (RBMPs) - APPENDIX - LESSONS LEARNT - ELECTRONIC REPORTING

This was indeed an issue. The situation described is likely cause by the fact that there is no option ‘Unknown’ in the 3rd cycle codelist. In fact, the reporting guidance says that ‘P8 - Anthropogenic pressure - Unknown’ is only relevant where status is lower than good and pressure is unknown.

For the future, the codelist should include the option ‘unknown’, to be used in this scope. (Also the option can be used if status is unknown.)

Show code

Example 51 Surface water bodies with unknown chemical status, non-failing ecological status and ‘P8 - Anthropogenic pressure - Unknown’ - 3rd cycle

-- Query 1: unknown chemical status, non-failing ecological status
SELECT a.countryCode,
       a.numberOfWaterBodies,
       b.numberOfWaterBodies_potentiallyWithIssue,
       c.numberOfWaterBodies_actuallyWithIssue,
       round(cast(b.numberOfWaterBodies_potentiallyWithIssue as float) * 100 / a.numberOfWaterBodies, 1) AS percent_potentiallyWithIssue,
       round(cast(c.numberOfWaterBodies_actuallyWithIssue as float) * 100 / a.numberOfWaterBodies, 1) AS percent_actuallyWithIssue
FROM (
    SELECT countryCode, count(distinct euSurfaceWaterBodyCode) AS numberOfWaterBodies
    FROM WISE_WFD.v2r1.SWB_SurfaceWaterBody
    WHERE cYear = 2022
      AND surfaceWaterBodyCategory != 'TeW'
    GROUP BY countryCode
) a
JOIN (
    SELECT countryCode, count(distinct euSurfaceWaterBodyCode) AS numberOfWaterBodies_potentiallyWithIssue
    FROM WISE_WFD.v2r1.SWB_SurfaceWaterBody
    WHERE cYear = 2022
      AND surfaceWaterBodyCategory != 'TeW'
      AND swChemicalStatusValue = 'Unknown'
      AND swEcologicalStatusOrPotentialValue NOT IN ('3','4','5')
    GROUP BY countryCode
) b
  ON a.countryCode = b.countryCode
JOIN (
    SELECT countryCode,
           swSignificantPressureType,
           count(distinct euSurfaceWaterBodyCode) AS numberOfWaterBodies_actuallyWithIssue
    FROM (
        SELECT countryCode,
               euSurfaceWaterBodyCode,
               max(swSignificantPressureType) AS swSignificantPressureType
        FROM WISE_WFD.v2r1.SWB_SurfaceWaterBody_swSignificantPressureType
        WHERE cYear = 2022
          AND surfaceWaterBodyCategory != 'TeW'
          AND swChemicalStatusValue = 'Unknown'
          AND swEcologicalStatusOrPotentialValue NOT IN ('3','4','5')
        GROUP BY countryCode, euSurfaceWaterBodyCode
        HAVING count(DISTINCT swSignificantPressureType) = 1
           AND max(swSignificantPressureType) = 'P8 - Anthropogenic pressure - Unknown'
    ) x
    GROUP BY countryCode, swSignificantPressureType
) c
  ON a.countryCode = c.countryCode
ORDER BY percent_actuallyWithIssue DESC;

Groundwater water bodies with ‘P8 - Anthropogenic pressure - Unknown’ and unknown chemical status

The issue above didn’t appear to affect groundwater bodies. But nevertheless it will be solved for both.

Show code

Example 52 Groundwater bodies with unknown chemical status, non-failing quantitative status and ‘P8 - Anthropogenic pressure - Unknown’ - 3rd cycle

-- Query 1: unknown chemical status, non-failing quantitative status, and only P8 significant pressure type
SELECT a.countryCode,
       a.numberOfWaterBodies,
       b.numberOfWaterBodies_potentiallyWithIssue,
       c.numberOfWaterBodies_actuallyWithIssue,
       round(cast(b.numberOfWaterBodies_potentiallyWithIssue as float) * 100 / a.numberOfWaterBodies, 1) AS percent_potentiallyWithIssue,
       round(cast(c.numberOfWaterBodies_actuallyWithIssue as float) * 100 / a.numberOfWaterBodies, 1) AS percent_actuallyWithIssue
FROM (
    SELECT countryCode, count(distinct euGroundWaterBodyCode) AS numberOfWaterBodies
    FROM WISE_WFD.v2r1.GWB_GroundWaterBody
    WHERE cYear = 2022
      AND hasDescriptiveData = 1
    GROUP BY countryCode
) a
JOIN (
    SELECT countryCode, count(distinct euGroundWaterBodyCode) AS numberOfWaterBodies_potentiallyWithIssue
    FROM WISE_WFD.v2r1.GWB_GroundWaterBody
    WHERE cYear = 2022
      AND hasDescriptiveData = 1
      AND gwChemicalStatusValue = 'Unknown'
      AND gwQuantitativeStatusValue NOT IN ('3')
    GROUP BY countryCode
) b
  ON a.countryCode = b.countryCode
JOIN (
    SELECT countryCode,
           gwSignificantPressureType,
           count(distinct euGroundWaterBodyCode) AS numberOfWaterBodies_actuallyWithIssue
    FROM (
        SELECT countryCode,
               euGroundWaterBodyCode,
               max(gwSignificantPressureType) AS gwSignificantPressureType
        FROM WISE_WFD.v2r1.GWB_GroundWaterBody_gwSignificantPressureType
        WHERE cYear = 2022
          AND hasDescriptiveData = 1
          AND gwChemicalStatusValue = 'Unknown'
          AND gwQuantitativeStatusValue NOT IN ('3')
        GROUP BY countryCode, euGroundWaterBodyCode
        HAVING count(DISTINCT gwSignificantPressureType) = 1
           AND max(gwSignificantPressureType) = 'P8 - Anthropogenic pressure - Unknown'
    ) x
    GROUP BY countryCode, gwSignificantPressureType
) c
  ON a.countryCode = c.countryCode
ORDER BY percent_actuallyWithIssue DESC;

Groundwater bodies where the single pressure causing failure is ‘P6-1 - Groundwater - Recharges’

A total of 124 groundwater bodies have ‘P6-1 - Groundwater - Recharges’ as a significant pressure. But only 12 groundwater bodies have ‘P6-1 - Groundwater - Recharges’ as the single significant pressure.

Show code

Example 53 Groundwater bodies where the single pressure causing failure is ‘P6-1 - Groundwater - Recharges’ - 3rd cycle

SELECT euGroundWaterBodyCode, gwChemicalStatusValue, gwQuantitativeStatusValue
FROM [WISE_WFD].[v2r1].[GWB_GroundWaterBody_gwSignificantPressureType]
WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
AND euGroundWaterBodyCode IN
(
-- Of which only 16 have only P6 significant pressure type and failing chemical or quantitative status, and no other significant pressure types.
SELECT euGroundWaterBodyCode
FROM [WISE_WFD].[v2r1].[GWB_GroundWaterBody_gwSignificantPressureType]
WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
AND [euGroundWaterBodyCode] in 
  ( -- 124 Ground Water Bodies with P6 significant pressure type and failing chemical or quantitative status
  SELECT euGroundWaterBodyCode
  FROM [WISE_WFD].[v2r1].[GWB_GroundWaterBody_gwSignificantPressureType]
  WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
  AND [gwSignificantPressureType] LIKE 'P6%'
  AND (gwChemicalStatusValue = '3' or gwQuantitativeStatusValue = '3') 
  ) 
  GROUP BY euGroundWaterBodyCode
  HAVING COUNT(DISTINCT gwSignificantPressureType) = 1
  ) 

Surface bodies where the single pressure causing failure is ‘P6-1 - Groundwater - Recharges’

A total of 37 surface water bodies have ‘P6-1 - Groundwater - Recharges’ as a significant pressure. But zero surface water bodies have ‘P6-1 - Groundwater - Recharges’ as the single significant pressure.

Show code

Example 54 Surface bodies where the single pressure causing failure is ‘P6-1 - Groundwater - Recharges’ - 3rd cycle

SELECT euSurfaceWaterBodyCode, swChemicalStatusValue, swEcologicalStatusOrPotentialValue
FROM [WISE_WFD].[v2r1].[SWB_SurfaceWaterBody_swSignificantPressureType]
WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
AND euSurfaceWaterBodyCode IN
(
-- Of which ZERO have only P6 significant pressure type and failing chemical or quantitative status, and no other significant pressure types.
SELECT euSurfaceWaterBodyCode
FROM [WISE_WFD].[v2r1].[SWB_SurfaceWaterBody_swSignificantPressureType]
WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
AND euSurfaceWaterBodyCode in 
  ( -- 37 Surface water Bodies with P6 significant pressure type and failing chemical or ecological status
  SELECT euSurfaceWaterBodyCode
  FROM [WISE_WFD].[v2r1].[SWB_SurfaceWaterBody_swSignificantPressureType]
  WHERE [cYear] = 2022 AND [hasDescriptiveData] = 1
  AND [swSignificantPressureType] LIKE 'P6%'
  AND (swChemicalStatusValue = '3' or swEcologicalStatusOrPotentialValue in ('3','4','5')) 
  ) 
  GROUP BY euSurfaceWaterBodyCode
  HAVING COUNT(DISTINCT swSignificantPressureType) = 1
  ) 

---

References

---

[1]

European Commission and Directorate-General for Environment. CIS Guidance Document No. 3: Analysis of Pressures and Impacts. Common Implementation Strategy for the Water Framework Directive (2000/60/EC), European Commission, 2003. URL: https://circabc.europa.eu/rest/download/7e01a7e0-9ccb-4f3d-8cec-aeef1335c2f7.

[2]

European Commission and Directorate-General for Environment. CIS Guidance Document: WFD Reporting Guidance 2022. Common Implementation Strategy for the Water Framework Directive (2000/60/EC), European Commission, 2022. URL: https://circabc.europa.eu/rest/download/37913699-1297-4d93-8cdf-9975277bb685.

[3]

International Office for Water (OiEau), Ramboll, and EMVIS. Appraisal of electronic reporting under the water framework directive. Technical Report, Directorate-General for Environment of the European Commission, September 2025. Service Request 090202/2025/4500087778/SFRA/ENV.C1. URL: https://circabc.europa.eu/ui/group/9ab5926d-bed4-4322-9aa7-9964bbe8312d/library/c6bd5217-496c-423f-9357-b841fdbd082a/details.