Road inspection platform

A small production-style backend for road inspection results: the national road register (NVDB road network and guard rails), road photo positions (Statens vegvesen's Vegbilder) and the predictions of computer-vision models live together in PostGIS. A FastAPI service takes uploads from inference jobs, compares them with the register in SQL and serves GeoJSON and vector tiles to a TypeScript/MapLibre map. Docker Compose runs it locally; Terraform describes the AWS deployment (ECS Fargate, RDS PostgreSQL, S3 + CloudFront), and GitHub Actions tests, builds and deploys it.

It is the serving layer for the computer-vision projects in this portfolio: a model labels the photos on a GPU cluster node, the job uploads its results through the API, and the map shows where the photos and the register disagree.

_{What happens on upload: as an inference job posts its results, every photo is compared with the register on both sides of the road, and runs of disagreeing photos become stretches to inspect.}

flowchart LR
    N[NVDB API Les v4<br/>road network, rekkverk] -->|insp-import-nvdb| DB[(PostGIS<br/>EPSG:25833)]
    V[Vegbilder WFS<br/>photo positions] -->|insp-import-images| DB
    J[inference job<br/>GPU cluster / any GPU] -->|POST /v1/runs/id/detections| API
    API[FastAPI<br/>uvicorn] <--> DB
    API -->|vector tiles, GeoJSON| W[MapLibre + TypeScript]
    subgraph AWS
        CF[CloudFront] --> S3[S3: web build]
        CF -->|/api/*| ALB --> ECS[ECS Fargate: API] --> RDS[(RDS PostgreSQL + PostGIS)]
    end

Loading

What it does

• Imports open road data. insp-import-nvdb pages through NVDB API Les v4 (road network segments and any road object type, e.g. 5 = rekkverk) and insp-import-images reads photo positions from the Vegbilder WFS or a GeoPackage. Everything is stored in EUREF89 UTM 33 (EPSG:25833), so lengths and distances are metres. E6 through Trondheim (102 km of road segments, 377 guard rails, 8009 photos from 2024) imports in about ten seconds.
• Linear referencing. Every photo and every detection is snapped to the nearest NVDB road link within 30 m on insert, with its position along the link, in one SQL statement: the rows are streamed into a temporary table with COPY, then inserted with a k-nearest-neighbour lateral join on the GiST index.
• What should each photo show? A SQL function builds the area a forward-looking camera sees on each side of the road from the photo's position and heading (4-25 m ahead, 0.8-10 m to the side, the rule of guardrail-inspector's weak labels). A materialised view measures how many metres of registered guard rail fall inside it, for every photo.
• Where do model and register disagree? image_comparison puts a run's per-photo predictions next to that expectation; deviation_stretches groups consecutive photos with the same disagreement into lines (a gaps-and-islands query with window functions). GET /v1/runs/{id}/agreement returns the confusion matrix, /deviations the stretches.
• Idempotent uploads. Each prediction carries a key chosen by the job; uploading the same batch again updates rows instead of duplicating them, so an inference job that is killed at its time limit and restarted simply sends its results again.
• Vector tiles straight from the database. /v1/tiles/{layer}/{z}/{x}/{y}.pbf builds Mapbox vector tiles with ST_AsMVT; layers have a minimum zoom so a tile never carries a whole region of photos.

Results: a segmentation model against the register

The demo run uses the YOLO26s-sem model from road-scene-segmentation, fine-tuned on KITTI with guard rail as a 20th class, on 1193 photos of the E6 south of Trondheim (every third photo of one survey day, 2024-05-06). scripts/guardrail_sides.py turns its masks into a left and a right score per photo (share of guard-rail pixels in the lower part of each image half; a demonstration rule, not a calibrated classifier) and uploads them; the API compares them with the register.

Threshold	Sides compared	Agree	Precision vs. NVDB	Recall vs. NVDB
0.3	2371	2081 (88 %)	91 %	93 %
0.5	2371	2037 (86 %)	91 %	89 %
0.7	2371	1977 (83 %)	92 %	85 %

Fifteen photo sides with 1-5 m of rail in view are left out as borderline. Which rails does the model miss? The register says what kind of rail stands there, so the recall can be split by NVDB's Rekkverkstype (threshold 0.5, the longest registered rail in view):

Registered rail type	Sides	Seen by the model
Steel tube, round steel posts	1345	95 %
Steel rail, steel posts	151	89 %
Bridge railing, steel with guide rail	60	92 %
Steel tube or rail, sigma posts	64	73 %
Concrete, New Jersey profile	89	31 %
Concrete, prefabricated	16	13 %

The model learned the class from 160 KITTI images of German roads. Concrete barriers, common in the E6 median, are mostly not recognised: they make up 75 of the 183 "not seen" sides. That is a finding about the model, found by joining its output with the register's attributes in one query (/v1/runs/{id}/agreement/by-property).

There are 36 stretches of three or more disagreeing photos; the photos of the two longest show why they disagree. The longest "not seen" stretch (13 photos, left) runs along a concrete median barrier. The longest "unregistered" one (19 photos, right) is on a bridge: the photos show the bridge railing, and NVDB has it too (Brurekkverk), 12-16 m from the camera. The survey vehicle drove in the leftmost of three lanes, so the rail lies outside the 10 m side window of the comparison. Here the rule is wrong, not the register; NVDB's lane data (feltoversikt) would let the window follow the lane the photo was taken from.

Quickstart

With Docker:

docker compose up --build                                   # PostGIS, migrations, API :8000, web :8080
docker compose run --rm api insp-import-nvdb --kommune 5001 --road EV6
docker compose run --rm api insp-import-images --wfs --year 2024 --road EV6 \
    --bbox 264000 7029900 269300 7036300

Without Docker (PostgreSQL 17 with PostGIS, e.g. brew install postgresql@17 postgis):

pip install -e ".[dev]"
export INSPECTION_DATABASE_URL=postgresql://postgres@localhost:5432/inspection
insp-migrate
insp-import-nvdb --kommune 5001 --road EV6
insp-import-images --wfs --year 2024 --road EV6 --bbox 264000 7029900 269300 7036300
uvicorn inspection.api.app:app --port 8000           # OpenAPI docs on http://localhost:8000/docs
cd web && npm ci && npm run dev                       # http://localhost:5173, /api proxied to :8000

An inference job uploads its results with insp-import-predictions (or any HTTP client):

insp-import-predictions --api http://localhost:8000 --run e6-2024-05-06 --model yolo26s-sem+kitti20 predictions.csv

API

Method	Path
GET	/health, /ready	liveness; readiness checks the database and reports the schema version
GET	/v1/stats	counts of road links, km, assets, photos, runs, detections
GET, POST	/v1/runs	list runs; create or update one by name
POST	/v1/runs/{id}/detections	upload up to 10 000 predictions (photo id or lon/lat), idempotent by key
POST	/v1/images	upload photo positions (lon/lat, heading, time, URL)
POST	/v1/refresh	recompute the register's expectation per photo after imports
GET	/v1/images, /v1/images/{id}, /v1/detections	GeoJSON, filtered by bbox, time, run, label, score
GET	/v1/runs/{id}/agreement	confusion matrix of the run against the register, per photo side
GET	/v1/runs/{id}/agreement/by-property	recall split by an NVDB attribute of the registered rail
GET	/v1/runs/{id}/deviations	stretches of at least 3 photos where they disagree, as lines
GET	/v1/tiles/{roads,assets,images,detections}/{z}/{x}/{y}.pbf	vector tiles

Write endpoints require an X-API-Key header when INSPECTION_API_KEY is set. Settings are environment variables with the INSPECTION_ prefix (src/inspection/config.py).

Deployment on AWS

infra/terraform describes the whole stack in eu-north-1 (Stockholm):

• Network: a VPC over two availability zones; the load balancer and one NAT gateway in the public subnets, API tasks and the database in the private ones. The load balancer accepts only CloudFront's address ranges (AWS managed prefix list).
• Database: RDS PostgreSQL 17 (PostGIS is created by the first migration), encrypted, TLS enforced, 7 days of backups. The master password is generated and stored by RDS in Secrets Manager and injected into the tasks by ECS: it is never in Terraform state, in the repository or in an environment file.
• API: ECS Fargate on ARM64 (Graviton) behind an Application Load Balancer, health check on /ready, CPU target-tracking autoscaling (1-4 tasks), deployment circuit breaker with rollback, logs in CloudWatch. Migrations run as a one-off task of the same image before each rollout.
• Front end: a private S3 bucket behind CloudFront with origin access control. CloudFront also routes /api/* to the load balancer (a CloudFront Function strips the prefix), so the map and the API share one origin; vector tiles are cached at the edge for 60 s.
• CI/CD: ci.yml runs ruff and pytest against a PostGIS service container, type-checks, tests and builds the front end, checks terraform fmt and validate, and builds both images. deploy.yml runs on a version tag: GitHub's OIDC token assumes a deploy role limited to this stack (no AWS keys in the repository), pushes the image to ECR, runs the migrations, rolls the service and publishes the front end.

cd infra/terraform
cp example.tfvars terraform.tfvars                    # GitHub repository, API key
terraform init -backend-config="bucket=<state-bucket>" -backend-config="region=eu-north-1"
terraform apply
# then store the outputs as GitHub variables (ECR_REPOSITORY, ECS_CLUSTER, ...) and
# deploy_role_arn as the AWS_DEPLOY_ROLE secret; push a tag v0.1.0 to deploy

The configuration passes validate (checked locally with OpenTofu 1.13; CI runs Terraform's). It has not been applied to an AWS account as part of this project.

Tests

pytest -q                                                       # unit tests, no database needed
INSPECTION_TEST_DATABASE_URL=postgresql://postgres@localhost:5432/postgres pytest -q   # + PostGIS
cd web && npm test

The integration tests create a throw-away database, apply the migrations and build a synthetic scene: a straight road, a guard rail on its left from 210 m to 610 m, photos every 20 m and a model that reports the rail from 100 m to 700 m. They check the snapping and the offset along the road, the camera view box, the metres of rail each photo should see, the confusion matrix (21 sides agree, 10 "unregistered"), the two disagreement stretches, idempotent uploads, validation errors and vector tiles.

Project layout

src/inspection/
  migrations/         0001 schema, snapping and view-box functions; 0002 expectation view, comparison, stretches
  migrate.py          applies the SQL files in order, each once (insp-migrate)
  store.py            bulk upserts: COPY into a temporary table, one INSERT ... SELECT with snapping
  api/                FastAPI app, routes, pydantic schemas, vector tile queries
  ingest/             NVDB API client, Vegbilder WFS / GeoPackage reader, prediction uploader
web/                  TypeScript + MapLibre GL + Vite; vitest for the pure helpers
docker/               API image (uv, non-root), web image (nginx proxying /api), nginx.conf
infra/terraform/      VPC, RDS, ECR, ECS Fargate + ALB, S3 + CloudFront, GitHub OIDC deploy role
.github/workflows/    ci.yml, deploy.yml
scripts/              guardrail_sides.py: the demo inference job used for the results above

Design notes

• SQL first. The interesting work is spatial: snapping, view boxes, intersections, runs of consecutive photos. Those are a few lines of PostGIS each and use the indexes; an ORM would hide them. Queries are parameterised; table and column names never come from requests.
• Store in the data's own projection. NVDB and Vegbilder publish EPSG:25833. Distances for snapping and lengths in view are then plain metres; transformation to WGS84 or Web Mercator happens only on output. NVDB's EPSG:4326 output writes latitude first, which is a classic source of swapped coordinates, so the importer never asks for it.
• PostgreSQL 17 and materialised views. REFRESH MATERIALIZED VIEW runs with a safe search_path (only pg_catalog); the SQL functions that call PostGIS pin their own path with SET search_path FROM CURRENT.
• Signed photo URLs expire. Vegbilder URLs are valid for some hours. The platform stores them and refreshes them on re-import; a production system would proxy or cache the images.
• Not included: authentication beyond an API key for writes, multi-tenant data, a queue between inference jobs and the API (uploads are synchronous batches of up to 10 000 rows).

Data and licences

NVDB and Vegbilder: Statens vegvesen, Norwegian Licence for Open Government Data (NLOD 2.0). Base map: © Kartverket (CC BY 4.0). Photos are not stored or redistributed; the map shows them from Statens vegvesen's servers.