Common SSVID VMS positions and registry tables
N
Nate Miller
Currently in BQ it is hard to join VMS position tables to country registry tables. Typically each VMS dataset has a unique SSVID though this may differ between each VMS system.
However the corresponding field in the registry table is not labeled SSVID. Thus rather than a simple
USING (ssvid)
join, the user must know what field is used as the SSVID for each specific VMS system and then join based that field.Requiring this specific knowledge makes us more reliant on specific teammates who have this knowledge.
N
Nate Miller
hey Willa. I think we might pause on this for a moment. When I made this request I just wanted to ensure that whatever field we were using to unique identify a vessel in our system was called an SSVID. Currently we are trying to ensure we get unique identifiers for each national registry. Once we have secured that for a few more registry sources (like Panama and Chile) it would be good to revisit this.
For now as an example, in this table (world-fishing-827.pipe_registry_sources_v1_internal.registry_per_v20250601) the
list_uvi
contains the unique identifier (minus the PER prefix). This should be the source specific vessel identifier in the Peruvian registry and in the Peruvian VMS.W
Willa Brooks
Nate Miller can you share the different registry table names where making this field name change to ssvid would be beneficial?
Hannah Linder
Nate Miller do you mean just changing the column name in the registry table to SSVID to help with this?
N
Nate Miller
Hannah Linder: Yes. I think there are additional updates we can make, but this is a very simple one.
Rollan has also been trying to document the current methods/queries needed to combine registry and VMS position data and we're hoping to identify ways that this might be streamlined. He'll have more info on this after he finishes with the Taiwan workshop next week and has a few days to finalize the initial analysis.