# Airports

*Every airport, heliport and seaplane base, with codes and coordinates.*

OurAirports' worldwide list of airports, heliports, seaplane bases, balloonports and closed fields, with the ICAO, IATA, GPS and local codes, the country and region, the municipality and whether scheduled service exists. One row per airport. The community keeps it current; the export is daily.

## At a glance

| | |
|---|---|
| Table | `pubdata.transport.airports` |
| Category | Transport · Transport, Spatial |
| Rows | 86,231 |
| Size | 4.7 MB in 1 data file(s) |
| Iceberg format | v2 |
| Last refreshed | 2026-10-10T10:48:47Z |
| Source version | Sat, 10 Oct 2026 01:53:58 GMT |
| Refresh cadence | weekly (replace) |
| Coverage | worldwide, ~80,000 fields; current state (no history) |

## Origin

- **Source:** [OurAirports](https://ourairports.com/data/)
- **Licence:** Public domain (released without restrictions)
- **Attribution:** Data from OurAirports (ourairports.com), David Megginson and contributors, public domain.
- **Recipe:** `pubdata/recipes/airports.py` in the LakehouseBox repository; registry entry `pubdata/registry/airports.yaml`.

## Schema

| column | type | description |
|---|---|---|
| `airport_id` | int | OurAirports' stable numeric id |
| `ident` | string | the identifier OurAirports uses (ICAO where one exists) |
| `type` | string | small_airport, medium_airport, large_airport, heliport, seaplane_base, balloonport, closed |
| `name` | string | the airport's name |
| `latitude` | double | WGS84 degrees |
| `longitude` | double | WGS84 degrees |
| `elevation_ft` | int | elevation in feet where known |
| `continent` | string | AF, AN, AS, EU, NA, OC, SA |
| `country_code` | string | ISO 3166-1 alpha-2 (joins reference.countries) |
| `region_code` | string | ISO 3166-2 region, e.g. US-PA |
| `municipality` | string | the city the airport serves |
| `scheduled_service` | boolean | true when airlines fly scheduled service |
| `icao_code` | string | ICAO code where assigned |
| `iata_code` | string | IATA code where assigned |
| `gps_code` | string | GPS code |
| `local_code` | string | local (national) code |
| `wikipedia_url` | string | Wikipedia article |

**Join keys:** `country_code`, `iata_code`, `icao_code`.

## For agents

`iata_code` is null for most small fields; join on it only for commercial airports. `type = 'closed'` rows are kept
on purpose (historic references); filter them out for "where can I fly". Coordinates are the reference point, not
the terminal.

## Example SQL (DuckDB)

```sql
-- large airports with scheduled service per country
SELECT country_code, count(*) AS large_airports
FROM pubdata.transport.airports
WHERE type = 'large_airport' AND scheduled_service
GROUP BY 1 ORDER BY 2 DESC LIMIT 20;
-- the nearest airport to each of your locations (flat-earth approximation, fine under ~200 km)
SELECT l.location_id, a.iata_code, a.name,
       6371 * acos(cos(radians(l.lat)) * cos(radians(a.latitude)) * cos(radians(a.longitude) - radians(l.lon))
                   + sin(radians(l.lat)) * sin(radians(a.latitude))) AS km
FROM my.locations AS l, pubdata.transport.airports AS a
WHERE a.type IN ('medium_airport', 'large_airport')
QUALIFY row_number() OVER (PARTITION BY l.location_id ORDER BY km) = 1;
```

## How to read it

Without an account, over HTTPS (the bucket is public):

```sql
INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs;
-- anonymous: the bucket is public, requests go unsigned, through the public-data cache
CREATE OR REPLACE SECRET pubdata (TYPE S3, ENDPOINT 'lakehousebox-public-data.b-cdn.net', URL_STYLE 'path', REGION 'us-east-1');
CREATE OR REPLACE VIEW airports AS SELECT * FROM iceberg_scan('s3://commons--pubdata/transport/airports');
SELECT * FROM airports LIMIT 10;
```

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.airports LIMIT 10;
```

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