SchemaSpy Analysis of topp.public Generated by
SchemaSpy
Generated by SchemaSpy on Wed Dec 27 13:25 PST 2017
Database Type: PostgreSQL - 9.3.20 SourceForge.net

XML Representation
Insertion Order Deletion Order (for database loading/purging scripts)

Table / View Children Parents Columns Rows Comments
ac_deploy_current 8 view
ac_palmyra_stations 4 70
ac_palmyra_stations_vw 4 view
accepted_filetypes 3 3 27
accepted_products 3 2 9
acoustic_atn_station 7 view This view summarizes acoustic detections at main Block Lab locations by station and site. It is used by the ATN.
acoustic_data 18 2,964,525 This table contains all Block Lab acoustic detection data. It joins to acoustic_deployment with receiver and receiver_dnum fields and to tm_acoustic_with_eventid with code and codespace fields.
acoustic_data_vmt 7 view
acoustic_dep_current 15 view
acoustic_deployment 26 2,272 This table contains deployment info for acoustic receivers affiliated with Block Lab acoustic detection data. A deployment represents a specific period of time at a specific location and is denoted by the fields receiver and receiver_dnum. The geographic name for the deployment is broken down into three levels by area of influence - station, site and region. These levels are subjective. Joins to acoustic_data with receiver and receiver_dnum.
acoustic_files 9 635
acoustic_foreign 18 481 This table tracks ID codes of unknown, non-Block Lab tags detected on our network.
acoustic_recovery 11 354 This table contains recovery info for acoustic receivers affiliated with Block Lab acoustic detection data. Generally the only important fields are receiver, receiver_dnum and recdatetime. It joins to acoustic_deployment with receiver and receiver_dnum.
acoustic_vmtprogramming 29 11
acoustic_vr4files 9 16,650
acoustic_vr4plan 8 1
acoustic_vr4pos 6 16,528 Simple table containing positions from "sbd_pos.csv" files for VR4G receivers.
acoustic_whiteshark 16 view
aec 39 view
all_names 5 view
animalexcursioncopyview 6 view
animalview 22 view
argos_diag 3 2,880,594 This table contains raw Argos diag data for allactive PTTs in program 2621 and any others for which we are user-on-copy or a password has been shared with us. The table is updated nightly. It is a sourcetable for many others.
argos_diag_with_lc 4 2,880,595
argos_diag_with_lc_lat_lon 8 2,840,252
argos_diag_with_lc_lat_lon_add_rows 8 6,193
argos_diag_with_lc_lat_lon_bak 8 2,833,807
argos_diag_with_lc_lat_lon_changed_rows 8 0
argos_diag_with_lc_lat_lon_delete_rows 8 5,125
argos_diag_with_lc_lat_lon_view 42 view
argos_prv 4 2,857,223 This table contains raw Argos prv data for allactive PTTs in program 2621 and any others for which we are user-on-copy or a password has been shared with us. The table is updated nightly. It is a sourcetable for many others.
atn_deploy_rec_fgsg 34 view This view creates a minimum dataset of fish and shark group deploy, recovery, processing and management metadata.
atn_eventids 1 2,029
atn_files 4 2,857
atn_species_cnt 3 55
atn_species_cnt_popup 3 28
atn_track_ingest 5 517
atn_track_qc 18 6,287 This table was initially created to track reprocessing of low quality SSM tracks found on the ATN. It has been expanded to include additional controls for track display on the ATN.
best_url 20 view
bestsessions 6 view
bestupdates 5 view
bogusenddates 6 view
bs2 6 view
cache_argos 2 1,085,781
canada 1 1
casecopyview 9 view
conventionalrecoveriescopyview 12 view
conventionaltaggingcopyview 9 view
curr_meta_runnable 34 0
dbltag_with_metadata 8 view
dbltagshark_with_metadata 4 view
dbltagsmarlin_with_metadata 4 view
deleteme 3 9
deleteme2 4 3
deleteme3 4 3
deploymentcopyview 25 view
deployview 15 view
diag_satdeployments 22 view
diag_satdeployments_mc 23 view This view gathers transmitted Argos data for tags with records in the satdeployments table having valid startdate values. It includes all fields from satdeployments plus the Argos data. Joins to this table will usually be made to eventid, pttnumber or toppid.
distinctsatdeployments 20 view
download_urls 2 5 1,610
duplicatestartdates 3 view
encountercopyview 9 view
encounterdatacopyview 15 view
encounterview 62 view
eventid_combined 17 view
eventid_combined_with_ownerid 34 view
eventid_combined_with_ownerid_all_rows 34 view
eventid_parsed 17 view
eventid_to_run 1 0
eventid_union 13 view
eventid_unionx 13 view
examinationcopyview 16 view
fishencountercopyview 29 view
fishgroup_popups 9 view
fishgroup_popups_diag 13 view This view takes the Argos data from diag_satdeployments_mc and breaks it into separate fields. It joins to fishgroup deploy data and is used for catching new popups. Joins to this table will usually be made to eventid or pttnumber.
freshness 5 view
harvested_files_url 9 view
junk.deleteme 1 0
junk.td 1 0
junk.tdasss 1 3
ltd2310programingview 47 view
mamvis_deployments 4 view
mamvis_diag 6 view
mbari_diag 3 19,773
mbari_prv 4 19,793
my_dbltagshark_with_metadata 4 view
my_fishgroup_popups_diag 2 view
my_pdt_after_popoff_with_metadata 9 view
my_pdt_with_metadata 9 view
my_psatlongitude_with_metadata 9 view
my_satellitelocation_with_metadata 14 view
my_sharkgroup_popups_diag 2 view
my_tad_with_metadata 8 view
my_tat_with_metadata 8 view
my_tat_with_metadata2 8 view
new_atn_track_qc_row 51 0
new_deployment_row 31 4
pdt_location 7 view
pdt_with_satdeploymentid 8 view
phantomsatdeployments 20 view
popups_diag 12 view This view takes all Argos data from diag_satdeployments_mc and breaks it into separate fields. The name should probably be changed to reflect the fact that more than popup data is present. It joins to fishgroup and sharkgroup deploy data and is used for catching new popups. Joins to this table will usually be made to eventid or pttnumber.
prev_meta 34 41
private_turtle_archival 170 view
private_turtle_sat 181 view
private_turtle_smru 130 view
progd 20 view
progs 17 view
prv_satdeployments 19 view
psatlongitude_with_satdeploymentid 6 view
pttcopyview 12 view
pttview 12 view
public_turtle_archival 116 view
public_turtle_sat 150 view
public_turtle_smru 89 view
recoveriescopyview 15 view
recoveryview 23 view
sat_turtles_non_smru 72 view
satdeployments 20 4,646 This is a critical table for controlling the nightly Argos download. Several fields must be populated for a specific PTT download. These are eventid, pttnumber, speciesname, and startdate. Commonly PTTs are predeployed by using temporary eventids and speciesnames. Joins to this table will usually be made to eventid, pttnumber or toppid.
satdeployments_report_url 3 view
satdeployments_report_url_beta 3 view
satdeployments_url 22 view
satdeployments_with_phantoms 20 view
satdeployments_with_urls 19 view
satdeployview 15 view
satellitelocation 12 12,689
satellitelocation_with_satdeploymentid 14 view
serial_combo 3 3
shadowedenddates 6 view
sharkgroup_popups_diag 12 view This view takes the Argos data from diag_satdeployments_mc and breaks it into separate fields. It joins to sharkgroup deploy data and is used for catching new popups. Joins to this table will usually be made to eventid or pttnumber.
source_files 6 view
special_argos_share_processing 1 10
spot_goodness 6 view
ssm_complete 3 1,905 This table reflects the SSM files available in the Recovery directories for all species (fishgroup and sharkgroup). It is possible for any SSM version to be present, so eventid is not a unique field.
ssmupdates 8 30,997
ssmupdates_test 7 327
sulikowski 5 24
table_argos_diag_with_lc_lat_lon_with_dups 8 2,829,380
tables_with_comments 1 view
tad_with_satdeploymentid 7 view
tagcopyview 13 view
taggingcopyview 24 view
tagprocessingcopyview 33 view
tagview 9 view
tat_with_satdeploymentid 7 view
tblabft_deploy_all 28 view This is a basic view that gathers all essential ABFT deploy data. Used as a source in other views.
tblabft_deploy_rec_all 55 view This view gathers all essential ABFT deploy, recovery and processing data. It calculates deployage using Restrepo 2010.
tblabft_geodata 18 42,371
tblabft_geodata_ssm 26 82,333 This table combines the standard SSM output with basic ABFT metadata for all ABFT with SSM data.
tblabft_iccat 17 view
tblabft_iccat_nodata 19 view
tblabft_recovery_all 25 view This is a basic view that gathers all essential ABFT recovery data. Used as a source in other views.
tblalb_geodata 34 3,628
tblchagos_deploy_rec_all 31 view
tblchagos_receivers 15 view
tbldrifter_ssts 9 12,291 This table contains SST data from drifting popup tags. The data comes from the Wildlife Computers -Status.csv file. The table is updated nightly for any actively transmitting tags within the last 90 days.
tblfg_pdt 9 734,903 This table contains Wildlife PDT data from all available fishgroup tags.
tblfg_ssm 22 2,880 This table combines the standard SSM output with basic metadata for fish with SSM data, except for ABFT, PBFT and marlin.
tblfg_tad 8 751,670 This table contains Wildlife time at depth data from all available fishgroup tags. It is easy to join with a programming table (using eventid) to get the depth value for each bin_num, but it is tricky to parse out into individual fields.
tblfg_tat 8 749,419 This table contains Wildlife time at temperature data from all available fishgroup tags. It is easy to join with a programming table (using eventid) to get the temperature value for each bin_num, but it is tricky to parse out into individual fields.
tblfgarchivaldeployment 92 2,702 This table contains deployment metadata from all archival tagged fishgroup animals. Typically joins will be made to the fgadkey or toppid fields. Important fields used as query filters are project, seriesname, successful, taggingdate, tagmodel, tagtype and releasetype. This can be a confusing table. There are probably some unnecessary fields. All of the yes/no fields use 1 and 0 as their values and should be changed from type text to integer. All date fields should be converted from type text to date or timestamp.
tblfgarchivalfiles 53 view
tblfgarchivalprogramming 61 2,074 This table contains programming metadata for archival tagged fishgroup animals. Typically joins will be made to the fgapkey field. All date fields should be converted from type text to date or timestamp.
tblfgarchivalrecovery 54 889 This table contains recovery metadata from all archival tagged fishgroup animals. Typically joins will be made to the fgarkey or toppid fields. Important fields are recdate, harvdate, lab_recdate, tagreturned, rdateestimated, and recpositionestimated. There are probably some unnecessary fields. All of the yes/no fields use 1 and 0 as their values and should be changed from type text to integer. All date fields should be converted from type text to date or timestamp.
tblfgconvdeploy 29 478
tblfgconvrecovery 21 48
tblfgmk10programming 108 748 This table contains programming metadata for later generation (MK10, miniPAT) satellite tagged fishgroup animals. Typically joins will be made to the fgsdkey field. There are 14 bin fields for depth and temperature each. The histogram bin values, in order from lowest to highest, can be found in these fields in incremental order starting with bin1. These same values are concatenated into the depth_bins and temp_bins fields, as well as stored in tblprogbin_depth and tblprogbin_temp. The idea of using the progbin tables is to make this programming table simpler. Some fields are MK10 or miniPAT specific. Many are not required for a successful deployment. Some have carried over from tblpatprogramming and should be eliminated.
tblfgprocessingstatus 59 2,040 This table contains data for tracking the status of recovered and/or popup tag data. Typically joins will be made to the fgdkey and toppid fields. The most common fields used to filter queries are med_fish, gom_fish, breedingstatus, qc_track, geolocated, ssm_complete, date_last_depth, date_last_etemp, date_last_itemp, date_last_light, no_pdt, no_tad, and no_tat. All of the yes/no fields use 1 and 0 as their values and should be changed from type text to integer. All date fields should be converted from type text to date or timestamp.
tblfgsatdeployments 108 1,610 This table contains deployment metadata from all satellite and acoustic tagged fishgroup animals. Typically joins will be made to the fgsdkey, toppid or ptt fields. Important fields used as query filters are project, seriesname, successful, taggingdate, tagmodel, tagtype and releasetype. This can be a confusing table. There are probably some unnecessary fields. All of the yes/no fields use 1 and 0 as their values and should be changed from type text to integer. All date fields should be converted from type text to date or timestamp.
tblfgsatrecovery 60 1,073 This table contains recovery metadata from all satellite and acoustic tagged fishgroup animals. Typically joins will be made to the fgsdkey or toppid fields. Important fields are actpopdate, rdate, lab_recdate, tagrecovered, fish_recaptured, rdateestimated, and recpositionestimated. There are probably some unnecessary fields. In particular there seem to be too many comment fields. All of the yes/no fields use 1 and 0 as their values and should be changed from type text to integer. All date fields should be converted from type text to date or timestamp.
tblfgspotprogramming 38 33 This table contains programming metadata for SPOT satellite tagged fishgroup animals. There are similarities to the other satellite programming tables though it is simpler due to SPOTS having fewer parameters. Typically joins will be made to the fgsdkey field. All date fields should be converted from type text to date or timestamp.
tblmarlin_deploy_rec_all 38 view
tblmarlin_geodata 13 16,748
tblmarlin_geodata_ssm 22 26,306 This table combines the standard SSM output with basic marlin metadata for all marlin with SSM data.
tblpatprogramming 80 507 This table contains programming metadata for early generation (PAT1 thru PAT5) satellite tagged fishgroup animals. Typically joins will be made to the fgsdkey field. All date fields should be converted from type text to date or timestamp.
tblpbft_days_afterdeploy 18 view
tblpbft_deploy_all 28 view
tblpbft_deploy_rec_all 44 view
tblpbft_geodata 34 90,106
tblpbft_geodata_ssm 26 93,911 This table combines the standard SSM output with basic PBFT metadata for all PBFT with SSM data.
tblpbft_recovery_all 22 view
tblprogbins_depth 4 759 This table stores different depth bin combinations used during satellite tag programming. The bins are recognized as a group by having the same bin_id. Sorting a group by bin_num puts them in programming order.
tblprogbins_dvw 2 view
tblprogbins_temp 4 909 This table stores different temperature bin combinations used during satellite tag programming. The bins are recognized as a group by having the same bin_id. Sorting a group by bin_num puts them in programming order.
tblprogbins_tvw 2 view
tblsatdeployments 15 2,020
tblsatdeployments_url 8 12,281,849
tblsgarchivaldeployment 81 28 This table is almost never used. It is rare for an archival deployment on a shark. The table structure is similar to tblfgarchivaldeployment.
tblsgarchivalprogramming 35 20 This table is almost never used. It is rare for an archival deployment on a shark. The table structure is similar to tblfgarchivalprogramming.
tblsgarchivalrecovery 43 1 This table is almost never used. It is rare for an archival deployment on a shark. The table structure is similar to tblfgarchivalrecovery.
tblsgconvdeployment 19 37
tblsgmk10programming 108 225 This table contains programming metadata for later generation (MK10, miniPAT) satellite tagged sharkgroup animals. Typically joins will be made to the sgdepkey field. There are 14 bin fields for depth and temperature each. The histogram bin values, in order from lowest to highest, can be found in these fields in incremental order starting with bin1. These same values are concatenated into the depth_bins and temp_bins fields, as well as stored in tblprogbin_depth and tblprogbin_temp. The idea of using the progbin tables is to make this programming table simpler. Some fields are MK10 or miniPAT specific. Many are not required for a successful deployment. Some have carried over from tblpatprogramming and should be eliminated.
tblsgpatprogramming 71 300 This table contains programming metadata for early generation (PAT1 thru PAT5) satellite tagged sharkgroup animals. Typically joins will be made to the sgdepkey field. All date fields should be converted from type text to date or timestamp.
tblsgprocessingstatus 38 403 This table contains data for tracking the status of recovered and/or popup tag data. Typically joins will be made to the sgdkey field. The most common fields used to filter queries are geolocated, ssm_complete, date_last_depth, date_last_etemp, date_last_itemp, date_last_light, no_pdt, no_tad, and no_tat. All of the yes/no fields use 1 and 0 as their values and should be changed from type text to integer. All date fields should be converted from type text to date or timestamp.
tblsgspotprogramming 50 262 This table contains programming metadata for SPOT satellite tagged fishgroup animals. There are similarities to the other satellite programming tables though it is simpler due to SPOTS having fewer parameters. Typically joins will be made to the sgdepkey field. There are 14 histogram bin fields, b1low to b12high, that define how temperature data will be summarized. All date fields should be converted from type text to date or timestamp.
tblshark_daylog 16 798
tblshark_deploy_rec_all 51 view
tblshark_matchmaster 9 0
tblshark_pdt 9 605,372 This table contains Wildlife PDT data from all available sharkgroup tags.
tblshark_sighting 22 1,329
tblshark_spot_endpoints 2 view
tblshark_ssm 22 25,926 This table combines the standard SSM output with basic metadata for all sharks with SSM data.
tblshark_tad 8 648,345 This table contains Wildlife time at depth data from all available sharkgroup tags. It is easy to join with a programming table (using eventid) to get the depth value for each bin_num, but it is tricky to parse out into individual fields.
tblshark_tat 8 625,500 This table contains Wildlife time at temperature data from all available sharkgroup tags. It is easy to join with a programming table (using eventid) to get the temperature value for each bin_num, but it is tricky to parse out into individual fields.
tblsharkdeployment 105 1,639 This table contains deployment metadata from all satellite and acoustic tagged sharkgroup animals. Typically joins will be made to the sgdepkey, toppid or ptt fields. Important fields used as query filters are project, seriesname, taggingdate, and tagtype. All of the yes/no fields use 1 and 0 as their values and should be changed from type text to integer. All date fields should be converted from type text to date or timestamp.
tblsharkrecovery 48 427 This table contains recovery metadata from all satellite and acoustic tagged sharkgroup animals. Typically joins will be made to the sgdepkey or toppid fields. Important fields are actpopdate, rdate, lab_recdate, tagrecovered, fish_recaptured, rdateestimated, and recpositionestimated. There are probably some unnecessary fields. All of the yes/no fields use 1 and 0 as their values and should be changed from type text to integer. All date fields should be converted from type text to date or timestamp.
tblssm_files 3 1,514
tbltgarchivaldeployment 76 32
tbltgarchivalrecovery 38 21
tbltgsatdeployment 75 109
tbltgsmrudeployments 38 64
tbltgsmrudeployments_non_null 38 view
tbltgsplashprogsheet 26 4
tbltgspotprogsheet 60 14
tbltm_tagflaws 8 7
tbltmacoustic 67 1,321 This is the management table for acoustic tags, receivers and related equipment. Typically joins will be made to one of the idcode fields. Joining to the acoustic_data table requires both idcode and codespace1. Important fields used as query filters are tagfamily, interval_min and max, taglocation, invoicedate and owner. All date fields should be converted from type text to date or timestamp.
tbltmarchival 27 1,856 This is the management table for archival tags. Typically joins will be made to the tagnumber field. Important fields used as query filters are tagmodel, tagstatus, invoicedate and owner. All date fields should be converted from type text to date or timestamp.
tbltmrma 13 833
tbltmsat 45 2,444 This is the management table for satellite tags. Typically joins will be made using both the tagnumber and pttnumber fields. Important fields used as query filters are tagmodel, tagmodel_num, tagstatus, invoicedate and owner. There is only one record for each tagnumber but there are multiples times where pttnumber is duplicated across different tag numbers. All date fields should be converted from type text to date or timestamp.
tbltopptags 9 68
tbltrccarchivaldeployment 31 56
tbltrccarchivalrecovery 14 42
tbltrccsatdeployment 16 4
tbltrccsatrecovery 4 2
tbltuna_arch_deprec_stats 11 view
tbltuna_sat_ac_deprec_stats 11 view
tblyft_geodata 37 30,367
temp_diag_satdeployments_mc 21 view
temp_fast_max_datetime 21 view
temp_fishgroup_popups_diag 10 view
temp_sharkgroup_popups_diag 9 view
test_stat_id_view 2 view
tm_acoustic_with_eventid 70 view Joins management table and deploy info.
tm_archival_with_eventid 23 view Joins management table and deploy info.
tm_sat_with_eventid 49 view Joins management table and deploy info.
tmp_prepone 3 1
toppid_tagcode_url 20 view
toppspecies 14 94
ts 5 view
turtle_archival 119 view
turtle_archival_url 5 view
turtle_dive_analysis_with_metadata 50 view
turtle_sat 74 view
turtle_satfiles_url 3 view
turtle_smru 91 view
updatesessions 6 view
updatessessions 6 view
vwfgconventional 35 view
ws_ssm_wdeprec 12 view
           
115 Tables     2,741 38,177,897  
134 Views     3,267