Following a quarter of global earthquakes, a humanitarian aviation desk must prepare a contact list for airfield status checks. An airfield near an epicentre could be useful or damaged; distance alone settles neither. The desk needs event–airfield pairs within a defensible initial search radius, with the original event ID so it can open the relevant USGS products.
Write one PostGIS query for the fixed 2025 Q1 USGS catalogue. Consider events with mag >= 6.0 and depth_km <= 70. Pair each with a civil mid-size or major airport within 250 km of its epicentre. Airport type is free text: include strings containing mid or major, except those beginning military.
The sandbox tables are earthquakes and airports, both EPSG:4326; their columns are below. Return one row per qualifying pair with exactly these columns: event_id, airport_id, airport_name, mag, distance_km, geom. geom is the airport point, for mapping; distance_km is the spheroidal event-to-airport distance rounded to one decimal. Sort by event_id, then distance_km, then airport_id. Use an index-aware ST_DWithin predicate on geography so the 250000 threshold is metres.
This is a contact shortlist, not an airport safety or shaking assessment; an operations lead must check ShakeMap and direct airfield reports before routing aircraft.
earthquakes — SRID 4326, GiST index on geom.
| column | type | meaning |
|---|---|---|
id | integer | row number the loader adds as the primary key; the file carries no id of its own |
event_id | text | USGS event identifier |
mag | double precision | Preferred magnitude |
depth_km | double precision | Hypocentral depth (km) |
time_utc | text | Event origin time in UTC |
place | text | USGS place description |
tsunami | boolean | USGS tsunami flag; not a hazard forecast |
alert | text | PAGER alert if assigned, otherwise unassigned |
geom | geometry(Geometry, 4326) | Point |
airports — SRID 4326, GiST index on geom.
| column | type | meaning |
|---|---|---|
id | integer | row number the loader adds as the primary key; the file carries no id of its own |
airport_id | text | AP- plus the Natural Earth id |
name | text | Airport name |
iata_code | text | Three-letter IATA code; null for a few |
gps_code | text | Four-letter ICAO code |
type | text | major | mid | small | military | spaceport, and combinations such as 'major and military' |
location | text | What the point marks: terminal | ramp | runway | parking | freight | approximate |
scalerank | integer | Natural Earth prominence, 2 (largest) to 9 |
geom | geometry(Geometry, 4326) | Point |
These are the input columns your query can read. The Data tab also shows field meanings and sample records.
earthquakesCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| event_id | text |
| mag | double precision |
| depth_km | double precision |
| time_utc | text |
| place | text |
| tsunami | boolean |
| alert | text |
| geom | geometry(Geometry,4326) |
airportsCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| airport_id | text |
| name | text |
| iata_code | text |
| gps_code | text |
| type | text |
| location | text |
| scalerank | integer |
| geom | geometry(Geometry,4326) |
These are where the teaching is. Read them twice.
USGS ShakeMap supports emergency response after significant events; nearby facilities require direct condition checks (https://earthquake.usgs.gov/data/shakemap/).
6 scored, 0 informational
Carries event, airfield, distance and map geometry
14%Qualifying event–airfield pairs
29%Correct earthquake identifiers represented
21%Uses index-aware geography proximity
14%Maps the airfield points
7%Airport proximity uses a spatial index
14%