ICES-DOME Pilot Database

A pilot study to evaluate the database structure using the ICES-DOME data

This site presents the initial version of the database design and its potential applications using the publicly available ICES-DOME dataset.

Source Data

• Thematic views page at ICES-DOME: Contaminants and biological effects of contaminants in biota

Search criteria

Parameter Group

• Metals and matalloids (Cobalt, Copper, Manganese, Molybdenum, Selenium, Zinc)
• Major inorganic constituents (Iodine)

Count of Sediment Sites

The table below summarizes the number of sediment sites collected across 19 countries between 1985 and 2024.

db = {
  const StratumSQLite = await window._stratumSQLite;
  return StratumSQLite.open(window._dbPath, {
    sqlJsPath: window._sqljsBase,
    cacheKey:  "pilot-ices-dome@v0.1.5",
  });
}

aq = require("arquero")

// --- Query: long format counts per cruise type, year, and element ---
counts_long = db.query(`
    SELECT country,
           year,
           parameter,
           COUNT(*) AS count
    FROM (SELECT DISTINCT p2.project_id,
                          p2.country,
                          s2.site_id,
                          s2.sample_id,
                          CAST(s2.year AS TEXT) year,
                          s.param,
                          UPPER(SUBSTR(p1.param_description, 1, 1)) || LOWER(SUBSTR(p1.param_description, 2)) AS parameter
          FROM sediment s
                   JOIN parameter p1 ON p1.param = s.param
                   JOIN sample s2 ON s2.sample_id = s.sample_id
                   JOIN site s3 ON s3.site_id = s.site_id
                   JOIN project p2 ON p2.project_id = s.project_id
          WHERE p1.param_description IN ('cobalt', 'copper', 'iodine', 'manganese', 'molybdenum', 'selenium', 'zinc')) t
    GROUP BY country, year, parameter
`)

// --- Pivot to wide format using Arquero ---
elements_wide = ["Cobalt", "Copper", "Iodine", "Manganese", "Molybdenum", "Selenium", "Zinc"]

counts_wide = aq.from(counts_long)
  .groupby("country", "year")
  .pivot("parameter", "count")
  .orderby("country", "year")
  .rename({ country: "Country", year: "Year" })
  .objects()

Inputs.table(counts_wide, {
  columns: ["Country", "Year", ...elements_wide],
  layout: "auto",
  rows: 20
})

Database Design and Creation

After creating the ER (Entity–Relationship) diagram, the tables were implemented in a single SQLite file containing 10 tables. All required data were extracted from the ICES-DOME export file and imported into the corresponding tables.

• See the DB Schema page for details on the database architecture.

Geospatial Analysis

To facilitate efficient core and sample selection, distances to the nearest coastline were calculated for the ICES-DOME data. In addition, the nearest country and municipality, as well as the sea and ocean names, were identified based on the core locations.

• See the Distance Calculation page for the methods and results of calculating the distance to the nearest coastline.
• See the Location Names page for the estimation of location names from geographic coordinates.
• See the Interactive Map page for an interactive visualization of site locations with meta-information.

Data Export

Data can be exported for both exploration and submission purposes.

• See the Export to Tabular File page for a description of a single tabular file format (.tsv.gz) that is extracted and merged from all database tables for data exploration.

Tools

• See the Pilot DB Viewer page for a read-only view of the pilot database.
• See the Sediment Trace Element Map page for an interactive map view of the trace elements.