World seaports

lhbox duckdb --catalog commons/pubdata

SELECT country_code, country_name, count(*) AS total_ports,
       count(*) FILTER (WHERE harbor_size = 'Large') AS large_ports
FROM commons_pubdata.transport.seaports
GROUP BY 1, 2 ORDER BY large_ports DESC, total_ports DESC LIMIT 20;

INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs;
CREATE SECRET pubdata (TYPE S3, ENDPOINT 'lakehousebox-public-data.b-cdn.net', URL_STYLE 'path', REGION 'us-east-1');
ATTACH ':memory:' AS pubdata;
CREATE SCHEMA pubdata.transport;
CREATE VIEW pubdata.transport.seaports AS SELECT * FROM iceberg_scan('s3://commons--pubdata/transport/seaports');

SELECT country_code, country_name, count(*) AS total_ports,
       count(*) FILTER (WHERE harbor_size = 'Large') AS large_ports
FROM pubdata.transport.seaports
GROUP BY 1, 2 ORDER BY large_ports DESC, total_ports DESC LIMIT 20;

# World seaports

*Maritime ports and terminals worldwide with UN/LOCODEs, harbor depths and coordinates.*

The National Geospatial-Intelligence Agency's World Port Index (Publication 150): ~3,800 maritime ports and shipping terminals around the world with their World Port Index number, main port name, UN/LOCODE, ISO country code, water body, harbor size and type, shelter afforded, channel, cargo pier and oil terminal depths, maximum vessel length and draft, and WGS84 coordinates. One row per port.

## At a glance

| | |
|---|---|
| Table | `pubdata.transport.seaports` |
| Category | Transport · Transport, Spatial, Country |
| Rows | n/a |
| Size | n/a data file(s) |
| Iceberg format | v? |
| Last refreshed | not loaded yet |
| Source version | n/a |
| Refresh cadence | monthly (replace) |
| Coverage | worldwide, ~3,800 maritime ports and terminals; current NGA World Port Index edition |

## Origin

- **Source:** [National Geospatial-Intelligence Agency (NGA), Publication 150 World Port Index](https://msi.nga.mil/Publications/WPI)
- **Licence:** Public domain (US government work)
- **Attribution:** World Port Index (Pub 150), National Geospatial-Intelligence Agency (NGA), public domain.
- **Recipe:** `pubdata/recipes/seaports.py` in the LakehouseBox repository; registry entry `pubdata/registry/seaports.yaml`.

## Schema

| column | type | description |
|---|---|---|
| `port_number` | int | NGA World Port Index unique numeric identifier |
| `port_name` | string | main port name |
| `un_locode` | string | 5-character UN/LOCODE (e.g. NLRTM for Rotterdam, USLAX for Los Angeles) |
| `country_code` | string | ISO 3166-1 alpha-2 country code (joins reference.countries) |
| `country_name` | string | country or territory name as listed in Pub 150 |
| `region_name` | string | NGA hydrographic region name |
| `water_body` | string | ocean, sea or gulf the port opens onto |
| `harbor_size` | string | Large, Medium, Small or Very Small |
| `harbor_type` | string | Coastal (Natural), Coastal (Breakwater), River (Natural), River (Basin), Open Roadstead, etc. |
| `shelter_afforded` | string | Excellent, Good, Fair, Poor or None |
| `channel_depth_m` | double | controlling channel depth, metres |
| `cargo_pier_depth_m` | double | cargo pier depth, metres |
| `oil_terminal_depth_m` | double | oil terminal depth, metres |
| `max_vessel_length_m` | double | maximum vessel length, metres |
| `max_vessel_draft_m` | double | maximum vessel draft, metres |
| `latitude` | double | WGS84 latitude in decimal degrees |
| `longitude` | double | WGS84 longitude in decimal degrees |

**Join keys:** `country_code`, `un_locode`.

## For agents

`un_locode` is normalized without spaces (e.g. `'NLRTM'`, `'ESBCN'`) and is null for small anchorages that have no
assigned UN/LOCODE. Depth and vessel dimension columns (`channel_depth_m`, `cargo_pier_depth_m`, `max_vessel_length_m`)
are null when unreported (0 in the raw NGA table is stored as null here, since 0 m means unknown, not zero depth).

## Example SQL (DuckDB)

```sql
-- large seaports by country
SELECT country_code, country_name, count(*) AS total_ports,
       count(*) FILTER (WHERE harbor_size = 'Large') AS large_ports
FROM pubdata.transport.seaports
GROUP BY 1, 2 ORDER BY large_ports DESC, total_ports DESC LIMIT 20;
-- deep-water Mediterranean ports able to berth >= 15 m cargo piers
SELECT port_name, un_locode, country_code, harbor_size, cargo_pier_depth_m, latitude, longitude
FROM pubdata.transport.seaports
WHERE water_body ILIKE '%Mediterranean%' AND cargo_pier_depth_m >= 15
ORDER BY cargo_pier_depth_m DESC;
```

## How to read it

With a LakehouseBox account:

```bash
# with a LakehouseBox account (any organisation): the public catalog by name, read-only
lhbox duckdb --catalog commons/pubdata     # opens DuckDB attached as `commons_pubdata`
SELECT * FROM commons_pubdata.transport.seaports LIMIT 10;
```

Public URL of the table's storage: `https://s3.lakehousebox.com/commons--pubdata/transport/seaports/`. This card: `https://lakehousebox.com/pubdata/cards/seaports.md`; the page: https://lakehousebox.com/public-data/seaports. Every dataset as a table: `commons_pubdata.meta.datasets`; as Markdown: https://lakehousebox.com/public-data.md.