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

Governor Gavin Newsom proclaimed a state of emergency on 21 September 2026 to prepare California for El Niño 2026. It names recent wildfire areas as particularly susceptible to mud and debris flows. Above Altadena, the Eaton fire left 42 basins that the US Geological Survey (USGS) rates high hazard for debris flows. USGS computes the rating for a 15-minute burst of 40 mm/h of rain (1.57 in/hour), and an atmospheric river can deliver that kind of deluge. The rating covers the basins on the hillside. The homes and roads downslope of the basins fall outside the rating, and the queries here count them.

WherobotsDB does the counting in SQL, reading every dataset in place. A join to the USGS basins returns 7,718 mapped building footprints inside the high-hazard basins or within 500 m (1,640 ft) of them. A raster vector join over elevation returns 411 km (255 mi) of mapped road and path.

Start with elevation, buildings and roads in the catalog

All three datasets sit in wherobots_open_data: the Copernicus 30 m (98 ft) elevation model and Overture’s buildings and roads. 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,830 footprints pass the bounding box, 83,220 fall within 3 km (1.9 mi) of the perimeter, and 10,765 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

Which building footprints sit below the burn scar’s lowest point?

15,898 of the 83,220 building footprints sit below the scar’s lowest point, 229.9 m (754.3 ft). RS_ZonalStats runs the raster vector join per building, so every footprint gets one elevation value of its own. That value becomes one more column beside the building’s other attributes.

-- scar, dem and inaoi (the 83,220 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;

15,898 of the 83,220 building footprints sit below that line. Footprints that straddle two rasters have their pixels pooled before averaging, since the rasters meet under them. All 371 of them stay on the same side of the line. A 30 m (98 ft) pixel is wider than many houses, but every footprint still got an elevation value from the query.

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

Why do roads need a different join than buildings?

Roads need a different join because length, not a single point, decides the answer. A building gets one average elevation, and that single value places it above or below the line. A 1 km (0.6 mi) road dips below the line for a short stretch and stays above it for the rest. An average across the whole road would place it on one side, and the low stretch would go uncounted. The query takes three steps instead. RS_MapAlgebra marks every elevation pixel below 229.9 m (754.3 ft). RS_Polygonize turns the marked pixels into polygons, and ST_Union_Aggr merges them into one shape of low ground. ST_Intersection then cuts each road at the polygon’s edge, and the query adds up the length inside it. WherobotsDB builds the polygon once and tests every road against that same shape.

-- aoi, dem and roads are views from the notebook's setup cell
WITH masked AS (
  SELECT RS_MapAlgebra(rast, 'D', 'out[0] = rast[0] < 229.93 ? 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;

The low ground covers 17.3 km² (6.7 sq mi) and holds 411 km (255 mi) of road and path within 3 km (1.9 mi) of the scar. Of that, 324 km (201 mi) is drivable and 35 km (22 mi) is motorway. Lengths follow each mapped segment, so both carriageways of every divided road are counted in full.

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

698 footprints sit inside a high-hazard basin, 7,718 inside or within 500 m (1,640 ft), and 16,766 within 1 km (0.6 mi). The map below plots the 7,718 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.

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.copernicus_dem.glo_30m d ON ST_Intersects(d.footprint, p.pt)
ORDER BY p.i;

The line climbs to 1,582 m (5,190 ft) on the ridge. Then it crosses 2.3 km (1.4 mi) of high-hazard basin and 4.3 km (2.7 mi) of scar, down to 252 m (827 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 Copernicus 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: produced using Copernicus WorldDEM-30 © DLR e.V. 2010-2014 and © Airbus Defence and Space GmbH 2014-2018 provided under COPERNICUS by the European Union and ESA; all rights reserved. Buildings and roads: Overture Maps Foundation. Fire perimeter: CAL FIRE FRAP, CC BY. Hazard basins: USGS Landslide Hazards Program, public domain.

Get Started with Wherobots

Try Now