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,
    ...)

Firstly, 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

Before any measurements assoicated with a tow can be imported, we need to define the tows that they will be associated with, and attributes such as length:

mfdb_import_tow_taxonomy(mdb, table_string('
name latitude longitude  depth length
   A     64.1    -23.15  98.82     10
   B     63.4    -20.18  44.90     10
   C     64.9    -13.55 140.91     20
   D     66.2    -18.55 122.61     20
'))

For other possible columns, see ?mfdb_import_tow_taxonomy.

Now tows are defined, we can import data that uses these definitions:

mfdb_import_survey(mdb, data_source = "tow_example_a",
    table_string("
year    month   areacell        species tow  length  age     weight
2000    1       45G01           COD     A    21      2       210
2000    1       45G02           COD     A    34      3       220
2000    1       45G03           COD     A    34      3       230
2000    1       45G01           COD     B    62      1       320
2000    1       45G02           COD     B    53      1       330
2000    1       45G03           COD     B    54      2       430
2000    1       45G01           COD     C    28      2       210
2000    1       45G02           COD     C    34      3       220
2000    1       45G03           COD     C    24      3       230
2000    1       45G01           COD     D    12      1       320
2000    1       45G02           COD     D    44      1       330
2000    1       45G03           COD     D    14      2       430
    "))

Importing Data: Longlines

mfdb_import_tow_taxonomy also allows us to import bait_type and hook_count, useful when importing data from longline fisheries.

As with other taxonomy tables, we have to first define bait types before we can use them:

mfdb_import_bait_type_taxonomy(mdb, table_string('
name    description
b1  "Bait type 1"
b2  "Bait type 2"
b3  "Bait type 3"
'))

…and then use mfdb_import_tow_taxonomy as before. Since the names don’t overlap with the previous call, the old tow definitions won’t be removed:

mfdb_import_tow_taxonomy(mdb, table_string('
name latitude longitude  length hook_count bait_type
 llA     63.4    -20.18      11         30       b1
 llB     63.4    -20.18      12         30       b2
 llC     63.4    -20.18      13         30       b3
'))

Finally, import some data using these definitions:

mfdb_import_survey(mdb, data_source = 'longline', table_string('
year    month   areacell        species tow  length age weight
2000    1   45G01       COD llA 12  15  236
2000    1   45G01       COD llA 47  6   243
2000    1   45G01       COD llA 92  11  118
2000    1   45G01       COD llB 13  14  392
2000    1   45G01       COD llB 15  4   169
2000    1   45G01       COD llB 52  3   272
2000    1   45G01       COD llC 85  10  132
2000    1   45G01       COD llC 94  9   342
2000    1   45G01       COD llC 71  12  375
    '))

Importing Data: Gillnets

mfdb_import_tow_taxonomy also allows us to import net_count, net_type and mesh_size, useful when importing data from Gillnet fisheries.

Again, we need to define net types first:

mfdb_import_net_type_taxonomy(mdb, table_string('
name    description
black   Black
orange  Orange
white   White
'))

…define the tows:

# Add more detailed information for gillnets
mfdb_import_tow_taxonomy(mdb, table_string('
name latitude longitude  length net_count net_type mesh_size
 gnA     63.4    -20.18      21         2  black         6
 gnB     63.4    -20.18      22         2  orange        7
 gnC     63.4    -20.18      23         2  white         8
'))

…and import some data using these definitions:

mfdb_import_survey(mdb, data_source = 'gillnet', table_string('
year    month   areacell        species tow  length age weight
2000    1   45G01       COD gnA 34  10  314
2000    1   45G01       COD gnA 45  14  255
2000    1   45G01       COD gnA 48  5   322
2000    1   45G01       COD gnB 24  8   170
2000    1   45G01       COD gnB 83  7   122
2000    1   45G01       COD gnB 15  4   152
2000    1   45G01       COD gnC 33  14  311
2000    1   45G01       COD gnC 79  6   373
2000    1   45G01       COD gnC 57  5   186
    '))

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 tow attributes. For instance, to group/filter by tow_depth:

agg_data <- mfdb_sample_meanlength(mdb, c('tow_depth'), list(
    step = mfdb_timestep_yearly,
    tow_depth = mfdb_interval('depth', c(0, 50, 100, 150)),
    null = NULL))
agg_data
## $`0.0.0.0`
##   year step area tow_depth number     mean
## 1  all    1  all    depth0      3 56.33333
## 2  all    1  all  depth100      6 26.00000
## 3  all    1  all   depth50      3 29.66667

Any of the other tow attributes are available with a “tow_” prefix. For example, we can show metadata per tow by using mfdb_unaggregated:

# Show all tow data unaggregated
agg_data <- mfdb_sample_meanlength(mdb, c('tow', 'tow_latitude', 'tow_longitude', 'tow_depth', 'tow_length'), list(
    step = mfdb_timestep_yearly,
    area = mfdb_group(divB = 'divB'),
    tow = mfdb_unaggregated(),
    tow_latitude = mfdb_unaggregated(),
    tow_longitude = mfdb_unaggregated(),
    tow_depth = mfdb_unaggregated(),
    tow_length = mfdb_unaggregated(),
    null = NULL))
agg_data
## $`0.0.0.0.0.0.0.0`
##   year step area tow tow_latitude tow_longitude tow_depth tow_length number
## 1  all    1 divB   A         64.1        -23.15     98.82         10      1
## 2  all    1 divB   B         63.4        -20.18     44.90         10      1
## 3  all    1 divB   C         64.9        -13.55    140.91         20      1
## 4  all    1 divB   D         66.2        -18.55    122.61         20      1
##   mean
## 1   34
## 2   54
## 3   24
## 4   14

As well as using tow attributes to group/filter, we can use the tow to scale the output of the query. For example, the following scales mean length by tow length, for each tow:

agg_data <- mfdb_sample_meanlength(mdb, c('tow'), list(
    step = mfdb_timestep_yearly,
    tow = mfdb_unaggregated(),
    null = NULL), scale_index = 'tow_length')
agg_data
## $`0.0.0.0`
##    year step area tow    number     mean
## 1   all    1  all   A 0.3000000 29.66667
## 2   all    1  all   B 0.3000000 56.33333
## 3   all    1  all   C 0.1500000 28.66667
## 4   all    1  all   D 0.1500000 23.33333
## 5   all    1  all gnA 0.1428571 42.33333
## 6   all    1  all gnB 0.1363636 40.66667
## 7   all    1  all gnC 0.1304348 56.33333
## 8   all    1  all llA 0.2727273 50.33333
## 9   all    1  all llB 0.2500000 26.66667
## 10  all    1  all llC 0.2307692 83.33333

…or scale by tow length whilst grouping by depth:

agg_data <- mfdb_sample_scaled(mdb, c('tow_depth'), list(
    step = mfdb_timestep_yearly,
    tow_depth = mfdb_interval('depth', c(0, 50, 100, 150)),
    null = NULL), scale = 'tow_length')
agg_data
## $`0.0.0.0`
##   year step area tow_depth number mean_weight
## 1  all    1  all    depth0   0.10   1.8777778
## 2  all    1  all  depth100   0.05   0.2166667
## 3  all    1  all   depth50   0.10   0.9888889

An example of grouping by tow_bait_type:

agg_data <- mfdb_sample_count(mdb, c('tow_bait_type'), list(
    tow_bait_type = mfdb_unaggregated()))
agg_data
## $`0.0.0.0`
##   year step area tow_bait_type number
## 1  all  all  all          <NA>     21
## 2  all  all  all            b1      3
## 3  all  all  all            b2      3
## 4  all  all  all            b3      3

And finally tow_net_type:

agg_data <- mfdb_sample_count(mdb, c('tow_net_type'), list(
    tow_net_type = mfdb_unaggregated()))
agg_data
## $`0.0.0.0`
##   year step area tow_net_type number
## 1  all  all  all         <NA>     21
## 2  all  all  all        black      3
## 3  all  all  all       orange      3
## 4  all  all  all        white      3