SCE Deployment Basemap · pipeline
Open the map →
How it was built

Nine stages, one fact table, one invariant.

Every number on this site is a GROUP BY over a single table of 4,808,866 estimated service connections. The pipeline below builds that table from thirty dated sources, and asserts on every run that meters summed by block equal meters summed by section equal the grand total.

The flow

Open the flow diagram 1040 px wide, and it scrolls sideways. The stage table below carries the same nine stages in a single column — on a phone, read that.
Harvest — 30 sources, each dated and cited 15 county assessors 6,478,429 parcels countywide + Riverside condos, a separate layer SCE DRPEP 524,522 ICA sections · 4,304 circuits 735 substations · service territory Hazard & equity CPUC HFTD 5,335 · SB 535 DAC 2,310 CAL FIRE FHSZ 121,805 Census & roads 270,639 blocks · 1,611 places Overture 1,459,162 road segments Stage 1 — the boundary everything is measured inside territory.py Carve the municipal utilities out Opt-in erase list with a recorded reason per utility — never a blanket difference. 135,380 → 131,456 km² net. Metropolitan Water District is the largest overlap and must NOT be erased: wholesale water, no meters. Stage 2 — parcels, then geography clip.py County → net territory 4,133,590 parcels in territory — 63.8% of countywide allocate.py Parcel → block, place, line section Geometry only. Split from the units model on purpose. Blocks tessellate, so a parcel may split across them. Line sections do not — a meter has one true section. Stage 3 — how many meters a parcel implies crosswalk.py Seeds config/crosswalk.csv from the observed use-code vocabularies. Hand edits win over a rebuild. calibrate.py → units.py Fit units per code, then classify Non-negative least squares against decennial HU100, using only blocks lying wholly inside net territory. Validated on LA, where the answer is published and the fit never sees it: fitted 0.90 vs published 0.96 for single-family. It underestimates small multifamily, and says so. Stage 4 — the fact table everything else is a GROUP BY over service_points.py core.service_point — 4,808,866 rows One row per metered service connection, carrying block, place, section, circuit, substation, class and method. Invariant, asserted By block = by section = by county = grand total. The build exits non-zero if it ever stops holding. Stage 5 — enrich, then score roads.py Overture queried in place on S3 — never a bulk download. 171,334 km of drivable road, capped at 3 km. index.py The difficulty index — 272,354 sections Density, access, HFTD, multifamily, vintage, DAC share. Weights live in weights.yml, never in code. A missing component has its weight redistributed — scoring it zero would rank it easy. Stage 6 — the cube the browser re-sums cube.py → geo_levels.py marts.cost_cube — 8 levels × 6 install classes Block, block group, tract, county, place, section, circuit, substation. All from ONE pass, so they cannot disagree. Census levels are a string slice of the 15-character block GEOID, not three spatial joins. Truncation cannot disagree with itself. Stage 7 — ship export.py GeoParquet, CSV, OpenFileGDB, catalog.json and a CITATION.md generated from the manifests. tiles.py PMTiles over HTTP range requests. No tile server, and no database behind the map. cost.html + model.js The model is written once, as an expression tree. MapLibre paints it; the totals panel evaluates it. MCP + catalog An agent reads the catalogue, queries the Parquet in place and builds its own exports. The two things that make it checkable Every estimate carries a method measured / inferred / modelled, never averaged into one number. The count and the location are separate claims: locations are modelled everywhere, on every row. Every source carries a date Each staging row keeps its source_url and retrieved_at, and the register is generated from the harvest manifests — so a source that was used but not cited cannot exist.

Stage by stage

ModuleDoesKey output
extract.py Paginated ArcGIS REST harvest, then assemble. Three services return error bodies under HTTP 200, so it checks the payload rather than the status code; TIGERweb needs OBJECTID-window paging because it lies about its page size. 30 dated source layers
territory.py Erases municipal utilities from SCE's own polygon, opt-in per utility with a recorded reason. 131,456 km² net
clip.py County parcels to net territory; builds parcel_uid and gates on its uniqueness. Assessor numbers are not primary keys — ~42,000 across seven counties are duplicated. 4,133,590 parcels
allocate.py Parcel to block, place and line section, on geometry alone. Split from the units model so a crosswalk change re-runs a cheap join, not a 4M-parcel spatial one. allocation per parcel
crosswalk.py Seeds the use-code crosswalk from observed vocabularies; carries forward rows it cannot observe, after a rebuild once silently zeroed 795,000 meters. config/crosswalk.csv
calibrate.py Non-negative least squares of units-per-code against decennial HU100, then rakes to the control margin. Agreement on the total is true by construction and must not be read as accuracy — the per-block spread is the honest measure. fitted coefficients
units.py Class, dwelling units and meters per parcel. Rounding is stochastic, keyed on a hash of the parcel id: rounding independently discarded 13% of one county's services. units per parcel
service_points.py Fans out to one row per metered service and asserts the invariant globally. 4,808,866 service points
roads.py Overture transportation queried in place on S3 with bbox pushdown. Only drivable classes: unfiltered, footways outnumbered residential streets three to one. 171,334 route km
index.py The composite difficulty index and its rollups. Components are percentile ranks, not min-max — one section carries 4,488 meters and another 252 km of road. 272,354 scored sections
cube.py Meters per geography × install class, every level from one pass over the fact table. 8 cube levels
geo_levels.py Attaches geometry to each cube level. Tracts and block groups are dissolved from blocks rather than harvested, so the boundaries cannot disagree at the seams. tiling inputs
export.py GeoParquet, CSV, OpenFileGDB, catalogue and citation. Reads the geodatabase back and refuses to finish if a layer came up short. the distribution
tiles.py PMTiles archives with per-layer zoom bands. A generalised layer drawn outside its band looks like corrupted data. 3 tile archives

The rules it is built on

Parcels are not meters, and the error runs both ways. Clipping to territory subtracts; multifamily adds back; vacant and public land subtracts again. So every parcel carries a dwelling-unit and use-class model, and every estimate is labelled.
Denormalise only along genuine nesting. Section → circuit → substation and block → block group → tract are real hierarchies, so caching a parent on a row cannot create a contradiction. The grid and census axes do not nest into each other, and neither is ever derived from the other.
A confidence flag has to be defined at the grain of the decision it informs. Flagging an ambiguous line section by distance ratio alone marked 70% of Los Angeles — in a dense network the two nearest sections are near-equidistant by construction. Flagging only where the ambiguity survives the rollup to a different circuit gives 7.5%.
Materialise polygon counts with a LEFT JOIN. Zero-meter blocks and vacant parcels must render as zero, not vanish into holes. A hole reads as missing data and invites the reader to distrust everything else on the map.