# Storing & grouping by tow metadata

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

    mfdb_disconnect(mdb)