Dutch Portolan Demo

# National Road Network (NWB Wegen) — Rijkswaterstaat / Netherlands

## What This Dataset Is

The complete Dutch National Road Network (Nationaal Wegenbestand, NWB) — 1,626,321 road
segments covering every road in the Netherlands, from motorways (A-roads) to local
residential streets. Each record is a road segment (wegvak) with detailed attributes
including street name, place name, municipality, road number, road type, driving direction,
road manager, and kilometer markers.

The NWB is the authoritative road reference dataset for the Netherlands, maintained by
Rijkswaterstaat and updated monthly. It is used for traffic management, navigation, road
infrastructure planning, and spatial analysis across government and industry.

**Source:** https://service.pdok.nl/rws/nationaal-wegenbestand-wegen/atom/index.xml
**Provider:** Rijkswaterstaat (RWS, part of the Ministry of Infrastructure and Water Management)
**License:** CC0 (public domain)
**Update frequency:** Monthly

## How to Access

This is a GeoParquet file in EPSG:28992 (Amersfoort / RD New), the Dutch national
coordinate reference system. The file is 779 MB. Use DuckDB with the spatial extension
for efficient querying — DuckDB can stream the file via HTTP range requests without
downloading the entire 779 MB.

```python
import duckdb

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

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

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

**Important:** The geometry is in EPSG:28992 (metres), not WGS84. For lat/lon coordinates,
transform with `ST_Transform(ST_GeomFromWKB(geom), 'EPSG:28992', 'EPSG:4326')` in DuckDB.

## Schema — Field Meanings (Dutch to English)

| Field | Type | Dutch | English Meaning |
|-------|------|-------|-----------------|
| `wvk_id` | double | Wegvak ID | Road segment ID (unique identifier) |
| `wvk_begdat` | string | Wegvak begindatum | Segment start date |
| `jte_id_beg` | double | Junctie ID begin | Junction ID at start of segment |
| `jte_id_end` | double | Junctie ID eind | Junction ID at end of segment |
| `wegbehsrt` | string | Wegbeheerder soort | Road manager type: R=Rijk (national), P=Provincie, G=Gemeente (municipality), W=Waterschap (water board) |
| `wegbehcode` | string | Wegbeheerder code | Road manager code |
| `wegbehnaam` | string | Wegbeheerder naam | Road manager name (e.g., "Rijkswaterstaat", "Gemeente Amsterdam") |
| `wegnummer` | string | Wegnummer | Road number (e.g., A1, N50, A28) |
| `wegdeelltr` | string | Wegdeel letter | Road section letter |
| `begafstand` | int32 | Begin afstand | Begin distance (hectometers) |
| `endafstand` | int32 | Eind afstand | End distance (hectometers) |
| `beginkm` | double | Begin kilometer | Begin kilometer point |
| `eindkm` | double | Eind kilometer | End kilometer point |
| `pos_tv_wol` | string | Positie t.o.v. wegas | Position relative to road center line (L=left, R=right, M=middle) |
| `rijrichtng` | string | Rijrichting | Driving direction: H=heen (forward), T=terug (backward), B=both, O=onbekend (unknown) |
| `admrichtng` | string | Administratieve richting | Administrative direction |
| `wegtype` | string | Wegtype | Road type code |
| `wgtype_oms` | string | Wegtype omschrijving | Road type description (e.g., "autosnelweg", "lokale weg") |
| `routeltr` | string | Route letter | Route letter (A, N, etc.) |
| `routenr` | int16 | Route nummer | Route number (numeric part of road number) |
| `gme_id` | int32 | Gemeente ID | Municipality code — links to CBS gebiedsindelingen |
| `gme_naam` | string | Gemeente naam | Municipality name (e.g., "Amsterdam", "Utrecht") |
| `stt_naam` | string | Straatnaam | Street name |
| `wpsnaam` | string | Woonplaatsnaam | Place name (town/city name) |
| `stt_bron` | string | Straatnaam bron | Street name source |
| `wpsbron` | string | Woonplaatsnaam bron | Place name source |
| `gmebron` | string | Gemeente bron | Municipality data source |
| `hnrstrlnks` | string | Huisnummer straat links | House number street left side |
| `hnrstrrhts` | string | Huisnummer straat rechts | House number street right side |
| `e_hnr_lnks` | string | Eerste huisnummer links | First house number left |
| `e_hnr_rhts` | string | Eerste huisnummer rechts | First house number right |
| `l_hnr_lnks` | string | Laatste huisnummer links | Last house number left |
| `l_hnr_rhts` | string | Laatste huisnummer rechts | Last house number right |
| `beg_opafst` | int32 | Begin op afstand | Start address distance |
| `end_opafst` | int32 | Eind op afstand | End address distance |
| `st_lengthshape` | double | Lengte shape | Segment length in metres |
| `geom` | MultiLineString | Geometrie | Road segment geometry in **EPSG:28992** (WKB encoded) |

## Important Columns

The columns you'll use most often:

- **`stt_naam`** — street name (most queries start here)
- **`wpsnaam`** — place name (town/city)
- **`gme_id` / `gme_naam`** — municipality code and name
- **`wegnummer`** — road number (A1, N50, etc.) for highways and provincial roads
- **`wegtype` / `wgtype_oms`** — road type code and description
- **`wegbehsrt`** — road manager type (R/P/G/W) for filtering by governance level
- **`rijrichtng`** — driving direction
- **`st_lengthshape`** — segment length in metres
- **`routeltr` / `routenr`** — route letter and number (for highways)
- **`geom`** — road segment geometry

## Geometry Notes

- CRS is **EPSG:28992** (Amersfoort / RD New) — the standard Dutch national grid in metres.
  This is NOT WGS84. To get lat/lon, transform to EPSG:4326.
- Geometry column is named `geom` (not `geometry`)
- All geometries are MultiLineString
- Bounding box in WGS84: approximately [3.21, 50.73, 7.24, 53.58] (all of Netherlands)
- Coordinates are in metres (Dutch RD), not degrees

## Road Type Categories

The `wgtype_oms` field contains these road type descriptions:

| Dutch | English |
|-------|---------|
| autosnelweg | Motorway (A-road) |
| autoweg | Expressway |
| regionale weg | Regional road |
| lokale weg | Local road |
| straat | Street |
| overig | Other |

## Road Manager Types

The `wegbehsrt` field indicates who manages the road:

| Code | Dutch | English |
|------|-------|---------|
| R | Rijk | National government (Rijkswaterstaat) — motorways and national roads |
| P | Provincie | Province — provincial roads (N-roads) |
| G | Gemeente | Municipality — local roads and streets |
| W | Waterschap | Water board — roads on dikes and water infrastructure |

## Useful Query Patterns

### Find roads by street name

```sql
INSTALL spatial; LOAD spatial;

SELECT stt_naam, wpsnaam, gme_naam, wgtype_oms, st_lengthshape
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/nwb_wegen/nwb_wegen.parquet')
WHERE stt_naam = 'Kalverstraat'
```

### Find all roads in a municipality

```sql
SELECT stt_naam, wpsnaam, wgtype_oms, wegnummer,
       SUM(st_lengthshape) AS total_length_m
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/nwb_wegen/nwb_wegen.parquet')
WHERE gme_naam = 'Amsterdam'
GROUP BY stt_naam, wpsnaam, wgtype_oms, wegnummer
ORDER BY total_length_m DESC
LIMIT 20
```

### List all highway segments (A-roads / motorways)

```sql
SELECT wegnummer, stt_naam, gme_naam, beginkm, eindkm, st_lengthshape
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/nwb_wegen/nwb_wegen.parquet')
WHERE routeltr = 'A'
ORDER BY wegnummer, beginkm
LIMIT 50
```

### Total road length by road type

```sql
SELECT wgtype_oms,
       COUNT(*) AS segment_count,
       ROUND(SUM(st_lengthshape) / 1000, 1) AS total_km
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/nwb_wegen/nwb_wegen.parquet')
GROUP BY wgtype_oms
ORDER BY total_km DESC
```

### Total road length by road manager type

```sql
SELECT wegbehsrt,
       CASE wegbehsrt
           WHEN 'R' THEN 'Rijkswaterstaat (national)'
           WHEN 'P' THEN 'Provincie (provincial)'
           WHEN 'G' THEN 'Gemeente (municipal)'
           WHEN 'W' THEN 'Waterschap (water board)'
           ELSE 'Other'
       END AS manager_type,
       COUNT(*) AS segments,
       ROUND(SUM(st_lengthshape) / 1000, 1) AS total_km
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/nwb_wegen/nwb_wegen.parquet')
GROUP BY wegbehsrt
ORDER BY total_km DESC
```

### Find all roads with a specific road number (e.g., A1)

```sql
SELECT wegnummer, stt_naam, gme_naam, wgtype_oms,
       rijrichtng, beginkm, eindkm, st_lengthshape
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/nwb_wegen/nwb_wegen.parquet')
WHERE wegnummer = 'A001'
ORDER BY beginkm
```

Note: Road numbers may be zero-padded (e.g., 'A001' instead of 'A1'). Check with:
```sql
SELECT DISTINCT wegnummer
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/nwb_wegen/nwb_wegen.parquet')
WHERE wegnummer LIKE 'A%'
ORDER BY wegnummer
LIMIT 20
```

### Roads in a specific place (town/city)

```sql
SELECT stt_naam, wgtype_oms, wegnummer,
       ROUND(SUM(st_lengthshape), 1) AS length_m
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/nwb_wegen/nwb_wegen.parquet')
WHERE wpsnaam = 'Utrecht'
GROUP BY stt_naam, wgtype_oms, wegnummer
ORDER BY length_m DESC
LIMIT 20
```

### Spatial query — roads near a point (requires coordinate transform)

```sql
INSTALL spatial; LOAD spatial;

-- Find roads within 500 metres of Amsterdam Centraal Station
-- First convert WGS84 point to EPSG:28992
SELECT stt_naam, wpsnaam, wgtype_oms, st_lengthshape
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/nwb_wegen/nwb_wegen.parquet')
WHERE ST_Distance(
    ST_GeomFromWKB(geom),
    ST_Transform(ST_Point(4.9003, 52.3792), 'EPSG:4326', 'EPSG:28992')
) < 500
```

### Count roads per municipality

```sql
SELECT gme_naam, gme_id,
       COUNT(*) AS segment_count,
       ROUND(SUM(st_lengthshape) / 1000, 1) AS total_km
FROM read_parquet('https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/nwb_wegen/nwb_wegen.parquet')
WHERE gme_naam IS NOT NULL
GROUP BY gme_naam, gme_id
ORDER BY total_km DESC
LIMIT 20
```

### Load into GeoPandas

```python
import geopandas as gpd

# Select specific columns to reduce memory usage (full file is 779 MB)
gdf = gpd.read_parquet(
    'https://data.source.coop/cholmes/portolan-nl/rijkswaterstaat/nwb_wegen/nwb_wegen.parquet',
    columns=['wvk_id', 'stt_naam', 'wpsnaam', 'gme_naam', 'wegnummer',
             'wgtype_oms', 'st_lengthshape', 'geom']
)
# CRS will be EPSG:28992 — transform if needed:
# gdf = gdf.to_crs(epsg=4326)
```

## Related Datasets

- **CBS Gebiedsindelingen (bestuurlijke_gebieden):** Municipality boundaries with `gme_id`
  codes that match this dataset's `gme_id` field. Use for spatial joins or to get
  municipality polygons for the roads.
  URL: `https://data.source.coop/cholmes/portolan-nl/cbs/gebiedsindelingen/`

## Caveats

- **EPSG:28992 coordinates:** Geometry is in the Dutch national grid (metres), not WGS84.
  All spatial queries with lat/lon coordinates require a coordinate transform.
- **Geometry column is named `geom`**, not `geometry` — adjust queries accordingly.
- **Large file (779 MB):** DuckDB can stream it efficiently via HTTP range requests.
  For GeoPandas, consider selecting only the columns you need.
- **Road numbers may be zero-padded:** e.g., 'A001' instead of 'A1'. Always check the
  actual values with a DISTINCT query before filtering.
- **Monthly updates:** The NWB is updated monthly by Rijkswaterstaat. This copy is a
  point-in-time snapshot; check the source for the latest version.
- **MultiLineString geometry:** All geometries are MultiLineString, even for simple
  single-part line segments.
- **Street names may be null:** Not all road segments have a street name (stt_naam),
  especially motorway segments. Use `wegnummer` for highways.
- **st_lengthshape is in metres:** Since the CRS is EPSG:28992 (metric), the length
  field is already in metres — no conversion needed.