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

# 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 (

cboettig

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:

cboettig

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))

cboettig

cboettig

# get the h0 hexes covering a polygon. 


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

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

cboettig

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"

cboettig

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

cboettig

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))
       )

cboettig

import streamlit as st
import os


#os.environ["OPENAI_API_KEY"] = st.secrets['OPENAI_API_KEY'] # for gpt
os.environ["OPENAI_API_KEY"] = st.secrets['LITELLM_KEY']     # for litellm

from pydantic import BaseModel, Field
class SQLResponse(BaseModel):
    """Defines the structure for SQL response."""