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."""

cboettig

parquet = "https://espm-157-f24.github.io/spatial-carl-amanda-tyler/new_haven_stats.parquet"


# create sharable low-level connection, see: https://github.com/Mause/duckdb_engine
import sqlalchemy
eng = sqlalchemy.create_engine("duckdb:///:memory:")

# ibis can talk to this connection and create the VIEW
import ibis
con = ibis.duckdb.from_connection(eng.raw_connection())

cboettig

%%time

import ibis
from ibis import _
con = ibis.duckdb.connect()

endpoint="rook-ceph-rgw-nautiluss3.rook"

query=   f'''
CREATE OR REPLACE SECRET secret2 (

cboettig

import ibis
from ibis import _
con = ibis.duckdb.connect()

# example query, could be anything
subset = (gbif
  .filter(_["class"] == "Aves")
  .rename(hex = "h8")
  .group_by(_.hex)
  .agg(n = _.count())

cboettig

To solve

ImportError: cannot import name 'types' from 'geoarrow' (unknown location), 

install geoarrow-types.

ibis may also need geoarrow-cto work with geoparquet now.