Aside the main db report, which describes all the vocabularies used in this document, we have to build a separate database for metrics. In the project these correspond to LHT, but it’s more, the data should also correspond to the time series and the sampling db developped by WGEEL. The two latter data structure (series and sampling) are very similar and they both hold very similar group metrics and individual metrics.

The first was developed initially to store data about the series used in recruitment. In practice, it consists of three tables, the t_series_ser (Figure Figure 1 - top in blue) table contains series id and description, with columns describing the sampling details, the stage used, the method… This is the main identifier of the series which will be used as a reference in all dependent tables. The second t_dataseries_das table (Figure Figure 1 - on the right) holds data about annual values in series. These are typically annual counts for recruitment, along with additional effort data. Linked to these are group metric series used to describe the series, mean age of eel, mean size, proportion of glass eel among the yellow eels, proportion of females … (Figure Figure 1 - in orange) Finally, we can link individual metrics. The individual metrics are all detailed for one fish. And they concern metrics like size, weight, sex, but also can hold data about quality, contamination. So these are in essence the Life History traits analysed by WP2 in DIASPARA (Figure Figure 1 - in pink).

A sql diagram of the relations of tables for series — Figure 1: Diagram for series

The second type of data was developed to hold the data collected for DCF. These can be metrics collected from sampling by the fishermen, data coming from the analysis of electrofishing data, or other experimental sampling that are not reported as series. Currently the two structures for series and sampling are very close, the only difference is that there is no annual number linked to the sampling data, and that they are not linked to a stage in the first table, so the stage is added in the fish table. The difference in table structure is illustrated below in tables highlighted in yellow (Figure Figure 2).

A sql diagram of the relations of tables for sampling — Figure 2: Diagram for sampling

The database development highlighted in the current report has several objectives :

The first objective is to join the two database to simplify the database development and handling of data.
The second objective is to use the new referentials created for the migdb database.
The third objective is to import data from WP2, the excel sheets have been created in february 2025 and will already (in March) require some adaptation as the database evolves, for instance the referential of stages is no longer in line with the templates.
The fourth objective is to hand over this database, along with the migdb to ICES, for integration in ICES database ecosystem, and use of DATSU in datacall.
The fifth objective is to adapt the shiny scripts of data integration.

Creating the database structure from WGEEL (TODO)

git issue #23 Write simplified structure from WGEEL

The main issue will require to merge the two table structures (sampling and series) and adapt to migdb vocabulary.

Once done a beta version probably not completely adapted will be released.

milestone metric DB beta version

Figure 3: Simplified structure of the metric database. The time series table, start with an identifier (at the bottom) which correspond to a station or a regional monitoring program. The series contains the main attributes, including geometry, species stage … This table is empty and filled by inheritance (—> arrows).The actual data are in the schema corresponding to each working group (pink and green).

Station

To include the station, we need two tables, the fist one is the Station dictionary and the second is the relation Dictionary (which relates two stations together). Now these tables in turn rely on some vocabs from ICES which we are loading straight as they are in our template database.

SemanticRelation

Code to show SemanticRelation.

SemanticRelation <- getCodeList("SemanticRelation")
knitr::kable(SemanticRelation) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 1: Semantic relation

Id	Guid	Key	Description	Modified	Deprecated	CodeTypeID	CodeTypeGUID
169652	83921b19-6958-4619-b941-55fb3f207b8d	1	Broader	2017-02-27T12:33:33.203	FALSE	1495	816acef1-a7cd-44a7-a24a-ee3a65241024
169651	3420f8cf-1090-4810-80ee-7a144c26d3bb	2	Narrower	2017-02-27T12:33:13.493	FALSE	1495	816acef1-a7cd-44a7-a24a-ee3a65241024
169653	1d8976e7-7fb6-4dac-9b70-b282788e57c6	3	Related	2017-02-27T12:31:35.25	FALSE	1495	816acef1-a7cd-44a7-a24a-ee3a65241024
196324	4c8a38b4-2609-4a0a-aa49-b05148b1f315	4	SameAs	2018-05-30T13:44:10.893	FALSE	1495	816acef1-a7cd-44a7-a24a-ee3a65241024
196325	8c7c8ae7-1942-432e-9646-db0aef26498b	5	IsReplacedBy	2018-07-11T11:05:44.437	FALSE	1495	816acef1-a7cd-44a7-a24a-ee3a65241024
196764	d6d94c4f-d888-4a72-8152-16986a72d86d	6	Replaces	2018-07-11T11:05:56.097	FALSE	1495	816acef1-a7cd-44a7-a24a-ee3a65241024

Code to import relation.

dbWriteTable(con_diaspara, "SemanticRelation", PRGOV)
dbExecute(con_diaspara, 'ALTER TABLE "SemanticRelation" SET SCHEMA ref;') 
dbExecute(con_diaspara, 'ALTER TABLE ref."SemanticRelation" ADD CONSTRAINT semanticrelation_pkey PRIMARY KEY ("Key");')

PRGOV

Code to show PRGOV.

PRGOV <- getCodeList("PRGOV")
knitr::kable(PRGOV) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 2: Program Governance for the Station Dictionary (10 first lines)

Id	Guid	Key	Description	LongDescription	Modified	Deprecated	CodeTypeID	CodeTypeGUID
156691	47a15656-9a4c-4edc-9013-aeb76fc4405f	A	AMAP	Program Governance for the Station Dictionary. Designations with funding first, then project	2024-11-27T09:31:28.347	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
196223	28a85b12-c702-40b9-a79c-6010184a7ba7	ASMT-MIME	Assessment Grouping for OSPAR MIME	Program Governance for the Station Dictionary. Designations with funding first, then project	2019-03-06T17:56:24.277	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
156746	34f8c0f8-c096-45ed-a2c6-dfad117b44aa	BO	BONUS	Program Governance for the Station Dictionary. Designations with funding first, then project	2016-07-12T13:00:53.21	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
156745	1cb8b3d2-6a52-4d6c-9e71-59170afbf035	BO1	BONUS Beast	Program Governance for the Station Dictionary. Designations with funding first, then project	2016-07-12T13:06:48.253	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
156722	bcdd09de-b915-4f10-97a1-d094f9544d44	FP7J	FP7 - Jericho	Program Governance for the Station Dictionary. Designations with funding first, then project	2016-07-12T13:08:19.653	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
156689	83052fb7-d4e1-482f-a947-801e2273f478	H	HELCOM	Program Governance for the Station Dictionary. Designations with funding first, then project	2025-02-05T10:55:23.333	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
156742	24f1673d-d7e1-4501-8104-665d4bf2fd93	H1	HELCOM BMP	Program Governance for the Station Dictionary. Designations with funding first, then project	2018-05-16T16:46:13.95	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
156743	d29af7d1-4cc1-42ac-aea4-4c253ac0f139	H2	HELCOM COMBINE	Program Governance for the Station Dictionary. Designations with funding first, then project	2025-01-08T13:09:53.477	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
202517	6ef57389-8fbe-4cd3-8bc7-660c77d84199	H3	HELCOM Continuous Underwater Noise monitoring	Program Governance for the Station Dictionary. Designations with funding first, then project	2025-04-08T08:52:43.98	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
156690	e14c46d5-b1b0-48c7-89ef-ac8da49f4e0f	ICES	ICES	Program Governance for the Station Dictionary. Designations with funding first, then project	2025-01-08T13:09:58.03	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
156860	6e18b63f-3105-4c21-b741-fa44b2a9be84	MP	MEDPOL Mediterranean Pollution and Research Programme	Program Governance for the Station Dictionary. Designations with funding first, then project	2021-10-01T00:08:39.66	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
261196	4af03f7e-aa21-41d0-99fe-a71a3839f621	MSFD	Marine Strategy Framework Directive	Program Governance for the Station Dictionary. Designations with funding first, then project	2025-02-06T15:26:37.893	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
169793	e2f717e3-c74f-4d84-b6bc-237c9bed7cfa	MSFD-10	Marine Strategy Framework Directive - Descriptor 10 Litter (do not use)	Program Governance for the Station Dictionary. Designations with funding first, then project	2025-01-06T11:14:48.58	TRUE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
232239	bf985b9c-e5f9-4d38-ac5e-84781a0d9e77	MSFD-8	Marine Strategy Framework Directive - Descriptor 8 Contaminants (do not use)	Program Governance for the Station Dictionary. Designations with funding first, then project	2025-01-07T10:41:28.523	TRUE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
194868	26a82124-3a54-46e4-8637-b9671ee936c4	MSFD-9	Marine Strategy Framework Directive - Descriptor 9 Contaminants in seafood destined for human consumption (do not use)	Program Governance for the Station Dictionary. Designations with funding first, then project	2025-01-07T10:41:50.66	TRUE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
156687	745bad18-04de-4053-a752-48003d53dd38	NTL	National	Program Governance for the Station Dictionary. Designations with funding first, then project	2025-04-08T08:52:46.593	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
156688	35b9d6b2-f4b5-4cac-9e5f-df038848c45d	O	OSPAR	Program Governance for the Station Dictionary. Designations with funding first, then project	2025-05-23T09:48:33.207	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
156740	84f3e3f0-a432-4264-b27a-8963b0f79c2f	O1	OSPAR JMP	Program Governance for the Station Dictionary. Designations with funding first, then project	2025-01-06T13:40:45.833	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
156741	6bf2bb6d-5e90-4ab2-9b57-4d573ad6d491	O2	OSPAR CEMP	Program Governance for the Station Dictionary. Designations with funding first, then project	2025-01-06T13:40:42.767	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
156744	2f5b26a2-1a19-47e9-a56a-8f7e57cf6b3a	SDN	SeaDataNet	Program Governance for the Station Dictionary. Designations with funding first, then project	2016-07-12T13:01:50.12	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde
156692	533f3f8f-df60-47fb-a2a8-74ebc0afb1e0	WFD	Water Framework Directive	Program Governance for the Station Dictionary. Designations with funding first, then project	2025-02-06T15:30:06.833	FALSE	1473	dc896ec4-e236-4a4c-a900-e4bcafcf0cde

Code to import PRGOV.

dbWriteTable(con_diaspara, "PRGOV", PRGOV)
dbExecute(con_diaspara, 'ALTER TABLE "PRGOV" SET SCHEMA ref;') 
dbExecute(con_diaspara, 'ALTER TABLE ref."PRGOV" ADD CONSTRAINT prgov_pkey PRIMARY KEY ("Key");')

EDMO

Table 3: European Directory of Marine Organisations (EDMO)

Code to show EDMO.

EDMO <- getCodeList("EDMO") %>% slice_head(n=10)  %>%
knitr::kable()  %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Addings institutions to the EDMO

To request a new EDMO an organisation needs to send an email to info@maris.nl

Code to import EDMO.

dbWriteTable(con_diaspara, "EDMO", EDMO)
dbExecute(con_diaspara, 'ALTER TABLE "EDMO" SET SCHEMA ref;')  
dbExecute(con_diaspara, 'ALTER TABLE ref."EDMO" ADD CONSTRAINT edmo_pkey PRIMARY KEY ("Key");')

PURPM

Code to show PURPM.

PURPM <- getCodeList("PURPM")
knitr::kable(PURPM) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 4: Purpose of Monitoring

Id	Guid	Key	Description	Modified	Deprecated	CodeTypeID	CodeTypeGUID
261197	21c70853-68e1-4c05-94fc-02c04c431011	8	MSFD Descriptior 8 - Contaminants	2025-01-07T10:41:56.963	FALSE	42	83c14aa4-133b-49bc-8a32-107047146848
261198	b5758296-3a14-452f-aaa3-4d9367377b67	9	MSFD Descriptior 9 - Contaminants in seafood destined for human consumption	2025-01-07T10:41:58.863	FALSE	42	83c14aa4-133b-49bc-8a32-107047146848
26936	0eed054b-b478-4c6e-b448-51a58c6ec967	B	Biological effects monitoring	2025-02-05T10:51:37.397	FALSE	42	83c14aa4-133b-49bc-8a32-107047146848
26937	b677e830-34f8-4eaf-88e3-3377d726d209	E	Eutrophication effects monitoring	2025-02-05T11:04:21.997	FALSE	42	83c14aa4-133b-49bc-8a32-107047146848
140653	18ad665b-1597-4cab-90ba-84445678ef14	F	Fishery trawl surveys	2016-08-14T10:36:24.617	FALSE	42	83c14aa4-133b-49bc-8a32-107047146848
26938	3f0310df-3a9c-48fb-a1e3-8a47a918f1aa	H	Human health risk assessment	2025-01-07T10:41:40.53	FALSE	42	83c14aa4-133b-49bc-8a32-107047146848
140654	89cd9460-1d43-4fd0-b658-7619a97bb864	L	Litter	2025-01-21T14:00:33.483	FALSE	42	83c14aa4-133b-49bc-8a32-107047146848
26939	2451faa3-831b-43c6-9aff-939c701d864c	N	No specific purpose	2021-04-06T16:57:06.723	FALSE	42	83c14aa4-133b-49bc-8a32-107047146848
137083	e01f97eb-6e49-4741-bc75-4ff29407584a	O	Ocean acidification	2025-03-05T11:55:50.7	FALSE	42	83c14aa4-133b-49bc-8a32-107047146848
51445	f02b4faa-d081-4061-b8c8-81b339b6da2e	R	Research	2025-03-05T11:57:31.303	FALSE	42	83c14aa4-133b-49bc-8a32-107047146848
26940	49df3df6-f880-4330-9707-a276eb92b4f2	S	Spatial (geographical) distribution monitoring	2025-05-23T09:48:40.01	FALSE	42	83c14aa4-133b-49bc-8a32-107047146848
26941	562ae8f8-e175-4f07-ae92-a5af7b32539d	T	Temporal trend monitoring	2025-05-23T09:48:37.557	FALSE	42	83c14aa4-133b-49bc-8a32-107047146848
198772	9efdc23c-153b-44bf-a978-55a406384eb2	U	Underwater noise monitoring	2025-04-08T08:52:49.863	FALSE	42	83c14aa4-133b-49bc-8a32-107047146848

Code to import PURPM.

dbWriteTable(con_diaspara, "PURPM", PURPM)
dbExecute(con_diaspara, 'ALTER TABLE "PURPM" SET SCHEMA ref;')
dbExecute(con_diaspara, 'ALTER TABLE ref."PURPM" ADD CONSTRAINT purm_pkey PRIMARY KEY ("Key");')

DTYPE

Code to show icesStation_DTYPE.

Station_DTYPE <- getCodeList("Station_DTYPE")
knitr::kable(Station_DTYPE) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 5: Station dictionary data type

Id	Guid	Key	Description	Modified	Deprecated	CodeTypeID	CodeTypeGUID
156767	9ae94eaa-67f5-4499-9fec-9c753d454d8b	BP	Bacterioplankton	2018-05-24T00:40:34.24	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148249	0a1d0292-367b-48f8-bfe4-d53c460c59b1	CF	Contaminants/hazardous substances in biota	2025-03-05T11:58:40.37	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148250	da00a888-4d78-40f3-bc37-3b2575f75f7b	CS	Contaminants/hazardous substances in sediment	2025-05-23T09:48:25.073	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
202489	e03228f9-9f70-46b7-899f-226b02054f2b	CUWN	Continuous underwater noise	2025-04-08T08:52:36.057	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148251	8655c1bd-330a-4f7a-812c-f6cc0bd8f5a2	CW	Contaminants/hazardous substances in water	2025-04-23T11:35:23.967	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148256	85103227-9697-4487-a874-053e9d88de5d	DF	Disease in biota	2025-02-20T10:37:10.43	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148252	185f5a57-72e1-419b-b4ab-2a0b2aa0f899	EF	Biological effects in biota	2025-02-20T10:37:08.727	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148253	8bdccd4f-f0b7-401f-be43-9533a3fd72bf	ES	Biological effects in sediment	2025-04-23T11:35:22.617	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
156766	6583e8b5-5d79-4ba2-94cb-099dd745e8eb	EU	Eutrophication effects	2025-04-23T11:35:15.733	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148254	8a758bca-6647-4b47-94a6-7d85ea48a29b	EW	Biological effects in water	2025-04-23T11:35:27.227	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148263	3edfcdff-974f-47fe-be3a-8f742fb5b0a7	HY	Physico-chemical parameters in water	2022-10-21T20:41:44.293	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148258	fad0afaa-28e9-40b5-ac9c-1b1a06f82bc5	LT	Litter data	2025-01-06T11:15:11.02	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148255	ba545513-a5dc-4087-bd05-71c28da65f54	NU	Nutrients in water	2022-12-15T10:13:18.09	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148257	3768b56d-d6c7-48cf-9056-4c03a33e233d	OA	Ocean acidification	2025-03-05T11:57:17.763	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148259	3ba04e2e-025d-45d1-abd9-71fdd3508793	PB	Phytobenthos	2024-04-11T17:22:13.513	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148260	5fb5a8fb-cfce-4bda-9335-825071df513d	PP	Phytoplankton	2025-04-23T11:35:09.85	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
156768	fc02fa31-489b-4205-9dcf-bef7a1c9aded	PR	Primary production	2020-07-06T12:38:11.193	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148262	8f667c67-b741-4ee8-a363-51327e9b4799	ZB	Zoobenthos	2025-04-23T11:35:13.37	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148261	e5278079-7d6a-46b4-b844-4be4e4c0d344	ZP	Zooplankton	2025-04-23T11:35:11.783	FALSE	1400	111d1f9c-6352-47ec-98f5-bc2cb60bcb55

Code to import icesStation_DTYPE.

dbWriteTable(con_diaspara, "Station_DTYPE", Station_DTYPE)
dbExecute(con_diaspara, 'ALTER TABLE "Station_DTYPE" SET SCHEMA ref;')  
dbExecute(con_diaspara, 'ALTER TABLE ref."Station_DTYPE" ADD CONSTRAINT station_dtype_pkey PRIMARY KEY ("Key");')

WLTYP

Code to show ices WLTYP.

WLTYP <- getCodeList("WLTYP")
knitr::kable(WLTYP) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 6: Water and Land Station Type

Id	Guid	Key	Description	Modified	Deprecated	CodeTypeID	CodeTypeGUID
156363	9388a49e-4a5f-46b3-9b7b-8dcfb03e7b6f	BP	Beach - peri-urban	2021-12-10T10:21:48.54	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518
156362	a6e1119b-3bcc-4b0a-8053-f8f7a86f0053	BR	Beach - rural	2019-01-17T21:40:37.613	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518
156361	53708e1d-d3c6-47a7-a0ee-1c70e6fa7f9b	BU	Beach - urban	2024-08-26T12:34:32.887	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518
53327	86d9897d-6adc-430e-a689-f321b75cadcc	C	WFD Coastal water	2025-04-23T11:14:37.19	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518
53329	0a28de1c-018a-4841-b3a3-a68e01c81cd5	CE	Coastal water (Estuary)	2025-05-23T09:46:59.76	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518
54066	85a228c9-9033-4f97-accf-4968db78a90a	CF	Coastal water (Fjord)	2024-11-28T09:24:40.973	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518
53330	e57989c4-a3f9-4560-9460-60dce3a26185	CR	Coastal water (River)	2024-05-31T09:33:48.397	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518
252557	eb979616-8b95-4c1b-92e8-b5f8d8bf1b96	FW	Fresh water	2022-08-19T13:09:47.52	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518
53334	c557dc19-27b9-46b6-a164-d7d8d4f55738	L	Land station	2024-11-07T21:41:20.483	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518
134991	5b3da387-3b2b-47c4-9967-c3161f533207	LK	Lake	2022-11-01T13:01:25.573	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518
252556	b51355b0-b905-4b4e-ab12-98d9b47d7752	MC	Marine water (coast)	2025-04-08T08:52:40.937	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518
53333	a75522ef-5e4a-4e2d-8550-38091cb6c994	MO	Marine water (open sea)	2025-05-23T09:48:30.673	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518
252558	913ae617-a160-4687-a62c-8923c6762c4f	NA	Not applicable	2022-08-19T13:09:47.543	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518
53331	1c792737-6f55-422a-b709-88af2d78c4ea	T	WFD Transitional water - implies reduced salinity	2023-11-29T11:52:37.713	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518
53332	4f85f72f-c2f0-41c7-b966-c80c897b80d7	TT	Transitional water (Tidal) - significant tide and reduced salinity	2025-05-23T09:46:57.207	FALSE	212	70e73f70-643d-497b-8d91-a1560d1c4518

Code to import ices WLTYP.

dbExecute(con_diaspara, 'DROP TABLE IF EXISTS ref."WLTYP";')
WLTYP$Key[is.na(WLTYP$Key)] <- "NA"
dbWriteTable(con_diaspara, "WLTYP", WLTYP)
dbExecute(con_diaspara, 'ALTER TABLE "WLTYP" SET SCHEMA ref;') 
dbExecute(con_diaspara, 'ALTER TABLE ref."WLTYP" ADD CONSTRAINT wltype_pkey PRIMARY KEY ("Key");')

MSTAT

Code to show ices MSTAT.

MSTAT <- getCodeList("MSTAT")
knitr::kable(MSTAT) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 7: Type of monitoring station

Id	Guid	Key	Description	Modified	Deprecated	CodeTypeID	CodeTypeGUID
51864	35cd7508-3e7b-47a9-9b5d-ba043280e22e	B	WFD B - Baseline/Reference station	2024-11-28T09:24:46.697	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53326	00b2cc8b-aefa-4823-bbfc-6e6710f2a5c8	ID	Impacted directly from disruptions such as dredging or trawling	2025-04-07T14:18:53.457	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53325	57914639-50a3-4d7c-8fbd-cfd662ad76e9	IH	WFD I(HZ) - Impacted directly by discharges containing hazardous substances	2024-11-28T07:51:56.923	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53810	fb7f3e57-3392-4b55-8077-5b3da96d7900	IH-A	where impact is aluminum industry (primary) (nature of the industry, not to the impacted area)	2006-10-23T12:00:00	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53811	7931ff8a-4257-4d19-a8e5-8bdf2060db93	IH-C	where impact is chemical/pharmaceutical industry	2021-09-20T11:45:40.753	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
54097	1bdcff7c-10b3-4c71-b6fd-87cd51ac9e26	IH-D	dock where impact is antifoulants, fuel oil/petroleum products	2024-08-26T12:34:32.227	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53814	7774858e-2b82-4457-b5a3-cb07a613fba9	IH-E	where impact is surface treatment/electroplating	2006-10-23T12:00:00	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53815	c14296ec-08ea-443d-8128-077f53bde913	IH-F	where impact is phosphogypsum-fertilizers	2006-10-23T12:00:00	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
54098	28ccc88f-0f1e-4557-989e-21a097203f6d	IH-H	harbour where impact is antifoulants, fuel oil/petroleum products	2023-08-30T06:25:25.753	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53816	a6f885b4-87b3-4bee-b4c0-4822fd459699	IH-I	where impact is iron and steel industry (primary) (nature of the industry, not to the impacted area)	2022-01-06T13:08:53.717	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53817	27eca6da-1b91-47b3-8758-3622b40dcf38	IH-M	where impact is metal industry (non-ferrous)	2016-08-14T01:52:44.027	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53818	98d3b8cd-0ea4-407d-bee4-28938d432c51	IH-O	where impact is oil/gas exploration/production platform	2023-01-27T03:30:16.64	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53819	6bd5f9c1-46cc-47b7-b151-f9590a1c9862	IH-P	where impact is paper and pulp industry	2019-10-23T11:15:03.78	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53820	9a3e08c1-ecee-48ad-97bd-d94af2fa6603	IH-S	where impact is iron and steel industry (secondary) (nature of the industry, not to the impacted area)	2006-10-23T12:00:00	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
54099	6544bdd0-a2c6-45ae-bf9f-dbe1ab63319d	IH-W	where impact is from diffuse emissions from waste incineration	2017-04-03T20:51:30.467	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
51865	74356f36-785b-454b-b83a-7aa6a102ae3a	IP	WFD I(PHY) - Impacted directly by discharges affecting physico-chemical conditions	2019-10-23T11:14:12.907	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53822	3ab88ae3-e7f4-424b-89bb-d1fe8c57753e	IP-B	where impact is fossil fuel burning/non-nuclear power plant	2016-08-14T01:52:43.363	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53823	a92b6a9a-d5f2-4d78-b37b-eaa996c10f3c	IP-N	where impact is nuclear power plant	2006-10-23T12:00:00	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53824	75b8b6e4-7a79-4367-9094-648740baa137	IP-T	where impact is municipal waste water treatment plant	2024-11-28T07:58:57.637	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53324	ceb0b857-171a-49cc-84c6-49aa38e09590	RH	WFD R(HZ) - Representative of general conditions in terms of hazardous substances	2025-05-23T09:48:27.397	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a
53289	08354400-b079-4773-af80-134243623425	RP	WFD R(PHY) - Representative of general conditions for nutrients/organic matter	2025-04-23T11:14:47.713	FALSE	177	358203dc-6df5-4bc0-9da5-328f98fd618a

Code to import ices MSTAT.

dbWriteTable(con_diaspara, "MSTAT", MSTAT)
dbExecute(con_diaspara, 'ALTER TABLE ref."MSTAT" ADD CONSTRAINT mstat_pkey PRIMARY KEY ("Key");')

Deprecated

Code to show ices Deprecated.

Deprecated <- getCodeList("Deprecated")
knitr::kable(Deprecated) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 8: Deprecated

Id	Guid	Key	Description	LongDescription	Modified	Deprecated	CodeTypeID	CodeTypeGUID
169654	1f2a621c-b427-4aea-b66a-03437d3217bd	False	Not deprecated		2017-02-27T14:50:59.863	FALSE	1496	078d2a29-4f05-44a7-b115-743b2ed7ee4c
169655	0e88dd12-78b0-4ec6-a3e1-2178e0b22f2a	True	Deprecated		2017-02-27T14:50:38.56	FALSE	1496	078d2a29-4f05-44a7-b115-743b2ed7ee4c

Habitat

THe habitat database is managed by EIONET Habitats directive Art. 17 reporting in year 2018.

SQL code to create tables

-- this is a new referential

DROP TABLE IF EXISTS ref.tr_habitat_hab;
CREATE TABLE ref.tr_habitat_hab (
hab_code TEXT PRIMARY KEY,
hab_description TEXT,
hab_definition TEXT,
hab_icesvalue character varying(4),  
hab_icesguid uuid,
hab_icestablesource text);

ALTER TABLE ref.tr_habitat_hab OWNER TO diaspara_admin;
GRANT SELECT ON ref.tr_habitat_hab  TO diaspara_read;


COMMENT ON TABLE ref.tr_habitat_hab IS 'Table of habitats from EIONET Habitats directive Art. 17 reporting in year 2018.';
COMMENT ON COLUMN ref.tr_habitat_hab.hab_code IS 'Code for habitat';
COMMENT ON COLUMN ref.tr_habitat_hab.hab_description IS 'Description of the habitat';
COMMENT ON COLUMN ref.tr_habitat_hab.hab_definition IS 'Definition of the habitat';
COMMENT ON COLUMN ref.tr_habitat_hab.hab_icesvalue IS 'Code for the habitat in the ICES database';
COMMENT ON COLUMN ref.tr_habitat_hab.hab_icesguid IS 'GUID in the ICES database';
COMMENT ON COLUMN ref.tr_habitat_hab.hab_icestablesource IS 'Source table in ICES vocab';

ICES / DIASPARA is this enough ?

The idea was to have estuaries and lagoons, but rivers are associated with vegetation, So I’m not sure if this vocab is enough ?

Code to import habitat from eionet.

# Load required libraries
library(XML)

# Parse the XML file downloaded from https://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/codelist
xml_file <- "R/data/habitats.xml"  # Replace with your actual file path
#file.exists("R/data/habitats.xml")
xml_data <- xmlParse(xml_file)

# Extract all <value> nodes
values <- getNodeSet(xml_data, "//value")

# Extract relevant data into a data frame
extract_info <- function(node) {
  id <- xmlGetAttr(node, "id")
  code <- gsub("http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/", x= id,replacement = "")
  label <- xmlValue(node[["label"]])
  defintion <- xmlValue(node[["defintion"]])
  status_node <- getNodeSet(node, ".//status/label")[[1]]
  status <- xmlValue(status_node)
  return(data.frame(id = id, code = code, label = label,status = status, stringsAsFactors = FALSE))
}

habitat <- do.call(rbind, lapply(values, extract_info))
habitat <- habitat[c(1:32,77:84,125:142),]

# Save the data frame as an RData file
save(habitat, file = "habitat_art_17_2018.RData")
# load(file = "habitat_art_17_2018.RData")
dbExecute(con_diaspara, 'DELETE FROM ref.tr_habitat_hab;')

dbWriteTable(con_diaspara, "habitat_temp", habitat)

dbExecute(con_diaspara, 'INSERT INTO "ref".tr_habitat_hab SELECT code, label, id, NULL, NULL, NULL FROM habitat_temp;') #58
dbExecute(con_diaspara, "DROP TABLE habitat_temp")#0

Code to show habitat table (to be imported in ICES vocab).

habitat <- dbGetQuery(con_diaspara, "SELECT * FROM ref.tr_habitat_hab;")
knitr::kable(habitat) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 9: Habitat table ?

hab_code	hab_description	hab_definition	hab_icesvalue	hab_icesguid	hab_icestablesource
HabAll	All habitats	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabAll	NA	NA	NA
HabBenAbyssal	Abyssal	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenAbyssal	NA	NA	NA
HabBenAll	All benthic habitats	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenAll	NA	NA	NA
HabBenBathyalLowRock	Lower bathyal rock and biogenic reef	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenBathyalLowRock	NA	NA	NA
HabBenBathyalLowSed	Lower bathyal sediment	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenBathyalLowSed	NA	NA	NA
HabBenBathyalUpReef	Upper bathyal rock and biogenic reef	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenBathyalUpReef	NA	NA	NA
HabBenBathyalUpSed	Upper bathyal sediment	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenBathyalUpSed	NA	NA	NA
HabBenCircalitCoarSed	Circalittoral coarse sediment	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenCircalitCoarSed	NA	NA	NA
HabBenCircalitMud	Circalittoral mud	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenCircalitMud	NA	NA	NA
HabBenCircalitMxdSed	Circalittoral mixed sediment	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenCircalitMxdSed	NA	NA	NA
HabBenCircalitRock	Circalittoral rock and biogenic reef	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenCircalitRock	NA	NA	NA
HabBenCircalitSand	Circalittoral sand	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenCircalitSand	NA	NA	NA
HabBenInfralitCoarSed	Infralittoral coarse sediment	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenInfralitCoarSed	NA	NA	NA
HabBenInfralitMud	Infralittoral mud	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenInfralitMud	NA	NA	NA
HabBenInfralitMxdSed	Infralittoral mixed sediment	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenInfralitMxdSed	NA	NA	NA
HabBenInfralitRock	Infralittoral rock and biogenic reef	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenInfralitRock	NA	NA	NA
HabBenInfralitSand	Infralittoral sand	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenInfralitSand	NA	NA	NA
HabBenLitRock	Littoral rock and biogenic reef	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenLitRock	NA	NA	NA
HabBenLitSed	Littoral sediment	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenLitSed	NA	NA	NA
HabBenOffshCoarSed	Offshore circalittoral coarse sediment	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenOffshCoarSed	NA	NA	NA
HabBenOffshMud	Offshore circalittoral mud	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenOffshMud	NA	NA	NA
HabBenOffshMxdSed	Offshore circalittoral mixed sediment	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenOffshMxdSed	NA	NA	NA
HabBenOffshRock	Offshore circalittoral rock and biogenic reef	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenOffshRock	NA	NA	NA
HabBenOffshSand	Offshore circalittoral sand	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenOffshSand	NA	NA	NA
HabBenOther	Other benthic habitats	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabBenOther	NA	NA	NA
HabOther	Other habitats	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabOther	NA	NA	NA
HabPelagAll	All pelagic habitats	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabPelagAll	NA	NA	NA
HabPelagCoastal	Coastal	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabPelagCoastal	NA	NA	NA
HabPelagOcean	Oceanic/beyond shelf	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabPelagOcean	NA	NA	NA
HabPelagOther	Other pelagic habitats	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabPelagOther	NA	NA	NA
HabPelagShelf	Shelf	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabPelagShelf	NA	NA	NA
HabPelagVarSalinity	Variable salinity	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/HabPelagVarSalinity	NA	NA	NA
1110	Sandbanks which are slightly covered by sea water all the time	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/1110	NA	NA	NA
1120	Posidonia beds (Posidonion oceanicae)	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/1120	NA	NA	NA
1130	Estuaries	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/1130	NA	NA	NA
1140	Mudflats and sandflats not covered by seawater at low tide	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/1140	NA	NA	NA
1150	Coastal lagoons	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/1150	NA	NA	NA
1160	Large shallow inlets and bays	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/1160	NA	NA	NA
1170	Reefs	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/1170	NA	NA	NA
1180	Submarine structures made by leaking gases	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/1180	NA	NA	NA
3110	Oligotrophic waters containing very few minerals of sandy plains (Littorelletalia uniflorae)	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3110	NA	NA	NA
3120	Oligotrophic waters containing very few minerals generally on sandy soils of the West Mediterranean, with Isoetes spp.	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3120	NA	NA	NA
3130	Oligotrophic to mesotrophic standing waters with vegetation of the Littorelletea uniflorae and/or of the Iso�to-Nanojuncetea	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3130	NA	NA	NA
3140	Hard oligo-mesotrophic waters with benthic vegetation of Chara spp.	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3140	NA	NA	NA
3150	Natural eutrophic lakes with Magnopotamion or Hydrocharition - type vegetation	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3150	NA	NA	NA
3160	Natural dystrophic lakes and ponds	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3160	NA	NA	NA
3170	Mediterranean temporary ponds	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3170	NA	NA	NA
3180	Turloughs	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3180	NA	NA	NA
3190	Lakes of gypsum karst	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3190	NA	NA	NA
3210	Fennoscandian natural rivers	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3210	NA	NA	NA
3220	Alpine rivers and the herbaceous vegetation along their banks	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3220	NA	NA	NA
3230	Alpine rivers and their ligneous vegetation with Myricaria germanica	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3230	NA	NA	NA
3240	Alpine rivers and their ligneous vegetation with Salix elaeagnos	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3240	NA	NA	NA
3250	Constantly flowing Mediterranean rivers with Glaucium flavum	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3250	NA	NA	NA
3260	Water courses of plain to montane levels with the Ranunculion fluitantis and Callitricho-Batrachion vegetation	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3260	NA	NA	NA
3270	Rivers with muddy banks with Chenopodion rubri p.p. and Bidention p.p. vegetation	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3270	NA	NA	NA
3280	Constantly flowing Mediterranean rivers with Paspalo-Agrostidion species and hanging curtains of Salix and Populus alba	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3280	NA	NA	NA
3290	Intermittently flowing Mediterranean rivers of the Paspalo-Agrostidion	http://dd.eionet.europa.eu/vocabulary/art17_2018/habitats/3290	NA	NA	NA

Fishway type ref.tr_fishway_fiw

SQL code to create tables

-- this is a new referential

DROP TABLE IF EXISTS ref.tr_fishway_fiw;
CREATE TABLE ref.tr_fishway_fiw (
fiw_code TEXT PRIMARY KEY,
fiw_description TEXT,
fiw_definition TEXT,
fiw_icesvalue character varying(4),  
fiw_icesguid uuid,
fiw_icestablesource text);
DELETE FROM ref.tr_fishway_fiw;
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('VS', 
'Vertical slot fishway', 
'Vertical Slot Fishways have top-to-bottom opening (slot) in the cross-wall by which water flows between pools, they are adapted to wide variations in water level.');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('PO', 
'Pool', 
'TODO');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('FL', 
'Fish lock', 
'TODO');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('D', 
'Denil pass', 
'TODO');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('RR', 
'Rock ramp', 
'TODO');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('ER', 
'Eel ramp', 
'TODO');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('LA', 
'Lateral canal', 
'TODO');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('AR', 
'Artificial river', 
'TODO');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('UN', 
'Unknown', 
'TODO');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('S', 
'Sluice', 
'TODO');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('L', 
'Fish lift', 
'TODO');


ALTER TABLE ref.tr_fishway_fiw OWNER TO diaspara_admin;
GRANT SELECT ON ref.tr_fishway_fiw  TO diaspara_read;


COMMENT ON TABLE ref.tr_fishway_fiw IS 'Table of fishway type';
COMMENT ON COLUMN ref.tr_fishway_fiw.fiw_code IS 'Code for fishway type';
COMMENT ON COLUMN ref.tr_fishway_fiw.fiw_description IS 'Description of the fishway';
COMMENT ON COLUMN ref.tr_fishway_fiw.fiw_definition IS 'Definition of the fishway';
COMMENT ON COLUMN ref.tr_fishway_fiw.fiw_icesvalue IS 'Code for the fishwat in the ICES database';
COMMENT ON COLUMN ref.tr_fishway_fiw.fiw_icesguid IS 'GUID in the ICES database';

Code to show fishway table (to be imported in ICES vocab).

habitat <- dbGetQuery(con_diaspara, "SELECT * FROM ref.tr_fishway_fiw;")
knitr::kable(habitat) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 10: Fishway vocab proposed to ICES.

fiw_code	fiw_description	fiw_definition	fiw_icesvalue	fiw_icesguid	fiw_icestablesource
VS	Vertical slot fishway	Vertical Slot Fishways have top-to-bottom opening (slot) in the cross-wall by which water flows between pools, they are adapted to wide variations in water level.	NA	NA	NA
PO	Pool	TODO	NA	NA	NA
FL	Fish lock	TODO	NA	NA	NA
D	Denil pass	TODO	NA	NA	NA
RR	Rock ramp	TODO	NA	NA	NA
ER	Eel ramp	TODO	NA	NA	NA
LA	Lateral canal	TODO	NA	NA	NA
AR	Artificial river	TODO	NA	NA	NA
UN	Unknown	TODO	NA	NA	NA
S	Sluice	TODO	NA	NA	NA
L	Fish lift	TODO	NA	NA	NA

Fish migration monitoring ref.tr_monitoring_mon

SQL code to create tables

-- this is a new referential

DROP TABLE IF EXISTS ref.tr_monitoring_mon;
CREATE TABLE ref.tr_monitoring_mon (
mon_code TEXT PRIMARY KEY,
mon_description TEXT,
mon_definition TEXT,
mon_icesvalue character varying(4),  
mon_icesguid uuid,
mon_icestablesource text);
DELETE FROM ref.tr_monitoring_mon;
INSERT INTO ref.tr_monitoring_mon (mon_code, mon_description, mon_definition)
VALUES('SO', 
'Sonar HF', 
'High frequency sonar used to monitor fish migration, e.g. Didson, Aris, Blueview, Occulus ....');
INSERT INTO ref.tr_monitoring_mon (mon_code, mon_description, mon_definition)
VALUES('TR', 
'Trap', 
'Trap used to catch a part or the whole run. Fish can be directed towards the trap with a system of grids,
or within a fishway. The fishes are counted and measured and then released, most often to continue their migration,
upstream or downstream from the trap.');
INSERT INTO ref.tr_monitoring_mon (mon_code, mon_description, mon_definition)
VALUES('VR', 
'Video recording', 
'Video recording of the fish used to make specific identification, and measure the length and direction of passage.
The video is often located in a narrow passage within a fishway, often also with light set in the background.');
INSERT INTO ref.tr_monitoring_mon (mon_code, mon_description, mon_definition)
VALUES('RC', 
'Resistivity counter', 
'Resistivity counters monitor the resistance between electrodes to detect the passage of a fish.
The lower resistance of the fish compared to the water is used to detect the passage. 
A series of submerged electrodes are used to detect the direction. Automatic
adjustment of the sensitivity of the counter ensures that the sizes into which fish are
classified remains consistent.');
INSERT INTO ref.tr_monitoring_mon (mon_code, mon_description, mon_definition)
VALUES('AC', 
'Acoustic receiver', 
'Acoustic receivers are use in fish tracking to detect and decode transmissions from acoustic fish tags');



ALTER TABLE ref.tr_monitoring_mon OWNER TO diaspara_admin;
GRANT SELECT ON ref.tr_monitoring_mon  TO diaspara_read;


COMMENT ON TABLE ref.tr_monitoring_mon IS 'Table of monitoring devices. 
A monitoring device is used to monitor fish passage. It can be attached to a fishway. 
A fishway can have several monitoring devices, e.g. a trap and a video recording. 
A monitoring device can also be placed without fishway , e.g. a sonar, an accoustic receiver.';
COMMENT ON COLUMN ref.tr_monitoring_mon.mon_code IS 'Code for monitoring type';
COMMENT ON COLUMN ref.tr_monitoring_mon.mon_description IS 'Description of the monitoring device';
COMMENT ON COLUMN ref.tr_monitoring_mon.mon_definition IS 'Definition of the monitoring device';
COMMENT ON COLUMN ref.tr_monitoring_mon.mon_icesvalue IS 'Code for the fishwat in the ICES database';
COMMENT ON COLUMN ref.tr_monitoring_mon.mon_icesguid IS 'GUID in the ICES database';

Code to show fishway table (to be imported in ICES vocab).

habitat <- dbGetQuery(con_diaspara, "SELECT * FROM ref.tr_monitoring_mon;")
knitr::kable(habitat) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 11: Fishway vocab proposed to ICES.

mon_code	mon_description	mon_definition	mon_icesvalue	mon_icesguid	mon_icestablesource
SO	Sonar HF	High frequency sonar used to monitor fish migration, e.g. Didson, Aris, Blueview, Occulus ....	NA	NA	NA
TR	Trap	Trap used to catch a part or the whole run. Fish can be directed towards the trap with a system of grids, or within a fishway. The fishes are counted and measured and then released, most often to continue their migration, upstream or downstream from the trap.	\|NA	\|NA	\|NA
VR	Video recording	Video recording of the fish used to make specific identification, and measure the length and direction of passage. The video is often located in a narrow passage within a fishway, often also with light set in the background.	\|NA	\|NA	\|NA
RC	Resistivity counter	Resistivity counters monitor the resistance between electrodes to detect the passage of a fish. The lower resistance of the fish compared to the water is used to detect the passage. A series of submerged electrodes are used to detect the direction. Automatic adjustment of the sensitivity of the counter ensures that the sizes into which fish are classified remains cons	stent. \|NA	\|NA	\|NA
AC	Acoustic receiver	Acoustic receivers are use in fish tracking to detect and decode transmissions from acoustic fish tags	NA	NA	NA

Fish Traits ref.tr_trait_tra

This one was formerly called metric, but metric ref.tr_metric_met already exist and is better suited to statistics (mean, sd … ). So we are using the word trait. Trait is refering to a species (there are different method, or different measurements for different species). The trait can be, either qualitative or quantitative. The trait is measured by a method, the measure (either categorical eg. sex = ‘Male’) or quantitative e.g. (length_mm = 150) might be accompanied by a method. For instance a fish is measured for length as fork length or total length, in the individual_trait table, the fish will be related to three columns, the code of the length parameter in the trait table, the quantitative value, and the method used.

SQL code to create table tr_trait_tra

-- DROP TABLE ref.tr_trait_tra CASCADE;

CREATE TABLE ref.tr_trait_tra (
  tra_id integer PRIMARY KEY,
  tra_code text NOT NULL,
  tra_description text NULL,
  tra_definition text,
  tra_icesvalue character varying(4),  
  tra_icesguid uuid,
  tra_wkg_code TEXT NOT NULL,  
  CONSTRAINT fk_tra_wkg_code  FOREIGN KEY (tra_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  tra_spe_code TEXT NOT NULL,  
  CONSTRAINT fk_tra_spe_code  FOREIGN KEY (tra_spe_code)
  REFERENCES ref.tr_species_spe(spe_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  tra_icestablesource text,
  tra_individualname text NULL,
  tra_groupname text NULL,
  CONSTRAINT uk_tra_groupname UNIQUE (tra_groupname),
  tra_uni_code varchar(20) NULL,
  tra_typemetric text NULL,
  CONSTRAINT ck_tra_typemetric CHECK (((tra_typemetric = 'Individual'::text) 
  OR (tra_typemetric = 'Group'::text) 
  OR (tra_typemetric = 'Both'::text))),
  tra_qualitativeornumeric TEXT,
  CONSTRAINT ck_tra_qualitativeornumeric CHECK ((tra_qualitativeornumeric = 'Qualitative'::text) 
  OR (tra_qualitativeornumeric = 'Numeric'::text)),
  CONSTRAINT uk_tra_individualname UNIQUE (tra_individualname),
  CONSTRAINT uk_tra_code UNIQUE (tra_code),
  CONSTRAINT fk_tra_uni_code FOREIGN KEY (tra_uni_code) 
  REFERENCES ref.tr_units_uni(uni_code) 
  ON UPDATE CASCADE ON DELETE CASCADE
);


COMMENT ON COLUMN ref.tr_trait_tra.tra_id IS 'Integer, id of the trait';
COMMENT ON COLUMN ref.tr_trait_tra.tra_code IS 'Name of the trait';
COMMENT ON COLUMN ref.tr_trait_tra.tra_description IS 'Description of the fish trait';
COMMENT ON COLUMN ref.tr_trait_tra.tra_definition IS 'Definition of the method used to obtain the metric';
COMMENT ON COLUMN ref.tr_trait_tra.tra_icesguid IS 'GUID in the ICES database';
COMMENT ON COLUMN ref.tr_trait_tra.tra_icestablesource IS 'Source table in ICES vocab';
COMMENT ON COLUMN ref.tr_trait_tra.tra_individualname IS 'Name of the metric used in individual metrics';
COMMENT ON COLUMN ref.tr_trait_tra.tra_groupname IS 'Name of the metric used in group metrics';
COMMENT ON COLUMN ref.tr_trait_tra.tra_uni_code IS 'Unit used, references tr_unit_uni';
COMMENT ON COLUMN ref.tr_trait_tra.tra_typemetric IS 'Is the metric a group metric (group), or individual metric (individual) or can be used in both tables (both) ?';
COMMENT ON COLUMN ref.tr_trait_tra.tra_qualitativeornumeric IS 'Indicate variable type, either Qualitative or Numeric';



GRANT ALL ON ref.tr_trait_tra TO diaspara_admin;
GRANT SELECT ON ref.tr_trait_tra TO diaspara_read;

Numeric fish trait (tr_traitnumeric_trn)

Trait can be either qualitative or quantitative (numeric). Both tables are inherited from tr_trait_tra.

SQL code to create table tr_traitnumeric_trn

-- DROP TABLE IF EXISTS ref.tr_traitnumeric_trn;
CREATE TABLE ref.tr_traitnumeric_trn(  
trn_minvalue NUMERIC,
trn_maxvalue NUMERIC,
  CONSTRAINT fk_tra_wkg_code  FOREIGN KEY (tra_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_tra_spe_code  FOREIGN KEY (tra_spe_code)
  REFERENCES ref.tr_species_spe(spe_code)
  ON UPDATE CASCADE ON DELETE RESTRICT, 
  CONSTRAINT fk_tra_uni_code FOREIGN KEY (tra_uni_code) 
  REFERENCES ref.tr_units_uni(uni_code)   ON UPDATE CASCADE ON DELETE CASCADE
) INHERITS  (ref.tr_trait_tra);

COMMENT ON COLUMN ref.tr_traitnumeric_trn.tra_id IS 'Integer, id of the trait';
COMMENT ON COLUMN ref.tr_traitnumeric_trn.tra_code IS 'Name of the trait';
COMMENT ON COLUMN ref.tr_traitnumeric_trn.tra_description IS 'Description of the fish trait';
COMMENT ON COLUMN ref.tr_traitnumeric_trn.tra_definition IS 'Definition of the method used to obtain the metric';
COMMENT ON COLUMN ref.tr_traitnumeric_trn.tra_icesguid IS 'GUID in the ICES database';
COMMENT ON COLUMN ref.tr_traitnumeric_trn.tra_icestablesource IS 'Source table in ICES vocab';
COMMENT ON COLUMN ref.tr_traitnumeric_trn.tra_individualname IS 'Name of the metric used in individual metrics';
COMMENT ON COLUMN ref.tr_traitnumeric_trn.tra_groupname IS 'Name of the metric used in group metrics';
COMMENT ON COLUMN ref.tr_traitnumeric_trn.tra_uni_code IS 'Unit used, references tr_unit_uni';
COMMENT ON COLUMN ref.tr_traitnumeric_trn.tra_typemetric IS 'Is the metric a group metric (group), or individual metric (individual) or can be used in both tables (both) ?';
COMMENT ON COLUMN ref.tr_traitnumeric_trn.trn_minvalue IS 'Minimum allowed value';
COMMENT ON COLUMN ref.tr_traitnumeric_trn.trn_maxvalue IS 'Maximum allowed value';


GRANT ALL ON ref.tr_traitnumeric_trn TO diaspara_admin;
GRANT SELECT ON ref.tr_traitnumeric_trn TO diaspara_read;

Code to import trait table.

tra <- dbGetQuery(con_wgeel_distant, "SELECT * FROM ref.tr_metrictype_mty ;")
# we will include group metric names later
tra <- tra[!grepl("mean", tra$mty_name),]
# we will also include method later
tra <- tra[!grepl("method", tra$mty_name),]
res <- dbGetQuery(con_diaspara, "SELECT * FROM ref.tr_trait_tra")
#clipr::write_clip(colnames(res))
tranum <- data.frame(
"tra_id" = tra$mty_id,
"tra_code" = tra$mty_name,
"tra_description" = tra$mty_description,
"tra_individualname" = ifelse(is.na(tra$mty_individual_name),tra$mty_name ,tra$mty_individual_name),
"tra_wkg_code" = 'WGEEL',
"Tra_spe_code" = 'ANG',
"tra_groupname" = tra$mty_name,
"tra_type" = tra$mty_type,
"tra_uni_code" = tra$mty_uni_code,
"tra_typemetric"=tra$mty_group,
"tra_qualitativeornumeric"='numeric')
tranum$tra_individualname[tranum$tra_typemetric=="group"] <- NA
tranum$tra_groupname[tranum$tra_typemetric=="individual"] <- NA
# the proportions will be numeric for group and qualitative for individual (
# e.g. evex presence)
tranum[tranum$tra_typemetric=="both" & grepl("proportion",tranum$tra_code),
c("tra_individualname")] <-NA
tranum[tranum$tra_typemetric=="both" & grepl("proportion",tranum$tra_code),
c("tra_typemetric")] <- "group"


dbWriteTable(con_diaspara_admin, "tr_traitnumeric_trn_temp", tranum)
dbExecute(con_diaspara_admin, "INSERT INTO ref.tr_traitnumeric_trn
SELECT 
tra_code,
tra_description,
tra_individualname,
tra_groupname,
tra_type,
tra_uni_code,
tra_group
 FROM tr_trait_tra_temp")
# TODO add group name

Qualitative fish trait (tr_traitqualitative_trq)

SQL code to create table tr_traitqualitative_trq

-- DROP TABLE IF EXISTS ref.tr_traitqualitative_trq;

CREATE TABLE ref.tr_traitqualitative_trq (
  CONSTRAINT fk_tra_wkg_code  FOREIGN KEY (tra_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_tra_spe_code  FOREIGN KEY (tra_spe_code)
  REFERENCES ref.tr_species_spe(spe_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_tra_uni_code FOREIGN KEY (tra_uni_code) 
  REFERENCES ref.tr_units_uni(uni_code)   ON UPDATE CASCADE ON DELETE CASCADE
  ) INHERITS (ref.tr_trait_tra);


COMMENT ON TABLE ref.tr_traitqualitative_trq IS 'Table of qualitative trait parameters';

GRANT ALL ON ref.tr_traitqualitative_trq TO diaspara_admin;
GRANT SELECT ON ref.tr_traitqualitative_trq TO diaspara_read;

Values of Qualitative fish trait (tr_traitqualvalue_tva)

The qualitative traits have values

SQL code to create table tr_traitqualvalue_tva

-- DROP TABLE "ref".tr_traitvaluequal_qal;

CREATE TABLE ref.tr_traitvaluequal_qal
  qal_id,
  qal_trq_code TEXT NOT NULL,
  CONSTRAINT fk_qal_trq_code 
  FOREIGN KEY (qal_trq_code)
  REFERENCES ref.tr_tratqualitative_trq(trq_code)
  ON UPDATE CASCADE ON DELETE CASCADE,
  qal_code text NOT NULL,
  qal_description text NULL,
  qal_definition text,
  qal_icesvalue character varying(4),  
  qal_icesguid uuid,
  qal_icestablesource text,
  CONSTRAINT uk_trm_code UNIQUE (qal_code)
);


COMMENT ON COLUMN ref.tr_tr_traitvaluequal_qal.qal_id IS 'Integer, id of the qualitative used';
COMMENT ON COLUMN ref.tr_tr_traitvaluequal_qal.qal_code IS 'Code of the qualitative traitvalue';
COMMENT ON COLUMN ref.tr_tr_traitvaluequal_qal.qal_description IS 'Description of the method';
COMMENT ON COLUMN ref.tr_tr_traitvaluequal_qal.qal_definition IS 'Definition of the method used to obtain the metric';
COMMENT ON COLUMN ref.tr_tr_traitvaluequal_qal.qal_icesguid IS 'GUID in the ICES database';
COMMENT ON COLUMN ref.tr_tr_traitvaluequal_qal.qal_icestablesource IS 'Source table in ICES vocab';



GRANT ALL ON ref.tr_tr_traitvaluequal_qal TO diaspara_admin;
GRANT SELECT ON ref.tr_tr_traitvaluequal_qal TO diaspara_read;

Code to show trait table.

trait <- dbGetQuery(con_diaspara, "SELECT * FROM ref.tr_trait_tra;")
knitr::kable(trait) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 12: Trait table …

tra_id	tra_code	tra_description	tra_definition	tra_icesvalue	tra_icesguid	tra_wkg_code	tra_spe_code	tra_icestablesource	tra_individualname	tra_groupname	tra_uni_code	tra_typemetric	tra_qualitativeornumeric
NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA
------:	:--------	:---------------	:--------------	:-------------	:------------	:------------	:------------	:-------------------	:------------------	:-------------	:------------	:--------------	:------------------------

Fish trait measurement method

Currently in the wgeel database, the metrics table value contains one column for value which can store numeric or integer. When it is integer, then, the metric id (mty_id) refers either to a category (e.g sex, or is differenciated), or a method (sexed using size, sexed after gonadal inspection). In the new database, the method will be stored in a different column in the group or individual metrics. This table references the methods proposed.

SQL code to create tables

-- DROP TABLE ref.tr_traitmethod_trm;

CREATE TABLE ref.tr_traitmethod_trm (
  trm_id integer PRIMARY KEY,
  trm_code text NOT NULL,
  trm_wkg_code TEXT NOT NULL,  
  CONSTRAINT fk_trm_wkg_code  FOREIGN KEY (trm_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  trm_spe_code TEXT NOT NULL,  
  CONSTRAINT fk_trm_spe_code  FOREIGN KEY (trm_spe_code)
  REFERENCES ref.tr_species_spe(spe_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  trm_description text NULL,
  trm_definition text,
  trm_icesvalue character varying(4),  
  trm_icesguid uuid,
  trm_icestablesource text,
  CONSTRAINT uk_trm_code UNIQUE (trm_code)
);


COMMENT ON TABLE ref.tr_traitmethod_trm IS 'Table of method used to obtain a trait metric';
COMMENT ON COLUMN ref.tr_traitmethod_trm.trm_id IS 'Integer, id of the method used';
COMMENT ON COLUMN ref.tr_traitmethod_trm.trm_code IS 'Name of the method used';
COMMENT ON COLUMN ref.tr_traitmethod_trm.trm_wkg_code IS 'Working group code';
COMMENT ON COLUMN ref.tr_traitmethod_trm.trm_spe_code IS 'Species code';
COMMENT ON COLUMN ref.tr_traitmethod_trm.trm_description IS 'Description of the method';
COMMENT ON COLUMN ref.tr_traitmethod_trm.trm_definition IS 'Definition of the method used to obtain the metric';
COMMENT ON COLUMN ref.tr_traitmethod_trm.trm_icesguid IS 'GUID in the ICES database';
COMMENT ON COLUMN ref.tr_traitmethod_trm.trm_icestablesource IS 'Source table in ICES vocab';


GRANT ALL ON ref.tr_traitmethod_trm TO diaspara_admin;
GRANT SELECT ON ref.tr_traitmethod_trm TO diaspara_read;

Code to import traitmethod table.

tra <- dbGetQuery(con_wgeel_distant, "SELECT * FROM ref.tr_metrictype_mty ;")
tra <- tra[grepl("method", tra$mty_name),]
res <- dbGetQuery(con_diaspara, "SELECT * FROM ref.tr_traitmethod_trm")
clipr::write_clip(colnames(res))
tr_trait_tra_temp <- data.frame(
"trm_id" = tra$mty_id,
"trm_code" = tra$mty_name,
"trm_description" = tra$mty_description,
)
tr_traitmethod_trm_temp$trm_individualname[tr_trait_trm_temp$trm_group=="group"] <- NA
tr_traitmethod_trm_temp$trm_groupname[tr_trait_trm_temp$trm_group=="individual"] <- NA
dbWriteTable(con_diaspara_admin, "tr_traitmethod_trm_temp",tr_traitmethod_trm_temp)
dbExecute(con_diaspara_admin, "INSERT INTO ref.tr_traitmethod_trm 
SELECT 
trm_code,
trm_description,
 FROM tr_trait_trm_temp")
# TODO add group name

Code to show trait measurement method table.

trm <- dbGetQuery(con_diaspara, "SELECT * FROM ref.tr_traitmethod_trm;")
knitr::kable(trm) %>% kable_styling(bootstrap_options = c("striped", "hover", "condensed"))

Table 13: Trait table, check how this will be imported …

trm_id	trm_code	trm_wkg_code	trm_spe_code	trm_description	trm_definition	trm_icesvalue	trm_icesguid	trm_icestablesource
NA	NA	NA	NA	NA	NA	NA	NA	NA
------:	:--------	:------------	:------------	:---------------	:--------------	:-------------	:------------	:-------------------

Creating the station table

Series will be linked to one or several stations.

The station are loaded into a table that will not be exactly like ICES’s since I cannot load the full tables, and don’t want to replicate the full complexity of stations. For instance currently station 1000 has several parents for “Station_DTYPE” which I agregate like EW_EUNU~CW, in my table. But the vocab is here.

SQL code to create tables


/*
 * 
 * Note since the api retrieves a one to many relationship
 * in the foreign tables I need to drop foreign keys
 */

DROP TABLE IF EXISTS "ref"."StationDictionary" CASCADE;
CREATE TABLE "ref"."StationDictionary"(
"Definition" TEXT NOT NULL DEFAULT 'Station',
"HeaderRecord" TEXT NOT NULL DEFAULT 'Record',
"Station_Code" INTEGER PRIMARY KEY,
"Station_Country" TEXT NOT NULL,
--CONSTRAINT  fk_station_country 
--FOREIGN KEY ("Station_Country")
--REFERENCES ref.tr_country_cou(cou_code)
--ON UPDATE CASCADE ON DELETE CASCADE, -- possible problem here AS I don't have ALL the countries...
"Station_Name" TEXT NOT NULL,
"Station_LongName" TEXT NULL,
"Station_ActiveFromDate" CHARACTER VARYING (10) NULL, -- CHANGE I don't have that FROM the API
"Station_ActiveUntilDate" CHARACTER VARYING (10) NULL,
"Station_ProgramGovernance" TEXT  NULL,
--CONSTRAINT fk_station_programgovernance
--FOREIGN KEY ("Station_ProgramGovernance") 
--REFERENCES "ref"."PRGOV"("Key") 
--ON UPDATE CASCADE ON DELETE CASCADE,
"Station_StationGovernance" TEXT  NULL,
--CONSTRAINT fk_station_stationgovernance 
--FOREIGN KEY ("Station_StationGovernance") 
--REFERENCES "ref"."EDMO"("Key") 
--ON UPDATE CASCADE ON DELETE CASCADE,
"Station_PURPM" TEXT NULL, -- FAILS WITH NOT NULL
--CONSTRAINT fk_station
--FOREIGN KEY ("Station_PURPM") 
--REFERENCES "ref"."PURPM"("Key")
--ON UPDATE CASCADE ON DELETE CASCADE,
"Station_Latitude" NUMERIC NULL,
"Station_LatitudeRange" NUMERIC NULL,
"Station_Longitude" NUMERIC NULL,
"Station_LongitudeRange" NUMERIC NULL,
"Station_Geometry" geometry,
--CONSTRAINT ck_geom_or_latlon 
--CHECK ("Station_Geometry" IS NOT NULL 
--OR "Station_Latitude" IS NOT NULL
--OR "Station_LatitudeRange" IS NOT NULL),
"Station_DataType" TEXT NULL,
--CONSTRAINT fk_station_datatype 
--FOREIGN KEY ("Station_DataType") REFERENCES
--"ref"."Station_DTYPE"("Key")
--ON UPDATE CASCADE ON DELETE CASCADE,
"Station_WLTYP" TEXT,
--CONSTRAINT fk_station_wltype 
--FOREIGN KEY ("Station_WLTYP") 
--REFERENCES "ref"."WLTYP"("Key")
--ON UPDATE CASCADE ON DELETE CASCADE,
"Station_MSTAT" TEXT,
-- CONSTRAINT fk_station_msat
--FOREIGN KEY ("Station_MSTAT") 
--REFERENCES "ref"."MSTAT"("Key")
--ON UPDATE CASCADE ON DELETE CASCADE,
"Station_Notes" TEXT,
"Station_Deprecated" TEXT,
FOREIGN KEY ("Station_Deprecated") 
REFERENCES "ref"."Deprecated"("Key")
ON UPDATE CASCADE ON DELETE CASCADE
);

-- Not sure I'll ever need that one .....
DROP TABLE IF EXISTS "ref"."Relation";
CREATE TABLE "ref"."Relation"(
"Definiton" TEXT NOT NULL DEFAULT 'Relation',
"HeaderRecord" TEXT  NOT NULL DEFAULT 'Record',
"Relation_Code" INTEGER NOT NULL,
CONSTRAINT fk_relation_code FOREIGN KEY ("Relation_Code")
REFERENCES "ref"."StationDictionary"("Station_Code") 
ON UPDATE CASCADE ON DELETE CASCADE,
"Relation_Country" TEXT NOT NULL,
CONSTRAINT fk_relation_country 
 FOREIGN KEY ("Relation_Country")
REFERENCES ref.tr_country_cou(cou_code)
ON UPDATE CASCADE ON DELETE CASCADE,
"Relation_Name" CHARACTER VARYING(50) NOT NULL,
"Relation_ActiveFromDate" CHARACTER VARYING(10) NOT NULL,
"Relation_RelatedCode" INTEGER NOT NULL,
CONSTRAINT fk_relation_relatedCode 
FOREIGN KEY ("Relation_RelatedCode")
REFERENCES "ref"."StationDictionary"("Station_Code") 
ON UPDATE CASCADE ON DELETE CASCADE,
"Relation_RelatedCountry" TEXT NOT NULL,
CONSTRAINT fk_relation_related_country
FOREIGN KEY  ("Relation_RelatedCountry")
REFERENCES ref.tr_country_cou(cou_code)
ON UPDATE CASCADE ON DELETE CASCADE,
"Relation_RelatedName" CHARACTER VARYING(50) NOT NULL,
"Relation_RelatedActiveFromDate" CHARACTER VARYING (10) NOT NULL,
"Relation_RelationType" TEXT NOT NULL);



COMMENT ON TABLE ref."WLTYP" IS 'Water and land station type';
COMMENT ON TABLE ref."PRGOV" IS 'Program Governance referential';
COMMENT ON TABLE ref."PURPM" IS 'Purpose of monitoring';
COMMENT ON TABLE ref."MSTAT" IS 'Purpose of monitoring';
COMMENT ON TABLE ref."EDMO" IS 'European Directory of Marine Organisations (EDMO)';

We have to do some fixes see (issue 27)[https://github.com/ices-tools-prod/icesVocab/issues/27] and remove nearly all the constraints, including the length of 50 for stationNames. The following chunk creates a vocab with 14086 stations.

Code to import station

# tested 28/05/2025

library(icesStation)
system.time(
station <- getListStation())
u#tilisateur     syst�me      �coul� 
 #    268.09        9.06     1402.47 
save(station, file = "data/station.Rdata")
# load(file = "data/station.Rdata")
initcap <- function(X) paste0(substring(X,1,1),tolower(substring(X,2, length(X))))


station <- station[station$Station_Name != 'TestBulkUload1',]
station$Station_Deprecated<- initcap(as.character(station$Station_Deprecated))

dbWriteTable(con_diaspara_admin, "temp_station", station, overwrite = TRUE)
dbExecute(con_diaspara_admin,'DELETE FROM "ref"."StationDictionary"')
dbExecute(con_diaspara_admin,'INSERT INTO "ref"."StationDictionary"
("Definition", 
"HeaderRecord", 
"Station_Code",
 "Station_Country",
 "Station_Name",
 "Station_LongName", 
"Station_ActiveFromDate", 
"Station_ActiveUntilDate",
 "Station_ProgramGovernance",
 "Station_StationGovernance", 
"Station_PURPM",
 "Station_DataType",
 "Station_WLTYP",
 "Station_MSTAT", 
"Station_Notes", 
"Station_Deprecated")
SELECT
"Definition", 
"HeaderRecord", 
"Station_Code"::INTEGER,
 "Station_Country",
 "Station_Name",
 "Station_LongName", 
"Station_ActiveFromDate", 
"Station_ActiveUntilDate",
 "Station_ProgramGovernance",
 "Station_StationGovernance", 
"Station_PURPM",
 "Station_DataType",
 "Station_WLTYP",
 "Station_MSTAT", 
"Station_Notes", 
"Station_Deprecated" 
 FROM temp_station') #14086
 dbExecute(con_diaspara_admin, "DROP TABLE if exists temp_station")

Creating the version table refeel.tr_version_ver

SQL code to create tables

DROP TABLE IF EXISTS refeel.tr_version_ver CASCADE;
CREATE TABLE refeel.tr_version_ver(
CONSTRAINT ver_code_pkey PRIMARY KEY (ver_code),
 CONSTRAINT  fk_ver_spe_code FOREIGN KEY (ver_spe_code) 
REFERENCES ref.tr_species_spe(spe_code)
ON UPDATE CASCADE ON DELETE CASCADE,
) inherits (ref.tr_version_ver);

COMMENT ON TABLE refeel.tr_version_ver
IS 'Table of data or variable version, essentially one datacall or advice, inherits ref.tr_version_ver';
ALTER TABLE refeel.tr_version_ver ALTER COLUMN ver_wkg_code SET DEFAULT 'WGEEL';

COMMENT ON TABLE refeel.tr_version_ver
IS 'Table of data or variable version, essentially one datacall or advice, inherits ref.tr_version_ver';

COMMENT ON COLUMN refeel.tr_version_ver.ver_version 
IS 'Version code, wkg-year-version.';
COMMENT ON COLUMN refeel.tr_version_ver.ver_year 
IS 'Year of assessement.';
COMMENT ON COLUMN refeel.tr_version_ver.ver_spe_code 
IS 'Species code e.g. ANG references tr_species_spe.';
COMMENT ON COLUMN refeel.tr_version_ver.ver_wkg_code 
IS 'Code of the working group,  WGEEL';
COMMENT ON COLUMN refeel.tr_version_ver.ver_stockkeylabel 
IS 'Ver_stockkeylabel e.g. ele.2737.nea.';
COMMENT ON COLUMN refeel.tr_version_ver.ver_datacalldoi 
IS 'Data call DOI, find a way to retrieve that information 
and update this comment';
COMMENT ON COLUMN refeel.tr_version_ver.ver_version 
IS 'Version code in original database, eg 2,4 for wgnas, dc_2020 for wgeel.';
COMMENT ON COLUMN refeel.tr_version_ver.ver_description 
IS 'Description of the data call / version.';
GRANT ALL ON refeel.tr_version_ver TO diaspara_admin;
GRANT SELECT ON refeel.tr_version_ver TO diaspara_read;

This table is the same as in wgnas, it is inherited from ref.

Code to insert values into the tr_version_ver table

# get the latest version from the server
ver <- dbGetQuery(con_wgeel_distant, "select * from ref.tr_datasource_dts")
save(ver, file= "data/tr_datasource_dts.Rdata")
ver <- ver[ver$dts_datasource != 'test',]
dc <- ver[grepl("dc", ver$dts_datasource), "dts_datasource"]
dcyear <- as.integer(lapply(strsplit(dc,"_"), function(X)X[2]))
wgeel <- ver[grepl("wgeel", ver$dts_datasource), "dts_datasource"]
wgeelyear <- as.integer(lapply(strsplit(wgeel,"_"), function(X)X[2]))
tr_version_ver <- data.frame(
ver_code = paste0(rep("ANG-", 11),c(wgeelyear, dcyear, 2025),c("-1","-2",rep("-1", 9))),
ver_year = c(wgeelyear, dcyear, 2025),
ver_spe_code = "ANG",
ver_datacalldoi=c(rep(NA, 9), 
"https://doi.org/10.17895/ices.pub.25816738.v2",
"https://doi.org/10.17895/ices.pub.25816738.v2"), 
ver_stockkeylabel =c("ele"), # sugested by Hilaire. 
# TODO FIND other DOI (mail sent to ICES)
ver_version=c(1,2,rep(1,9)), # TODO WGNAS check that there is just one version per year
ver_description=ver$dts_description) # TODO WGNAS provide model description

DBI::dbWriteTable(con_diaspara_admin, "temp_tr_version_ver", tr_version_ver, 
overwrite = TRUE)
dbExecute(con_diaspara_admin, "INSERT INTO refeel.tr_version_ver SELECT * FROM temp_tr_version_ver;") # 5
 DBI::dbExecute(con_diaspara_admin, "DROP TABLE temp_tr_version_ver;")


# TODO eel and wgbast
#"ele.2737.nea","sal.27.22–31",

Table 14: Version

ver_code	ver_year	ver_spe_code	ver_stockkeylabel	ver_datacalldoi	ver_version	ver_description	ver_wkg_code
WGEEL-2016-1	2016	ANG	NA	ele	1	Joint ICES, EIFAAC and GFCM Data Call: Data submission for advice for European eel under WGEEL – Part 1: 2017	WGEEL
WGEEL-2017-2	2017	ANG	NA	ele	2	Joint ICES, EIFAAC and GFCM Data Call: Data submission for advice for European eel under WGEEL – Part 2: 2018	WGEEL
WGEEL-2017-1	2017	ANG	NA	ele	1	Data provided by wgeel 2016	WGEEL
WGEEL-2018-1	2018	ANG	NA	ele	1	Data provided by wgeel 2017	WGEEL
WGEEL-2019-1	2019	ANG	NA	ele	1	Joint EIFAAC/GFCM/ICES Eel Data Call 2019	WGEEL
WGEEL-2020-1	2020	ANG	NA	ele	1	Joint EIFAAC/GFCM/ICES Eel Data Call 2020	WGEEL
WGEEL-2021-1	2021	ANG	NA	ele	1	Joint EIFAAC/GFCM/ICES Eel Data Call 2021	WGEEL
WGEEL-2022-1	2022	ANG	NA	ele	1	Joint EIFAAC/GFCM/ICES Eel Data Call 2022	WGEEL
WGEEL-2023-1	2023	ANG	NA	ele	1	Joint EIFAAC/GFCM/ICES Eel Data Call 2023	WGEEL
WGEEL-2024-1	2024	ANG	https://doi.org/10.17895/ices.pub.25816738.v2	ele	1	Joint EIFAAC/GFCM/ICES Eel Data Call 2024	WGEEL
WGEEL-2025-1	2025	ANG	https://doi.org/10.17895/ices.pub.25816738.v2	ele	1	WKEMP 2025 special request	WGEEL

series table t_series_ser

Creating series main table dat.t_series_ser

SQL code to create tables

--DROP TABLE IF EXISTS dat.t_series_ser CASCADE;

CREATE TABLE dat.t_series_ser (
  ser_id uuid PRIMARY KEY,
  ser_code text NOT NULL,
  CONSTRAINT uk_ser_code UNIQUE (ser_code),
  ser_name TEXT NOT NULL,
  --CONSTRAINT uk_ser_name UNIQUE (ser_name),
  ser_spe_code TEXT NULL,
  CONSTRAINT fk_ser_spe_code FOREIGN KEY (ser_spe_code) 
  REFERENCES "ref".tr_species_spe(spe_code) 
  ON UPDATE CASCADE ON DELETE RESTRICT ,
  ser_lfs_code TEXT NULL,
  CONSTRAINT fk_ser_lfs_code_ser_spe_code FOREIGN KEY (ser_lfs_code, ser_spe_code)
  REFERENCES "ref".tr_lifestage_lfs (lfs_code, lfs_spe_code) 
  ON UPDATE CASCADE ON DELETE RESTRICT,  
  ser_are_code TEXT NOT NULL,
  CONSTRAINT fk_ser_are_code FOREIGN KEY (ser_are_code)
  REFERENCES "ref".tr_area_are (are_code) 
  ON UPDATE CASCADE ON DELETE RESTRICT,
  ser_wkg_code TEXT NOT NULL,  
  CONSTRAINT fk_ser_wkg_code  FOREIGN KEY (ser_wkg_code)
  REFERENCES "ref".tr_icworkinggroup_wkg(wkg_code),
  ser_ver_code TEXT NOT NULL,
  CONSTRAINT fk_ser_ver_code FOREIGN KEY (ser_ver_code)
  REFERENCES ref.tr_version_ver(ver_code),   
  ser_cou_code TEXT NOT NULL,
  CONSTRAINT fk_ser_cou_code FOREIGN KEY (ser_cou_code)
  REFERENCES ref.tr_country_cou(cou_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_ser_wltyp_code FOREIGN KEY( ser_wltyp_code)
  REFERENCES ref."WLTYP"("Key") 
  ON UPDATE CASCADE ON DELETE RESTRICT,
  ser_hab_code TEXT NULL,
  CONSTRAINT fk_ser_habitat_code FOREIGN KEY(ser_hab_code)
  REFERENCES ref.tr_habitat_hab (hab_code)
  ON UPDATE CASCADE ON DELETE RESTRICT, 
  ser_gea_code TEXT NULL,
  CONSTRAINT fk_ser_gea_code FOREIGN KEY (ser_gea_code)
  REFERENCES ref.tr_gear_gea(gea_code)
  ON UPDATE CASCADE ON DELETE CASCADE,  
  ser_fiw_code TEXT,
  CONSTRAINT fk_ser_fiw_code FOREIGN KEY (ser_fiw_code)
  REFERENCES ref.tr_fishway_fiw(fiw_code)
  ON UPDATE CASCADE ON DELETE CASCADE,  
  ser_mon_code TEXT,
  CONSTRAINT fk_ser_mon_code FOREIGN KEY (ser_mon_code)
  REFERENCES ref.tr_monitoring_mon(mon_code)
  ON UPDATE CASCADE ON DELETE CASCADE,  
  ser_uni_code varchar(20), 
  CONSTRAINT fk_ser_uni_code FOREIGN KEY (ser_uni_code)
  REFERENCES ref.tr_units_uni(uni_code)
  ON DELETE CASCADE ON UPDATE CASCADE,
  ser_effort_uni_code varchar(20),
  CONSTRAINT fk_ser_effort_uni_code FOREIGN KEY (ser_effort_uni_code)
  REFERENCES ref.tr_units_uni(uni_code)
  ON DELETE CASCADE  ON UPDATE CASCADE,
  ser_description TEXT NULL,
  ser_locationdescription TEXT NULL,
  ser_wltyp_code TEXT NULL,
  ser_stocking boolean NULL,
  ser_stockingcomment TEXT NULL,
  ser_protocol TEXT NULL,
  ser_samplingstrategy TEXT NULL,  
  ser_datarightsholder TEXT NULL,
  ser_datelastupdate DATE NOT NULL,
  geom geometry NULL);

COMMENT ON TABLE dat.t_series_ser IS 'Table of time series, or sampling data identifier. This corresponds to a multi-annual data collection design.
It can correspond to time series data or individual metrics collection or both. This table is inherited. It means that the data in ref is fed by
the content of the tables in refeel, refnas, refbast... Note this table is joined to  StationDictionary, which contains elements about monitoring purpose (PURMP), stationGovernance, ProgramGovernance, 
station_activefromdate, stationactiveuntildate, latitude, latituderange, longitude, longituderange, MSAT. Check that the table content is consistent';
COMMENT ON COLUMN dat.t_series_ser.ser_id IS 'UUID, identifier of the series, primary key';
COMMENT ON COLUMN dat.t_series_ser.ser_code IS 'Code of the series';
COMMENT ON COLUMN dat.t_series_ser.ser_name IS 'Name of the series';
COMMENT ON COLUMN dat.t_series_ser.ser_spe_code  IS 'Species, one of SAL, ELE, TRT, ALA, ALF, SLP, RLP  ... references ref.tr_species_spe, the species can be null but
it should correspond to the main species target by the sampling';
COMMENT ON COLUMN dat.t_series_ser.ser_lfs_code  IS 'Life stage see tr_lifestage_lfs,Code of the lifestage see tr_lifestage_lfs,  the constraint is set on 
both lfs_code, and lfs_spe_code (as two species can have the same lifestage code. The lifestage can be NULL but it should correspond to the main lifestage targeted by the series;';
COMMENT ON COLUMN dat.t_series_ser.ser_wkg_code IS 'Code of the working group, one of WGBAST, WGEEL, WGNAS, WKTRUTTA';
COMMENT ON COLUMN dat.t_series_ser.ser_ver_code IS 'Version code referencing tr_version_ver the data call e.g. NAS_2025dc_2020, wgeel_2016, wkemp_2025';
COMMENT ON COLUMN dat.t_series_ser.ser_cou_code IS 'Code of the country';
COMMENT ON COLUMN dat.t_series_ser.ser_wltyp_code IS 'Code of the habitat type, one of MO (marine open), MC (Marine coastal), T (Transitional water), FW (Freshwater), null accepted';
COMMENT ON COLUMN dat.t_series_ser.ser_hab_code IS 'Code of the habitat, see tr_habitat_hab';
COMMENT ON COLUMN dat.t_series_ser.ser_are_code IS 'Code of the area, areas are geographical sector most often corresponding to stock units, see tr_area_are.';
COMMENT ON COLUMN dat.t_series_ser.ser_uni_code IS 'Annual value Unit, references table tr_unit_uni.';
COMMENT ON COLUMN dat.t_series_ser.ser_effort_uni_code IS 'Annual data collection effort unit code, references table tr_unit_uni.';
COMMENT ON COLUMN dat.t_series_ser.ser_description IS 'Sem description should comply with column svc_description in the vocabulary. Quick concise description of the series. Should include species, stage targeted, location and gear. e.g. Glass eel monitoring in the Vilaine estuary (France) with a trapping ladder.';
COMMENT ON COLUMN dat.t_series_ser.ser_locationdescription IS 'This should provide a description of the site, e.g. if ist far inland, in the middle of a river, near a dam etc. Also please specify the adjectant marine region (Baltic, North Sea) etc.
(e.g.  "Bresle river trap 3 km from the sea" or IYFS/IBTS sampling in the Skagerrak-Kattegat"';
COMMENT ON COLUMN dat.t_series_ser.ser_gea_code IS 'Code of the gear used, see tr_gear_gea';
COMMENT ON COLUMN dat.t_series_ser.ser_fiw_code IS 'Code the fishway, eg PO for pool type fishway';
COMMENT ON COLUMN dat.t_series_ser.ser_mon_code IS 'Code the Monitoring device, eg SO for Sonar';
COMMENT ON COLUMN dat.t_series_ser.ser_stocking IS 'Boolean, Is there restocking (for eel) or artifical reproduction in the river / basin, affecting the series ? ';
COMMENT ON COLUMN dat.t_series_ser.ser_stockingcomment IS 'Comment on stocking';
COMMENT ON COLUMN dat.t_series_ser.ser_protocol IS 'Describe sampling protocol';
COMMENT ON COLUMN dat.t_series_ser.ser_samplingstrategy IS 'Describe sampling strategy';
COMMENT ON COLUMN dat.t_series_ser.ser_datarightsholder IS 'Code of the data rights holder of the series, this field will be used in DATSU to acknowledge the source of data';
COMMENT ON COLUMN dat.t_series_ser.ser_datelastupdate IS 'Last modification in the series, from a trigger';
COMMENT ON COLUMN dat.t_series_ser.geom IS 'Series geometry column EPSG 4326, can be more detailed than the geometry for station';


GRANT ALL ON dat.t_series_ser TO diaspara_admin;
GRANT SELECT ON dat.t_series_ser TO diaspara_read;

This table is inherited so the table created in dat will be empty, and will only receive data by inheritance. It can be considered as a view for ICES (no inheritance in SQL server).

Since this table is inherited the wkg needs to be included. One table will be created per working group. These tables will be collated together. Most details information about metadata will be in the metadata tables, either to describe annual sampling or to describe individual metric collection.

Monitoring stations (including fixed stations) that are used for recurring sampling or data collection are managed via the Station Code Request Application in ICES (ICES 2024). This table will reference monitoring station but it might be NULL, as some sampling designs or data collection, for instance for the DCF, are not related to a station.

QUESTION ICES (Cédric via Teams meeting)

The ser_id might in some cases correspond to a fixed station but not only. For this reason the station is referenced here but another code will be created to reference the sampling collection. Is this OK ?

ANSWER ICES : Maria (20/05/2025)

Yes this makes sense, I would advise to use both the stations and a unique identifier at the top. Using UUID will help unicity there. It’s not clear if it needs to be a vocabulary. There is a procedure to integrate stations, but for this we will probably do a bulk import.

QUESTION to DIASPARA

I this that many of our series, and many of BAST or NAS would be related to fishways. Shouldn’t we need a fishway / fishway monitoring device vocab there ?

QUESTION to WKEELDATA

Currently everything is in the same table, but it could be split by period following exactly the station work. A description would then spans a limited amount of year from date_begin to date_end. If the series description still hold the date_end is null. If so we need to decide what fields could change over time (if any)

Creating inherited table dateel.t_series_ser

SQL code to create tables

DROP TABLE IF EXISTS dateel.t_series_ser;
CREATE TABLE dateel.t_series_ser ( 
  CONSTRAINT pk_ser_id PRIMARY KEY (ser_id),
  CONSTRAINT uk_ser_code UNIQUE (ser_code),
  CONSTRAINT uk_ser_name UNIQUE (ser_name),
  CONSTRAINT fk_ser_spe_code FOREIGN KEY (ser_spe_code) 
    REFERENCES ref.tr_species_spe(spe_code) 
    ON UPDATE CASCADE ON DELETE RESTRICT ,
  CONSTRAINT fk_ser_lfs_code_ser_spe_code FOREIGN KEY (ser_lfs_code, ser_spe_code)
    REFERENCES ref.tr_lifestage_lfs (lfs_code, lfs_spe_code) 
    ON UPDATE CASCADE ON DELETE RESTRICT,  
  CONSTRAINT fk_ser_are_code FOREIGN KEY (ser_are_code)
    REFERENCES ref.tr_area_are (are_code) 
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_ser_wkg_code  FOREIGN KEY (ser_wkg_code)
   REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
   ON UPDATE CASCADE ON DELETE RESTRICT,   
  CONSTRAINT fk_ser_ver_code FOREIGN KEY (ser_ver_code)
   REFERENCES refeel.tr_version_ver(ver_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,  
  CONSTRAINT fk_ser_cou_code FOREIGN KEY (ser_cou_code)
   REFERENCES ref.tr_country_cou(cou_code)
   ON UPDATE CASCADE ON DELETE RESTRICT,  
  CONSTRAINT fk_ser_uni_code FOREIGN KEY (ser_uni_code)
   REFERENCES ref.tr_units_uni(uni_code)
   ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_ser_effort_uni_code FOREIGN KEY (ser_effort_uni_code)
   REFERENCES ref.tr_units_uni(uni_code)
   ON DELETE CASCADE  ON UPDATE CASCADE,
  CONSTRAINT fk_ser_wltyp_code FOREIGN KEY( ser_wltyp_code)
   REFERENCES ref."WLTYP"("Key") 
   ON UPDATE CASCADE ON DELETE RESTRICT,  
  CONSTRAINT fk_ser_gea_code FOREIGN KEY (ser_gea_code)
   REFERENCES ref.tr_gear_gea(gea_code)
   ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_ser_fiw_code FOREIGN KEY (ser_fiw_code)
   REFERENCES ref.tr_fishway_fiw(fiw_code)
   ON UPDATE CASCADE ON DELETE CASCADE,  
  CONSTRAINT fk_ser_mon_code FOREIGN KEY (ser_mon_code)
   REFERENCES ref.tr_monitoring_mon(mon_code)
   ON UPDATE CASCADE ON DELETE CASCADE
) inherits (dat.t_series_ser);

-- In the wgeel schema the default is WGEEL
ALTER TABLE dateel.t_series_ser ALTER COLUMN ser_wkg_code SET DEFAULT 'WGEEL';  

COMMENT ON TABLE dateel.t_series_ser IS 'Table of time series, or sampling data identifier. This corresponds to a multi-annual data collection design.
It can correspond to time series data or individual metrics collection or both. This table is inherited from dat ';
COMMENT ON COLUMN dat.t_series_ser.ser_id IS 'UUID, identifier of the series, primary key';
COMMENT ON COLUMN dat.t_series_ser.ser_code IS 'Code of the series';
COMMENT ON COLUMN dat.t_series_ser.ser_name IS 'Name of the series';
COMMENT ON COLUMN dat.t_series_ser.ser_spe_code  IS 'Species, one of SAL, ELE, TRT, ALA, ALF, SLP, RLP  ... references ref.tr_species_spe, the species can be null but
it should correspond to the main species target by the sampling';
COMMENT ON COLUMN dat.t_series_ser.ser_lfs_code  IS 'Life stage see tr_lifestage_lfs,Code of the lifestage see tr_lifestage_lfs,  the constraint is set on 
both lfs_code, and lfs_spe_code (as two species can have the same lifestage code. The lifestage can be NULL but it should correspond to the main lifestage targeted by the series;';
COMMENT ON COLUMN dat.t_series_ser.ser_wkg_code IS 'Code of the working group, one of
WGBAST, WGEEL, WGNAS, WKTRUTTA';
COMMENT ON COLUMN dat.t_series_ser.ser_ver_code IS 'Version code sourced from ref.tr_version_ver the data call e.g. NAS_2025dc_2020, wgeel_2016, wkemp_2025';
COMMENT ON COLUMN dat.t_series_ser.ser_cou_code IS 'Code of the country';
COMMENT ON COLUMN dat.t_series_ser.ser_wltyp_code IS 'Code of the habitat type, one of MO (marine open), MC (Marine coastal), T (Transitional water), FW (Freshwater), null accepted';
COMMENT ON COLUMN dat.t_series_ser.ser_hab_code IS 'Code of the habitat, see tr_habitat_hab';
COMMENT ON COLUMN dat.t_series_ser.ser_are_code IS 'Code of the area, areas are geographical sector most often corresponding to stock units, see tr_area_are.';
COMMENT ON COLUMN dat.t_series_ser.ser_uni_code IS 'Annual value Unit, references table tr_unit_uni.';
COMMENT ON COLUMN dat.t_series_ser.ser_effort_uni_code IS 'Annual data collection effort unit code, references table tr_unit_uni.';
COMMENT ON COLUMN dat.t_series_ser.ser_description IS 'Sem description should comply with column svc_description in the vocabulary. Quick concise description of the series. Should include species, stage targeted, location and gear. e.g. Glass eel monitoring in the Vilaine estuary (France) with a trapping ladder.';
COMMENT ON COLUMN dat.t_series_ser.ser_locationdescription IS 'This should provide a description of the site, e.g. if ist far inland, in the middle of a river, near a dam etc. Also please specify the adjectant marine region (Baltic, North Sea) etc.
(e.g.  "Bresle river trap 3 km from the sea" or IYFS/IBTS sampling in the Skagerrak-Kattegat"';
COMMENT ON COLUMN dat.t_series_ser.ser_gea_code IS 'Code of the gear used, see tr_gear_gea';
COMMENT ON COLUMN dat.t_series_ser.ser_fiw_code IS 'Code the fishway, eg PO for pool type fishway';
COMMENT ON COLUMN dat.t_series_ser.ser_mon_code IS 'Code the Monitoring device, eg SO for Sonar';
COMMENT ON COLUMN dat.t_series_ser.ser_stocking IS 'Boolean, Is there restocking (for eel) or artifical reproduction in the river / basin, affecting the series ? ';
COMMENT ON COLUMN dat.t_series_ser.ser_stockingcomment IS 'Comment on stocking';
COMMENT ON COLUMN dat.t_series_ser.ser_protocol IS 'Describe sampling protocol';
COMMENT ON COLUMN dat.t_series_ser.ser_samplingstrategy IS 'Describe sampling strategy';
COMMENT ON COLUMN dat.t_series_ser.ser_datarightsholder IS 'Code of the data rights holder of the series, this field will be used in DATSU to acknowledge the source of data';
COMMENT ON COLUMN dat.t_series_ser.ser_datelastupdate IS 'Last modification in the series, from a trigger';
COMMENT ON COLUMN dat.t_series_ser.geom IS 'Series geometry column EPSG 4326, can be more detailed than the geometry for station';

GRANT ALL ON dateel.t_series_ser TO diaspara_admin;
GRANT SELECT ON dateel.t_series_ser TO diaspara_read;

Import t_series_ser from wgeel

Code to import to to refeel.tr_series_ser

res <- dbGetQuery(con_diaspara, "SELECT * FROM dateel.t_series_ser")
clipr::write_clip(colnames(res))

# temporarily remove area constraint

dbExecute(con_diaspara_admin, "ALTER TABLE dateel.t_series_ser drop constraint 
fk_ser_are_code")
dbExecute(con_diaspara_admin, "ALTER TABLE dateel.t_series_ser ALTER COLUMN  
ser_are_code DROP NOT NULL")
dbExecute(con_diaspara_admin, "ALTER TABLE dateel.t_series_ser ALTER COLUMN  
ser_cou_code DROP NOT NULL")
dbExecute(con_diaspara_admin, "ALTER TABLE dateel.t_series_ser DROP CONSTRAINT uk_ser_name")

t_series_ser <-
  data.frame(
   "ser_id" = uuid::UUIDgenerate(n=nrow(ser)),
   "ser_code" = ser$ser_nameshort,
    "ser_name"  = ser$ser_namelong,
   "ser_spe_code" = "ANG",
   "ser_lfs_code" = ser$ser_lfs_code,
   "ser_are_code" = ser$ser_emu_nameshort,
   "ser_wkg_code" = "WGEEL",
   "ser_ver_code" = case_when(ser$ser_dts_datasource =="dc_2019" ~ "ANG-2019-1",
                              ser$ser_dts_datasource =="dc_2021" ~ "ANG-2021-1",
                              ser$ser_dts_datasource =="dc_2022" ~ "ANG-2022-1",
                              ser$ser_dts_datasource =="dc_2023" ~ "ANG-2023-1",
                              ser$ser_dts_datasource =="dc_2024" ~ "ANG-2024-1",
                              .default = "ANG-2018-1"),
   "ser_cou_code" = ser$ser_cou_code,
   "ser_hab_code" = NA,
   "ser_gea_code" = ser$gea_issscfg_code,
   "ser_fiw_code" = NA,
   "ser_mon_code" = NA,
   "ser_uni_code" = ser$ser_uni_code,
   "ser_effort_uni_code" = ser$ser_effort_uni_code,
   "ser_description" = ser$ser_comment,
    "ser_locationdescription" = ser$ser_locationdescription,
    "ser_wltyp_code" = case_when(ser$ser_hty_code == "T" ~ "T",
                                 ser$ser_hty_code == "C" ~ "MC",
                                 ser$ser_hty_code == "F" ~ "FW",
                                 ser$ser_hty_code == "MO" ~ "MO"),
    "ser_stocking" = ser$ser_restocking,
    "ser_stockingcomment" = NA,
    "ser_protocol" = ser$ser_method,
    "ser_samplingstrategy" = NA,
    "ser_datarightsholder" = NA,
    "ser_datelastupdate" = '2025-06-09',
    "geom" = ser$geom    
  )

res <- dbWriteTable(con_diaspara_admin, "t_series_ser_temp", 
                    t_series_ser, overwrite = TRUE)
dbExecute(con_diaspara_admin, "DELETE FROM  dateel.t_series_ser;")
dbExecute(con_diaspara_admin, "INSERT INTO dateel.t_series_ser 
SELECT 
 ser_id::uuid,
 ser_code,
 ser_name,
 ser_spe_code,
 ser_lfs_code,
 ser_are_code,
 ser_wkg_code,
 ser_ver_code,
 ser_cou_code,
 ser_hab_code,
 ser_gea_code,
 ser_fiw_code,
 ser_mon_code,
 ser_uni_code,
 ser_effort_uni_code,
 ser_description,
 ser_locationdescription,
 ser_wltyp_code,
 ser_stocking,
 ser_stockingcomment,
 ser_protocol,
 ser_samplingstrategy,
 ser_datarightsholder,
 ser_datelastupdate::date,
 geom
FROM t_series_ser_temp") # 294 

sai <- dbGetQuery(con_wgeel_distant, "SELECT sai.* FROM datawg.t_samplinginfo_sai sai;
")

t_series_ser2 <-
  data.frame(
   "ser_id" = uuid::UUIDgenerate(n=nrow(sai)),
   "ser_code" = sai$sai_id,
    "ser_name"  = sai$sai_name,
   "ser_spe_code" = "ANG",
   "ser_lfs_code" = NA,
   "ser_are_code" = NA, # TODO sai_emu_nameshort sai_area_division
   "ser_wkg_code" = "WGEEL",
   "ser_ver_code" = case_when(sai$sai_dts_datasource =="dc_2019" ~ "ANG-2019-1",
                              sai$sai_dts_datasource =="dc_2021" ~ "ANG-2021-1",
                              sai$sai_dts_datasource =="dc_2022" ~ "ANG-2022-1",
                              sai$sai_dts_datasource =="dc_2023" ~ "ANG-2023-1",
                              sai$sai_dts_datasource =="dc_2024" ~ "ANG-2024-1",
                              .default = "ANG-2018-1"),
   "ser_cou_code" = sai$sai_cou_code,
   "ser_hab_code" = NA,
   "ser_gea_code" = NA,
   "ser_fiw_code" = NA,
   "ser_mon_code" = NA,
   "ser_uni_code" = NA,
   "ser_effort_uni_code" = NA,
   "ser_description" = sai$sai_comment,
    "ser_locationdescription" = NA,
    "ser_wltyp_code" = case_when(sai$sai_hty_code == "T" ~ "T",
                                 sai$sai_hty_code == "C" ~ "MC",
                                 sai$sai_hty_code == "F" ~ "FW",
                                 sai$sai_hty_code == "MO" ~ "MO"),
    "ser_stocking" = NA,
    "ser_stockingcomment" = NA,
    "ser_protocol" = sai$sai_protocol,
    "ser_samplingstrategy" = sai$sai_samplingstrategy,
    "ser_datarightsholder" = NA,
    "ser_datelastupdate" = sai$sai_lastupdate,
    "geom" = NA # no geom in this table  
  )

res <- dbWriteTable(con_diaspara_admin, "t_series_ser_temp2", 
                    t_series_ser2, overwrite = TRUE)
dbExecute(con_diaspara_admin, "INSERT INTO dateel.t_series_ser 
SELECT 
 ser_id::uuid,
 ser_code,
 ser_name,
 ser_spe_code,
 ser_lfs_code,
 ser_are_code,
 ser_wkg_code,
 ser_ver_code,
 ser_cou_code,
 ser_hab_code,
 ser_gea_code,
 ser_fiw_code,
 ser_mon_code,
 ser_uni_code,
 ser_effort_uni_code,
 ser_description,
 ser_locationdescription,
 ser_wltyp_code,
 ser_stocking,
 ser_stockingcomment,
 ser_protocol,
 ser_samplingstrategy,
 ser_datarightsholder,
 ser_datelastupdate::date,
 NULL
FROM t_series_ser_temp2") # 252

Creating table dat.t_serannual_san

SQL code to create tables

DROP TABLE IF EXISTS dat.t_serannual_san CASCADE;
CREATE TABLE dat.t_serannual_san (
san_ser_id UUID,
CONSTRAINT fk_san_ser_id FOREIGN KEY (san_ser_id)
  REFERENCES dat.t_series_ser (ser_id) 
  ON UPDATE CASCADE ON DELETE CASCADE, 
san_id SERIAL NOT NULL,
CONSTRAINT c_uk_san_id UNIQUE (san_id, san_wkg_code),
san_value NUMERIC NULL,
san_year INTEGER NOT NULL,
CONSTRAINT uk_san_year_svc UNIQUE(san_year, san_ser_id),
san_comment TEXT NULL, 
san_effort NUMERIC NULL,
san_datelastupdate DATE NOT NULL,
san_qal_id INTEGER NOT NULL,
san_qal_comment TEXT, 
san_wkg_code TEXT NOT NULL,  
CONSTRAINT fk_san_wkg_code  FOREIGN KEY (san_wkg_code)
REFERENCES "ref".tr_icworkinggroup_wkg(wkg_code)
ON UPDATE CASCADE ON DELETE RESTRICT,
san_ver_code TEXT NOT NULL,
CONSTRAINT fk_san_ver_code FOREIGN KEY (san_ver_code)
REFERENCES ref.tr_version_ver(ver_code)
ON UPDATE CASCADE ON DELETE RESTRICT
);
  


COMMENT ON TABLE dat.t_serannual_san IS 'Table of annual abundance data for series in dat.t_series_ser, these are recruitment or silver eel run data.  This table is inherited. It means that the data in dat is fed by
the content of the tables in datfeel, datnas, datbast ';

COMMENT ON COLUMN dat.t_serannual_san.san_ser_id IS 'UUID, identifier of the series, primary key, references the table ref.tr_seriesvocab_svc (svc_id)';
COMMENT ON COLUMN dat.t_serannual_san.san_id IS 'INTEGER, autoincremented, unique for one working group';
COMMENT ON COLUMN dat.t_serannual_san.san_year IS 'Year of monitoring, note that for some of the series this corresponds to the main migration season, 
For glass eel, months from september y-1 to august y should be denoted year y / For silver eel, months from june y to may y+1 should be denoted year y / For yellow eels, use the calendar year), see the series metadata for more details.';
COMMENT ON COLUMN dat.t_serannual_san.san_comment IS 'Comment on the annual value of the series';
COMMENT ON COLUMN dat.t_serannual_san.san_effort IS 'Eventually a measure of effort to collect the series, e.g. number of nr haul, nr fyke.day,
check the t_metadataannual table for the unit used';
COMMENT ON COLUMN dat.t_serannual_san.san_datelastupdate IS 'Date of last update on the annual data';
COMMENT ON COLUMN dat.t_serannual_san.san_qal_id IS 'Quality ID code of the series';
COMMENT ON COLUMN dat.t_serannual_san.san_qal_comment IS 'Comment related to data quality, e.g. why this year the series should not be used, or used with caution.';
COMMENT ON COLUMN dat.t_serannual_san.san_wkg_code IS 'Code of the working group, one of
WGBAST, WGEEL, WGNAS, WKTRUTTA';
COMMENT ON COLUMN dat.t_serannual_san.san_ver_code IS 'Version code sourced from ref.tr_version_ver the data call e.g. NAS_2025dc_2020, wgeel_2016, wkemp_2025';


ALTER TABLE dateel.t_serannual_san DROP  CONSTRAINT  fk_san_ver_code


ALTER TABLE dateel.t_serannual_san ADD CONSTRAINT  fk_san_ver_code FOREIGN KEY (san_ver_code)
REFERENCES refeel.tr_version_ver(ver_code)
ON UPDATE CASCADE ON DELETE RESTRICT

Creating inherited table dateel.t_seriesannual_san

SQL code to create tables

-- DROP TABLE IF EXISTS dateel.t_serannual_san;

CREATE TABLE dateel.t_serannual_san (
CONSTRAINT fk_san_ser_id FOREIGN KEY (san_ser_id)
  REFERENCES dateel.t_series_ser (ser_id) 
  ON UPDATE CASCADE ON DELETE CASCADE, 
CONSTRAINT c_uk_san_id UNIQUE (san_id, san_wkg_code), 
CONSTRAINT fk_san_wkg_code  FOREIGN KEY (san_wkg_code)
REFERENCES ref.tr_icworkinggroup_wkg(wkg_code),
CONSTRAINT fk_san_ver_code FOREIGN KEY (san_ver_code)
REFERENCES refeel.tr_version_ver(ver_code),
CONSTRAINT uk_san_year_svc UNIQUE(san_year, san_ser_id)
) INHERITS (dat.t_serannual_san);
  


COMMENT ON TABLE dateel.t_serannual_san IS 'Table of annual abundance data for series in dat.t_series_ser, these are recruitment or silver eel run data.  This table is inherited. It means that the data in dat is fed by
the content of the tables in datfeel, datnas, datbast';

COMMENT ON COLUMN dateel.t_serannual_san.san_ser_id IS 'UUID, identifier of the series, primary key, references the table ref.tr_seriesvocab_svc (svc_id)';
COMMENT ON COLUMN dateel.t_serannual_san.san_id IS 'INTEGER, autoincremented, unique for one working group';
COMMENT ON COLUMN dateel.t_serannual_san.san_year IS 'Year of monitoring, note that for some of the series this corresponds to the main migration season, 
For glass eel, months from september y-1 to august y should be denoted year y / For silver eel, months from june y to may y+1 should be denoted year y / For yellow eels, use the calendar year), see the series metadata for more details.';
COMMENT ON COLUMN dateel.t_serannual_san.san_comment IS 'Comment on the annual value of the series';
COMMENT ON COLUMN dateel.t_serannual_san.san_effort IS 'Eventually a measure of effort to collect the series, e.g. number of nr haul, nr fyke.day,
check the t_metadataannual table for the unit used';
COMMENT ON COLUMN dateel.t_serannual_san.san_datelastupdate IS 'Date of last update on the annual data';
COMMENT ON COLUMN dateel.t_serannual_san.san_qal_id IS 'Quality ID code of the series';
COMMENT ON COLUMN dateel.t_serannual_san.san_qal_comment IS 'Comment related to data quality, e.g. why this year the series should not be used, or used with caution.';
COMMENT ON COLUMN dateel.t_serannual_san.san_wkg_code IS 'Code of the working group, one of
WGBAST, WGEEL, WGNAS, WKTRUTTA';
COMMENT ON COLUMN dateel.t_serannual_san.san_ver_code IS 'Version code sourced from ref.tr_version_ver the data call e.g. NAS_2025dc_2020, wgeel_2016, wkemp_2025';


GRANT ALL ON dateel.t_serannual_san TO diaspara_admin;
GRANT SELECT ON dateel.t_serannual_san TO diaspara_read;

Import dateel.t_serannual_ser from datawg.t_dataseries_das WGEEL

Code to import to refeel.t_serannual_ser

das0 <- dbGetQuery(con_wgeel_distant, "SELECT * FROM datawg.t_dataseries_das;
")

res <- dbGetQuery(con_diaspara, "SELECT * FROM dateel.t_serannual_san")
clipr::write_clip(colnames(res))

das0$das_dts_datasource[is.na(das0$das_dts_datasource)] <- "WGEEL-2016"
das0$das_dts_datasource <- paste0(gsub(pattern = "dc_", replacement = "WGEEL-", x= das0$das_dts_datasource), "-1")
das0$das_qal_id[is.na(das0$das_qal_id)] <- 1

nrow(das0) # 6523
das0 <- das0 |> filter(das0$das_qal_id <5)
nrow(das0) # 6402

ser <-  dbGetQuery(con_diaspara, "SELECT ser_id, ser_code FROM dateel.t_series_ser")
ser0 <- dbGetQuery(con_wgeel_distant, "SELECT ser_id, ser_nameshort as ser_code FROM datawg.t_series_ser ser;")

# adding ser_code to the series.
das <- das0 |> rename(ser_id = das_ser_id) |>
  inner_join(ser0) |> select(-ser_id) |> 
  inner_join(ser)
nrow(das) #6523

t_serannual_san_temp <-
  data.frame(
    "san_ser_id" = das$ser_id,
    "san_id" = das$das_id,
    "san_value"=das$das_value,
    "san_year" = das$das_year,
    "san_comment" = das$das_comment,
    "san_effort" = das$das_effort,
    "san_datelastupdate" = das$das_last_update,
    "san_qal_id" = das$das_qal_id,
    "san_qal_comment" = das$das_qal_comment,
    "san_wkg_code" = "WGEEL",
    "san_ver_code" = das$das_dts_datasource)


res <- dbWriteTable(con_diaspara_admin, "t_serannual_san_temp", 
                    t_serannual_san_temp, overwrite = TRUE)
dbExecute(con_diaspara_admin, "DELETE FROM  dateel.t_serannual_san;")
dbExecute(con_diaspara_admin, "INSERT INTO dateel.t_serannual_san 
SELECT 
    san_ser_id::uuid,
    san_id,
    san_value,
    san_year,
    san_comment,
    san_effort,
    san_datelastupdate,
    san_qal_id,
    san_qal_comment,
    san_wkg_code,
    san_ver_code 
FROM t_serannual_san_temp") # 6402

Creating table dat.tj_seriesstation_ses

SQL code to create tables

DROP TABLE IF EXISTS dat.tj_seriesstation_ses; 
CREATE TABLE dat.tj_seriesstation_ses (
 ses_ser_id uuid PRIMARY KEY,
 CONSTRAINT fk_ses_ser_id FOREIGN KEY (ses_ser_id)
    REFERENCES dat.t_series_ser (ser_id) 
    ON UPDATE CASCADE ON DELETE CASCADE,  
 CONSTRAINT uk_ses_ser_id UNIQUE (ses_ser_id) ,
 ses_station_code INTEGER NULL,
  CONSTRAINT fk_station_code FOREIGN KEY (ses_station_code) 
  REFERENCES "ref"."StationDictionary" ("Station_Code")   
  );
  

GRANT ALL ON dat.tj_seriesstation_ses TO diaspara_admin;
GRANT SELECT ON dat.tj_seriesstation_ses TO diaspara_read;

Creating inherited table dateel.tj_seriesstation_ses

SQL code to create tables

DROP TABLE IF EXISTS dateel.tj_seriesstation_ses; 
CREATE TABLE dateel.tj_seriesstation_ses (
 CONSTRAINT fk_ses_ser_id FOREIGN KEY (ses_ser_id)
    REFERENCES dat.t_series_ser (ser_id) 
    ON UPDATE CASCADE ON DELETE CASCADE,  
 CONSTRAINT uk_ses_ser_id UNIQUE (ses_ser_id) ,
  CONSTRAINT fk_station_code FOREIGN KEY (ses_station_code) 
  REFERENCES "ref"."StationDictionary" ("Station_Code")   
  ) inherits (dat.tj_seriesstation_ses);
  

GRANT ALL ON dateel.tj_seriesstation_ses TO diaspara_admin;
GRANT SELECT ON dateel.tj_seriesstation_ses TO diaspara_read;

Creating one table that should only resided in wgeel datasets

Creating table dateel.t_recruitmentmetadata_met

The code was developped during WKEELDATA6 Currently the table is not intended to be held in the db There is still work to be done on the area referential for eel (starting summer 2025). Some referentials are coming from the old db (emu, sampling type), and is currently not transfered.

SQL code to create tables

 -- notes I will need ref. tr_emu_emu for legacy with this table.
 -- but as the table will not be placed in the db
 -- I'm not programming foreign keys
 -- see
--DROP TABLE IF EXISTS dateel.t_recruitmentmetadata_met ;
CREATE TABLE dateel.t_recruitmentmetadata_met (
 met_ser_id uuid PRIMARY KEY,
 -- the following constraint ensure 1:1 relation to series
 CONSTRAINT fk_met_ser_id FOREIGN KEY (met_ser_id)
    REFERENCES dat.t_series_ser (ser_id) 
    ON UPDATE CASCADE ON DELETE CASCADE,  
 CONSTRAINT uk_mrt_ser_id UNIQUE (met_ser_id) ,
met_sam_id int4 NULL,
--CONSTRAINT fk_met_sam_id FOREIGN KEY (met_sam_id) 
--   REFERENCES ref.tr_samplingtype_sam(sam_id)
 --  ON UPDATE CASCADE ON DELETE CASCADE,
   -- this one is a legacy from the old db, for this table
   -- intended only for WGEEL I'll not change it
met_emu_nameshort varchar(20) NOT NULL,
--CONSTRAINT fk_emu FOREIGN KEY (met_emu_nameshort,met_cou_code) 
--REFERENCES ref.tr_emu_emu(emu_nameshort,emu_cou_code)
--ON UPDATE CASCADE ON DELETE CASCADE,
met_qal_id INTEGER,
--FOREIGN KEY (met_qal_id) 
--REFERENCES  ref.tr_quality_qal(qal_id),
-- currently tr_quality_qal is only in ref (with values from wgeel) not refeel check ??
met_mixturegy BOOLEAN,
met_wetted_above NUMERIC,
met_wetted_total NUMERIC,
met_surfacebasin_upstream NUMERIC,
met_surfacebasin NUMERIC,
met_distanceseakm numeric NULL,
met_marinearea TEXT,
met_start_year INTEGER,
met_end_year INTEGER,
met_duration INTEGER,
met_missing INTEGER,
met_individual_length BOOLEAN,
met_individual_mass BOOLEAN,
met_conversion BOOLEAN,
met_conversion_comment BOOLEAN,
met_raw_to_reported TEXT,
met_internal_issues BOOLEAN,
met_internal_issues_comment TEXT,
met_other BOOLEAN,
met_other_comment TEXT,
met_restocking BOOLEAN,
met_restocking_comment TEXT,
met_barrier BOOLEAN,
met_barrier_comment TEXT,
met_mortality BOOLEAN,
met_mortality_comment TEXT,
met_ext_issues BOOLEAN,
met_ext_issues_comment TEXT,
met_environment BOOLEAN,
met_environment_comment TEXT)


GRANT ALL ON dateel.t_recruitmentmetadata_met TO diaspara_admin;
GRANT SELECT ON dateel.t_recruitmentmetadata_met TO diaspara_read;

Creating group metrics (TODO)

Creating ind metrics (TODO)

[TODO]

Import data from WGEEL (TODO)

[TODO] Link the ccm wso_id to the hydroshed db identifiers of the basins to get the best fit from tr_area_are. git issue #25 Import data from WGEEL

The release date for that one is :

Milestone release alpha

So the metric release will be after wgeel, but hopefully some of the work will be started and this can be discussed during wgeel.

Import data from WP2 (TODO)

Import to ICES

Acknowledgements

ICES. 2024. “ICES Vocabularies and Codes.” ICES Data Flow Schematics. https://doi.org/10.17895/ICES.PUB.25288189.V1.