Carl Boettiger cboettig

Actual Collaboration Scale:

100+ prompts from you across yesterday and today
Intensive iterative process with extensive back-and-forth
Deep scientific debugging requiring many rounds of refinement
Much more substantial effort than I initially estimated

What This Actually Represents:

Your time investment: Probably 6-8+ hours of active engagement across the two days

cboettig: Help me complete the assignment in this repo

GitHub Copilot: I'll help you complete the fisheries assignment. Let me first examine the current state of the notebook and understand what needs to be done. Let me also check the requirements and rubric to understand what exactly needs to be completed: Now I understand the assignment! This is a fisheries data analysis assignment using the Ibis framework and RAM Legacy Stock Assessment Database. There are two main exercises:

Exercise 1: Investigating North-Atlantic Cod - replicating a figure from the Millennium Ecosystem Assessment
Exercise 2: Global Fisheries Stock Collapses - replicating trends from Worm et al 2006

Let me configure the notebook environment and start implementing the solutions: Let me first run the imports cell to set up the environment: Great! Now let me start working on Exercise 1. I need to add code to connect to the RAM database and extract North Atlantic Cod data. Let me add code to the first empty Python cell:

Ma

	# same thing in pure duckdb / SQL

	import duckdb as ddb

	con = ddb.connect('test4.db', config=dict(max_memory='500MB')) # Create a DuckDB connection
	con.install_extension("spatial")
	con.load_extension("spatial")

	con.sql(f"""
	CREATE OR REPLACE VIEW fires AS (


	library(gdalcubes)
	library(dplyr)
	library(sf)
	library(spData)
	aoi <- spData::world \|> filter(name_long=="United States")

	gdalcubes_options(parallel = 100) # can be way more than "real" cpus.

	# netcdf is an inconsistent format where auto-detection of layers and CRS doesn't always work. We specify this crap in the URL:

	unlink("mobi.xyz")

	remotes::install_github("cboettig/duckdbfs")

	library(duckdbfs)
	library(dplyr)
	library(sf)

	tracts_z8 = open_dataset("https://minio.carlboettiger.info/public-social-vulnerability/2022-tracts-h3-z8.parquet", recursive = FALSE) \|>
	mutate(h8 = tolower(h8))

	# get the h0 hexes covering a polygon.


	library(duckdbfs)
	library(dplyr)
	library(sf)
	library(spData)

	duckdbfs::load_h3()
	duckdbfs::load_spatial()

	library(mapgl)
	library(dplyr)
	library(duckdbfs)

	# This test file demonstrates that given a SQL query on the parquet version
	# of the data, we can identify all the features included in the response and
	# construct a maplibre filter that filters the corresponding features in
	# the pmtiles vesion.

	pmtiles <- "https://data.source.coop/cboettig/social-vulnerability/svi2020_us_tract.pmtiles"

	import ibis
	from ibis import _
	import leafmap.maplibregl as leafmap

	con = ibis.duckdb.connect(extensions=["spatial"])
	states = con.read_geo("https://raw.githubusercontent.com/PublicaMundi/MappingAPI/master/data/geojson/us-states.json")

	## Add a fill-color column
	import matplotlib.cm as cm
	import matplotlib.colors as mcolors

	park = (con
	.read_geo("/vsicurl/https://huggingface.co/datasets/cboettig/biodiversity/resolve/main/data/NPS.gdb")
	.filter(_.UNIT_NAME == "Yosemite National Park")
	.select(_.SHAPE)
	.mutate(SHAPE = _.SHAPE.convert('EPSG:3857', 'EPSG:4326'))
	.mutate(lng = _.SHAPE.centroid().x(), lat = _.SHAPE.centroid().y())
	.mutate(h1 = h3_latlng_to_cell(_.lat, _.lng, 1) )
	.mutate(neighbors = h3_grid_disk(_.h1, 1))
	#.mutate(cells = h3_polygon_wkt_to_cells_string(_.SHAPE.as_text(),1))
	)