AcousMed project Task 3.4 Acoustic Survey Database Iraklion, 16/12/2011

1. AcousMed project Task 3.4 Acoustic Survey Database Iraklion, 16/12/2011 Andrea De Felice, Roberto Gramolini, Claudio Vasapollo, Iole Leonori

2. Standarization of a common format for acoustic data (Sub Task 3.4) • The aim of this task is the proposal of fields and algorithms that a common database for acoustic surveys should have in order to fulfill DCF requirements and standardize the output of surveys estimations. The standardization of acoustic methodology also involves the need for a common protocol for the format of acoustic data in order to facilitate the exchange of information between the different parties. • For this purpose within the framework of the current project, a common protocol on the format of acoustic data from all areas will be decided. This will assure the comparable presentation of acoustic data coming from different areas and surveys for joint analysis, facilitate the submission of acoustic data following the DCF requirements as well as provide the necessary estimations for stock assessment purposes (i.e. total abundance indices, abundance at age indices). • Summarizing: • Protocol for a common database • Suggestions to be incorporated into the MEDIAS protocol

3. ISMAR-CNR: DEVELOPING A GEODATABASE FOR ACOUSTIC SURVEY • AIMS • Ensure an easy way to load and retrieve data (developing an user friendly interface) • Make the database flexible in order to store different parameters collected in the past and that could be collected in the future • Maintain data accuracy and completeness (developing a well structured database tables and relationship) • Avoid data input errors (using tables with codes) • Develop GIS tools to produce maps that represent raw data and results (connecting the database to a GIS software) • Developing data import/export procedures for sharing (through the right queries)

4. Entity Relationships Diagram Acoustic data CTD in situ Satellite Biological sampling

5. ISMAR-CNR: GIS TOOL ROLE ACOUSTIC DATA SATELLITE CTD IN SITU BIOLOGICAL SAMPLING ENVIRONMENTAL DATA GIS

6. ISMAR-CNR: Acoustic data stored in the database:

7. ISMAR-CNR: CTD data stored in the database: temperature, salinity, fluorescence, oxygen, density profiles.

8. ISMAR-CNR: Satellite data stored in the database: SST data and Chlorophyll-a

9. ISMAR-CNR: Biological sampling data stored in the database (data were loaded but a specific Graphic User Interface will be soon developed): Hauls Catches

10. HCMR • Type of Raw data: • Acoustic data: NASC values per EDSU per species, Total NASC values per EDSU, Geographic coordinates of EDSU, Echosounder Frequency, Bottom Depth. • 2) Haul data: including general information of hauls, biological measurements data and net position. • 3) Environmental data: CTD data, geographic coordinates of sampling stations, vertical profile of temperature, salinity, fluorescence, photosynthetic active radiation, density • File formats to be stored: *.txt • Time frame of the data included in the database: • 1) Acoustic data period: 1995-1996, 1999-2001, 2003-2006, 2008; GSA: 22; 1999-2001:GSA 20 • 2) Haul data period: 1995-1996, 1999-2001, 2003-2006, 2008; GSA: 22; 1999-2001:GSA 20 • 3) Environmental data period: 1995-1996, 1999-2001, 2003-2006, 2008; GSA: 22; 1999-2001:GSA 20 • Structural software(s) for database:

11. Acoustic data NASC values per species, EDSU, transect and area Position of transect, EDSU, NASC values per species

12. Basic flow chart of the database fields Environmental database Acoustic database

13. Haul data – Biological sampling

14. IFREMER Basic flow chart of the database fields

15. IEO • General description of IEO database at present state • Type of Raw data: • Acoustic data: NASC values per EDSU per species, Total NASC values per EDSU, Geographic coordinates of EDSU, Echosounder Frequency and Bottom Depth. • 2) Haul data: including general information of hauls, biological measurements data and net position. • 3) Environmental data: CTD data, geographic coordinates of sampling stations, vertical profile of temperature and salinity. • File formats to be stored: *.xls • Time frame of the data included in the database: • 1) Acoustic data period: 2003-2010; GSA: 06; 2003-2005:GSA 01 • 2) Haul data period: 2003-2010; GSA: 06; 2003-2005:GSA 01 • 3) Environmental data period: 2008-2010: GSA 06 and 01. • Structural software(s) for database:

16. Acoustic data Position of transect, EDSU, NASC values per species NASC values per species, EDSU and transect.

17. IAMC-CNR The IAMC-CNR of Capo Granitola has a long time series of data regarding the acoustic evaluation of small pelagic resources. During acoustic surveys three main categories of data are collected: Oceanography and Atmospheric main parameters Underwater acoustic Fish biology measurements and trawl positioning information data Type of Raw data: Acoustic data: Vessel geographic position, date, time, species density, Total NASC and NASC per species Biological data: Length weight, sex, maturity and age Trawl positioning: geographical positioning, date, time, opening distance from the bottom and from the vessel Environmental data: temperature, conductivity, florescence, pressure, oxygen, etc. Time frame of the data included in the database: Summer survey (1998, 2000, 2002, 2003, 2005, 2006, 2007,2008,2009,2010) Autumnal survey (1999, 2000, 2001, 2004, 2005) The investigated areas are: Strait of Sicily Italian waters Strait of Sicily Maltese waters Tyrrhenian sea (2009) Strait of Sicily Libyan waters (2008 and 2010) The GIS database was improved by means of the dbms “Postgres” which was integrated with the geographical information by means of the PostGIS software. The database structure is made of several tables with different datasets, joined together by means of some data field as the name of the survey or the trawl id. The tables and links are illustrated in the following figure.

18. Acoustic data Acoustic transects done during the survey: i.e NASC for species

19. Basic flow chart of the database fields

20. The system has as primary aim to allow the access to archived data and their geographical representation. The access would be also via Internet. The several clients are operative under Windows system only if the open source software “QuantumGIS is installed. Transect data and Geographic positioning representation tools.

21. Software outputs Data logging at sea Data processing Outputs Simrad ER60 Myriax Echoview • NASC fish per ESDU • NASC per species per EDSU (only available when you can identify regions composed by one species without mixed species regions) • Total biomass and biomass per species per EDSU • Total number and number per species per EDSU In the case of Myriax Echoview it would be convenient to enter in the database data categories 1 or 3 & 4. In case NASC fish is the software output the database should have queries to convert the data into biomass and number of fish and give results per area; in case biomass and numbers are the output queries to obtain the results by area are needed Output from MOVIES: same as Echoview

22. Possible structure of a common database

27. Some ideas for database routines Sub-area creation: the ideal situation would be to select data on transects (or parts of them) and the relative hauls that are contained in a sub-area respecting some criteria (depth, etc.), possibly directly on GIS software linked to our database Calculation of NASC average value and standard error in a sub-area Merge haul information in a sub-area: calculation of mean size by species and of the percentage in weight and in number of the species Biomass estimation per species in a sub-area: using average NASC fish value and information from hauls or through direct conversion of NASC per species if available

28. Data quality • Data quality in a database generallypresentstwo control levels. Data validationisgivenevenbeforethat data wereinsertedinto the database, as to sayat database planning level. • «Impossible» data are forbidden in the database by the creation of specifictablefieldsprovided with valueconfirmations. • Indexes in the tablesblock data duplicates input. • Relations set amongtablesprevent from incomplete information input. • During data input qualityisguaranteedthrough: • Defining a series of tables with codes relative to parametersarchivied in the tables • Use of specific data checkroutineswhileimporting data throughfiles or manually • Theseroutinescouldalso be used on data alreadypresent in the database, in factthereis the possibility to improvetheseroutinesevenwhen some data havealreadybeenloaded in the database.

29. Parameter coding Coding of the researchvesselsused in time for surveys Coding of geographicalareascovered with surveys Coding of echosoundersusedduringsurveys Coding of frequenciesused Coding of strata to subdivide the water column (ifused) Coding of aggregationpatterns of fish Coding of schoolshapes Coding of geometricalmeasures of schools Coding of species Coding of parametersmeasuredthrough CTD …

30. Data input and relative checks Automatic creation of nautical mile progressive number Check on echosounder type and frequencies in use Check on minimum and maximum depth of used strata Check on minimum and maximum size of single individuals per species Check on minimum and maximum weight of an individual per species …

31. Do we intend to propose a specific project to realize a common database? If yes, in which framework? What could be the costs for this?