How the project fits together

gear_share

WITH mapped AS (
  SELECT DISTINCT
    geartype
  FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map`
  WHERE
    document_id IN UNNEST(@candidate_ids)
), totals AS (
  SELECT
    m.geartype,
    SUM(m.fishing_hours) AS h
  FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` AS m
  JOIN mapped AS g
    USING (geartype)
  WHERE
    date >= @start
    AND date < @end
    AND cell_ll_lat >= @min_lat
    AND cell_ll_lat < @max_lat
    AND fishing_hours > 0
  GROUP BY
    1
)
SELECT
  geartype,
  ROUND(h, 3) AS fishing_hours,
  ROUND(100 * SAFE_DIVIDE(h, SUM(h) OVER ()), 3) AS share_pct
FROM totals
ORDER BY
  fishing_hours DESC,
  geartype

monthly_method

WITH mapped AS (
  SELECT DISTINCT
    geartype,
    family
  FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map`
  WHERE
    family IN ('hooks', 'nets')
)
SELECT
  EXTRACT(MONTH FROM m.date) AS month,
  g.family,
  ROUND(SUM(m.fishing_hours), 3) AS fishing_hours
FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` AS m
JOIN mapped AS g
  USING (geartype)
WHERE
  date >= @start AND date < @end AND fishing_hours > 0
GROUP BY
  1,
  2
ORDER BY
  1,
  2

First 20 of 300 bins shown; the chart uses all 300.

hotspots

WITH mapped AS (
  SELECT DISTINCT
    geartype
  FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map`
  WHERE
    document_id IN UNNEST(@candidate_ids)
)
SELECT
  CAST(FLOOR(cell_ll_lat) AS INT64) AS latitude,
  CAST(FLOOR(cell_ll_lon) AS INT64) AS longitude,
  ROUND(SUM(fishing_hours), 3) AS fishing_hours
FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort`
JOIN mapped
  USING (geartype)
WHERE
  date >= @start
  AND date < @end
  AND cell_ll_lat >= @min_lat
  AND cell_ll_lat < @max_lat
  AND fishing_hours > 0
GROUP BY
  1,
  2
ORDER BY
  fishing_hours DESC,
  latitude,
  longitude
LIMIT @n

First 20 of 300 bins shown; the chart uses all 300.

fleet_activity

SELECT
  COALESCE(v.vessel_class_gfw, 'unknown') AS geartype,
  COUNT(DISTINCT d.mmsi) AS vessels,
  ROUND(SUM(d.fishing_hours), 3) AS fishing_hours,
  ROUND(SAFE_DIVIDE(SUM(d.fishing_hours), COUNT(DISTINCT d.mmsi)), 3) AS hours_per_vessel
FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.daily_effort` AS d
LEFT JOIN `cs145-p2-sandbox-20261011.cs145_fishing_reference.vessels` AS v
  ON d.mmsi = v.mmsi AND EXTRACT(YEAR FROM d.date) = v.year
WHERE
  d.date >= @start AND d.date < @end AND d.fishing_hours > 0
GROUP BY
  1
ORDER BY
  fishing_hours DESC,
  geartype

metadata_audit

SELECT
  CASE
    WHEN v.mmsi IS NULL
    THEN 'no_metadata'
    WHEN v.vessel_class_registry IS NULL
    THEN 'no_registry_gear'
    ELSE 'registry_gear_available'
  END AS metadata_status,
  COUNT(DISTINCT d.mmsi) AS vessels,
  ROUND(SUM(d.fishing_hours), 3) AS fishing_hours
FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.daily_effort` AS d
LEFT JOIN `cs145-p2-sandbox-20261011.cs145_fishing_reference.vessels` AS v
  ON d.mmsi = v.mmsi AND EXTRACT(YEAR FROM d.date) = v.year
WHERE
  d.date >= @start AND d.date < @end AND d.fishing_hours > 0
GROUP BY
  1
ORDER BY
  1

Search: Fishing techniques that use baited hooks or nets: longline fishing, jigging, gillnetting, seine fishing and trawling

Seine fishing

seine fishing ( or seine - haul fishing ; sayn ) is a method of fishing that employs a surrounding net, called a seine, that hangs vertically in the water with its bottom edge held down by weights and its top edge buoyed by floats. seine nets can be deployed from the shore as a beach seine, or from a boat. boats deploying seine nets are known as seiners. two main types of seine net are deployed from seiners : purse seines and danish seines. a seine differs from a gillnet, in that a seine encloses fish, where a gillnet directly snares fish.

Document 1513400 · cosine distance 0.274

Longline fishing

longline fishing, or longlining, is a commercial fishing angling technique that uses a long main line with baited hooks attached at intervals via short branch lines called snoods or gangions. a snood is attached to the main line using a clip or swivel, with the hook at the other end. longlines are classified mainly by where they are placed in the water column. this can be at the surface or at the bottom. lines can also be set by means of an anchor, or left to drift. hundreds or even thousands of baited hooks can hang from a single line. this can lead to the death of many different marine species known as bycatch. longliners – fishing vessels rigged for longlining – commonly target swordfish, tuna, halibut, sablefish and many other species. in some unstable fisheries, such as the patagonian toothfish, fishermen may be limited to as few as 25 hooks per line. in contrast, commercial longliners in certain robust fisheries of the bering sea and north pacific generally run over 2, 500 hand - baited hooks on a single series of connected lines many miles in length. longlines can be set to hang near

Document 338407 · cosine distance 0.321

Jigging

jigging is the practice of fishing with a jig, a type of weighted fishing lure. a jig consists of a heavy metal ( typically lead ) sinker with an attached fish hook that is usually obscured inside a soft lure or feather - like decorations. jigs are intended to create a jerky, vertical " jumping " motion to attract fish, as opposed to other common lures like swimbaits, spoons and spinnerbaits, which move through the water more or less horizontally. the jig is very versatile and can be used in both salt and fresh water. many deeper water fish species are attracted to the lure, which has made it popular among anglers for years. the jigging technique mainly involves rapid lifting motions of a fishing rod, which jerk the line exert a temporarily upward pull upon the sinking lure. when the target fish is enticed to swallow the lure, the angler then sets the hook to pierce and tether the fish in the mouth. because the jigs are weighted, a harder - than - usual hookset is often needed to overcome the added weight and provide sufficient momentum to pierce the hook point into the fish mouth. jigging gears are often involved with the lu

Document 764476 · cosine distance 0.343

Trawling

trawling is an industrial method of fishing that involves pulling a fishing net through the water behind one or more boats. the net used for trawling is called a trawl. this principle requires netting bags which are towed through water to catch different species of fishes or sometimes targeted species. trawls are often called towed gear or dragged gear. the boats that are used for trawling are called trawlers or draggers. trawlers vary in size from small open boats with as little as 30 hp ( 22 kw ) engines to large factory trawlers with over 10, 000 hp ( 7. 5 mw ). trawling can be carried out by one trawler or by two trawlers fishing cooperatively ( pair trawling ). trawling can be contrasted with trolling. while trawling involves a net and is typically done for commercial usage, trolling instead involves a reel, rod and a bait or a lure and is typically done for recreational purposes. trawling is also commonly used as a scientific sampling, or survey, method.

Document 335840 · cosine distance 0.365

Gillnetting

gillnetting is a fishing method that uses gillnets : vertical panels of netting that hang from a line with regularly spaced floaters that hold the line on the surface of the water. the floats are sometimes called " corks " and the line with corks is generally referred to as a " cork line. " the line along the bottom of the panels is generally weighted. traditionally this line has been weighted with lead and may be referred to as " lead line. " a gillnet is normally set in a straight line. gillnets can be characterized by mesh size, as well as colour and type of filament from which they are made. fish may be caught by gillnets in three ways : wedged – held by the mesh around the body. gilled – held by mesh slipping behind the opercula. tangled – held by teeth, spines, maxillaries, or other protrusions without the body penetrating the mesh. most fish have gills. a fish swims into a net and passes only part way through the mesh. when it struggles to free itself, the twine slips behind the gill cover and prevents escape. gillnets are so effective that their use is closely monitored and regulated by fisheries management and

Document 1003338 · cosine distance 0.396

IDs used: 1003338, 1513400, 335840, 338407, 764476

Source descriptions are checked against the explicit gear mappings. Duplicate evidence must not multiply activity.

SQL and checked result · Definition and test

Search: Fishing with nets that surround fish or are dragged through the water

Gillnetting

gillnetting is a fishing method that uses gillnets : vertical panels of netting that hang from a line with regularly spaced floaters that hold the line on the surface of the water. the floats are sometimes called " corks " and the line with corks is generally referred to as a " cork line. " the line along the bottom of the panels is generally weighted. traditionally this line has been weighted with lead and may be referred to as " lead line. " a gillnet is normally set in a straight line. gillnets can be characterized by mesh size, as well as colour and type of filament from which they are made. fish may be caught by gillnets in three ways : wedged – held by the mesh around the body. gilled – held by mesh slipping behind the opercula. tangled – held by teeth, spines, maxillaries, or other protrusions without the body penetrating the mesh. most fish have gills. a fish swims into a net and passes only part way through the mesh. when it struggles to free itself, the twine slips behind the gill cover and prevents escape. gillnets are so effective that their use is closely monitored and regulated by fisheries management and

Document 1003338 · cosine distance 0.349

Seine fishing

seine fishing ( or seine - haul fishing ; sayn ) is a method of fishing that employs a surrounding net, called a seine, that hangs vertically in the water with its bottom edge held down by weights and its top edge buoyed by floats. seine nets can be deployed from the shore as a beach seine, or from a boat. boats deploying seine nets are known as seiners. two main types of seine net are deployed from seiners : purse seines and danish seines. a seine differs from a gillnet, in that a seine encloses fish, where a gillnet directly snares fish.

Document 1513400 · cosine distance 0.357

Trawling

trawling is an industrial method of fishing that involves pulling a fishing net through the water behind one or more boats. the net used for trawling is called a trawl. this principle requires netting bags which are towed through water to catch different species of fishes or sometimes targeted species. trawls are often called towed gear or dragged gear. the boats that are used for trawling are called trawlers or draggers. trawlers vary in size from small open boats with as little as 30 hp ( 22 kw ) engines to large factory trawlers with over 10, 000 hp ( 7. 5 mw ). trawling can be carried out by one trawler or by two trawlers fishing cooperatively ( pair trawling ). trawling can be contrasted with trolling. while trawling involves a net and is typically done for commercial usage, trolling instead involves a reel, rod and a bait or a lure and is typically done for recreational purposes. trawling is also commonly used as a scientific sampling, or survey, method.

Document 335840 · cosine distance 0.383

IDs used: 1003338, 1513400, 335840

Source descriptions are checked against the explicit gear mappings. Duplicate evidence must not multiply activity.

The search also returned hook-fishing articles. They were excluded using the reviewed method mapping. The remaining IDs map to gillnet, seine, and trawl categories.

SQL and checked result · Definition and test

Apparent fishing hours and percent of selected hours by gear; only document-mapped gears.

Inputs and output

candidate_ids: ARRAY<STRING> is supplied separately by the shared binding helper. It contains checked Wikipedia document IDs.

Output columns: geartype, fishing_hours, share_pct

Rules

Use DISTINCT mapped geartypes from candidate document IDs to avoid multiplying activity. Half-open dates, inclusive lower and exclusive upper latitude. Denominator is only the selected mapped activity. Exclude nonpositive fishing hours.

SQL template

WITH mapped AS (
  SELECT DISTINCT
    geartype
  FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map`
  WHERE
    document_id IN UNNEST(@candidate_ids)
), totals AS (
  SELECT
    m.geartype,
    SUM(m.fishing_hours) AS h
  FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` AS m
  JOIN mapped AS g
    USING (geartype)
  WHERE
    date >= @start
    AND date < @end
    AND cell_ll_lat >= @min_lat
    AND cell_ll_lat < @max_lat
    AND fishing_hours > 0
  GROUP BY
    1
)
SELECT
  geartype,
  ROUND(h, 3) AS fishing_hours,
  ROUND(100 * SAFE_DIVIDE(h, SUM(h) OVER ()), 3) AS share_pct
FROM totals
ORDER BY
  fishing_hours DESC,
  geartype

Test: expected and actual

Assertion passed.

Monthly apparent fishing hours by verified hook/net family.

Inputs and output

Output columns: month, family, fishing_hours

Rules

Use the reviewed gear taxonomy in document_map. DISTINCT geartype,family avoids repeated evidence. Coarse fishing/fixed_gear labels remain unclassified, not assigned to hooks or nets.

SQL template

WITH mapped AS (
  SELECT DISTINCT
    geartype,
    family
  FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map`
  WHERE
    family IN ('hooks', 'nets')
)
SELECT
  EXTRACT(MONTH FROM m.date) AS month,
  g.family,
  ROUND(SUM(m.fishing_hours), 3) AS fishing_hours
FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` AS m
JOIN mapped AS g
  USING (geartype)
WHERE
  date >= @start AND date < @end AND fishing_hours > 0
GROUP BY
  1,
  2
ORDER BY
  1,
  2

Test: expected and actual

Assertion passed.

Top one-degree bins of apparent fishing activity for retrieved document categories.

Inputs and output

candidate_ids: ARRAY<STRING> is supplied separately by the shared binding helper. It contains checked Wikipedia document IDs.

Output columns: latitude, longitude, fishing_hours

Rules

FLOOR latitude/longitude including negative coordinates. Bin coordinates are lower-left corners, not centroids. Rank hours descending then coordinates. Candidate mapping must be deduplicated.

SQL template

WITH mapped AS (
  SELECT DISTINCT
    geartype
  FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map`
  WHERE
    document_id IN UNNEST(@candidate_ids)
)
SELECT
  CAST(FLOOR(cell_ll_lat) AS INT64) AS latitude,
  CAST(FLOOR(cell_ll_lon) AS INT64) AS longitude,
  ROUND(SUM(fishing_hours), 3) AS fishing_hours
FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort`
JOIN mapped
  USING (geartype)
WHERE
  date >= @start
  AND date < @end
  AND cell_ll_lat >= @min_lat
  AND cell_ll_lat < @max_lat
  AND fishing_hours > 0
GROUP BY
  1,
  2
ORDER BY
  fishing_hours DESC,
  latitude,
  longitude
LIMIT @n

Test: expected and actual

Assertion passed.

Q1 observed active fishing vessels and hours by gear, retaining unmatched metadata.

Inputs and output

Output columns: geartype, vessels, fishing_hours, hours_per_vessel

Rules

Join on MMSI AND year. Count distinct vessel identifiers across grid cells and dates. Include unmatched daily records under unknown. One vessel-year metadata row per key is a checked prerequisite. Only positive fishing hours. Vessel IDs are AIS identifiers, not guaranteed physical-vessel identities.

SQL template

SELECT
  COALESCE(v.vessel_class_gfw, 'unknown') AS geartype,
  COUNT(DISTINCT d.mmsi) AS vessels,
  ROUND(SUM(d.fishing_hours), 3) AS fishing_hours,
  ROUND(SAFE_DIVIDE(SUM(d.fishing_hours), COUNT(DISTINCT d.mmsi)), 3) AS hours_per_vessel
FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.daily_effort` AS d
LEFT JOIN `cs145-p2-sandbox-20261011.cs145_fishing_reference.vessels` AS v
  ON d.mmsi = v.mmsi AND EXTRACT(YEAR FROM d.date) = v.year
WHERE
  d.date >= @start AND d.date < @end AND d.fishing_hours > 0
GROUP BY
  1
ORDER BY
  fishing_hours DESC,
  geartype

Test: expected and actual

Assertion passed.

Count metadata availability and associated activity, keeping unmatched rows.

Inputs and output

Output columns: metadata_status, vessels, fishing_hours

Rules

Priority: unmatched MMSI-year -> no_metadata; matched with NULL registry gear -> no_registry_gear; otherwise registry_gear_available. Count DISTINCT MMSI, never sum grid-cell vessel counts. Preserve left join.

SQL template

SELECT
  CASE
    WHEN v.mmsi IS NULL
    THEN 'no_metadata'
    WHEN v.vessel_class_registry IS NULL
    THEN 'no_registry_gear'
    ELSE 'registry_gear_available'
  END AS metadata_status,
  COUNT(DISTINCT d.mmsi) AS vessels,
  ROUND(SUM(d.fishing_hours), 3) AS fishing_hours
FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.daily_effort` AS d
LEFT JOIN `cs145-p2-sandbox-20261011.cs145_fishing_reference.vessels` AS v
  ON d.mmsi = v.mmsi AND EXTRACT(YEAR FROM d.date) = v.year
WHERE
  d.date >= @start AND d.date < @end AND d.fishing_hours > 0
GROUP BY
  1
ORDER BY
  1

Test: expected and actual

Assertion passed.

Store each entry under its name in LIBRARY. The helper binds typed values; it does not splice values into SQL strings.

LIBRARY = {
  "gear_share": {
    "description": "Apparent fishing hours and percent of selected hours by gear; only document-mapped gears.",
    "parameters": {
      "start": "DATE",
      "end": "DATE",
      "min_lat": "FLOAT64",
      "max_lat": "FLOAT64"
    },
    "output_columns": [
      "geartype",
      "fishing_hours",
      "share_pct"
    ],
    "domain_rules": "Use DISTINCT mapped geartypes from candidate document IDs to avoid multiplying activity. Half-open dates, inclusive lower and exclusive upper latitude. Denominator is only the selected mapped activity. Exclude nonpositive fishing hours.",
    "sql": "WITH mapped AS (SELECT DISTINCT geartype FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map` WHERE document_id IN UNNEST(@candidate_ids)), totals AS (\nSELECT m.geartype,SUM(m.fishing_hours) h FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` m JOIN mapped g USING(geartype)\nWHERE date>=@start AND date<@end AND cell_ll_lat>=@min_lat AND cell_ll_lat<@max_lat AND fishing_hours>0 GROUP BY 1)\nSELECT geartype,ROUND(h,3) fishing_hours,ROUND(100*SAFE_DIVIDE(h,SUM(h) OVER()),3) share_pct FROM totals ORDER BY fishing_hours DESC,geartype"
  },
  "monthly_method": {
    "description": "Monthly apparent fishing hours by verified hook/net family.",
    "parameters": {
      "start": "DATE",
      "end": "DATE"
    },
    "output_columns": [
      "month",
      "family",
      "fishing_hours"
    ],
    "domain_rules": "Use the reviewed gear taxonomy in document_map. DISTINCT geartype,family avoids repeated evidence. Coarse fishing/fixed_gear labels remain unclassified, not assigned to hooks or nets.",
    "sql": "WITH mapped AS (SELECT DISTINCT geartype,family FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map` WHERE family IN ('hooks','nets'))\nSELECT EXTRACT(MONTH FROM m.date) month,g.family,ROUND(SUM(m.fishing_hours),3) fishing_hours FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` m JOIN mapped g USING(geartype)\nWHERE date>=@start AND date<@end AND fishing_hours>0 GROUP BY 1,2 ORDER BY 1,2"
  },
  "hotspots": {
    "description": "Top one-degree bins of apparent fishing activity for retrieved document categories.",
    "parameters": {
      "start": "DATE",
      "end": "DATE",
      "min_lat": "FLOAT64",
      "max_lat": "FLOAT64",
      "n": "INT64"
    },
    "output_columns": [
      "latitude",
      "longitude",
      "fishing_hours"
    ],
    "domain_rules": "FLOOR latitude/longitude including negative coordinates. Bin coordinates are lower-left corners, not centroids. Rank hours descending then coordinates. Candidate mapping must be deduplicated.",
    "sql": "WITH mapped AS (SELECT DISTINCT geartype FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map` WHERE document_id IN UNNEST(@candidate_ids))\nSELECT CAST(FLOOR(cell_ll_lat) AS INT64) latitude,CAST(FLOOR(cell_ll_lon) AS INT64) longitude,ROUND(SUM(fishing_hours),3) fishing_hours\nFROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` JOIN mapped USING(geartype)\nWHERE date>=@start AND date<@end AND cell_ll_lat>=@min_lat AND cell_ll_lat<@max_lat AND fishing_hours>0 GROUP BY 1,2 ORDER BY fishing_hours DESC,latitude,longitude LIMIT @n"
  },
  "fleet_activity": {
    "description": "Q1 observed active fishing vessels and hours by gear, retaining unmatched metadata.",
    "parameters": {
      "start": "DATE",
      "end": "DATE"
    },
    "output_columns": [
      "geartype",
      "vessels",
      "fishing_hours",
      "hours_per_vessel"
    ],
    "domain_rules": "Join on MMSI AND year. Count distinct vessel identifiers across grid cells and dates. Include unmatched daily records under unknown. One vessel-year metadata row per key is a checked prerequisite. Only positive fishing hours. Vessel IDs are AIS identifiers, not guaranteed physical-vessel identities.",
    "sql": "SELECT COALESCE(v.vessel_class_gfw,'unknown') geartype,COUNT(DISTINCT d.mmsi) vessels,ROUND(SUM(d.fishing_hours),3) fishing_hours,\nROUND(SAFE_DIVIDE(SUM(d.fishing_hours),COUNT(DISTINCT d.mmsi)),3) hours_per_vessel FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.daily_effort` d LEFT JOIN `cs145-p2-sandbox-20261011.cs145_fishing_reference.vessels` v ON d.mmsi=v.mmsi AND EXTRACT(YEAR FROM d.date)=v.year\nWHERE d.date>=@start AND d.date<@end AND d.fishing_hours>0 GROUP BY 1 ORDER BY fishing_hours DESC,geartype"
  },
  "metadata_audit": {
    "description": "Count metadata availability and associated activity, keeping unmatched rows.",
    "parameters": {
      "start": "DATE",
      "end": "DATE"
    },
    "output_columns": [
      "metadata_status",
      "vessels",
      "fishing_hours"
    ],
    "domain_rules": "Priority: unmatched MMSI-year -> no_metadata; matched with NULL registry gear -> no_registry_gear; otherwise registry_gear_available. Count DISTINCT MMSI, never sum grid-cell vessel counts. Preserve left join.",
    "sql": "SELECT CASE WHEN v.mmsi IS NULL THEN 'no_metadata' WHEN v.vessel_class_registry IS NULL THEN 'no_registry_gear' ELSE 'registry_gear_available' END metadata_status,\nCOUNT(DISTINCT d.mmsi) vessels,ROUND(SUM(d.fishing_hours),3) fishing_hours FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.daily_effort` d LEFT JOIN `cs145-p2-sandbox-20261011.cs145_fishing_reference.vessels` v ON d.mmsi=v.mmsi AND EXTRACT(YEAR FROM d.date)=v.year\nWHERE d.date>=@start AND d.date<@end AND d.fishing_hours>0 GROUP BY 1 ORDER BY 1"
  }
}

def parameters(entry,values,ids=None):
    out=[bigquery.ScalarQueryParameter(k,typ,values[k]) for k,typ in entry['parameters'].items()]
    if '@candidate_ids' in entry['sql']:out.append(bigquery.ArrayQueryParameter('candidate_ids','STRING',ids or []))
    return out

def run_definition(name,values,ids=None):
    entry=LIBRARY[name]
    return run_select(entry['sql'],parameters(entry,values,ids))

result = run_definition(
    'gear_share',
    dict(start='2023-01-01', end='2024-01-01', min_lat=-90., max_lat=90.),
    ids=['1003338', '1513400', '335840', '338407', '764476']
)

# Five hand-checked fixtures run the same library SQL on tiny inline tables.
# Duplicate evidence rows must not duplicate activity; unmatched metadata survives.
fixture='''vessels AS (
 SELECT 'v1' mmsi,2023 year,'trawlers' vessel_class_gfw,CAST(NULL AS STRING) vessel_class_registry
 UNION ALL SELECT 'v2',2023,'drifting_longlines','drifting_longlines'),
daily_effort AS (
 SELECT DATE '2023-01-01' date,'v1' mmsi,10.0 fishing_hours
 UNION ALL SELECT DATE '2023-01-02','v1',20.0
 UNION ALL SELECT DATE '2023-01-01','v2',15.0
 UNION ALL SELECT DATE '2023-01-01','v3',5.0),
monthly_effort AS (
 SELECT DATE '2023-01-01' date,'trawlers' geartype,-0.2 cell_ll_lat,-0.2 cell_ll_lon,100.0 fishing_hours
 UNION ALL SELECT DATE '2023-01-01','drifting_longlines',10.2,20.1,50.0
 UNION ALL SELECT DATE '2023-01-01','set_gillnets',11.2,20.2,50.0),
document_map AS (
 SELECT 'd1' document_id,'trawlers' geartype,'nets' family
 UNION ALL SELECT 'd1','trawlers','nets'
 UNION ALL SELECT 'd2','drifting_longlines','hooks'
 UNION ALL SELECT 'd3','set_gillnets','nets')'''
expected={
'gear_share':[dict(geartype='trawlers',fishing_hours=100,share_pct=50),dict(geartype='drifting_longlines',fishing_hours=50,share_pct=25),dict(geartype='set_gillnets',fishing_hours=50,share_pct=25)],
'monthly_method':[dict(month=1,family='hooks',fishing_hours=50),dict(month=1,family='nets',fishing_hours=150)],
'hotspots':[dict(latitude=-1,longitude=-1,fishing_hours=100),dict(latitude=10,longitude=20,fishing_hours=50),dict(latitude=11,longitude=20,fishing_hours=50)],
'fleet_activity':[dict(geartype='trawlers',vessels=1,fishing_hours=30,hours_per_vessel=30),dict(geartype='drifting_longlines',vessels=1,fishing_hours=15,hours_per_vessel=15),dict(geartype='unknown',vessels=1,fishing_hours=5,hours_per_vessel=5)],
'metadata_audit':[dict(metadata_status='no_metadata',vessels=1,fishing_hours=5),dict(metadata_status='no_registry_gear',vessels=1,fishing_hours=30),dict(metadata_status='registry_gear_available',vessels=1,fishing_hours=15)]}
values=dict(start='2023-01-01',end='2024-01-01',min_lat=-90.,max_lat=90.,n=3)
LIBRARY_TESTS=[]
for name,entry in LIBRARY.items():
    sql=entry['sql']
    for table in ['vessels','daily_effort','monthly_effort','document_map']:sql=sql.replace(f'`{T}.{table}`',table)
    sql='WITH '+fixture+', '+sql[5:] if sql.startswith('WITH ') else 'WITH '+fixture+' '+sql
    result=run_select(sql,parameters(entry,values,['d1','d2','d3']))
    assert results_equal(result['rows'],expected[name],entry['output_columns'],ordered=True),(name,result)
    LIBRARY_TESTS.append({'definition':name,'expected':expected[name],'actual':result['rows'],'passed':True})
print('Five library fixtures passed: duplicate mappings, duplicate vessel observations, NULL registry gear, missing metadata, and negative-coordinate flooring.')
display(pd.DataFrame([{'definition':x['definition'],'passed':x['passed']} for x in LIBRARY_TESTS]))
(ROOT/'tests.json').write_text(json.dumps(LIBRARY_TESTS,default=str,indent=2))

messages = build_ab_messages(
    question, schema_notes, LIBRARY,
    candidate_ids=candidate_ids,
    output_columns=output_columns
)