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("
year    month   areacell   species vessel trip tow  length  count
2019    1       45G01      COD     V1     T1   T1a    10      358
2019    1       45G01      COD     V1     T1   T1a    20      320
2019    1       45G01      COD     V1     T1   T1a    30      162
2019    1       45G02      COD     V1     T1   T1b    10      240
2019    1       45G02      COD     V1     T1   T1b    20      278
2019    1       45G02      COD     V1     T1   T1b    30      122

2019    2       45G01      COD     V2     T2   T2a    10      255
2019    2       45G01      COD     V2     T2   T2a    20      138
2019    2       45G01      COD     V2     T2   T2a    30      168
2019    2       45G02      COD     V2     T2   T2b    10      349
2019    2       45G02      COD     V2     T2   T2b    20      106
2019    2       45G02      COD     V2     T2   T2b    30      262

2019    4       45G01      COD     V1     T3   T3a    10      395
2019    4       45G01      COD     V1     T3   T3a    20      214
2019    4       45G01      COD     V1     T3   T3a    30      195
2019    4       45G02      COD     V1     T3   T3b    10      396
2019    4       45G02      COD     V1     T3   T3b    20      325
2019    4       45G02      COD     V1     T3   T3b    30      269

2019    5       45G01      COD     V2     T4   T4a    10      239
2019    5       45G01      COD     V2     T4   T4a    20      309
2019    5       45G01      COD     V2     T4   T4a    30      162
2019    5       45G02      COD     V2     T4   T4b    10      342
2019    5       45G02      COD     V2     T4   T4b    20      175
2019    5       45G02      COD     V2     T4   T4b    30      299
"))

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:

# TODO: Use native pipe syntax once available
x <- mfdb_dplyr_sample(mdb, c('trip_start_port', 'trip_end_port', 'trip_start_port_latitude'))
x <- dplyr::select(x, 'year', 'month', 'trip_start_port', 'trip_end_port', 'trip_start_port_latitude', 'count')
as.data.frame(x)
##    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
mfdb_disconnect(mdb)