import ibis
from ibis import _
con = ibis.duckdb.connect(extensions=["spatial"])

jtree = (con
    .read_geo("/vsicurl/https://minio.carlboettiger.info/public-biodiversity/NPS.gdb")
    .filter(_.PARKNAME == "Joshua Tree")
    .mutate(geometry = _.SHAPE.convert('EPSG:3857', 'EPSG:4326'))
    .mutate(start_date = ibis.date('2020-01-01'), 
            end_date = ibis.date('2023-12-31')) # arbitrarily add some date ranges
)

fires = (con
    .read_parquet("https://minio.carlboettiger.info/public-fire/calfire-2023.parquet")
    .mutate(alarm_date = _.ALARM_DATE.cast('Date'), 
            control_date = _.CONT_DATE.cast('Date'))
    .select('alarm_date', 'control_date', 'geometry')
)

jtree_fires = (fires
    .join(jtree, 
          [
           fires.geometry.within(jtree.geometry), 
           fires.alarm_date.between(jtree.start_date, jtree.end_date)
          ])
)
    

#ibis.to_sql(jtree_fires)

jtree_fires.execute()