Produced water from oil and gas production is disposed of by injecting it back underground, and in Oklahoma and the Permian Basin that injection is linked to earthquakes. A monitoring team pulls the USGS catalogue for the play each month into a register that accumulates rather than being replaced, because a magnitude threshold being crossed is what triggers a rate reduction or a shut-in. The data is real and public domain: the USGS earthquake catalogue queried for magnitude 2.5 and above between 31.0 and 37.0 north and 104.5 and 94.4 west. Three monthly pulls of 34, 47 and 64 events, and the trend is the point. GeoJSON is JSON read under a curated profile rather than a parser of its own, and two properties of that profile decide the table: the feature array is both the row path and an explode path, so one feature becomes one row instead of the whole collection becoming one, and the geometry is opaque, so a coordinate array stays a single value rather than exploding into a column per ordinate, which for a polygon layer would mean a column per vertex. A camelCase property is split, so the USGS magType lands as properties_mag_type. It asserts what a real catalogue holds and a written one would not: three magnitude scales mixed across 145 events, 137 of them on the Texas and Oklahoma regional networks because that is where the injection is, and exactly one event at magnitude 4.0 or above.
-- ============================================================================
-- Induced Seismicity Monitoring - Setup Script
-- ============================================================================
-- Produced water from oil and gas production is disposed of by injecting it
-- back underground, and in Oklahoma and the Permian Basin that injection is
-- linked to earthquakes. Operators and regulators watch the seismic record
-- around their disposal wells, because a magnitude threshold being crossed is
-- what triggers a rate reduction or a shut-in.
--
-- The monitoring team pulls the USGS catalogue for the play each month and
-- loads it into a register that accumulates rather than being replaced:
--
-- 11 March 2026-01 34 events
-- 11 March 2026-02 47 events
-- 12 March 2026-03 64 events
--
-- The data is REAL. It is the USGS earthquake catalogue, queried for
-- magnitude 2.5 and above between 31.0 and 37.0 north and 104.5 and 94.4
-- west, which covers the Oklahoma seismic zone and the Permian Basin. USGS
-- data is a work of the United States government and is in the public domain.
-- See ATTRIBUTION.md in the parent folder for the exact query.
--
-- GeoJSON is JSON, read through the JSON engine under a curated profile
-- rather than a parser of its own. Two properties of that profile decide what
-- the table looks like:
--
-- * `$.features` is both the row path and an explode path, so one feature
-- becomes one row rather than the whole collection becoming one row.
-- * `$.geometry` is OPAQUE. A point's coordinate array stays a single value
-- instead of exploding into a column per ordinate, which for a polygon
-- would mean a column per vertex.
--
-- 1. seismic_feed external, DISCOVER over the landing folder
-- 2. seismic_register DELTA, the accumulating catalogue
-- ============================================================================
-- ----------------------------------------------------------------------------
-- STEP 1: Zone and schema
-- ----------------------------------------------------------------------------
CREATE ZONE IF NOT EXISTS {{zone_name}} TYPE EXTERNAL
COMMENT 'External tables - demo datasets and file-backed data';
CREATE SCHEMA IF NOT EXISTS {{zone_name}}.seismicity
COMMENT 'USGS seismic catalogue read in place, curated into Delta';
-- ----------------------------------------------------------------------------
-- STEP 2: Register the landing folder with DISCOVER
-- ----------------------------------------------------------------------------
-- One DISCOVER over the folder rather than one per file: every monthly pull
-- has the same shape, so they belong in one external table, and the file name
-- travels with each row as the watermark the incremental load keys on.
--
-- Column names come from the feature, so a property lands as
-- `properties_<name>` and a camelCase property is split: USGS writes
-- `magType` and the column is `properties_mag_type`.
-- ----------------------------------------------------------------------------
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.seismicity.seismic_feed;
DISCOVER {{zone_name}}.seismicity.seismic_feed
PATH '{{data_subdir}}/landing'
WITH (FILE_METADATA = true);
-- (excerpt; run the demo for the full script)## What this demo does Produced water from oil and gas production is disposed of by injecting it back underground, and in Oklahoma and the Permian Basin that injection is linked to earthquakes. A monitoring team pulls the USGS catalogue for the play each month into a register that accumulates rather than being replaced, because a magnitude threshold being crossed is what triggers a rate reduction or a shut-in. The data is real and public domain: the USGS earthquake catalogue queried for magnitude 2.5 and above between 31.0 and 37.0 north and 104.5 and 94.4 west. Three monthly pulls of 34, 47 and 64 events, and the trend is the point. GeoJSON is JSON read under a curated profile rather than a parser of its own, and two properties of that profile decide the table: the feature array is both the row path and an explode path, so one feature becomes one row instead of the whole collection becoming one, and the geometry is opaque, so a coordinate array stays a single value rather than exploding into a column per ordinate, which for a polygon layer would mean a column per vertex. A camelCase property is split, so the USGS magType lands as properties_mag_type. It asserts what a real catalogue holds and a written one would not: three magnitude scales mixed across 145 events, 137 of them on the Texas and Oklahoma regional networks because that is where the injection is, and exactly one event at magnitude 4.0 or above. ## What it creates - `{{zone_name}}.seismicity.seismic_feed` (external table) - `{{zone_name}}.seismicity.seismic_register` (delta table) ## Running it The demo ships `setup.sql`, `queries.sql` and `cleanup.sql` under `demos/subsurface/geojson-induced-seismicity`. The queries carry their own `ASSERT`s, so a run that completes has verified its own numbers. Data: 3 files, 0.1 MB.