A regional aviation response network is replacing emailed spreadsheets. It receives USGS event updates, maintains a global airfield register, and records repeated condition checks after each event. An event can change magnitude or location as reviewed products arrive, while a check must remain traceable to its event, airfield and time. Analysts will search geographically and read each event's check history.
Write PostgreSQL/PostGIS DDL for three tables. The fixed teaching snapshots contain 86 USGS events (M5.5+, January–March 2025) and 893 Natural Earth airports, but the live system must accept updates and multiple timestamped checks. The USGS event identifier is stable for upserts; origin fields can be revised. Use these exact identifiers so downstream ingestion and queries can bind to the schema:
seismic_events: event_id text primary key, magnitude numeric not null, depth_km numeric not null, event_time timestamptz not null, and geom typed geometry(Point,4326) not null.airfields: airfield_id text primary key, name text not null, facility_type text not null, and geom typed geometry(Point,4326) not null.event_airfield_checks: event_id text referencing seismic_events, airfield_id text referencing airfields, checked_at timestamptz not null, and condition text not null. The combination of event, airfield and time is the primary key: repeated checks at different times must be retained, while a duplicate timestamp for the same pair must be refused.Add usable spatial indexes on both point columns for map extent and spatial prefilter queries. Add an index on event_airfield_checks(event_id, checked_at) for an event's chronological checks. The DDL runs inside a rolled-back sandbox transaction; create tables and indexes only. Run reveals the resulting catalogue without a grade; Submit grades the built schema.
These are where the teaching is. Read them twice.
USGS FDSN event service documents stable event IDs and revisable origin information (https://earthquake.usgs.gov/fdsnws/event/1/); PostGIS documents GiST spatial indexes and spatial-index-aware functions (https://postgis.net/documentation/faq/spatial-indexes/). Snapshot counts are measured from the Kharita teaching datasets.
12 scored, 0 informational
Event register exists
5%Airfield register exists
5%Timestamped condition history exists
5%Events carry typed WGS 84 points
10%Airfields carry typed WGS 84 points
10%Event map reads can use a spatial index
10%Airfield spatial searches can use an index
10%Every condition check references a known event
10%Every condition check references a known airfield
10%A repeated timestamp for one pair is refused
10%Event check history has a chronological index
10%Spatial columns declare an SRID
5%