Samples can be assigned to a trip, which provides a way of including socioeconomic data into MFDB.

The following examples use the following table_string helper to succintly define tables:

# Convert a string into a data.frame
table_string <- function (text, ...) read.table(
    text = text,
    blank.lines.skip = TRUE,
    header = TRUE,
    stringsAsFactors = FALSE,
    ...)

Then connect to a database and set up some areas/divisions:

mdb <- mfdb(tempfile(fileext = '.duckdb'))
mfdb_import_area(mdb, table_string('
name  division size
45G01     divA   10
45G02     divA  200
45G03     divB  400
'))

Importing data

Trips can have a start_port and end_port associated with them, before we can do this we need to define any ports we use. We do this with mfdb_import_port_taxonomy:

mfdb_import_port_taxonomy(mdb, table_string('
name    description     institute   latitude longitude
ISHFF   Hafnarfjordur   ISL         64.04   21.57
NOHVJ   Hvalfjordur     ISL         64.21   21.45
ISKEF   Keflavik    ISL         64.00   22.33
ISREK   Reykjavik   ISL         64.09   21.55
ISSEJ   Seydisfjordur   ISL         65.15   13.55
ISKJF   Skerjafjordur   ISL         64.07   21.54
'))

We can now define trips that use these ports:

mfdb_import_trip_taxonomy(mdb, table_string('
name    start_date  end_date    crew    oil_consumption start_port  end_port
T1  2019-01-21  2019-02-11  4   3000        ISKEF       ISREK
T2  2019-01-24  2019-02-14  5   9000        ISKEF       ISSEJ
T3  2019-04-21  2019-05-11  4   3000        ISREK       ISKEF
T4  2019-04-24  2019-05-14  5   9000        ISSEJ       ISKEF
'))

There is no formal link between trips and tows, so you can link a sample to a tow without recording trips. But we can create tows and vessels to use:

mfdb_import_tow_taxonomy(mdb, table_string('
name latitude longitude  depth length
 T1a     64.1    -23.15  98.82     10
 T1b     64.1    -23.15  98.82     10
 T2a     64.1    -23.15  98.82     10
 T2b     64.1    -23.15  98.82     10
 T3a     64.1    -23.15  98.82     10
 T3b     64.1    -23.15  98.82     10
 T4a     64.1    -23.15  98.82     10
 T4b     64.1    -23.15  98.82     10
'))
mfdb_import_vessel_taxonomy(mdb, table_string('
name full_name
V1  "Vessel 1"
V2  "Vessel 2"
'))

And finally import data that is associated to a vessel/trip/tow:

mfdb_import_survey(mdb, data_source = "cod2000",
table_string([1654 chars quoted with '"']))

Querying data

We can now use the mfdb_sample_* functions to select this data back out again.

We can group and filter by any of the trip attributes. For instance, to group/filter by trip_start_date:

agg_data <- mfdb_sample_count(mdb, c('trip_start_date', 'length'), list(
    trip_start_date = '2019-01-21',
    length = mfdb_unaggregated()))
agg_data
## $`0.0.0.0.0`
##   year step area trip_start_date length number
## 1  all  all  all      2019-01-21     10    598
## 2  all  all  all      2019-01-21     20    598
## 3  all  all  all      2019-01-21     30    284

Note that we’ve summed T1a and T1b, since their start date matches.

We can use mfdb_group() to aggregate several ports, for instance:

agg_data <- mfdb_sample_count(mdb, c('trip_start_port', 'length'), list(
    trip_start_port = mfdb_group(KEF = 'ISKEF', other = c('ISREK', 'ISSEJ')),
    length = mfdb_unaggregated()))
agg_data
## $`0.0.0.0.0`
##   year step area trip_start_port length number
## 1  all  all  all             KEF     10   1202
## 2  all  all  all             KEF     20    842
## 3  all  all  all             KEF     30    714
## 4  all  all  all           other     10   1372
## 5  all  all  all           other     20   1023
## 6  all  all  all           other     30    925

We can also use mfdb_dplyr_sample to re-extract data, joining with port metadata:

mfdb_dplyr_sample(mdb, c('trip_start_port', 'trip_end_port', 'trip_start_port_latitude')) |>
    dplyr::select('year', 'month', 'trip_start_port', 'trip_end_port', 'trip_start_port_latitude', 'count') |>
    as.data.frame()
##    year month trip_start_port trip_end_port trip_start_port_latitude count
## 1  2019     1           ISKEF         ISREK                    64.00   358
## 2  2019     1           ISKEF         ISREK                    64.00   320
## 3  2019     1           ISKEF         ISREK                    64.00   162
## 4  2019     1           ISKEF         ISREK                    64.00   240
## 5  2019     1           ISKEF         ISREK                    64.00   278
## 6  2019     1           ISKEF         ISREK                    64.00   122
## 7  2019     2           ISKEF         ISSEJ                    64.00   255
## 8  2019     2           ISKEF         ISSEJ                    64.00   138
## 9  2019     2           ISKEF         ISSEJ                    64.00   168
## 10 2019     2           ISKEF         ISSEJ                    64.00   349
## 11 2019     2           ISKEF         ISSEJ                    64.00   106
## 12 2019     2           ISKEF         ISSEJ                    64.00   262
## 13 2019     4           ISREK         ISKEF                    64.09   395
## 14 2019     4           ISREK         ISKEF                    64.09   214
## 15 2019     4           ISREK         ISKEF                    64.09   195
## 16 2019     4           ISREK         ISKEF                    64.09   396
## 17 2019     4           ISREK         ISKEF                    64.09   325
## 18 2019     4           ISREK         ISKEF                    64.09   269
## 19 2019     5           ISSEJ         ISKEF                    65.15   239
## 20 2019     5           ISSEJ         ISKEF                    65.15   309
## 21 2019     5           ISSEJ         ISKEF                    65.15   162
## 22 2019     5           ISSEJ         ISKEF                    65.15   342
## 23 2019     5           ISSEJ         ISKEF                    65.15   175
## 24 2019     5           ISSEJ         ISKEF                    65.15   299