Electrofishing database

DIASPARA WP3.2 final

Structure for the electrofishing database

1 Introduction

Electrofishing is a method widely used to sample the fish populations in the aquatic environment (Cowx and Lamarque 1990). It uses electric current between a cathode submersed in the water and a hand held anode (Lamarque 1976). Fishes are first affected in their movements by electricity, and then stunned. Fishing operators operate around the anode with nets to catch the fish. Depending on the environment, electrofishing provides quantitative, or semi-quantitative estimations of the number of fish per species (with additional biometry information), which can be related to the surface of electrofished area or duration of electrofishing, to provide either density estimates or semi-quantitative estimates of abundance. In wadeable environments, the use of several passes (i.e., electrofishing the same area multiple times) allows the application of depletion statistical methods to estimate the abundance. However such operations are time consuming. There is a possibility to raise the single pass electrofishing using an average electrofishing efficiency to estimate eel densities. The type of electrofishing can be used to assess the probability of capture in the delta model when using Eel Density Analysis (EDA) (Briand et al. 2022). Capture probabilities can be inferred from habitat characteristics (Millar et al. 2016) for salmon in Scotland.

The European Water Framework Directive (Directive 2000/60/EC) establishes the legal framework for achieving “good ecological status” in all surface waters throughout the European Union. To meet this objective, the directive requires Member States to implement standardized biological monitoring programs, including the assessment of fish communities as one of the core biological quality elements. Electrofishing has therefore become a fundamental and widely applied method for freshwater fish monitoring across Europe, providing quantitative and comparable information on species composition, abundance, and size structure. Electrofishing operations are conducted routinely in most European countries to sample freshwater populations to assess their status in the frame of the Water Framework Directive (WFD) and these data could support the development of regional model for eel (“Report of the Workshop on the Development of a Spatial Database and Model for Eels (WKSMEEL)” 2024)

Electofishing operations are also applied in conservation-relevant fish sampling surveys in lotic freshwaters (e.g. Habitat Directive applications). Specific protocols are developed for salmonids (Bohlin et al. 1989) and eel (Lambert, Feunteun, and Rigaud 1994) to better account for fish behaviour and the reaction of fish to the current. Salmonids will react very quickly and need to be caught early in nets, and the timing of the operation is used. On the contrary, eels which often buries in the sediment or under boulders and rocks requires a long time to react to the electrical current. Short stop of the electrical current or movement of the electrode are required to make the eel out of their shelter, and for that, reason eel-specific protocols sometimes require the anode to stay in place for at least 30 seconds (Germis 2009). In deeper areas, electrofishing can be carried out from a boat to provide indices of the abundance of fishes (Tomanova et al. 2013). These indices, while they do not provide any quantitative assessment of abundance, can be used to model spatial variation in eel abundance (Briand et al. 2022).

As methods used in electrofishing can vary widely, it is necessary to collect information on the different protocols. For instance, electrofishing can be carried out by foot or by boat, or using a mixture of boat and wading in the stream. The apparatus used, and type of electric current used (DC or crenated) is important both in terms of efficiency and fish injuries (Pottier, Nevoux, and Marchand 2020). The technique used (number of nets, number of anodes, size of nets, fishing protocol) varies depending on the country, the objective of the operation and the type of habitat, and this is true even for specific studies (e.g. trout ICES and WGTRUTTA (2024)).

Multiple environmental factors can influence electrofishing efficiency including habitat (water conductivity, depth, riparian cover), turbidity, habitat, time, equipment personnel and protocol, and fish behaviour Millar et al. (2016). The size‑selectivity of electrofishing may also be biased, as most electrofished habitats are shallow. In the case of eels, shallow areas typically hold a greater proportion of small individuals

Electrofishing operations are conducted, as an example, by national and regional agencies for the Water Framework Directive (WFD) or with specific diadromous surveys objectives. Both sources have been identified as a major potential source of information for developing regional spatial models for Anguilla anguilla (European eel), Salmo salar (Salmon), and Trout (Salmo trutta) particularly because of their broad geographic coverage and long time-series availability. More broadly, electrofishing data form the basis of freshwater fish monitoring programmes in many countries, providing standardized measures of species presence, abundance, and size structure.

Despite the widespread use, electrofishing data are often dispersed across institutions, heterogeneously formatted, or underutilized for regional analyses and international stock assessments. These limitations highlight the need to design and implement a coherent, standardized electrofishing database, tailored to the needs of ICES working groups, and capable of integrating datasets from multiple sources, survey protocols, and monitoring objectives.

In recent years, the SUDOANG project has further developed a centralized database called dbeel, initiated during the POSE project, that harmonizes electrofishing datasets for eel monitoring across the Iberian Peninsula. The SUDOANG electrofishing database demonstrated the feasibility and value of structuring heterogeneous survey data into a unified framework, enabling its use in large‑scale assessments, modelling. Building on this foundation, our current work aims to adapt the SUDOANG database structure to support multispecific electrofishing surveys rather than eel‑focused datasets alone. This database is also made fully compatible with the DIADROMOUS database developed during the DIASPARA project. Furthermore, the use of a hierarchical structure provides an easy way to update data coming from regional or national authorities.

The objective of this report is to present the detailed code for building such a database in PostgreSQL and develop the excel format in support of future data calls for the WGEEL.

2 Creating a database

2.1 Database structure

The electrofishing starts with a location which forms the top table (Station) (Figure 1). To this location, an ‘event’ is added corresponding to a single fishing operation carried out at a certain time. This operation details elements about the station at the time of electrofishing, like the water surface, fishing conditions, equipment used, protocols … The use of gear operations in lakes (like fyke net fishing) is developed into that structure, so basically different types of operations will (or might) be used. The Batch contains results about the fishing operation, either considered as a whole or per pass. So this can be an estimate of density, an estimate of number caught, or the number caught per pass. The densities can be reported according to different methods. The fish table follows the batch structure but is made for an individual fish, to which all types of biological characteristics used in the Diadromous DB can be attached.

flowchart TB
    A([Station]):::table
    A --> B([Electrofishing operation]):::table
    A --> C([Fishing gear operation]):::table
    B -->|pass| D([Batch Ope]):::table
    C --> D([Batch Ope]):::table
    D --> E([Fish]):::table
    E --> F([Biological Characteristics]):::table    
    classDef table fill:#CD88
Figure 1: Simplied structure of the electrofishing database

Inherited tables

To handle the updates of data, the database uses inheritance. The data are entered in the country schema, in this schema all tables inherit from the tables in the main dam schema (Figure 2). When updates are collected, one can simply upload all tables again in the country schema. Other columns can be added at the national level to collect additional information.

flowchart TB
    A([Station]):::main
    A --> B([Electrofishing operation]):::main
    A([Station]):::table
    %% Short Path
    A1([Station FR]):::table1 .-> A
    A2([Station ES]):::table2  .-> A
    A3([Station ES]):::table3 .-> A  
    B1([Electrofishing operation FR]):::table1  --> B
    B2([Electrofishing operation FR]):::table2  --> B
    B3([Electrofishing operation FR]):::table3  --> B
    classDef table fill:#CD88
    classDef main fill:#268073,color:white
    classDef table1 fill:#C3CA3C
    classDef table2 fill:#5C308A,color:white
    classDef table3 fill:#CC783D
Figure 2: Inheritance in obstruction places tables

3 Annex : SQL code

The code to create the database is available in the Dam report as the nomenclature is common to the two database and inherted. The code is derived from the POSE project SLIME (Walker et al. 2011), but cleaned up and adapted to the new diadromous Database vocabularies.

3.1 Annex 1 : SQL code users

SQL code to create user rights 
-- Should be created only once
-- each role groups will appear and all databases
-- note the roless might already have been created on the server.... so check ...




createDB -U postgres migdb


--- extension management
CREATE EXTENSION "uuid-ossp" SCHEMA "public";
CREATE EXTENSION postgis SCHEMA "public";
-- here we create an admin password, you probably want to use one local admin there to change this
-- everywhere
CREATE USER diaspara_admin ;
CREATE USER diaspara_read;

GRANT CONNECT ON DATABASE "diaspara" TO diaspara_read;
ALTER DATABASE "diaspara" OWNER TO diaspara_admin;
ALTER ROLE diaspara_read  WITH LOGIN;




GRANT ALL PRIVILEGES ON SCHEMA public TO diaspara_admin ;


-- Should be done each time the database is created
--- role management
GRANT CONNECT ON DATABASE damdb TO diaspara_read;
ALTER DATABASE damdb OWNER TO diaspara_admin;

-- grant roles for diaspara_read
GRANT USAGE 
    ON SCHEMA dam
    TO diaspara_read;

GRANT EXECUTE 
    ON ALL FUNCTIONS
    IN SCHEMA dam
    TO diaspara_read;

GRANT SELECT
    ON ALL TABLES
    IN SCHEMA dam
    TO diaspara_read;
    

GRANT USAGE 
    ON SCHEMA nomenclature
    TO diaspara_read;

GRANT EXECUTE 
    ON ALL FUNCTIONS
    IN SCHEMA nomenclature
    TO diaspara_read;

GRANT SELECT
    ON ALL TABLES
    IN SCHEMA nomenclature
    TO diaspara_read;

   
GRANT USAGE 
    ON SCHEMA dam_france
    TO diaspara_read;

GRANT EXECUTE 
    ON ALL FUNCTIONS
    IN SCHEMA dam_france
    TO diaspara_read;

GRANT SELECT
    ON ALL TABLES
    IN SCHEMA dam_france
    TO diaspara_read;

3.2 Annex 2 : SQL code nomenclature tables

The nomenclature has been adapted to the DIASPARA project using the vocab in stockDB.

SQL code to create nomenclature 
--
-- PostgreSQL database dump
--

-- Dumped from database version 14.22 (Ubuntu 14.22-0ubuntu0.22.04.1)
-- Dumped by pg_dump version 17.5

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: nomenclature; Type: SCHEMA; Schema: -; Owner: diaspara_admin
--

CREATE SCHEMA nomenclature;


ALTER SCHEMA nomenclature OWNER TO diaspara_admin;

--
-- Name: nomenclature_id_insert(); Type: FUNCTION; Schema: nomenclature; Owner: postgres
--

CREATE FUNCTION nomenclature.nomenclature_id_insert() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  PERFORM no_id FROM "nomenclature".nomenclature WHERE no_id = NEW.no_id;
  IF FOUND THEN
   RAISE EXCEPTION '1- Invalid no_id (%)', NEW.no_id;
  ELSE
   RETURN NEW;
  END IF;
END
$$;


ALTER FUNCTION nomenclature.nomenclature_id_insert() OWNER TO postgres;

--
-- Name: nomenclature_id_update(); Type: FUNCTION; Schema: nomenclature; Owner: postgres
--

CREATE FUNCTION nomenclature.nomenclature_id_update() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  PERFORM no_id FROM "nomenclature".nomenclature WHERE no_id = NEW.no_id;
  IF FOUND THEN
   RETURN NEW;
  ELSE
   RAISE EXCEPTION '1- Invalid no_id (%)', NEW.no_id;
  END IF;
END
$$;


ALTER FUNCTION nomenclature.nomenclature_id_update() OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: nomenclature; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.nomenclature (
    no_id integer NOT NULL,
    no_code text,
    no_type character varying(60),
    no_name character varying(80)
);


ALTER TABLE nomenclature.nomenclature OWNER TO diaspara_admin;

--
-- Name: age; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.age (
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.age OWNER TO diaspara_admin;

--
-- Name: biological_characteristic_type; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.biological_characteristic_type (
    bc_label character varying(100),
    bc_unit character varying(25),
    bc_data_type character varying(25)
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.biological_characteristic_type OWNER TO diaspara_admin;

--
-- Name: downstream_mitigation_measure; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.downstream_mitigation_measure (
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.downstream_mitigation_measure OWNER TO diaspara_admin;

--
-- Name: effort_type; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.effort_type (
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.effort_type OWNER TO diaspara_admin;

--
-- Name: electrofishing_mean; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.electrofishing_mean (
    ef_definition character varying(500)
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.electrofishing_mean OWNER TO diaspara_admin;

--
-- Name: event_change; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.event_change (
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.event_change OWNER TO diaspara_admin;

--
-- Name: fishway_type; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.fishway_type (
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.fishway_type OWNER TO diaspara_admin;

--
-- Name: gear_type; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.gear_type (
    isscfg_code character varying(6),
    main_gear character varying(60),
    eel_specific_gear character varying(60)
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.gear_type OWNER TO diaspara_admin;

--
-- Name: maturity; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.maturity (
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.maturity OWNER TO diaspara_admin;

--
-- Name: migration_direction; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.migration_direction (
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.migration_direction OWNER TO diaspara_admin;

--
-- Name: nomenclature_no_id_seq; Type: SEQUENCE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE SEQUENCE nomenclature.nomenclature_no_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE nomenclature.nomenclature_no_id_seq OWNER TO diaspara_admin;

--
-- Name: nomenclature_no_id_seq; Type: SEQUENCE OWNED BY; Schema: nomenclature; Owner: diaspara_admin
--

ALTER SEQUENCE nomenclature.nomenclature_no_id_seq OWNED BY nomenclature.nomenclature.no_id;


--
-- Name: obstruction_type; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.obstruction_type (
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.obstruction_type OWNER TO diaspara_admin;

--
-- Name: TABLE obstruction_type; Type: COMMENT; Schema: nomenclature; Owner: diaspara_admin
--

COMMENT ON TABLE nomenclature.obstruction_type IS 'This table used to contain only physical obstruction as type, the types have been extended during sudoang';


--
-- Name: orient_flow; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.orient_flow (
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.orient_flow OWNER TO diaspara_admin;

--
-- Name: scientific_observation_method; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.scientific_observation_method (
    sc_observation_category character varying(30),
    sc_definition character varying(500)
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.scientific_observation_method OWNER TO diaspara_admin;

--
-- Name: sex; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.sex (
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.sex OWNER TO diaspara_admin;

--
-- Name: species; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.species (
    sp_vernacular_name character varying(30)
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.species OWNER TO diaspara_admin;

--
-- Name: stage; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.stage (
    spe_code text
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.stage OWNER TO diaspara_admin;

--
-- Name: turbine_type; Type: TABLE; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TABLE nomenclature.turbine_type (
)
INHERITS (nomenclature.nomenclature);


ALTER TABLE nomenclature.turbine_type OWNER TO diaspara_admin;

--
-- Name: age no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.age ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: biological_characteristic_type no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.biological_characteristic_type ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: downstream_mitigation_measure no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.downstream_mitigation_measure ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: effort_type no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.effort_type ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: electrofishing_mean no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.electrofishing_mean ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: event_change no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.event_change ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: fishway_type no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.fishway_type ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: gear_type no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.gear_type ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: maturity no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.maturity ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: migration_direction no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.migration_direction ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: nomenclature no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.nomenclature ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: obstruction_type no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.obstruction_type ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: orient_flow no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.orient_flow ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: scientific_observation_method no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.scientific_observation_method ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: sex no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.sex ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: species no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.species ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: stage no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.stage ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: turbine_type no_id; Type: DEFAULT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.turbine_type ALTER COLUMN no_id SET DEFAULT nextval('nomenclature.nomenclature_no_id_seq'::regclass);


--
-- Name: age age_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.age
    ADD CONSTRAINT age_id PRIMARY KEY (no_id);


--
-- Name: biological_characteristic_type bc_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.biological_characteristic_type
    ADD CONSTRAINT bc_id PRIMARY KEY (no_id);


--
-- Name: downstream_mitigation_measure c_pk_downstream_mitigation_measure; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.downstream_mitigation_measure
    ADD CONSTRAINT c_pk_downstream_mitigation_measure PRIMARY KEY (no_id);


--
-- Name: electrofishing_mean ef_mean_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.electrofishing_mean
    ADD CONSTRAINT ef_mean_id PRIMARY KEY (no_id);


--
-- Name: effort_type effort_type_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.effort_type
    ADD CONSTRAINT effort_type_id PRIMARY KEY (no_id);


--
-- Name: event_change event_change_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.event_change
    ADD CONSTRAINT event_change_id PRIMARY KEY (no_id);


--
-- Name: fishway_type fishway_type_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.fishway_type
    ADD CONSTRAINT fishway_type_id PRIMARY KEY (no_id);


--
-- Name: gear_type gear_type_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.gear_type
    ADD CONSTRAINT gear_type_id PRIMARY KEY (no_id);


--
-- Name: maturity maturity_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.maturity
    ADD CONSTRAINT maturity_id PRIMARY KEY (no_id);


--
-- Name: migration_direction migration_direction_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.migration_direction
    ADD CONSTRAINT migration_direction_id PRIMARY KEY (no_id);


--
-- Name: nomenclature nomenclature_pkey; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.nomenclature
    ADD CONSTRAINT nomenclature_pkey PRIMARY KEY (no_id);


--
-- Name: obstruction_type obstruction_type_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.obstruction_type
    ADD CONSTRAINT obstruction_type_id PRIMARY KEY (no_id);


--
-- Name: orient_flow orient_flow_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.orient_flow
    ADD CONSTRAINT orient_flow_id PRIMARY KEY (no_id);


--
-- Name: scientific_observation_method sc_observ_method_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.scientific_observation_method
    ADD CONSTRAINT sc_observ_method_id PRIMARY KEY (no_id);


--
-- Name: sex sex_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.sex
    ADD CONSTRAINT sex_id PRIMARY KEY (no_id);


--
-- Name: species sp_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.species
    ADD CONSTRAINT sp_id PRIMARY KEY (no_id);


--
-- Name: stage st_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.stage
    ADD CONSTRAINT st_id PRIMARY KEY (no_id);


--
-- Name: turbine_type turbine_type_id; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.turbine_type
    ADD CONSTRAINT turbine_type_id PRIMARY KEY (no_id);


--
-- Name: species uk_no_code; Type: CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.species
    ADD CONSTRAINT uk_no_code UNIQUE (no_code);


--
-- Name: downstream_mitigation_measure tr_downstream_mitigation_measure_insert; Type: TRIGGER; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TRIGGER tr_downstream_mitigation_measure_insert BEFORE INSERT ON nomenclature.downstream_mitigation_measure FOR EACH ROW EXECUTE FUNCTION nomenclature.nomenclature_id_insert();


--
-- Name: downstream_mitigation_measure tr_downstream_mitigation_measure_update; Type: TRIGGER; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TRIGGER tr_downstream_mitigation_measure_update BEFORE UPDATE ON nomenclature.downstream_mitigation_measure FOR EACH ROW EXECUTE FUNCTION nomenclature.nomenclature_id_update();


--
-- Name: effort_type tr_effort_insert; Type: TRIGGER; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TRIGGER tr_effort_insert BEFORE INSERT ON nomenclature.effort_type FOR EACH ROW EXECUTE FUNCTION nomenclature.nomenclature_id_insert();


--
-- Name: effort_type tr_effort_update; Type: TRIGGER; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TRIGGER tr_effort_update BEFORE UPDATE ON nomenclature.effort_type FOR EACH ROW EXECUTE FUNCTION nomenclature.nomenclature_id_update();


--
-- Name: event_change tr_event_change_insert; Type: TRIGGER; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TRIGGER tr_event_change_insert BEFORE INSERT ON nomenclature.event_change FOR EACH ROW EXECUTE FUNCTION nomenclature.nomenclature_id_insert();


--
-- Name: event_change tr_event_change_update; Type: TRIGGER; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TRIGGER tr_event_change_update BEFORE UPDATE ON nomenclature.event_change FOR EACH ROW EXECUTE FUNCTION nomenclature.nomenclature_id_update();


--
-- Name: fishway_type tr_fishway_type_insert; Type: TRIGGER; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TRIGGER tr_fishway_type_insert BEFORE INSERT ON nomenclature.fishway_type FOR EACH ROW EXECUTE FUNCTION nomenclature.nomenclature_id_insert();


--
-- Name: fishway_type tr_fishway_type_update; Type: TRIGGER; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TRIGGER tr_fishway_type_update BEFORE UPDATE ON nomenclature.fishway_type FOR EACH ROW EXECUTE FUNCTION nomenclature.nomenclature_id_update();


--
-- Name: obstruction_type tr_obstruction_type_insert; Type: TRIGGER; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TRIGGER tr_obstruction_type_insert BEFORE INSERT ON nomenclature.obstruction_type FOR EACH ROW EXECUTE FUNCTION nomenclature.nomenclature_id_insert();


--
-- Name: obstruction_type tr_obstruction_type_update; Type: TRIGGER; Schema: nomenclature; Owner: diaspara_admin
--

CREATE TRIGGER tr_obstruction_type_update BEFORE UPDATE ON nomenclature.obstruction_type FOR EACH ROW EXECUTE FUNCTION nomenclature.nomenclature_id_update();


--
-- Name: stage fk_species; Type: FK CONSTRAINT; Schema: nomenclature; Owner: diaspara_admin
--

ALTER TABLE ONLY nomenclature.stage
    ADD CONSTRAINT fk_species FOREIGN KEY (spe_code) REFERENCES nomenclature.species(no_code) ON UPDATE CASCADE ON DELETE RESTRICT;


--
-- Name: SCHEMA nomenclature; Type: ACL; Schema: -; Owner: diaspara_admin
--

GRANT USAGE ON SCHEMA nomenclature TO diaspara_read;


--
-- Name: FUNCTION nomenclature_id_insert(); Type: ACL; Schema: nomenclature; Owner: postgres
--

GRANT ALL ON FUNCTION nomenclature.nomenclature_id_insert() TO diaspara_read;


--
-- Name: FUNCTION nomenclature_id_update(); Type: ACL; Schema: nomenclature; Owner: postgres
--

GRANT ALL ON FUNCTION nomenclature.nomenclature_id_update() TO diaspara_read;


--
-- Name: TABLE nomenclature; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.nomenclature TO diaspara_read;


--
-- Name: TABLE age; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.age TO diaspara_read;


--
-- Name: TABLE biological_characteristic_type; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.biological_characteristic_type TO diaspara_read;


--
-- Name: TABLE downstream_mitigation_measure; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.downstream_mitigation_measure TO diaspara_read;


--
-- Name: TABLE effort_type; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.effort_type TO diaspara_read;


--
-- Name: TABLE electrofishing_mean; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.electrofishing_mean TO diaspara_read;


--
-- Name: TABLE event_change; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.event_change TO diaspara_read;


--
-- Name: TABLE fishway_type; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.fishway_type TO diaspara_read;


--
-- Name: TABLE gear_type; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.gear_type TO diaspara_read;


--
-- Name: TABLE maturity; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.maturity TO diaspara_read;


--
-- Name: TABLE migration_direction; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.migration_direction TO diaspara_read;


--
-- Name: TABLE obstruction_type; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.obstruction_type TO diaspara_read;


--
-- Name: TABLE orient_flow; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.orient_flow TO diaspara_read;


--
-- Name: TABLE scientific_observation_method; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.scientific_observation_method TO diaspara_read;


--
-- Name: TABLE sex; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.sex TO diaspara_read;


--
-- Name: TABLE species; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.species TO diaspara_read;


--
-- Name: TABLE stage; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.stage TO diaspara_read;


--
-- Name: TABLE turbine_type; Type: ACL; Schema: nomenclature; Owner: diaspara_admin
--

GRANT SELECT ON TABLE nomenclature.turbine_type TO diaspara_read;


--
-- PostgreSQL database dump complete
--

3.3 Annex 3 : SQL code nomenclature data

Data in nomenclature 
--
-- PostgreSQL database dump
--

-- Dumped from database version 14.22 (Ubuntu 14.22-0ubuntu0.22.04.1)
-- Dumped by pg_dump version 17.5

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Data for Name: age; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.age (no_id, no_code, no_type, no_name) FROM stdin;
1  0FW age 0 year in freshwater
2  1FW age 1 year in freshwater
3  2FW age 2 years in freshwater
4  3FW age 3 years in freshwater
5  4FW age 4 years in freshwater
6  5FW age 5 years in freshwater
7  6FW age 6 years in freshwater
8  1SW age 1 year in seawater
9  2SW age 2 years in seawater
10 MSW age Two years or more in seawater
11 1+  age Older than one year in freshwater
\.


--
-- Data for Name: biological_characteristic_type; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.biological_characteristic_type (no_id, no_code, no_type, no_name, bc_label, bc_unit, bc_data_type) FROM stdin;
38 \N  Biological characteristic type  Unknown Unknown Unknown Unknown
44 \N  Biological characteristic type  Sex Sex Dimensionless   nomenclature
45 \N  Biological characteristic type  Stage   stage   Dimensionless   nomenclature
48 \N  Biological characteristic type  Density Density nb/m²   real
41 \N  Biological characteristic type  Upper length    Total length - upper bound  mm  real
40 \N  Biological characteristic type  Lower length    Total length - lower bound  mm  real
47 \N  Biological characteristic type  Number  Number  Dimensionless   Integer
232    \N  Biological characteristic type  Number p2   Number in the second pass   Dimensionless   Integer
231    \N  Biological characteristic type  Number p1   Number in the first pass    Dimensionless   Integer
233    \N  Biological characteristic type  Number p3   Number in the 3rd pass  Dimensionless   Integer
234    \N  Biological characteristic type  Number p4   Number in the 4th pass  Dimensionless   Integer
235    \N  Biological characteristic type  Number p5   Number in the 5th pass  Dimensionless   Integer
236    \N  Biological characteristic type  Number p6   Number in the 6th pass  Dimensionless   Integer
265    \N  Biological characteristic type  Presence_neuromast  Presence of neuromasts along the lateral line   logical boolean
266    \N  Biological characteristic type  Contrast    Contrasted colors between dorsal and ventral    logical boolean
307    \N  Biological characteristic type  Eye_diam_mean_mm    Mean eye diameter   mm  real
42 \N  Biological characteristic type  Weightg Weight in g g   real
43 \N  Biological characteristic type  Ageyear Age in year year    integer
262    \N  Biological characteristic type  Eye_diam_vert_mm    Vertical eye diameter   mm  real
263    \N  Biological characteristic type  Eye_diam_horiz_mm   Horizontal eye diameter mm  real
264    \N  Biological characteristic type  Pectoral_lengthmm   Length of the pectoral fin  mm  real
46 \N  Biological characteristic type  Rate    rate    Dimensionless   real
39 \N  Biological characteristic type  Lengthmm    Total length    mm  real
\.


--
-- Data for Name: downstream_mitigation_measure; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.downstream_mitigation_measure (no_id, no_code, no_type, no_name) FROM stdin;
282    \N  downstream mitigation measure   Water intake
283    \N  downstream mitigation measure   Fish friendly turbine
284    \N  downstream mitigation measure   Fish adapted Management
\.


--
-- Data for Name: effort_type; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.effort_type (no_id, no_code, no_type, no_name) FROM stdin;
99 \N  Effort type Unknown
103    \N  Effort type Volume in cubic meters
101    nrd Effort type Number of day (nr day)
104    nd  Effort type Net-days (nd)
100    gd  Effort type Gear-days (Fyke net, traps)
102    m2  Effort type Area in square meters
\.


--
-- Data for Name: electrofishing_mean; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.electrofishing_mean (no_id, no_code, no_type, no_name, ef_definition) FROM stdin;
70   \N  electrofishing_mean Unknown \N
71   \N  electrofishing_mean By foot \N
72   \N  electrofishing_mean By boat \N
73   \N  electrofishing_mean Mix \N
\.


--
-- Data for Name: event_change; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.event_change (no_id, no_code, no_type, no_name) FROM stdin;
307  ST  Event change    Start (Default)
308  CO  Event change    Construction
309  SU  Event change    Start usage
310  ER  Event change    Erasement
311  EU  Event change    End usage
312  HC  Event change    Height change
313  PE  Event change    Partial erasement
332  FW  Event change    Fishway built or changed
\.


--
-- Data for Name: fishway_type; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.fishway_type (no_id, no_code, no_type, no_name) FROM stdin;
270  VS  Fishway type    Vertical slot fishway
271  PO  Fishway type    Pool type fishway
272  FL  Fishway type    Fish lock
273  D   Fishway type    Denil pass
275  RR  Fishway type    Rock ramp
276  ER  Fishway type    Eel ramp
277  LA  Fishway type    Lateral canal
278  AR  Fishway type    Artificial river
279  ?   Fishway type    Unknown
280  S   Fishway type    Sluice
274  L   Fishway type    Fish lift
\.


--
-- Data for Name: gear_type; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.gear_type (no_id, no_code, no_type, no_name, isscfg_code, main_gear, eel_specific_gear) FROM stdin;
106  \N  Gear type   Unknown 01.0.0  SURROUNDING NETS    \N
107  \N  Gear type   Surrounding Nets with Purse Lines (Purse Seines)    01.1.0  SURROUNDING NETS    \N
108  \N  Gear type   Surrounding Nets with Purse Lines - one boat    01.1.1  SURROUNDING NETS    \N
109  \N  Gear type   Surrounding Nets with Purse Lines - two boats   01.1.2  SURROUNDING NETS    \N
110  \N  Gear type   Surrounding Nets without Purse Lines    01.2.0  SURROUNDING NETS    \N
111  \N  Gear type   Unknown 02.0.0  SEINE NETS  \N
112  \N  Gear type   Beach Seines    02.1.0  SEINE NETS  \N
113  \N  Gear type   Boat Seines 02.2.0  SEINE NETS  \N
114  \N  Gear type   Boat Seines 02.2.1  SEINE NETS  \N
115  \N  Gear type   Boat Seines 02.2.2  SEINE NETS  \N
116  \N  Gear type   Boat Seines 02.2.3  SEINE NETS  \N
117  \N  Gear type   Boat Seines 02.9.0  SEINE NETS  \N
118  \N  Gear type   Unknown 03.0.0  TRAWLS  \N
119  \N  Gear type   Unknown 03.1.0  TRAWLS  \N
120  \N  Gear type   Beam trawls 03.1.1  TRAWL NETS  \N
121  \N  Gear type   Bottom otter trawls 03.1.2  TRAWL NETS  \N
122  \N  Gear type   Bottom pair trawls  03.1.3  TRAWL NETS  \N
123  \N  Gear type   Unknown 03.1.4  TRAWLS  \N
124  \N  Gear type   Unknown 03.1.5  TRAWLS  \N
125  \N  Gear type   Unknown 03.1.9  TRAWLS  \N
126  \N  Gear type   Unknown 03.2.0  TRAWLS  \N
127  \N  Gear type   Midwater otter trawls   03.2.1  TRAWL NETS  \N
128  \N  Gear type   Midwater pair trawls    03.2.2  TRAWL NETS  \N
129  \N  Gear type   Unknown 03.2.3  TRAWLS  \N
130  \N  Gear type   Unknown 03.2.9  TRAWLS  \N
131  \N  Gear type   0tter Twin Trawls   03.3.0  TRAWL NETS  \N
132  \N  Gear type   0tter Twin Trawls   03.4.9  TRAWL NETS  \N
133  \N  Gear type   0tter Twin Trawls   03.5.9  TRAWL NETS  \N
134  \N  Gear type   0tter Twin Trawls   03.9.0  TRAWL NETS  \N
135  \N  Gear type   Boat Dredges    04.0.0  DREDGES \N
136  \N  Gear type   Boat Dredges    04.1.0  DREDGES \N
137  \N  Gear type   Hand Dredges    04.2.0  DREDGES \N
138  \N  Gear type   Portable Lift Nets  05.0.0  LIFT NETS   \N
139  \N  Gear type   Portable Lift Nets  05.1.0  LIFT NETS   \N
140  \N  Gear type   Boat Operated Lift Nets 05.2.0  LIFT NETS   \N
141  \N  Gear type   Shore Operated Lift Nets    05.3.0  LIFT NETS   \N
142  \N  Gear type   Shore Operated Lift Nets    05.9.0  LIFT NETS   \N
143  \N  Gear type   Cast Nets   06.0.0  FALLING GEAR    \N
144  \N  Gear type   Cast Nets   06.1.0  FALLING GEAR    \N
145  \N  Gear type   Falling Gear (Not Specified)    06.9.0  FALLING GEAR    \N
146  \N  Gear type   Set Gillnets (Anchored) 07.0.0  GILLNETS AND ENTANGLING NETS    \N
147  \N  Gear type   Set Gillnets (Anchored) 07.1.0  GILLNETS AND ENTANGLING NETS    \N
148  \N  Gear type   Drifting Gillnets (Driftnets)   07.2.0  GILLNETS AND ENTANGLING NETS    \N
149  \N  Gear type   Encircling gillnets 07.3.0  GILLNETS AND ENTANGLING NETS    \N
150  \N  Gear type   Fixed Gillnets (on Stakes)  07.4.0  GILLNETS AND ENTANGLING NETS    \N
151  \N  Gear type   Trammel Nets    07.5.0  GILLNETS AND ENTANGLING NETS    \N
152  \N  Gear type   Trammel Nets    07.6.0  GILLNETS AND ENTANGLING NETS    \N
153  \N  Gear type   Trammel Nets    07.9.0  GILLNETS AND ENTANGLING NETS    \N
154  \N  Gear type   Trammel Nets    07.9.1  GILLNETS AND ENTANGLING NETS    \N
155  \N  Gear type   Unknown 08.0.0  TRAPS   \N
156  \N  Gear type   Stationary Uncovered Pound Nets 08.1.0  TRAPS   \N
157  \N  Gear type   Pots    08.2.0  TRAPS   Eel pots
158  \N  Gear type   Fyke Nets   08.3.0  TRAPS   Eel fyke nets
159  \N  Gear type   Stow Nets   08.4.0  TRAPS   Dideau
160  \N  Gear type   Barriers, Fences, Weirs, Corrals, etc.  08.5.0  TRAPS   \N
161  \N  Gear type   Aerial Traps    08.6.0  TRAPS   \N
162  \N  Gear type   Aerial Traps    08.9.0  TRAPS   \N
163  \N  Gear type   Handlines and Pole-Lines (Hand Operated)    09.0.0  HOOKS AND LINES \N
164  \N  Gear type   Handlines and Pole-Lines (Hand Operated)    09.1.0  HOOKS AND LINES \N
165  \N  Gear type   Handlines and Pole-Lines (Mechanized)   09.2.0  HOOKS AND LINES \N
166  \N  Gear type   Set Longlines   09.3.0  HOOKS AND LINES \N
167  \N  Gear type   Drifting Longlines  09.4.0  HOOKS AND LINES \N
168  \N  Gear type   Longlines (Not Specified)   09.5.0  HOOKS AND LINES \N
169  \N  Gear type   Trolling Lines  09.6.0  HOOKS AND LINES \N
170  \N  Gear type   Trolling Lines  09.9.0  HOOKS AND LINES \N
171  \N  Gear type   Unknown 10.0.0  GRAPPLING AND WOUNDING  \N
172  \N  Gear type   Harpoons    10.1.0  GRAPPLING AND WOUNDING  \N
173  \N  Gear type   Pumps   11.0.0  HARVESTING GEAR \N
174  \N  Gear type   Pumps   11.1.0  HARVESTING GEAR \N
175  \N  Gear type   Mechanized Dredges  11.2.0  HARVESTING GEAR \N
176  \N  Gear type   Pumps   11.9.0  HARVESTING GEAR \N
177  \N  Gear type   Miscellaneous   20.0.0  MISCELLANEOUS   Glass eel scoop net and push net
178  \N  Gear type   Recreational fishing gear   25.0.0  RECREATIVE FISHING GEAR \N
179  \N  Gear type   Unknown 99.0.0  UNKNOWN \N
\.


--
-- Data for Name: maturity; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.maturity (no_id, no_code, no_type, no_name) FROM stdin;
347  A   maturity    Immature
348  B   maturity    Developing
349  Ba  maturity    Developing, but functionally immature (first-time developer)
350  Bb  maturity    Developing and functionally mature
351  C   maturity    Spawning
352  Ca  maturity    Actively spawning
353  Cb  maturity    Spawning capable
354  D   maturity    Regressing / Regenerating
355  Da  maturity    Regressing
356  Db  maturity    Regenerating
357  E   maturity    Omitted spawning
358  F   maturity    Abnormal
\.


--
-- Data for Name: migration_direction; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.migration_direction (no_id, no_code, no_type, no_name) FROM stdin;
\.


--
-- Data for Name: nomenclature; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.nomenclature (no_id, no_code, no_type, no_name) FROM stdin;
\.


--
-- Data for Name: obstruction_type; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.obstruction_type (no_id, no_code, no_type, no_name) FROM stdin;
218  UN  Obstruction type    Unknown
291  DA  Obstruction_type    Dam
292  WE  Obstruction_type    Weir
293  RR  Obstruction_type    Rock ramp
294  CU  Obstruction_type    Culvert
295  FO  Obstruction_type    Ford
296  BR  Obstruction_type    Bridge
297  OT  Obstruction_type    Other
298  DI  Obstruction_type    Dike
299  GR  Obstruction_type    Grid
300  PP  Obstruction_type    Penstock Pipe
314  WA  Obstruction type    Waterfall, Natural obstruction
\.


--
-- Data for Name: orient_flow; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.orient_flow (no_id, no_code, no_type, no_name) FROM stdin;
238  1   orient_flow [70-90°]
239  2   orient_flow [50-70°[
240  3   orient_flow [30-50°[
241  4   orient_flow <30°
\.


--
-- Data for Name: scientific_observation_method; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.scientific_observation_method (no_id, no_code, no_type, no_name, sc_observation_category, sc_definition) FROM stdin;
68   \N  scientific_observation_method   Unknown Gear fishing    \N
61   UN  scientific_observation_method   Unknown Electro-fishing Electrofishing, method unknown
63   P1  scientific_observation_method   Partial1bank    Electro-fishing Electrofishing, partial on one bank
64   P2  scientific_observation_method   Partial2banks   Electro-fishing Electrofishing, partial on two banks
65   PR  scientific_observation_method   Partialrandom   Electro-fishing Electrofishing, partial random
66   PR  scientific_observation_method   Partialprop Electro-fishing Electrofishing, partial proportional
67   OT  scientific_observation_method   Other   Electro-fishing Electrofishing, other method
301  DH  scientific_observation_method   Deep habitat    Electro-fishing Normalized method for deep habitat (Belliard et al.,2018)
303  PE  scientific_observation_method   Point sampling eel  Electro-fishing Electrofishing, eel specific point sampling (Germis, 2009)
305  BB  scientific_observation_method   Boom boat   Electro-fishing Boom boat (Pulsed)
304  TE  scientific_observation_method   TEF Electro-fishing Timed electrofishing, 10 min in Ireland
306  EP  scientific_observation_method   EPA Electro-fishing Partial point sampling with density current, protocol Feunteun, Rigaud
62   ST  scientific_observation_method   Standard by foot    Electro-fishing Electrofishing by foot, specify the number of pass
302  EE  scientific_observation_method   Standard eel    Electro-fishing Electrofishing by foot, eel specific, specify the number of pass
\.


--
-- Data for Name: sex; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.sex (no_id, no_code, no_type, no_name) FROM stdin;
182  F   sex Female
181  M   sex Male
360  H   sex Hermaphrodite
361  I   sex Immature - attempt made but sex could not be destinguished
362  T   sex Transitional
363  U   sex Undetermined - no attempt made
364  X   sex Mixed
\.


--
-- Data for Name: species; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.species (no_id, no_code, no_type, no_name, sp_vernacular_name) FROM stdin;
333  CIV Species Anguilla_anguilla (glass eel)   European eel (glass eel stage)
315  127186  Species Salmo salar Atlantic salmon
317  126413  Species Alosa alosa Twait shad
318  126415  Species Alosa fallax    Allis shad
319  101174  Species Petromyzon marinus  Sea lamprey
320  101172  Species Lampretra fluviatilis   European river lamprey
30   126281  Species Anguilla anguilla   European eel
316  127187  Species Salmo trutta    Sea trout
\.


--
-- Data for Name: stage; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.stage (no_id, no_code, no_type, no_name, spe_code) FROM stdin;
225  G   Stage   Glass eel   126281
226  Y   Stage   Yellow eel  126281
227  S   Stage   Silver eel  126281
228  GY  Stage   Glass & yellow eel mixed    126281
229  YS  Stage   Yellow & silver eel mixed   126281
230  AL  Stage   G, Y & S eel mixed  126281
426  E   stage   Egg 127187
427  EE  stage   Eyed egg    127187
428  ALV stage   Alevin with the yolk sac    127187
429  FR  stage   Fry 127187
430  P   stage   Parr    127187
431  SM  stage   Smolt   127187
432  PS  stage   Post Smolt  127187
433  A   stage   Adult   127187
434  AL  stage   All stages  127187
435  _   stage   No life stage   127187
416  E   stage   Egg 127186
417  EE  stage   Eyed egg    127186
418  ALV stage   Alevin with the yolk sac    127186
419  FR  stage   Fry 127186
420  P   stage   Parr    127186
421  SM  stage   Smolt   127186
422  PS  stage   Post Smolt  127186
423  A   stage   Adult   127186
424  AL  stage   All stages  127186
425  _   stage   No life stage   127186
\.


--
-- Data for Name: turbine_type; Type: TABLE DATA; Schema: nomenclature; Owner: diaspara_admin
--

COPY nomenclature.turbine_type (no_id, no_code, no_type, no_name) FROM stdin;
242  \N  Turbine type    Horizontal axis Kaplan (bulb)
243  \N  Turbine type    Other (please specify)
244  \N  Turbine type    Double Francis (spiral case)
245  \N  Turbine type    Francis unspecified
246  \N  Turbine type    Turbine with fixed blade propeller and vertical axis
247  \N  Turbine type    Kaplan not specified
248  \N  Turbine type    Pelton
249  \N  Turbine type    Reversible
250  \N  Turbine type    Kaplan (model of S-turbine)
251  \N  Turbine type    Turbine with fixed blade propeller and horizontal axis
252  \N  Turbine type    Unknown
253  \N  Turbine type    Vertical axis Kaplan
254  \N  Turbine type    Francis without volute
255  \N  Turbine type    Francis (spiral case)
256  \N  Turbine type    Banki-Michell (cross-flow)
257  \N  Turbine type    VLH
258  \N  Turbine type    Archimedean screw turbine
259  \N  Turbine type    Water wheel turbine (aqualienne)
260  \N  Turbine type    Water wheel turbine (others)
261  \N  Turbine type    Propeller unspecified
\.


--
-- Name: nomenclature_no_id_seq; Type: SEQUENCE SET; Schema: nomenclature; Owner: diaspara_admin
--

SELECT pg_catalog.setval('nomenclature.nomenclature_no_id_seq', 333, true);


--
-- PostgreSQL database dump complete
--

3.4 Annex 4 : SQL code electrofishing schema

Electrofishing data structure 
--
-- PostgreSQL database dump
--

-- Dumped from database version 14.20 (Ubuntu 14.20-0ubuntu0.22.04.1)
-- Dumped by pg_dump version 17.5

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: electrofishing; Type: SCHEMA; Schema: -; Owner: diaspara_admin
--

CREATE SCHEMA electrofishing;


ALTER SCHEMA electrofishing OWNER TO diaspara_admin;



SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: batch; Type: TABLE; Schema: electrofishing; Owner: diaspara_admin
--

CREATE TABLE electrofishing.batch (
    ba_id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    ba_op_id uuid NOT NULL,
    ba_ba_id uuid,
    ba_id_original text,
    ba_no_species integer,
    ba_no_stage integer,
    ba_no_biol_char integer,
    ba_quantity real,
    ba_batch_level integer
);


ALTER TABLE electrofishing.batch OWNER TO diaspara_admin;

--
-- Name: batch_fish; Type: TABLE; Schema: electrofishing; Owner: diaspara_admin
--

CREATE TABLE electrofishing.batch_fish (
    CONSTRAINT c_ck_ba_batch_level CHECK ((ba_batch_level = 2))
)
INHERITS (electrofishing.batch);


ALTER TABLE electrofishing.batch_fish OWNER TO diaspara_admin;

--
-- Name: batch_ope; Type: TABLE; Schema: electrofishing; Owner: diaspara_admin
--

CREATE TABLE electrofishing.batch_ope (
    ba_pass integer,
    CONSTRAINT c_ck_ba_ba_id CHECK ((ba_ba_id IS NULL)),
    CONSTRAINT c_ck_ba_batch_level CHECK ((ba_batch_level = 1))
)
INHERITS (electrofishing.batch);


ALTER TABLE electrofishing.batch_ope OWNER TO diaspara_admin;

--
-- Name: biological_characteristic; Type: TABLE; Schema: electrofishing; Owner: diaspara_admin
--

CREATE TABLE electrofishing.biological_characteristic (
    bc_id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    bc_ba_id uuid NOT NULL,
    bc_no_biol_char integer NOT NULL,
    bc_numvalue real
);


ALTER TABLE electrofishing.biological_characteristic OWNER TO diaspara_admin;

--
-- Name: data_provider; Type: TABLE; Schema: electrofishing; Owner: diaspara_admin
--

CREATE TABLE electrofishing.data_provider (
    dp_id integer NOT NULL,
    dp_name character varying(60),
    dp_edmo_key integer,
    dp_establishment_name text
);


ALTER TABLE electrofishing.data_provider OWNER TO diaspara_admin;

--
-- Name: data_provider_dp_id_seq; Type: SEQUENCE; Schema: electrofishing; Owner: diaspara_admin
--

CREATE SEQUENCE electrofishing.data_provider_dp_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE electrofishing.data_provider_dp_id_seq OWNER TO diaspara_admin;

--
-- Name: data_provider_dp_id_seq; Type: SEQUENCE OWNED BY; Schema: electrofishing; Owner: diaspara_admin
--

ALTER SEQUENCE electrofishing.data_provider_dp_id_seq OWNED BY electrofishing.data_provider.dp_id;


--
-- Name: gear_fishing; Type: TABLE; Schema: electrofishing; Owner: diaspara_admin
--

CREATE TABLE electrofishing.gear_fishing (
    gf_id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    gf_id_original text,
    gf_sta_id uuid NOT NULL,
    gf_starting_date date NOT NULL,
    gf_ending_date date,
    gf_dp_id integer NOT NULL,
    gf_no_gear_type integer,
    gf_gear_number integer,
    gf_no_effort_type integer,
    gf_effort_value real,
    CONSTRAINT ck_gf_gear_number CHECK ((gf_gear_number > 0)),
    CONSTRAINT ck_gf_gf_effort_value CHECK ((gf_effort_value > (0)::double precision))
);


ALTER TABLE electrofishing.gear_fishing OWNER TO diaspara_admin;

--
-- Name: operation; Type: TABLE; Schema: electrofishing; Owner: diaspara_admin
--

CREATE TABLE electrofishing.operation (
    op_id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    op_id_original text,
    op_sta_id uuid NOT NULL,
    op_starting_date date NOT NULL,
    op_ending_date date,
    op_dp_id integer NOT NULL,
    op_no_method integer,
    op_no_efishing_mean integer,
    op_wetted_area real,
    op_fished_length real,
    op_fished_width real,
    op_duration double precision,
    op_nbpas integer,
    CONSTRAINT ck_op_duration CHECK ((op_duration > (0)::double precision)),
    CONSTRAINT ck_op_fished_length CHECK ((op_fished_length > (0)::double precision)),
    CONSTRAINT ck_op_fished_width CHECK ((op_fished_width > (0)::double precision)),
    CONSTRAINT ck_op_nbpas CHECK ((op_nbpas > 0)),
    CONSTRAINT ck_op_wetted_area CHECK ((op_wetted_area > (0)::double precision))
);


ALTER TABLE electrofishing.operation OWNER TO diaspara_admin;

--
-- Name: station; Type: TABLE; Schema: electrofishing; Owner: diaspara_admin
--

CREATE TABLE electrofishing.station (
    sta_id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    sta_name text,
    sta_sta_id uuid,
    sta_dp_id integer,
    sta_id_original text NOT NULL,
    sta_country character varying(2),
    geom public.geometry
);


ALTER TABLE electrofishing.station OWNER TO diaspara_admin;

--
-- Name: batch_fish ba_id; Type: DEFAULT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch_fish ALTER COLUMN ba_id SET DEFAULT public.uuid_generate_v4();


--
-- Name: batch_ope ba_id; Type: DEFAULT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch_ope ALTER COLUMN ba_id SET DEFAULT public.uuid_generate_v4();


--
-- Name: data_provider dp_id; Type: DEFAULT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.data_provider ALTER COLUMN dp_id SET DEFAULT nextval('electrofishing.data_provider_dp_id_seq'::regclass);



--
-- Name: data_provider_dp_id_seq; Type: SEQUENCE SET; Schema: electrofishing; Owner: diaspara_admin
--

SELECT pg_catalog.setval('electrofishing.data_provider_dp_id_seq', 1, false);


--
-- Name: batch_fish batch_fish_pkey; Type: CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch_fish
    ADD CONSTRAINT batch_fish_pkey PRIMARY KEY (ba_id);


--
-- Name: batch_ope batch_op_pkey; Type: CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch_ope
    ADD CONSTRAINT batch_op_pkey PRIMARY KEY (ba_id);


--
-- Name: batch batch_pkey; Type: CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch
    ADD CONSTRAINT batch_pkey PRIMARY KEY (ba_id);


--
-- Name: biological_characteristic biological_characteristic_pkey; Type: CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.biological_characteristic
    ADD CONSTRAINT biological_characteristic_pkey PRIMARY KEY (bc_id);


--
-- Name: data_provider data_provider_pkey; Type: CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.data_provider
    ADD CONSTRAINT data_provider_pkey PRIMARY KEY (dp_id);


--
-- Name: gear_fishing gear_fishing_pkey; Type: CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.gear_fishing
    ADD CONSTRAINT gear_fishing_pkey PRIMARY KEY (gf_id);


--
-- Name: operation operation_pkey; Type: CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.operation
    ADD CONSTRAINT operation_pkey PRIMARY KEY (op_id);


--
-- Name: station station_pkey; Type: CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.station
    ADD CONSTRAINT station_pkey PRIMARY KEY (sta_id);


--
-- Name: station uk_sta_id_original; Type: CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.station
    ADD CONSTRAINT uk_sta_id_original UNIQUE (sta_id_original);


--
-- Name: dbmig_batch_ix_ba_id; Type: INDEX; Schema: electrofishing; Owner: diaspara_admin
--

CREATE INDEX dbmig_batch_ix_ba_id ON electrofishing.batch USING btree (ba_id);


--
-- Name: dbmig_batch_ix_ba_ob_id; Type: INDEX; Schema: electrofishing; Owner: diaspara_admin
--

CREATE INDEX dbmig_batch_ix_ba_ob_id ON electrofishing.batch USING btree (ba_op_id);


--
-- Name: dbmig_biological_characteristic_ix_bc_ba_id; Type: INDEX; Schema: electrofishing; Owner: diaspara_admin
--

CREATE INDEX dbmig_biological_characteristic_ix_bc_ba_id ON electrofishing.biological_characteristic USING btree (bc_ba_id);


--
-- Name: electrofishing_station_ix_sta_id; Type: INDEX; Schema: electrofishing; Owner: diaspara_admin
--

CREATE INDEX electrofishing_station_ix_sta_id ON electrofishing.station USING btree (sta_id);


--
-- Name: index_geom; Type: INDEX; Schema: electrofishing; Owner: diaspara_admin
--

CREATE INDEX index_geom ON electrofishing.station USING gist (geom);


--
-- Name: batch fk_ba_ba_id; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch
    ADD CONSTRAINT fk_ba_ba_id FOREIGN KEY (ba_ba_id) REFERENCES electrofishing.batch(ba_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: batch_fish fk_ba_ba_id; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch_fish
    ADD CONSTRAINT fk_ba_ba_id FOREIGN KEY (ba_ba_id) REFERENCES electrofishing.batch_ope(ba_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: batch fk_ba_no_biol_char; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch
    ADD CONSTRAINT fk_ba_no_biol_char FOREIGN KEY (ba_no_biol_char) REFERENCES nomenclature.biological_characteristic_type(no_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: batch_ope fk_ba_no_biol_char; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch_ope
    ADD CONSTRAINT fk_ba_no_biol_char FOREIGN KEY (ba_no_biol_char) REFERENCES nomenclature.biological_characteristic_type(no_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: batch_fish fk_ba_no_biol_char; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch_fish
    ADD CONSTRAINT fk_ba_no_biol_char FOREIGN KEY (ba_no_biol_char) REFERENCES nomenclature.biological_characteristic_type(no_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: batch fk_ba_no_species; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch
    ADD CONSTRAINT fk_ba_no_species FOREIGN KEY (ba_no_species) REFERENCES nomenclature.species(no_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: batch_ope fk_ba_no_species; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch_ope
    ADD CONSTRAINT fk_ba_no_species FOREIGN KEY (ba_no_species) REFERENCES nomenclature.species(no_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: batch_fish fk_ba_no_species; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch_fish
    ADD CONSTRAINT fk_ba_no_species FOREIGN KEY (ba_no_species) REFERENCES nomenclature.species(no_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: batch fk_ba_no_stage; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch
    ADD CONSTRAINT fk_ba_no_stage FOREIGN KEY (ba_no_stage) REFERENCES nomenclature.stage(no_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: batch_ope fk_ba_no_stage; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch_ope
    ADD CONSTRAINT fk_ba_no_stage FOREIGN KEY (ba_no_stage) REFERENCES nomenclature.stage(no_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: batch_fish fk_ba_no_stage; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch_fish
    ADD CONSTRAINT fk_ba_no_stage FOREIGN KEY (ba_no_stage) REFERENCES nomenclature.stage(no_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: batch fk_ba_op_id; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch
    ADD CONSTRAINT fk_ba_op_id FOREIGN KEY (ba_op_id) REFERENCES electrofishing.operation(op_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: batch_ope fk_ba_op_id; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch_ope
    ADD CONSTRAINT fk_ba_op_id FOREIGN KEY (ba_op_id) REFERENCES electrofishing.operation(op_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: batch_fish fk_ba_op_id; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.batch_fish
    ADD CONSTRAINT fk_ba_op_id FOREIGN KEY (ba_op_id) REFERENCES electrofishing.operation(op_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: biological_characteristic fk_bc_no_biol_char; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.biological_characteristic
    ADD CONSTRAINT fk_bc_no_biol_char FOREIGN KEY (bc_no_biol_char) REFERENCES nomenclature.biological_characteristic_type(no_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: biological_characteristic fk_bd_ba_id; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.biological_characteristic
    ADD CONSTRAINT fk_bd_ba_id FOREIGN KEY (bc_ba_id) REFERENCES electrofishing.batch_fish(ba_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: data_provider fk_dp_edmo_key; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.data_provider
    ADD CONSTRAINT fk_dp_edmo_key FOREIGN KEY (dp_edmo_key) REFERENCES ref."EDMO"("Key") ON UPDATE CASCADE ON DELETE RESTRICT;


--
-- Name: gear_fishing fk_gf_dp_id; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.gear_fishing
    ADD CONSTRAINT fk_gf_dp_id FOREIGN KEY (gf_dp_id) REFERENCES electrofishing.data_provider(dp_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: gear_fishing fk_gf_no_effort_type; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.gear_fishing
    ADD CONSTRAINT fk_gf_no_effort_type FOREIGN KEY (gf_no_effort_type) REFERENCES nomenclature.effort_type(no_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: gear_fishing fk_gf_no_gear_type; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.gear_fishing
    ADD CONSTRAINT fk_gf_no_gear_type FOREIGN KEY (gf_no_gear_type) REFERENCES nomenclature.gear_type(no_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: gear_fishing fk_gf_sta_id; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.gear_fishing
    ADD CONSTRAINT fk_gf_sta_id FOREIGN KEY (gf_sta_id) REFERENCES electrofishing.station(sta_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: operation fk_op_dp_id; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.operation
    ADD CONSTRAINT fk_op_dp_id FOREIGN KEY (op_dp_id) REFERENCES electrofishing.data_provider(dp_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: operation fk_op_method; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.operation
    ADD CONSTRAINT fk_op_method FOREIGN KEY (op_no_method) REFERENCES nomenclature.scientific_observation_method(no_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: operation fk_op_no_efishing_mean; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.operation
    ADD CONSTRAINT fk_op_no_efishing_mean FOREIGN KEY (op_no_efishing_mean) REFERENCES nomenclature.electrofishing_mean(no_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: operation fk_op_sta_id; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.operation
    ADD CONSTRAINT fk_op_sta_id FOREIGN KEY (op_sta_id) REFERENCES electrofishing.station(sta_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: station fk_sta_dp_id; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.station
    ADD CONSTRAINT fk_sta_dp_id FOREIGN KEY (sta_dp_id) REFERENCES electrofishing.data_provider(dp_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: station fk_sta_sta_id; Type: FK CONSTRAINT; Schema: electrofishing; Owner: diaspara_admin
--

ALTER TABLE ONLY electrofishing.station
    ADD CONSTRAINT fk_sta_sta_id FOREIGN KEY (sta_sta_id) REFERENCES electrofishing.station(sta_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: SCHEMA electrofishing; Type: ACL; Schema: -; Owner: diaspara_admin
--

GRANT USAGE ON SCHEMA electrofishing TO diaspara_read;


--
-- Name: FUNCTION fk_id_batch_insert(); Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT ALL ON FUNCTION electrofishing.fk_id_batch_insert() TO diaspara_read;


--
-- Name: FUNCTION fk_id_environmental_characteristic_insert(); Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT ALL ON FUNCTION electrofishing.fk_id_environmental_characteristic_insert() TO diaspara_read;


--
-- Name: FUNCTION fk_id_observations_insert(); Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT ALL ON FUNCTION electrofishing.fk_id_observations_insert() TO diaspara_read;


--
-- Name: FUNCTION integrity_observations_verify(); Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT ALL ON FUNCTION electrofishing.integrity_observations_verify() TO diaspara_read;


--
-- Name: FUNCTION observation_id_place_insert(); Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT ALL ON FUNCTION electrofishing.observation_id_place_insert() TO diaspara_read;


--
-- Name: FUNCTION observation_id_place_update(); Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT ALL ON FUNCTION electrofishing.observation_id_place_update() TO diaspara_read;


--
-- Name: FUNCTION observations_id_insert(); Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT ALL ON FUNCTION electrofishing.observations_id_insert() TO diaspara_read;


--
-- Name: FUNCTION observations_id_update(); Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT ALL ON FUNCTION electrofishing.observations_id_update() TO diaspara_read;


--
-- Name: TABLE batch; Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT SELECT ON TABLE electrofishing.batch TO diaspara_read;


--
-- Name: TABLE batch_fish; Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT SELECT ON TABLE electrofishing.batch_fish TO diaspara_read;


--
-- Name: TABLE batch_ope; Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT SELECT ON TABLE electrofishing.batch_ope TO diaspara_read;


--
-- Name: TABLE biological_characteristic; Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT SELECT ON TABLE electrofishing.biological_characteristic TO diaspara_read;


--
-- Name: TABLE data_provider; Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT SELECT ON TABLE electrofishing.data_provider TO diaspara_read;


--
-- Name: TABLE gear_fishing; Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT SELECT ON TABLE electrofishing.gear_fishing TO diaspara_read;


--
-- Name: TABLE operation; Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT SELECT ON TABLE electrofishing.operation TO diaspara_read;


--
-- Name: TABLE station; Type: ACL; Schema: electrofishing; Owner: diaspara_admin
--

GRANT SELECT ON TABLE electrofishing.station TO diaspara_read;


--
-- PostgreSQL database dump complete
--

3.5 Annex 5 : Database ER diagram

An entity relationship diagram for the dam database
Figure 3: Entity Relationship database dam

3.6 Annex 6 : Nomenclature content

The nomenclature is derived from the diadromous database.

3.6.1 Scientific Observation (electrofishing types)

The scientific observation table was created within the dbeel (Walker et al. 2011). The list of fishing types was further adapted during SUDOANG and from exchanges with other users when building EDA model (e.g. Ireland). The database is currently fit and tested for eel and, pending final tests for salmon and trout, other species can be added as well.

Notenote for WGDIAD

At the international level, under the premices of WGDIAD, a review of the different methods used for electrofishing and their metadata would help building a more consistent vocabulary.

no_id

no_code

no_type

no_name

sc_observation_category

sc_definition

68

scientific_observation_method

Unknown

Gear fishing

61

UN

scientific_observation_method

Unknown

Electro-fishing

Electrofishing, method unknown

63

P1

scientific_observation_method

Partial1bank

Electro-fishing

Electrofishing, partial on one bank

64

P2

scientific_observation_method

Partial2banks

Electro-fishing

Electrofishing, partial on two banks

65

PR

scientific_observation_method

Partialrandom

Electro-fishing

Electrofishing, partial random

66

PR

scientific_observation_method

Partialprop

Electro-fishing

Electrofishing, partial proportional

67

OT

scientific_observation_method

Other

Electro-fishing

Electrofishing, other method

301

DH

scientific_observation_method

Deep habitat

Electro-fishing

Normalized method for deep habitat (Belliard et al.,2018)

303

PE

scientific_observation_method

Point sampling eel

Electro-fishing

Electrofishing, eel specific point sampling (Germis, 2009)

305

BB

scientific_observation_method

Boom boat

Electro-fishing

Boom boat (Pulsed)

304

TE

scientific_observation_method

TEF

Electro-fishing

Timed electrofishing, 10 min in Ireland

306

EP

scientific_observation_method

EPA

Electro-fishing

Partial point sampling with density current, protocol Feunteun, Rigaud

62

ST

scientific_observation_method

Standard by foot

Electro-fishing

Electrofishing by foot, specify the number of pass

302

EE

scientific_observation_method

Standard eel

Electro-fishing

Electrofishing by foot, eel specific, specify the number of pass

3.6.2 Species

ICES uses AphiaID as the reference, and this code was chosen for the DIADROMOUS database Species table in DIADROMOUS

no_id

no_code

no_type

no_name

sp_vernacular_name

315

127186

Species

Salmo salar

Atlantic salmon

317

126413

Species

Alosa alosa

Twait shad

318

126415

Species

Alosa fallax

Allis shad

319

101174

Species

Petromyzon marinus

Sea lamprey

320

101172

Species

Lampretra fluviatilis

European river lamprey

30

126281

Species

Anguilla anguilla

European eel

316

127187

Species

Salmo trutta

Sea trout

3.6.3 Stage

Stage are species specific. For more details see Life stages in the diadromous DB

no_id

no_code

no_type

no_name

spe_code

225

G

Stage

Glass eel

126281

226

Y

Stage

Yellow eel

126281

227

S

Stage

Silver eel

126281

228

GY

Stage

Glass & yellow eel mixed

126281

229

YS

Stage

Yellow & silver eel mixed

126281

230

AL

Stage

G, Y & S eel mixed

126281

426

E

stage

Egg

127187

427

EE

stage

Eyed egg

127187

428

ALV

stage

Alevin with the yolk sac

127187

429

FR

stage

Fry

127187

430

P

stage

Parr

127187

431

SM

stage

Smolt

127187

432

PS

stage

Post Smolt

127187

433

A

stage

Adult

127187

434

AL

stage

All stages

127187

435

_

stage

No life stage

127187

416

E

stage

Egg

127186

417

EE

stage

Eyed egg

127186

418

ALV

stage

Alevin with the yolk sac

127186

419

FR

stage

Fry

127186

420

P

stage

Parr

127186

421

SM

stage

Smolt

127186

422

PS

stage

Post Smolt

127186

423

A

stage

Adult

127186

424

AL

stage

All stages

127186

425

_

stage

No life stage

127186

3.6.4 Age

Ages can be entered either as a numeric value or a class. This table might need some adaptation during future integration of national Salmon and Trout data. Age repartition can also be inferred from size using country specific size limits for age. For age see Ages in the diadromous DB

no_id

no_code

no_type

no_name

1

0FW

age

0 year in freshwater

2

1FW

age

1 year in freshwater

3

2FW

age

2 years in freshwater

4

3FW

age

3 years in freshwater

5

4FW

age

4 years in freshwater

6

5FW

age

5 years in freshwater

7

6FW

age

6 years in freshwater

8

1SW

age

1 year in seawater

9

2SW

age

2 years in seawater

10

MSW

age

Two years or more in seawater

11

1+

age

Older than one year in freshwater

3.6.5 Marking (wild, released)

Some electrofishing dataset record the source of the fish, hatchery or wild or unknown, most often based on the presence of an adipose fin clip.

4 References

Baldwin, Liz, and Miran Aprahamian. 2012. “An Evaluation of Electric Fishing for Assessment of Resident Eel in Rivers.” Fisheries Research 123: 4–8. http://www.sciencedirect.com/science/article/pii/S0165783611003407.
Bohlin, Torgny, Stellan Hamrin, Tor G. Heggberget, Gorm Rasmussen, and Svein Jakob Saltveit. 1989. “Electrofishing — Theory and Practice with Special Emphasis on Salmonids.” Hydrobiologia 173 (1): 9–43. https://doi.org/10.1007/BF00008596.
Briand, Cédric, Maria Mateo, Hilaire Drouineau, Korta Maria, Estibaliz Diaz, and Laurent Beaulaton. 2022. “Eel Density Analysis (EDA 2.3) Escapement of Silver Eels (Anguilla Anguilla) from French, Spanish and Portuguese Rivers.”
Cowx, I. G., and P. Lamarque, eds. 1990. Fishing with Electricity, Applications in Freshwater Fisheries Management. Fishing News Books. Oxford: Blackwell.
Germis, Gaelle. 2009. “Evaluation de l’état de La Population d’anguille En Bretagne Par La Méthode Des Indices d’abondance ‘Anguille’ de 2003 à 2008.” Ouest Grand Migrateur.
ICES, and WGTRUTTA. 2024. “Working Group to Develop and Test Assessment Methods for Sea Trout Populations (Anadromous Salmo Trutta) (Outputs from 2020-2023 Meetings) (WGTRUTTA).” ICES Scientific Reports. https://doi.org/10.17895/ICES.PUB.27169515.
Lamarque, P. 1976. “Types de Courant Électrique à Utiliser Pour La Capture Optimale Des Poissons Principalement Les Anguilles.” La Pisciculture Française 47: 30–37.
Lambert, P., E. Feunteun, and C. Rigaud. 1994. “Etude de l’anguille En Marais d’eau Douce. Première Analyse Des Probabilités de Capture Observées Lors Des Inventaires Par Éléctrique.” Bulletin Français de Pêche Et de Pisciculture 335: 111–22.
Millar, Colin P., Robert J. Fryer, Karen J. Millidine, and Iain A. Malcolm. 2016. “Modelling Capture Probability of Atlantic Salmon (Salmo Salar) from a Diverse National Electrofishing Dataset: Implications for the Estimation of Abundance.” Fisheries Research 177 (May): 1–12. https://doi.org/10.1016/j.fishres.2016.01.001.
Pottier, Gaétan, Marie Nevoux, and Frédéric Marchand. 2020. “Electrofishing Eel, Salmon and Trout: Impact of Waveform and Frequency on Capture-Per-Unit-Effort and Spinal Damage.” Knowledge & Management of Aquatic Ecosystems, no. 421: 42. https://doi.org/10.1051/kmae/2020034.
“Report of the Workshop on the Development of a Spatial Database and Model for Eels (WKSMEEL).” 2024. https://doi.org/10.17895/ICES.PUB.24648135.
Tomanova, S., P. A. Tedesco, N. Roset, R. Berrebi dit Thomas, and J. Belliard. 2013. “Systematic Point Sampling of Fish Communities in Medium-and Large-Sized Rivers: Sampling Procedure and Effort.” Fisheries Management and Ecology 20 (6): 533–43.
Walker, A. M., Eider Andonegi, P. Apolostolaki, M. Aprahamian, L. Beaulaton, D. Bevacqua, Cédric Briand, et al. 2011. “Pilot Projects to Estimate Potential and Actual Escapement of Silver Eel.”
 

EU is not reponsible for the content of the project