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
'))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
"))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
'))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
'))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.66667Any 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 14As 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.9888889An 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 3And 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 3mfdb_disconnect(mdb)