Dutch Portolan Demo

# Primary Flood Defenses — Rijkswaterstaat / Netherlands

## What This Dataset Is

**238 LineString features** representing primary flood defense sections (primaire waterkeringen)
in the Netherlands — the dike sections that protect the country from flooding from the sea,
major rivers, and large lakes. These are regulated under the Water Act (Waterwet) and subject
to periodic safety assessments.

The Netherlands has one of the world's most sophisticated flood defense systems. These primary
defenses protect roughly 60% of the country that lies below sea level or is flood-prone. Each
section (traject) has legally prescribed safety norms that specify the maximum acceptable
probability of failure or flooding.

**Provider:** Rijkswaterstaat (Directorate-General for Public Works and Water Management)
**License:** Public domain (Dutch government open data)

## How to Access

The data is a GeoParquet file in **EPSG:28992** (Amersfoort / RD New). Use DuckDB with the
spatial extension.

```python
import duckdb

con = duckdb.connect()
con.execute("INSTALL spatial; LOAD spatial;")

URL = 'https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/waterkeringen/waterkeringen.parquet'

df = con.execute(f"""
    SELECT * FROM read_parquet('{URL}')
    LIMIT 5
""").df()
```

## Schema — Field Meanings

| Field | Type | Meaning |
|-------|------|---------|
| `geometry` | WKB LineString | Defense section geometry in **EPSG:28992**. |
| `TRAJECT_ID` | string | **Section identifier** (e.g., `"1-1"`, `"13-5"`). The primary key for each dike section. |
| `NORMTYP_O` | string | **Norm type**: `"Faalkans"` (failure probability) or `"Overstromingskans"` (flooding probability). |
| `NORM_SW` | float64 | Safety norm value — signaling level (signaleringswaarde). |
| `NORM_OG` | float64 | Safety norm value — lower limit (ondergrens). |
| `NORM_THB` | float64 | Safety norm value — currently applicable norm. |
| `NORM_NS` | float64 | Safety norm value — new norm (nieuwe signalering). Higher values = stricter norms. |
| `WET_JN` | string | Whether legally established (`Ja`/`Nee`). |
| `VERSIE` | string | Version of the norm data. |
| `FL_BEGIN` | string | Start of validity period (flood defense layer). |
| `FL_EINDE` | string | End of validity period (flood defense layer). |
| `FH_BEGIN` | string | Start of validity period (assessment norm). |
| `FH_EINDE` | string | End of validity period (assessment norm). |
| `Shape__Length` | float64 | **Section length in metres.** |

## Useful Query Patterns

### Total length of flood defenses

```sql
SELECT COUNT(*) AS sections,
       SUM(Shape__Length) / 1000 AS total_length_km
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/waterkeringen/waterkeringen.parquet')
```

### Group by norm type

```sql
SELECT NORMTYP_O, COUNT(*) AS sections,
       SUM(Shape__Length) / 1000 AS length_km
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/waterkeringen/waterkeringen.parquet')
GROUP BY NORMTYP_O
```

### Find sections with strictest norms

```sql
SELECT TRAJECT_ID, NORMTYP_O, NORM_SW, NORM_OG, NORM_THB, NORM_NS,
       Shape__Length / 1000 AS length_km
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/waterkeringen/waterkeringen.parquet')
ORDER BY NORM_NS DESC NULLS LAST
LIMIT 20
```

### Legally established vs not

```sql
SELECT WET_JN, COUNT(*) AS sections,
       SUM(Shape__Length) / 1000 AS length_km
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/waterkeringen/waterkeringen.parquet')
GROUP BY WET_JN
```

### List all sections sorted by length

```sql
SELECT TRAJECT_ID, NORMTYP_O, Shape__Length / 1000 AS length_km
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/waterkeringen/waterkeringen.parquet')
ORDER BY Shape__Length DESC
```

## Geometry Notes

- CRS is **EPSG:28992** (Amersfoort / RD New). Coordinates are in metres.
- All geometries are LineString — representing the line of the flood defense.
- Reproject to EPSG:4326 for web maps.
- `Shape__Length` gives the section length in metres.


## Visualization Styles

One Mapbox GL v8 style is available for interactive map visualization via the PMTiles file.

Style files are Mapbox GL v8 JSON with relative PMTiles source paths. They can be
used with MapLibre GL JS, OpenLayers (via ol-mapbox-style), or any Mapbox GL v8-compatible renderer.

- **`styles/default.json`** — Bold red lines showing primary flood defense routes, with width increasing at higher zoom levels. Shows the critical Dutch dike and flood defense infrastructure.

Style files are at: `https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/waterkeringen/styles/`
## Caveats

- **Norm values are probabilities** — higher numbers mean stricter requirements. `NORM_SW`
  through `NORM_NS` represent different assessment thresholds, not independent norms.
- `NORMTYP_O` distinguishes between two norm frameworks: "Faalkans" (probability of
  structural failure) and "Overstromingskans" (probability of actual flooding).
- Column names use uppercase and Dutch abbreviations (e.g., `NORMTYP_O`, `WET_JN`).
- `Shape__Length` uses double underscores (ArcGIS convention).
- Validity periods (`FL_BEGIN`, `FL_EINDE`, etc.) may contain various date formats.