/beta/query_datalinks

This guide details available datalinks, table schemas, sample queries, and field definitions for querying SynMax’s datasets via the unified /v4/beta/query_datalinks endpoint.

Key Benefits:

• Query multiple datasets with a single endpoint (Hyperion, Vulcan, Leviaton)

• Use standard SQL syntax across all datalinks

• Access real-time and historical data in one place

Warning

Beta API Notice: This endpoint is currently in beta and may change as we refine the feature.

Endpoint

Method	POST
URL	https://hyperion.api.synmax.com/v4/beta/query_datalinks

Request Examples

cURLSynmax Python ClientPowerShell

curl -X POST "https://hyperion.api.synmax.com/v4/beta/query_datalinks" \
     -H "Access-Key: $API_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{
           "query": "SELECT * FROM hdl.production_by_well LIMIT 10"
         }'

Hyperion

Comprehensive upstream oil and gas intelligence covering the entire well lifecycle from drilling to production across U.S. and Canadian basins. Includes historical state-reported monthly production (1960s-present), well attributes, and short/long-term forecasts with 411M+ production records and 4.6M+ wells. Daily tracking of drilling rigs, frac crews, completions, and DUC inventory provides real-time visibility into well development activity (coverage begins Sept 2020 for most U.S. regions; May 2024 for Canada). Also includes daily interstate pipeline nomination and capacity data tracking natural gas movements across the U.S. transmission network at point-by-point level with scheduled flows, operating capacities, and location metadata.

hdl.production_by_well

Well-level monthly oil and gas production data derived from state and provincial regulatory reporting, covering US states (TX, PA, CO, ND, NM, LA, OK, OH, WV, etc.) and Canadian provinces (AB, BC). Each row represents one well’s production for one specific month, with pre-calculated daily rates and production month indexing for decline curve analysis. Supports IP rate calculations using production_month = 2 for first full month performance and regional aggregation via region_natgas and sub_region_natgas fields.

Warning

Production data has significant reporting lag (4 months to 3+ years depending on state). Recent months appear to show declining production due to incomplete reporting—exclude the most recent 6-12 months for trend analysis.

Table ID: hdl.production_by_well

Sample Queries

-- Regional gas production time series (Lower 48)
SELECT
    date_prod,
    region_natgas,
    SUM(prod_wet_gas_mcf_day) / 1000000.0 AS prod_gas_bcf_day,
    COUNT(DISTINCT well_id) AS well_count
FROM hdl.production_by_well
WHERE date_prod BETWEEN '2023-01-01' AND '2024-06-01'
  AND region_natgas NOT IN ('Alaska', 'Canada')
GROUP BY date_prod, region_natgas
ORDER BY date_prod, region_natgas;

-- IP rates for new wells (month 2 = first full production month)
SELECT
    well_id,
    date_prod,
    prod_wet_gas_mcf_day AS ip_gas_mcf_day,
    prod_oil_bbl_day AS ip_oil_bbl_day,
    operator,
    sub_region_natgas
FROM hdl.production_by_well
WHERE production_month = 2
  AND date_prod >= '2024-01-01'
  AND prod_wet_gas_mcf_month > 10000
ORDER BY prod_wet_gas_mcf_day DESC
LIMIT 50;

Field Reference

Field Name	Data Type	Description
well_id	varchar	Unique well identifier (API number). Example: 0500105534
well_api	varchar	Well API number (alternate identifier)
well_name	varchar	Well name
well_no	varchar	Well number. Example: 1-17
field_name	varchar	Name of the field that the well is located in. Example: Johns
date_prod	date	Production month (first day of month). Example: 2024-01-01
production_month	integer	Months since first production (1-indexed). Example: 12
prod_wet_gas_mcf_month	integer	Monthly gas production in MCF/month
prod_oil_bbl_month	integer	Monthly oil production in BBL/month
prod_water_bbl_month	integer	Monthly water production in BBL/month
prod_wet_gas_mcf_day	decimal	Daily gas production rate in MCF/day (pre-calculated)
prod_oil_bbl_day	decimal	Daily oil production rate in BBL/day (pre-calculated)
prod_water_bbl_day	decimal	Daily water production rate in BBL/day (pre-calculated)
operator	varchar	Operating company name
state_code	varchar	State/province code (TX, PA, CO, ND, AB, BC, etc.)
county	varchar	County name
region_natgas	varchar	Natural gas region (gulf, northeast, midwest, west, Canada)
sub_region_natgas	varchar	Sub-region (Haynesville - TX, SW PA, Permian-NM, etc.)
formation	varchar	Producing formation/reservoir
township	varchar	Township location
range	varchar	Range location
section	varchar	Section location

hdl.wells

Comprehensive well master containing 4.6M+ wells (1900-2025) with detailed information from regulatory filings, permits, completion reports, and production records. Includes standardized drilling/completion dates, lateral lengths, wellpad assignments, coordinates, and geological data across 7,552 formations and 14 basins. Use well_id as primary key and wellpad_id to analyze multi-well pad development patterns.

Table ID: hdl.wells

Sample Queries

-- Recent horizontal wells in Permian Basin
SELECT
    well_id,
    operator,
    horizontal_length,
    date_first_production,
    produced_formation
FROM hdl.wells
WHERE sub_region_natgas = 'Permian-NM'
  AND wellbore_type = 'Horizontal'
  AND date_spud >= '2023-01-01'
ORDER BY horizontal_length DESC
LIMIT 20;

-- Count wells by operator and basin for 2024 completions
SELECT
    operator,
    sub_region_natgas,
    COUNT(*) AS well_count,
    AVG(horizontal_length) AS avg_lateral_ft
FROM hdl.wells
WHERE date_completion >= '2024-01-01'
  AND date_completion < '2025-01-01'
GROUP BY operator, sub_region_natgas
ORDER BY well_count DESC
LIMIT 20;

Field Reference

Field Name	Data Type	Description
well_id	nvarchar	Unique well identifier/API number (primary key). Example: 0500105534
wellpad_id	bigint	Groups wells that share the same drilling pad location
operator	nvarchar	Operating company name. Example: EOG RESOURCES
county	nvarchar	County or municipal district name
state_code	nvarchar	State/province code (TX, PA, OH, WV, NM, OK, LA, ND, CO, AB, BC)
region_natgas	nvarchar	Natural gas region (gulf, northeast, midwest, west, Alaska, Canada)
sub_region_natgas	nvarchar	Sub-region/basin (Permian-NM, Haynesville - LA, SW PA, etc.)
lat_surface	decimal	Surface latitude where drilling begins (decimal degrees)
lon_surface	decimal	Surface longitude where drilling begins (decimal degrees)
lat_bottomhole	decimal	Bottomhole latitude where horizontal wellbore ends
lon_bottomhole	decimal	Bottomhole longitude where horizontal wellbore ends
date_permit	date	Date drilling permit was issued. Example: 2024-03-15
date_spud	date	Date when drilling operations began (spud date)
date_completion	date	Date when well completion operations finished
date_first_production	date	Date when well first produced hydrocarbons
depth_measured	decimal	Total measured depth of wellbore in feet
depth_tvd	decimal	True vertical depth in feet
horizontal_length	decimal	Length of horizontal/lateral section in feet. Example: 10500
wellbore_type	nvarchar	Well configuration (Horizontal, Vertical, Directional, Unknown)
produced_formation	nvarchar	Primary producing formation (NIOBRARA, WOLFCAMP, MARCELLUS, etc.)
produced_basin	nvarchar	Producing basin classification (PERMIAN BASIN, DENVER-CHEYENNE, etc.)

hdl.long_term_forecast

Three-year regional natural gas production forecasts at sub-region-quarter grain in BCF/d and MBD units. Based on public producer earnings for the public/reported portion of production, with growth rates adjusted by public/private frac activity ratios at the sub_region_natgas level. Updated quarterly to support long-term supply planning and regional production comparisons.

Table ID: hdl.long_term_forecast

Sample Queries

-- Long-term forecast by region (time series)
SELECT
    date_prod,
    region_natgas,
    SUM(prod_dry_gas_bcf_day) AS total_gas_bcf_day,
    SUM(prod_oil_bbl_day) AS total_oil_bbl_day
FROM hdl.long_term_forecast
WHERE date_prod BETWEEN '2025-06-01' AND '2026-12-31'
GROUP BY date_prod, region_natgas
ORDER BY date_prod;

-- Sub-region production comparison (West and Northeast)
SELECT
    date_prod,
    sub_region_natgas,
    prod_dry_gas_bcf_day
FROM hdl.long_term_forecast
WHERE region_natgas IN ('west', 'northeast')
  AND date_prod >= '2025-06-01'
ORDER BY date_prod, prod_dry_gas_bcf_day DESC;

Field Reference

Field Name	Data Type	Description
date_prod	date	Production forecast date. Example: 2025-06-01
sub_region_natgas	varchar	Natural gas sub-region. Example: Wyoming
region_natgas	varchar	Natural gas region. Example: west
prod_dry_gas_bcf_day	float	Forecasted dry gas production (BCF/day). Example: 0.188561
prod_oil_bbl_day	float	Forecasted oil production (BBL/day). Example: 52.0

hdl.long_term_forecast_history

Historical archive of all long-term production forecast vintages, enabling comparison of how forecasts have evolved over time. Each row represents a forecast for a specific sub-region and production month, tagged with forecast_run_date indicating when that forecast was generated. Contains 19 forecast vintages from December 2022 to February 2026. Updated quarterly as new forecasts are generated.

The forecast methodology is based on public producer earnings guidance for the portion of production that is publicly reported. That growth rate is then adjusted by the ratio of public-to-private frac activity at the sub-region level — the growth rate increases when there is more private than public frac activity and decreases in the opposite case.

Note

The prod_oil_bbl_day and prod_dry_gas_bcf_day columns may contain NULLs in some forecast vintages. Not every vintage covers every sub-region or commodity. Filter by forecast_run_date to retrieve a single vintage.

Warning

Gas and oil forecasts are updated on different schedules. The latest forecast_run_date for gas and oil will often differ (e.g., gas updated 2026-02-17, oil updated 2025-11-21). A naive WHERE forecast_run_date = (SELECT MAX(forecast_run_date) ...) will return only the commodity that was updated most recently, with NULLs for the other. Always find the latest forecast_run_date independently per commodity. See the “Latest forecast” example below.

Warning

Values are daily rates (BCF/day for gas, BBL/day for oil), not monthly volumes. Use AVG() when aggregating across time periods and SUM() when aggregating across sub-regions.

Note

Sub-regions “L48” and “SD” are excluded from this table.

Table ID: hdl.long_term_forecast_history

Sample Queries

-- Latest forecast (handles different gas/oil vintage dates)
-- IMPORTANT: Gas and oil may have different latest forecast_run_dates.
-- You must find the latest forecast_run_date independently for each commodity.
WITH latest_gas AS (
    SELECT date_prod, sub_region_natgas, region_natgas, prod_dry_gas_bcf_day
    FROM hdl.long_term_forecast_history
    WHERE forecast_run_date = (
        SELECT MAX(forecast_run_date)
        FROM hdl.long_term_forecast_history
        WHERE prod_dry_gas_bcf_day IS NOT NULL
    )
    AND prod_dry_gas_bcf_day IS NOT NULL
),
latest_oil AS (
    SELECT date_prod, sub_region_natgas, region_natgas, prod_oil_bbl_day
    FROM hdl.long_term_forecast_history
    WHERE forecast_run_date = (
        SELECT MAX(forecast_run_date)
        FROM hdl.long_term_forecast_history
        WHERE prod_oil_bbl_day IS NOT NULL
    )
    AND prod_oil_bbl_day IS NOT NULL
)
SELECT
    COALESCE(g.date_prod, o.date_prod)                AS date_prod,
    COALESCE(g.sub_region_natgas, o.sub_region_natgas) AS sub_region_natgas,
    COALESCE(g.region_natgas, o.region_natgas)         AS region_natgas,
    g.prod_dry_gas_bcf_day,
    o.prod_oil_bbl_day
FROM latest_gas g
FULL OUTER JOIN latest_oil o
    ON g.date_prod = o.date_prod
    AND g.sub_region_natgas = o.sub_region_natgas
ORDER BY date_prod;

-- Compare two forecast vintages for Haynesville - LA
SELECT
    a.date_prod,
    a.prod_dry_gas_bcf_day AS gas_vintage_1,
    b.prod_dry_gas_bcf_day AS gas_vintage_2,
    b.prod_dry_gas_bcf_day - a.prod_dry_gas_bcf_day AS gas_revision
FROM hdl.long_term_forecast_history a
JOIN hdl.long_term_forecast_history b
  ON a.date_prod = b.date_prod
 AND a.sub_region_natgas = b.sub_region_natgas
WHERE a.forecast_run_date = '2025-06-06'
  AND b.forecast_run_date = '2026-02-17'
  AND a.sub_region_natgas = 'Haynesville - LA'
ORDER BY a.date_prod;

-- List all available forecast vintage dates
-- Gas and oil forecasts may have different vintage dates, so you may want to filter by commodity when listing vintages.
SELECT DISTINCT forecast_run_date
FROM hdl.long_term_forecast_history
ORDER BY forecast_run_date;

-- To list vintages for gas forecasts only:
SELECT DISTINCT forecast_run_date
FROM hdl.long_term_forecast_history
WHERE prod_dry_gas_bcf_day IS NOT NULL
ORDER BY forecast_run_date;

-- Aggregate by time period and geography for a specific vintage:
SELECT date_prod, region_natgas,
      SUM(prod_dry_gas_bcf_day) AS total_gas_bcf_day,
      SUM(prod_oil_bbl_day) AS total_oil_bbl_day
FROM hdl.long_term_forecast_history
WHERE forecast_run_date = '2025-06-15'
  AND date_prod BETWEEN '2025-06-01' AND '2026-12-31'
GROUP BY date_prod, region_natgas
ORDER BY date_prod;

-- Track how a specific region's forecast has evolved over time:
SELECT forecast_run_date, date_prod,
      SUM(prod_dry_gas_bcf_day) AS total_gas_bcf_day
FROM hdl.long_term_forecast_history
WHERE region_natgas = 'northeast'
  AND date_prod = '2026-06-01'
GROUP BY forecast_run_date, date_prod
ORDER BY forecast_run_date;

Field Reference

Field Name	Data Type	Description
date_prod	date	Forecast target month. Example: 2021-01-01
sub_region_natgas	varchar	Sub-region. Example: Wyoming
region_natgas	varchar	High-level region. Example: west
forecast_run_date	date	Date the forecast was produced. Example: 2025-06-15
prod_dry_gas_bcf_day	float	as production forecast in BCF/day. Example: 0.188561
prod_oil_bbl_day	float	Oil production forecast in barrels/day. Example: 52.0

hdl.tils

TIL (Turned In Line) monitoring data tracking when wellpads transition from completed to producing status. Each row represents one wellpad being turned in line on a specific date, with fields for frac end date, TIL date, operator, and initial production rate. Use for analyzing completion-to-production timing, TIL inventory calculations, and production ramp-up analysis.

Warning

This dataset only covers Expand Energy, Apex, and EQT in Haynesville - LA and Northeast regions for the period beginning March 1, 2024. Do not use for analysis outside this scope.

Table ID: hdl.tils

Sample Queries

-- TIL lag analysis by operator
SELECT
    operator,
    sub_region_natgas,
    COUNT(*) AS wellpad_count,
    SUM(wells) AS total_wells,
    AVG(date_til - date_frac_end) AS avg_days_to_til
FROM hdl.tils
WHERE date_til IS NOT NULL
GROUP BY operator, sub_region_natgas
ORDER BY total_wells DESC;

-- Pending TIL inventory (completed but not yet producing)
SELECT
    operator,
    sub_region_natgas,
    wellpad_id,
    date_frac_end,
    wells,
    prod_wet_gas_bcf_day
FROM hdl.tils
WHERE date_til IS NULL
ORDER BY date_frac_end;

Field Reference

Field Name	Data Type	Description
wellpad_id	varchar	Wellpad identifier. Example: 3163
wells	bigint	Number of wells on the wellpad. Example: 3
date_frac_end	date	Date frac operations ended. Example: 2025-01-01
date_til	date	Turned-in-line date (NULL if not yet TIL). Example: 2025-01-29
operator	varchar	Operator name (EXPAND, APEX, EQT). Example: EXPAND
sub_region_natgas	varchar	Natural gas sub-region (Haynesville - LA, NorthEast). Example: Haynesville - LA
prod_wet_gas_bcf_day	float	Initial production rate (BCF/day). Example: 0.078

hdl.dry_gas_factors

Monthly dry gas conversion factors by subregion, representing the ratio of dry gas to wet gas production. Each row is one subregion-month combination. Use for converting wet gas volumes to dry gas equivalents in production analysis and forecasting. Multiply wet gas production by dry_gas_factor to get dry gas equivalent. Factors typically range from 0.7 to 1.0 depending on gas composition in the subregion.

Table ID: hdl.dry_gas_factors

Sample Queries

-- Latest dry gas factors by subregion
SELECT sub_region_natgas, date_factor, dry_gas_factor
FROM hdl.dry_gas_factors
WHERE date_factor >= '2024-01-01'
ORDER BY sub_region_natgas, date_factor;

-- Latest month's dry gas factors
SELECT *
FROM hdl.dry_gas_factors
WHERE date_factor = (SELECT MAX(date_factor) FROM hdl.dry_gas_factors);

-- Latest factor per subregion (if subregions update differently)
WITH latest AS (
    SELECT sub_region_natgas, MAX(date_factor) AS max_date
    FROM hdl.dry_gas_factors
    GROUP BY sub_region_natgas
)
SELECT d.*
FROM hdl.dry_gas_factors d
INNER JOIN latest l
    ON d.sub_region_natgas = l.sub_region_natgas
   AND d.date_factor       = l.max_date;

Field Reference

Field Name	Data Type	Description
sub_region_natgas	varchar	Subregion divisions based on natural gas production profiles
date_factor	date	The month during which this dry gas factor applies
dry_gas_factor	float	The dry gas factor (ratio of dry to wet gas, typically 0.7–1.0)

hdl.oil_production

Daily modeled oil production rates by subregion. Each row is one subregion-day combination. Analogous to hdl.daily_production but for oil instead of gas. Use for oil supply monitoring, trend analysis, and basin-level oil production comparisons.

Warning

Critical Aggregation Rules: Use AVG() for time aggregation (monthly/quarterly/annual) since values are daily RATES, not volumes. Use SUM() for spatial aggregation across sub-regions on a single date.

Table ID: hdl.oil_production

Sample Queries

-- Daily oil production by subregion
SELECT date_prod, sub_region_natgas, prod_oil_bbl_day
FROM hdl.oil_production
WHERE date_prod >= '2024-01-01'
ORDER BY date_prod, sub_region_natgas;

-- Latest date's oil production by subregion
SELECT *
FROM hdl.oil_production
WHERE date_prod = (SELECT MAX(date_prod) FROM hdl.oil_production);

-- Monthly average oil production by subregion (time aggregation - use AVG)
SELECT
    DATE_TRUNC('month', date_prod) as month,
    sub_region_natgas,
    AVG(prod_oil_bbl_day) as avg_daily_bbl
FROM hdl.oil_production
WHERE date_prod >= '2024-01-01'
GROUP BY DATE_TRUNC('month', date_prod), sub_region_natgas
ORDER BY 1, 3 DESC;

Field Reference

Field Name	Data Type	Description
date_prod	date	The date of production in YYYY-MM-DD format
sub_region_natgas	varchar	Subregion divisions based on natural gas production profiles
region_natgas	varchar	Region divisions based on natural gas production profiles
prod_oil_bbl_day	float	Daily oil production in barrels

hdl.short_term_forecast

Monthly well-level production forecasts (2-3 months ahead) with oil/gas volumes in daily and monthly units. Each row represents a forecast for a specific well and production month, with date_forecast_run indicating when the forecast model was executed for tracking forecast vintages. Supports aggregation by county, operator, region, and sub-region for supply planning and trend analysis.

Note

This table uses MCF for gas units. To convert to BCF: prod_dry_gas_mcf_day / 1000000. When querying US-only production, filter with WHERE region_natgas != 'Canada'.

Table ID: hdl.short_term_forecast

Sample Queries

-- Forecast production by sub-region (U.S. only)
SELECT
    date_prod,
    sub_region_natgas,
    SUM(prod_dry_gas_mcf_day) AS total_gas_mcf_day,
    SUM(prod_oil_bbl_day) AS total_oil_bbl_day
FROM hdl.short_term_forecast
WHERE region_natgas != 'Canada'
  AND date_prod >= CURRENT_DATE
GROUP BY date_prod, sub_region_natgas
ORDER BY date_prod, total_gas_mcf_day DESC
LIMIT 50;

-- Operator forecast comparison
SELECT
    operator,
    date_prod,
    SUM(prod_oil_bbl_day) AS total_oil_bbl_day,
    SUM(prod_dry_gas_mcf_day) AS total_gas_mcf_day,
    COUNT(DISTINCT well_id) AS well_count
FROM hdl.short_term_forecast
WHERE operator IN ('EOG RESOURCES', 'PIONEER NATURAL')
  AND date_prod >= CURRENT_DATE
GROUP BY operator, date_prod
ORDER BY date_prod, operator;

Field Reference

Field Name	Data Type	Description
well_id	varchar	Unique well identifier. Example: 0014062007000
date_prod	date	Production forecast month. Example: 2025-02-01
date_forecast_run	date	Date the forecast model was executed. Example: 2026-01-25
prod_dry_gas_mcf_day	float	Forecasted daily dry gas production (MCF/day). Example: 0.297323
prod_dry_gas_mcf_month	float	Forecasted monthly dry gas production (MCF/month). Example: 9.217
prod_oil_bbl_day	float	Forecasted daily oil production (BBL/day)
prod_oil_bbl_month	float	Forecasted monthly oil production (BBL/month)
operator	varchar	Well operator. Example: PINE CLIFF ENERGY LTD
state_code	varchar	State/province code. Example: TX
county	varchar	County name
region_natgas	varchar	Natural gas region (west, northeast, gulf, Canada)
sub_region_natgas	varchar	Sub-region (NE PA, West - TX, Haynesville - LA, AB)

hdl.short_term_forecast_declines

Well-level decline curves combining state-reported production history with modeled forecasts. Each row is one well-month observation with both production volumes and metadata about whether values are state-reported or modeled. Use for type-curve analysis, decline rate calculations, EUR estimation, and distinguishing between actual vs forecasted production. Requires filtering by date_forecast_run to select a specific forecast vintage.

Note

Use the modeled field to distinguish between state-reported actuals (false) and forecast values (true). Use hdl.short_term_forecast_history_dates to discover available date_forecast_run values.

Table ID: hdl.short_term_forecast_declines

Sample Queries

-- Well decline curves for a subregion
SELECT production_month, AVG(prod_wet_gas_mcf_day) as avg_gas_day
FROM hdl.short_term_forecast_declines
WHERE sub_region_natgas = 'Haynesville - LA'
  AND production_month BETWEEN 1 AND 60
GROUP BY production_month
ORDER BY production_month;

-- Latest forecast vintage's decline data
SELECT *
FROM hdl.short_term_forecast_declines
WHERE date_forecast_run = (
    SELECT MAX(date_forecast_run)
    FROM hdl.short_term_forecast_declines
)
LIMIT 100;

-- Compare latest vs previous forecast vintage for a subregion
WITH vintages AS (
    SELECT DISTINCT date_forecast_run
    FROM hdl.short_term_forecast_declines
    ORDER BY date_forecast_run DESC
    LIMIT 2
)
SELECT d.date_forecast_run, d.production_month,
       AVG(d.prod_wet_gas_mcf_day) AS avg_gas_day
FROM hdl.short_term_forecast_declines d
WHERE d.date_forecast_run IN (SELECT date_forecast_run FROM vintages)
  AND d.sub_region_natgas = 'Haynesville - LA'
GROUP BY d.date_forecast_run, d.production_month
ORDER BY d.date_forecast_run, d.production_month;

Field Reference

Field Name	Data Type	Description
well_id	varchar	A well identifier
date_prod	date	The date of production in YYYY-MM-DD format
prod_wet_gas_mcf_month	float	Monthly wet gas production in thousand cubic feet
prod_oil_bbl_month	float	Monthly oil production in barrels
prod_wet_gas_mcf_day	float	Daily wet gas production in thousand cubic feet
prod_oil_bbl_day	float	Daily oil production in barrels
date_completion	date	Date when well completion operations finished
first_production_month	date	Date of first production month
production_month	int	The number of months this well has been producing
county	varchar	The county the well is in
operator	varchar	A well operator
sub_region_natgas	varchar	Subregion divisions based on natural gas production profiles
region_natgas	varchar	Region divisions based on natural gas production profiles
date_forecast_run	date	The date the short term forecast was simulated on in YYYY-MM-DD format
modeled	bit	Indicates whether the production values come from the forecast model (true) or state-reported data (false)
state_reported	bit	Whether the state published any production numbers for this well
state_code	varchar	A state abbreviation

hdl.short_term_forecast_history

Historical snapshots of well-level short-term production forecasts (backcasts). Each row is one well-month from a specific forecast run, allowing comparison of how forecasts evolved over time. Requires a date_forecast_run filter. Use for forecast accuracy analysis, model validation, and tracking how production estimates changed between forecast vintages.

Note

Use hdl.short_term_forecast_history_dates to discover available date_forecast_run values before filtering this table.

Table ID: hdl.short_term_forecast_history

Sample Queries

-- Backcast for a specific forecast run
SELECT date_prod, sub_region_natgas,
       SUM(prod_wet_gas_mcf_day) as total_gas_mcf_day
FROM hdl.short_term_forecast_history
WHERE date_forecast_run = '2025-01-25'
GROUP BY date_prod, sub_region_natgas
ORDER BY date_prod;

-- Aggregated latest vintage by subregion
SELECT date_prod, sub_region_natgas,
       SUM(prod_wet_gas_mcf_day) AS total_gas_mcf_day,
       SUM(prod_oil_bbl_day) AS total_oil_bbl_day
FROM hdl.short_term_forecast_history
WHERE date_forecast_run = (
    SELECT MAX(date_forecast_run)
    FROM hdl.short_term_forecast_history
)
GROUP BY date_prod, sub_region_natgas
ORDER BY date_prod, sub_region_natgas;

Field Reference

Field Name	Data Type	Description
well_id	varchar	A well identifier
date_prod	date	The date of production in YYYY-MM-DD format
prod_wet_gas_mcf_month	float	Monthly wet gas production in thousand cubic feet
prod_oil_bbl_month	float	Monthly oil production in barrels
prod_wet_gas_mcf_day	float	Daily wet gas production in thousand cubic feet
prod_oil_bbl_day	float	Daily oil production in barrels
date_forecast_run	date	The date the short term forecast was simulated on in YYYY-MM-DD format
state_code	varchar	A state abbreviation
region_natgas	varchar	Region divisions based on natural gas production profiles
sub_region_natgas	varchar	Subregion divisions based on natural gas production profiles
county	varchar	The county the well is in
operator	varchar	A well operator

hdl.short_term_forecast_aggregated_history

Pre-aggregated subregion-level history of all short-term forecast runs. Each row is one subregion-month from a specific forecast run. Use for quickly comparing regional production forecast trajectories across multiple forecast vintages without needing to aggregate well-level data.

Note

Use hdl.short_term_forecast_history_dates to discover available date_forecast_run values.

Table ID: hdl.short_term_forecast_aggregated_history

Sample Queries

-- Compare forecast vintages for a subregion
SELECT date_forecast_run, date_prod, prod_dry_gas_bcf_day
FROM hdl.short_term_forecast_aggregated_history
WHERE sub_region_natgas = 'Haynesville - LA'
  AND date_prod >= '2025-01-01'
ORDER BY date_forecast_run, date_prod;

-- Latest forecast vintage by subregion
SELECT *
FROM hdl.short_term_forecast_aggregated_history
WHERE date_forecast_run = (
    SELECT MAX(date_forecast_run)
    FROM hdl.short_term_forecast_aggregated_history
);

-- Compare latest two vintages side by side for a subregion
WITH vintages AS (
    SELECT DISTINCT date_forecast_run
    FROM hdl.short_term_forecast_aggregated_history
    ORDER BY date_forecast_run DESC
    LIMIT 2
)
SELECT a.date_forecast_run, a.date_prod, a.sub_region_natgas,
       a.prod_dry_gas_bcf_day, a.prod_oil_mbbl_day
FROM hdl.short_term_forecast_aggregated_history a
WHERE a.date_forecast_run IN (SELECT date_forecast_run FROM vintages)
  AND a.sub_region_natgas = 'Haynesville - LA'
ORDER BY a.sub_region_natgas, a.date_prod, a.date_forecast_run;

Field Reference

Field Name	Data Type	Description
date_prod	datetime	The date of production in YYYY-MM-DD format
prod_dry_gas_bcf_day	float	Daily dry gas production in billion cubic feet
prod_oil_mbbl_day	float	Daily oil production in thousand barrels
sub_region_natgas	varchar	Subregion divisions based on natural gas production profiles
date_forecast_run	date	The date the short term forecast was simulated on in YYYY-MM-DD format

hdl.short_term_forecast_history_dates

Reference table listing all available short-term forecast run dates. Each row is one date on which the STF model was executed. Use for discovering available forecast vintages before querying hdl.short_term_forecast_history, hdl.short_term_forecast_aggregated_history, or hdl.short_term_forecast_declines.

Table ID: hdl.short_term_forecast_history_dates

Sample Queries

-- List available forecast run dates
SELECT date_forecast_run
FROM hdl.short_term_forecast_history_dates
ORDER BY date_forecast_run DESC;

-- Latest 5 forecast run dates
SELECT date_forecast_run
FROM hdl.short_term_forecast_history_dates
ORDER BY date_forecast_run DESC
LIMIT 5;

Field Reference

Field Name	Data Type	Description
date_forecast_run	date	The date the short term forecast was simulated on in YYYY-MM-DD format

hdl.complete_report_date

Data completeness dates by subregion, indicating the last date for which each data type (pipeline scrapes, completions, gas production, oil production) is considered complete and reliable. Use for determining safe date ranges for analysis and understanding data latency by region. Query this before analyzing production data to avoid using incomplete reporting periods.

Table ID: hdl.complete_report_date

Sample Queries

-- Check data completeness by subregion
SELECT sub_region_natgas, date_pipe_scrape_report,
       date_gas_prod_report, date_oil_prod_report
FROM hdl.complete_report_date
ORDER BY sub_region_natgas;

-- Use in a subquery to cap other tables' date ranges
SELECT o.*
FROM hdl.oil_production o
INNER JOIN hdl.complete_report_date c
    ON o.sub_region_natgas = c.sub_region_natgas
WHERE o.date_prod <= CAST(c.date_oil_prod_report AS date);

Field Reference

Field Name	Data Type	Description
sub_region_natgas	varchar	Subregion divisions based on natural gas production profiles
date_pipe_scrape_report	date	The complete date for state data used by the daily production regression model which is derived from daily pipe scrapes
date_completion_report	varchar	Well completions information up to this date is complete
date_gas_prod_report	varchar	Date up to which state-reported gas production data is complete
date_oil_prod_report	varchar	Date up to which state-reported oil production data is complete

hdl.lower_48_stf_filled

Regional short-term forecast with plug regions filled in, providing daily production by subregion for both gas and oil. This table provides subregion-level breakdowns including modeled “plug” regions that estimate production for states not covered by the core STF model. Because it contains every subregion, a simple SUM() across all subregions replicates the national total that was previously available in the separate lower_48_total_stf endpoint. Use for regional supply analysis, comparing STF production across subregions, and deriving headline U.S. gas supply figures.

Note

Replicating lower_48_total_stf: To get the exact same national total previously available from /v4/lower_48_total_stf, simply SUM(prod_dry_gas_bcf_day) grouped by date_prod across all subregions.

Table ID: hdl.lower_48_stf_filled

Sample Queries

-- Regional STF with plug regions
SELECT date_prod, sub_region_natgas, region_natgas,
       prod_dry_gas_bcf_day, prod_oil_mbbl_day
FROM hdl.lower_48_stf_filled
WHERE date_prod >= '2025-01-01'
ORDER BY date_prod, sub_region_natgas;

-- Exact replica of lower_48_total_stf (national daily total)
SELECT date_prod,
       SUM(prod_dry_gas_bcf_day) AS prod_dry_gas_bcf_day
FROM hdl.lower_48_stf_filled
GROUP BY date_prod
ORDER BY date_prod;

-- Latest national total
SELECT date_prod,
       SUM(prod_dry_gas_bcf_day) AS prod_dry_gas_bcf_day
FROM hdl.lower_48_stf_filled
WHERE date_prod = (SELECT MAX(date_prod) FROM hdl.lower_48_stf_filled)
GROUP BY date_prod;

-- Monthly average national total (temporal aggregation uses AVG of daily sums)
WITH daily_totals AS (
    SELECT date_prod,
           SUM(prod_dry_gas_bcf_day) AS total_bcf_day
    FROM hdl.lower_48_stf_filled
    GROUP BY date_prod
)
SELECT DATE_TRUNC('month', date_prod) AS month,
       AVG(total_bcf_day) AS avg_bcf_day
FROM daily_totals
GROUP BY DATE_TRUNC('month', date_prod)
ORDER BY month;

Field Reference

Field Name	Data Type	Description
date_prod	date	The date of production in YYYY-MM-DD format
sub_region_natgas	varchar	Subregion divisions based on natural gas production profiles
region_natgas	varchar	Region divisions based on natural gas production profiles
prod_dry_gas_bcf_day	float	Daily dry gas production in billion cubic feet
prod_oil_mbbl_day	float	Daily oil production in thousand barrels

hdl.geowells

Lightweight well master with geographic coordinates and key attributes. A slimmer alternative to hdl.wells focused on location data and essential metadata. Each row is one well. Use for geographic mapping, spatial queries, and lightweight well lookups when the full wells table is not needed.

Table ID: hdl.geowells

Sample Queries

-- Well locations for mapping
SELECT well_id, lat_surface, lon_surface, operator,
       sub_region_natgas, well_status
FROM hdl.geowells
WHERE region_natgas = 'gulf'
  AND date_spud >= '2024-01-01'
LIMIT 1000;

-- Wells spud in the last 90 days
SELECT *
FROM hdl.geowells
WHERE date_spud >= CURRENT_DATE - INTERVAL '90 days'
ORDER BY date_spud DESC;

Field Reference

Field Name	Data Type	Description
well_id	varchar	A well identifier
lat_surface	decimal(9,6)	Latitude of the wellbore surface location
lon_surface	decimal(9,6)	Longitude of the wellbore surface location
county	varchar	The county the well is in
date_spud	date	Spud date from well drilling permit in YYYY-MM-DD format
depth_tvd	decimal(10,2)	True vertical depth of the wellbore
horizontal_length	decimal(10,2)	Horizontal length of the wellbore
operator	varchar	A well operator
region_natgas	varchar	Region divisions based on natural gas production profiles
sub_region_natgas	varchar	Subregion divisions based on natural gas production profiles
state_code	varchar	A state abbreviation
wellpad_id	bigint	The unique identifier for the wellpad
well_status	varchar	The status of the well

hdl.rigs

Daily drilling rig activity at wellpad-day grain tracking active rigs across North American oil and gas operations. Each row represents one active drilling rig working on a specific wellpad for a single day. Average drilling duration is 44-73 days per wellpad. Drilling is the first step in well development, leading production by 3-6 months. Use COUNT(*) grouped by date_observed for daily rig counts; use COUNT(DISTINCT wellpad_id) to count unique wellpads drilled.

Warning

Regional Coverage Varies: Gulf/West regions have data from Sept 2020; Northeast from March 2021; Canada from May 2024. Always verify date coverage by region_natgas before cross-regional comparisons.

Table ID: hdl.rigs

Sample Queries

-- Daily active rig count (time series)
SELECT
    date_observed,
    COUNT(*) as active_rigs
FROM hdl.rigs
WHERE date_observed >= '2024-01-01'
GROUP BY date_observed
ORDER BY date_observed;

-- Permian basin rig activity by operator
SELECT
    operator,
    COUNT(*) as rig_days,
    COUNT(DISTINCT wellpad_id) as wellpads_drilled
FROM hdl.rigs
WHERE sub_region_natgas IN ('West - TX', 'Permian-NM')
  AND date_observed >= '2024-01-01'
GROUP BY operator
ORDER BY rig_days DESC
LIMIT 20;

Note

Recreating /rigs/latest: The /rigs/latest API endpoint returns only the most recent date’s rig data per region. Each region independently returns its most current data, accounting for staggered update schedules (e.g., Canada may lag the U.S.).

-- Exact equivalent of /rigs/latest (per-region latest)
WITH latest_dates AS (
    SELECT region_natgas, MAX(date_observed) AS max_date
    FROM hdl.rigs
    GROUP BY region_natgas
)
SELECT r.*
FROM hdl.rigs r
INNER JOIN latest_dates ld
    ON r.region_natgas = ld.region_natgas
   AND r.date_observed = ld.max_date;

-- Latest U.S.-only (single date, excludes Canada)
SELECT *
FROM hdl.rigs
WHERE date_observed = (
    SELECT MAX(date_observed)
    FROM hdl.rigs
    WHERE region_natgas != 'Canada'
);

Field Reference

Field Name	Data Type	Description
wellpad_id	varchar	Unique identifier for the wellpad being drilled. Example: 1088
date_observed	date	Date of rig activity. Example: 2024-03-24
lat	float	Wellpad latitude in decimal degrees. Example: 32.722146
long	float	Wellpad longitude in decimal degrees. Example: -93.619682
operator	varchar	E&P company operating the well (mineral rights owner). Example: CIVITAS RESOURCES
county	varchar	County name. Example: WELD
state_code	varchar	State/province 2-letter abbreviation. Example: TX
sub_region_natgas	varchar	Detailed basin/area classification. Example: West - TX
region_natgas	varchar	High-level geographic region (gulf, west, midwest, northeast, Canada). Example: gulf
rig_class	varchar	Well target commodity type (oil, gas). Blank for Canada. Example: oil

hdl.fraccrews

Daily hydraulic fracturing (frac) crew activity at wellpad-day grain tracking active frac crews across North American oil and gas operations. Each row represents one active frac crew working on a specific wellpad for a single day. Average frac duration is 13-21 days per wellpad. Fracking is the completion phase that follows drilling, typically occurring 1-3 months after drilling and leading production by 1-3 months. Use COUNT(*) grouped by date_frac for daily crew counts.

Warning

Regional Coverage Varies: Gulf/West regions have data from Sept 2020; Northeast from March 2021; Canada from May 2024. Canadian records may have blank service_company and frac_class fields.

Table ID: hdl.fraccrews

Sample Queries

-- Daily active frac crew count (time series)
SELECT
    date_frac,
    COUNT(*) as active_crews
FROM hdl.fraccrews
WHERE date_frac >= '2024-01-01'
GROUP BY date_frac
ORDER BY date_frac;

-- Frac activity by service company
SELECT
    service_company,
    COUNT(*) as crew_days,
    COUNT(DISTINCT wellpad_id) as wellpads_fracked
FROM hdl.fraccrews
WHERE date_frac >= '2024-01-01'
  AND service_company != ''
GROUP BY service_company
ORDER BY crew_days DESC
LIMIT 15;

Note

Recreating /fraccrews/latest: The /fraccrews/latest API endpoint returns only the most recent date’s frac crew data per region. Each region independently returns its most current data, accounting for staggered update schedules (e.g., Canada may lag the U.S.).

-- Exact equivalent of /fraccrews/latest (per-region latest)
WITH latest_dates AS (
    SELECT region_natgas, MAX(date_frac) AS max_date
    FROM hdl.fraccrews
    GROUP BY region_natgas
)
SELECT f.*
FROM hdl.fraccrews f
INNER JOIN latest_dates ld
    ON f.region_natgas = ld.region_natgas
   AND f.date_frac    = ld.max_date;

-- Latest U.S.-only (single date, excludes Canada)
SELECT *
FROM hdl.fraccrews
WHERE date_frac = (
    SELECT MAX(date_frac)
    FROM hdl.fraccrews
    WHERE region_natgas != 'Canada'
);

Field Reference

Field Name	Data Type	Description
wellpad_id	varchar	Unique identifier for the wellpad being fracked. Example: 916
date_frac	date	Date of frac crew activity. Example: 2021-10-30
lat	float	Wellpad latitude in decimal degrees. Example: 40.37
long	float	Wellpad longitude in decimal degrees. Example: -104.427
operator	varchar	E&P company operating the well (mineral rights owner). Example: CIVITAS RESOURCES
service_company	varchar	Frac service provider performing the work. Blank for Canada. Example: Liberty
county	varchar	County name. Example: WELD
state_code	varchar	State/province 2-letter abbreviation. Example: CO
sub_region_natgas	varchar	Detailed basin/area classification. Example: Colorado wo SJ
region_natgas	varchar	High-level geographic region (gulf, northeast, west, midwest, Canada). Example: west
frac_class	varchar	Well target commodity type (oil, gas). Blank for Canada. Example: oil

hdl.completions

Well-level completion records showing individual wells fractured as part of wellpad-level frac jobs. Each row represents a single well that was completed during a wellpad frac operation. The start_date and end_date represent the timing of the frac job at the wellpad level—multiple wells from the same wellpad share these dates. Always filter by end_date to measure completion activity over time, as this is when wells become ready for production.

Note

Date Field Usage: Use end_date (not start_date) for all completion activity trending and counts. This represents when the wellpad frac job was finished and wells are ready for production.

Table ID: hdl.completions

Sample Queries

-- Monthly completions by region (use end_date)
SELECT
    DATE_TRUNC('month', end_date) as completion_month,
    region,
    COUNT(*) as wells_completed
FROM hdl.completions
WHERE end_date >= '2024-01-01'
  AND end_date < '2025-01-01'
GROUP BY DATE_TRUNC('month', end_date), region
ORDER BY completion_month, wells_completed DESC;

-- Top operators by completion count
SELECT
    operator_name,
    completion_class,
    COUNT(*) as wells_completed
FROM hdl.completions
WHERE end_date >= '2024-01-01'
GROUP BY operator_name, completion_class
ORDER BY wells_completed DESC
LIMIT 20;

Field Reference

Field Name	Data Type	Description
well_id	bigint	Unique identifier for each individual well completed. Example: 500507580
completion_class	varchar	Oil or gas classification based on EIA-defined production ratios. Example: oil
county	varchar	County where the frac job occurred. Example: ARAPAHOE
date_completion_end	date	Date when the wellpad frac job ended (use for activity trending). Example: 2020-10-01
date_completion_start	date	Date when the wellpad frac job started. Example: 2020-09-15
operator	varchar	Company that performed the frac job. Example: 1776 ENERGY OPERATORS LLC
region_natgas	varchar	Natural gas production region. Example: gulf
state_code	varchar	State abbreviation. Example: CO
sub_region_natgas	varchar	Specific sub-region/basin. Example: Permian
wellpad_id	varchar	Identifier for the wellpad where the frac job was performed. Example: 2686221

hdl.ducs_by_operator

Daily snapshot of Drilled but Uncompleted (DUC) well inventory at county-operator-day grain. Each row represents the count of DUCs for a single operator in a specific county on a given observation date. DUCs are wells that have been drilled but not yet completed (fractured). Rising DUC inventory indicates drilling outpacing completions (rig/frac ratio >3); falling inventory indicates completions outpacing drilling (ratio <2.5). Use SUM(ducs_count) for aggregation and preserve daily granularity for time-series visualization.

Note

Time-Series Visualization: Never aggregate DUC inventory into weekly/monthly sums—this is a daily snapshot, not a flow. Display as a daily time-series or use AVG() for period averages.

Table ID: hdl.ducs_by_operator

Sample Queries

-- Current total DUC inventory (most recent snapshot)
SELECT
    SUM(ducs_count) as total_ducs
FROM hdl.ducs_by_operator
WHERE date_observed = (SELECT MAX(date_observed) FROM hdl.ducs_by_operator);

-- DUC inventory time-series by region (daily granularity)
SELECT
    date_observed,
    region_natgas,
    SUM(ducs_count) as duc_inventory
FROM hdl.ducs_by_operator
WHERE date_observed >= '2024-01-01'
GROUP BY date_observed, region_natgas
ORDER BY date_observed, duc_inventory DESC;

Field Reference

Field Name	Data Type	Description
county	varchar	County name. Example: HENDERSON
date_observed	date	Observation date for the DUC inventory snapshot. Example: 2024-01-01
ducs_count	int	Number of drilled but uncompleted wells for this operator/county. Example: 1
operator	varchar	Operator name. Example: 1776 ENERGY OPERATORS LLC
region_natgas	varchar	High-level geographic region. Example: gulf
state_code	varchar	State abbreviation. Example: CO
sub_region_natgas	varchar	Detailed basin/area classification. Example: Central - TX

hdl.daily_fracked_feet

Daily fracked feet by subregion, tracking the rate of hydraulic fracturing completion activity. Each row represents the total lateral feet fracked on a given day within a subregion. Use for monitoring frac intensity trends, comparing basin-level completion rates, and correlating frac activity with production outcomes.

Table ID: hdl.daily_fracked_feet

Sample Queries

-- Daily fracked feet by subregion
SELECT date_frac, sub_region_natgas, fracked_feet
FROM hdl.daily_fracked_feet
WHERE date_frac >= '2024-01-01'
ORDER BY date_frac, sub_region_natgas;

-- Latest day's fracked feet by subregion
SELECT *
FROM hdl.daily_fracked_feet
WHERE date_frac = (SELECT MAX(date_frac) FROM hdl.daily_fracked_feet);

-- Latest per subregion (if subregions update at different times)
WITH latest AS (
    SELECT sub_region_natgas, MAX(date_frac) AS max_date
    FROM hdl.daily_fracked_feet
    GROUP BY sub_region_natgas
)
SELECT d.*
FROM hdl.daily_fracked_feet d
INNER JOIN latest l
    ON d.sub_region_natgas = l.sub_region_natgas
   AND d.date_frac         = l.max_date;

Field Reference

Field Name	Data Type	Description
date_frac	date	The date observed via satellite that the well crew completed fracking in YYYY-MM-DD format
region_natgas	varchar	Region divisions based on natural gas production profiles
fracked_feet	float	Daily length of feet fracked for wells
sub_region_natgas	varchar	Subregion divisions based on natural gas production profiles

hdl.operator_classification

Reference table classifying well operators as public or private companies, with stock ticker symbols for public operators. Use for segmenting drilling/production analysis by operator type and linking to financial data via ticker. Join to other hdl tables on the operator column to add public/private segmentation to any analysis.

Table ID: hdl.operator_classification

Sample Queries

-- List all public operators with tickers
SELECT operator, ticker
FROM hdl.operator_classification
WHERE public_or_private = 'public'
ORDER BY operator;

-- Count operators by classification
SELECT public_or_private, COUNT(*) AS operator_count
FROM hdl.operator_classification
GROUP BY public_or_private;

Field Reference

Field Name	Data Type	Description
operator	varchar	A well operator name
ticker	varchar	The stock ticker of the operator (null for private companies)
public_or_private	varchar	Whether the operator is public or private

hdl.wellpad_status

Daily interpolated wellpad lifecycle status (Drilling or Fracking) by date, operator, and geography. Each row represents the status of a wellpad on a given day. Use for tracking wellpad progression through the drilling-to-completion lifecycle, counting active wellpads by status, and analyzing operator development timing.

Table ID: hdl.wellpad_status

Sample Queries

-- Count of wellpads by status on a given date
SELECT date_status, wellpad_status, region_natgas, COUNT(*) as pad_count
FROM hdl.wellpad_status
WHERE date_status >= '2024-01-01'
GROUP BY date_status, wellpad_status, region_natgas
ORDER BY date_status;

-- Latest status snapshot (global max date)
SELECT *
FROM hdl.wellpad_status
WHERE date_status = (SELECT MAX(date_status) FROM hdl.wellpad_status);

-- Latest status per region (if regions update at different times)
WITH latest AS (
    SELECT region_natgas, MAX(date_status) AS max_date
    FROM hdl.wellpad_status
    GROUP BY region_natgas
)
SELECT w.*
FROM hdl.wellpad_status w
INNER JOIN latest l
    ON w.region_natgas = l.region_natgas
   AND w.date_status   = l.max_date;

Field Reference

Field Name	Data Type	Description
date_status	date	The date the wellpad status was applied in YYYY-MM-DD format
wellpad_status	varchar	The status of the wellpad such as Drilling or Fracking
operator	varchar	A well operator
county	varchar	The county the wellpad is in
state_code	varchar	A state abbreviation
sub_region_natgas	varchar	Subregion divisions based on natural gas production profiles
region_natgas	varchar	Region divisions based on natural gas production profiles
wellpad_id	varchar	The unique identifier for the wellpad

hdl.regions

Reference table mapping counties to SynMax natural gas region and subregion classifications. Each row represents a unique county-to-region mapping. Use for joining geographic context to other datasets, building region filter dropdowns, or looking up which region/subregion a county belongs to.

Table ID: hdl.regions

Sample Queries

-- List all subregions in a region
SELECT DISTINCT sub_region_natgas, state_code
FROM hdl.regions
WHERE region_natgas = 'gulf'
ORDER BY sub_region_natgas;

-- Find which region a county belongs to
SELECT state_code, county, sub_region_natgas, region_natgas
FROM hdl.regions
WHERE county = 'WEBB'
  AND state_code = 'TX';

Field Reference

Field Name	Data Type	Description
state_code	varchar	A state abbreviation
sub_region_natgas	varchar	Subregion divisions based on natural gas production profiles
region_natgas	varchar	Region divisions based on natural gas production profiles
county	varchar	The county name

hdl.glossary

Reference table of available filter parameter values for all Hyperion API endpoints. Each row represents a unique combination of filter values available for a given target_function (API endpoint). Use this table to discover valid operators, regions, counties, and other filter values before querying the corresponding data table. Not all columns are populated for every target_function; irrelevant columns will be NULL (e.g., service_company is only populated for fraccrews).

Table ID: hdl.glossary

Sample Queries

-- Get all valid operators for rigs data
SELECT DISTINCT operator
FROM hdl.glossary
WHERE target_function = 'rigs'
  AND operator IS NOT NULL
ORDER BY operator;

-- Get all valid sub-regions for short term forecast
SELECT DISTINCT sub_region_natgas
FROM hdl.glossary
WHERE target_function = 'shorttermforecast'
  AND sub_region_natgas IS NOT NULL
ORDER BY sub_region_natgas;

-- List all target functions (endpoints) available
SELECT DISTINCT target_function
FROM hdl.glossary
ORDER BY target_function;

-- Get available forecast run dates
SELECT DISTINCT date_forecast_run
FROM hdl.glossary
WHERE target_function = 'shorttermforecast'
  AND date_forecast_run IS NOT NULL
ORDER BY date_forecast_run DESC;

Field Reference

Field Name	Data Type	Description
target_function	nvarchar(50)	The API endpoint / data table these filter values apply to (e.g. ‘rigs’, ‘fraccrews’, ‘wells’, ‘shorttermforecast’)
region_natgas	nvarchar(15)	Region divisions based on natural gas production profiles
state_code	nvarchar(15)	A state abbreviation
sub_region_natgas	nvarchar(30)	Subregion divisions based on natural gas production profiles
county	nvarchar(50)	The county the well is in
operator	nvarchar(120)	A well operator
service_company	nvarchar(120)	The service company for the well
completion_class	nvarchar(6)	Oil/Gas classification based on EIA-defined gas/oil production ratios
rig_class	nvarchar(6)	Oil/Gas classification based on EIA-defined gas/oil production ratios
frac_class	nvarchar(6)	Oil/Gas classification based on EIA-defined gas/oil production ratios
date_forecast_run	nvarchar(10)	The date the short term forecast was simulated on in YYYY-MM-DD format
modeled	nvarchar(6)	Whether the record is modeled (‘True’) or state-reported (‘False’)
rec_del	float	Record deletion flag
category	nvarchar(20)	Pipeline location category (e.g. Production, LDC, Power, Storage, LNG, Interconnect)
source	nvarchar(20)	Pipeline data source/cycle (e.g. Timely, Evening, ID1, ID2, ID3)

hdl.pipeline_flow

Simplified, cleaned daily pipeline nomination dataset containing flow volumes, capacities, and location metadata for interstate natural gas pipeline points across the United States. Each row represents one day’s best flow data for a specific pipeline location, enabling supply/demand analysis via location_category filtering (Production, LNG, Power, Industrial, Storage, Interconnect), directional flow separation via rec_del (1=receipt into pipeline, -1=delivery out), and time-series tracking via smx_location_id. Supports regional aggregation, capacity utilization analysis, and LNG export feedgas monitoring.

Table ID: hdl.pipeline_flow

Sample Queries

-- Daily LNG export feedgas by terminal
SELECT
    gas_day,
    pipeline_name,
    location_name,
    SUM(total_scheduled_quantity) AS total_feedgas
FROM hdl.pipeline_flow
WHERE location_category = 'LNG'
  AND rec_del = -1
  AND gas_day >= '2024-01-01'
GROUP BY gas_day, pipeline_name, location_name
ORDER BY gas_day, pipeline_name;

-- Identify constrained points (high utilization)
SELECT
    gas_day,
    pipeline_name,
    location_name,
    location_category,
    total_scheduled_quantity,
    operating_capacity,
    (total_scheduled_quantity / NULLIF(operating_capacity, 0)) * 100 AS utilization_pct
FROM hdl.pipeline_flow
WHERE operating_capacity > 0
  AND gas_day = '2024-12-01'
  AND (total_scheduled_quantity / NULLIF(operating_capacity, 0)) > 0.9
ORDER BY utilization_pct DESC;

Field Reference

Field Name	Data Type	Description
smx_location_id	int	Unique SynMax location identifier. One smx_location_id = one physical pipeline point.
gas_day	date	The gas day this data represents (standard pipeline day).
cycle_lookup	varchar	Standardized nomination cycle (Timely, Evening, Intraday1, etc.).
cycle_desc	varchar	Raw pipeline-reported cycle description.
posting_dt	datetime	When pipeline published this data.
created_dt	datetime	When SynMax ingested this data.
pipeline_name	varchar	Name of the pipeline. Example: Tennessee Gas
location_name	varchar	Name of the specific point. Example: STRATTON RIDGE
smx_tsp_id	varchar	SynMax-standardized pipeline ID.
location_county	varchar	County name.
location_state_ab	varchar	State abbreviation (TX, LA, PA, etc.).
location_county_composite	varchar	Combined “County (State)” format.
location_latitude	float	Latitude coordinate.
location_longitude	float	Longitude coordinate.
region_nat_gas	varchar	Regional classification (reflects regional source of gas for production points).
sub_region_nat_gas	varchar	Sub-regional classification.
rec_del	int	Canonical receipt/delivery flag: 1 = Receipt (gas into pipeline), -1 = Delivery (gas out of pipeline).
flow_indicator	varchar	Directional context from pipeline: R = Receipt, D = Delivery, BD = Bi-Directional.
flow_indicator_long	varchar	Full description (e.g., “Receipt Location”, “Delivery Location”).
location_category	varchar	Functional role: Production, Interconnect, LDC, Power, Industrial, Storage, LNG, Compressor, etc.
total_scheduled_quantity	decimal	Primary flow metric - nominated/scheduled gas volume for this day.
design_capacity	decimal	Theoretical maximum built capacity.
operating_capacity	decimal	Allowed maximum for this gas day.
operationally_available_capacity	decimal	Remaining schedulable capacity (≈ operating_capacity − total_scheduled_quantity).
mean_basis_desc	varchar	Unit of measurement: MMBtu (most common), MCF, or GJ.
interconnect_location_id	varchar	ID of interconnected pipeline/facility.
interconnect_company_name	varchar	Name of interconnected company.
interconnect_location_name	varchar	Name of interconnected location.
source	varchar	Data source identifier (pipeline-specific).

hdl.pipeline_locations

Master reported flow point dataset containing location metadata for interstate natural gas pipeline points across the United States. Each row represents one reported data point for a specific pipeline location with one smx_location_id per physical point. Use this table for location lookups and filtering, then join to hdl.pipeline_flow_only for flow data. Metadata is updated periodically as points are better classified or geographic data is added, providing the latest location info without re-querying flow data.

Table ID: hdl.pipeline_locations

Sample Queries

-- Search for points by location name pattern
SELECT DISTINCT
    smx_location_id,
    pipeline_name,
    location_name,
    location_category,
    location_county_composite
FROM hdl.pipeline_locations
WHERE location_name ILIKE '%STRATTON%'
   OR location_name ILIKE '%FREEPORT%'
LIMIT 50;

-- Count locations by category and state
SELECT
    location_category,
    location_state_ab,
    COUNT(*) AS location_count
FROM hdl.pipeline_locations
WHERE location_category IN ('Production', 'LNG', 'Power', 'Storage')
GROUP BY location_category, location_state_ab
ORDER BY location_category, location_count DESC;

Field Reference

Field Name	Data Type	Description
smx_location_id	int	Unique SynMax location identifier. One smx_location_id = one physical pipeline point. Example: 69259
pipeline_name	varchar	Name of the pipeline. Example: Cheyenne Connector
smx_tsp_id	varchar	SynMax-standardized pipeline ID. Example: 100670676
location_name	varchar	Name of the specific point. Example: CONNECTOR/REX WELD
pipeline_location_id	varchar	Pipeline-specific location identifier as reported by operator. Example: 210
flow_indicator	varchar	Directional context: R = Receipt, D = Delivery, BD = Bi-Directional. Example: D
flow_indicator_short	varchar	Short flow indicator code. Example: D
flow_indicator_long	varchar	Full description (e.g., “Receipt Location”, “Delivery Location”, “Bi-Directional”).
location_category	varchar	Functional role: Production, Interconnect, LDC, Power, Industrial, Storage, LNG, Compressor, etc. Example: Interconnect
rec_del	int	Canonical receipt/delivery flag: 1 = Receipt (gas into pipeline), -1 = Delivery (gas out of pipeline). Example: 1
region_nat_gas	varchar	SynMax regional classification. Example: northeast
sub_region_nat_gas	varchar	SynMax sub-regional classification. Example: NE PA
location_county_composite	varchar	Combined “County (State)” format. Example: New Castle (DE)
location_county	varchar	County name. Example: New Castle
location_state_ab	varchar	State abbreviation (TX, LA, PA, etc.). OF represents Federal Offshore Gulf of Mexico. Example: DE
location_latitude	float	Latitude coordinate. Example: 39.8153
location_longitude	float	Longitude coordinate. Example: -75.4372
effective_dt	datetime	Date when this location became effective as reported by pipeline. Example: Oct 30 2013
inactive_dt	datetime	Date when this location was deactivated (if applicable). Example: Jul 29 2024
loc_status_code	varchar	Location status code (e.g., A for Active). Example: A
interconnect_location_id	varchar	ID of interconnected pipeline/facility. Example: 643195
interconnect_company_name	varchar	Name of interconnected company. Example: Columbia Gas Pipeline
interconnect_location_name	varchar	Name of interconnected location. Example: Claymont
update_dt	datetime	When this location metadata was last updated in SynMax. Example: Apr 1 2025

hdl.pipeline_flow_only

Simplified, cleaned daily pipeline nomination dataset containing flow volumes and capacities for natural gas pipeline points across the United States without location metadata. Each row represents one day’s best flow data for a specific pipeline location (gas_day). This table provides the same core data as hdl.pipeline_flow but with a streamlined schema—join to hdl.pipeline_locations using smx_location_id for location details. Ideal for time-series analysis, capacity utilization tracking, and regional aggregation workflows.

Note

Zero Values: If all four capacity metrics (total_scheduled_quantity, design_capacity, operating_capacity, operationally_available_capacity) are zero, interpret as inactive/not applicable, not an error.

Table ID: hdl.pipeline_flow_only

Sample Queries

-- Track LNG export feedgas with location join
SELECT
    f.gas_day,
    l.pipeline_name,
    l.location_name,
    SUM(f.total_scheduled_quantity) AS total_feedgas
FROM hdl.pipeline_flow_only f
JOIN hdl.pipeline_locations l ON f.smx_location_id = l.smx_location_id
WHERE l.location_category = 'LNG'
  AND l.rec_del = -1
  AND f.gas_day >= '2024-01-01'
GROUP BY f.gas_day, l.pipeline_name, l.location_name
ORDER BY f.gas_day, l.pipeline_name;

-- Pipeline capacity utilization with constrained points
SELECT
    f.gas_day,
    l.pipeline_name,
    l.location_name,
    l.location_category,
    f.total_scheduled_quantity,
    f.operating_capacity,
    (f.total_scheduled_quantity / NULLIF(f.operating_capacity, 0)) * 100 AS utilization_pct
FROM hdl.pipeline_flow_only f
JOIN hdl.pipeline_locations l ON f.smx_location_id = l.smx_location_id
WHERE f.operating_capacity > 0
  AND f.gas_day = '2024-12-01'
  AND (f.total_scheduled_quantity / NULLIF(f.operating_capacity, 0)) > 0.9
ORDER BY utilization_pct DESC;

Field Reference

Field Name	Data Type	Description
smx_location_id	int	Unique location identifier (join key to hdl.pipeline_locations). Example: 50669
gas_day	date	The gas day this data represents (standard pipeline day in CT). Example: 2026-01-30
cycle_lookup	varchar	Standardized nomination cycle (Timely, Evening, Intraday1, etc.). Example: Evening
cycle_desc	varchar	Raw pipeline-reported cycle description. Example: Evening
total_scheduled_quantity	decimal	Primary flow metric - nominated/scheduled gas volume for this day. Example: 112713.0000
design_capacity	decimal	Theoretical maximum built capacity. Example: 300000.0000
operating_capacity	decimal	Allowed maximum for this gas day. Example: 300000.0000
operationally_available_capacity	decimal	Remaining schedulable capacity (≈ operating_capacity − total_scheduled_quantity). Example: 187287.0000
mean_basis_desc	varchar	Unit of measurement: MMBtu (most common), MCF, or GJ. Example: MMBtu
source	varchar	Data source identifier (pipeline-specific). Example: kindermorgan
created_dt	datetime	When SynMax ingested this data (in UTC). Example: 2026-01-29 06:41:58
posting_dt	datetime	When pipeline published this data (in UTC). Example: 2026-01-29 02:49:00

hdl.recent_pipeline_notices

Curated, analysis-ready view of interstate natural gas pipeline notices covering force majeure events, Operational Flow Orders (OFOs), maintenance windows, capacity constraints, and operational alerts across approximately 150 U.S. interstate pipelines. Each row represents one notice posting. Supports monitoring of critical pipeline operational events, tracking notice supersession chains via prior_notice_identifier, and filtering for active/critical notices that may impact gas flows.

Table ID: hdl.recent_pipeline_notices

Sample Queries

-- Recent critical notices
SELECT
    posting_dt,
    pipeline_name,
    notice_type,
    critical_notice_description,
    notice_effective_datetime,
    notice_end_datetime
FROM hdl.recent_pipeline_notices
WHERE critical_notice_indicator = 'Y'
  AND posting_dt >= '2025-01-01'
ORDER BY posting_dt DESC
LIMIT 50;

-- Force majeure events by pipeline
SELECT
    pipeline_name,
    notice_id,
    notice_subject,
    notice_effective_datetime,
    notice_end_datetime,
    notice_status_description
FROM hdl.recent_pipeline_notices
WHERE notice_type = 'Force Majeure'
ORDER BY posting_dt DESC;

-- Count of notices by type and pipeline
SELECT
    pipeline_name,
    notice_type,
    COUNT(*) AS notice_count
FROM hdl.recent_pipeline_notices
WHERE posting_dt >= '2024-01-01'
GROUP BY pipeline_name, notice_type
ORDER BY notice_count DESC
LIMIT 30;

Field Reference

Field Name	Data Type	Description
pk_id	float	Primary key identifier for the notice record.
smx_tsp_id	varchar	SynMax-standardized pipeline ID. Use to join with hdl.pipeline_flow or hdl.pipeline_locations.
full_pipeline_name	varchar	Full legal pipeline name.
pipeline_name	varchar	Short pipeline name. Example: Tennessee Gas
critical_notice_indicator	varchar	Whether this is a critical notice: Y or N.
critical_notice_description	varchar	Description of the critical notice classification.
notice_id	varchar	Pipeline-assigned notice identifier.
notice_status_description	varchar	Current status of the notice (e.g., Active, Superseded).
raw_notice_type	varchar	Original notice type as reported by the pipeline.
notice_type	varchar	Normalized notice type (Force Majeure, OFO, Maintenance, Capacity Constraint, etc.).
posting_dt	varchar	Date/time the notice was posted by the pipeline.
notice_effective_datetime	varchar	When the notice takes effect.
notice_end_datetime	varchar	When the notice expires or ends.
required_response_indicator_description	varchar	Whether a shipper response is required.
response_datetime	varchar	Deadline for shipper response, if applicable.
prior_notice_identifier	varchar	ID of the notice this one supersedes (for tracking supersession chains).
notice_subject	varchar	Subject line of the notice.
notice_text	varchar	Full text body of the notice.
notice_url	varchar	URL to the original notice on the pipeline’s electronic bulletin board.
update_dt	varchar	Last update timestamp.
source_created_dt	varchar	When the notice was originally created in the source system.
snapshot_dt	varchar	Date of the data snapshot.

Vulcan

Satellite-verified construction monitoring for energy infrastructure including power generation projects, data centers, and LNG facilities across the United States and globally. Tracks construction milestones from land clearing through commissioning via satellite imagery analysis, providing independent predictions of online dates that often differ from official EIA or developer estimates. Supports capacity forecasting, supply planning, and construction pipeline analysis with project-level visibility into solar, wind, battery, natural gas, hyperscale data centers, and LNG terminals.

vdl.under_construction

Power generation projects (solar, wind, battery, natural gas) under construction with satellite-verified progress monitoring at the generator level. Each row represents a single generator with construction milestones, timeline predictions, and comparison of EIA-reported vs satellite-observed status. Supports technology filtering via technology, construction likelihood scoring via project_rank (0-7 scale), and schedule comparison via days_eia_minus_vulcan_earliest_online. Use vulcan_status = 'Confirmed' for satellite-verified projects and COUNT(DISTINCT plant_id) for unique facility counts since plants may have multiple generators.

Table ID: vdl.under_construction

Sample Queries

-- Confirmed capacity under construction by technology
SELECT
    technology,
    COUNT(DISTINCT plant_id) AS plant_count,
    COUNT(*) AS generator_count,
    SUM(nameplate_capacity) AS total_capacity_mw
FROM vdl.under_construction
WHERE vulcan_status = 'Confirmed'
GROUP BY technology
ORDER BY total_capacity_mw DESC;

-- High-likelihood projects (rank >= 5) expected online in 12 months
SELECT
    plant_name,
    technology,
    nameplate_capacity,
    state_code,
    date_vulcan_earliest_online,
    project_rank,
    days_eia_minus_vulcan_earliest_online
FROM vdl.under_construction
WHERE project_rank >= 5
  AND vulcan_status = 'Confirmed'
  AND date_vulcan_earliest_online BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '365 days'
ORDER BY date_vulcan_earliest_online
LIMIT 25;

Field Reference

Field Name	Data Type	Description
synmax_id	varchar	Unique key combining plant_id + generator_id. Example: 66685-GEN1
plant_id	varchar	EIA plant identifier. Example: 66685
plant_name	varchar	EIA plant name. Example: Solar Farm Alpha
generator_id	varchar	EIA generator identifier. Example: GEN1
entity_name	text	Operating entity/owner name
total_generators	integer	Total count of generators at this plant
technology	text	Generation technology (Solar Photovoltaic, Batteries, Natural Gas, Onshore Wind). Example: Solar Photovoltaic
vulcan_tech	varchar	Simplified tech category for construction time estimates
plant_status	text	EIA plant status with detail
vulcan_status	text	Satellite verification status (Confirmed, none). Example: Confirmed
project_rank	numeric	Construction likelihood score (0-7 scale, higher = more likely). Example: 5.2
state_code	text	2-letter state code. Example: TX
balancing_authority	text	Grid balancing authority code. Example: ERCOT
latitude	double precision	Plant latitude. Example: 32.7221
longitude	double precision	Plant longitude. Example: -97.1234
nameplate_capacity	bigint	Generator rated capacity in MW. Example: 150
nameplate_capacity_unit	text	Capacity unit (typically MW)
date_planned_operation	date	EIA expected online date (normalized to 15th of month). Example: 2025-06-15
date_vulcan_earliest_online	date	Earliest predicted operational date based on construction progress. Example: 2025-04-01
date_vulcan_latest_online	date	Latest predicted operational date (conservative estimate). Example: 2025-08-15
date_vulcan_earliest_plus_7	date	Earliest date if construction starts within 1 week
date_land_cleared	date	First land clearing observed via satellite
date_first_structures	date	First permanent structures identified
date_construction_start	date	Construction start date
date_construction_50_percent_complete	text	Visually assessed as 50% complete
date_construction_completed	text	Construction complete per satellite imagery
date_vulcan_status_change	date	Date status changed to under construction
date_image	date	Latest satellite image date (data freshness indicator)
observation	text	Satellite observation notes/comments
days_eia_minus_vulcan_earliest_online	integer	Days difference: EIA - Vulcan earliest (positive = EIA later/more conservative). Example: 45
days_eia_minus_vulcan_latest_online	integer	Days difference: EIA - Vulcan latest (negative = EIA earlier/more optimistic)

vdl.datacenters

Data center facility projects (hyperscale, co-location, enterprise, crypto mining) with satellite-verified construction progress, capacity forecasts, and power demand timelines at the unit level. Each facility (plant_id) may have multiple units (synmax_id), so aggregate to facility level when counting unique data centers. Supports filtering by data_center_type, vulcan_status = 'Confirmed' for verified construction, and owner analysis via owner_name. Use balancing_authority for grid-level power demand analysis.

Warning

Deduplication Required: This table contains historical snapshots. Each unit (synmax_id) may have multiple rows with different modified_at timestamps. Always filter for the latest modified_at per synmax_id using ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY modified_at DESC) before analysis.

Note

State Code Inconsistency: The state_code field contains mixed formats—both full names (“Virginia”, “Texas”) and 2-letter codes (“VA”, “TX”). Normalize before aggregating by state.

Table ID: vdl.datacenters

Sample Queries

-- Confirmed data centers under construction by type (deduplicated)
WITH latest_datacenters AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY modified_at DESC) AS rn
    FROM vdl.datacenters
)
SELECT
    data_center_type,
    COUNT(DISTINCT plant_id) AS facility_count,
    SUM(unit_capacity) AS total_capacity_mw
FROM latest_datacenters
WHERE rn = 1
  AND vulcan_status = 'Confirmed'
GROUP BY data_center_type
ORDER BY total_capacity_mw DESC;

-- Top data center owners by capacity (deduplicated)
WITH latest_datacenters AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY modified_at DESC) AS rn
    FROM vdl.datacenters
)
SELECT
    owner_name,
    COUNT(DISTINCT plant_id) AS facility_count,
    SUM(unit_capacity) AS total_capacity_mw
FROM latest_datacenters
WHERE rn = 1
  AND vulcan_status = 'Confirmed'
GROUP BY owner_name
ORDER BY total_capacity_mw DESC
LIMIT 15;

Field Reference

Field Name	Data Type	Description
synmax_id	text	Unique key: plant_id + unit_id. Example: 68847-PV1
plant_id	bigint	IIR plant identifier
plant_name	text	IIR facility/plant name
unit_id	bigint	IIR unit identifier
unit_name	text	IIR unit name
owner_name	text	Data center owner/operator
data_center_type	text	Type: Hyperscale Data Center, Co-Location Data Center, Enterprise Data Center, Crypto Mining Data Center
plant_status	text	IIR facility status (Under Construction, Planned, Operational, Engineered)
unit_status	text	Unit status (Engineered, Planned, On Hold, Under Construction)
vulcan_status	text	Satellite verification status (Confirmed, none). Example: Confirmed
state_code	text	State name or 2-letter code (mixed format—normalize before aggregation). Example: Virginia
balancing_authority	text	Grid balancing authority (ERCOT, MISO, PJM, SPP). Example: PJM
unit_capacity	numeric	Unit power capacity in MW. Example: 200
date_planned_operation	date	IIR expected online date
date_vulcan_earliest_online	date	Earliest predicted operational date based on construction progress
date_vulcan_latest_online	date	Latest predicted operational date (conservative estimate)
date_vulcan_median_online	date	Median predicted operational date (middle estimate)
date_vulcan_earliest_plus_7	text	Earliest online if construction starts 1 week after last update
date_land_cleared	date	First land clearing observed via satellite
date_first_structures	date	First permanent structures identified
date_construction_start	date	Construction start date
date_construction_50_percent_complete	text	Visually assessed as 50% complete
date_construction_completed	text	Construction complete per satellite imagery
date_vulcan_status_change	date	Date status changed to under construction
date_image	date	Latest satellite image date (data freshness indicator)
observation	text	Satellite observation notes
days_iir_minus_vulcan_earliest_online	text	Days difference: IIR - Vulcan earliest (positive = IIR later/more conservative)
days_iir_minus_vulcan_latest_online	text	Days difference: IIR - Vulcan latest (negative = IIR earlier/more optimistic)
source	text	Data source identifier. Example: IIR
created_at	text	Record creation timestamp
modified_at	date	Last modification date (use for deduplication)

vdl.lng_projects

Global LNG facility development projects (liquefaction and regasification) with satellite-verified construction progress, capacity forecasts, and milestone tracking. Each row represents one project phase—use plant_name + phase_number as the composite unique key. Supports technology filtering via technology (Liquefaction for export terminals, Regasification for import terminals), schedule analysis via Vulcan date predictions, and capacity aggregation in MTPA (million tonnes per annum).

Table ID: vdl.lng_projects

Sample Queries

-- LNG capacity under construction by technology type
SELECT
    technology,
    COUNT(*) AS project_count,
    SUM(nameplate_capacity) AS total_capacity_mtpa,
    SUM(trains) AS total_trains
FROM vdl.lng_projects
WHERE vulcan_status = 'Confirmed'
GROUP BY technology
ORDER BY total_capacity_mtpa DESC;

-- LNG projects expected online in next 24 months
SELECT
    plant_name,
    phase_number,
    technology,
    nameplate_capacity,
    capacity_unit,
    date_vulcan_earliest_online,
    date_vulcan_latest_online,
    entity_name
FROM vdl.lng_projects
WHERE vulcan_status = 'Confirmed'
  AND date_vulcan_earliest_online BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '730 days'
ORDER BY date_vulcan_earliest_online
LIMIT 20;

Field Reference

Field Name	Data Type	Description
synmax_id	text	Currently TBD for all LNG projects—do not use
plant_name	text	LNG facility name (part of composite key). Example: Sabine Pass LNG
phase_number	integer	Project phase number (part of composite key). Example: 2
entity_name	text	Project owner/operator
technology	text	Liquefaction (export) or Regasification (import). Example: Liquefaction
vulcan_status	text	Satellite verification status (Confirmed, none). Example: Confirmed
latitude	double precision	Facility latitude
longitude	double precision	Facility longitude
nameplate_capacity	double precision	Rated capacity value. Example: 4.5
capacity_unit	text	Capacity unit (typically mtpa = million tonnes per annum)
trains	integer	Number of LNG trains or storage tanks
date_planned_operation	date	Officially planned/expected operational date
date_vulcan_earliest_online	date	Earliest predicted operational date based on construction progress
date_vulcan_latest_online	date	Latest predicted operational date (conservative estimate)
date_land_cleared	date	First land clearing observed via satellite
date_first_structures	date	First permanent structures identified
date_construction_start	date	Construction start date
date_construction_50_percent_complete	text	Visually assessed as 50% complete
date_construction_completed	date	Construction complete per satellite imagery
date_vulcan_status_change	date	Date status changed to under construction
date_image	date	Latest satellite image date (data freshness indicator)
observation	text	Satellite observation notes/comments
days_planned_operation_minus_vulcan_earliest_online	text	Days difference: planned - Vulcan earliest (positive = ahead of schedule)
days_planned_operation_minus_vulcan_latest_online	text	Days difference: planned - Vulcan latest (positive = ahead of schedule)
created_at	date	Record creation date
modified_at	date	Last modification date

vdl.project_rankings

Construction likelihood rankings (0-7 scale) for power generation projects under development, providing probability scores and forecasted online dates. Each row represents a ranking snapshot for a specific project, with final_rank indicating construction likelihood (6-7 = very high, 4-5 = moderate, 2-3 = lower, 0-1 = unlikely). Use date_vulcan_proposed_v2_online for near-term (2-year) seasonal forecasts and date_vulcan_proposed_online for longer-term (5-year) conservative forecasts. Join with vdl.under_construction on synmax_id for full project details.

Warning

Deduplication Required: This table contains historical snapshots. Each project (synmax_id) may have multiple rows with different date_updated timestamps. Always filter for the latest date_updated per synmax_id using ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY date_updated DESC) before analysis.

Table ID: vdl.project_rankings

Sample Queries

-- High-likelihood projects (rank >= 5) deduplicated
WITH latest_ranks AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY date_updated DESC) AS rn
    FROM vdl.project_rankings
)
SELECT
    synmax_id,
    plant_id,
    generator_id,
    final_rank,
    date_vulcan_proposed_v2_online,
    date_updated
FROM latest_ranks
WHERE rn = 1
  AND final_rank >= 5
ORDER BY final_rank DESC, date_vulcan_proposed_v2_online
LIMIT 25;

-- Rank distribution across all projects (deduplicated)
WITH latest_ranks AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY date_updated DESC) AS rn
    FROM vdl.project_rankings
)
SELECT
    FLOOR(final_rank) AS rank_bucket,
    COUNT(*) AS project_count
FROM latest_ranks
WHERE rn = 1
GROUP BY FLOOR(final_rank)
ORDER BY rank_bucket DESC;

Field Reference

Field Name	Data Type	Description
synmax_id	character varying	Unique project identifier (plant_id + generator_id). Example: 66685-WH001
plant_id	bigint	EIA plant identifier. Example: 66685
generator_id	character varying	EIA generator identifier. Example: WH001
final_rank	real	Construction likelihood score (0-7 scale, higher = more likely). Example: 5.33
date_vulcan_proposed_online	date	Conservative forecast (5-year outlook). Example: 2028-10-12
date_vulcan_proposed_v2_online	date	Seasonal forecast (2-year outlook). Example: 2026-06-17
date_updated	date	Last ranking update date (use for deduplication). Example: 2024-11-20
created_at	text	Record creation timestamp (may be TBD)
modified_at	text	Last modification timestamp (may be TBD)

vdl.metadata_history

Historical EIA metadata tracking for power generation projects containing one record per project per EIA monthly update. Enables analysis of capacity changes, schedule slippage, status transitions, and project lifecycle tracking over time. Use days_planned_operation_minus_first_seen_planned_operation to identify delayed projects (positive values indicate schedule slip in days). Supports technology analysis, state-level aggregation, and identifying projects that have retired or been cancelled.

Warning

Deduplication Required: This table contains multiple snapshots per project. Each project (synmax_id) appears once per EIA monthly update (date_eia_updated). Always filter for the latest date_eia_updated per synmax_id using ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY date_eia_updated DESC) before analysis.

Table ID: vdl.metadata_history

Sample Queries

-- Latest metadata for all projects (deduplicated)
WITH latest_metadata AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY date_eia_updated DESC) AS rn
    FROM vdl.metadata_history
)
SELECT
    plant_name,
    technology,
    nameplate_capacity,
    state_code,
    date_planned_operations,
    plant_status
FROM latest_metadata
WHERE rn = 1
ORDER BY nameplate_capacity DESC
LIMIT 25;

-- Projects with significant schedule slip (> 1 year delay)
WITH latest_metadata AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY synmax_id ORDER BY date_eia_updated DESC) AS rn
    FROM vdl.metadata_history
)
SELECT
    plant_name,
    technology,
    nameplate_capacity,
    state_code,
    days_planned_operation_minus_first_seen_planned_operation AS schedule_slip_days
FROM latest_metadata
WHERE rn = 1
  AND days_planned_operation_minus_first_seen_planned_operation > 365
ORDER BY schedule_slip_days DESC
LIMIT 25;

Field Reference

Field Name	Data Type	Description
synmax_id	text	Unique project identifier (plant_id + generator_id)
plant_id	bigint	EIA plant identifier
plant_name	varchar	EIA plant name
generator_id	varchar	EIA generator identifier
generator_unit_code	text	Generator unit code
entity_id	bigint	Operating entity identifier
entity_name	text	Operating entity/owner name
plant_status	text	Current EIA plant status
first_seen_status	varchar	Status when first reported in EIA data
last_seen_status	varchar	Status when last seen in EIA data
state_code	varchar	2-letter state code. Example: TX
county	text	County name
latitude	double precision	Plant latitude
longitude	double precision	Plant longitude
balancing_authority_code	text	Grid balancing authority code
technology	text	Generation technology (Solar PV, Natural Gas Combined Cycle, etc.)
energy_source_code	text	Primary energy source (SUN, NG, WND)
prime_mover_code	text	Prime mover type (BA = battery, CT = combustion turbine)
sector	text	Ownership sector code
sector_name	text	Ownership sector description
nameplate_capacity	double precision	Rated/nameplate capacity in MW
nameplate_capacity_unit	text	Capacity unit (typically MW)
net_summer_capacity	double precision	Summer peak capacity (derated)
net_winter_capacity	double precision	Winter peak capacity (derated)
nameplate_energy_capacity	double precision	Storage capacity for batteries (MWh)
date_eia_updated	date	EIA update date (use for deduplication)
date_planned_operations	date	Planned/scheduled operation date
date_operating	date	Actual operation date
date_retirement	date	Retirement date
date_first_seen	date	First appearance in EIA dataset
date_last_seen	date	Last appearance in EIA dataset
date_first_seen_planned_operations	date	First reported planned operation date
date_planned_derate	date	Planned capacity reduction date
planned_derate_of_summer_capacity	double precision	Planned reduction amount (MW)
date_planned_uprate	date	Planned capacity increase date
planned_uprate_of_summer_capacity	double precision	Planned increase amount (MW)
date_planned_retirement	date	Planned retirement date
days_last_seen_minus_first_seen	double precision	Days project has been in EIA dataset
days_operation_minus_first_seen	double precision	Days from first seen to operation
days_planned_operation_minus_first_seen_planned_operation	double precision	Schedule slip in days (positive = delayed). Example: 180
created_at	text	Record creation timestamp
modified_at	text	Last modification timestamp

Daily Nat Gas Production

Modeled daily dry gas production by sub-region (basin level) covering all major U.S. natural gas producing areas from January 2018 to present. Provides daily production rate estimates in Bcf/day with 60-90 day timeliness advantage over state-reported monthly production.

hdl.daily_production

Daily dry gas production by sub-region for all major U.S. producing areas. Modeled daily production rates derived from pipeline flow data and other sources, providing more timely estimates than state-reported monthly production which lags by 60-90 days. Data represents dry gas (processed natural gas with NGLs removed) measured in Bcf/day across 30 sub-regions covering all major U.S. natural gas producing basins. Use date_prod for production date, sub_region_natgas for basin-level aggregation, and region_natgas for regional groupings. Supports real-time supply monitoring, basin-level trend analysis, and correlating upstream activity with production response.

Warning

Critical Aggregation Rules: Use AVG() for time aggregation (monthly/quarterly/annual) since values are daily RATES, not volumes. Use SUM() for spatial aggregation across sub-regions on a single date. Data is already in Bcf/day—no unit conversion needed.

Table ID: hdl.daily_production

Sample Queries

-- Total U.S. daily production (spatial aggregation - use SUM)
SELECT
    date_prod,
    SUM(prod_dry_gas_bcf_day) as total_us_bcf_per_day
FROM hdl.daily_production
WHERE date_prod >= '2024-01-01'
GROUP BY date_prod
ORDER BY date_prod DESC
LIMIT 30;

-- Monthly basin production (time aggregation - use AVG)
SELECT
    DATE_TRUNC('month', date_prod) as month,
    sub_region_natgas,
    AVG(prod_dry_gas_bcf_day) as avg_daily_bcf
FROM hdl.daily_production
WHERE date_prod >= '2024-01-01'
GROUP BY DATE_TRUNC('month', date_prod), sub_region_natgas
ORDER BY 1, 3 DESC
LIMIT 50;

Field Reference

Field Name	Data Type	Description
date_prod	date	Production date in YYYY-MM-DD format. Example: 2018-06-02
sub_region_natgas	varchar	Basin-level sub-region name. 30 unique sub-regions covering all major U.S. natural gas producing basins. Example: AL
region_natgas	text	Regional grouping for sub-regions (gulf, northeast, west, midwest, southeast). Example: southeast
prod_dry_gas_bcf_day	numeric	Daily dry gas production rate in Bcf/day. Already in standard units—no conversion needed. Example: 0.3600333333

Leviaton

Global LNG trade flow intelligence tracking cargo transactions, vessel movements, and fleet status for 921 LNG carriers worldwide. Provides vessel-level visibility into LNG supply chains from production terminals to consumption markets.

ldl.transaction_history_raw

Comprehensive global LNG cargo tracking dataset recording every loading and offloading transaction for LNG carriers worldwide. Tracks complete cargo journeys from production facilities to consumption terminals with BCF volumes, enabling trade flow analysis via loading_origin_country for true production source identification and imo for individual vessel journey tracking.

Table ID: ldl.transaction_history_raw

Sample Queries

-- US LNG exports by terminal (last 30 days)
SELECT
    terminal,
    COUNT(*) as transaction_count,
    SUM(volume) as total_bcf
FROM ldl.transaction_history_raw
WHERE transaction_type = 'loading'
  AND country = 'USA'
  AND timestamp >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY terminal
ORDER BY total_bcf DESC
LIMIT 10;

-- Global trade flows by origin country
SELECT
    loading_origin_country,
    country as destination_country,
    COUNT(*) as shipments,
    SUM(volume) as total_volume_bcf
FROM ldl.transaction_history_raw
WHERE transaction_type = 'offloading'
  AND loading_origin_country IS NOT NULL
  AND timestamp >= '2024-01-01'
GROUP BY loading_origin_country, country
ORDER BY total_volume_bcf DESC
LIMIT 20;

Field Reference

Field Name	Data Type	Description
timestamp	timestamp	Exact time of the current transaction (loading/offloading). Example: 2020-09-18T03:00:25
imo	bigint	International Maritime Organization number - unique ship identifier. Example: 7390181
terminal	text	Name of the current terminal where transaction occurred. Example: Arzew
terminal_id	text	Unique identifier for the current terminal. Example: terminal_df01c7aa2
country	text	Country where current terminal is located. Example: India
country_code	text	Two-letter country code. Example: DZ
origin_timestamp	timestamp	Previous transaction timestamp (87% populated). Example: 2020-01-01T04:00:11
origin_terminal	text	Previous terminal in the cargo’s journey. Example: Bontang
origin_terminal_id	text	Unique ID for previous terminal. Example: terminal_220d7ad08
origin_country	text	Country of previous terminal. Example: Cameroon
origin_country_code	text	Country code of previous location. Example: JP
loading_origin_timestamp	timestamp	When cargo was originally loaded (42% populated). Example: 2020-01-17T13:05:11
loading_origin_terminal	text	Original loading terminal (production facility). Example: North West Shelf
loading_origin_terminal_id	text	Unique ID for original loading terminal. Example: terminal_f691e936d
loading_origin_country	text	Country where LNG was produced. Example: Australia
loading_origin_country_code	text	Producer country code. Example: ID
volume	double precision	LNG cargo volume in BCF (billion cubic feet). Example: 2.6680875
volume_unit	text	Volume unit, always bcf
transaction_type	text	Type of event: loading (export) or offloading (import). Example: offloading
modified_at	timestamp	When this record was last updated in the database

ldl.ais_history_raw

Comprehensive global LNG vessel tracking database containing 26.7 million AIS records for 921 unique LNG vessels spanning January 2019 to present with real-time updates. Captures precise GPS coordinates, speed, heading, draught (loading indicator), and captain’s destination with standardized port codes for trade flow mapping, voyage optimization, and port congestion analysis.

Table ID: ldl.ais_history_raw

Sample Queries

-- Track specific vessel journey (last 30 days)
SELECT
    timestamp_dynamic,
    latitude,
    longitude,
    speed,
    captains_destination,
    draught
FROM ldl.ais_history_raw
WHERE imo = 9636723
  AND timestamp_dynamic >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY timestamp_dynamic
LIMIT 100;

-- Daily active vessel count
SELECT
    DATE(timestamp_dynamic) as date,
    COUNT(DISTINCT vessel_id) as active_vessels
FROM ldl.ais_history_raw
WHERE timestamp_dynamic >= '2024-01-01'
GROUP BY DATE(timestamp_dynamic)
ORDER BY date
LIMIT 30;

Field Reference

Field Name	Data Type	Description
vessel_id	varchar	Unique internal vessel identifier. Example: vessel_ca8613a15
imo	integer	International Maritime Organization number (7-digit unique vessel ID). Example: 9636723
name	varchar	Vessel name. Example: LNG ADAMAWA
mmsi	integer	Maritime Mobile Service Identity (some nulls present). Example: 229581000
callsign	varchar	Radio call sign. Example: JNYU
timestamp_dynamic	timestamp with time zone	Actual position timestamp for real-time tracking. Example: 2025-06-09T03:48:23+00:00
timestamp_static	timestamp with time zone	Static information timestamp. Example: 2025-06-09T03:53:47+00:00
longitude	double precision	Precise vessel longitude (-176° to 180°). Example: 93.8470016666667
latitude	double precision	Precise vessel latitude (-36° to 78°). Example: -9.31481
location	USER-DEFINED	GeoJSON Point geometry with EPSG:4326 CRS
speed	double precision	Vessel speed in knots (0-68.7 knots range). Example: 14.4
heading	double precision	Vessel heading in degrees (0-511, where 511 = no data). Example: 160
course	double precision	Course over ground in degrees (0-511). Example: 241.8
captains_destination	varchar	Standardized port codes (100% complete). Example: MYMKZ
captains_eta	timestamp with time zone	Captain’s estimated time of arrival. Example: 2024-09-16T00:00:00+00:00
draught	double precision	Vessel draught in meters (loading indicator, 0-19.1m). Example: 9.2
flag	varchar	Flag state (currently 0% complete)
ais_nav_status	varchar	Navigation status (currently 0% complete)
rate_of_turn	varchar	Rate of turn data (mostly null)
msg_collection_method	varchar	AIS message collection method (mostly null)
created_at	timestamp with time zone	Record creation timestamp
modified_at	timestamp with time zone	Record modification timestamp

ldl.vessel_info_latest

Real-time snapshot of 903 active LNG vessels combining latest AIS position with complete vessel specifications (capacity, dimensions, type) and forecasted destinations. Each vessel appears exactly once with its most current information, enabling fleet monitoring, supply forecasting, and load status analysis via draught thresholds. Use imo to join with ldl.ais_history_raw for historical tracking or ldl.transaction_history_raw for cargo movements.

Table ID: ldl.vessel_info_latest

Sample Queries

-- Current fleet snapshot by vessel type
SELECT
    vessel_type,
    COUNT(*) as vessel_count,
    AVG(maximum_capacity) as avg_capacity_cbm,
    AVG(speed) as avg_speed_knots
FROM ldl.vessel_info_latest
WHERE synmax_status = 'ACTIVE'
GROUP BY vessel_type
ORDER BY vessel_count DESC;

-- Vessels heading to specific terminal (next 30 days)
SELECT
    name,
    imo,
    maximum_capacity,
    forecast_destination_terminal,
    forecast_destination_timestamp,
    speed,
    draught
FROM ldl.vessel_info_latest
WHERE forecast_destination_terminal LIKE '%Sabine%'
  AND forecast_destination_timestamp BETWEEN NOW() AND NOW() + INTERVAL '30 days'
ORDER BY forecast_destination_timestamp;

Field Reference

Field Name	Data Type	Description
vessel_id	varchar	Unique internal vessel identifier. Example: vessel_0609defc0
imo	integer	IMO number (7-digit unique vessel ID). Example: 9762962
mmsi	integer	Maritime Mobile Service Identity. Example: 311000634
name	varchar	Vessel name. Example: SERI BAKTI
callsign	varchar	Radio callsign. Example: SVCS9
flag	varchar	Flag state. Example: Hong Kong
vessel_type	text	Type of LNG vessel (LNG_CARRIER, FSRU, BUNKERING_TANKER, etc.). Example: LNG_CARRIER
commodity	text	Commodity type, always LNG
synmax_status	text	Vessel operational status. Example: ACTIVE
maximum_capacity	double precision	Maximum cargo capacity in cubic meters. Example: 217000
maximum_capacity_unit	text	Capacity unit, always CUBIC_METER
length	integer	Vessel length in meters. Example: 300
width	integer	Vessel width in meters. Example: 44
draught	double precision	Current vessel draft in meters (load indicator: <8m unladen, >10m loaded). Example: 9.3
speed	double precision	Current speed in knots. Example: 14
heading	double precision	Current heading in degrees. Example: 511
course	double precision	Current course over ground. Example: 360
latitude	double precision	Current latitude. Example: 37.3076233333333
longitude	double precision	Current longitude. Example: -93.340995
location	geometry	GeoJSON Point geometry with EPSG:4326 CRS
timestamp_dynamic	timestamp with time zone	Timestamp of latest position. Example: 2026-01-29T17:00:57+00:00
timestamp_static	timestamp with time zone	Static information timestamp
captains_destination	varchar	Captain-reported destination. Example: FOR ORDERS
captains_eta	timestamp with time zone	Captain-reported ETA. Example: 2026-02-10T20:00:00+00:00
forecast_destination_terminal_id	text	Unique terminal identifier for forecasted destination. Example: terminal_ffc062f22
forecast_destination_terminal	text	SynMax forecasted destination terminal (85% populated). Example: Gorgon LNG
forecast_destination_timestamp	timestamp	Predicted arrival time. Example: 2026-02-03T16:53:06.637073
is_loaded	boolean	Load status indicator (unpopulated - use draught instead)
current_charterer	text	Charter company (0% populated)
ais_nav_status	varchar	Navigation status
naval_flag_country_code	char(2)	Naval flag country code
first_active_date	timestamp with time zone	First active date
first_operation_date	timestamp with time zone	First operation date
ais_created_at	timestamp with time zone	AIS record creation timestamp
ais_modified_at	timestamp with time zone	AIS record modification timestamp
vessel_info_created_at	timestamp with time zone	Vessel info creation timestamp
vessel_info_modified_at	timestamp with time zone	Vessel info modification timestamp
last_loading_terminal	varchar	Last terminal where vessel loaded cargo
last_loading_country	varchar	Country of last loading
last_loading_timestamp	timestamp	Timestamp of last loading
ais_nav_status	varchar	AIS navigation status
build_year	int	Year vessel was built
modified_at	timestamp	Record modification timestamp
created_at	timestamp	Record creation timestamp

Gulf Monitor

Persian Gulf vessel traffic monitoring system providing real-time tracking of vessel crossings through the Strait of Hormuz, port infrastructure data, and fleet position snapshots. Includes pre-computed crossing events with speed-quality filters, a port/terminal reference catalog covering 146 facilities across 10 countries, geographic region-of-interest boundary definitions, and a materialized view of latest vessel positions with Persian Gulf location classification.

gdl.ports

Port and terminal reference catalog for the Gulf Monitor system containing 146 ports and terminals in the Persian Gulf region and surrounding countries (India, Pakistan, Iran, Oman, UAE, Qatar, Kuwait, Iraq, Saudi Arabia, Bahrain). Each port has geographic coordinates, a boundary polygon, UNECE codes, and classification of ship types and cargo types handled. Use for geofencing vessel positions to ports, identifying port capabilities, and mapping port infrastructure.

Note

ship_type and cargo_type are JSON arrays stored as strings. Use LIKE '%value%' for simple filtering, or cast to jsonb for proper array operations (e.g., ship_type::jsonb ? 'Tanker'). Country names are uppercase and use formal names (e.g., IRAN, ISLAMIC REPUBLIC OF, UNITED ARAB EMIRATES).

Table ID: gdl.ports

Sample Queries

-- Find which port a vessel is in
SELECT p.name, p.country
FROM gdl.ports p
WHERE ST_Contains(p.polygon, ST_SetSRID(ST_MakePoint(:vessel_lon, :vessel_lat), 4326));

-- Ports that handle crude oil
SELECT name, country, cargo_type
FROM gdl.ports
WHERE cargo_type LIKE '%Crude%';

-- LNG terminals
SELECT name, country, cargo_type
FROM gdl.ports
WHERE cargo_type LIKE '%LNG%';

-- All ports for mapping
SELECT name, country, latitude, longitude, ship_type, cargo_type
FROM gdl.ports;

Field Reference

Field Name	Data Type	Description
name	varchar	Port name in uppercase. Example: AL JAZEERA
country	varchar	Country name in uppercase (formal names). Example: UNITED ARAB EMIRATES
location	geometry	PostGIS point geometry (SRID 4326) representing the port centroid. Use ST_AsText(location) to extract WKT or ST_X(location)/ST_Y(location) for lon/lat.
latitude	float	Port centroid latitude in decimal degrees
longitude	float	Port centroid longitude in decimal degrees
polygon	geometry	PostGIS polygon geometry (SRID 4326) defining the port boundary. Use for geofencing with ST_Contains(polygon, vessel_point).
unlocode	varchar	UN/LOCODE port code. Example: AEJAZ
unrefcode	varchar	UNECE reference code. Usually matches unlocode but may differ. Example: QARUS
ship_type	varchar	JSON array (as string) of ship types the port handles. Values include: Tanker, General Cargo, Bulk Carrier, Container, Gas Carrier, Passenger, RoRo, Others. Example: ['Tanker']
cargo_type	varchar	JSON array (as string) of cargo types handled. Values include: Crude, Black Products, White Products, General Cargo, Container, Chemicals, NGL, Bulk Other, Coal, Grain, Livestock, Roro, LNG, LPG. Example: ['Crude', 'Black Products']
modified_at	varchar	Record last-modified timestamp (ISO 8601)

gdl.vessel_crossings

Pre-computed records of vessels crossing the boundary of the Persian Gulf region of interest. Each row represents a single crossing event — one vessel transiting in or out of the ROI at a specific timestamp. This table is the primary source for Strait of Hormuz daily transit analytics. Populated by an upstream pipeline (not derived from raw AIS at query time). Contains approximately 64,669 records covering 8,980 unique vessels from 2025-01-01 to present.

Warning

Mandatory Speed Filtering: Raw data includes records with unphysical speeds from GPS errors and AIS gaps. Always apply both speed filters: WHERE calculated_speed <= 25 AND speed <= 25. Without this filter, teleportation artifacts inflate crossing counts significantly.

Warning

Deduplication Required: A vessel may have multiple AIS pings recorded as it crosses the boundary. Always deduplicate to one crossing per vessel per day per direction using DISTINCT ON (synmax_ship_id, timestamp::date, direction).

Table ID: gdl.vessel_crossings

Sample Queries

-- Daily crossing count (with mandatory speed filter and deduplication)
SELECT date, COUNT(*) AS daily_crossings
FROM (
    SELECT DISTINCT ON (synmax_ship_id, timestamp::date, direction)
        synmax_ship_id,
        timestamp::date AS date,
        direction
    FROM gdl.vessel_crossings
    WHERE calculated_speed <= 25
      AND speed <= 25
    ORDER BY synmax_ship_id, timestamp::date, direction, timestamp DESC
) deduped
GROUP BY date
ORDER BY date;

-- Daily crossings by direction (in/out)
SELECT date, direction, COUNT(*) AS crossings
FROM (
    SELECT DISTINCT ON (synmax_ship_id, timestamp::date, direction)
        synmax_ship_id,
        timestamp::date AS date,
        direction
    FROM gdl.vessel_crossings
    WHERE calculated_speed <= 25
      AND speed <= 25
    ORDER BY synmax_ship_id, timestamp::date, direction, timestamp DESC
) deduped
GROUP BY date, direction
ORDER BY date, direction;

Field Reference

Field Name	Data Type	Description
synmax_ship_id	varchar	SynMax unique vessel identifier (UUID). Joinable to other Gulf Monitor vessel tables.
timestamp	varchar	UTC timestamp of the crossing event (ISO 8601). Range: 2025-01-01 to present.
roi_name	varchar	Name of the region of interest being crossed. Currently all records are persian_gulf.
direction	varchar	Crossing direction relative to the ROI. Values: in (entering the Persian Gulf) or out (exiting the Persian Gulf).
draught	float	Vessel draught in meters at time of crossing. Nullable. Deeper draught generally indicates a laden (loaded) vessel.
flag_short_code	varchar	Vessel flag state abbreviation. Nullable. Example: SG
speed	float	Self-reported AIS speed over ground in knots. Caution: self-reported speed can be erroneous.
heading	float	Self-reported AIS heading in degrees (0–360). Nullable.
destination	varchar	AIS captain’s destination field (free-text, not standardized). Nullable. Example: AE KHL
eta	varchar	AIS-reported estimated time of arrival (ISO 8601). Nullable.
status	varchar	AIS navigation status code. Most common: 0.0 (Under Way Using Engine, ~80%). Other values: 1.0 (At Anchor), 5.0 (Moored), 8.0 (Sailing), 15.0 (Not Defined).
calculated_speed	float	Implied speed in knots computed from distance and time between consecutive AIS positions. Critical for quality filtering — values above ~25 kts indicate GPS spikes or spoofing artifacts.
calculated_heading	float	Implied heading in degrees derived from consecutive AIS positions.
modified_at	varchar	Record last-modified timestamp (ISO 8601)

gdl.regions_of_interest

Geographic boundary definitions used by the Gulf Monitor system to detect vessel crossings. Each row is a named region with a PostGIS polygon boundary. The gdl.vessel_crossings table records events when vessels enter or exit these regions. Currently contains a single region: the Persian Gulf, defined by a 9-vertex polygon encompassing the entire waterway from the Strait of Hormuz through the northern Gulf.

Note

Only 1 ROI currently exists (persian_gulf). The schema supports expansion to additional regions. The polygon column is a PostGIS geometry — use ST_AsText() or ST_AsGeoJSON() to extract readable formats. Coordinates use SRID 4326 (WGS 84) with (longitude, latitude) order within PostGIS functions.

Table ID: gdl.regions_of_interest

Sample Queries

-- Check if a position is inside the Persian Gulf
SELECT ST_Contains(
    (SELECT polygon FROM gdl.regions_of_interest WHERE name = 'persian_gulf'),
    ST_SetSRID(ST_MakePoint(:longitude, :latitude), 4326)
) AS in_persian_gulf;

-- Extract boundary as GeoJSON for mapping
SELECT name, ST_AsGeoJSON(polygon) as geojson
FROM gdl.regions_of_interest
WHERE name = 'persian_gulf';

-- Extract boundary as WKT
SELECT name, ST_AsText(polygon) as wkt
FROM gdl.regions_of_interest;

Field Reference

Field Name	Data Type	Description
name	varchar	Region name identifier. Currently: persian_gulf. Used as the roi_name foreign key in gdl.vessel_crossings.
polygon	geometry	PostGIS polygon geometry (SRID 4326) defining the region boundary. Use ST_AsText() or ST_AsGeoJSON() to extract readable formats.
modified_at	varchar	Record last-modified timestamp (ISO 8601)

gdl.vessel_latest_positions

Materialized view providing a real-time snapshot of every tracked vessel’s most recent AIS position, combined with vessel metadata and a Persian Gulf location flag. Built by cross-joining the vessels table with a lateral subquery on AIS history to find each vessel’s latest AIS record within the past 7 days. Use for fleet monitoring, current vessel locations, and determining which vessels are currently inside or outside the Persian Gulf.

Warning

This is a materialized view, not a live table — data is a snapshot as of the last refresh. Only includes vessels with AIS data in the last 7 days; vessels that haven’t transmitted recently are excluded. If you need the absolute latest position, query AIS history directly.

Note

Use ship_and_cargo_type_class for vessel type filtering — ship_type is ~99% NULL. last_seen_in_roi (from the vessels registry) is not the same as latest_timestamp (the most recent AIS position, which may be outside the Gulf).

Table ID: gdl.vessel_latest_positions

Sample Queries

-- Current fleet in the Persian Gulf
SELECT synmax_ship_id, name, ship_and_cargo_type_class,
       latitude, longitude, speed, draught
FROM gdl.vessel_latest_positions
WHERE in_persian_gulf = TRUE;

-- Tankers currently outside the Gulf
SELECT *
FROM gdl.vessel_latest_positions
WHERE in_persian_gulf = FALSE
  AND ship_and_cargo_type_class = 'Tanker';

-- All vessel positions for mapping
SELECT name, latitude, longitude, ship_and_cargo_type_class,
       speed, in_persian_gulf
FROM gdl.vessel_latest_positions;

-- Laden vs ballast tanker analysis (using draught)
SELECT name, draught, destination, in_persian_gulf
FROM gdl.vessel_latest_positions
WHERE ship_and_cargo_type_class = 'Tanker'
ORDER BY draught DESC;

Field Reference

Field Name	Data Type	Description
synmax_ship_id	varchar	SynMax unique vessel identifier (UUID). Primary key.
name	varchar	Vessel name from the vessels registry.
mmsi	integer	Maritime Mobile Service Identity.
imo	integer	IMO number.
ship_type	varchar	High-level vessel type (~99% NULL — use ship_and_cargo_type_class instead).
ship_and_cargo_type_class	varchar	Vessel classification. Values: Cargo, Tanker, Other, Tug, NULL. Recommended field for type filtering.
length	float	Vessel length in meters.
width	float	Vessel beam in meters.
last_seen_in_roi	varchar	When the vessel was last seen inside the Persian Gulf ROI (ISO 8601, from the vessels registry — not the same as latest_timestamp).
latest_timestamp	timestamp	UTC timestamp of the vessel’s most recent AIS position (within last 7 days).
latitude	float	Latest latitude in decimal degrees.
longitude	float	Latest longitude in decimal degrees.
location	geometry	PostGIS point geometry of latest position.
speed	float	Self-reported AIS speed over ground in knots.
heading	float	Self-reported AIS heading in degrees (0–360).
course	float	AIS course over ground in degrees.
flag_short_code	varchar	Vessel flag state abbreviation.
destination	varchar	AIS captain’s destination (free-text).
draught	float	Vessel draught in meters.
status	varchar	AIS navigation status.
in_persian_gulf	boolean	TRUE if the vessel’s latest position falls within the Persian Gulf polygon, FALSE otherwise. Computed using ST_Contains against the Persian Gulf boundary polygon.

Natural Gas Demand

Daily natural gas demand (consumption) and trade flow volumes modeled and tracked across CONUS and the 5 EIA storage regions. Covers seven demand sectors (Commercial, Electric Power, Industrial, Lease and Plant Fuel, Pipeline & Distribution Use, Residential, Vehicle Fuel) and five trade/LNG flow types (Canada Pipeline Exports, Canada Pipeline Imports, LNG Feedgas, LNG Imports, Mexico Pipeline Exports). Data is updated intraday and spans from 2023-01-01 to present.

Warning

Aggregation rules: Values are daily rates in Bcf/d. Use AVG() when aggregating over time periods (monthly, quarterly, annual) since values represent daily rates, not cumulative volumes. Use SUM() when aggregating across regions for spatial totals. Always filter by model_scenario = 'baseline' and weather_scenario = 'baseline'.

Warning

Avoid double-counting: Do not mix CONUS-level rows with regional rows. Filter by either region = 'CONUS' for national totals or region IN ('East', 'Midwest', 'Mountain', 'Pacific', 'South Central') for regional breakdowns. Trade and LNG flows (Canada Pipeline Exports, Canada Pipeline Imports, LNG Feedgas, LNG Imports, Mexico Pipeline Exports) are present only at the CONUS level.

hdl.gas_demand

Daily natural gas demand dataset with current values across consumption sectors and trade flows.

Note

Trade and LNG flow demand types are only available at the CONUS level. Regional rows contain only the seven consumption sectors.

Table ID: hdl.gas_demand

Sample Queries

-- Get the latest national demand by sector
SELECT date, demand_type, value
FROM hdl.gas_demand
WHERE region = 'CONUS'
  AND model_scenario = 'baseline'
  AND weather_scenario = 'baseline'
ORDER BY date DESC
LIMIT 50;

-- Get total demand for a specific region over a date range
SELECT date, SUM(value) AS total_demand_bcfd
FROM hdl.gas_demand
WHERE region = 'South Central'
  AND model_scenario = 'baseline'
  AND weather_scenario = 'baseline'
  AND date BETWEEN '2025-01-01' AND '2025-06-30'
GROUP BY date
ORDER BY date;

-- Monthly average demand by sector (national)
SELECT DATE_TRUNC('month', date) AS month,
       demand_type,
       AVG(value) AS avg_bcfd
FROM hdl.gas_demand
WHERE region = 'CONUS'
  AND model_scenario = 'baseline'
  AND weather_scenario = 'baseline'
GROUP BY DATE_TRUNC('month', date), demand_type
ORDER BY month, demand_type;

-- Compare demand across all regions for a single day
SELECT region, demand_type, value
FROM hdl.gas_demand
WHERE date = '2025-06-01'
  AND model_scenario = 'baseline'
  AND weather_scenario = 'baseline'
  AND region != 'CONUS'
ORDER BY region, demand_type;

Warning

model_scenario and weather_scenario must always be included in your WHERE clause. Currently the only value for both is 'baseline', but future releases will include forecast scenarios. Omitting these filters will cause double-counting when additional scenarios are added.

Field Reference

Field Name	Data Type	Description
date	date	Calendar date of the observation.
region	varchar	Geographic region. One of: 'CONUS', 'East', 'Midwest', 'Mountain', 'Pacific', 'South Central'.
demand_type	varchar	Demand sector or trade flow category. Consumption sectors: 'Commercial', 'Electric Power', 'Industrial', 'Lease and Plant Fuel', 'Pipeline & Distribution Use', 'Residential', 'Vehicle Fuel'. Trade flows (CONUS only): 'Canada Pipeline Exports', 'Canada Pipeline Imports', 'LNG Feedgas', 'LNG Imports', 'Mexico Pipeline Exports'.
value	float	Demand volume in Bcf/d (billion cubic feet per day).
model_scenario	varchar	Model scenario identifier. Currently always 'baseline'.
weather_scenario	varchar	Weather scenario identifier. Currently always 'baseline'.

hdl.gas_demand_history

Historical vintages and daily revisions of natural gas demand (consumption) and trade flow volumes. This table has the same geographic and sectoral coverage as hdl.gas_demand but includes an as_of timestamp to identify each vintage run, enabling analysis of forecast revisions and model changes over time. Data is updated intraday and spans from 2023-01-01 to present, with multiple as_of vintages per date.

Warning

Vintage filtering required: This table contains multiple vintages of the same data identified by the as_of column. You must filter by a specific as_of value (typically the latest) or use as_of = (SELECT MAX(as_of) FROM hdl.gas_demand_history) to avoid double-counting. Without this filter, every row will appear duplicated across all vintage runs.

Note

The as_of column represents when a particular model run was executed. Comparing the same date across different as_of values reveals how demand estimates were revised over time.

Note

The updated_at column tracks the last database modification time for each row. Use as_of (not updated_at) for vintage selection.

Table ID: hdl.gas_demand_history

Sample Queries

-- Get the latest vintage of demand data
SELECT date, region, demand_type, value
FROM hdl.gas_demand_history
WHERE as_of = (SELECT MAX(as_of) FROM hdl.gas_demand_history)
  AND region = 'CONUS'
  AND model_scenario = 'baseline'
  AND weather_scenario = 'baseline'
ORDER BY date DESC
LIMIT 50;

-- Compare two vintages for a specific date range
SELECT a.date, a.demand_type,
       a.value AS old_estimate,
       b.value AS new_estimate,
       b.value - a.value AS revision
FROM hdl.gas_demand_history a
JOIN hdl.gas_demand_history b
  ON a.date = b.date
  AND a.region = b.region
  AND a.demand_type = b.demand_type
  AND a.model_scenario = b.model_scenario
  AND a.weather_scenario = b.weather_scenario
WHERE a.as_of = '2025-06-01'
  AND b.as_of = '2025-07-01'
  AND a.region = 'CONUS'
  AND a.model_scenario = 'baseline'
  AND a.weather_scenario = 'baseline'
ORDER BY a.date, a.demand_type;

-- List all available vintages
SELECT DISTINCT as_of
FROM hdl.gas_demand_history
ORDER BY as_of DESC;

-- Track how a single day's demand estimate evolved across vintages
SELECT as_of, demand_type, value
FROM hdl.gas_demand_history
WHERE date = '2025-06-15'
  AND region = 'CONUS'
  AND demand_type = 'Residential'
  AND model_scenario = 'baseline'
  AND weather_scenario = 'baseline'
ORDER BY as_of;

Warning

Do not confuse as_of with updated_at. The as_of column identifies the model vintage (when the forecast/estimate was produced). The updated_at column is a database timestamp indicating when the row was last modified in the system.

Field Reference

Field Name	Data Type	Description
date	date	Calendar date of the observation.
region	varchar	Geographic region. One of: 'CONUS', 'East', 'Midwest', 'Mountain', 'Pacific', 'South Central'.
demand_type	varchar	Demand sector or trade flow category. Same values as hdl.gas_demand.
value	float	Demand volume in Bcf/d (billion cubic feet per day).
model_scenario	varchar	Model scenario identifier. Currently always 'baseline'.
weather_scenario	varchar	Weather scenario identifier. Currently always 'baseline'.
as_of	timestamp	Vintage timestamp identifying when this model run was produced. Use this to select a specific vintage or compare revisions over time.
updated_at	timestamp	Database timestamp of the last modification to this row. Not used for vintage selection.

Weather & Temperature Data

Daily historical and 16-day forecast temperature data at state, county, and MSA granularity, with pre-calculated Heating and Cooling Degree Days (HDD/CDD). Based on the NOAA GFS Model and HRRR gridded data with history back to 2000. Includes annual population estimates for population-weighting degree days and metadata/dimension tables with boundaries, area weights, and region assignments for joining. Together these views support energy-weather analytics such as demand forecasting, heating/cooling load estimation, and climate-normal deviation analysis across any U.S. geography.

hdl.temperature_state

Historical daily temperatures (°F) and degree days (HDD/CDD) for all 48 CONUS states from 2000 to present. State-level values are inherently area-weighted via HRRR gridding — no further spatial weighting is needed for temperature fields. For population-weighted degree-day aggregations to larger geographies (e.g., national), use county-level data with population weights instead.

Table ID: hdl.temperature_state

Sample Queries

-- Daily HDD for Texas over winter 2024-2025
SELECT
    date,
    state_abbr,
    temp_avg_raw,
    hdd_65_raw,
    cdd_65_raw
FROM hdl.temperature_state
WHERE state_abbr = 'TX'
  AND date BETWEEN '2024-11-01' AND '2025-03-31'
ORDER BY date;

-- Total HDD by state for January 2025
SELECT
    state_abbr,
    state_name,
    SUM(hdd_65_raw) AS total_hdd,
    SUM(cdd_65_raw) AS total_cdd,
    AVG(temp_avg_raw) AS avg_temp
FROM hdl.temperature_state
WHERE date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY state_abbr, state_name
ORDER BY total_hdd DESC;

Field Reference

Field Name	Data Type	Description
date	date	Calendar date.
state_geo_id	varchar	2-digit state FIPS code. Example: 48 (Texas). Join key to hdl.state_metadata.
state_abbr	varchar	State abbreviation. Example: TX
state_name	varchar	Full state name. Example: Texas
temp_min_raw	float	Daily minimum temperature (°F), area-weighted.
temp_max_raw	float	Daily maximum temperature (°F), area-weighted.
temp_avg_raw	float	Daily average temperature (°F), area-weighted.
hdd_65_raw	float	Heating degree days (base 65°F). MAX(0, 65 - temp_avg).
cdd_65_raw	float	Cooling degree days (base 65°F). MAX(0, temp_avg - 65).

hdl.temperature_county

Historical daily temperatures (°F) and degree days (HDD/CDD) for 3,108 CONUS counties from 2000 to present. County-level data enables the most granular weather analysis. When aggregating to larger geographies, area-weight temperatures and population-weight degree days by joining to hdl.county_annual_population for population weights and hdl.county_metadata for area weights.

Warning

This is a large table (~29.7M rows). Always filter by date range and/or geography to avoid slow queries.

Table ID: hdl.temperature_county

Sample Queries

-- Population-weighted HDD for a natural gas region
SELECT
    t.date,
    SUM(t.hdd_65_raw * p.population) / SUM(p.population) AS pop_weighted_hdd
FROM hdl.temperature_county t
JOIN hdl.county_annual_population p
  ON t.county_geo_id = p.county_geo_id
  AND EXTRACT(YEAR FROM t.date) = p.year
JOIN hdl.county_metadata m
  ON t.county_geo_id = m.county_geo_id
WHERE m.region_natgas = 'northeast'
  AND t.date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY t.date
ORDER BY t.date;

-- Coldest counties on a specific day
SELECT
    county_geo_id,
    temp_min_raw,
    temp_avg_raw,
    hdd_65_raw
FROM hdl.temperature_county
WHERE date = '2025-01-20'
ORDER BY temp_min_raw ASC
LIMIT 20;

Field Reference

Field Name	Data Type	Description
date	date	Calendar date.
county_geo_id	varchar	5-digit county FIPS code. Example: 48201 (Harris County, TX). Join key to hdl.county_metadata.
state_geo_id	varchar	2-digit state FIPS code.
climate_zone	varchar	NOAA climate zone classification.
temp_min_raw	float	Daily minimum temperature (°F).
temp_max_raw	float	Daily maximum temperature (°F).
temp_avg_raw	float	Daily average temperature (°F).
hdd_65_raw	float	Heating degree days (base 65°F).
cdd_65_raw	float	Cooling degree days (base 65°F).

hdl.temperature_msa

Historical daily temperatures (°F) and degree days (HDD/CDD) for 267 U.S. and Canadian Metropolitan Statistical Areas (MSAs) from 2000 to present. MSA-level data is useful for demand analysis tied to population centers. Join to hdl.msa_annual_population for population-weighted degree-day aggregations across metros.

Table ID: hdl.temperature_msa

Sample Queries

-- HDD/CDD for Houston MSA in winter 2024-2025
SELECT
    t.date,
    m.metro_name,
    t.temp_avg_raw,
    t.hdd_65_raw,
    t.cdd_65_raw
FROM hdl.temperature_msa t
JOIN hdl.msa_metadata m ON t.msa_id = m.msa_id
WHERE m.airport_code = 'IAH'
  AND t.date BETWEEN '2024-11-01' AND '2025-03-31'
ORDER BY t.date;

-- Top 10 MSAs by cumulative CDD (summer cooling demand)
SELECT
    t.msa_id,
    m.metro_name,
    SUM(t.cdd_65_raw) AS total_cdd
FROM hdl.temperature_msa t
JOIN hdl.msa_metadata m ON t.msa_id = m.msa_id
WHERE t.date BETWEEN '2024-06-01' AND '2024-08-31'
GROUP BY t.msa_id, m.metro_name
ORDER BY total_cdd DESC
LIMIT 10;

Field Reference

Field Name	Data Type	Description
date	date	Calendar date.
msa_id	varchar	CBSA (Core Based Statistical Area) code. Join key to hdl.msa_metadata.
temp_min_raw	float	Daily minimum temperature (°F).
temp_max_raw	float	Daily maximum temperature (°F).
temp_avg_raw	float	Daily average temperature (°F).
hdd_65_raw	float	Heating degree days (base 65°F).
cdd_65_raw	float	Cooling degree days (base 65°F).

hdl.temperature_forecast_state

GFS 16-day daily temperature and degree-day forecasts for 48 CONUS states. Each row represents one state’s forecast for one day from one model run. Multiple model runs per day produce overlapping forecasts — use init_hour = 18 for the most complete forecast horizon. Values are inherently area-weighted via gridding.

Table ID: hdl.temperature_forecast_state

Sample Queries

-- Latest 16-day forecast for Texas
SELECT
    date,
    temp_avg_raw,
    hdd_65_raw,
    cdd_65_raw,
    forecast_lead
FROM hdl.temperature_forecast_state
WHERE state_abbr = 'TX'
  AND init_hour = 18
  AND init_date = (SELECT MAX(init_date) FROM hdl.temperature_forecast_state WHERE init_hour = 18)
ORDER BY date;

-- National HDD forecast (area-weighted average across states)
SELECT
    date,
    AVG(hdd_65_raw) AS avg_hdd,
    AVG(cdd_65_raw) AS avg_cdd
FROM hdl.temperature_forecast_state
WHERE init_hour = 18
  AND init_date = (SELECT MAX(init_date) FROM hdl.temperature_forecast_state WHERE init_hour = 18)
GROUP BY date
ORDER BY date;

Field Reference

Field Name	Data Type	Description
date	date	Forecast target date.
init_date	date	Model initialization date (when the forecast was generated).
init_hour	int	Model initialization hour (UTC). Use 18 for the most complete horizon.
forecast_lead	int	Number of days ahead from the init_date. Example: 3 = 3-day forecast.
state_geo_id	varchar	2-digit state FIPS code.
state_abbr	varchar	State abbreviation. Example: TX
state_name	varchar	Full state name.
temp_min_raw	float	Forecast daily minimum temperature (°F).
temp_max_raw	float	Forecast daily maximum temperature (°F).
temp_avg_raw	float	Forecast daily average temperature (°F).
hdd_65_raw	float	Forecast heating degree days (base 65°F).
cdd_65_raw	float	Forecast cooling degree days (base 65°F).

hdl.temperature_forecast_county

GFS 16-day daily temperature and degree-day forecasts for 3,108 CONUS counties. Use init_hour = 18 for the most complete forecast horizon. Area-weight temperatures and population-weight degree days when aggregating to larger geographies.

Warning

This table contains ~190K rolling rows. Always filter by init_hour and init_date to avoid large result sets.

Table ID: hdl.temperature_forecast_county

Sample Queries

-- Forecast HDD for Permian Basin counties (next 7 days)
SELECT
    t.date,
    m.county_name,
    m.state_abbr,
    t.temp_avg_raw,
    t.hdd_65_raw
FROM hdl.temperature_forecast_county t
JOIN hdl.county_metadata m ON t.county_geo_id = m.county_geo_id
WHERE m.sub_region_natgas = 'Permian-TX'
  AND t.init_hour = 18
  AND t.init_date = (SELECT MAX(init_date) FROM hdl.temperature_forecast_county WHERE init_hour = 18)
  AND t.forecast_lead <= 7
ORDER BY t.date, m.county_name;

Field Reference

Field Name	Data Type	Description
date	date	Forecast target date.
init_date	date	Model initialization date.
init_hour	int	Model initialization hour (UTC). Use 18 for most complete horizon.
forecast_lead	int	Days ahead from init_date.
county_geo_id	varchar	5-digit county FIPS code. Join key to hdl.county_metadata.
state_geo_id	varchar	2-digit state FIPS code.
climate_zone	varchar	NOAA climate zone classification.
temp_min_raw	float	Forecast daily minimum temperature (°F).
temp_max_raw	float	Forecast daily maximum temperature (°F).
temp_avg_raw	float	Forecast daily average temperature (°F).
hdd_65_raw	float	Forecast heating degree days (base 65°F).
cdd_65_raw	float	Forecast cooling degree days (base 65°F).

hdl.temperature_forecast_msa

GFS 16-day daily temperature and degree-day forecasts for 267 U.S. and Canadian MSAs. Use init_hour = 18 for the most complete forecast horizon.

Table ID: hdl.temperature_forecast_msa

Sample Queries

-- Forecast for major metro areas (next 7 days)
SELECT
    t.date,
    m.metro_name,
    t.temp_avg_raw,
    t.hdd_65_raw,
    t.cdd_65_raw
FROM hdl.temperature_forecast_msa t
JOIN hdl.msa_metadata m ON t.msa_id = m.msa_id
WHERE m.airport_code IN ('JFK', 'ORD', 'IAH', 'LAX')
  AND t.init_hour = 18
  AND t.init_date = (SELECT MAX(init_date) FROM hdl.temperature_forecast_msa WHERE init_hour = 18)
ORDER BY m.metro_name, t.date;

Field Reference

Field Name	Data Type	Description
date	date	Forecast target date.
init_date	date	Model initialization date.
init_hour	int	Model initialization hour (UTC). Use 18 for most complete horizon.
forecast_lead	int	Days ahead from init_date.
msa_id	varchar	CBSA code. Join key to hdl.msa_metadata.
temp_min_raw	float	Forecast daily minimum temperature (°F).
temp_max_raw	float	Forecast daily maximum temperature (°F).
temp_avg_raw	float	Forecast daily average temperature (°F).
hdd_65_raw	float	Forecast heating degree days (base 65°F).
cdd_65_raw	float	Forecast cooling degree days (base 65°F).

hdl.county_annual_population

Annual population estimates by U.S. county with natural gas sub-region and region enrichment, covering 2010 to present (~52K rows). Primary use is population-weighting county-level HDD/CDD when aggregating degree days to larger geographies (regions, national). Join to hdl.temperature_county on county_geo_id and year.

Table ID: hdl.county_annual_population

Sample Queries

-- Population by natural gas region (latest year)
SELECT
    m.region_natgas,
    SUM(p.population) AS total_population
FROM hdl.county_annual_population p
JOIN hdl.county_metadata m ON p.county_geo_id = m.county_geo_id
WHERE p.year = (SELECT MAX(year) FROM hdl.county_annual_population)
GROUP BY m.region_natgas
ORDER BY total_population DESC;

Field Reference

Field Name	Data Type	Description
county_geo_id	varchar	5-digit county FIPS code. Join key to hdl.county_metadata and hdl.temperature_county.
state_geo_id	varchar	2-digit state FIPS code.
year	int	Year of the population estimate.
population	int	Estimated population for the county in that year.

hdl.msa_annual_population

Annual population estimates by Metropolitan Statistical Area (MSA), covering 2010 to present (~4K rows). Primary use is population-weighting MSA-level HDD/CDD when aggregating degree days across metro areas.

Table ID: hdl.msa_annual_population

Sample Queries

-- Top 10 MSAs by population (latest year)
SELECT
    p.msa_id,
    m.metro_name,
    p.population
FROM hdl.msa_annual_population p
JOIN hdl.msa_metadata m ON p.msa_id = m.msa_id
WHERE p.year = (SELECT MAX(year) FROM hdl.msa_annual_population)
ORDER BY p.population DESC
LIMIT 10;

Field Reference

Field Name	Data Type	Description
msa_id	varchar	CBSA (Core Based Statistical Area) code. Join key to hdl.msa_metadata.
year	int	Year of the population estimate.
population	int	Estimated population for the MSA in that year.

hdl.county_metadata

County dimension table (3,146 rows) with 5-digit FIPS key, natural gas sub-region/region assignments, county and state names, total area in km², and GeoJSON boundaries. Use for joining county-level data to natural gas regions, area-weighting temperatures, and rendering choropleth maps.

Table ID: hdl.county_metadata

Sample Queries

-- Counties in the Haynesville sub-region
SELECT
    county_geo_id,
    county_name,
    state_abbr,
    sub_region_natgas,
    total_area_km2
FROM hdl.county_metadata
WHERE sub_region_natgas LIKE 'Haynesville%'
ORDER BY county_name;

-- List all natural gas sub-regions with county counts
SELECT
    region_natgas,
    sub_region_natgas,
    COUNT(*) AS county_count,
    SUM(total_area_km2) AS total_area_km2
FROM hdl.county_metadata
GROUP BY region_natgas, sub_region_natgas
ORDER BY region_natgas, sub_region_natgas;

Field Reference

Field Name	Data Type	Description
county_geo_id	varchar	5-digit county FIPS code. Primary key. Example: 48201 (Harris County, TX).
state_geo_id	varchar	2-digit state FIPS code.
sub_region_natgas	varchar	Natural gas sub-region (e.g., Haynesville-LA, Permian-TX).
region_natgas	varchar	Natural gas region (e.g., gulf, northeast, west).
county_name	varchar	County name. Example: Harris
full_county_name	varchar	Full county name with state. Example: Harris County, TX
state_abbr	varchar	State abbreviation. Example: TX
total_area_km2	float	Total area in square kilometers (land + water).
boundaries	text	GeoJSON polygon geometry for the county boundary. Use for mapping.

hdl.msa_metadata

MSA dimension table (~267 rows) with CBSA key, airport codes, metro name, NOAA climate divisions, and GeoJSON boundaries. Join on msa_id for enrichment and mapping.

Table ID: hdl.msa_metadata

Sample Queries

-- Find MSA by airport code
SELECT
    msa_id,
    metro_name,
    airport_code,
    alt_airport_codes,
    country_code
FROM hdl.msa_metadata
WHERE airport_code IN ('IAH', 'DFW', 'JFK', 'ORD', 'LAX')
ORDER BY metro_name;

Field Reference

Field Name	Data Type	Description
msa_id	varchar	CBSA (Core Based Statistical Area) code. Primary key.
airport_code	varchar	Primary airport code for the metro area. Example: IAH
metro_name	varchar	Metropolitan area name. Example: Houston-The Woodlands-Sugar Land
country_code	varchar	Country code (US or CA).
alt_airport_codes	varchar	Alternative airport codes for the metro area.
noaa_climate_divisions	varchar	Associated NOAA climate division codes.
boundaries	text	GeoJSON polygon geometry for the MSA boundary. Use for mapping.

hdl.state_metadata

State dimension table (~49 rows) with 2-digit FIPS key, state abbreviation, state name, total area in km², and GeoJSON boundaries. Join on state_geo_id for area-weighting state temperatures to national level and for state-level mapping.

Table ID: hdl.state_metadata

Sample Queries

-- All states with areas
SELECT
    state_geo_id,
    state_abbr,
    state_name,
    total_area_km2
FROM hdl.state_metadata
ORDER BY total_area_km2 DESC;

Field Reference

Field Name	Data Type	Description
state_geo_id	varchar	2-digit state FIPS code. Primary key. Example: 48 (Texas).
state_abbr	varchar	State abbreviation. Example: TX
state_name	varchar	Full state name. Example: Texas
total_area_km2	float	Total area in square kilometers (land + water).
boundaries	text	GeoJSON polygon geometry for the state boundary. Use for mapping.

Texas Gas Flaring

Daily gas flaring and production volumes at oil and gas sites across Texas from Railroad Commission (RRC) SWR-32 exception filings. Includes classified flaring reasons via LLM extraction, geographic flare/vent point locations, and both reported (actual filed) and estimated (modeled) data sources. Data is deduplicated across overlapping filings.

hdl.flaring_records

Daily gas flaring and production volumes at oil and gas sites across Texas, derived from Railroad Commission (RRC) SWR-32 exception filings. Each row represents one property’s flaring data for one day. Includes both reported (actual filed data) and estimated (modeled from release rates × operator discount ratios) sources. Classified flaring reasons are extracted via LLM from filing documents.

Warning

Volumes are stored in MCF but must always be reported in BCF/d. Use SUM(gas_flared_mcf) / COUNT(DISTINCT date) / 1000000.0 for daily rates. Do NOT use AVG(gas_flared_mcf) — that averages per record, not per day. NULL values mean unreported (not zero). Always distinguish reported vs estimated sources in results.

Table ID: hdl.flaring_records

Sample Queries

-- Monthly flaring rate by source type (BCF/d)
SELECT
    DATE_TRUNC('month', date::date) AS month,
    source,
    SUM(gas_flared_mcf) / COUNT(DISTINCT date) / 1000000.0 AS flaring_bcf_d,
    COUNT(DISTINCT filing_number) AS filing_count
FROM hdl.flaring_records
WHERE date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', date::date), source
ORDER BY month, source;

-- Top operators by reported flaring volume
SELECT
    operator_name,
    SUM(gas_flared_mcf) / 1000000.0 AS total_flared_bcf,
    COUNT(DISTINCT filing_number) AS filings
FROM hdl.flaring_records
WHERE source = 'reported'
  AND date >= '2024-01-01'
GROUP BY operator_name
ORDER BY total_flared_bcf DESC
LIMIT 20;

-- Flaring by reason category
SELECT
    reason_label,
    COUNT(*) AS record_count,
    SUM(gas_flared_mcf) / 1000000.0 AS total_flared_bcf
FROM hdl.flaring_records
WHERE source = 'reported'
  AND date >= '2024-01-01'
GROUP BY reason_label
ORDER BY total_flared_bcf DESC;

Field Reference

Field Name	Data Type	Description
date	date	Calendar date of the flaring record.
gas_flared_mcf	float	Gas flared volume in MCF. Convert to BCF/d for reporting.
gas_production_mcf	float	Gas production volume in MCF (incomplete — only production reported alongside flaring filings, not total output).
source	varchar	Data source: reported (actual filed data) or estimated (modeled from release rates).
site_name	varchar	Name of the flaring site/lease.
property_ids	varchar	RRC property identifiers associated with the filing.
filing_number	varchar	SWR-32 filing number.
exception_number	varchar	RRC exception number.
sequence_number	float	Sequence number within the filing.
submitted_date	date	Date the filing was submitted to the RRC.
filing_status	varchar	Status of the filing (e.g., Approved).
release_rate_mcf_per_day	float	Authorized flaring release rate in MCF/day.
effective_date	date	Start date of the flaring authorization.
expiration_date	date	End date of the flaring authorization.
operator_name	varchar	Operating company name.
created_at	datetime	When SynMax ingested this record.
reason_label	varchar	Classified flaring reason (e.g., Pipeline Unavailable, System Upset, Well Completion / Extended Clean-Up).
reason_summary	varchar	LLM-generated summary of the flaring reason from the filing document.

hdl.flaring_records_with_locations

Extends hdl.flaring_records with flare/vent point geographic locations (county, district, latitude/longitude, release type). Rows are fanned out by location count — approximately 7% of filings have multiple flare/vent points. Always multiply volumes by location_weight when aggregating by geography to avoid double-counting.

Warning

When aggregating volumes by geography, always use SUM(gas_flared_mcf * location_weight) instead of SUM(gas_flared_mcf) to account for multi-location filings.

Table ID: hdl.flaring_records_with_locations

Sample Queries

-- Flaring by county (properly weighted)
SELECT
    county,
    district,
    SUM(gas_flared_mcf * location_weight) / COUNT(DISTINCT date) / 1000000.0 AS flaring_bcf_d
FROM hdl.flaring_records_with_locations
WHERE source = 'reported'
  AND date >= '2024-01-01'
GROUP BY county, district
ORDER BY flaring_bcf_d DESC
LIMIT 20;

-- Map flare locations
SELECT DISTINCT
    latitude,
    longitude,
    site_name,
    operator_name,
    release_type,
    county
FROM hdl.flaring_records_with_locations
WHERE latitude IS NOT NULL
  AND date >= '2025-01-01'
LIMIT 500;

Field Reference

Field Name	Data Type	Description
date	date	Calendar date of the flaring record.
gas_flared_mcf	float	Gas flared volume in MCF. Multiply by location_weight when aggregating by geography.
gas_production_mcf	float	Gas production volume in MCF (incomplete).
source	varchar	reported or estimated.
filing_number	varchar	SWR-32 filing number.
property_ids	varchar	RRC property identifiers.
operator_name	varchar	Operating company name.
site_name	varchar	Name of the flaring site/lease.
location_index	float	Index of this location within the filing (0-based).
location_name	varchar	Name of the flare/vent point.
county	varchar	County where the flare/vent point is located.
district	varchar	RRC district code.
latitude	float	Latitude of the flare/vent point.
longitude	float	Longitude of the flare/vent point.
release_type	varchar	Type of release: Flare or Vent.
release_height	float	Height of the flare/vent stack in feet.
location_weight	float	Weight factor for multi-location filings. 1.0 if single location; 0.5 if two locations, etc.
created_at	datetime	When SynMax ingested this record.

hdl.active_exceptions

Currently active (Approved) SWR-32 exception filings where today falls within the authorization window. Contains authorization metadata only — no volume data. Join to hdl.flaring_records on filing_number for associated flaring volumes.

Table ID: hdl.active_exceptions

Sample Queries

-- Active exceptions expiring within 30 days
SELECT
    filing_number,
    operator_name,
    site_name,
    effective_date,
    expiration_date,
    days_remaining,
    reason_label,
    primary_county
FROM hdl.active_exceptions
WHERE days_remaining <= 30
ORDER BY days_remaining ASC;

-- Count of active exceptions by reason
SELECT
    reason_label,
    COUNT(*) AS exception_count,
    AVG(cumulative_days) AS avg_duration_days
FROM hdl.active_exceptions
GROUP BY reason_label
ORDER BY exception_count DESC;

Field Reference

Field Name	Data Type	Description
filing_number	varchar	SWR-32 filing number. Join key to hdl.flaring_records.
operator_name	varchar	Operating company name.
site_name	varchar	Name of the flaring site/lease.
effective_date	date	Start date of the current authorization.
expiration_date	date	End date of the current authorization.
days_remaining	float	Days remaining until expiration.
cumulative_days	float	Total days of authorization across all sequences for this filing.
connected_to_gas	boolean	Whether the site is connected to a gas gathering system.
permanent_exception	boolean	Whether this is a permanent (non-expiring) exception.
exception_number	varchar	RRC exception number.
sequence_number	float	Sequence number (filings can have multiple renewal sequences).
primary_county	varchar	Primary county of the flaring site.
primary_district	varchar	Primary RRC district code.
primary_latitude	float	Latitude of the primary flare/vent point.
primary_longitude	float	Longitude of the primary flare/vent point.
release_type	varchar	Type of release: Flare or Vent.
num_locations	float	Number of flare/vent points in this filing.
reason_label	varchar	Classified flaring reason.
reason_summary	varchar	LLM-generated summary of the flaring reason.

North Sea / UKCS

UK Continental Shelf (UKCS) North Sea oil and gas data sourced from the North Sea Transition Authority (NSTA). Combines field production history, pipeline infrastructure, and licensing into a single interconnected schema. The universal join key across all three North Sea views is field_name. Use for production decline analysis, infrastructure and tieback economics, operator benchmarking, and unified asset context across the North Sea basin.

hdl.ns_field_overview

Unified UKCS (UK Continental Shelf) field context providing one row per North Sea field with metadata, latest production rates, cumulative volumes, well/pipeline/licence counts, and field geometry. Start here to look up any named North Sea asset or scan the basin. The universal join key across all North Sea views is field_name.

Table ID: hdl.ns_field_overview

Sample Queries

-- Currently producing fields sorted by latest oil production
SELECT
    field_name,
    field_type,
    current_operator,
    latest_oil_mmbld,
    latest_gas_mmcfd,
    cumulative_oil_m3,
    well_count,
    pipeline_count
FROM hdl.ns_field_overview
WHERE status = 'PRODUCING'
ORDER BY latest_oil_mmbld DESC
LIMIT 20;

-- Field inventory by operator
SELECT
    current_operator,
    COUNT(*) AS field_count,
    SUM(latest_oil_mmbld) AS total_oil_mmbld,
    SUM(latest_gas_mmcfd) AS total_gas_mmcfd
FROM hdl.ns_field_overview
WHERE status = 'PRODUCING'
GROUP BY current_operator
ORDER BY total_oil_mmbld DESC
LIMIT 15;

Field Reference

Field Name	Data Type	Description
field_name	varchar	UKCS field name. Universal join key across all North Sea views.
field_type	varchar	Primary product type: OIL, GAS, CONDENSATE, etc.
status	varchar	Current field status: PRODUCING, DECOMMISSIONING, SHUT-IN, etc.
current_operator	varchar	Current operating company.
discovery_date	date	Date of field discovery.
production_start_date	date	Date production commenced.
depth_m	float	Water depth in meters.
source	varchar	Data source classification (offshore).
well_count	float	Number of wells in the field.
pipeline_count	float	Number of pipelines connected to the field.
licence_count	float	Number of licences covering the field.
first_prod_date	date	Earliest production date in the time series.
latest_prod_date	date	Most recent production date.
latest_oil_mmbld	float	Latest monthly oil production rate in million barrels/day.
latest_gas_mmcfd	float	Latest monthly gas production rate in million cubic feet/day.
latest_water_mmbld	float	Latest monthly water production rate in million barrels/day.
cumulative_oil_m3	float	Cumulative oil production in cubic meters.
cumulative_gas_ksm3	float	Cumulative gas production in thousand standard cubic meters.
geometry	text	WKB hex polygon geometry for the field boundary.

hdl.ns_field_infrastructure

Pipeline network per UKCS field: each pipeline’s fluid type, status, operator, dimensions, and which other fields share the same pipeline. Use for infrastructure context, tieback candidate identification, and network mapping. Join to hdl.ns_field_overview on field_name.

Table ID: hdl.ns_field_infrastructure

Sample Queries

-- Active pipelines for a specific field
SELECT
    field_name,
    pipeline_name,
    fluid,
    pipeline_status,
    pipeline_operator,
    diameter_mm,
    length_m,
    connected_field
FROM hdl.ns_field_infrastructure
WHERE field_name = 'FORTIES'
  AND pipeline_status = 'ACTIVE'
ORDER BY fluid, pipeline_name;

-- Fields connected via shared pipelines
SELECT
    field_name,
    connected_field,
    pipeline_name,
    fluid
FROM hdl.ns_field_infrastructure
WHERE connected_field IS NOT NULL
  AND connected_field != ''
  AND pipeline_status = 'ACTIVE'
ORDER BY field_name, connected_field;

Field Reference

Field Name	Data Type	Description
field_name	varchar	UKCS field name. Join key to hdl.ns_field_overview.
pipeline_id	varchar	Unique pipeline identifier from NSTA.
pipeline_name	varchar	Descriptive pipeline name.
fluid	varchar	Fluid type carried: OIL, GAS, CONDENSATE, MULTIPHASE, etc.
pipeline_status	varchar	Pipeline status: ACTIVE, DECOMMISSIONED, etc.
pipeline_operator	varchar	Company operating the pipeline.
diameter_mm	float	Pipeline diameter in millimeters.
length_m	float	Pipeline length in meters.
connected_field	varchar	Other field(s) connected via this pipeline (for tieback analysis).

hdl.ns_field_prod_performance

Monthly production time-series per UKCS field with 3-month and 12-month rolling averages, year-on-year percentage change, and water cut. Use for decline analysis, production anomaly detection, and reservoir maturity assessment. Join to hdl.ns_field_overview on field_name.

Table ID: hdl.ns_field_prod_performance

Sample Queries

-- Production history for a specific field
SELECT
    field_name,
    period_date,
    oil_prod_mmbld,
    gas_prod_mmcfd,
    water_cut_pct,
    oil_yoy_pct_change
FROM hdl.ns_field_prod_performance
WHERE field_name = 'FORTIES'
ORDER BY period_date DESC
LIMIT 36;

-- Fields with steepest year-on-year oil decline
SELECT
    field_name,
    period_date,
    oil_prod_mmbld,
    oil_yoy_pct_change
FROM hdl.ns_field_prod_performance
WHERE period_date = (SELECT MAX(period_date) FROM hdl.ns_field_prod_performance)
  AND oil_yoy_pct_change IS NOT NULL
  AND oil_prod_mmbld > 0.001
ORDER BY oil_yoy_pct_change ASC
LIMIT 20;

Field Reference

Field Name	Data Type	Description
field_name	varchar	UKCS field name. Join key to hdl.ns_field_overview.
period_date	date	Month of the production record (15th of each month by convention).
oil_prod_mmbld	float	Monthly oil production rate in million barrels/day.
gas_prod_mmcfd	float	Monthly gas production rate in million cubic feet/day.
water_prod_mmbld	float	Monthly water production rate in million barrels/day.
oil_3mo_avg	float	3-month rolling average oil production (million barrels/day).
oil_12mo_avg	float	12-month rolling average oil production (million barrels/day).
oil_yoy_pct_change	float	Year-on-year percentage change in oil production.
water_cut_pct	float	Water cut percentage: water / (water + oil) * 100.

LNG Feed Gas

Daily natural gas feed gas deliveries to U.S. LNG export terminals, derived from pipeline nomination data. Covers 9 terminals including Calcasieu Pass, Cameron, Corpus Christi, Cove Point, Elba Island, Freeport, Golden Pass, Plaquemines, and Sabine Pass.

hdl.lng_feed_gas

Daily natural gas feed gas deliveries to 9 U.S. LNG export terminals, derived from pipeline nomination data. Covers Calcasieu Pass, Cameron, Corpus Christi, Cove Point, Elba Island, Freeport, Golden Pass, Plaquemines, and Sabine Pass. For facilities with intrastate feeds (Freeport, Corpus Christi), feed gas includes modeled estimates.

Warning

Do NOT sum feed_gas over time directly to represent a daily rate. Always use AVG(feed_gas) for temporal aggregation, and multiply by the number of days if you need a total volume.

Table ID: hdl.lng_feed_gas

Sample Queries

-- Daily total LNG feed gas across all terminals
SELECT
    gas_day,
    SUM(feed_gas) AS total_feed_gas_bcfd
FROM hdl.lng_feed_gas
WHERE gas_day >= '2025-01-01'
GROUP BY gas_day
ORDER BY gas_day;

-- Monthly average feed gas by terminal
SELECT
    terminal_name,
    DATE_TRUNC('month', gas_day::date) AS month,
    AVG(feed_gas) AS avg_feed_gas_bcfd,
    COUNT(*) AS days_reported
FROM hdl.lng_feed_gas
WHERE gas_day >= '2024-01-01'
GROUP BY terminal_name, DATE_TRUNC('month', gas_day::date)
ORDER BY terminal_name, month;

-- Compare terminal utilization (latest week)
SELECT
    terminal_name,
    AVG(feed_gas) AS avg_feed_gas_bcfd,
    MAX(feed_gas) AS max_feed_gas_bcfd,
    MIN(feed_gas) AS min_feed_gas_bcfd
FROM hdl.lng_feed_gas
WHERE gas_day >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY terminal_name
ORDER BY avg_feed_gas_bcfd DESC;

Field Reference

Field Name	Data Type	Description
gas_day	date	The gas day (standard pipeline day).
terminal_name	varchar	LNG export terminal name. Values: Calcasieu Pass, Cameron, Corpus Christi, Cove Point, Elba Island, Freeport, Golden Pass, Plaquemines, Sabine Pass.
feed_gas	float	Feed gas delivery volume in Bcf/d (billion cubic feet per day).
updated_at	datetime	Timestamp of the last data update.