GlobalFishingWatch / pipe-events

Pipeline which generates different kinds of vessel events
Apache License 2.0
0 stars 0 forks source link

Limit fishing events to only fishing vessels #13

Open pwoods25443 opened 5 years ago

pwoods25443 commented 5 years ago

We should not be publishing fishing events for vessels that we do not believe are fishing vessels. Consider filtering with something like:

WITH 
  fishing as (
  SELECT
    DISTINCT CAST(mmsi as STRING) as ssvid
  FROM
    `gfw_research.vessel_info_allyears_20181002`
  WHERE
    on_fishing_list_best
    AND is_active
    AND (spoofing_days < 5
      OR spoofing_days IS NULL)
    AND (spoofing_factor < 1.05
      OR spoofing_factor IS NULL)
)
SELECT * FROM messages JOIN fishing USING (ssvid)
pwoods25443 commented 5 years ago

Need to get the list of fishing vessels from a well-defined location