El Niño 2026, atmospheric rivers, and California’s burn scars: one SQL engine, two data models

Start with elevation, buildings and roads in the catalog

All four datasets sit in wherobots_open_data: the ASTER GDEM 30 m (98 ft) elevation model, Overture’s buildings and roads, and Regrid’s parcel sample. The companion notebook fetches the CAL FIRE perimeter and USGS basins. It also registers every shared view, so each query below runs as written, with no setup of its own. Each elevation row stores a footprint polygon, so WherobotsDB selects rows by location before it reads any pixel. Ten rasters cover the Eaton area.

Filter on the bounding box before the spatial test

Overture features carry a bbox struct beside the geometry. With a range filter on it before ST_Intersects, the first building count finished in 22.8 seconds. Without it, the run was cancelled at 7% after three minutes. The range check on four numbers is cheap, so the costly geometry test runs only on the rows that pass.

-- scar holds the Eaton perimeter; the notebook fetches it from CAL FIRE and registers it as a view
WITH aoi AS (
  SELECT ST_Transform(
           ST_Buffer(ST_Transform(g, 'EPSG:4326', 'EPSG:32611'), 3000),
           'EPSG:32611', 'EPSG:4326') AS g
  FROM scar
),
bld AS (
  SELECT t.id, t.geometry AS geom
  FROM wherobots_open_data.overture_maps_foundation.buildings_building t
  WHERE t.bbox.xmin BETWEEN -118.20 AND -117.97   -- range scan first
    AND t.bbox.ymin BETWEEN  34.13  AND  34.27
)
SELECT count(*) FROM bld b JOIN aoi ON ST_Intersects(b.geom, aoi.g);

118,746 footprints pass the bounding box, 83,930 fall within 3 km (1.9 mi) of the perimeter, and 10,652 sit inside the scar.

Each of two questions below gets its own raster vector join. For buildings the answer is one elevation per footprint, so the first join reads the raster under every footprint. For roads, however, the answer is a length. So the second join turns the raster into a zone polygon first, then clips each road segment against it.

Buildings: read the elevation under each footprint

-- scar, dem and inaoi (the 83,930 footprints within 3 km) are views from the notebook's setup cell
WITH scar_floor AS (
  SELECT min(RS_ZonalStats(d.rast, s.g, 1, 'min', true)) AS m FROM dem d, scar s
),
be AS (
  -- pool pixels across every raster a footprint touches: total value over pixel count
  SELECT i.id,
         sum(RS_ZonalStats(d.rast, i.geom, 1, 'sum', true))
           / NULLIF(sum(RS_ZonalStats(d.rast, i.geom, 1, 'count', true)), 0) AS elev
  FROM inaoi i JOIN dem d ON RS_Intersects(d.rast, i.geom)
  GROUP BY i.id
)
SELECT count(*) AS below_scar_floor
FROM be, scar_floor
WHERE be.elev < scar_floor.m;

Roads: a raster vector join through a low-ground zone

-- aoi, dem and roads are views from the notebook's setup cell
WITH masked AS (
  SELECT RS_MapAlgebra(rast, 'D', 'out[0] = rast[0] < 223 ? 1 : 0;') AS m FROM dem
),
polys AS (
  SELECT t.p.geom AS g
  FROM masked LATERAL VIEW explode(RS_Polygonize(m, 1)) t AS p
  WHERE t.p.value = 1.0
),
low AS (
  SELECT ST_Intersection(u.g, a.g) AS g
  FROM (SELECT ST_Union_Aggr(ST_MakeValid(g)) AS g FROM polys) u, aoi a
)
SELECT r.class,
       sum(ST_Length(ST_Transform(ST_Intersection(r.geom, low.g),
                                  'EPSG:4326', 'EPSG:32611'))) / 1000 AS km
FROM roads r JOIN low ON ST_Intersects(r.geom, low.g)
GROUP BY r.class
ORDER BY km DESC;

Joining to what USGS already modelled

An elevation line is only a screen. USGS publishes the basins as a live feature service, so they join in as one more vector layer. Each building counts once per distance, however many basins it touches.

-- high holds the 42 dissolved high-hazard basins; the notebook fetches them from USGS
WITH z AS (
  SELECT g AS inside,
         ST_Transform(ST_Buffer(ST_Transform(g, 'EPSG:4326', 'EPSG:32611'), 500),  'EPSG:32611', 'EPSG:4326') AS b500,
         ST_Transform(ST_Buffer(ST_Transform(g, 'EPSG:4326', 'EPSG:32611'), 1000), 'EPSG:32611', 'EPSG:4326') AS b1k
  FROM high
)
SELECT sum(CASE WHEN ST_Intersects(b.geom, z.inside) THEN 1 ELSE 0 END) AS inside,
       sum(CASE WHEN ST_Intersects(b.geom, z.b500)   THEN 1 ELSE 0 END) AS inside_or_500m,
       sum(CASE WHEN ST_Intersects(b.geom, z.b1k)    THEN 1 ELSE 0 END) AS inside_or_1km
FROM bld b, z
WHERE ST_Intersects(b.geom, z.b1k);

696 footprints sit inside a high-hazard basin, 7,713 inside or within 500 m (1,640 ft), and 16,748 within 1 km (0.6 mi). The map below plots the 7,713 on Sentinel-2 imagery from 5 August 2026. They form a solid band along the scar’s lower edge, where the basins drain onto the valley floor. Nineteen months on, the scar is still bare.

Joining parcels to put a value on the screen

Building counts measure how many structures sit in each screen, not what they are worth. Regrid’s parcel sample in wherobots_open_data.partner_samples adds use codes, acreage and assessed values, and a join to the exposed footprints carries those onto both screens. The join runs in two steps so nothing is counted twice: first the distinct parcel and footprint pairs, keyed on Regrid’s stable ll_uuid, then one row per parcel for its use code, acreage and assessed value. A footprint that crosses a parcel line counts once in the total and once in each use it touches, so the rows sum slightly above the total.

-- exposed holds the footprints from either screen; parcels is Regrid's sample with the same bbox filter as bld
WITH pairs AS (
  SELECT DISTINCT p.ll_uuid, p.usedesc, e.id AS bid
  FROM parcels p JOIN exposed e ON ST_Intersects(p.geometry, e.geom)
),
parcel AS (  -- one row per parcel, so acres and value are counted once
  SELECT ll_uuid, first(usedesc) AS usedesc, first(ll_gisacre) AS acres, first(parval) AS parval
  FROM parcels WHERE ll_uuid IN (SELECT ll_uuid FROM pairs) GROUP BY ll_uuid
),
by_use AS (
  SELECT usedesc, count(*) AS parcels, round(sum(acres)) AS acres, round(sum(parval) / 1e6, 1) AS assessed_musd
  FROM parcel GROUP BY usedesc
),
bld_use AS (SELECT usedesc, count(distinct bid) AS buildings FROM pairs GROUP BY usedesc)
SELECT u.usedesc, u.parcels, b.buildings, u.acres, u.assessed_musd
FROM by_use u JOIN bld_use b USING (usedesc) ORDER BY u.parcels DESC;

Inside or within 500 m (1,640 ft) of a high-hazard basin: 5,934 parcels, 7,709 footprints

Below the scar’s lowest point, 223 m (732 ft): 10,513 parcels, 15,227 footprints

The 7,713 footprints inside or within 500 m (1,640 ft) of a high-hazard basin sit on 5,934 parcels with a combined assessed value of $4.8 billion. Single-family homes account for $4.7 billion of it. The downhill screen is larger: the 15,233 footprints below the scar’s lowest point sit on 10,513 parcels assessed at $11.1 billion, and here the mix changes. Single-family homes are $8.5 billion of that total, and the remaining $2.6 billion is the commercial and apartment stock along the valley floor, led by $511 million in office buildings, $499 million in apartments and $222 million in stores. The two screens overlap, so the totals do not add. Every value comes from the assessor’s 2023 tax roll, so it describes the neighbourhood before the fire and before any rebuilding. Proposition 13 holds California assessed values well below market value, so these totals are a floor.

RS_Value reads the elevation under a point, so a cross-section is a line of generated points.

-- a point every 0.00025 degrees, about 28 m, along 118.125° W, and the elevation under each
WITH pts AS (
  SELECT i, ST_SetSRID(ST_Point(-118.125, 34.265 - i * 0.00025), 4326) AS pt
  FROM (SELECT explode(sequence(0, 460)) AS i)
)
SELECT p.i, ST_Y(p.pt) AS lat, RS_Value(d.rast, p.pt, 1) AS elev
FROM pts p
JOIN wherobots_open_data.aster_gdem.v3_30m d ON ST_Intersects(d.footprint, p.pt)
ORDER BY p.i;

The line climbs to 1,577 m (5,174 ft) on the ridge. Then it crosses 2.4 km (1.5 mi) of high-hazard basin and 4.5 km (2.8 mi) of scar, down to 248 m (814 ft). 1,112 footprints lie within 60 m (197 ft) of it. The first of those buildings already sit inside the lower end of the high-hazard stretch.

Scope and limits of the exposure screen

These counts are an exposure screen. Neither they nor the USGS assessment predict where a debris flow travels or what it buries. USGS notes that most come within two years of a fire. So this winter falls near the end of that window, while the burned slopes are still recovering.

Three limits carry over. The elevation model is a surface model, so rooftops and treetops count as ground. Proximity is straight-line distance rather than flow path. Finally, the perimeter is simplified to 47 vertices, which changes its area by 0.4%. Flow routing would turn distance bands into runout paths. However, WherobotsDB has no built-in flow-direction function, so that takes a custom function over the rasters. It is the natural next step.

From one burn scar to the western United States

This screen covers one burn scar: the Eaton fire above Altadena. The same queries extend to every recent burn scar in California, and across the western United States, where USGS publishes debris-flow hazard assessments after major fires. Overture buildings and roads and the ASTER elevation model in wherobots_open_data already cover that whole region. Each new fire adds a perimeter and a set of basins to the same joins, once the study-area bounding box and UTM zone are updated to match. Governor Newsom declared the emergency ahead of El Niño rain this winter. The open question for every recently burned community is how many homes and roads sit below a high-hazard basin when that rain arrives.

One engine processed buildings, roads, elevation, and USGS hazard polygons, as vector and raster, with every file left where it sits. An AI agent grounded only in text, documents, databases, and the internet holds none of those layers, so it cannot say what sits below a basin. WherobotsDB supplies that physical-world context by joining them in place.

Imagery: Copernicus Sentinel data 2026. Elevation: ASTER GDEM is a product of METI and NASA. Buildings and roads: Overture Maps Foundation. Parcels: Regrid parcel sample. Fire perimeter: CAL FIRE FRAP, CC BY. Hazard basins: USGS Landslide Hazards Program, public domain.