How to score every building in a state for catastrophe risk: an exploration project

Part 1 of a series: What’s possible?

I didn’t start this project to build a real risk model, not one that can be used by an insurer tomorrow. But it is a workflow that an insurer can put into practice to create their own risk scores with Wherobots and their own expertise. The idea was simple: what can be done in a day with Wherobots MCP for Claude and the VSCode plugin? It turns out quite a lot, and the barrier to entry is a Claude (or other coding agent) Account and VS Code running on a laptop with access to a Wherobots pro tier connected.

Total costs across tokens and data processing was about $300. This example looks at the Insurance industry and scoring risk on a property and building level. I’ll be following up with additional examples on Drone Delivery hub / nest site selection as well as communication services network connectivity in the coming weeks.

Lets start with the concept.

What does an insurer need to understand when evaluating the risk of a location?

Insurance works on a basic idea: what is the risk at a given location, and how should it be priced? For a property and casualty carrier that question repeats across every building and property asset in its portfolio. The data to answer it, in many cases, already exists spread across wildfire rasters, hail radar, flood layers, parcels, and road networks. Getting it into one scored, queryable, mappable form, updated on a recurring schedule is usually the hard part, because handling these data types is unsupported and often impossible in other compute environments.

This post walks that process end to end: every Overture building in Colorado, 2,771,126 of them, joined against the Parcels they sit upon from Regrid, and scored across five perils, from a statewide view down to a single rooftop. The data processing engine used is WherobotsDB on Wherobots Cloud in AWS. Our team at Wherobots maintains Apache Sedona, and WherobotsDB is 100% code compatible the OSS, but fully managed with more spatial functions and capabilities, grater performance, and enterprise support options. Three capabilities carry this data pipeline: spatial SQL over raster and vector datasets combined, a spatial operation that prunes on reads time, and PMTiles that WherobotsDB can deliver to enable a snappy browser experience on MapLibre.

The finished map is live on the Wherobots Website here. You can play with it inline below:

The Colorado catastrophe-risk explorer, built with WherobotsDB, Claude Opus 5 & VSCode via agentic app development.

The data fed into the application layer

Seven sources feed the app and the data pipeline:

Layer	Source
Building footprints	Overture Maps
Wildfire hazard	USFS Wildfire Risk to Communities (30 m raster)
Hail	NOAA radar, 403M observations since 2016
Flood	FEMA National Flood Hazard Layer
Tornado / wind	FEMA National Risk Index (tract)
Parcels, land use	Regrid
Fire-station access	Overture places (road-network distance computed in the pipeline)

Two structural decisions:

• A count is a floor. No measurement means no record, so a building with no hail observation is not a building at zero hail risk. Every count reads as “at least“.
• The ranking: by building count, not by dollars. Assessor value coverage runs near 63% and is county dependent. El Paso, the largest county at 290,630 buildings, with $0 reported by the county. A dollar ranking would identify who reports property values, not where actual risk value is identified. A P&C insurer would have a clear understanding of building / parcel / asset value, so this diverts from what would be the likely standard they would want. These values could easily be added to this data pipeline, however.

The data pipeline: a medallion architecture for catastrophe risk scoring

The pipeline is a medallion architecture, and every layer is an Iceberg catalog table you can query on its own.

Bronze, raw as it lands, one table per source:

Table	What lands in it
overture_buildings	Overture footprints (2.77M)
noaa_hail	NOAA radar hail, 403M observations since 2016
usfs_wildfire	USFS Wildfire Risk to Communities, 30 m raster (COG)
fema_nfhl	FEMA National Flood Hazard Layer (flood zones)
fema_nri	FEMA National Risk Index, tract (tornado, wind)
regrid_parcels	Regrid parcels: reported parcel value, land use
overture_places	Overture fire-station locations

Silver, conformed: clipped to the Colorado boundary, coordinate systems reconciled, geometries validated: buildings, hail_grid, flood_zones, parcels, fire_stations, nri_tracts.

Gold, scored and tile-ready: building_perils (five peril scores per building), hex_rollup (H3 exposure and composite), parcels_scored.

Update frequency. Hail accumulation and Regrid parcels refresh monthly, so a monthly run keeps the map current. USFS wildfire and the FEMA National Risk Index refresh annually. The pipeline is defined with the Wherobots Python SDK and scheduled with Apache Airflow, one run per medallion stage.

Reading and clipping at scale with spatial predicate pushdown

The buildings layer is continental but the analysis is for Colorado. A spatial predicate filter pushdown on the read keeps makes it easy and cheap to run that state only:

-- Silver: every Colorado building footprint, clipped with a pushed-down spatial predicate
CREATE TABLE org_catalog.co_pc_risk_silver.buildings AS
SELECT b.id, b.geometry
FROM wherobots_open_data.overture_maps_foundation.buildings_building b
JOIN org_catalog.co_pc_risk_bronze.co_boundary aoi
  ON ST_Intersects(b.geometry, aoi.geometry);

WherobotsDB pushes ST_Intersects down to the GeoParquet files, reads each file’s bounding-box metadata, and skips the files that fall outside Colorado. Because WherobotsDB can easily index a dataset by proximity using say a Hilbert Curve index, this makes this type of operation even easier.

Fusing raster and vector in one query

This is a core WherobotsDB capability that this and many other use cases leverage. Wildfire hazard is a 30 m raster. Hail is millions of radar points. Flood is polygons. Buildings are polygons. One set-based query samples and joins all of them, per building:

-- Gold: score each building against the wildfire raster and the hazard vectors.
WITH wf AS (
  SELECT b.id, MAX(RS_ZonalStats(w.rast, b.geometry, 'max')) AS wildfire_score
  FROM org_catalog.co_pc_risk_silver.buildings     b
  JOIN org_catalog.co_pc_risk_silver.wildfire       w ON RS_Intersects(w.rast, b.geometry)
  GROUP BY b.id
),
hail AS (
  SELECT b.id, COUNT(*)  AS hail_events,
         MAX(h.size_in)  AS max_hail_in
  FROM org_catalog.co_pc_risk_silver.buildings     b
  LEFT JOIN org_catalog.co_pc_risk_silver.hail_obs h ON ST_Intersects(b.geometry, h.geometry)
  GROUP BY b.id
),
flood AS (
  SELECT b.id, ANY_VALUE(f.zone) AS flood_zone
  FROM org_catalog.co_pc_risk_silver.buildings       b
  LEFT JOIN org_catalog.co_pc_risk_silver.flood_zones f ON ST_Intersects(b.geometry, f.geometry)
  GROUP BY b.id
)
SELECT
  b.id,
  b.geometry,
  wf.wildfire_score,
  hail.hail_events,
  hail.max_hail_in,
  flood.flood_zone
FROM org_catalog.co_pc_risk_silver.buildings b
LEFT JOIN wf    ON b.id = wf.id
LEFT JOIN hail  ON b.id = hail.id
LEFT JOIN flood ON b.id = flood.id;

RS_ZonalStats reads the raster values under each footprint and returns key statistics. Each peril is computed in its own CTE so the joins stay one-to-many and the counts are exact. WherobotsDB executes RS_Intersects and ST_Intersects as spatial range joins, so the raster-to-building and point-to-building matches avoid a cross product. The run scores the full state in just a few minutes on one WherobotsDB medium runtime.

Five catastrophe risk scores per building

The fused query gives each building a raw hazard value per peril. Scoring turns those into five small integers on one row, so a building is one comparable object across every hazard. Each peril is scored on its own scale and the five sum to a 0-to-7 composite:

Peril	Range	What sets the top score
Wildfire	0-2	USFS hazard sampled under the footprint, banded high / moderate / low
Hail	0-2	Largest radar-detected hail size over the building: 2 at 2 inches or greater
Flood	0-1	Inside a FEMA Special Flood Hazard Area
Wind / tornado	0-1	FEMA National Risk Index rating for the tract
Access	0-1	Greater than five road miles of the nearest fire station

The perils are scored individually. A single blended number would reads like a hazard map while hail dominates the sum, because hail is Colorado’s most costly regular peril. Keeping five columns lets the map user and the copilot switch to one peril at a time and understand them specifically.

The assignment is set-based, one CASE per peril over the fused hazard values:

-- Gold: turn raw hazard values into five 0-based peril scores per building
SELECT
  id,
  geometry,
  -- wildfire: USFS hazard under the footprint, banded 0 / 1 / 2
  CASE WHEN wildfire_hazard >= hi_break THEN 2
       WHEN wildfire_hazard >= lo_break THEN 1 ELSE 0 END               AS wf_score,
  -- hail: largest radar-detected hail size over the building, in inches
  CASE WHEN max_hail_in >= 2.0 THEN 2
       WHEN max_hail_in >= 1.0 THEN 1 ELSE 0 END                        AS hail_score,
  -- flood: inside a FEMA Special Flood Hazard Area
  CASE WHEN in_sfha THEN 1 ELSE 0 END                                   AS flood_score,
  -- wind / tornado: FEMA National Risk Index rating for the tract
  CASE WHEN nri_wind_elevated THEN 1 ELSE 0 END                         AS wind_score,
  -- access: beyond five road miles (8047 m) of the nearest fire station
  CASE WHEN road_m IS NULL OR road_m > 8047 THEN 1 ELSE 0 END           AS access_score,
  -- composite: the five summed, domain 0-7
  ( CASE WHEN wildfire_hazard >= hi_break THEN 2
         WHEN wildfire_hazard >= lo_break THEN 1 ELSE 0 END
  + CASE WHEN max_hail_in >= 2.0 THEN 2
         WHEN max_hail_in >= 1.0 THEN 1 ELSE 0 END
  + CASE WHEN in_sfha THEN 1 ELSE 0 END
  + CASE WHEN nri_wind_elevated THEN 1 ELSE 0 END
  + CASE WHEN road_m IS NULL OR road_m > 8047 THEN 1 ELSE 0 END )       AS composite
FROM org_catalog.co_pc_risk_gold.building_hazards;

Two of these bands are exposed in a way worth highlighting.

Wildfire and hail are measured under each footprint; wind and flood are inherited. wf_score reads the USFS raster at the building, and hail_score reads the radar accumulation over it. wind_score comes from a FEMA National Risk Index rating at census-tract resolution, and flood_score from a FEMA zone polygon. A tract or a zone is far coarser than a rooftop, so those two scores are derived from an area where the detections exist, and that coarser grain is labeled in the schema, not intended to represent as a per-structure reading.

On access, a NULL distance means furthest, not missing. The access score flags every building beyond five road miles of a fire station. The nearest-station search runs to a 25 km radius; a building with no station inside that radius has a NULL distance and is the most remote in the state, so the rule scores NULL as 1. Writing road_m > 8047 alone drops exactly the worst-access cohort. That is why the score, not the raw distance, drives the map.

Run over all 2.77M buildings, the top wildfire band holds 307,679 buildings and the 2 in-and-up hail band holds 790,889. Both counts are floors: a building with no observation for a peril scores 0 for it, and reads as at least that exposed.

The accumulation view

The per-building table has fine grained details. A reinsurer often is concerned with concentration risk, and it’s easier to visualize when aggregated, so its helpful to roll the same scores up to an H3 grid:

-- Gold: roll per-building scores up to H3 for the accumulation view
SELECT
  ST_H3CellIDs(geometry, 7, false)[1] AS h3,
  COUNT(*)                            AS buildings,
  SUM(wildfire_score)                 AS wildfire_exposure,
  AVG(composite)                      AS mean_composite
FROM org_catalog.co_pc_risk_gold.building_perils
GROUP BY ST_H3CellIDs(geometry, 7, false)[1];

The same gold tables drive both the statewide hex view and the street-level building view. Nothing recomputes between zoom levels.

From a gold table to a map anyone can interact iwth

WherobotsDB builds the PMTiles as vector tiles and ships them to object storage, so a hand-written MapLibre GL JS page reads them by their S3 URL. No separate tiling tool, no tile server, no build step in the app.

# WherobotsDB builds and ships the PMTiles for the web app
from wherobots import WherobotsJob
WherobotsJob(script="s3://.../export_pmtiles.py", name="co-risk-export", runtime="small").submit()

The build-to-browser toolchain

The whole build was authored and shipped with one repeatable stack:

• Build and version: Claude Code (Opus 5) inside VS Code, with the pipeline and the app committed to GitHub.
• Compute and data: WherobotsDB on Wherobots Cloud runs the medallion pipeline; the Wherobots Python SDK defines each job and it is scheduled with Apache Airflow.
• Tiles and hosting: WherobotsDB builds the PMTiles and ships them to Amazon S3; Vercel serves the static MapLibre GL JS page from its CDN.
• Ask the map: the app includes a Claude copilot. Ask where is the worst wildfire exposure in El Paso County, and Claude switches the peril, recolors the map, and moves to show you the answer. It runs as a Vercel serverless function that calls the Anthropic API, with the analysis rules in its system prompt and the key held server-side.

What these catastrophe risk scores are, and what they are not

The five scores screen where to look. They are not a calibrated loss model, and nothing here is a determination of insurability. The bands, the floors, and the inherited-grain labels live in the pipeline and again in the copilot’s system prompt, so the map view and the copilot’s answers stay inside the same limits.

What the map surfaces

Hail is Colorado’s dominant peril, ahead of wildfire. In the top wildfire tier alone, 307,679 buildings screen as elevated: a starting list for where to focus. The access-to-services peril, powered by road-network distance to the nearest fire station, marks the insurability boundary, the cohort beyond a fire department’s reach. A P&C analyst can review all of it by toggling the map.

How to port this catastrophe risk scoring pipeline to any state or portfolio

The Colorado build is a template. Swap the boundary for your state or the whole country, swap the peril rasters and point clouds for the ones on your portfolio, and the pipeline holds: push the spatial predicate on the read, fuse raster and vector in one query, roll up to H3, let WherobotsDB build and ship the tiles down to the property parcel and building layer.

That is the point of an AI Context Engine for the Physical World: the physical-world data, prepared and scored at the scale a portfolio spans, in a form an analyst and an agent can both query.

Start on Wherobots Cloud, connect to the Wherobots Claude MCP, or open the Colorado risk explorer to see what I built, or check out the docs at docs.wherobots.com.