The Swiss Register of Plant Protection Products as a Relational Data Model

Since the year 2011, the Swiss Federal Office of Agriculture (FOAG) and later the Federal Food Safety and Veterinary Office, now responsible for the authorisation of plant protection products in Switzerland, publish the contents of the Swiss Register of Plant Protection Products (SRPPP) on their respective websites in a custom format based on the Extensible Markup Language (XML).

In our group at Agroscope, different solutions have been used to read in, process and use these data. This package offers a fresh approach to directly read in the data into R.

The current download location of the latest published XML version of the SRPPP is stored in the package as srppp::srppp_xml_url. Reading in the current data is as simple as

library(srppp)
current_register <- srppp_dm()

The resulting data object contains a number of related tables with information on the authorized products and their uses. The relation between those tables is shown below. The identification numbers of the evaluated products (pNbrs) and the general information associated with them are shown in dark blue. The tables defining their composition in terms of the most important ingredients are shown in orange. Tables with information on the authorized uses of the products are shown in dark green. Finally, the tables giving names and expiration dates of products and parallel imports as well as an identification number for the authorization holder are shown in light blue.

library(DiagrammeR)
dm_draw(current_register)

Substances

At the bottom of the table hierarchy, there is the list of substances. For each substance, there is a primary key pk, a chemical name based on IUPAC nomenclature, and substance names in the official languages of Switzerland. The first four entries out of 440 are shown below.

library(knitr)
current_register$substances |> 
  select(pk, iupac, substance_de, substance_fr, substance_it) |> 
  head(n = 4L) |> 
  kable()

pk	iupac	substance_de	substance_fr	substance_it
3	1-naphthylacetic acid	1-Naphthylacetic acid	1-naphtyl acide acétique	Acido 1-naftilacetico
4	(2,4-dichlorophenoxy)acetic acid	2,4-D	2,4-D	2,4-D
8	avermectin B1	Abamectin	abamectine	Abamectina
13	2-chloro-6-nitro-3-phenoxyaniline	Aclonifen	aclonifène	Aclonifen

Products

There are three tables defining the products, pNbrs, products and ingredients. The P-Numbers contained in the table pNbrs are identifiers of product compositions. Products with the same P-Number are considered equivalent in terms of efficacy and risks. The table pNbrs is only there for a technical reason. It simply contains a column holding the P-Numbers.

Unique products (P-Numbers) and their composition

The composition of these products in terms of active substances, additives to declare, synergists and safeners is is defined in the table ingredients, giving the contents in percent weight per weight (percent). For liquid products, a content in grams per litre is also given (g_per_L). If a substance is contained in a form that differs from the definition given in the substance table, this is documented in the respective additional columns as illustrated by the first five rows shown below.

current_register$ingredients |> 
  select(pNbr, pk, type, percent, g_per_L, ingredient_de, ingredient_fr) |> 
  head(n = 5L) |> 
  kable()

pNbr	pk	type	percent	g_per_L	ingredient_de	ingredient_fr
38	338	ACTIVE_INGREDIENT	80.0
1182	1067	ACTIVE_INGREDIENT	34.7	400	als 38.0% MCPB-Natrium-Salz (439 g/l)	sous forme de 38.0 % MCPB de sel de sodium (439 g/L)
1192	1067	ACTIVE_INGREDIENT	34.7	400	als 38.0 % MCPB-Natrium-Salz (439 g/L)	sous forme de 38.0 % MCPB de sel de sodium (439 g/L)
1263	338	ACTIVE_INGREDIENT	80.0
1865	1027	ACTIVE_INGREDIENT	99.1	830

The frequency of occurrence of the four different ingredient types is quite different.

library(dplyr)
current_register$ingredients |> 
  select(pk, type) |> 
  unique() |> 
  group_by(type) |> 
  summarize(n = n()) |> 
  kable()

type	n
ACTIVE_INGREDIENT	335
ADDITIVE_TO_DECLARE	101
SAFENER	5
SYNERGIST	2

Additives to declare are additives that have an effect on classification and labelling of the product. All substances occurring as synergists or safeners are listed below.

current_register$ingredients |> 
  left_join(current_register$substances, by = "pk") |>
  filter(type %in% c("SYNERGIST", "SAFENER")) |>
  group_by(type, substance_de) |> 
  summarize(n = n(), .groups = "drop_last") |> 
  select(type, substance_de, n) |> 
  arrange(type, substance_de) |> 
  kable()

type	substance_de	n
SAFENER	Benoxacor	1
SAFENER	Cloquintocet-mexyl	17
SAFENER	Cyprosulfamid	2
SAFENER	Isoxadifen-ethyl	3
SAFENER	Mefenpyr-Diethyl	10
SYNERGIST	Piperonyl butoxid	6
SYNERGIST	Sesamöl raffiniert	3

Note that the first two lines in the code could also be replaced by

current_register |> 
  dm_flatten_to_tbl(ingredients) |>

which makes use of the foreign key declaration in the data object. However, the more explicit version using left_join is probably easier to understand.

Registered products (W-Numbers)

The registered products are identified by the so-called W-Numbers. The relation between P-Numbers and W-Numbers is illustrated below by showing the first five entries in the products table.

current_register$products |> 
  select(-terminationReason) |> 
  head() |> 
  kable()

pNbr	wNbr	name	isSalePermission	permission_holder
38	18	Thiovit Jet	FALSE	10388
38	18-1	Sufralo	TRUE	10712
38	18-2	Capito Bio-Schwefel	TRUE	10712
38	18-3	Sanoplant Schwefel	TRUE	10388
38	18-4	Biorga Contra Schwefel	TRUE	10388
1182	923	Divopan	FALSE	10388

As can be seen in these example entries, several registrations (W-Numbers) of the same product type (P-Number) can exist. The W-Numbers without a dash (e.g. 18) are the original registrations, and the ones containing a dash and a trailing number (e.g. 18-1, 18-2) are equivalent products with sales permissions that have a different legal entity as permission holder.

If the product registration has been revoked, the relevant latest dates for selling the product (soldoutDeadline) and for use of the product (exhaustionDeadline) are given in the respective columns.

current_register$products |>
  filter(exhaustionDeadline != "") |> 
  select(-terminationReason) |> 
  head() |> 
  kable()

pNbr	wNbr	name	exhaustionDeadline	soldoutDeadline	isSalePermission	permission_holder
3726	2935	Polyram DF	2025-07-01	2025-01-01	FALSE	10019
3726	2935-1	Metiram WG	2025-07-01	2025-01-01	TRUE	10213
3726	2935-2	Aviso	2025-07-01	2025-01-01	TRUE	10050
4426	4343	Cypermethrin	2026-06-11	2025-06-11	FALSE	10079
5166	3060	Etephon Médol	2026-06-30	2025-06-30	FALSE	10115
6579	5530	Lontrel 100	2024-11-30	2023-11-30	FALSE	10054

At the build time of this vignette, there were 1716 product registrations for 1125 P-Numbers in the Swiss Register of Plant Protection Products (SRPPP) as published on the website of the Federal Food Safety and Veterinary Office.

Example code for getting a product composition from a product name

If the name of a product is known, the associated P-Numbers and W-Numbers as well as the product composition can be retrieved by a command like the following.

current_register$products |>
  filter(name == "Plüsstar") |>
  left_join(current_register$ingredients, by = "pNbr") |>
  left_join(current_register$substances, by = "pk") |>
  select(pNbr, name, substance_de, percent, g_per_L) |> 
  kable()

pNbr	name	substance_de	percent	g_per_L
4077	Plüsstar	2,4-D	14.8	170
4077	Plüsstar	Mecoprop-P	35.3	405

Uses

For each product type (P-Number), the registered uses (tagged as <Indication> in the XML file) are specified in the uses table. The use numbers in the column use_nr are generated while reading in the XML file, in order to be able to refer to each use by a combination of P-Number (pNbr) and use number (use_nr).

current_register$uses |> 
  filter(pNbr %in% c(6521L, 7511L) & use_nr < 10) |> 
  select(pNbr, use_nr, ends_with("dosage"), ends_with("rate"), units_de,
    waiting_period, time_units_en, application_area_de) |> 
  head(20) |> 
  kable()

pNbr	use_nr	min_dosage	min_rate	max_rate	units_de	waiting_period	time_units_en	application_area_de
6521	1		1.0		l/ha			Gemüsebau
6521	2		0.5	1	l/ha			Feldbau
6521	3		1.0		l/ha			Feldbau
6521	4		1.0		l/ha			Feldbau
6521	5		1.0		l/ha			Feldbau
6521	6		1.0		l/ha			Feldbau
6521	7		1.0		l/ha			Feldbau
6521	8		1.0		l/ha	3	Week(s)	Feldbau
6521	9		1.0		l/ha			Feldbau
7511	1	0.4	4.0		kg/ha	3	Days	Beerenbau
7511	2	0.2	3.2		kg/ha	3	Week(s)	Obstbau
7511	3	0.3				3	Days	Gemüsebau
7511	4	0.3	3.0		kg/ha	3	Days	Beerenbau
7511	5	0.2	3.2		kg/ha	2	Week(s)	Obstbau
7511	6	0.4				3	Days	Beerenbau
7511	7	0.3				3	Days	Beerenbau
7511	8	0.4	4.0		kg/ha	3	Days	Beerenbau
7511	9	0.3			kg/ha	3	Days	Gemüsebau

The columns min_dosage and max_dosage contain either a range of recommended product concentrations in the spraying solution in percent, or, if only min_dosage is given, the recommended concentration. Similarly, if there is a single recommended application rate, it is stored in min_rate. Only if there is a recommended range of application rates, max_rate is given as well. The units of the application rate are given in the columns starting with units_. In addition, a required waiting period before harvest can be specified, as well as the application area associated with the use.

Application rates

Application rates in terms of grams of the active substances contained in the products per hectare can be calculated using the function application_rate_g_per_ha() as illustrated in the example below.

In a first step, some uses need to be selected and joined with the information in the ingredients table. The names of the active substances can be joined as well.

example_uses <- current_register$products |> 
  filter(wNbr == "6168") |>
  left_join(current_register$uses, by = join_by(pNbr),
    relationship = "many-to-many") |> 
  left_join(current_register$ingredients, by = join_by(pNbr),
    relationship = "many-to-many") |>
  left_join(current_register$substances, by = join_by(pk)) |>
  select(pNbr, name, use_nr,
    min_dosage, max_dosage, min_rate, max_rate, units_de,
    application_area_de,
    substance_de, percent, g_per_L) |> 
  filter(use_nr %in% c(1:5, 12:17))

kable(example_uses)

pNbr	name	use_nr	min_rate	max_rate	units_de	application_area_de	substance_de	percent	g_per_L
7105	Boxer	1	5.0		l/ha	Gemüsebau	Prosulfocarb	78.4	800
7105	Boxer	2	5.0		l/ha	Gemüsebau	Prosulfocarb	78.4	800
7105	Boxer	3	5.0		l/ha	Gemüsebau	Prosulfocarb	78.4	800
7105	Boxer	4	4.0		l/ha	Gemüsebau	Prosulfocarb	78.4	800
7105	Boxer	5	2.5	3.0	l/ha	Gemüsebau	Prosulfocarb	78.4	800
7105	Boxer	12	3.0	4.5	l/ha	Feldbau	Prosulfocarb	78.4	800
7105	Boxer	13	2.5	5.0	l/ha	Feldbau	Prosulfocarb	78.4	800
7105	Boxer	14	3.0	5.0	l/ha	Feldbau	Prosulfocarb	78.4	800
7105	Boxer	15	5.0		l/ha	Feldbau	Prosulfocarb	78.4	800
7105	Boxer	16	4.0		l/ha	Gemüsebau	Prosulfocarb	78.4	800
7105	Boxer	17	2.5	5.0	l/ha	Feldbau	Prosulfocarb	78.4	800

Then, the application rates can be calculated for these uses as illustrated below.

application_rate_g_per_ha(example_uses) |>
  select(ai = substance_de, app_area = application_area_de,
  ends_with("rate"), units_de, rate = rate_g_per_ha) |> 
  head(n = 14) |> 
  kable()

ai	app_area	min_rate	max_rate	units_de	rate
Prosulfocarb	Gemüsebau	5.0		l/ha	4000
Prosulfocarb	Gemüsebau	5.0		l/ha	4000
Prosulfocarb	Gemüsebau	5.0		l/ha	4000
Prosulfocarb	Gemüsebau	4.0		l/ha	3200
Prosulfocarb	Gemüsebau	2.5	3.0	l/ha	2400
Prosulfocarb	Feldbau	3.0	4.5	l/ha	3600
Prosulfocarb	Feldbau	2.5	5.0	l/ha	4000
Prosulfocarb	Feldbau	3.0	5.0	l/ha	4000
Prosulfocarb	Feldbau	5.0		l/ha	4000
Prosulfocarb	Gemüsebau	4.0		l/ha	3200
Prosulfocarb	Feldbau	2.5	5.0	l/ha	4000

Culture forms and cultures

In the current SRPPP versions, there are only two culture forms, greenhouse cultivation and field cultivation.

current_register$culture_forms |> 
  select(starts_with("culture")) |> 
  unique() |> 
  kable()

culture_form_de	culture_form_fr	culture_form_it	culture_form_en
Gewächshaus	serre	Serra
Freiland	plein air	Pieno campo

For specific uses, e.g. for uses number 1 and 2 of product “Boxer” with W-Number 6168, the associated culture form and the registered cultures can be listed as shown below. As each use is typically associated with only one culture form, the culture form and the actual cultures can be joined to the use numbers in one step.

current_register$products |> 
  filter(wNbr == "6168") |> 
  left_join(current_register$uses, by = "pNbr") |> 
  filter(use_nr %in% 1:2) |> 
  left_join(current_register$culture_forms, by = c("pNbr", "use_nr")) |> 
  select(pNbr, use_nr, ends_with("de")) |> 
  kable()

pNbr	use_nr	units_de	time_units_de	application_area_de	culture_form_de
7105	1	l/ha	Tage	Gemüsebau	Freiland
7105	2	l/ha	Tage	Gemüsebau	Freiland

Target organisms

The target organisms for each use can be found in the table pests. Example code for retrieving the target organisms for specific uses is given below.

current_register$pests |> 
  filter(pNbr == 7105L, use_nr %in% 1:2) |> 
  select(use_nr, ends_with("de"), ends_with("fr")) |> 
  kable()

use_nr	pest_de	pest_fr
1	Einjährige Dicotyledonen (Unkräuter)	dicotylédones annuelles
1	Einjährige Monocotyledonen (Ungräser)	monocotylédones annuelles
2	Einjährige Dicotyledonen (Unkräuter)	dicotylédones annuelles
2	Einjährige Monocotyledonen (Ungräser)	monocotylédones annuelles

Unique combinations of cultures and target organisms

In the calculations of mean application rates for the Swiss National Risk Indicator (Korkaric et al. 2022, 2023), unique combinations of product, culture, and target organism were termed “indications”. Note that when using this definition of indications, each XML section <Indication> can describe several indications. The relation between uses (<Indication> sections) and indications as defined in the indicator project is illustrated below.

culture_pest_combinations <- current_register$uses |> 
  filter(pNbr == 6521L) |> 
  left_join(current_register$cultures, by = c("pNbr", "use_nr")) |> 
  left_join(current_register$pests, by = c("pNbr", "use_nr")) |> 
  select(pNbr, use_nr, application_area_de, culture_de, pest_de)

kable(culture_pest_combinations)

pNbr	use_nr	application_area_de	culture_de	pest_de
6521	1	Gemüsebau	Spargel	Spargelrost
6521	1	Gemüsebau	Spargel	Blattschwärze der Spargel
6521	2	Feldbau	Weizen	Gelbrost
6521	3	Feldbau	Weizen	Septoria-Spelzenbräune (S. nodorum)
6521	4	Feldbau	Weizen	Ährenfusariosen
6521	5	Feldbau	Weizen	Echter Mehltau des Getreides
6521	6	Feldbau	Grasbestände zur Saatgutproduktion	Blattfleckenpilze
6521	6	Feldbau	Grasbestände zur Saatgutproduktion	Rost der Gräser
6521	7	Feldbau	Winterroggen	Braunrost
6521	8	Feldbau	Lupinen	Anthraknose
6521	9	Feldbau	Lein	Stängelbräune des Leins
6521	9	Feldbau	Lein	Pasmokrankheit
6521	9	Feldbau	Lein	Echter Mehltau des Leins
6521	10	Feldbau	Raps	Erhöhung der Standfestigkeit
6521	10	Feldbau	Raps	Wurzelhals- und Stengelfäule
6521	11	Feldbau	Eiweisserbse	Graufäule (Botrytis cinerea)
6521	11	Feldbau	Eiweisserbse	Rost der Erbse
6521	11	Feldbau	Eiweisserbse	Brennfleckenkrankheit der Erbse
6521	12	Gemüsebau	Erbsen	Brennfleckenkrankheit der Erbse
6521	12	Gemüsebau	Erbsen	Rost der Erbse
6521	12	Gemüsebau	Erbsen	Graufäule (Botrytis cinerea)
6521	13	Feldbau	Ackerbohne	Rost der Ackerbohne
6521	13	Feldbau	Ackerbohne	Braunfleckenkrankheit
6521	14	Feldbau	Raps	Wurzelhals- und Stengelfäule
6521	15	Feldbau	Raps	Sclerotinia-Fäule

In this example, there are 25 such “indications” for the 15 uses.

Application comments

Sometimes, use specific comments can be found in the application_comments table.

current_register$application_comments |>
  filter(pNbr == 7105, use_nr %in% 1:2) |> 
  select(pNbr, use_nr, ends_with("de"), ends_with("fr")) |> 
  kable()

pNbr	use_nr	application_comment_de	application_comment_fr
7105	1	7 Tage nach dem Pflanzen.	7 jours après la plantation.
7105	2	7 Tage nach dem Pflanzen.	7 jours après la plantation.

Obligations

The use conditions for each use are listed in the table obligations. In the following example, the column sw_runoff_points is selected in the output, as both use authorisations are conditional on risk mitigation for runoff to surface water amounting to at least one point.

current_register$obligations |>
  filter(pNbr == 7105, use_nr %in% 1:2) |> 
  select(pNbr, use_nr, code, obligation_de, sw_runoff_points) |> 
  kable()

pNbr	use_nr	code	obligation_de	sw_runoff_points
7105	1	obligation 692	Nachbau anderer Kulturen: 16 Wochen Wartefrist.
7105	1	N01: Profi Re-entry	Nachfolgearbeiten in behandelten Kulturen: bis 48 Stunden nach Ausbringung des Mittels Schutzhandschuhe + Schutzanzug tragen.
7105	1	obligation 1928	Maximal 1 Behandlung pro Kultur.
7105	1	ML01_A04_T01	Ansetzen der Spritzbrühe: Schutzhandschuhe tragen. Ausbringen der Spritzbrühe: Schutzhandschuhe + Schutzanzug + Visier + Kopfbedeckung tragen. Technische Schutzvorrichtungen während des Ausbringens (z.B. geschlossene Traktorkabine) können die vorgeschriebene persönliche Schutzausrüstung ersetzen, wenn gewährleistet ist, dass sie einen vergleichbaren oder höheren Schutz bieten.
7105	1	Abschwemmung 1 Punkt	SPe 3: Zum Schutz von Gewässerorganismen muss das Abschwemmungsrisiko gemäss den Weisungen der Zulassungsstelle um 1 Punkt reduziert werden.	1
7105	1		Splitbehandlung gemäss den Angaben der Bewilligungsinhaberin (max. 3 l/ha je Split, angegebene Aufwandmenge entspricht total bewilligter Menge).
7105	1	obligation 2032	Phytotoxschäden bei empfindlichen Arten oder Sorten möglich; vor allgemeiner Anwendung Versuchspritzung durchführen.
7105	1	Bewilligt nach Art. 35 PSMV (minor use).	Bewilligt als geringfügige Verwendung nach Art. 35 PSMV (minor use).
7105	2	Abschwemmung 1 Punkt	SPe 3: Zum Schutz von Gewässerorganismen muss das Abschwemmungsrisiko gemäss den Weisungen der Zulassungsstelle um 1 Punkt reduziert werden.	1
7105	2	N01: Profi Re-entry	Nachfolgearbeiten in behandelten Kulturen: bis 48 Stunden nach Ausbringung des Mittels Schutzhandschuhe + Schutzanzug tragen.
7105	2	obligation 692	Nachbau anderer Kulturen: 16 Wochen Wartefrist.
7105	2	ML01_A04_T01	Ansetzen der Spritzbrühe: Schutzhandschuhe tragen. Ausbringen der Spritzbrühe: Schutzhandschuhe + Schutzanzug + Visier + Kopfbedeckung tragen. Technische Schutzvorrichtungen während des Ausbringens (z.B. geschlossene Traktorkabine) können die vorgeschriebene persönliche Schutzausrüstung ersetzen, wenn gewährleistet ist, dass sie einen vergleichbaren oder höheren Schutz bieten.
7105	2	obligation 1928	Maximal 1 Behandlung pro Kultur.
7105	2	obligation 2032	Phytotoxschäden bei empfindlichen Arten oder Sorten möglich; vor allgemeiner Anwendung Versuchspritzung durchführen.
7105	2		Splitbehandlung gemäss den Angaben der Bewilligungsinhaberin (max. 3 l/ha je Split, angegebene Aufwandmenge entspricht total bewilligter Menge).
7105	2	Bewilligt nach Art. 35 PSMV (minor use).	Bewilligt als geringfügige Verwendung nach Art. 35 PSMV (minor use).

References

Korkaric, M., L. Ammann, I. Hanke, J. Schneuwly, M. Lehto, T. Poiger, L. de Baan, O. Daniel, and J. F. Blom. 2022. “Nationale Risikoindikatoren Basierend Auf Dem Verkauf von Pflanzenschutzmitteln.” Agrarforschung Schweiz 13: 1–10.

Korkaric, M., M. Lehto, T. Poiger, L. de Baan, M. Mathis, L. Ammann, I. Hanke, M. Balmer, and J. Blom. 2023. “Nationale Risikoindikatoren Für Pflanzenschutzmittel : Weiterführende Analysen.” Journal Article. Agroscope Science, 1–48.

Johannes Ranke

13 August, 2024 (rebuilt 2024-09-10)