diaspara metric database creation script

DIASPARA WP3.2 working document

Creation of metric db, version = build
Author

Briand Cédric, Oliviero Jules, Helminen Jani

Published

09-07-2025

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 additionally, 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).

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).

Figure 2: Diagram for sampling

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

Building database structure from WGEEL

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

schema stationDictionary ref.StationDictionary        sta_code (integer)          sta_activefromdate date        sta_activeuntildate date ts dat.series        ser_id        ...     metts dat.metadata-series These are metadata            created during benchmark            probably not in DB               ser_id         ...      station_ts_join dat.series-station-join One series can have multiple stations       ser_id         StationCode        station_ts_join->stationDictionary 1:n station_ts_join->ts n:1 ann dat.annual-series       ser_id         year           ...    ann->ts n:1 grouptrait dat.grouptrait       ser_id         year          mean size       ... group dat.group       ser_id         gr_id          year                 ... grouptrait->group n:1 trait ref.trait            metric_id (length, weight..)           grouptrait->trait n:1 traitmethod ref.traitmethod            Sex (Gonadal inspection), Sex (length based)..           grouptrait->traitmethod n:1 traitqalvalue ref.traitqalvalue            traitqalcode (pigment stage, sex..)         qalValue (VB, M(male), F(female))         grouptrait->traitqalvalue n:1 anneel dat.annual-series grouptrait->anneel implicit (year - ser_id) group->ts n:1 fish dat.fish       ser_id         fi_id          x          y          date       ... fish->ts n:1 indtrait dat.individualmetrics       fi_id         metric_id         value   indtrait->fish n:1 indtrait->trait n:1 indtrait->traitmethod n:1 indtrait->traitqalvalue n:1 traitqal ref.trait_qualitative            trait_code (Sex, Pigment stage..)           trait->traitqal inherits traitnum ref.trait_numeric            trait_code (Length_mm, Weight_g..)           trait->traitnum inherits traitqalvalue->traitqal n:1 tseel dateel.series tseel->ts inherits mettseel dateel.metadata-series mettseel->metts inherits anneel->ann inherits anneel->tseel n:1 grouptraiteel dateel.grouptrait grouptraiteel->grouptrait inherits metanneel metanneel grouptraiteel->metanneel n:1 indtraiteel dateel.indtrait indtraiteel->indtrait inherits fisheel dateel.fish indtraiteel->fisheel n:1 fisheel->fish inherits fisheel->tseel n:1 traiteel dateel.trait traiteel->trait inherits tsnas datnas.series tsnas->ts inherits indtraitnas datnas.indtrait indtraitnas->indtrait inherits fishnas datnas.fish indtraitnas->fishnas n:1 fishnas->fish inherits fishnas->tsnas n:1 traitnas datnas.trait traitnas->trait inherits
Figure 3: Simplified structure of the metric database. The time series table, start with the series (at the bottom) which correspond to a series site 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). The series table can join several stations from the ICES vocab.

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 LongDescription 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-07-07T06:34:44.657 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-07-07T18:19:52.437 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 LongDescription 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-07-07T18:20:57.213 FALSE 42 83c14aa4-133b-49bc-8a32-107047146848
26937 b677e830-34f8-4eaf-88e3-3377d726d209 E Eutrophication effects monitoring 2025-07-07T18:20:58.913 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-07-07T06:35:55.053 FALSE 42 83c14aa4-133b-49bc-8a32-107047146848
26941 562ae8f8-e175-4f07-ae92-a5af7b32539d T Temporal trend monitoring 2025-07-07T18:21:00.377 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 LongDescription 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-07-07T18:15:36.883 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-07-07T18:21:01.82 FALSE 1400 111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148256 85103227-9697-4487-a874-053e9d88de5d DF Disease in biota 2025-07-07T18:15:40.493 FALSE 1400 111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148252 185f5a57-72e1-419b-b4ab-2a0b2aa0f899 EF Biological effects in biota 2025-07-07T18:15:38.633 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-07-07T18:21:46.69 FALSE 1400 111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148254 8a758bca-6647-4b47-94a6-7d85ea48a29b EW Biological effects in water 2025-07-07T18:21:45.353 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-07-07T18:22:22.77 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-07-07T18:17:13.433 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-07-07T06:34:49.877 FALSE 1400 111d1f9c-6352-47ec-98f5-bc2cb60bcb55
148261 e5278079-7d6a-46b4-b844-4be4e4c0d344 ZP Zooplankton 2025-07-07T18:17:15.013 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 LongDescription 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-07-07T18:16:00.73 FALSE 212 70e73f70-643d-497b-8d91-a1560d1c4518
53329 0a28de1c-018a-4841-b3a3-a68e01c81cd5 CE Coastal water (Estuary) 2025-07-07T06:20:30.267 FALSE 212 70e73f70-643d-497b-8d91-a1560d1c4518
54066 85a228c9-9033-4f97-accf-4968db78a90a CF Coastal water (Fjord) 2025-07-07T18:15:42.03 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-07-07T06:25:44.82 FALSE 212 70e73f70-643d-497b-8d91-a1560d1c4518
53333 a75522ef-5e4a-4e2d-8550-38091cb6c994 MO Marine water (open sea) 2025-07-07T18:20:33.66 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-07-07T18:20:02.697 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 LongDescription 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-07-07T18:15:43.513 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-07-07T06:33:52.457 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 table tr_fishway_fiw 
-- 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', 
'A pool pass consists of a stepped channel divided by cross-walls that form a series of pools, where water flows through submerged or surface openings, allowing fish to rest between short bursts through higher-velocity zones. The rough bottom and calm pools make it especially suitable for both swimming and bottom-dwelling species.');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('FL', 
'Fish lock', 
'A fish lock, similar in structure to a ship lock, uses a lock chamber with inlet and outlet gates to help fish ascend past barriers; unlike ship locks, it is specifically designed to support fish migration by addressing issues like turbulence, timing, and the need for a guiding current. While ship locks generally cannot replace fish passes, they can sometimes be adapted during peak migration seasons to aid species like salmon or glass eels.');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('D', 
'Denil pass', 
'compact, steeply sloped fish pass featuring angled baffles that create backflows to reduce water velocity, enabling fish to ascend over moderate height differences. Its prefabricated design and efficient energy dissipation make it ideal for retrofitting existing dams with limited space, with the standard U-shaped baffle version now widely used.');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('RR', 
'Rock ramp', 
'Close-to_nature type, gently sloped rough-surfaced sill spanning the river width with a gentle slope, designed to overcome riverbed level differences; it may include stabilizing structures like weirs if they share similar sloped, loose construction.');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('ER', 
'Eel ramp', 
'Eel ladders/ramps, e.g., pipe-based systems laid through weirs and filled with baffles or brushwood to slow flow, shallow channels fitted with brush or gravel structures that help eels ascend while offering better visibility, maintenance, and protection from predators.');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('LA', 
'Lateral canal', 
'Bybass channel that uses lateral canals');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('AR', 
'Artificial river', 
'Human-made channel designed to mimic the characteristics of a natural stream, allowing fish to bypass barriers like dams. These fishways typically feature a gentle slope, varied flow conditions, resting areas, and natural substrates (like gravel and rocks, trees, bushes, etc.)');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('UN', 
'Unknown', 
'Unknown type fish passage');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('S', 
'Sluice', 
'Periodically opening sluice gates that creates a strong, directed flow that attracts and allows fish to pass. ');
INSERT INTO ref.tr_fishway_fiw (fiw_code, fiw_description, fiw_definition)
VALUES('HL', 
'Hydraulic lift', 
'A type of fish elevator that uses hydraulic mechanisms—such as pumps, valves, and water pressure—to move fish over high barriers like dams.');


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 A pool pass consists of a stepped channel divided by cross-walls that form a series of pools, where water flows through submerged or surface openings, allowing fish to rest between short bursts through higher-velocity zones. The rough bottom and calm pools make it especially suitable for both swimming and bottom-dwelling species. NA NA NA
FL Fish lock A fish lock, similar in structure to a ship lock, uses a lock chamber with inlet and outlet gates to help fish ascend past barriers; unlike ship locks, it is specifically designed to support fish migration by addressing issues like turbulence, timing, and the need for a guiding current. While ship locks generally cannot replace fish passes, they can sometimes be adapted during peak migration seasons to aid species like salmon or glass eels. NA NA NA
D Denil pass compact, steeply sloped fish pass featuring angled baffles that create backflows to reduce water velocity, enabling fish to ascend over moderate height differences. Its prefabricated design and efficient energy dissipation make it ideal for retrofitting existing dams with limited space, with the standard U-shaped baffle version now widely used. NA NA NA
RR Rock ramp Close-to_nature type, gently sloped rough-surfaced sill spanning the river width with a gentle slope, designed to overcome riverbed level differences; it may include stabilizing structures like weirs if they share similar sloped, loose construction. NA NA NA
ER Eel ramp Eel ladders/ramps, e.g., pipe-based systems laid through weirs and filled with baffles or brushwood to slow flow, shallow channels fitted with brush or gravel structures that help eels ascend while offering better visibility, maintenance, and protection from predators. NA NA NA
LA Lateral canal Bybass channel that uses lateral canals NA NA NA
AR Artificial river Human-made channel designed to mimic the characteristics of a natural stream, allowing fish to bypass barriers like dams. These fishways typically feature a gentle slope, varied flow conditions, resting areas, and natural substrates (like gravel and rocks, trees, bushes, etc.) NA NA NA
UN Unknown Unknown type fish passage NA NA NA
S Sluice Periodically opening sluice gates that creates a strong, directed flow that attracts and allows fish to pass. NA NA NA
HL Hydraulic lift A type of fish elevator that uses hydraulic mechanisms—such as pumps, valves, and water pressure—to move fish over high barriers like dams. NA NA NA

Fish migration monitoring ref.tr_monitoring_mon

SQL code to create table tr_monitoring_mon 
-- 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', 
'Multibeam, 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', 
'Telemetry', 
'e.g., Acoustic, PIT, or radiotelemetry receivers are use in fish tracking to detect and decode transmissions from fish tags');
INSERT INTO ref.tr_monitoring_mon (mon_code, mon_description, mon_definition)
VALUES('IR', 
'Infrared counter', 
'Counter that uses infrared, e.g., Vaki Riverwatcher');






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 Multibeam, 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 Telemetry e.g., Acoustic, PIT, or radiotelemetry receivers are use in fish tracking to detect and decode transmissions from fish tags NA NA NA
IR Infrared counter Counter that uses infrared, e.g., Vaki Riverwatcher NA NA NA
OC Other catching methods Any other type of fish catching method than a trap. E.g. rod, gillnet, etc. NA NA NA
VC Visual count Visual fish counting methods, not including with the use of cameras NA NA NA
IQ Interview or questionnaire Interview or questionnaire of e.g. recreational fishers or other parties NA NA NA
MK Mark and Recapture Mark and recapture methods NA NA NA
CD Catch data Recreational or commercial fisheries catch data 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 tables ref.tr_trait_tra and refeel.tg_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_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_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_code UNIQUE (tra_code)
);


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_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; 

/*
note The refeel.tg_trait_tra actually contains physically all parms and that's not the case of
ref.tr_trait_tra which only gets those by inheritance.
refeel.tg_trait_tra must be created after insertion in tr_traitnumeric_trn
and tr_traitqualitative_trq
*/

CREATE TABLE refeel.tg_trait_tra AS (
SELECT  
  tra_id,
  tra_code,
  tra_description, 
  tra_wkg_code,  
  tra_spe_code , 
  tra_typemetric,
  tra_qualitativeornumeric FROM 
  refeel.tr_traitnumeric_trn
UNION
SELECT  
  tra_id,
  tra_code,
  tra_description, 
  tra_wkg_code,  
  tra_spe_code , 
  tra_typemetric,
  tra_qualitativeornumeric FROM 
  refeel.tr_traitqualitative_trq);
ALTER TABLE refeel.tg_trait_tra 
ADD  CONSTRAINT uk_tra_code UNIQUE (tra_code);
refeel.tg_trait_tra and ref.tr_trait_tra have different structures.

The first has nothing inside, it’s a shell for inheritance. Tables ref.tr_traitnumeric_trn and ref.tr_traitqualitative_trq are inherited from ref.tr_trait_tra. They in turn are inherited from tables refeel.tr_traitnumeric_trn and refeel.tr_traitqualitative_trq. So the values entered in refeel.tr_traitqualitative_trq appear when running a select query from ref.tr_trait_tra even if they are not physically in these tables, and foreign key constraint to ref.tr_trait_tra would return no value there. Now if I inherit refeel.tr_trait_tra the values will get to ref.tr_trait_tra from two sources and I will double the lines there. Also, physically data are not there. So I still cannot use it as a reference table. So the table refeel.tg_trait_tra is not inherited, it’s a grouping table with the values actually physically there.

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 tables ref.tr_traitnumeric_trn and refeel.tr_traitnumeric_trn 
-- DROP TABLE IF EXISTS ref.tr_traitnumeric_trn;
CREATE TABLE ref.tr_traitnumeric_trn(  
trn_uni_code varchar(20) NULL,
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_trn_uni_code FOREIGN KEY (trn_uni_code) 
  REFERENCES ref.tr_units_uni(uni_code)   ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT uk_trn_code UNIQUE (tra_code)
) 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_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_uni_code IS 'Unit used, references tr_unit_uni';
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; 


DROP TABLE IF EXISTS refeel.tr_traitnumeric_trn;
CREATE TABLE refeel.tr_traitnumeric_trn(  
  CONSTRAINT uk_refeel_tra_id UNIQUE (tra_id),
  CONSTRAINT uk_refell_tra_code UNIQUE(tra_code),
  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_trn_uni_code FOREIGN KEY (trn_uni_code) 
  REFERENCES ref.tr_units_uni(uni_code)  
  ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT uk_trn_code UNIQUE (tra_code)
) INHERITS  (ref.tr_traitnumeric_trn);

COMMENT ON COLUMN refeel.tr_traitnumeric_trn.tra_id IS 'Integer, id of the trait';
COMMENT ON COLUMN refeel.tr_traitnumeric_trn.tra_code IS 'Name of the trait';
COMMENT ON COLUMN refeel.tr_traitnumeric_trn.tra_description IS 'Description of the fish trait';
COMMENT ON COLUMN refeel.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 refeel.tr_traitnumeric_trn.trn_uni_code IS 'Unit used, references tr_unit_uni';
COMMENT ON COLUMN refeel.tr_traitnumeric_trn.trn_minvalue IS 'Minimum allowed value';
COMMENT ON COLUMN refeel.tr_traitnumeric_trn.trn_maxvalue IS 'Maximum allowed value';


GRANT ALL ON refeel.tr_traitnumeric_trn TO diaspara_admin;
GRANT SELECT ON refeel.tr_traitnumeric_trn TO diaspara_read;
Code to import numeric trait. 
tra <- dbGetQuery(con_wgeel_local, "SELECT * FROM ref.tr_metrictype_mty ;")
# we will include group metric names later
tra <- tra[(!grepl("mean", tra$mty_name) | tra$mty_name =='eye_diam_meanmm' | tra$mty_name == 'teq'),]
# 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" = stringi::stri_trans_totitle(tra$mty_name),
"tra_description" = tra$mty_description,
"tra_wkg_code" = 'WGEEL',
"Tra_spe_code" = 'ANG',
"trn_uni_code" = tra$mty_uni_code,
"tra_typemetric"=stringi::stri_trans_totitle(tra$mty_group),"tra_qualitativeornumeric"='Numeric',
"trn_minvalue" = tra$mty_min,
"trn_maxvalue" = tra$mty_max)


# 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_typemetric")] <- "Group"


# view(tranum)
# fix names
tranum$tra_description[tranum$tra_code=="Lengthmm"] <- 'Total body length in millimeters (mm) or mean total body length for group'
tranum$tra_description[tranum$tra_code=="Differentiated_proportion"] <- 'Proportion of differentiated eel (between 0 and 1)'

tranum$tra_description[tranum$tra_code=="Female_proportion"] <- 'Female proportion in the population female/(male+female) for group (between 0 and 1)'
tranum$tra_description[tranum$tra_code=="Anguillicola_proportion"] <- 'Prevalence of Anguillicola in proportion in group (between 0 and 1)'
tranum$tra_description[tranum$tra_code=="Evex_proportion"] <- 'EVE and EVEX proportion in the group (between 0 and 1)'
tranum$tra_description[tranum$tra_code=="Hva_proportion"] <- 'HVA proportion in the group (between 0 and 1)'
# duplicated, now two methods
tranum <- tranum[tranum$tra_id!=11,] 
tranum$tra_description[tranum$tra_id==10] <-
"Lipid percentage or mean muscle lipid percentage for group"
tranum[tranum$tra_id==10,"tra_code"] <- "Muscle_lipid"

dbWriteTable(con_diaspara_admin, "tr_traitnumeric_trn_temp", tranum, overwrite =TRUE)
dbExecute(con_diaspara_admin, "DELETE FROM refeel.tr_traitnumeric_trn")
dbExecute(con_diaspara_admin, "INSERT INTO refeel.tr_traitnumeric_trn(
tra_id,
tra_code,
tra_wkg_code,
tra_spe_code,
tra_description,
tra_typemetric,
trn_uni_code,
tra_qualitativeornumeric,
trn_minvalue,
trn_maxvalue)
SELECT 
tra_id,
tra_code,
'WGEEL',
'ANG',
tra_description,
tra_typemetric,
trn_uni_code,
tra_qualitativeornumeric,
trn_minvalue,
trn_maxvalue
 FROM tr_traitnumeric_trn_temp") #18
Table 12: Quantitative parameters parameters
tra_id tra_code tra_description tra_wkg_code tra_spe_code tra_typemetric tra_qualitativeornumeric trn_uni_code trn_minvalue trn_maxvalue
1 Lengthmm Total body length in millimeters (mm) or mean total body length for group WGEEL ANG Both Numeric mm 50.0 1500
2 Weightg Weight (g) or mean weight for group WGEEL ANG Both Numeric g 0.1 3000
3 Ageyear Age (year) or mean age for group WGEEL ANG Both Numeric nr year 0.0 75
5 Pectoral_lengthmm Pectoral fin length (mm) WGEEL ANG Individual Numeric mm 3.0 54
7 Differentiated_proportion Proportion of differentiated eel (between 0 and 1) WGEEL ANG Group Numeric wo 0.0 1
9 Anguillicola_intensity A. crassus intensity or mean A. crassus intensity for group WGEEL ANG Both Numeric nr 0.0 NA
10 Muscle_lipid Lipid percentage or mean muscle lipid percentage for group WGEEL ANG Both Numeric percent 0.0 NA
12 Sum_6_pcb Sum of six PCBs or mean sum of six PCBs for groups WGEEL ANG Both Numeric ng/g 0.0 NA
13 Evex_proportion EVE and EVEX proportion in the group (between 0 and 1) WGEEL ANG Group Numeric wo 0.0 1
14 Hva_proportion HVA proportion in the group (between 0 and 1) WGEEL ANG Group Numeric wo 0.0 1
15 Pb Lead (Pb) concentration or mean lead (Pb) concentration WGEEL ANG Both Numeric ng/g 0.0 NA
16 Hg Mercury (Hg) concentration or mean lead (Pb) concentration for group WGEEL ANG Both Numeric ng/g 0.0 NA
17 Cd Cadmium (Cd) concentration or mean cadmium concentration for group WGEEL ANG Both Numeric ng/g 0.0 NA
24 G_in_gy_proportion Proportion of glass eel in number during the season when the series is a grouping of glass and yellow eels WGEEL ANG Group Numeric wo 0.0 1
25 S_in_ys_proportion Proportion of silver eel in number in the group WGEEL ANG Group Numeric wo 0.0 1
26 Teq Sum TEQ of measured dioxin-like PCBs or mean sum TEQ of measured dioxin-like PCBs WGEEL ANG Both Numeric ng/g 0.0 NA
6 Female_proportion Female proportion in the population female/(male+female) for group (between 0 and 1) WGEEL ANG Group Numeric wo 0.0 1
8 Anguillicola_proportion Prevalence of Anguillicola in proportion in group (between 0 and 1) WGEEL ANG Group Numeric wo 0.0 1
4 Eye_diam_meanmm Eye diameter, or average of vertical and horizontal diameter (mm) WGEEL ANG Individual Numeric mm 1.0 15

Qualitative fish trait (tr_traitqualitative_trq)

SQL code to create table ref.tr_traitqualitative_trq and refeel.tr_traitqualitative_trq 
-- DROP TABLE IF EXISTS ref.tr_traitqualitative_trq CASCADE;

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 uk_trq_code UNIQUE (tra_code)
  ) 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; 

DROP TABLE IF EXISTS refeel.tr_traitqualitative_trq;
CREATE TABLE refeel.tr_traitqualitative_trq (
  CONSTRAINT uk_refeel_tra_id UNIQUE (tra_id),
  CONSTRAINT uk_refeel_tra_code UNIQUE(tra_code),
  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
  ) INHERITS (ref.tr_traitqualitative_trq);


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

GRANT ALL ON refeel.tr_traitqualitative_trq TO diaspara_admin;
GRANT SELECT ON refeel.tr_traitqualitative_trq TO diaspara_read;
Code to import qualitative table. 
tra <- dbGetQuery(con_wgeel_local, "SELECT * FROM ref.tr_metrictype_mty ;")
# we will include group metric names later
tra <- tra[grepl("is_", tra$mty_individual_name) | grepl("presence", tra$mty_individual_name),]
# we will also include method later

#clipr::write_clip(colnames(res))
traqal <- data.frame(
"tra_id" = tra$mty_id,
"tra_code" = stringi::stri_trans_totitle(tra$mty_individual_name),
"tra_description" = tra$mty_description,
"tra_wkg_code" = 'WGEEL',
"Tra_spe_code" = 'ANG',
"tra_uni_code" = tra$mty_uni_code,
"tra_typemetric"='Individual',
"tra_qualitativeornumeric"='Qualitative'
)


# the proportions will be numeric for group and qualitative for individual (
# e.g. evex presence)

traqal$tra_code <- gsub("\\s*_\\([^\\)]+\\)", "", traqal$tra_code)
traqal$tra_code[traqal$tra_code=="Is_female"] <- "Sex"
traqal$tra_individualname <- traqal$tra_code

# fix names

traqal$tra_description[traqal$tra_code=="Is_differentiated"] <- 'Is the eel differentiated (Y Yes, N No, NA Not applicable,P probable, U Unknown)'
traqal$tra_description[traqal$tra_code=="Sex"] <- 'Sex (F Female,H Hermaphordite,
I Immature attempt made,M Male, T Transitional, U Undetermined no attempt made, X Mixed)'
traqal$tra_description[traqal$tra_code=="Anguillicola_presence"] <- 'Presence of Anguillicola (Y Yes, N No, NA Not applicable,P probable, U Unknown)'
traqal$tra_description[traqal$tra_code=="Evex_presence"] <- 'EVE or EVEX presence (Y Yes, N No, NA Not applicable,P probable, U Unknown)'
traqal$tra_description[traqal$tra_code=="Hva_presence"] <- 'HVA presence (Y Yes, N No, NA Not applicable,P probable, U Unknown)'
# view(traqal)

dbWriteTable(con_diaspara_admin, "tr_traitqualitative_trq_temp", traqal, overwrite =TRUE)
dbExecute(con_diaspara_admin, "DELETE FROM refeel.tr_traitqualitative_trq");
dbExecute(con_diaspara_admin, "INSERT INTO refeel.tr_traitqualitative_trq(
tra_id,
tra_code,
tra_wkg_code,
tra_spe_code,
tra_description,
tra_typemetric,
tra_qualitativeornumeric)
SELECT 
tra_id,
tra_code,
'WGEEL',
'ANG',
tra_description,
tra_typemetric,
tra_qualitativeornumeric
 FROM tr_traitqualitative_trq_temp") #5
# TODO add group name


dbExecute(con_diaspara_admin, "INSERT INTO refeel.tr_traitqualitative_trq(
tra_id,
tra_code,
tra_wkg_code,
tra_spe_code,
tra_description,
tra_typemetric,
tra_qualitativeornumeric)
SELECT 
29,
'Pigment_stage',
'WGEEL',
'ANG',
'Pigmentation stage according to Elie, 1982',
'Individual',
'Qualitative'") 
# duplicated for variables which were both group and 
dbExecute(con_diaspara_admin, "UPDATE refeel.tr_traitqualitative_trq SET tra_id = 34 WHERE tra_code='Hva_presence';")
dbExecute(con_diaspara_admin, "UPDATE refeel.tr_traitqualitative_trq SET tra_id = 33 WHERE tra_code='Evex_presence';")
dbExecute(con_diaspara_admin, "UPDATE refeel.tr_traitqualitative_trq SET tra_id= 32 WHERE tra_code='Anguillicola_presence';")
dbExecute(con_diaspara_admin, "UPDATE refeel.tr_traitqualitative_trq SET tra_id= 31 WHERE tra_code='Is_differentiated'")
dbExecute(con_diaspara_admin, "UPDATE refeel.tr_traitqualitative_trq SET tra_id= 30 WHERE tra_code='Sex';")
Table 13: Qualtitative parameters
tra_id tra_code tra_description tra_wkg_code tra_spe_code tra_typemetric tra_qualitativeornumeric
29 Pigment_stage Pigmentation stage according to Elie, 1982 WGEEL ANG Individual Qualitative
34 Hva_presence HVA presence (Y Yes, N No, NA Not applicable,P probable, U Unknown) WGEEL ANG Individual Qualitative
33 Evex_presence EVE or EVEX presence (Y Yes, N No, NA Not applicable,P probable, U Unknown) WGEEL ANG Individual Qualitative
32 Anguillicola_presence Presence of Anguillicola (Y Yes, N No, NA Not applicable,P probable, U Unknown) WGEEL ANG Individual Qualitative
31 Is_differentiated Is the eel differentiated (Y Yes, N No, NA Not applicable,P probable, U Unknown) WGEEL ANG Individual Qualitative
30 Sex Sex (F Female,H Hermaphordite, I Immature attempt made,M Male, T Transitional, U Undetermined no attempt made, X Mixed) |WGEEL |ANG |Individual |Qualitative

Values of Qualitative fish trait (tr_traitvaluequal_trv)

The qualitative traits have values

SQL code to create tables ref.tr_traitqualvalue_trv and refeel.tr_traitqualvalue_trv 
-- DROP TABLE IF EXISTS ref.tr_traitvaluequal_trv CASCADE;

CREATE TABLE ref.tr_traitvaluequal_trv(
  trv_id INTEGER,
  trv_trq_code TEXT NOT NULL,
  CONSTRAINT fk_trv_trq_code 
  FOREIGN KEY (trv_trq_code)
  REFERENCES ref.tr_traitqualitative_trq(tra_code)
  ON UPDATE CASCADE ON DELETE CASCADE,
  trv_code text NOT NULL ,
  trv_description text NULL,
  trv_spe_code TEXT NOT NULL,
  CONSTRAINT fk_trv_spe_code  FOREIGN KEY (trv_spe_code)
  REFERENCES ref.tr_species_spe(spe_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  trv_wkg_code TEXT NOT NULL,
  CONSTRAINT fk_trv_wkg_code  FOREIGN KEY (trv_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,  
  CONSTRAINT uk_trv_code UNIQUE (trv_code, trv_trq_code,trv_wkg_code)
);


COMMENT ON COLUMN ref.tr_traitvaluequal_trv.trv_id IS 'Integer, id of the qualitative used';
COMMENT ON COLUMN ref.tr_traitvaluequal_trv.trv_code IS 'Code of the qualitative trait';
COMMENT ON COLUMN ref.tr_traitvaluequal_trv.trv_description IS 'Description of the method';

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

DROP TABLE IF EXISTS refeel.tr_traitvaluequal_trv CASCADE
  CONSTRAINT uk_trv_id UNIQUE (trv_id),
  CONSTRAINT fk_trv_trq_code 
  FOREIGN KEY (trv_trq_code)
    REFERENCES refeel.tr_traitqualitative_trq(tra_code)
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT uk_refeel_trv_code UNIQUE (trv_code, trv_trq_code)
) INHERITS (ref.tr_traitvaluequal_trv);


COMMENT ON COLUMN refeel.tr_traitvaluequal_trv.trv_id IS 'Integer, id of the qualitative used';
COMMENT ON COLUMN refeel.tr_traitvaluequal_trv.trv_code IS 'Code of the qualitative trait';
COMMENT ON COLUMN refeel.tr_traitvaluequal_trv.trv_description IS 'Description of the method';

GRANT ALL ON refeel.tr_traitvaluequal_trv TO diaspara_admin;
GRANT SELECT ON refeel.tr_traitvaluequal_trv TO diaspara_read;
Table 14: Table of possible qualitative trait values
Code to import trait values for qualitative parm. 
trv <- dbGetQuery(con_diaspara, "SELECT * FROM refeel.tr_traitvaluequal_trv;")
trq <- dbGetQuery(con_diaspara, "SELECT * FROM refeel.tr_traitqualitative_trq;")
#clipr::write_clip(colnames(trv))
trq <- data.frame(
  "trv_id"=1:(13+5*4+7),
  "trv_trq_code"=c(
    rep(trq$tra_code[1],13),
    rep(trq$tra_code[2],5),
    rep(trq$tra_code[3],5),
    rep(trq$tra_code[4],5),
    rep(trq$tra_code[5],5),
    rep(trq$tra_code[6],7))
  ,
  "trv_code"=c(c("VA","VB","VIA0","VIA1","VIA2","VIA3","VIA4","VIB","VII", "mix_VIA1_VIA4",
   "mix_VA_VB", "U","NA"),
   rep(c("N","NA","U","Y","P"),4),
   c("F","I","M","T","U","X","H")),
  "trv_description" = c(
  "No pigmentation except a spot on the caudal fin",
  "Early development of the pigmentation on the skull, no superficial piment beyond the cerebral spot.",
  "Development of dorsal pigmentation along the base of dorsal fin",
  "The dosal pigmentation is complete from head to tail",
  "Presence of medio lateral pigmentation but it does not reach the beginning of the dorsal fin",
  "The medio-lateral pigmentation reaches the beginning of the dorsal fin",
  "Ventro lateral pigmentation distributed along the myosepta. Pigments are still distinct.",
  "Pigments are no longer distinct in the ventro lateral region",
  "Loss of transparency, the abdominal cavity takes a silvery color. Generalised development of yellow eel pigment cells",
  "Development of surface and branchiostegal pigmentation",
  "No or early pigmentation",
  "Glass eels, but pigmentation stage is unknown",
  "Not available",
  rep(c("No","Not Applicable", "Unknown","Yes", "Probable"), 4),
  "Female", "Immature - attempt made but sex could not be destinguished",
  "Male", "Transitional", "Undetermined - no attempt made", "Mixed", "Hermaphrodite"
  ))
dbWriteTable(con_diaspara_admin, "tr_traitvaluequal_trv_temp", trq, overwrite =TRUE)
dbExecute(con_diaspara_admin, "DELETE FROM refeel.tr_traitvaluequal_trv");
dbExecute(con_diaspara_admin, "INSERT INTO refeel.tr_traitvaluequal_trv
SELECT 
trv_id,
trv_trq_code,
trv_code,
trv_description,
'WGEEL',
'ANG'
 FROM tr_traitvaluequal_trv_temp;") #40
Table 15: Values for qualitative parameters
trv_id trv_trq_code trv_code trv_description trv_spe_code trv_wkg_code
1 Pigment_stage VA No pigmentation except a spot on the caudal fin WGEEL ANG
2 Pigment_stage VB Early development of the pigmentation on the skull, no superficial piment beyond the cerebral spot. WGEEL ANG
3 Pigment_stage VIA0 Development of dorsal pigmentation along the base of dorsal fin WGEEL ANG
4 Pigment_stage VIA1 The dosal pigmentation is complete from head to tail WGEEL ANG
5 Pigment_stage VIA2 Presence of medio lateral pigmentation but it does not reach the beginning of the dorsal fin WGEEL ANG
6 Pigment_stage VIA3 The medio-lateral pigmentation reaches the beginning of the dorsal fin WGEEL ANG
7 Pigment_stage VIA4 Ventro lateral pigmentation distributed along the myosepta. Pigments are still distinct. WGEEL ANG
8 Pigment_stage VIB Pigments are no longer distinct in the ventro lateral region WGEEL ANG
9 Pigment_stage VII Loss of transparency, the abdominal cavity takes a silvery color. Generalised development of yellow eel pigment cells WGEEL ANG
10 Pigment_stage mix_VIA1_VIA4 Development of surface and branchiostegal pigmentation WGEEL ANG
11 Pigment_stage mix_VA_VB No or early pigmentation WGEEL ANG
12 Pigment_stage U Glass eels, but pigmentation stage is unknown WGEEL ANG
13 Pigment_stage NA Not available WGEEL ANG
15 Hva_presence NA Not Applicable WGEEL ANG
16 Hva_presence U Unknown WGEEL ANG
17 Hva_presence Y Yes WGEEL ANG
18 Hva_presence P Probable WGEEL ANG
20 Evex_presence NA Not Applicable WGEEL ANG
21 Evex_presence U Unknown WGEEL ANG
22 Evex_presence Y Yes WGEEL ANG
23 Evex_presence P Probable WGEEL ANG
25 Anguillicola_presence NA Not Applicable WGEEL ANG
26 Anguillicola_presence U Unknown WGEEL ANG
27 Anguillicola_presence Y Yes WGEEL ANG
28 Anguillicola_presence P Probable WGEEL ANG
30 Is_differentiated NA Not Applicable WGEEL ANG
31 Is_differentiated U Unknown WGEEL ANG
32 Is_differentiated Y Yes WGEEL ANG
33 Is_differentiated P Probable WGEEL ANG
34 Sex F Female WGEEL ANG
35 Sex I Immature - attempt made but sex could not be destinguished WGEEL ANG
36 Sex M Male WGEEL ANG
37 Sex T Transitional WGEEL ANG
38 Sex U Undetermined - no attempt made WGEEL ANG
39 Sex X Mixed WGEEL ANG
40 Sex H Hermaphrodite WGEEL ANG
14 Hva_presence N No WGEEL ANG
19 Evex_presence N No WGEEL ANG
29 Is_differentiated N No WGEEL ANG
24 Anguillicola_presence N No WGEEL ANG

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. Again these methods will be working group specific so we create an inherited table.

SQL code to create tables ref.tr_traitmethod_trm and refeel.tr_traitmethod_trm 
-- 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,
  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';



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

DROP TABLE IF EXISTS refeel.tr_traitmethod_trm;
CREATE TABLE refeel.tr_traitmethod_trm (
  CONSTRAINT uk_refeel_tm_id UNIQUE (trm_id),
  CONSTRAINT uk_refeel_tm_code UNIQUE (trm_code),
  CONSTRAINT fk_trm_wkg_code  FOREIGN KEY (trm_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_trm_spe_code  FOREIGN KEY (trm_spe_code)
  REFERENCES ref.tr_species_spe(spe_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT uk_trm_code UNIQUE (trm_code)
) INHERITS (ref.tr_traitmethod_trm);


COMMENT ON TABLE refeel.tr_traitmethod_trm IS 'Table of method used to obtain a trait metric';
COMMENT ON COLUMN refeel.tr_traitmethod_trm.trm_id IS 'Integer, id of the method used';
COMMENT ON COLUMN refeel.tr_traitmethod_trm.trm_code IS 'Name of the method used';
COMMENT ON COLUMN refeel.tr_traitmethod_trm.trm_wkg_code IS 'Working group code';
COMMENT ON COLUMN refeel.tr_traitmethod_trm.trm_spe_code IS 'Species code';
COMMENT ON COLUMN refeel.tr_traitmethod_trm.trm_description IS 'Description of the method';



GRANT ALL ON refeel.tr_traitmethod_trm TO diaspara_admin;
GRANT SELECT ON refeel.tr_traitmethod_trm TO diaspara_read;
Code to import traitmethod table. 
tra <- dbGetQuery(con_wgeel_local, "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))
traitmethod <- data.frame(
"trm_id" = 1:6,
"trm_tra_code"= c("Gonadal_inspection", "Length_based_sex", "Anguillicola_stereomicroscope_count", "Anguillicola_visual_count",
"Muscle_lipid_fatmeter", "Muscle_lipid_gravimeter"),
"trm_wkg_code" = rep('WGEEL', 6),
"trm_spe_code" = rep('ANG',6),
"trm_description" = c(
  "The eel is dissected and the gonads are inpected, In males, the testes  appear as thin, ribbon-like, whitish structures. In females, the ovaries are larger, lobed, and more granular, often yellowish or pinkish depending on maturity. Many eels pass through an intersexual phase (Here refered as Mixed to align with ICES Vocab), where gonads show both ovarian and testicular tissue. This is part of their natural development",
  "The size at silvering of eels depends on the sex. Males are found within the 25-45 cm range, and females are found over 45 cm. There is an overlap of sexes arround ",
  "Anguillicola count using a dissecting microscope, this allows to detect early-stage infections or small larvae",
  "Anguillicola visual count",
  "Non invasive estimation of the fat content by measuring the dielectric properties of tissues, this method requires a calibration.",
  "Gravimeter, muscle tissues are dried and the lipids are extracted using solvents, the extracted fat is dried and weighted")
)

dbWriteTable(con_diaspara_admin, "tr_traitmethod_trm_temp",traitmethod, overwrite =TRUE)
dbExecute(con_diaspara_admin, "DELETE FROM refeel.tr_traitmethod_trm")
dbExecute(con_diaspara_admin, "INSERT INTO refeel.tr_traitmethod_trm 
SELECT 
trm_id,
trm_tra_code,
trm_wkg_code,
trm_spe_code,
trm_description
 FROM tr_traitmethod_trm_temp") #6
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 16: Trait table, check how this will be imported …
trm_id trm_code trm_wkg_code trm_spe_code trm_description
1 Gonadal_inspection WGEEL ANG The eel is dissected and the gonads are inpected, In males, the testes appear as thin, ribbon-like, whitish structures. In females, the ovaries are larger, lobed, and more granular, often yellowish or pinkish depending on maturity. Many eels pass through an intersexual phase (Here refered as Mixed to align with ICES Vocab), where gonads show both ovarian and testicular tissue. This is part of their natural development
2 Length_based_sex WGEEL ANG The size at silvering of eels depends on the sex. Males are found within the 25-45 cm range, and females are found over 45 cm. There is an overlap of sexes arround
3 Anguillicola_stereomicroscope_count WGEEL ANG Anguillicola count using a dissecting microscope, this allows to detect early-stage infections or small larvae
5 Muscle_lipid_fatmeter WGEEL ANG Non invasive estimation of the fat content by measuring the dielectric properties of tissues, this method requires a calibration.
6 Muscle_lipid_gravimeter WGEEL ANG Gravimeter, muscle tissues are dried and the lipids are extracted using solvents, the extracted fat is dried and weighted
4 Anguillicola_visual_count WGEEL ANG Anguillicola visual count

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 EWEUNU~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())
user     system      spent
 #    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")
Code to import station 
# tested 28/05/2025

library(icesStation)
system.time(
station <- getListStation())
user     system      spent
 #    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")
Comment on Station import

Even though a table station exists in WGEEL, it really contains no actual code from ICES. To insert data into stations, the data providers will have to check that they have an EDMO code, and then we will have to bulk load stations.

Creating the version table refeel.tr_version_ver

SQL code to create table refeel.tr_version_ver 
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 17: 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 for series

SQL code to create table dat.t_series_ser 
--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 'Unit for the value reported in annual table, if the series reports annual data in Kg or Number use kg or number.';
COMMENT ON COLUMN dat.t_series_ser.ser_effort_uni_code IS 'Annual data collection effort unit code, used for the effort column in annual data';
COMMENT ON COLUMN dat.t_series_ser.ser_description IS '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 series

SQL code to create table dateel.t_series_ser 
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, short name of the recuitment series, this must be 4 letters + stage name, e.g. VilG, LiffGY, FremS for recruitment or silver eel series the first letter is capitalised and the stage name too. For sampling use country + name + (currently BIOM or HIST) for Biometry or Historic dataset, e.g. ES_Anda_Aguas_BIOM';
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 from ref.tr_version_ver the data call e.g. WGNAS_2020_1, WGEEL_2016_1';
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  '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" ~ "WGEEL-2019-1",
                              sai$sai_dts_datasource =="dc_2021" ~ "WGEEL-2021-1",
                              sai$sai_dts_datasource =="dc_2022" ~ "WGEEL-2022-1",
                              sai$sai_dts_datasource =="dc_2023" ~ "WGEEL-2023-1",
                              sai$sai_dts_datasource =="dc_2024" ~ "WGEEL-2024-1",
                              .default = "WGEEL-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
Table 18: Table t_series_ser for series first 20 lines
ser_id 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 geom
2445a12b-f5b9-4c8e-aa83-dcecc9e92717 31 ES_Astu_Esva _HIST ANG NA NA WGEEL WGEEL-2018-1 ES NA NA NA NA NA NA historical data Esva NA NA NA NA NA NA NA 2022-08-29 NA
95259d08-b9cf-4936-8484-0027c9c2d989 33 GB_Seve_Severn_HIST ANG NA NA WGEEL WGEEL-2018-1 GB NA NA NA NA NA NA historical data Severn NA NA NA NA NA NA NA 2022-08-29 NA
379b8d44-0251-4d00-82bd-b2212b6913ad 34 GB_Scot_Girnock_Burn_Scotland_HIST ANG NA NA WGEEL WGEEL-2018-1 GB NA NA NA NA NA NA historical data Girnock_Burn_Scotland NA NA NA NA NA NA NA 2022-08-29 NA
7e4fd77c-cf99-4eff-9c88-9d6d92b7242e 37 IE_West_Burrishoole_HIST ANG NA NA WGEEL WGEEL-2018-1 IE NA NA NA NA NA NA historical data Burrishoole NA NA NA NA NA NA NA 2022-08-29 NA
b0354939-8b73-4b52-8b8c-2b1f95119950 40 NL_Neth_Ijsselmeer_HIST ANG NA NA WGEEL WGEEL-2018-1 NL NA NA NA NA NA NA historical data Ijsselmeer NA NA NA NA NA NA NA 2022-08-29 NA
2e1d59f5-a843-4cc0-85e7-8a6f5b204014 43 NA_Balaton_HIST ANG NA NA WGEEL WGEEL-2018-1 NA NA NA NA NA NA NA historical data Balaton NA NA NA NA NA NA NA 2022-08-29 NA
d98b022b-df0e-442b-98e8-a998265eb38c SuSY Suffolk Stour Catchment ANG Y GB_Angl WGEEL WGEEL-2022-1 GB NA 10.4 NA NA nr/m2 nr electrofishing Index river estimates of eel density per year from pooled data taken from all quantitative surveys (all electrofishing) in that river. Any surveys that have 'Not recorded' as catch or sample method are excluded, as are all fyke net or other fixed instrument data and data with no area of fishing provided. There has been no major stocking in this EMU since 2013 so it is considered as not affected by stocking. Ser_distance was reported as a mean distance of 125 out of 141 surveyed sites. The sites with available data were relatively evenly distributed along the length of river, so seem representative enough. When new data become available this will be updated accordingly. river or catchment average FW FALSE NA Yellow eel sampled by hand-held electric fishing gear either single catch or catch depletion sampling across multiple sites. Sampling carried out throughout the year with main effort from June to September NA NA 2025-06-09 0101000020E6100000C3651536031CEC3FB24CBF44BCFB4940
fcadb13e-2fd3-4392-b622-8ed610cdb980 ImsaGY Imsa Near Sandnes trapping all ANG GY NO_total WGEEL WGEEL-2022-1 NO NA 08.9 NA NA nr NA The stage is not really glass eel but elver Near Sandnes FW FALSE NA glass eel ladder NA NA 2025-06-09 0101000020E61000005C8FC2F5285C164085EB51B81E454D40
72854801-b3fa-4b81-8827-eb4eda137c43 LeeY Lee River ANG Y GB_Tham WGEEL WGEEL-2022-1 GB NA 10.4 NA NA nr/m2 nr electrofishing Index river estimates of eel density per year from pooled data taken from all quantitative surveys (all electrofishing) in that river. Any surveys that have 'Not recorded' as catch or sample method are excluded, as are all fyke net or other fixed instrument data and data with no area of fishing provided. There has been no stocking in this EMU since 2013 so it is considered as not affected by stocking. Ser_distance was reported as a mean distance of 96 out of 143 surveyed sites. The sites with available data were relatively evenly distributed along the length of river, so seem representative enough. When new data become available this will be updated accordingly. river or catchment average FW FALSE NA Yellow eel sampled by hand-held electric fishing gear either single catch or catch depletion sampling across multiple sites. Sampling carried out throughout the year with main effort from June to September NA NA 2025-06-09 0101000020E610000092B9E81A1CDB91BF8675E3DD91D54940
31cb9a15-7b2a-46e4-887c-93673d9cd859 FneS Fane ANG S IE_East WGEEL WGEEL-2024-1 IE NA 08.9 NA NA kg nr net.night Fane is the site of former commercial eel fishing; it is located on the outflow of Lough Muckno Fane FW FALSE NA Coghill nets on frame NA NA 2025-06-09 0101000020E61000002288F37002B31AC088821953B00C4B40
04a2dfd7-8444-4ba4-afe7-8a520c12df8e BarS Barrow ANG S IE_East WGEEL WGEEL-2024-1 IE NA 08.9 NA NA kg nr net.night The barrow site was a former commercial eel fishing location, it is located above the tidal limit on the River Barrow Barrow FW FALSE NA Coghill nets on canal lock gates NA NA 2025-06-09 0101000020E6100000733ADECF04E21BC0BEF651828F4D4A40
1a6d2324-2d94-42d2-b3e4-4a65aa5ae98d DeBY Den Burg fyke net survey ANG Y NL_Neth WGEEL WGEEL-2024-1 NL NA 08.3 NA NA index index fyke net survey updated 2011 / new data series og fyke net monitoruing Den Burg fyke net (CPUE) MO TRUE NA fyke net survey updated 2011 / new data series og fyke net monitoruing. All eel (yellow and silver, but that is no option in ser_lfs_code) NA NA 2025-06-09 0101000020E61000007CED9925010A13405721E527D57E4A40
2d8f9e41-f487-4064-b7f1-6a21b084e434 IJsFVY FYOE-IJM-Veg ANG Y NL_Neth WGEEL WGEEL-2024-1 NL NA 10.4 NA NA index index Fishery indepent survey on shores in lake ijsselmeer with electro fishing net in august/september Lake ijsselmeer FW TRUE NA All eel, but almost all is Yellow eel. Fishery indepent survey on shores in lake ijsselmeer with electro fishing net in august/september NA NA 2025-06-09 0101000020E6100000AE4A22FB204B15409F7422C154694A40
d8b66f7a-c834-4b1b-b423-6a43d3b7c06a IJsFRY FYOE-IJM-Rock ANG Y NL_Neth WGEEL WGEEL-2024-1 NL NA 10.4 NA NA index index Fishery indepent survey on shores in lake ijsselmeer with electro fishing net in august/september Lake ijsselmeer FW TRUE NA All eel, but most is Yellow eel. Fishery indepent survey on shores in lake ijsselmeer with electro fishing net in august/september NA NA 2025-06-09 0101000020E6100000AE4A22FB204B15409F7422C154694A40
5b3f954e-028b-4619-a880-90410c9a88bd 44 NA_Corrib_HIST ANG NA NA WGEEL WGEEL-2018-1 NA NA NA NA NA NA NA historical data Corrib NA NA NA NA NA NA NA 2022-08-29 NA
4e2ad4f6-6b1b-4321-b370-1c133cbbb53e 45 ES_Anda_Guadalquivir _HIST ANG NA NA WGEEL WGEEL-2018-1 ES NA NA NA NA NA NA historical data Guadalquivir NA NA NA NA NA NA NA 2022-08-29 NA
dd1a2f8c-b874-4d47-be39-b3fde0465746 46 IE_NorW_Erne_HIST ANG NA NA WGEEL WGEEL-2018-1 IE NA NA NA NA NA NA historical data Erne NA NA NA NA NA NA NA 2022-08-29 NA
867aa8e7-632d-4406-b70e-ad60d208f3d1 AlCS Albufera lagoon commercial catch ANG S ES_Vale WGEEL WGEEL-2022-1 ES NA NA NA NA kg kg NA Albufera de Valencia in the Mediterranean. The Albufera de València is a lagoon located to the south of the city of Valencia, next to the Mediterranean Sea. With a surface area of 3,000 hectares of fresh water, it is fed mainly by the waters of the river Júcar, the Poyo ravine and a multitude of springs. T FALSE NA The administration of C. Valenciana compiles data from the different fishing points in the Albufera. Catches are sampled for biometrics. The arithmetic mean of the values corresponding to the catches of 23/11/2020, 29/12/2020 and 11/01/2021 has been calculated. NA NA 2025-06-09 0101000020E6100000333333333333D3BF48E17A14AEA74340
12fd691f-660b-44a3-99bf-2dab1861cdf5 WenY Wensum Catchment ANG Y GB_Angl WGEEL WGEEL-2022-1 GB NA 10.4 NA NA nr/m2 nr electrofishing Index river estimates of eel density per year from pooled data taken from all quantitative surveys (all electrofishing) in that river. Any surveys that have 'Not recorded' as catch or sample method are excluded, as are all fyke net or other fixed instrument data and data with no area of fishing provided. There has been no major stocking in this EMU since 2013 so it is considered as not affected by stocking. Ser_distance was reported as a mean distance of 105 out of 138 surveyed sites. The sites with available data were relatively evenly distributed along the length of river, so seem representative enough. When new data become available this will be updated accordingly. river or catchment average FW FALSE NA Yellow eel sampled by hand-held electric fishing gear either single catch or catch depletion sampling across multiple sites. Sampling carried out throughout the year with main effort from June to September NA NA 2025-06-09 0101000020E6100000BE89213999F8F33F80F10C1AFA514A40
2a9ec13c-7a85-497c-bde6-6da0b6c9f057 NalS Nalon river basin ANG S ES_Astu WGEEL WGEEL-2024-1 ES NA 10.4 NA NA nr/m2 nr electrofishing Double-pass electric fishing sampling without replacement, using the SEBER and LECREN method (1967), based on the successive catches of DE LURY (1947). The estimation of eel abundance is calculated using the aritmetic mean of 4 sampling points . For the biometries the aritmetic mean of all the eels in these stations haa been used. Nalon river and Narcea tributary. Flows to the Biscay Gulf,Atlantic ocean. Length of the river 217,2 km (101 km Narcea) Area of the basin 4892 km2 Length of the estuary 12 km Number of barriers 10 dams and 7 weirs FW |FALSE |NA |Electrofishing operations are performed during autum, from september to october.Double-pass electric fishing sampling without replacement, using the SEBER and LECREN method (1967), based on the successive catches of DE LURY (1947). The estimation of eel abundance is calculated using the aritmetic mean of 4 sampling points . For the biometries the aritmetic mean of all the eels in these stations haa been used. |NA |NA |202 -06-09 |0101000020E61000002F1C430E6BAB18C02

Annual series table

This table holds annual data from the series.

SQL code to create table dat.t_serannual_san 
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 ofWGBAST, 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. WGNAS_2020_1, WGEEL_2016_1';


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 for annual series

SQL code to create tables dateel.t_serannual_san 
-- 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 data for annual series

Data are imported from WGEEL datawg.t_dataseries_dastable to dateel.t_serannual_ser.

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
Table 19: Table t_serannual_san for annual values first 20 lines
san_ser_id san_id san_value san_year san_comment san_effort san_datelastupdate san_qal_id san_qal_comment san_wkg_code san_ver_code
9e8e2e9d-8ed6-4a61-ac83-d9249370d9e5 2837 3.820000 2016 Change 2017 3,82 -> 4,279. Das_qal_id added in 2022. NA 2022-09-09 1 NA WGEEL WGEEL-2017-1
9910419d-ee03-45ab-b4ea-409d72aa505b 2925 120.800000 2017 Additional new traps captured a further 15 kg NA 2022-09-08 1 NA WGEEL WGEEL-2017-1
4e662794-26bc-4da5-90d5-6e47a1455a59 2926 0.454000 2012 NA NA 2022-09-08 1 NA WGEEL WGEEL-2017-1
4e662794-26bc-4da5-90d5-6e47a1455a59 2927 1.144000 2013 NA NA 2022-09-08 1 NA WGEEL WGEEL-2017-1
4e662794-26bc-4da5-90d5-6e47a1455a59 2928 0.311000 2014 NA NA 2022-09-08 1 NA WGEEL WGEEL-2017-1
4e662794-26bc-4da5-90d5-6e47a1455a59 2929 0.159000 2015 NA NA 2022-09-08 1 NA WGEEL WGEEL-2017-1
4e662794-26bc-4da5-90d5-6e47a1455a59 2930 0.360000 2016 NA NA 2022-09-08 1 NA WGEEL WGEEL-2017-1
4e662794-26bc-4da5-90d5-6e47a1455a59 2931 0.518000 2017 Trap refurbished, glass eel fell, young yellow eel increased NA 2022-09-08 1 NA WGEEL WGEEL-2017-1
6846b849-d11a-4eef-903d-39e7e30c503a 2932 0.720000 1987 NA NA 2022-09-08 1 NA WGEEL WGEEL-2017-1
6846b849-d11a-4eef-903d-39e7e30c503a 2933 14.480000 1988 NA NA 2022-09-08 1 NA WGEEL WGEEL-2017-1
6846b849-d11a-4eef-903d-39e7e30c503a 2934 0.259000 2007 NA NA 2022-09-08 1 NA WGEEL WGEEL-2017-1
3ca798cf-9474-48a0-b742-2756aca6df3c 1061 4283.000000 1997 NA NA 2022-09-12 1 NA WGEEL WGEEL-2016-1
56760cce-14d6-414b-98d0-871bd8a64384 1777 9453.000000 1960 NA NA 2022-09-12 1 NA WGEEL WGEEL-2016-1
0d83cf08-df7a-4853-95d8-cbdc18cde5f2 632 36.700000 1989 NA NA 2022-09-09 1 NA WGEEL WGEEL-2016-1
5534b8e4-ced3-4655-b5b7-8252cf01cfe0 680 121.000000 1952 NA NA 2022-09-09 1 NA WGEEL WGEEL-2016-1
5534b8e4-ced3-4655-b5b7-8252cf01cfe0 690 185.000000 1962 NA NA 2022-09-09 1 NA WGEEL WGEEL-2016-1
5534b8e4-ced3-4655-b5b7-8252cf01cfe0 695 258.000000 1967 NA NA 2022-09-09 1 NA WGEEL WGEEL-2016-1
caa6d368-37a5-4093-9585-bc303c769c14 2160 25.200000 1985 number of hauls =6 6 2022-09-09 1 NA WGEEL WGEEL-2016-1
4ae8d33f-3808-4258-a31f-7bf1f1233a8a 5440 115.000000 2013 Das_qal_id added in 2022. NA 2022-09-09 1 NA WGEEL WGEEL-2019-1
11e4ae1e-c7a8-4ab1-a080-18545786886d 4537 0.152542 1991 NA NA 2022-09-08 1 NA WGEEL WGEEL-2019-1

Creating table joining series and station

The table is created but empty. We need the stations in ICES first.

SQL code to create table dat.tj_seriesstation_ses 
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 joining series and station

SQL code to create table dateel.tj_seriesstation_ses 
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 additional table for stock annex

Creating table t_recruitmentmetadata

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 are currently not transfered.

SQL code to create table dateel.t_recruitmentmetadata_met 
 -- 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;
Table 20: Table t_recruitmentmetadata_met for additional values collected during wkeeldata
met_ser_id met_sam_id met_emu_nameshort met_qal_id met_mixturegy met_wetted_above met_wetted_total met_surfacebasin_upstream met_surfacebasin met_distanceseakm met_marinearea met_start_year met_end_year met_duration met_missing met_individual_length met_individual_mass met_conversion met_conversion_comment met_raw_to_reported met_internal_issues met_internal_issues_comment met_other met_other_comment met_restocking met_restocking_comment met_barrier met_barrier_comment met_mortality met_mortality_comment met_ext_issues met_ext_issues_comment met_environment met_environment_comment
NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA NA
:---------- ----------: :----------------- ----------: :------------- ----------------: ----------------: -------------------------: ----------------: -----------------: :-------------- --------------: ------------: ------------: -----------: :--------------------- :------------------- :-------------- :---------------------- :------------------- :------------------- :--------------------------- :--------- :----------------- :-------------- :---------------------- :----------- :------------------- :------------- :--------------------- :-------------- :---------------------- :--------------- :-----------------------

Creating group metrics

Create group table

Note for group metrics

Need a foreign key on both species and lfs code.

SQL code to create table dat.t_group_gr 

-- DROP TABLE dat.t_group_gr CASCADE;

CREATE TABLE dat.t_group_gr (
  gr_id serial4 NOT NULL,
  gr_ser_id UUID NOT NULL,
  CONSTRAINT fk_gr_ser_id FOREIGN KEY (gr_ser_id)
  REFERENCES dat.t_series_ser (ser_id) 
  ON UPDATE CASCADE ON DELETE CASCADE, 
  gr_gr_id INTEGER NULL,
  CONSTRAINT fk_gr_gr_id  FOREIGN KEY (gr_gr_id, gr_wkg_code)
  REFERENCES dat.t_group_gr(gr_id, gr_wkg_code)
  ON UPDATE CASCADE ON DELETE CASCADE,
  gr_wkg_code TEXT NOT NULL,  
  CONSTRAINT fk_gr_wkg_code  FOREIGN KEY (gr_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT t_group_gr_pkey PRIMARY KEY (gr_id, gr_wkg_code),
  gr_spe_code TEXT,
  CONSTRAINT fk_gr_spe_code FOREIGN KEY (gr_spe_code) 
  REFERENCES "ref".tr_species_spe(spe_code) 
  ON UPDATE CASCADE ON DELETE RESTRICT ,
  gr_lfs_code TEXT,
  CONSTRAINT fk_gr_lfs_code_gr_spe_code FOREIGN KEY (gr_lfs_code, gr_spe_code)
  REFERENCES "ref".tr_lifestage_lfs (lfs_code, lfs_spe_code) 
  ON UPDATE CASCADE ON DELETE RESTRICT, 
  gr_sex_code TEXT,
  CONSTRAINT ck_nn_gr_sex_code_gr_gr_id CHECK ((gr_gr_id IS NULL AND gr_sex_code IS NULL) OR (gr_gr_id IS NOT NULL AND gr_sex_code IS NOT NULL)),
  CONSTRAINT ck_gr_sex_code CHECK (gr_sex_code = 'M' OR gr_sex_code = 'F' OR gr_sex_code IS NULL),
  gr_year int4 NULL,
  gr_number int4 NULL,
  gr_comment text NULL,
  gr_lastupdate date DEFAULT CURRENT_DATE NOT NULL,
  gr_ver_code TEXT NOT NULL,
  CONSTRAINT fk_gr_ver_code FOREIGN KEY (gr_ver_code)
  REFERENCES ref.tr_version_ver(ver_code)
  ON UPDATE CASCADE ON DELETE RESTRICT
);

COMMENT ON TABLE dat.t_group_gr IS 'Table identifying the group metrics, a group metric corresponds to a
number of fish sampled for a given year, mostly to describe the annual series. Comments can be made
on the sampling with gr_comments. There can be several group metrics for the same year, for instance
with sampling designs for different stages';

COMMENT ON COLUMN dat.t_group_gr.gr_id IS 'Group ID, serial primary key on gr_id and gr_wkg_code';
COMMENT ON COLUMN dat.t_group_gr.gr_gr_id IS 'Parent group ID, used when giving separate metrics for male and females, in that case the gr_sex_code must be provided';
COMMENT ON COLUMN dat.t_group_gr.gr_wkg_code IS 'Code of the working group, one of
WGBAST, WGEEL, WGNAS, WKTRUTTA';
COMMENT ON COLUMN dat.t_group_gr.gr_lfs_code IS 'Life stage code';
COMMENT ON COLUMN dat.t_group_gr.gr_spe_code IS 'Species code';
COMMENT ON COLUMN dat.t_group_gr.gr_sex_code IS 'Sex code only for subgroups male or female';
COMMENT ON COLUMN dat.t_group_gr.gr_year IS 'The year';
COMMENT ON COLUMN dat.t_group_gr.gr_number IS 'Number of fish in the group';
COMMENT ON COLUMN dat.t_group_gr.gr_comment IS 'Comment on the group metric, including on the sampling design applied to that particular year, if different from that applied for the whole series.';
COMMENT ON COLUMN dat.t_group_gr.gr_lastupdate IS 'Last update, inserted automatically';
COMMENT ON COLUMN dat.t_group_gr.gr_ver_code IS 'Version code as in tr_version_ver, corresponds to the working group code WGNAS-2024-1 WGEEL-2016-1, the -1 indicate the first data call in the year, -2 would be second etc....';


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

Create group table for dateel

SQL code to create table dateel.t_group_gr 

-- DROP TABLE IF EXISTS dateel.t_group_gr;

CREATE TABLE dateel.t_group_gr (
 CONSTRAINT fk_gr_ser_id FOREIGN KEY (gr_ser_id)
  REFERENCES dateel.t_series_ser (ser_id) 
  ON UPDATE CASCADE ON DELETE CASCADE, 
  CONSTRAINT fk_gr_gr_id  FOREIGN KEY (gr_gr_id, gr_wkg_code)
  REFERENCES dateel.t_group_gr(gr_id, gr_wkg_code)
  ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_gr_wkg_code  FOREIGN KEY (gr_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT t_group_gr_pkey PRIMARY KEY (gr_id, gr_wkg_code), 
  CONSTRAINT fk_gr_ver_code FOREIGN KEY (gr_ver_code)
  REFERENCES refeel.tr_version_ver(ver_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_gr_lfs_code_gr_spe_code FOREIGN KEY (gr_lfs_code, gr_spe_code)
  REFERENCES "ref".tr_lifestage_lfs (lfs_code, lfs_spe_code) 
  ON UPDATE CASCADE ON DELETE RESTRICT, 
   CONSTRAINT fk_gr_spe_code FOREIGN KEY (gr_spe_code) 
  REFERENCES "ref".tr_species_spe(spe_code) 
  ON UPDATE CASCADE ON DELETE RESTRICT 
) INHERITS (dat.t_group_gr);


COMMENT ON TABLE dateel.t_group_gr IS 'Table identifying the group metrics, a group metric corresponds to a
number of fish sampled for a given year, mostly to describe the annual series. Comments can be made
on the sampling with gr_comments. There can be several group metrics for the same year, for instance
with sampling designs for different stages';

COMMENT ON COLUMN dateel.t_group_gr.gr_id IS 'Group ID, serial primary key on gr_id and gr_wkg_code';
COMMENT ON COLUMN dat.t_group_gr.gr_gr_id IS 'Parent group ID, used when giving separate metrics for male and females, in that case the gr_sex_code must be provided';
COMMENT ON COLUMN dateel.t_group_gr.gr_wkg_code IS 'Code of the working group, one of
WGBAST, WGEEL, WGNAS, WKTRUTTA';
COMMENT ON COLUMN dateel.t_group_gr.gr_year IS 'The year';
COMMENT ON COLUMN dateel.t_group_gr.gr_number IS 'Number of fish in the group';
COMMENT ON COLUMN dateel.t_group_gr.gr_comment IS 'Comment on the group metric, including on the sampling design applied to that particular year, if different from that applied for the whole series.';
COMMENT ON COLUMN dateel.t_group_gr.gr_lastupdate IS 'Last update, inserted automatically';
COMMENT ON COLUMN dateel.t_group_gr.gr_ver_code IS 'Version code as in tr_version_ver, corresponds to the working group code WGNAS-2024-1 WGEEL-2016-1, the -1 indicate the first data call in the year, -2 would be second etc....';
COMMENT ON COLUMN dat.t_group_gr.gr_lfs_code IS 'Life stage code';
COMMENT ON COLUMN dat.t_group_gr.gr_spe_code IS 'Species code';
COMMENT ON COLUMN dat.t_group_gr.gr_sex_code IS 'Sex code only for subgroups male or female';
GRANT ALL ON dateel.t_group_gr TO diaspara_admin;
GRANT SELECT ON dateel.t_group_gr TO diaspara_read;

Import data for group

Scripts differ a bit for series and sampling. Check for some series with comment so see if need removing.

all related metrics have qal_id=22 following data call 2022

SQL code to Import data 
/*
SELECT DISTINCT gr_dts_datasource 
FROM datwgeel.t_groupseries_grser
*/

DELETE FROM dateel.t_group_gr;
INSERT INTO dateel.t_group_gr
(gr_id, gr_ser_id, gr_gr_id, gr_wkg_code, gr_spe_code, gr_lfs_code, gr_year, gr_number, gr_comment, gr_lastupdate, gr_ver_code)
SELECT 
gr_id,
tss2.ser_id ,
NULL AS gr_gr_id,
'WGEEL' AS gr_wkg_code,
'ANG' AS gr_spe_code,
tss.ser_lfs_code AS gr_lfs_code,
gr_year,
gr_number,
gr_comment,
gr_lastupdate,
CASE WHEN gr_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN gr_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN gr_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN gr_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN gr_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN gr_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS gr_ver_code
FROM  datwgeel.t_groupseries_grser grser
JOIN datwgeel.t_series_ser AS tss ON grser_ser_id = ser_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= ser_nameshort; --2681



INSERT INTO dateel.t_group_gr
(gr_id, gr_ser_id, gr_gr_id, gr_wkg_code,  gr_spe_code, gr_lfs_code, gr_year, gr_number, gr_comment, gr_lastupdate, gr_ver_code)
SELECT 
gr_id, 
tss2.ser_id  AS gr_ser_id,
NULL AS gr_gr_id,
'WGEEL' AS gr_wkg_code,
'ANG' AS gr_spe_code,
grsa_lfs_code AS gr_lfs_code,
gr_year,
gr_number,
gr_comment,
gr_lastupdate,
CASE WHEN gr_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN gr_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN gr_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN gr_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN gr_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN gr_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS gr_ver_code
FROM  datwgeel.t_groupsamp_grsa 
JOIN datwgeel.t_samplinginfo_sai AS tss ON grsa_sai_id = sai_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= sai_id::text; --798




-- INSERT Males AND Females WITH gr_gr_id

DROP SEQUENCE IF EXISTS seq_group;
CREATE TEMPORARY SEQUENCE seq_group;
SELECT setval('seq_group', (SELECT max(gr_id) FROM dateel.t_group_gr)); -- 13741

-- Males with gr_id from series (subgroupsample)

INSERT INTO dateel.t_group_gr
(gr_id, 
gr_ser_id, 
gr_gr_id, 
gr_wkg_code,  
gr_spe_code, 
gr_sex_code, 
gr_lfs_code, 
gr_year, 
gr_number, 
gr_comment, 
gr_lastupdate,
gr_ver_code)
SELECT 
nextval('seq_group') AS gr_id, 
tss2.ser_id  AS gr_ser_id,
grser.gr_id AS gr_gr_id,
'WGEEL' AS gr_wkg_code,
'ANG' AS gr_spe_code,
'M' AS gr_sex_code,
tss.ser_lfs_code AS gr_lfs_code,
gr_year,
gr_number,
gr_comment,
gr_lastupdate,
CASE WHEN gr_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN gr_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN gr_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN gr_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN gr_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN gr_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS gr_ver_code
FROM  
datwgeel.t_metricgroupseries_megser 
JOIN datwgeel.t_groupseries_grser grser ON meg_gr_id = gr_id
JOIN datwgeel.t_series_ser AS tss ON grser_ser_id = ser_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= ser_nameshort
WHERE meg_mty_id IN (18,19,20); --409

# Females WITH gr_gr_id series (subgroupsample)


INSERT INTO dateel.t_group_gr
(gr_id, 
gr_ser_id, 
gr_gr_id, 
gr_wkg_code,  
gr_spe_code, 
gr_sex_code, 
gr_lfs_code, 
gr_year, 
gr_number, 
gr_comment, 
gr_lastupdate,
gr_ver_code)
SELECT 
nextval('seq_group') AS gr_id, 
tss2.ser_id  AS gr_ser_id,
grser.gr_id AS gr_gr_id,
'WGEEL' AS gr_wkg_code,
'ANG' AS gr_spe_code,
'F' AS gr_sex_code,
tss.ser_lfs_code AS gr_lfs_code,
gr_year,
gr_number,
gr_comment,
gr_lastupdate,
CASE WHEN gr_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN gr_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN gr_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN gr_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN gr_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN gr_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS gr_ver_code
FROM  
datwgeel.t_metricgroupseries_megser 
JOIN datwgeel.t_groupseries_grser grser ON meg_gr_id = gr_id
JOIN datwgeel.t_series_ser AS tss ON grser_ser_id = ser_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= ser_nameshort
WHERE meg_mty_id IN (21,22,23); --553

# males from sampling


INSERT INTO dateel.t_group_gr
(gr_id, 
gr_ser_id, 
gr_gr_id, 
gr_wkg_code,  
gr_spe_code, 
gr_sex_code, 
gr_lfs_code, 
gr_year, 
gr_number, 
gr_comment, 
gr_lastupdate,
gr_ver_code)
SELECT 
nextval('seq_group') AS gr_id, 
tss2.ser_id  AS gr_ser_id,
grsa.gr_id AS gr_gr_id,
'WGEEL' AS gr_wkg_code,
'ANG' AS gr_spe_code,
'M' AS gr_sex_code,
grsa_lfs_code,
gr_year,
gr_number,
gr_comment,
gr_lastupdate,
CASE WHEN gr_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN gr_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN gr_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN gr_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN gr_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN gr_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS gr_ver_code
FROM  
datwgeel.t_metricgroupsamp_megsa 
JOIN datwgeel.t_groupsamp_grsa  grsa ON meg_gr_id = gr_id
JOIN datwgeel.t_samplinginfo_sai AS tss ON grsa_sai_id = sai_id
JOIN dateel.t_series_ser AS tss2 ON ser_code=  sai_id::text
WHERE meg_mty_id IN (18,19,20); --544

# Females from sampling


INSERT INTO dateel.t_group_gr
(gr_id, 
gr_ser_id, 
gr_gr_id, 
gr_wkg_code,  
gr_spe_code, 
gr_sex_code, 
gr_lfs_code, 
gr_year, 
gr_number, 
gr_comment, 
gr_lastupdate,
gr_ver_code)
SELECT 
nextval('seq_group') AS gr_id, 
tss2.ser_id  AS gr_ser_id,
grsa.gr_id AS gr_gr_id,
'WGEEL' AS gr_wkg_code,
'ANG' AS gr_spe_code,
'F' AS gr_sex_code,
grsa_lfs_code AS gr_lfs_code,
gr_year,
gr_number,
gr_comment,
gr_lastupdate,
CASE WHEN gr_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN gr_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN gr_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN gr_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN gr_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN gr_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS gr_ver_code
FROM  
datwgeel.t_metricgroupsamp_megsa 
JOIN datwgeel.t_groupsamp_grsa  grsa ON meg_gr_id = gr_id
JOIN datwgeel.t_samplinginfo_sai AS tss ON grsa_sai_id = sai_id
JOIN dateel.t_series_ser AS tss2 ON ser_code=  sai_id::text
WHERE meg_mty_id IN (21,22,23); --1183
Table 21: Table t_group_gr, group related to annual values first 20 lines
gr_id gr_ser_id gr_gr_id gr_wkg_code gr_spe_code gr_lfs_code gr_sex_code gr_year gr_number gr_comment gr_lastupdate gr_ver_code
690 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2018 233 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
689 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2017 49 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
688 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2016 163 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
687 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2015 228 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
686 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2014 136 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
685 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2013 33 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
684 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2012 261 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
683 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2011 23 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
682 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2010 172 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
681 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2009 169 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
680 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2008 33 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
679 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2007 51 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
678 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2006 434 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
677 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2005 17 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
676 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2004 30 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
675 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2003 199 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
674 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2002 31 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
13631 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2021 22 Note weight was not measured but estimated from L-W relationship 2022-09-09 WGEEL-2022-1
1194 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2019 21 gr_number added in 2022; Note weight was not measured but estimated from L-W relationship 2022-09-13 WGEEL-2022-1
4023 d98b022b-df0e-442b-98e8-a998265eb38c NA WGEEL ANG Y NA 2022 112 NA 2023-09-04 WGEEL-2023-1

Grouptrait table

Create table

SQL code to create tables dat.t_grouptrait_grt and dateel.t_grouptrait_grt 

-- DROP TABLE IF EXISTS dat.t_grouptrait_grt CASCADE;


CREATE TABLE dat.t_grouptrait_grt (
  grt_ser_id uuid,
 CONSTRAINT fk_grt_ser_id FOREIGN KEY (grt_ser_id)
    REFERENCES dat.t_series_ser (ser_id) 
    ON UPDATE CASCADE ON DELETE CASCADE,  
  grt_wkg_code TEXT NOT NULL,  
  CONSTRAINT fk_grt_wkg_code  FOREIGN KEY (grt_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  grt_spe_code TEXT NOT NULL,  
  CONSTRAINT fk_grt_spe_code  FOREIGN KEY (grt_spe_code)
  REFERENCES ref.tr_species_spe(spe_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  grt_id serial4 NOT NULL,
  grt_gr_id int4 NOT NULL,
  CONSTRAINT fk_grt_gr_id FOREIGN KEY (grt_gr_id, grt_wkg_code) 
  REFERENCES dat.t_group_gr(gr_id,gr_wkg_code) 
    ON UPDATE CASCADE ON DELETE CASCADE,
  grt_tra_code TEXT NOT NULL,
  CONSTRAINT fk_grt_tra_code FOREIGN KEY (grt_tra_code) 
  REFERENCES ref.tr_trait_tra(tra_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  grt_value numeric NULL,
  grt_trv_code TEXT,
  CONSTRAINT fk_grt_trv_tra_code FOREIGN KEY (grt_trv_code, grt_tra_code,grt_wkg_code)
  REFERENCES ref.tr_traitvaluequal_trv(trv_code, trv_trq_code, trv_wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  grt_trm_code TEXT,
  CONSTRAINT fk_grt_trm_code FOREIGN KEY (grt_trm_code)
  REFERENCES ref.tr_traitmethod_trm(trm_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  grt_last_update date DEFAULT CURRENT_DATE NOT NULL,
  grt_qal_code int4 NULL,
    CONSTRAINT fk_grt_qal_id FOREIGN KEY (grt_qal_code) 
  REFERENCES ref.tr_quality_qal(qal_code) ON UPDATE CASCADE,
  grt_ver_code TEXT NOT NULL,
  CONSTRAINT fk_grt_ver_code FOREIGN KEY (grt_ver_code)
  REFERENCES ref.tr_version_ver(ver_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT uk_grt_gr UNIQUE (grt_gr_id, grt_trm_code, grt_wkg_code),
  CONSTRAINT t_grouptrait_grt_pkey PRIMARY KEY (grt_id, grt_wkg_code),
  CONSTRAINT ck_qualitative_or_numeric CHECK 
  (
  (grt_value IS NULL AND grt_trv_code IS NOT NULL) OR
  (grt_value IS NOT NULL AND grt_trv_code IS  NULL)
  )
);
CREATE INDEX dat_t_grouptrait_grt_idx ON dat.t_grouptrait_grt USING btree (grt_gr_id);

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

DROP TABLE IF EXISTS  dateel.t_grouptrait_grt;
CREATE TABLE dateel.t_grouptrait_grt (
 CONSTRAINT fk_grt_ser_id FOREIGN KEY (grt_ser_id)
    REFERENCES dateel.t_series_ser (ser_id) 
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_grt_wkg_code  FOREIGN KEY (grt_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_grt_spe_code  FOREIGN KEY (grt_spe_code)
  REFERENCES ref.tr_species_spe(spe_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_grt_gr_id FOREIGN KEY (grt_gr_id, grt_wkg_code) 
  REFERENCES dateel.t_group_gr(gr_id,gr_wkg_code) 
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_grt_tra_code FOREIGN KEY (grt_tra_code) 
  REFERENCES refeel.tg_trait_tra(tra_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_grt_trv_tra_code FOREIGN KEY (grt_trv_code, grt_tra_code) 
  -- unlike in dat.t_grouptrait_grt this one does not take ref TO wkgcode, no need AS we are in dateel
  REFERENCES refeel.tr_traitvaluequal_trv(trv_code, trv_trq_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_grt_trm_code FOREIGN KEY (grt_trm_code)
  REFERENCES refeel.tr_traitmethod_trm(trm_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_grt_qal_id FOREIGN KEY (grt_qal_code) 
  REFERENCES ref.tr_quality_qal(qal_code) ON UPDATE CASCADE,
  CONSTRAINT fk_grt_ver_code FOREIGN KEY (grt_ver_code)
  REFERENCES refeel.tr_version_ver(ver_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT uk_trv_gr_id_trv_tra_code UNIQUE(grt_gr_id,grt_tra_code)
) INHERITS (dat.t_grouptrait_grt);
CREATE INDEX dateel_t_grouptrait_grt_idx ON dateel.t_grouptrait_grt USING btree (grt_gr_id);
GRANT ALL ON dateel.t_grouptrait_grt TO diaspara_admin;
GRANT SELECT ON dateel.t_grouptrait_grt TO diaspara_read; 


COMMENT ON TABLE dateel.t_grouptrait_grt IS 'Table joining groups and traits';
COMMENT ON COLUMN dateel.t_grouptrait_grt.grt_ser_id IS 'Series UUID';
COMMENT ON COLUMN dateel.t_grouptrait_grt.grt_wkg_code IS 'Working group on of WGEEL, WGNAS, WGBAST ...';
COMMENT ON COLUMN dateel.t_grouptrait_grt.grt_spe_code IS 'Species code here ANG';
COMMENT ON COLUMN dateel.t_grouptrait_grt.grt_id IS 'ID, integer, unique for wkg_code';
COMMENT ON COLUMN dateel.t_grouptrait_grt.grt_gr_id IS 'ID of the group';
COMMENT ON COLUMN dateel.t_grouptrait_grt.grt_tra_code IS 'Code of the trait, e.g. Lengthmm';
COMMENT ON COLUMN dateel.t_grouptrait_grt.grt_value IS 'Value for numeric';
COMMENT ON COLUMN dateel.t_grouptrait_grt.grt_trv_code IS 'Value for qualitative see refeel.tr_traitvaluequal_trv';
COMMENT ON COLUMN dateel.t_grouptrait_grt.grt_trm_code IS 'Method see refeel.tr_traimethod_trm';
COMMENT ON COLUMN dateel.t_grouptrait_grt.grt_last_update IS 'date last update';
COMMENT ON COLUMN dateel.t_grouptrait_grt.grt_qal_code IS 'Quality code references ref.tr_quality_qal';
COMMENT ON COLUMN dateel.t_grouptrait_grt.grt_ver_code IS 'version e.g. WGEEL_2024_1';

-- TODO trigger on date
-- TODO

Import data

Mapping previous database for groups :This is how the mapping was done for groups, on the left the new db with in red the fields for individual metric (not in group metrics), on the right, the old db, with in oranges fields that need to be mapped to method. In yellow the groups that now refer to subgroup by sex recreated at previous step
SQL code to import data 

/*
 * Insert numeric for group - series 
 */
DELETE FROM dateel.t_grouptrait_grt;

INSERT INTO dateel.t_grouptrait_grt
(grt_ser_id, 
grt_wkg_code, 
grt_spe_code, 
grt_id, 
grt_gr_id, 
grt_tra_code, 
grt_value, 
grt_trv_code, 
grt_trm_code, 
grt_last_update, 
grt_qal_code, 
grt_ver_code)
-- extract method from table
-- this will extract 3 columns, meg_gr_id, meg_method_anguillicola and meg_method_sex
WITH a1 AS (
SELECT meg_gr_id,
       meg_value AS meg_method_sex 
FROM datwgeel.t_metricgroupseries_megser 
WHERE meg_mty_id = 27
AND meg_value IS NOT NULL),
 a2 AS (
 SELECT meg_gr_id,
       meg_value AS meg_method_anguillicola 
FROM datwgeel.t_metricgroupseries_megser 
WHERE meg_mty_id = 28
AND meg_value IS NOT NULL),
mm AS (
SELECT coalesce(a1.meg_gr_id, a2.meg_gr_id)  AS meg_gr_id,
meg_method_sex,
meg_method_anguillicola
FROM  a1 FULL OUTER JOIN a2 ON a1.meg_gr_id = a2.meg_gr_id)
-- Insert select query
SELECT 
tss2.ser_id AS grt_ser_id,
'WGEEL' AS grt_wkg_code,
'ANG' AS grt_spe_code,
meg_id AS grt_id, 
megser.meg_gr_id AS grt_gr_id, 
CASE WHEN meg_mty_id = 1 THEN 'Lengthmm'
WHEN meg_mty_id = 2 THEN 'Weightg'
WHEN meg_mty_id = 3 THEN 'Ageyear'
WHEN meg_mty_id = 6 THEN 'Female_proportion'
WHEN meg_mty_id = 7 THEN 'Differentiated_proportion'
WHEN meg_mty_id = 8 THEN 'Anguillicola_proportion'
WHEN meg_mty_id = 9 THEN 'Anguillicola_intensity'
WHEN meg_mty_id = 10 THEN 'Muscle_lipid'
WHEN meg_mty_id = 11 THEN 'Muscle_lipid' -- different method see method
WHEN meg_mty_id = 12 THEN 'Sum_6_pcb'
WHEN meg_mty_id = 13 THEN 'Evex_proportion'
WHEN meg_mty_id = 14 THEN 'Hva_proportion'
WHEN meg_mty_id = 15 THEN 'Pb'
WHEN meg_mty_id = 16 THEN 'Hg'
WHEN meg_mty_id = 17 THEN 'Cd'
WHEN meg_mty_id = 24 THEN 'G_in_gy_proportion'
WHEN meg_mty_id = 25 THEN 'S_in_ys_proportion'
WHEN meg_mty_id = 26 THEN 'Teq'
ELSE 'problem' END AS grt_tra_code,
meg_value AS grt_value, 
NULL AS grt_trv_code, -- there ARE NO qualitative VALUES FOR GROUP metrics
CASE WHEN meg_mty_id = 10 THEN 'Muscle_lipid_fatmeter'
WHEN meg_mty_id = 11 THEN 'Muscle_lipid_gravimeter' -- different method see method
WHEN meg_mty_id = 6 AND meg_method_sex = 1 THEN 'Gonadal_inspection'
WHEN meg_mty_id = 6 AND meg_method_sex = 0 THEN 'Length_based_sex'
WHEN meg_mty_id = 8 AND meg_method_anguillicola = 1 THEN 'Anguillicola_stereomicroscope_count'
WHEN meg_mty_id = 8 AND meg_method_anguillicola = 0 THEN 'Anguillicola_visual_count'
WHEN meg_mty_id = 9 AND meg_method_anguillicola = 1 THEN 'Anguillicola_stereomicroscope_count'
WHEN meg_mty_id = 9 AND meg_method_anguillicola = 0 THEN 'Anguillicola_visual_count'
ELSE NULL END AS grt_trm_code,
meg_last_update AS grt_last_update, 
meg_qal_id AS grt_qal_code, 
CASE WHEN meg_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN meg_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN meg_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN meg_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN meg_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN meg_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS grt_ver_code
FROM datwgeel.t_metricgroupseries_megser megser
JOIN datwgeel.t_groupseries_grser grser ON megser.meg_gr_id = gr_id
JOIN datwgeel.t_series_ser AS tss ON grser_ser_id = ser_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= ser_nameshort
LEFT JOIN mm ON megser.meg_gr_id = mm.meg_gr_id -- joining subquery
WHERE meg_mty_id IN (1,2,3,6,7,8,9,10,11,12,13,14,15,16,17,24,25,26); --4194




/*
 * Insert numeric for group - sampling
 * Some values have both gravimeter and fatmeter, gravimeter chosen.
 */


INSERT INTO dateel.t_grouptrait_grt
(grt_ser_id, 
grt_wkg_code, 
grt_spe_code, 
grt_id, 
grt_gr_id, 
grt_tra_code, 
grt_value, 
grt_trv_code, 
grt_trm_code, 
grt_last_update, 
grt_qal_code, 
grt_ver_code)
-- extract method from table
-- this will extract 3 columns, meg_gr_id, meg_method_anguillicola and meg_method_sex
WITH a1 AS (
SELECT meg_gr_id,
       meg_value AS meg_method_sex 
FROM datwgeel.t_metricgroupsamp_megsa megsa
WHERE meg_mty_id = 27
AND meg_value IS NOT NULL),
 a2 AS (
 SELECT meg_gr_id,
       meg_value AS meg_method_anguillicola 
FROM datwgeel.t_metricgroupsamp_megsa megsa 
WHERE meg_mty_id = 28
AND meg_value IS NOT NULL),
mm AS (
SELECT coalesce(a1.meg_gr_id, a2.meg_gr_id)  AS meg_gr_id,
meg_method_sex,
meg_method_anguillicola
FROM  a1 FULL OUTER JOIN a2 ON a1.meg_gr_id = a2.meg_gr_id)
-- Insert select query
SELECT 
tss2.ser_id AS grt_ser_id,
'WGEEL' AS grt_wkg_code,
'ANG' AS grt_spe_code,
meg_id AS grt_id, 
megsa.meg_gr_id AS grt_gr_id, 
CASE WHEN meg_mty_id = 1 THEN 'Lengthmm'
WHEN meg_mty_id = 2 THEN 'Weightg'
WHEN meg_mty_id = 3 THEN 'Ageyear'
WHEN meg_mty_id = 6 THEN 'Female_proportion'
WHEN meg_mty_id = 7 THEN 'Differentiated_proportion'
WHEN meg_mty_id = 8 THEN 'Anguillicola_proportion'
WHEN meg_mty_id = 9 THEN 'Anguillicola_intensity'
WHEN meg_mty_id = 10 THEN 'Muscle_lipid'
WHEN meg_mty_id = 11 THEN 'Muscle_lipid' -- different method see method
WHEN meg_mty_id = 12 THEN 'Sum_6_pcb'
WHEN meg_mty_id = 13 THEN 'Evex_proportion'
WHEN meg_mty_id = 14 THEN 'Hva_proportion'
WHEN meg_mty_id = 15 THEN 'Pb'
WHEN meg_mty_id = 16 THEN 'Hg'
WHEN meg_mty_id = 17 THEN 'Cd'
WHEN meg_mty_id = 24 THEN 'G_in_gy_proportion'
WHEN meg_mty_id = 25 THEN 'S_in_ys_proportion'
WHEN meg_mty_id = 26 THEN 'Teq'
ELSE 'problem' END AS grt_tra_code,
meg_value AS grt_value, 
NULL AS grt_trv_code, -- there ARE NO qualitative VALUES FOR GROUP metrics
CASE WHEN meg_mty_id = 10 THEN 'Muscle_lipid_fatmeter'
WHEN meg_mty_id = 11 THEN 'Muscle_lipid_gravimeter' -- different method see method
WHEN meg_mty_id = 6 AND meg_method_sex = 1 THEN 'Gonadal_inspection'
WHEN meg_mty_id = 6 AND meg_method_sex = 0 THEN 'Length_based_sex'
WHEN meg_mty_id = 8 AND meg_method_anguillicola = 1 THEN 'Anguillicola_stereomicroscope_count'
WHEN meg_mty_id = 8 AND meg_method_anguillicola = 0 THEN 'Anguillicola_visual_count'
WHEN meg_mty_id = 9 AND meg_method_anguillicola = 1 THEN 'Anguillicola_stereomicroscope_count'
WHEN meg_mty_id = 9 AND meg_method_anguillicola = 0 THEN 'Anguillicola_visual_count'
ELSE NULL END AS grt_trm_code,
meg_last_update AS grt_last_update, 
meg_qal_id AS grt_qal_code, 
CASE WHEN meg_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN meg_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN meg_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN meg_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN meg_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN meg_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS grt_ver_code
FROM datwgeel.t_metricgroupsamp_megsa megsa
JOIN datwgeel.t_groupsamp_grsa  ON megsa.meg_gr_id = gr_id
JOIN datwgeel.t_samplinginfo_sai AS tss ON grsa_sai_id = sai_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= sai_id::text
LEFT JOIN mm ON megsa.meg_gr_id = mm.meg_gr_id -- joining subquery
WHERE meg_mty_id IN (1,2,3,6,7,8,9,11,12,13,14,15,16,17,24,25,26); --3028


-- Insert only fatmeter where gravimeter does not exist

INSERT INTO dateel.t_grouptrait_grt
(grt_ser_id, 
grt_wkg_code, 
grt_spe_code, 
grt_id, 
grt_gr_id, 
grt_tra_code, 
grt_value, 
grt_trv_code, 
grt_trm_code, 
grt_last_update, 
grt_qal_code, 
grt_ver_code)
WITH a1 AS (
SELECT meg_gr_id,
       meg_value AS meg_method_sex 
FROM datwgeel.t_metricgroupsamp_megsa megsa
WHERE meg_mty_id = 27
AND meg_value IS NOT NULL),
 a2 AS (
 SELECT meg_gr_id,
       meg_value AS meg_method_anguillicola 
FROM datwgeel.t_metricgroupsamp_megsa megsa 
WHERE meg_mty_id = 28
AND meg_value IS NOT NULL),
mm AS (
SELECT coalesce(a1.meg_gr_id, a2.meg_gr_id)  AS meg_gr_id,
meg_method_sex,
meg_method_anguillicola
FROM  a1 FULL OUTER JOIN a2 ON a1.meg_gr_id = a2.meg_gr_id),
-- Insert select query
fatmeter AS (
SELECT 
tss2.ser_id AS grt_ser_id,
'WGEEL' AS grt_wkg_code,
'ANG' AS grt_spe_code,
meg_id AS grt_id, 
megsa.meg_gr_id AS grt_gr_id, 
CASE WHEN meg_mty_id = 1 THEN 'Lengthmm'
WHEN meg_mty_id = 2 THEN 'Weightg'
WHEN meg_mty_id = 3 THEN 'Ageyear'
WHEN meg_mty_id = 6 THEN 'Female_proportion'
WHEN meg_mty_id = 7 THEN 'Differentiated_proportion'
WHEN meg_mty_id = 8 THEN 'Anguillicola_proportion'
WHEN meg_mty_id = 9 THEN 'Anguillicola_intensity'
WHEN meg_mty_id = 10 THEN 'Muscle_lipid'
WHEN meg_mty_id = 11 THEN 'Muscle_lipid' -- different method see method
WHEN meg_mty_id = 12 THEN 'Sum_6_pcb'
WHEN meg_mty_id = 13 THEN 'Evex_proportion'
WHEN meg_mty_id = 14 THEN 'Hva_proportion'
WHEN meg_mty_id = 15 THEN 'Pb'
WHEN meg_mty_id = 16 THEN 'Hg'
WHEN meg_mty_id = 17 THEN 'Cd'
WHEN meg_mty_id = 24 THEN 'G_in_gy_proportion'
WHEN meg_mty_id = 25 THEN 'S_in_ys_proportion'
WHEN meg_mty_id = 26 THEN 'Teq'
ELSE 'problem' END AS grt_tra_code,
meg_value AS grt_value, 
NULL AS grt_trv_code, -- there ARE NO qualitative VALUES FOR GROUP metrics
CASE WHEN meg_mty_id = 10 THEN 'Muscle_lipid_fatmeter'
WHEN meg_mty_id = 11 THEN 'Muscle_lipid_gravimeter' -- different method see method
WHEN meg_mty_id = 6 AND meg_method_sex = 1 THEN 'Gonadal_inspection'
WHEN meg_mty_id = 6 AND meg_method_sex = 0 THEN 'Length_based_sex'
WHEN meg_mty_id = 8 AND meg_method_anguillicola = 1 THEN 'Anguillicola_stereomicroscope_count'
WHEN meg_mty_id = 8 AND meg_method_anguillicola = 0 THEN 'Anguillicola_visual_count'
WHEN meg_mty_id = 9 AND meg_method_anguillicola = 1 THEN 'Anguillicola_stereomicroscope_count'
WHEN meg_mty_id = 9 AND meg_method_anguillicola = 0 THEN 'Anguillicola_visual_count'
ELSE NULL END AS grt_trm_code,
meg_last_update AS grt_last_update, 
meg_qal_id AS grt_qal_code, 
CASE WHEN meg_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN meg_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN meg_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN meg_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN meg_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN meg_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS grt_ver_code
FROM datwgeel.t_metricgroupsamp_megsa megsa
JOIN datwgeel.t_groupsamp_grsa  ON megsa.meg_gr_id = gr_id
JOIN datwgeel.t_samplinginfo_sai AS tss ON grsa_sai_id = sai_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= sai_id::text
LEFT JOIN mm ON megsa.meg_gr_id = mm.meg_gr_id -- joining subquery
WHERE meg_mty_id = 10)
SELECT * FROM fatmeter WHERE  grt_gr_id NOT IN 
(SELECT grt_gr_id FROM dateel.t_grouptrait_grt WHERE grt_tra_code = 'Muscle_lipid')
; --23





-- GROUP metrics FOR males sampling
SELECT count(*) FROM  datwgeel.t_metricgroupsamp_megsa megsa
WHERE meg_mty_id IN (18,19,20);--544



INSERT INTO dateel.t_grouptrait_grt
(grt_ser_id, 
grt_wkg_code, 
grt_spe_code, 
grt_id, 
grt_gr_id, 
grt_tra_code, 
grt_value, 
grt_trv_code, 
grt_trm_code, 
grt_last_update, 
grt_qal_code, 
grt_ver_code)
-- extract method from table
-- this will extract 3 columns, meg_gr_id, meg_method_anguillicola and meg_method_sex
WITH a1 AS (
SELECT meg_gr_id,
       meg_value AS meg_method_sex 
FROM datwgeel.t_metricgroupsamp_megsa megsa
WHERE meg_mty_id = 27
AND meg_value IS NOT NULL),
 a2 AS (
 SELECT meg_gr_id,
       meg_value AS meg_method_anguillicola 
FROM datwgeel.t_metricgroupsamp_megsa megsa 
WHERE meg_mty_id = 28
AND meg_value IS NOT NULL),
mm AS (
SELECT coalesce(a1.meg_gr_id, a2.meg_gr_id)  AS meg_gr_id,
meg_method_sex,
meg_method_anguillicola
FROM  a1 FULL OUTER JOIN a2 ON a1.meg_gr_id = a2.meg_gr_id)
-- Insert select query
SELECT DISTINCT ON (meg_id)
tss2.ser_id AS grt_ser_id,
'WGEEL' AS grt_wkg_code,
'ANG' AS grt_spe_code,
meg_id AS grt_id, 
gr.gr_id AS grt_gr_id, 
CASE WHEN meg_mty_id IN (18, 21) THEN 'Lengthmm'
WHEN meg_mty_id IN (19,22) THEN 'Weightg'
WHEN meg_mty_id IN (20,23) THEN 'Ageyear'
ELSE 'problem' END AS grt_tra_code,
meg_value AS grt_value, 
NULL AS grt_trv_code, -- there ARE NO qualitative VALUES FOR GROUP metrics
CASE WHEN meg_mty_id = 10 THEN 'Muscle_lipid_fatmeter'
WHEN meg_mty_id = 11 THEN 'Muscle_lipid_gravimeter' -- different method see method
WHEN meg_method_sex = 1 THEN 'Gonadal_inspection'
WHEN meg_method_sex = 0 THEN 'Length_based_sex'
ELSE NULL END AS grt_trm_code,
meg_last_update AS grt_last_update, 
meg_qal_id AS grt_qal_code, 
CASE WHEN meg_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN meg_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN meg_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN meg_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN meg_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN meg_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS grt_ver_code
FROM datwgeel.t_metricgroupsamp_megsa megsa
JOIN datwgeel.t_groupsamp_grsa grsa  ON megsa.meg_gr_id = grsa.gr_id
JOIN datwgeel.t_samplinginfo_sai AS tss ON grsa_sai_id = sai_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= sai_id::text
LEFT JOIN mm ON megsa.meg_gr_id = mm.meg_gr_id -- joining subquery
JOIN (SELECT gr_id, gr_ser_id, gr_year, gr_lfs_code FROM dateel.t_group_gr WHERE gr_sex_code = 'M') gr
ON (gr.gr_ser_id, COALESCE(gr.gr_year,1), gr_lfs_code) = ( tss2.ser_id, COALESCE(grsa.gr_year,1),grsa.grsa_lfs_code)
WHERE meg_mty_id IN (18,19,20); --544 C�dric pi fort

-- GROUP metrics FOR females   sampling
SELECT count(*) FROM  datwgeel.t_metricgroupseries_megser megsa
WHERE meg_mty_id IN (21,22,23);--553

INSERT INTO dateel.t_grouptrait_grt
(grt_ser_id, 
grt_wkg_code, 
grt_spe_code, 
grt_id, 
grt_gr_id, 
grt_tra_code, 
grt_value, 
grt_trv_code, 
grt_trm_code, 
grt_last_update, 
grt_qal_code, 
grt_ver_code)
-- extract method from table
-- this will extract 3 columns, meg_gr_id, meg_method_anguillicola and meg_method_sex
WITH a1 AS (
SELECT meg_gr_id,
       meg_value AS meg_method_sex 
FROM datwgeel.t_metricgroupsamp_megsa megsa
WHERE meg_mty_id = 27
AND meg_value IS NOT NULL),
 a2 AS (
 SELECT meg_gr_id,
       meg_value AS meg_method_anguillicola 
FROM datwgeel.t_metricgroupsamp_megsa megsa 
WHERE meg_mty_id = 28
AND meg_value IS NOT NULL),
mm AS (
SELECT coalesce(a1.meg_gr_id, a2.meg_gr_id)  AS meg_gr_id,
meg_method_sex,
meg_method_anguillicola
FROM  a1 FULL OUTER JOIN a2 ON a1.meg_gr_id = a2.meg_gr_id)
-- Insert select query
SELECT DISTINCT ON (meg_id)
tss2.ser_id AS grt_ser_id,
'WGEEL' AS grt_wkg_code,
'ANG' AS grt_spe_code,
meg_id AS grt_id, 
gr.gr_id AS grt_gr_id, 
CASE WHEN meg_mty_id IN (18, 21) THEN 'Lengthmm'
WHEN meg_mty_id IN (19,22) THEN 'Weightg'
WHEN meg_mty_id IN (20,23) THEN 'Ageyear'
ELSE 'problem' END AS grt_tra_code,
meg_value AS grt_value, 
NULL AS grt_trv_code, -- there ARE NO qualitative VALUES FOR GROUP metrics
CASE WHEN meg_mty_id = 10 THEN 'Muscle_lipid_fatmeter'
WHEN meg_mty_id = 11 THEN 'Muscle_lipid_gravimeter' -- different method see method
WHEN meg_method_sex = 1 THEN 'Gonadal_inspection'
WHEN meg_method_sex = 0 THEN 'Length_based_sex'
ELSE NULL END AS grt_trm_code,
meg_last_update AS grt_last_update, 
meg_qal_id AS grt_qal_code, 
CASE WHEN meg_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN meg_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN meg_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN meg_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN meg_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN meg_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS grt_ver_code
FROM datwgeel.t_metricgroupsamp_megsa megsa
JOIN datwgeel.t_groupsamp_grsa grsa  ON megsa.meg_gr_id = grsa.gr_id
JOIN datwgeel.t_samplinginfo_sai AS tss ON grsa_sai_id = sai_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= sai_id::text
LEFT JOIN mm ON megsa.meg_gr_id = mm.meg_gr_id -- joining subquery
JOIN (SELECT gr_id, gr_ser_id, gr_year, gr_lfs_code FROM dateel.t_group_gr WHERE gr_sex_code = 'F') gr
ON (gr.gr_ser_id, COALESCE(gr.gr_year,1), gr_lfs_code) = ( tss2.ser_id, COALESCE(grsa.gr_year,1),grsa.grsa_lfs_code)
WHERE meg_mty_id IN (21,22,23); --1183

-- GROUP metrics for males series

INSERT INTO dateel.t_grouptrait_grt
(grt_ser_id, 
grt_wkg_code, 
grt_spe_code, 
grt_id, 
grt_gr_id, 
grt_tra_code, 
grt_value, 
grt_trv_code, 
grt_trm_code, 
grt_last_update, 
grt_qal_code, 
grt_ver_code)
-- extract method from table
-- this will extract 3 columns, meg_gr_id, meg_method_anguillicola and meg_method_sex
WITH a1 AS (
SELECT meg_gr_id,
       meg_value AS meg_method_sex 
FROM datwgeel.t_metricgroupseries_megser megser
WHERE meg_mty_id = 27
AND meg_value IS NOT NULL),
 a2 AS (
 SELECT meg_gr_id,
       meg_value AS meg_method_anguillicola 
FROM datwgeel.t_metricgroupseries_megser megser 
WHERE meg_mty_id = 28
AND meg_value IS NOT NULL),
mm AS (
SELECT coalesce(a1.meg_gr_id, a2.meg_gr_id)  AS meg_gr_id,
meg_method_sex,
meg_method_anguillicola
FROM  a1 FULL OUTER JOIN a2 ON a1.meg_gr_id = a2.meg_gr_id)
-- Insert select query
SELECT DISTINCT ON (meg_id)
tss2.ser_id AS grt_ser_id,
'WGEEL' AS grt_wkg_code,
'ANG' AS grt_spe_code,
meg_id AS grt_id, 
gr.gr_id AS grt_gr_id, 
CASE WHEN meg_mty_id IN (18, 21) THEN 'Lengthmm'
WHEN meg_mty_id IN (19,22) THEN 'Weightg'
WHEN meg_mty_id IN (20,23) THEN 'Ageyear'
ELSE 'problem' END AS grt_tra_code,
meg_value AS grt_value, 
NULL AS grt_trv_code, -- there ARE NO qualitative VALUES FOR GROUP metrics
CASE WHEN meg_mty_id = 10 THEN 'Muscle_lipid_fatmeter'
WHEN meg_mty_id = 11 THEN 'Muscle_lipid_gravimeter' -- different method see method
WHEN meg_method_sex = 1 THEN 'Gonadal_inspection'
WHEN meg_method_sex = 0 THEN 'Length_based_sex'
ELSE NULL END AS grt_trm_code,
meg_last_update AS grt_last_update, 
meg_qal_id AS grt_qal_code, 
CASE WHEN meg_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN meg_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN meg_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN meg_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN meg_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN meg_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS grt_ver_code
FROM datwgeel.t_metricgroupseries_megser megser
JOIN datwgeel.t_groupseries_grser grser ON megser.meg_gr_id = gr_id
JOIN datwgeel.t_series_ser AS tss ON grser_ser_id = ser_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= ser_nameshort
LEFT JOIN mm ON megser.meg_gr_id = mm.meg_gr_id -- joining subquery
JOIN (SELECT gr_id, gr_ser_id, gr_year, gr_lfs_code FROM dateel.t_group_gr WHERE gr_sex_code = 'M') gr
ON (gr.gr_ser_id, COALESCE(gr.gr_year,1), gr_lfs_code) = ( tss2.ser_id, COALESCE(grser.gr_year,1),tss.ser_lfs_code)
WHERE meg_mty_id IN (18,19,20); --409

-- GROUP metrics for females series
SELECT count(*) FROM  datwgeel.t_metricgroupseries_megser megser
WHERE meg_mty_id IN (21,22,23);--553

INSERT INTO dateel.t_grouptrait_grt
(grt_ser_id, 
grt_wkg_code, 
grt_spe_code, 
grt_id, 
grt_gr_id, 
grt_tra_code, 
grt_value, 
grt_trv_code, 
grt_trm_code, 
grt_last_update, 
grt_qal_code, 
grt_ver_code)
-- extract method from table
-- this will extract 3 columns, meg_gr_id, meg_method_anguillicola and meg_method_sex
WITH a1 AS (
SELECT meg_gr_id,
       meg_value AS meg_method_sex 
FROM datwgeel.t_metricgroupseries_megser megser
WHERE meg_mty_id = 27
AND meg_value IS NOT NULL),
 a2 AS (
 SELECT meg_gr_id,
       meg_value AS meg_method_anguillicola 
FROM datwgeel.t_metricgroupseries_megser megser 
WHERE meg_mty_id = 28
AND meg_value IS NOT NULL),
mm AS (
SELECT coalesce(a1.meg_gr_id, a2.meg_gr_id)  AS meg_gr_id,
meg_method_sex,
meg_method_anguillicola
FROM  a1 FULL OUTER JOIN a2 ON a1.meg_gr_id = a2.meg_gr_id)
-- Insert select query
SELECT DISTINCT ON (meg_id)
tss2.ser_id AS grt_ser_id,
'WGEEL' AS grt_wkg_code,
'ANG' AS grt_spe_code,
meg_id AS grt_id, 
gr.gr_id AS grt_gr_id, 
CASE WHEN meg_mty_id IN (18, 21) THEN 'Lengthmm'
WHEN meg_mty_id IN (19,22) THEN 'Weightg'
WHEN meg_mty_id IN (20,23) THEN 'Ageyear'
ELSE 'problem' END AS grt_tra_code,
meg_value AS grt_value, 
NULL AS grt_trv_code, -- there ARE NO qualitative VALUES FOR GROUP metrics
CASE WHEN meg_mty_id = 10 THEN 'Muscle_lipid_fatmeter'
WHEN meg_mty_id = 11 THEN 'Muscle_lipid_gravimeter' -- different method see method
WHEN meg_method_sex = 1 THEN 'Gonadal_inspection'
WHEN meg_method_sex = 0 THEN 'Length_based_sex'
ELSE NULL END AS grt_trm_code,
meg_last_update AS grt_last_update, 
meg_qal_id AS grt_qal_code, 
CASE WHEN meg_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN meg_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN meg_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN meg_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN meg_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN meg_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS grt_ver_code
FROM datwgeel.t_metricgroupseries_megser megser
JOIN datwgeel.t_groupseries_grser grser ON megser.meg_gr_id = gr_id
JOIN datwgeel.t_series_ser AS tss ON grser_ser_id = ser_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= ser_nameshort
LEFT JOIN mm ON megser.meg_gr_id = mm.meg_gr_id -- joining subquery
JOIN (SELECT gr_id, gr_ser_id, gr_year, gr_lfs_code FROM dateel.t_group_gr WHERE gr_sex_code = 'F') gr
ON (gr.gr_ser_id, COALESCE(gr.gr_year,1), gr_lfs_code) = ( tss2.ser_id, COALESCE(grser.gr_year,1),tss.ser_lfs_code)
WHERE meg_mty_id IN (21,22,23); --553



;
Table 22: Table t_grouptrait_grt, group metrics (t_grouptrait_grt)
grt_ser_id grt_wkg_code grt_spe_code grt_id grt_gr_id grt_tra_code grt_value grt_trv_code grt_trm_code grt_last_update grt_qal_code grt_ver_code
7eb93711-736d-442c-9f14-1d293e576394 WGEEL ANG 12488 3990 Muscle_lipid 22.6 NA Muscle_lipid_fatmeter 2023-09-04 1 WGEEL-2023-1
7eb93711-736d-442c-9f14-1d293e576394 WGEEL ANG 5405 2708 Muscle_lipid 22.5 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
7eb93711-736d-442c-9f14-1d293e576394 WGEEL ANG 5394 2707 Muscle_lipid 23.0 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
7eb93711-736d-442c-9f14-1d293e576394 WGEEL ANG 5383 2706 Muscle_lipid 23.4 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
7eb93711-736d-442c-9f14-1d293e576394 WGEEL ANG 5372 2705 Muscle_lipid 25.8 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
7eb93711-736d-442c-9f14-1d293e576394 WGEEL ANG 5361 2704 Muscle_lipid 23.6 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
7eb93711-736d-442c-9f14-1d293e576394 WGEEL ANG 5350 2703 Muscle_lipid 25.4 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
0cd50424-0ba6-4b6c-b9fe-d9572ff9c424 WGEEL ANG 12477 3989 Muscle_lipid 21.0 NA Muscle_lipid_fatmeter 2023-09-04 1 WGEEL-2023-1
0cd50424-0ba6-4b6c-b9fe-d9572ff9c424 WGEEL ANG 5324 2701 Muscle_lipid 23.3 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
0cd50424-0ba6-4b6c-b9fe-d9572ff9c424 WGEEL ANG 5274 2697 Muscle_lipid 22.2 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
0cd50424-0ba6-4b6c-b9fe-d9572ff9c424 WGEEL ANG 5252 2695 Muscle_lipid 22.9 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
0cd50424-0ba6-4b6c-b9fe-d9572ff9c424 WGEEL ANG 5226 2693 Muscle_lipid 24.0 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
0cd50424-0ba6-4b6c-b9fe-d9572ff9c424 WGEEL ANG 5203 2691 Muscle_lipid 24.3 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
0cd50424-0ba6-4b6c-b9fe-d9572ff9c424 WGEEL ANG 5177 2689 Muscle_lipid 21.6 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
0cd50424-0ba6-4b6c-b9fe-d9572ff9c424 WGEEL ANG 5151 2687 Muscle_lipid 21.5 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
ae1030ac-b099-41b6-80bf-c3a7c455d17f WGEEL ANG 12466 3988 Muscle_lipid 18.8 NA Muscle_lipid_fatmeter 2023-09-04 1 WGEEL-2023-1
ae1030ac-b099-41b6-80bf-c3a7c455d17f WGEEL ANG 5335 2702 Muscle_lipid 19.3 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
ae1030ac-b099-41b6-80bf-c3a7c455d17f WGEEL ANG 5285 2698 Muscle_lipid 19.6 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
ae1030ac-b099-41b6-80bf-c3a7c455d17f WGEEL ANG 5263 2696 Muscle_lipid 19.2 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1
ae1030ac-b099-41b6-80bf-c3a7c455d17f WGEEL ANG 5237 2694 Muscle_lipid 20.0 NA Muscle_lipid_fatmeter 2022-09-06 1 WGEEL-2022-1

Creating ind metrics

Fish table

SQL code to create tables dat.t_fish_fi and dateel.t_fish_fi 
-- DROP TABLE IF EXISTS dat.t_fish_fi CASCADE;

CREATE TABLE dat.t_fish_fi (
  fi_id serial4 NOT NULL,
  fi_ser_id UUID NOT NULL,
  CONSTRAINT fk_fi_ser_id FOREIGN KEY (fi_ser_id)
  REFERENCES dat.t_series_ser (ser_id) 
  ON UPDATE CASCADE ON DELETE CASCADE, 
  fi_wkg_code TEXT NOT NULL,  
  CONSTRAINT fk_fi_wkg_code  FOREIGN KEY (fi_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT t_fish_fi_pkey PRIMARY KEY (fi_id, fi_wkg_code),
  fi_spe_code TEXT,
  CONSTRAINT fk_fi_spe_code FOREIGN KEY (fi_spe_code) 
  REFERENCES ref.tr_species_spe(spe_code) 
  ON UPDATE CASCADE ON DELETE RESTRICT,
  fi_lfs_code TEXT,
  CONSTRAINT fk_fi_lfs_code_fi_spe_code FOREIGN KEY (fi_lfs_code, fi_spe_code)
  REFERENCES ref.tr_lifestage_lfs (lfs_code, lfs_spe_code) 
  ON UPDATE CASCADE ON DELETE RESTRICT, 
  fi_date date NULL,
  fi_year int4 NULL,
  CONSTRAINT ck_fi_date_fi_year CHECK (((fi_date IS NOT NULL) OR (fi_year IS NOT NULL))),
  fi_comment text NULL,
  fi_lastupdate date DEFAULT CURRENT_DATE NOT NULL,
  fi_idsource TEXT NULL UNIQUE,
  fi_ver_code TEXT NOT NULL,
  CONSTRAINT fk_gr_ver_code FOREIGN KEY (fi_ver_code)
  REFERENCES ref.tr_version_ver(ver_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  fi_x_4326 NUMERIC,
  fi_y_4326 NUMERIC,
  fi_geom  GEOMETRY(Point, 4326) 
);




COMMENT ON TABLE dat.t_fish_fi IS 'Table identifying the fish metrics, a fish metric corresponds to a
 fish sampled at a given date or year.';

COMMENT ON COLUMN dat.t_fish_fi.fi_id IS 'Fi ID, serial primary key on fi_id and fi_wkg_code';
COMMENT ON COLUMN dat.t_fish_fi.fi_wkg_code IS 'Code of the working group, one of
WGBAST, WGEEL, WGNAS, WKTRUTTA';
COMMENT ON COLUMN dat.t_fish_fi.fi_lfs_code IS 'Life stage code';
COMMENT ON COLUMN dat.t_fish_fi.fi_spe_code IS 'Species code';
COMMENT ON COLUMN dat.t_fish_fi.fi_year IS 'The year';
COMMENT ON COLUMN dat.t_fish_fi.fi_comment IS 'Comment on the fish';
COMMENT ON COLUMN dat.t_fish_fi.fi_lastupdate IS 'Last update, inserted automatically';
COMMENT ON COLUMN dat.t_fish_fi.fi_ver_code IS 'Version code as in tr_version_ver, corresponds to the working group code WGNAS-2024-1 WGEEL-2016-1, the -1 indicate the first data call in the year, -2 would be second etc....';
COMMENT ON COLUMN dat.t_fish_fi.fi_idsource IS 'Identifier of the fish in the source (country) database';

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

DROP TABLE IF EXISTS dateel.t_fish_fi;
CREATE TABLE dateel.t_fish_fi (
  CONSTRAINT fk_fi_ser_id FOREIGN KEY (fi_ser_id)
  REFERENCES dateel.t_series_ser (ser_id) 
  ON UPDATE CASCADE ON DELETE CASCADE, 
  CONSTRAINT fk_fi_wkg_code  FOREIGN KEY (fi_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT t_fish_fi_pkey PRIMARY KEY (fi_id, fi_wkg_code),
  CONSTRAINT fk_fi_spe_code FOREIGN KEY (fi_spe_code) 
  REFERENCES ref.tr_species_spe(spe_code) 
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_fi_lfs_code_fi_spe_code FOREIGN KEY (fi_lfs_code, fi_spe_code)
  REFERENCES ref.tr_lifestage_lfs (lfs_code, lfs_spe_code) 
  ON UPDATE CASCADE ON DELETE RESTRICT, 
  CONSTRAINT ck_fi_date_fi_year CHECK (((fi_date IS NOT NULL) OR (fi_year IS NOT NULL))),
  CONSTRAINT fk_gr_ver_code FOREIGN KEY (fi_ver_code)
  REFERENCES refeel.tr_version_ver(ver_code)
  ON UPDATE CASCADE ON DELETE RESTRICT  
) INHERITS (dat.t_fish_fi);
 

GRANT ALL ON dateel.t_fish_fi TO diaspara_admin;
GRANT SELECT ON dateel.t_fish_fi TO diaspara_read;
SQL code to import data tables 


-- insert from series
INSERT INTO dateel.t_fish_fi
(fi_id, 
fi_ser_id, 
fi_wkg_code, 
fi_spe_code, 
fi_lfs_code, 
fi_date, 
fi_year, 
fi_comment, 
fi_lastupdate, 
fi_idsource,
fi_ver_code,
fi_x_4326,
fi_y_4326,
fi_geom)
SELECT 
fi_id, 
tss2.ser_id AS fi_ser_id,
'WGEEL' AS fi_wkg_code,
'ANG' AS fi_spe_code,
fi_lfs_code,
fi_date,
fi_year, 
fi_comment,
fi_lastupdate, 
fi_id_cou AS fi_idsource,
CASE WHEN fi_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN fi_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN fi_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN fi_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN fi_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN fi_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS fi_ver_code,
st_x(tss.geom),
st_y(tss.geom),
tss.geom
FROM datwgeel.t_fishseries_fiser 
JOIN datwgeel.t_series_ser tss ON ser_id = fiser_ser_id 
JOIN dat.t_series_ser tss2 ON ser_code = ser_nameshort ; --757787

-- Insert from sampling

INSERT INTO dateel.t_fish_fi
(fi_id, 
fi_ser_id, 
fi_wkg_code, 
fi_spe_code, 
fi_lfs_code, 
fi_date, 
fi_year, 
fi_comment, 
fi_lastupdate, 
fi_idsource,
fi_ver_code,
fi_x_4326,
fi_y_4326,
fi_geom)
SELECT 
fi_id, 
tss2.ser_id AS fi_ser_id,
'WGEEL' AS fi_wkg_code,
'ANG' AS fi_spe_code,
CASE WHEN fi_lfs_code = 'NA' THEN 'YS' ELSE fi_lfs_code END AS fi_lfs_code,
fi_date,
fi_year, 
fi_comment,
fi_lastupdate, 
fi_id_cou AS fi_idsource,
CASE WHEN fi_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN fi_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN fi_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN fi_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN fi_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN fi_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS fi_ver_code,
fisa_x_4326,
fisa_y_4326,
fisa_geom
FROM  datwgeel.t_fishsamp_fisa 
JOIN datwgeel.t_samplinginfo_sai AS tss ON fisa_sai_id = sai_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= sai_id::text;  --98545
Table 23: Table t_fish_fi, fish table
fi_id fi_ser_id fi_wkg_code fi_spe_code fi_lfs_code fi_date fi_year fi_comment fi_lastupdate fi_idsource fi_ver_code fi_x_4326 fi_y_4326 fi_geom
3292880 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-22 2023 no dissection data available for 2023 2024-09-10 fane_22.09.2023_004 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292877 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-22 2023 no dissection data available for 2023 2024-09-10 fane_22.09.2023_001 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292878 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-22 2023 no dissection data available for 2023 2024-09-10 fane_22.09.2023_002 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292879 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-22 2023 no dissection data available for 2023 2024-09-10 fane_22.09.2023_003 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292881 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-22 2023 no dissection data available for 2023 2024-09-10 fane_22.09.2023_005 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292882 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-22 2023 no dissection data available for 2023 2024-09-10 fane_22.09.2023_006 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292883 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-22 2023 no dissection data available for 2023 2024-09-10 fane_22.09.2023_007 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292884 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-22 2023 no dissection data available for 2023 2024-09-10 fane_22.09.2023_008 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292885 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-22 2023 no dissection data available for 2023 2024-09-10 fane_22.09.2023_009 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292886 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-22 2023 no dissection data available for 2023 2024-09-10 fane_22.09.2023_010 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292887 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-22 2023 no dissection data available for 2023 2024-09-10 fane_22.09.2023_011 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292888 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-22 2023 no dissection data available for 2023 2024-09-10 fane_22.09.2023_012 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292889 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-23 2023 no dissection data available for 2023 2024-09-10 fane_23.09.2023_001 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292890 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-23 2023 no dissection data available for 2023 2024-09-10 fane_23.09.2023_002 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292891 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-23 2023 no dissection data available for 2023 2024-09-10 fane_23.09.2023_003 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292892 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-23 2023 no dissection data available for 2023 2024-09-10 fane_23.09.2023_004 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292893 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-23 2023 no dissection data available for 2023 2024-09-10 fane_23.09.2023_005 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292894 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-23 2023 no dissection data available for 2023 2024-09-10 fane_23.09.2023_006 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292895 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-23 2023 no dissection data available for 2023 2024-09-10 fane_23.09.2023_007 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40
3292896 31cb9a15-7b2a-46e4-887c-93673d9cd859 WGEEL ANG S 2023-09-23 2023 no dissection data available for 2023 2024-09-10 fane_23.09.2023_008 WGEEL-2024-1 -6.674814 54.09913 0101000020E61000002288F37002B31AC088821953B00C4B40

Table t_indtrait_int

SQL code to create tables dat.t_indivtrait_int and dateel.t_indivtrait_int 

-- DROP TABLE IF EXISTS dat.t_indivtrait_int CASCADE;


CREATE TABLE dat.t_indivtrait_int (
  int_ser_id uuid,
 CONSTRAINT fk_int_ser_id FOREIGN KEY (int_ser_id)
    REFERENCES dat.t_series_ser (ser_id) 
    ON UPDATE CASCADE ON DELETE CASCADE,  
  int_wkg_code TEXT NOT NULL,  
  CONSTRAINT fk_int_wkg_code  FOREIGN KEY (int_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  int_spe_code TEXT NOT NULL,  
  CONSTRAINT fk_int_spe_code  FOREIGN KEY (int_spe_code)
  REFERENCES ref.tr_species_spe(spe_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  int_id serial4 NOT NULL,
  int_fi_id int4 NOT NULL,
  CONSTRAINT fk_int_fi_id FOREIGN KEY (int_fi_id, int_wkg_code) 
  REFERENCES dat.t_fish_fi(fi_id,fi_wkg_code) 
    ON UPDATE CASCADE ON DELETE CASCADE,
  int_tra_code TEXT NOT NULL,
  CONSTRAINT fk_int_tra_code FOREIGN KEY (int_tra_code) 
  REFERENCES ref.tr_trait_tra(tra_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  int_value numeric NULL,
  int_trv_code TEXT,
  CONSTRAINT fk_int_trv_tra_code FOREIGN KEY (int_trv_code, int_tra_code,int_wkg_code)
  REFERENCES ref.tr_traitvaluequal_trv(trv_code, trv_trq_code, trv_wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  int_trm_code TEXT,
  CONSTRAINT fk_int_trm_code FOREIGN KEY (int_trm_code)
  REFERENCES ref.tr_traitmethod_trm(trm_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  int_last_update date DEFAULT CURRENT_DATE NOT NULL,
  int_qal_code int4 NULL,
    CONSTRAINT fk_int_qal_id FOREIGN KEY (int_qal_code) 
  REFERENCES ref.tr_quality_qal(qal_code) ON UPDATE CASCADE,
  int_ver_code TEXT NOT NULL,
  CONSTRAINT fk_int_ver_code FOREIGN KEY (int_ver_code)
  REFERENCES ref.tr_version_ver(ver_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT uk_int_fi UNIQUE (int_fi_id, int_trm_code, int_wkg_code),
  CONSTRAINT t_indivtrait_int_pkey PRIMARY KEY (int_id, int_wkg_code),
  CONSTRAINT ck_qualitative_or_numeric CHECK 
  (
  (int_value IS NULL AND int_trv_code IS NOT NULL) OR
  (int_value IS NOT NULL AND int_trv_code IS  NULL)
  )
);
CREATE INDEX dat_t_indivtrait_int_idx ON dat.t_indivtrait_int USING btree (int_fi_id);

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

DROP TABLE IF EXISTS  dateel.t_indivtrait_int;
CREATE TABLE dateel.t_indivtrait_int (
 CONSTRAINT fk_int_ser_id FOREIGN KEY (int_ser_id)
    REFERENCES dateel.t_series_ser (ser_id) 
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_int_wkg_code  FOREIGN KEY (int_wkg_code)
  REFERENCES ref.tr_icworkinggroup_wkg(wkg_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_int_spe_code  FOREIGN KEY (int_spe_code)
  REFERENCES ref.tr_species_spe(spe_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_int_fi_id FOREIGN KEY (int_fi_id, int_wkg_code) 
  REFERENCES dateel.t_fish_fi(fi_id,fi_wkg_code) 
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_int_tra_code FOREIGN KEY (int_tra_code) 
  REFERENCES refeel.tg_trait_tra(tra_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_int_trv_tra_code FOREIGN KEY (int_trv_code, int_tra_code) 
  -- unlike in dat.t_indivtrait_int this one does not take ref TO wkgcode, no need AS we are in dateel
  REFERENCES refeel.tr_traitvaluequal_trv(trv_code, trv_trq_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_int_trm_code FOREIGN KEY (int_trm_code)
  REFERENCES refeel.tr_traitmethod_trm(trm_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_int_qal_id FOREIGN KEY (int_qal_code) 
  REFERENCES ref.tr_quality_qal(qal_code) ON UPDATE CASCADE,
  CONSTRAINT fk_int_ver_code FOREIGN KEY (int_ver_code)
  REFERENCES refeel.tr_version_ver(ver_code)
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT uk_trv_fi_id_trv_tra_code UNIQUE(int_fi_id,int_tra_code)
) INHERITS (dat.t_indivtrait_int);
CREATE INDEX dateel_t_indivtrait_int_idx ON dateel.t_indivtrait_int USING btree (int_fi_id);

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


COMMENT ON TABLE dateel.t_indivtrait_int IS 'Table joining fish and traits';
COMMENT ON COLUMN dateel.t_indivtrait_int.int_ser_id IS 'Series UUID';
COMMENT ON COLUMN dateel.t_indivtrait_int.int_wkg_code IS 'Working ind on of WGEEL, WGNAS, WGBAST ...';
COMMENT ON COLUMN dateel.t_indivtrait_int.int_spe_code IS 'Species code here ANG';
COMMENT ON COLUMN dateel.t_indivtrait_int.int_id IS 'ID, integer, unique for wkg_code';
COMMENT ON COLUMN dateel.t_indivtrait_int.int_fi_id IS 'ID of the fish';
COMMENT ON COLUMN dateel.t_indivtrait_int.int_tra_code IS 'Code of the trait, e.g. Lengthmm';
COMMENT ON COLUMN dateel.t_indivtrait_int.int_value IS 'Value for numeric';
COMMENT ON COLUMN dateel.t_indivtrait_int.int_trv_code IS 'Value for qualitative see refeel.tr_traitvaluequal_trv';
COMMENT ON COLUMN dateel.t_indivtrait_int.int_trm_code IS 'Method see refeel.tr_traimethod_trm';
COMMENT ON COLUMN dateel.t_indivtrait_int.int_last_update IS 'date last update';
COMMENT ON COLUMN dateel.t_indivtrait_int.int_qal_code IS 'Quality code references ref.tr_quality_qal';
COMMENT ON COLUMN dateel.t_indivtrait_int.int_ver_code IS 'version e.g. WGEEL_2024_1';

-- TODO trigger on date
SQL code to import individual metric tables 

/*
 * Insert numeric for indiv - series 
 */
DELETE FROM dateel.t_indivtrait_int;

INSERT INTO dateel.t_indivtrait_int
(int_ser_id, 
int_wkg_code, 
int_spe_code, 
int_id, 
int_fi_id, 
int_tra_code, 
int_value, 
int_trv_code, 
int_trm_code, 
int_last_update, 
int_qal_code, 
int_ver_code)
WITH a1 AS (
SELECT mei_fi_id,
       mei_value AS mei_method_sex 
FROM datwgeel.t_metricindsamp_meisa meisa
WHERE mei_mty_id = 27
AND mei_value IS NOT NULL),
 a2 AS (
 SELECT mei_fi_id,
       mei_value AS mei_method_anguillicola 
FROM datwgeel.t_metricindsamp_meisa meisa 
WHERE mei_mty_id = 28
AND mei_value IS NOT NULL),
mm AS (
SELECT coalesce(a1.mei_fi_id, a2.mei_fi_id)  AS mei_fi_id,
mei_method_sex,
mei_method_anguillicola
FROM  a1 FULL OUTER JOIN a2 ON a1.mei_fi_id = a2.mei_fi_id)
SELECT 
tss2.ser_id AS int_ser_id,
'WGEEL' AS int_wkg_code,
'ANG' AS int_spe_code,
fi_id AS int_id, 
meiser.mei_fi_id AS int_fi_id, 
CASE WHEN mei_mty_id = 1 THEN 'Lengthmm'
WHEN mei_mty_id = 2 THEN 'Weightg'
WHEN mei_mty_id = 3 THEN 'Ageyear'
WHEN mei_mty_id = 4 THEN 'Eye_diam_meanmm'
WHEN mei_mty_id = 5 THEN 'Pectoral_lengthmm'
WHEN mei_mty_id = 9 THEN 'Anguillicola_intensity'
WHEN mei_mty_id = 11 THEN 'Muscle_lipid' -- IF fatmeter insert later only if gravimeter does not exists
WHEN mei_mty_id = 12 THEN 'Sum_6_pcb'
WHEN mei_mty_id = 15 THEN 'Pb'
WHEN mei_mty_id = 16 THEN 'Hg'
WHEN mei_mty_id = 17 THEN 'Cd'
WHEN mei_mty_id = 26 THEN 'Teq'
ELSE 'problem' END AS int_tra_code,
mei_value AS int_value, 
NULL AS int_trv_code, -- there ARE NO qualitative VALUES FOR GROUP metrics
CASE WHEN mei_mty_id = 11 THEN 'Muscle_lipid_gravimeter' -- different method see METHOD
WHEN mei_mty_id = 9 AND mei_method_anguillicola = 1 THEN 'Anguillicola_stereomicroscope_count'
WHEN mei_mty_id = 9 AND mei_method_anguillicola = 0 THEN 'Anguillicola_visual_count'
ELSE NULL END AS int_trm_code,
mei_last_update AS int_last_update, 
mei_qal_id AS int_qal_code, 
CASE WHEN mei_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN mei_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN mei_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN mei_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN mei_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN mei_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS int_ver_code
FROM datwgeel.t_metricindseries_meiser meiser
JOIN datwgeel.t_fishseries_fiser fiser ON meiser.mei_fi_id = fi_id
JOIN datwgeel.t_series_ser AS tss ON fiser_ser_id = ser_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= ser_nameshort
LEFT JOIN mm ON meiser.mei_fi_id = mm.mei_fi_id -- joining subquery
WHERE mei_mty_id IN (1,2,3,4,5,9,11,12,15,16,17,26); --1145525


--10 Muscle lipid fatmeter




INSERT INTO dateel.t_indivtrait_int
(int_ser_id, 
int_wkg_code, 
int_spe_code, 
int_id, 
int_fi_id, 
int_tra_code, 
int_value, 
int_trv_code, 
int_trm_code, 
int_last_update, 
int_qal_code, 
int_ver_code)
WITH fatmeter AS (
SELECT 
tss2.ser_id AS int_ser_id,
'WGEEL' AS int_wkg_code,
'ANG' AS int_spe_code,
fi_id AS int_id, 
meiser.mei_fi_id AS int_fi_id, 
CASE WHEN mei_mty_id = 10 THEN  'Muscle_lipid' 
ELSE 'problem' END AS int_tra_code,
mei_value AS int_value, 
NULL AS int_trv_code, 
CASE WHEN mei_mty_id = 10 THEN 'Muscle_lipid_fatmeter' -- different method see method
ELSE NULL END AS int_trm_code,
mei_last_update AS int_last_update, 
mei_qal_id AS int_qal_code, 
CASE WHEN mei_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN mei_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN mei_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN mei_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN mei_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN mei_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS int_ver_code
FROM datwgeel.t_metricindseries_meiser meiser
JOIN datwgeel.t_fishseries_fiser fiser ON meiser.mei_fi_id = fi_id
JOIN datwgeel.t_series_ser AS tss ON fiser_ser_id = ser_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= ser_nameshort
WHERE mei_mty_id IN (10))
SELECT * FROM fatmeter WHERE  int_fi_id NOT IN 
(SELECT int_fi_id FROM dateel.t_indivtrait_int WHERE int_tra_code = 'Muscle_lipid')
; --0

/*
 * Insert numeric for  - sampling
 * Some values have both gravimeter and fatmeter, gravimeter chosen.
 */


INSERT INTO dateel.t_indivtrait_int
(int_ser_id, 
int_wkg_code, 
int_spe_code, 
int_id, 
int_fi_id, 
int_tra_code, 
int_value, 
int_trv_code, 
int_trm_code, 
int_last_update, 
int_qal_code, 
int_ver_code)
WITH a1 AS (
SELECT mei_fi_id,
       mei_value AS mei_method_sex 
FROM datwgeel.t_metricindsamp_meisa meisa
WHERE mei_mty_id = 27
AND mei_value IS NOT NULL),
 a2 AS (
 SELECT mei_fi_id,
       mei_value AS mei_method_anguillicola 
FROM datwgeel.t_metricindsamp_meisa meisa 
WHERE mei_mty_id = 28
AND mei_value IS NOT NULL),
mm AS (
SELECT coalesce(a1.mei_fi_id, a2.mei_fi_id)  AS mei_fi_id,
mei_method_sex,
mei_method_anguillicola
FROM  a1 FULL OUTER JOIN a2 ON a1.mei_fi_id = a2.mei_fi_id)
SELECT 
tss2.ser_id AS int_ser_id,
'WGEEL' AS int_wkg_code,
'ANG' AS int_spe_code,
mei_id AS int_id, 
meisa.mei_fi_id AS int_fi_id, 
CASE WHEN mei_mty_id = 1 THEN 'Lengthmm'
WHEN mei_mty_id = 2 THEN 'Weightg'
WHEN mei_mty_id = 3 THEN 'Ageyear'
WHEN mei_mty_id = 4 THEN 'Eye_diam_meanmm'
WHEN mei_mty_id = 5 THEN 'Pectoral_lengthmm'
WHEN mei_mty_id = 9 THEN 'Anguillicola_intensity'
WHEN mei_mty_id = 11 THEN 'Muscle_lipid' -- IF fatmeter insert later only if gravimeter does not exists
WHEN mei_mty_id = 12 THEN 'Sum_6_pcb'
WHEN mei_mty_id = 15 THEN 'Pb'
WHEN mei_mty_id = 16 THEN 'Hg'
WHEN mei_mty_id = 17 THEN 'Cd'
WHEN mei_mty_id = 26 THEN 'Teq'
ELSE 'problem' END AS int_tra_code,
mei_value AS int_value, 
NULL AS int_trv_code,
CASE WHEN mei_mty_id = 11 THEN 'Muscle_lipid_gravimeter' -- different method see METHOD
WHEN mei_mty_id = 9 AND mei_method_anguillicola = 1 THEN 'Anguillicola_stereomicroscope_count'
WHEN mei_mty_id = 9 AND mei_method_anguillicola = 0 THEN 'Anguillicola_visual_count'
ELSE NULL END AS int_trm_code,
mei_last_update AS int_last_update, 
mei_qal_id AS int_qal_code, 
CASE WHEN mei_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN mei_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN mei_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN mei_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN mei_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN mei_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS int_ver_code
FROM datwgeel.t_metricindsamp_meisa meisa
JOIN datwgeel.t_fishsamp_fisa  ON meisa.mei_fi_id = fi_id
JOIN datwgeel.t_samplinginfo_sai AS tss ON fisa_sai_id = sai_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= sai_id::TEXT
LEFT JOIN mm ON meisa.mei_fi_id = mm.mei_fi_id -- joining subquery
WHERE mei_mty_id IN (1,2,3,4,5,9,11,12,15,16,17,26); --298005


-- Insert only fatmeter where gravimeter does not exist

INSERT INTO dateel.t_indivtrait_int
(int_ser_id, 
int_wkg_code, 
int_spe_code, 
int_id, 
int_fi_id, 
int_tra_code, 
int_value, 
int_trv_code, 
int_trm_code, 
int_last_update, 
int_qal_code, 
int_ver_code)
WITH fatmeter AS (
SELECT 
tss2.ser_id AS int_ser_id,
'WGEEL' AS int_wkg_code,
'ANG' AS int_spe_code,
mei_id AS int_id, 
meisa.mei_fi_id AS int_fi_id, 
CASE WHEN mei_mty_id = 10 THEN  'Muscle_lipid' 
ELSE 'problem' END AS int_tra_code,
mei_value AS int_value, 
NULL AS int_trv_code, -- there ARE NO qualitative VALUES FOR GROUP metrics
CASE WHEN mei_mty_id = 10 THEN 'Muscle_lipid_fatmeter' -- different method see method
ELSE NULL END AS int_trm_code,
mei_last_update AS int_last_update, 
mei_qal_id AS int_qal_code, 
CASE WHEN mei_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN mei_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN mei_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN mei_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN mei_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN mei_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS int_ver_code
FROM datwgeel.t_metricindsamp_meisa meisa
JOIN datwgeel.t_fishsamp_fisa  ON meisa.mei_fi_id = fi_id
JOIN datwgeel.t_samplinginfo_sai AS tss ON fisa_sai_id = sai_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= sai_id::text
WHERE mei_mty_id = 10)
SELECT * FROM fatmeter WHERE  int_fi_id NOT IN 
(SELECT int_fi_id FROM dateel.t_indivtrait_int WHERE int_tra_code = 'Muscle_lipid')
; --100



-- Qualitative traits series


INSERT INTO dateel.t_indivtrait_int
(int_ser_id, 
int_wkg_code, 
int_spe_code, 
int_id, 
int_fi_id, 
int_tra_code, 
int_value, 
int_trv_code, 
int_trm_code, 
int_last_update, 
int_qal_code, 
int_ver_code)
WITH a1 AS (
SELECT mei_fi_id,
       mei_value AS mei_method_sex 
FROM datwgeel.t_metricindseries_meiser 
WHERE mei_mty_id = 27
AND mei_value IS NOT NULL),
 a2 AS (
 SELECT mei_fi_id,
       mei_value AS mei_method_anguillicola 
FROM datwgeel.t_metricindseries_meiser 
WHERE mei_mty_id = 28
AND mei_value IS NOT NULL),
mm AS (
SELECT coalesce(a1.mei_fi_id, a2.mei_fi_id)  AS mei_fi_id,
mei_method_sex,
mei_method_anguillicola
FROM  a1 FULL OUTER JOIN a2 ON a1.mei_fi_id = a2.mei_fi_id)
SELECT 
tss2.ser_id AS int_ser_id,
'WGEEL' AS int_wkg_code,
'ANG' AS int_spe_code,
fi_id AS int_id, 
meiser.mei_fi_id AS int_fi_id, 
CASE 
WHEN mei_mty_id = 6 THEN 'Sex'
WHEN mei_mty_id = 7 THEN 'Is_differentiated'
WHEN mei_mty_id = 8 THEN 'Anguillicola_presence'
WHEN mei_mty_id = 13 THEN 'Evex_presence'
WHEN mei_mty_id = 14 THEN 'Hva_presence'
ELSE 'problem' END AS int_tra_code,
NULL AS int_value, 
CASE 
WHEN mei_mty_id = 6 AND mei_value = 1 THEN 'F'
WHEN mei_mty_id = 6 AND mei_value = 0 THEN 'M'
WHEN mei_mty_id = 7 AND mei_value = 1 THEN 'Y'
WHEN mei_mty_id = 7 AND mei_value = 0 THEN 'N'
WHEN mei_mty_id = 8 AND mei_value = 1 THEN 'Y'
WHEN mei_mty_id = 8 AND mei_value = 0 THEN 'N'
WHEN mei_mty_id = 13 AND mei_value= 1 THEN 'Y' 
WHEN mei_mty_id = 13 AND mei_value= 0 THEN 'N'
WHEN mei_mty_id = 14 AND mei_value= 1 THEN 'Y' 
WHEN mei_mty_id = 14 AND mei_value= 0 THEN 'N'
ELSE NULL END AS int_trv_code,
CASE 
WHEN mei_mty_id = 6 AND mei_method_sex = 1 THEN 'Gonadal_inspection'
WHEN mei_mty_id = 6 AND mei_method_sex = 0 THEN 'Length_based_sex'
WHEN mei_mty_id = 8 AND mei_method_anguillicola = 1 THEN 'Anguillicola_stereomicroscope_count'
WHEN mei_mty_id = 8 AND mei_method_anguillicola = 0 THEN 'Anguillicola_visual_count'
ELSE NULL END AS int_trm_code,
mei_last_update AS int_last_update, 
mei_qal_id AS int_qal_code, 
CASE WHEN mei_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN mei_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN mei_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN mei_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN mei_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN mei_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS int_ver_code
FROM datwgeel.t_metricindseries_meiser meiser
JOIN datwgeel.t_fishseries_fiser fiser ON meiser.mei_fi_id = fi_id
JOIN datwgeel.t_series_ser AS tss ON fiser_ser_id = ser_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= ser_nameshort
LEFT JOIN mm ON meiser.mei_fi_id = mm.mei_fi_id -- joining subquery
WHERE mei_mty_id IN (6,7,8,13,14); --189327 (pigment stage is not yet in the db)

-- Qualitative trait sampling


INSERT INTO dateel.t_indivtrait_int
(int_ser_id, 
int_wkg_code, 
int_spe_code, 
int_id, 
int_fi_id, 
int_tra_code, 
int_value, 
int_trv_code, 
int_trm_code, 
int_last_update, 
int_qal_code, 
int_ver_code)
-- extract method from table
-- this will extract 3 columns, mei_fi_id, mei_method_anguillicola and mei_method_sex
WITH a1 AS (
SELECT mei_fi_id,
       mei_value AS mei_method_sex 
FROM datwgeel.t_metricindsamp_meisa meisa
WHERE mei_mty_id = 27
AND mei_value IS NOT NULL),
 a2 AS (
 SELECT mei_fi_id,
       mei_value AS mei_method_anguillicola 
FROM datwgeel.t_metricindsamp_meisa meisa 
WHERE mei_mty_id = 28
AND mei_value IS NOT NULL),
mm AS (
SELECT coalesce(a1.mei_fi_id, a2.mei_fi_id)  AS mei_fi_id,
mei_method_sex,
mei_method_anguillicola
FROM  a1 FULL OUTER JOIN a2 ON a1.mei_fi_id = a2.mei_fi_id)
-- Insert select query
SELECT 
tss2.ser_id AS int_ser_id,
'WGEEL' AS int_wkg_code,
'ANG' AS int_spe_code,
mei_id AS int_id, 
meisa.mei_fi_id AS int_fi_id, 
CASE 
WHEN mei_mty_id = 6 THEN 'Sex'
WHEN mei_mty_id = 7 THEN 'Is_differentiated'
WHEN mei_mty_id = 8 THEN 'Anguillicola_presence'
WHEN mei_mty_id = 13 THEN 'Evex_presence'
WHEN mei_mty_id = 14 THEN 'Hva_presence'
ELSE 'problem' END AS int_tra_code,
NULL AS int_value, 
CASE 
WHEN mei_mty_id = 6 AND mei_value = 1 THEN 'F'
WHEN mei_mty_id = 6 AND mei_value = 0 THEN 'M'
WHEN mei_mty_id = 7 AND mei_value = 1 THEN 'Y'
WHEN mei_mty_id = 7 AND mei_value = 0 THEN 'N'
WHEN mei_mty_id = 8 AND mei_value = 1 THEN 'Y'
WHEN mei_mty_id = 8 AND mei_value = 0 THEN 'N'
WHEN mei_mty_id = 13 AND mei_value= 1 THEN 'Y' 
WHEN mei_mty_id = 13 AND mei_value= 0 THEN 'N'
WHEN mei_mty_id = 14 AND mei_value= 1 THEN 'Y' 
WHEN mei_mty_id = 14 AND mei_value= 0 THEN 'N'
ELSE NULL END AS int_trv_code,
CASE 
WHEN mei_mty_id = 6 AND mei_method_sex = 1 THEN 'Gonadal_inspection'
WHEN mei_mty_id = 6 AND mei_method_sex = 0 THEN 'Length_based_sex'
WHEN mei_mty_id = 8 AND mei_method_anguillicola = 1 THEN 'Anguillicola_stereomicroscope_count'
WHEN mei_mty_id = 8 AND mei_method_anguillicola = 0 THEN 'Anguillicola_visual_count'
ELSE NULL END AS int_trm_code,
mei_last_update AS int_last_update, 
mei_qal_id AS int_qal_code, 
CASE WHEN mei_dts_datasource = 'dc_2019' THEN 'WGEEL-2019-1'
     WHEN mei_dts_datasource = 'dc_2020' THEN 'WGEEL-2020-1'
     WHEN mei_dts_datasource ='dc_2021' THEN 'WGEEL-2021-1'
     WHEN mei_dts_datasource ='dc_2022' THEN 'WGEEL-2022-1'
     WHEN mei_dts_datasource ='dc_2023' THEN 'WGEEL-2023-1'
     WHEN mei_dts_datasource ='dc_2024' THEN 'WGEEL-2024-1'
     ELSE 'WGEEL-2018-1' END AS int_ver_code
FROM datwgeel.t_metricindsamp_meisa meisa
JOIN datwgeel.t_fishsamp_fisa  ON meisa.mei_fi_id = fi_id
JOIN datwgeel.t_samplinginfo_sai AS tss ON fisa_sai_id = sai_id
JOIN dateel.t_series_ser AS tss2 ON ser_code= sai_id::TEXT
LEFT JOIN mm ON meisa.mei_fi_id = mm.mei_fi_id -- joining subquery
WHERE mei_mty_id IN (6,7,8,13,14); --163914 (pigment stage is not yet in the db)
Table 24: Table t_indivtrait_int, fish metric table
int_ser_id int_wkg_code int_spe_code int_id int_fi_id int_tra_code int_value int_trv_code int_trm_code int_last_update int_qal_code int_ver_code
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413553 3413553 Pectoral_lengthmm 15.490 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413553 3413553 Eye_diam_meanmm 6.035 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413553 3413553 Weightg 32.100 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413553 3413553 Lengthmm 290.000 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413535 3413535 Pectoral_lengthmm 17.250 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413535 3413535 Eye_diam_meanmm 5.660 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413535 3413535 Weightg 58.200 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413535 3413535 Lengthmm 328.000 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413502 3413502 Pectoral_lengthmm 9.300 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413502 3413502 Eye_diam_meanmm 4.715 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413502 3413502 Weightg 42.000 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413502 3413502 Lengthmm 308.000 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413498 3413498 Pectoral_lengthmm 12.520 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413498 3413498 Eye_diam_meanmm 4.510 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413498 3413498 Weightg 59.250 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413498 3413498 Lengthmm 316.000 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413562 3413562 Pectoral_lengthmm 15.000 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413562 3413562 Eye_diam_meanmm 6.930 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413562 3413562 Weightg 57.200 NA NA 2024-09-12 1 WGEEL-2024-1
0424851e-9533-4e08-91c8-239e05dd5b73 WGEEL ANG 3413562 3413562 Lengthmm 315.000 NA NA 2024-09-12 1 WGEEL-2024-1

[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, the work will be finished and this can be presented during wgeel.

Import data from WP2 (TODO)

Import to ICES (TODO)

Acknowledgements

Maria and Johana for their help.

References

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

EU is not reponsible for the content of the project