This database contains: rainfall, humidity, temperature, global solar radiation, wind velocity and wind direction ten-minute data from 150 stations of the Meteogalicia network between 1-jan-2000 and 31-dec-2018.

Version installed: postgresql 9.1

Extension installed: postgis 1.5.3-1

Instructions to restore the database:

1. Create template:

   createdb -E UTF8 -O postgres -U postgres template_postgis

2. Activate PL/pgSQL language:

   createlang plpgsql -d template_postgis -U postgres

3. Load definitions of PostGIS:

   psql -d template_postgis -U postgres -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql

   psql -d template_postgis -U postgres -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql

   psql -d template_postgis -U postgres -f /usr/share/postgresql/9.1/contrib/postgis_comments.sql

4. Create database with "MeteoGalicia" name with PostGIS extension:

   createdb -U postgres -T template_postgis MeteoGalicia

5. Restore backup:

   cat Meteogalicia* | psql MeteoGalicia

This example demonstrates how to use PostGIS capabilities in CyberGIS-Jupyter notebook environment. Modified from notebook by Weiye Chen (weiyec2@illinois.edu)

PostGIS is an extension to the PostgreSQL object-relational database system which allows GIS (Geographic Information Systems) objects to be stored in the database. PostGIS includes support for GiST-based R-Tree spatial indices, and functions for analysis and processing of GIS objects.

Resources for PostGIS:

Manual https://postgis.net/docs/ In this demo, we use PostGIS 3.0. Note that significant changes in APIs have been made to PostGIS compared to version 2.x. This demo assumes that you have basic knowledge of SQL.

1. MISSION ATLANTIC

The MISSION ATLANTIC project is an EU-funded initiative that focuses on understanding the impacts of climate change and human activities on these ecosystems. The project aims to map and assess the current and future status of Atlantic marine ecosystems, develop tools for sustainable management, and support ecosystem-based governance to ensure the resilience and sustainable use of ocean resources. The project brings together experts from 33 partner organizations across 14 countries, including Europe, Africa, North, and South America.

MISSION ATLANTIC includes ten work packages. The present published dataset is included in WP3, which focuses on mapping the pelagic ecosystems, resources, and pressures in the Atlantic Ocean. This WP aims to collect extensive spatial and temporal data to create 3D maps of the water column, identify key vertical ecosystem domains, and assess the pressures from climate change and human activities. More specifically, the dataset corresponds to the fishing intensity presented in the Deliverable 3.2, which integrates data from various sources to map the distribution and dynamics of present ecosystem pressures over time, providing crucial insights for sustainable management strategies.

2. Data description

2.1. Data Source

Fishing intensity estimates from the Global Fishing Watch initiative (GFW) (Kroodsma et al. 2018), who applies machine learning algorithms to data from Automatic Identification Systems (AIS), Vessel Monitoring Systems (VMS), and vessel registries, has been used for the year 2020. This machine learning approach has been able to distinguish between fishing and routing activity of individual vessels, while using pattern recognition to differentiate seven main fishing gear types at the Atlantic Ocean scale (Taconet et al., 2019). The seven main fishing vessel types considered are: trawlers, purse seiners, drifting longliners, set gillnets, squid jiggers, pots and traps, and other. In this work we have aggregated these into pelagic, seabed and passive fishing activities to align with our grouping of ecosystem components.

The GFW data has some limitations:

1. AIS is only required for large vessels. The International Maritime Organization requires AIS use for all vessels of 300 gross tonnage and upward, although some jurisdictions mandate its use in smaller vessels. For example, within the European Union it is required for fishing vessels at least 15m in length. This means that in some areas the fishing intensity estimates will not include the activity of small vessels operating near shore.
2. AIS can be intentionally turned off, for example, when vessels carry out illegal fishing activities (Kurekin et al. 2019).
3. In the GFW dataset, vessels classified as trawlers include both pelagic and bottom trawlers. As trawlers are included in the bottom fishing category, it is highly likely that the data overestimates the effort on the seafloor and underestimates it on the water column.

2.2. Data Processing

1. Data download from the GFW portal.

2. Using R:

• Add daily files and aggregate fishing hours by fishing gear and coordinates:

library(data.table)
## Load data
fileIdx = list.files(".../fleet-daily-csvs-100-v2-2020/", full.names = T)

## Loop
colsIdx = c("geartype", "hours", "fishing_hours", "x", "y")

lapply(fileIdx, function(xx) {
 out = data.table (x = NA_real_, y = NA_real_, geartype = NA_character_)
 tmp = fread(xx)
 tmp[, ":=" (y = floor(cell_ll_lat * 10L) / 10L,
       x = floor(cell_ll_lon * 10L) / 10L)]
 tmp = tmp[, ..colsIdx]
 h = tmp[, c(.N, lapply(.SD, sum, na.rm = T)), by = .(x, y, geartype)]
 outh = data.table::merge.data.table(out, h, by = c("x", "y", "geartype"), all=TRUE)
 fwrite(outh, ".../GFW_2020_0.1_degrees_and_gear_all.csv", nThread = 14, append = T)
})

• Group fishing gears into main fishing groups:

library(dplyr)
library(tidyr)
## Load data
fishing <- read.csv(".../GFW_2020_0.1_degrees_and_gear_all.csv", sep=",", dec=".", header=T, stringsAsFactors = FALSE)

## Grouping fishing gears (fishing, pelagic, bottom, passive)
# unique(fishing$geartype)
fishing$group <- NA
fishing$group[which(fishing$geartype == "fishing")] = "fishing" # Unknown

fishing$group[fishing$geartype %in% c("trollers", "squid_jigger", "pole_and_line", "purse_seines", 
                   "tuna_purse_seines", "seiners", "other_purse_seines", "other_seines",
                   "set_longlines", "drifting_longlines")] <- "pelagic"

fishing$group[fishing$geartype %in% c("trawlers", "dredge_fishing")] <- "bottom"

fishing$group[fishing$geartype %in% c("set_gillnets", "fixed_gear", "pots_and_traps")] <- "passive"

## Total fishing hours (by fishing and position)
fish_gr <- fishing %>% group_by(x, y, group) %>% summarise(gfishing_hours = sum(fishing_hours))

• Pivot table in order to have fishing groups in columns. Each row corresponds to the coordinates of the left corner of the grid cell (0.1 decimal degrees):

## Pivoting table (fishing groups in columns)
fish_gr3 <- fish_gr %>% pivot_wider(names_from = "group", values_from = "gfishing_hours", values_fill = 0)

## Saving data (to import in PostgreSQL)
write.csv(fish_gr3, ".../fishing.csv"), row.names = FALSE)

• Export the table in our PostGIS spatial database using QGis

3. Using PostgreSQL:

• Create grid cell identifiers (gid):

-- Generating a gid
ALTER TABLE public.fishing ADD COLUMN gid uuid PRIMARY KEY DEFAULT uuid_generate_v4();

• Estimate the centroid of each grid cell:

-- Create columns
ALTER TABLE public.fishing ADD COLUMN cen_lat float;
ALTER TABLE public.fishing ADD COLUMN cen_lon float;

-- Calculate the grid centroid
UPDATE public.fishing SET cen_lat = y + 0.05;
UPDATE public.fishing SET cen_lon = x + 0.05;

• Create the geometry column based on the estimated centroids to provide the spatial component:

-- (if necessary) SELECT AddGeometryColumn ('public','fishing','geom', 4326,'POINT',2); 
UPDATE public.fishing SET geom = ST_SetSRID(ST_MakePoint(cen_lon, cen_lat), 4326);
ALTER TABLE public.fishing RENAME COLUMN geom TO geom_point;

• Expand a bounding box in all directions from the centroid geometry to estimate the grid cell (from point to polygon):

-- Expand a bounding box in all directions from the centroid geometry
SELECT AddGeometryColumn ('public','fishing','geom', 4326,'POLYGON', 2); 
UPDATE public.fishing SET geom = St_Expand(geom_point, 0.05);

-- Drop deprecated columns
ALTER TABLE public.fishing DROP COLUMN geom_point;
ALTER TABLE public.fishing DROP COLUMN cen_lat;
ALTER TABLE public.fishing DROP COLUMN cen_lon;

-- Create a spatial index
CREATE INDEX ON public.fishing USING gist (geom);

• Estimate the fishing hours per square kilometre by fishing group in each grid cell:

-- Create columns to estimate fishing hours per km2
ALTER TABLE public.fishing ADD COLUMN pelagic_km numeric,
              ADD COLUMN bottom_km numeric,
              ADD COLUMN fishing_km numeric,
              ADD COLUMN passive_km numeric;

-- Estimate fishing hours per km2
UPDATE public.fishing SET pelagic_km = pelagic / (ST_Area(geom::geography)/1000000);
UPDATE public.fishing SET bottom_km = bottom / (ST_Area(geom::geography)/1000000);
UPDATE public.fishing SET fishing_km = fishing / (ST_Area(geom::geography)/1000000);
UPDATE public.fishing SET passive_km = passive / (ST_Area(geom::geography)/1000000);

• Select only the Atlantic Ocean area (we have used the boundaries of the Atlantic Ocean to select only the data that fall within it, joining both tables and using ST_Contains() function)

2.3. Data Output

The Fishing_Intensity_Mission_Atlantic table corresponds to fishing hours per square kilometre estimated by grid cell (0.1 degree) of the Atlantic Ocean in 2020, and spatially identified by geometry (Spatial Reference System 4326). The attributes associated are:

• gid: grid cell identifier [data type: UUID]
• name: name of the Atlantic Ocean area [data type: character]
• pelagic_km: Pelagic fishing hours per square kilometre [data type: numeric]
• bottom_km: Seabed fishing hours per square kilometre [data type: numeric]
• fishing_km: Unknown fishing hours per square kilometre [data type: numeric]
• passive_km: Passive fishing hours per square kilometre [data type: character]
• geom: grid cell geometry (EPSG: 4326) [data type: geometry]