Pulling EM Vessel Lists Directly From ODDS: A How-To Guide

In fisheries observation and deployment, accurate vessel lists are crucial. Currently, the system relies on Loki's em_vessels_by_adp, but this method has proven to be outdated and includes extraneous records. This article provides a comprehensive guide on how to pull Electronic Monitoring (EM) vessel lists directly from the Observer Data Deployment System (ODDS), ensuring more accurate and up-to-date information.

The Problem with Loki's em_vessels_by_adp

Loki's em_vessels_by_adp has several limitations that make it less reliable for generating current vessel lists. One significant issue is that it is not consistently maintained, leading to the inclusion of outdated records. For example, the system continues to assume the EM Trawl Electronic Fishery Program (EFP) is ongoing and automatically enrolls these vessels, even if they are no longer active in the program. This can lead to inaccurate vessel lists, impacting observer deployments and data collection efforts. Therefore, it is imperative to find a more reliable and current method for generating these lists.

To address these issues, the recommended solution is to pull vessel lists directly from ODDS. ODDS provides a more current and accurate representation of registered and eligible vessels. By querying ODDS directly, we can avoid the pitfalls of relying on a potentially outdated system like Loki's em_vessels_by_adp. This ensures that observer deployments are based on the most accurate information available, enhancing the efficiency and effectiveness of fisheries monitoring efforts. This approach not only improves data accuracy but also streamlines the process of vessel list generation, saving time and resources.

Why ODDS is the Better Source

ODDS, or the Observer Data Deployment System, offers several advantages over Loki's em_vessels_by_adp for generating EM vessel lists. First and foremost, ODDS is the primary system for managing observer deployments and vessel registration, meaning it is more likely to contain the most up-to-date information. The database is regularly updated with the latest vessel statuses, program enrollments, and regulatory changes, ensuring that the data is current and accurate. This real-time data is crucial for effective fisheries management and observation efforts.

Another key benefit of using ODDS is its comprehensive data structure. ODDS contains detailed information about vessel eligibility, registration periods, and associated business rules. This level of detail allows for highly specific and targeted queries, ensuring that the generated vessel lists meet the exact requirements of the observer deployment program. For example, it is possible to filter vessels by strata year, group code, and registration status, providing a precise list of vessels that are currently eligible for EM programs. This level of granularity is not always available in older systems like Loki's em_vessels_by_adp.

Furthermore, querying ODDS directly reduces the risk of including extraneous or outdated records. Because the data is actively maintained and updated, there is less chance of including vessels that are no longer participating in EM programs. This improves the accuracy of vessel lists and reduces the potential for errors in observer deployment planning. In addition, the direct access to ODDS data eliminates the need for intermediary systems or data transformations, streamlining the process and reducing the potential for data corruption or loss. This streamlined process not only saves time but also enhances the reliability of the data.

How to Pull Vessel Lists from ODDS

To pull vessel lists from ODDS, you will need to use SQL queries to extract the relevant data. This section provides sample queries for retrieving both requested and registered vessels. These queries can be adapted to meet specific needs, such as filtering by year, group code, or vessel status. Understanding how to construct and execute these queries is essential for anyone involved in fisheries observation and deployment.

Retrieving Requested Vessels

The following SQL query retrieves a list of requested vessels from ODDS. This query joins several tables, including odds.odds_request_opt_strata, odds.odds_eligible_opt_strata, norpac.atl_lov_vessel, and odds.odds_adp_business_rules, to gather comprehensive information about each vessel. The query filters the results by strata year, ensuring that only vessels eligible for a specific year are included. This is crucial for planning observer deployments for the correct period.

SELECT a.*, b.vessel_seq, b.group_code, c.name, c.permit, d.group_description
FROM odds.odds_request_opt_strata a
  LEFT JOIN odds.odds_eligible_opt_strata b
    ON a.eligible_opt_seq = b.eligible_opt_seq
  LEFT JOIN norpac.atl_lov_vessel c
    ON b.vessel_seq = c.vessel_seq
  LEFT JOIN odds.odds_adp_business_rules d
    ON b.group_code = d.group_code
WHERE strata_year = 2026

This query selects various fields, including vessel sequence number (vessel_seq), group code (group_code), vessel name (name), permit number (permit), and group description (group_description). The LEFT JOIN operations ensure that all records from the primary table (odds.odds_request_opt_strata) are included, even if there are no matching records in the other tables. This is important for capturing all requested vessels, regardless of their current status. The WHERE clause filters the results to include only vessels with a strata year of 2026, but this can be adjusted to retrieve data for other years as needed. This flexibility is essential for adapting the query to different deployment scenarios.

Retrieving Registered Vessels

The following SQL query retrieves a list of registered vessels from ODDS. This query joins odds.odds_registered_vessel, norpac.atl_lov_vessel, and odds.odds_adp_business_rules tables to provide detailed information about registered vessels, including their registration dates, permit numbers, and group descriptions. This information is essential for confirming that vessels are officially registered and eligible for EM programs. The results are ordered by the begin date of registration, making it easier to track vessel registrations over time.

SELECT a.vessel_seq, a.group_code, c.group_description, a.begin_date, a.end_date, b.permit, b.name
FROM odds.odds_registered_vessel a
  LEFT JOIN norpac.atl_lov_vessel b
    ON a.vessel_seq = b.vessel_seq
  LEFT JOIN odds.odds_adp_business_rules c
    ON a.group_code = c.group_code
ORDER BY BEGIN_DATE

This query selects fields such as vessel sequence number (vessel_seq), group code (group_code), group description (group_description), begin and end dates of registration (begin_date, end_date), permit number (permit), and vessel name (name). The use of LEFT JOIN ensures that all registered vessels are included in the results, even if they do not have matching records in the other tables. The ORDER BY clause sorts the results by the begin date, which is useful for tracking the history of vessel registrations. This historical perspective can be valuable for identifying trends and patterns in vessel participation in EM programs.

Example: Filtering for a Specific Vessel

To filter the results for a specific vessel, such as the