{
  "table": "DEMO.core.Fused",
  "theater": {
    "name": "Kahramanmaras earthquake sequence, southeast Turkey",
    "lat_min": 35.8,
    "lat_max": 38.3,
    "lon_min": 34.3,
    "lon_max": 38.5,
    "t_start_ms": 1675468800000,
    "t_end_ms": 1676246400000,
    "t_start_iso": "2023-02-04T00:00:00Z",
    "t_end_iso": "2023-02-13T00:00:00Z",
    "anchors": {
      "mainshock_M7_8": {
        "t_ms": 1675646254000,
        "iso": "2023-02-06T01:17:34Z",
        "lat": 37.226,
        "lon": 37.014
      },
      "aftershock_M7_6": {
        "t_ms": 1675679089000,
        "iso": "2023-02-06T10:24:49Z",
        "lat": 38.024,
        "lon": 37.203
      },
      "imagery_collect": {
        "t_ms": 1675845000000,
        "iso": "2023-02-08T08:30:00Z"
      }
    }
  },
  "columns": {
    "row_id": "BIGINT, primary key",
    "t_ms": "BIGINT, epoch millis, START of the row's validity. Indexed.",
    "t_end_ms": "BIGINT, epoch millis, END of validity. Equals t_ms for instantaneous rows.",
    "lat_e5": "INT, latitude x 1e5. Indexed. 36.2021 N is 3620210.",
    "lon_e5": "INT, longitude x 1e5. Indexed. 36.1606 E is 3616060.",
    "extent_m": "INT, radius in metres the row covers. 0 for a point.",
    "entity_id": "BIGINT, stable id of the thing. Vessels use MMSI. Other classes use disjoint high ranges.",
    "kind": "INT, modality/event class. See kinds.",
    "rel_id": "BIGINT, counterparty entity. 0 when none.",
    "ref_row": "BIGINT, row_id of the row this one cites or corroborates. 0 when none.",
    "conf": "SMALLINT, 0-100 confidence. For eo_scene this is damage severity x 25.",
    "src_sys": "VARCHAR, originating feed. Anything ending -SIM is simulated.",
    "payload": "VARCHAR, one-line display string.",
    "detail": "VARCHAR, full narrative text.",
    "src_ref": "VARCHAR, pointer to the native artifact. Pass to /resolve."
  },
  "kinds": {
    "1": "meeting        two units in sustained close proximity",
    "2": "dark           a unit stopped reporting AIS",
    "3": "person         response team on task",
    "5": "report         narrative report derived from other rows, see ref_row",
    "6": "eo_scene       electro-optical imagery cell, 1024 m grid",
    "7": "gdelt          geocoded open-source news event",
    "8": "infra          fixed infrastructure site",
    "10": "sar            synthetic aperture radar vessel detection",
    "11": "aircraft       aircraft arrival or departure",
    "12": "seismic        earthquake solution",
    "13": "destroyed      building recorded destroyed by an independent damage assessment",
    "9": "fmv            video. ref_row=0 is a parent clip, ref_row>0 is a labelled segment inside it"
  },
  "src_sys_values": {
    "MAXAR": "real, Maxar Open Data Program, CC BY-NC 4.0",
    "GDELT": "real, GDELT 2.0 event export",
    "USGS": "real, USGS FDSN event service",
    "INFRA-REF": "real, published site coordinates",
    "AIS-SIM": "simulated",
    "ADSB-SIM": "simulated",
    "SAR-SIM": "simulated",
    "ROSTER-SIM": "simulated",
    "REPORT-SIM": "simulated",
    "HOTOSM": "real, HOTOSM export via HDX, ODC-ODbL. detail names the underlying assessment: CopernicusEMS, cscrs, microsoft/BuildingFootprints",
    "OSM": "real, OpenStreetMap via Overpass, ODbL",
    "ARCHIVE-PD": "real, archive.org public-domain clips, labelled by OpenCLIP ViT-L-14. Location and capture time are NOT published: rows sit at the theater centroid with extent_m 120000 and the wall clock is anchored at the mainshock. The media offset in src_ref is the authoritative time."
  },
  "rules": [
    "ALWAYS bound both time and space. An unbounded query is rejected by the gateway.",
    "Time predicates MUST use interval overlap, never BETWEEN on t_ms alone. Rows with duration (kind 1,2,6,11) have t_end_ms > t_ms and a point-in-time predicate silently drops them. Correct form: t_ms < :win_end AND t_end_ms > :win_start",
    "Coordinates are scaled integers. Multiply degrees by 1e5 and compare as INT. Never compare against a float.",
    "Real and simulated rows are separable at any time: src_sys NOT LIKE '%-SIM'",
    "A bounding box in lat_e5 and lon_e5 outperforms a computed great-circle distance. Filter by box first, refine by distance only if needed.",
    "Corroboration runs through ref_row, not a spatial join. A kind 13 destroyed building carries the row_id of the imagery cell covering it, so agreement and disagreement between sources is one hop, not a distance computation.",
    "Video is not a special case, it is more rows. A labelled segment carries the media offset in src_ref as a #t=start,end fragment and cites its parent clip through ref_row, so searching inside video is an ordinary bounded query.",
    "Free text video search goes through SEMANTIC('...') which the gateway rewrites, not through LIKE on payload. LIKE only matches the 12 precomputed vocabulary labels. SEMANTIC matches any phrase, but it ranks rather than decides: always surface the score.",
    "Page with ORDER BY on a stable column plus LIMIT and OFFSET. truncated true means more rows match than were returned."
  ],
  "gateway_functions": {
    "SEMANTIC": {
      "signature": "SEMANTIC('free text') >= <score>",
      "applies_to": "kind 9 video segments",
      "how": "The gateway parses this predicate out of the SQL, embeds the phrase with OpenCLIP ViT-L-14, scores it against the stored per-segment embeddings, and rewrites the predicate to row_id IN (...) before execution. The front end emits ordinary SQL and never sees an embedding.",
      "cost": "140 segments x 768 dims is one small matrix multiply, microseconds, and the result is EXACT rather than approximate. No vector index is needed at this scale and brute force stays viable into the hundreds of thousands of segments.",
      "caveat": "Use it to RANK, never to decide presence or absence. Calibration on 15 probe queries showed the classes overlap: the weakest present query scored 0.206 and the strongest absent one 0.222. Show the score and let the analyst judge."
    }
  },
  "places": {
    "note": "Named locations with a ready-made bounding box at a sensible default radius. Use these rather than inventing coordinates. To change the radius: one degree of latitude is 111 km, one degree of longitude is 111 km times cos(latitude), so at 36.5 N a degree of longitude is about 89 km. Multiply degrees by 1e5.",
    "entries": {
      "Antakya": {
        "lat_e5": 3620210,
        "lon_e5": 3616060,
        "default_radius_km": 25,
        "note": "city, densest imagery coverage, 147 cells",
        "box": "lat_e5 BETWEEN 3597687 AND 3642733 AND lon_e5 BETWEEN 3588149 AND 3643971"
      },
      "Iskenderun": {
        "lat_e5": 3658670,
        "lon_e5": 3616670,
        "default_radius_km": 20,
        "note": "container port, closed after the mainshock",
        "box": "lat_e5 BETWEEN 3640652 AND 3676688 AND lon_e5 BETWEEN 3594230 AND 3639110"
      },
      "Ceyhan": {
        "lat_e5": 3687470,
        "lon_e5": 3592970,
        "default_radius_km": 20,
        "note": "BTC pipeline marine terminal",
        "box": "lat_e5 BETWEEN 3669452 AND 3705488 AND lon_e5 BETWEEN 3570446 AND 3615494"
      },
      "Mersin": {
        "lat_e5": 3679500,
        "lon_e5": 3462000,
        "default_radius_km": 20,
        "note": "largest container port on the Turkish Mediterranean",
        "box": "lat_e5 BETWEEN 3661482 AND 3697518 AND lon_e5 BETWEEN 3439500 AND 3484500"
      },
      "Karatas anchorage": {
        "lat_e5": 3655000,
        "lon_e5": 3538000,
        "default_radius_km": 25,
        "note": "open anchorage west of the Gulf of Iskenderun",
        "box": "lat_e5 BETWEEN 3632477 AND 3677523 AND lon_e5 BETWEEN 3509964 AND 3566036"
      },
      "Gaziantep": {
        "lat_e5": 3705000,
        "lon_e5": 3741000,
        "default_radius_km": 25,
        "note": "city, 187 imagery cells",
        "box": "lat_e5 BETWEEN 3682477 AND 3727523 AND lon_e5 BETWEEN 3712780 AND 3769220"
      },
      "Osmaniye": {
        "lat_e5": 3708000,
        "lon_e5": 3625000,
        "default_radius_km": 20,
        "note": "city, 51 imagery cells",
        "box": "lat_e5 BETWEEN 3689982 AND 3726018 AND lon_e5 BETWEEN 3602415 AND 3647585"
      },
      "Adana airport": {
        "lat_e5": 3698220,
        "lon_e5": 3528030,
        "default_radius_km": 15,
        "note": "ICAO LTAF, took diverted relief traffic",
        "box": "lat_e5 BETWEEN 3684706 AND 3711734 AND lon_e5 BETWEEN 3511113 AND 3544947"
      },
      "Gaziantep airport": {
        "lat_e5": 3694720,
        "lon_e5": 3747870,
        "default_radius_km": 15,
        "note": "ICAO LTAJ, took most relief flights",
        "box": "lat_e5 BETWEEN 3681206 AND 3708234 AND lon_e5 BETWEEN 3730961 AND 3764779"
      },
      "Hatay airport": {
        "lat_e5": 3636220,
        "lon_e5": 3628220,
        "default_radius_km": 15,
        "note": "ICAO LTDA, runway unusable after the mainshock",
        "box": "lat_e5 BETWEEN 3622706 AND 3649734 AND lon_e5 BETWEEN 3611439 AND 3645001"
      },
      "Kahramanmaras airport": {
        "lat_e5": 3753870,
        "lon_e5": 3695350,
        "default_radius_km": 15,
        "note": "ICAO LTCN",
        "box": "lat_e5 BETWEEN 3740356 AND 3767384 AND lon_e5 BETWEEN 3678308 AND 3712392"
      },
      "Pazarcik epicentre": {
        "lat_e5": 3722600,
        "lon_e5": 3701400,
        "default_radius_km": 30,
        "note": "M7.8 mainshock",
        "box": "lat_e5 BETWEEN 3695573 AND 3749627 AND lon_e5 BETWEEN 3667457 AND 3735343"
      },
      "Elbistan epicentre": {
        "lat_e5": 3802400,
        "lon_e5": 3720300,
        "default_radius_km": 30,
        "note": "M7.6, nine hours after the mainshock",
        "box": "lat_e5 BETWEEN 3775373 AND 3829427 AND lon_e5 BETWEEN 3685991 AND 3754609"
      },
      "Gulf of Iskenderun": {
        "lat_e5": 3660000,
        "lon_e5": 3600000,
        "default_radius_km": 60,
        "note": "the maritime area as a whole",
        "box": "lat_e5 BETWEEN 3605946 AND 3714054 AND lon_e5 BETWEEN 3532670 AND 3667330"
      }
    }
  },
  "time_anchors": {
    "note": "Epoch milliseconds. Build a window as t_ms < end AND t_end_ms > start.",
    "entries": {
      "window start": {
        "t_ms": 1675468800000,
        "iso": "2023-02-04T00:00:00Z",
        "note": "two days of pre-event baseline"
      },
      "mainshock": {
        "t_ms": 1675646254000,
        "iso": "2023-02-06T01:17:34Z",
        "note": "M7.8 Pazarcik, the reference point for everything"
      },
      "second shock": {
        "t_ms": 1675679089000,
        "iso": "2023-02-06T10:24:49Z",
        "note": "M7.6 Elbistan, nine hours later"
      },
      "imagery collect": {
        "t_ms": 1675845000000,
        "iso": "2023-02-08T08:30:00Z",
        "note": "every eo_scene row carries this timestamp"
      },
      "window end": {
        "t_ms": 1676246400000,
        "iso": "2023-02-13T00:00:00Z"
      }
    },
    "phrases": {
      "before the quake": "t_ms < 1675646254000 AND t_end_ms > 1675468800000",
      "first 24 hours": "t_ms < 1675732654000 AND t_end_ms > 1675646254000",
      "first 48 hours": "t_ms < 1675819054000 AND t_end_ms > 1675646254000",
      "first 72 hours": "t_ms < 1675905454000 AND t_end_ms > 1675646254000",
      "first week": "t_ms < 1676246400000 AND t_end_ms > 1675646254000",
      "the whole period": "t_ms < 1676246400000 AND t_end_ms > 1675468800000"
    }
  },
  "vocabulary": {
    "note": "payload is free text and differs per layer. Match it with LIKE only using the shapes below. For anything outside the video label list, use SEMANTIC on kind 9 rather than LIKE.",
    "payload_by_kind": {
      "1 meeting": "<VESSEL> alongside <VESSEL> <N> min pre-event | <VESSEL> holding offshore Iskenderun <N> h",
      "2 dark": "<VESSEL> dark <N.N> h; reachable <N.N> km",
      "3 person": "<ORG> team <NN> on task",
      "5 report": "Damage assessment; cell <ID> | Reporting gap review; MMSI <N> | Initial impact note; <QUAKE>",
      "6 eo_scene": "EO cell <ID> sev <N> <CATEGORY>",
      "7 gdelt": "<PLACE>; <ADM1>; <COUNTRY> evcode <CAMEO> tone <N.N>",
      "8 infra": "<SITE NAME>, for example 'Iskenderun container port' or 'hospital <name>'",
      "9 fmv": "Video clip <FILE> (<N>s) for a parent, video: <LABEL> @ <A>-<B>s for a segment",
      "10 sar": "SAR contact; no AIS; <N.N> km from last report | SAR contact; AIS correlated",
      "11 aircraft": "<COUNTRY> relief flight into <ICAO> | Scheduled arrival <ICAO> | Diverted from HTY to ADA",
      "12 seismic": "M<N.N> <USGS PLACE STRING>",
      "13 destroyed": "Destroyed building [<ASSESSMENT SOURCE>]"
    },
    "video_labels": {
      "rescue operation": 33,
      "collapsed building": 28,
      "snow": 24,
      "collapse dust": 24,
      "shelter": 11,
      "damaged road": 9,
      "structure fire": 4,
      "crowd": 3,
      "night scene": 2,
      "heavy machinery": 1,
      "news studio": 1
    },
    "cameo_note": "GDELT evcode is a CAMEO event code, not plain text. 042 is 'travel to', 090 is 'investigate', 073 is 'provide aid'. Do not filter on it unless the user names a code.",
    "notable_values": {
      "ICAO codes": [
        "LTAF Adana",
        "LTAJ Gaziantep",
        "LTDA Hatay",
        "LTCN Kahramanmaras"
      ],
      "assessment sources": [
        "CopernicusEMS",
        "cscrs",
        "microsoft/BuildingFootprints",
        "OpenAerialMap"
      ],
      "eo_scene conf": "severity 0 to 3 multiplied by 25, so 50 and above is severity 2 and above"
    }
  },
  "not_in_this_table": [
    "Casualty, injury or fatality counts. No layer carries them.",
    "Building occupancy, population or census figures.",
    "Power, water or telecoms outage status.",
    "Road closures or route passability.",
    "Any date outside 2023-02-04 to 2023-02-13.",
    "Anywhere outside 35.8 to 38.3 N and 34.3 to 38.5 E. Adana and Mersin are in the box, Istanbul is not.",
    "Named individuals. kind 3 rows are response teams, not people.",
    "Vessel cargo, ownership or destination. kind 1 and 2 carry MMSI, name and type only.",
    "Imagery outside the three blocks over Antakya, Gaziantep and Osmaniye. Coverage is not continuous.",
    "Video with a real geotag. Every kind 9 row sits at the theater centroid, 37.00 N 36.40 E."
  ],
  "when_a_query_fails": [
    "A 400 carries an error field naming the problem. Read it and retry rather than surfacing it.",
    "'must bound space' or 'must bound time' means a range is missing. Add it from places or time_anchors.",
    "Zero rows with a valid query usually means the question is outside the table. Check not_in_this_table before assuming a fault.",
    "Zero rows from a LIKE on payload usually means the wrong shape. Check vocabulary.payload_by_kind.",
    "For video, a LIKE outside the twelve labels returns nothing. Use SEMANTIC instead."
  ],
  "api": {
    "openapi": "/api/openapi.yaml",
    "version": "1.0.0",
    "pagination": "There is no cursor. The row cap is 4000 and a response sets truncated true when it is hit. Page with ORDER BY on a stable column, row_id is safest, then LIMIT and OFFSET. Ordering by a non-unique column such as t_ms can repeat or skip rows across pages.",
    "rate_limits": "None enforced at present. Do not rely on that.",
    "cors": "No CORS headers are sent. Call from a server, not from a browser on another origin.",
    "media_auth": "/chip and /clip need the browser session cookie, not the bearer token. /clip honours Range and returns 206, which a player needs in order to seek."
  }
}