Five upstream energy formats over one field, read in place with no export step, and then joined: a SEG-Y seismic volume (120 traces), two LAS well logs (600 depth steps), a ZMAP+ top-reservoir depth grid (1164 live nodes of 2000), an ECLIPSE GRDECL corner-point model (1664 active cells of 2400) and UKOOA P1/90 survey navigation (240 shot points). Each format keeps its own natural row shape, because a trace, a depth step, a grid node and a cell are different things and flattening them into one shape would lose what makes each queryable. The point is the joins: seismic geometry and survey navigation meet on real UTM coordinates, and the model meets the mapped surface, because reading the formats in place leaves the geometry already comparable. Also proves the details that silently go wrong: the SEG-Y coordinate scalar is applied, the LAS NULL sentinel reads as a real NULL, GRDECL run-length encoding expands correctly and ACTNUM is honoured, the ZMAP grid is not transposed, and packed P1/90 latitudes arrive as decimal degrees. This is the cross-format integration demo; each format also has a dedicated demo of its own.
-- ============================================================================
-- Subsurface: North Sea Demo Field - Setup Script
-- ============================================================================
-- Six external tables over five upstream energy formats, all read in place:
--
-- 1. seismic_traces SEG-Y, one row per trace, samples kept as an array
-- 2. seismic_headers SEG-Y again, header columns only, no sample decoding
-- 3. well_logs LAS, one row per depth step, well header on every row
-- 4. top_reservoir ZMAP+, one row per grid node with real coordinates
-- 5. reservoir_model GRDECL, one row per cell, one column per property
-- 6. survey_navigation UKOOA P1/90, one row per shot point
--
-- Every format keeps its own natural row shape. That is deliberate: a trace,
-- a depth step, a grid node and a cell are different things, and flattening
-- them into a common shape would lose what makes each queryable.
--
-- No format needs a USING clause it cannot infer, but each is written out
-- here so the script reads as documentation of which keyword goes with which
-- extension.
-- ============================================================================
-- 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}}.subsurface
COMMENT 'Upstream energy formats read in place: seismic, well logs, grids, simulation decks and navigation';
-- ============================================================================
-- TABLE 1: seismic_traces - SEG-Y, one row per trace
-- ============================================================================
-- The trace header becomes ordinary scalar columns (inline, crossline,
-- source_x, source_y, offset, ...) and the samples become a single
-- FixedSizeList<Float32> column. Coordinates arrive already corrected: SEG-Y
-- stores them as integers with a separate scalar, and the reader applies it,
-- so source_x is a real UTM easting rather than a raw integer.
-- ============================================================================
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.subsurface.seismic_traces
USING SEGY
LOCATION '{{data_subdir}}/demo_survey.segy';
-- ============================================================================
-- TABLE 2: seismic_headers - the same volume, without the samples
-- ============================================================================
-- include_samples = 'false' skips sample decoding entirely. On a real survey
-- this is the difference between reading the geometry of a 40 GB volume and
-- reading the volume, so it is the table to build a map or a fold plot from.
-- ============================================================================
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.subsurface.seismic_headers
USING SEGY
LOCATION '{{data_subdir}}/demo_survey.segy'
OPTIONS (
include_samples = 'false'
);
-- ============================================================================
-- TABLE 3: well_logs - LAS, both wells in one table
-- ============================================================================
-- The glob reads both files as one table. The ~W well-information section
-- becomes constant columns on every row (well_well, well_fld, well_uwi), so
-- the table is queryable by well or field with no join to a header table.
--
-- The RHOB curve has a washed-out interval written with the file's own NULL
-- sentinel (-999.25). It reads as a real SQL NULL, so AVG(rhob) is the average
-- of the readings that exist rather than being dragged to minus a thousand.
-- ============================================================================
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.subsurface.well_logs
USING LAS
LOCATION '{{data_subdir}}/*.las'
OPTIONS (
file_metadata = '{"columns":["df_file_name"]}'
);
-- (excerpt; run the demo for the full script)## What this demo does Five upstream energy formats over one field, read in place with no export step, and then joined: a SEG-Y seismic volume (120 traces), two LAS well logs (600 depth steps), a ZMAP+ top-reservoir depth grid (1164 live nodes of 2000), an ECLIPSE GRDECL corner-point model (1664 active cells of 2400) and UKOOA P1/90 survey navigation (240 shot points). Each format keeps its own natural row shape, because a trace, a depth step, a grid node and a cell are different things and flattening them into one shape would lose what makes each queryable. The point is the joins: seismic geometry and survey navigation meet on real UTM coordinates, and the model meets the mapped surface, because reading the formats in place leaves the geometry already comparable. Also proves the details that silently go wrong: the SEG-Y coordinate scalar is applied, the LAS NULL sentinel reads as a real NULL, GRDECL run-length encoding expands correctly and ACTNUM is honoured, the ZMAP grid is not transposed, and packed P1/90 latitudes arrive as decimal degrees. This is the cross-format integration demo; each format also has a dedicated demo of its own. ## Formats it reads The setup script registers external tables with: `USING CLAUSE`, `USING SEGY`, `USING LAS`, `USING ZMAP`, `USING GRDECL`, `USING UKOOA`. ## What it creates - `{{zone_name}}.subsurface.seismic_traces` (external table) - `{{zone_name}}.subsurface.seismic_headers` (external table) - `{{zone_name}}.subsurface.well_logs` (external table) - `{{zone_name}}.subsurface.top_reservoir` (external table) - `{{zone_name}}.subsurface.reservoir_model` (external table) - `{{zone_name}}.subsurface.survey_navigation` (external table) ## Running it The demo ships `setup.sql`, `queries.sql` and `cleanup.sql` under `demos/subsurface/north-sea-field`. The queries carry their own `ASSERT`s, so a run that completes has verified its own numbers. Data: 6 files, 0.2 MB.