Every active oil and gas lease on the United States Outer Continental Shelf, as the Bureau of Ocean Energy Management publishes it, loaded against the official block grid it is measured on. The data is real and public domain: 1870 lease polygons carrying lease number, mineral type, royalty rate, effective date, status and current area, and 29,186 block polygons across the three Gulf of Mexico planning areas. A shapefile is not one file but a set, and the demo reads the .shp for geometry, the .dbf for attributes and the .prj for the coordinate system as one table. Real data brings constraints a written fixture never would. DBF truncates every field name to ten characters, so LEASE_NUMBER arrives as lease_numb and CURRENT_AREA as current_ar, some ending in a bare underscore where the cut landed, and the demo asserts those names rather than pretending they are tidy. Every attribute arrives as the file's own text with the dBase type recorded in metadata rather than applied, so casting is the caller's decision and the demo casts explicitly. The coordinate system is NAD27 rather than the WGS84 most tools assume. And the register spans ninety years: 138 leases still active took effect before 1970, the oldest in February 1936, while every lease still in its primary term began in 2016 or later, two populations that do not overlap at all.
-- ============================================================================
-- Offshore Lease Footprint - Setup Script
-- ============================================================================
-- Every active oil and gas lease on the United States Outer Continental Shelf,
-- as the Bureau of Ocean Energy Management publishes it, loaded against the
-- official block grid the leases are measured on.
--
-- 11 March leases 1870 lease polygons
-- 12 March blocks 29,186 block polygons, the reference grid
--
-- The data is REAL and is in the public domain, being a work of the United
-- States government. See ATTRIBUTION.md in the parent folder.
--
-- A shapefile is not one file. It is a set that must be read together:
--
-- .shp the geometry, as a record per shape
-- .dbf the attributes, as a dBASE III table, one row per shape in order
-- .shx the index from record number to byte offset in the .shp
-- .prj the coordinate reference system, as WKT
-- .cpg the code page the .dbf strings are in
--
-- The reader opens the .shp and pulls its siblings in beside it, which is why
-- DISCOVER is pointed at the .shp and the rest are found rather than named.
--
-- 1. leases external, DISCOVER over the lease geometry
-- 2. blocks external, DISCOVER over the block grid
-- 3. lease_register DELTA, the curated register
-- ============================================================================
-- ----------------------------------------------------------------------------
-- STEP 1: Zone and schema
-- ----------------------------------------------------------------------------
CREATE ZONE IF NOT EXISTS {{zone_name}} TYPE EXTERNAL
COMMENT 'External tables - demo datasets and file-backed data';
CREATE SCHEMA IF NOT EXISTS {{zone_name}}.surface_land
COMMENT 'BOEM lease and block shapefiles read in place, curated into Delta';
-- ----------------------------------------------------------------------------
-- STEP 2: Register both layers with DISCOVER
-- ----------------------------------------------------------------------------
-- A shapefile is recognised from the .shp header's file code and shape type, a
-- two-field agreement no other format produces. Both of these are file code
-- 9994 and shape type 5, which is Polygon.
--
-- Three columns come from the geometry side (record_index, geometry_type and
-- geometry as OGC well-known binary) and the rest come from the .dbf, with
-- their names lowercased.
--
-- Those names are worth looking at, because they are the format speaking
-- rather than a naming choice. DBF truncates every field name to TEN
-- characters: LEASE_NUMBER becomes lease_numb, SALE_NUMBER becomes sale_numbe,
-- CURRENT_AREA becomes current_ar, and LEASE_EFF_DATE becomes lease_eff: the
-- cut landed mid-word and left DBF holding LEASE_EFF_, whose trailing
-- underscore is dropped when the name is normalised. This is what real
-- shapefile columns look like.
-- ----------------------------------------------------------------------------
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.surface_land.leases;
DISCOVER {{zone_name}}.surface_land.leases
PATH '{{data_subdir}}/landing/leases.shp'
WITH (FILE_METADATA = true);
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.surface_land.blocks;
DISCOVER {{zone_name}}.surface_land.blocks
PATH '{{data_subdir}}/landing/blocks.shp'
WITH (FILE_METADATA = true);
-- (excerpt; run the demo for the full script)## What this demo does Every active oil and gas lease on the United States Outer Continental Shelf, as the Bureau of Ocean Energy Management publishes it, loaded against the official block grid it is measured on. The data is real and public domain: 1870 lease polygons carrying lease number, mineral type, royalty rate, effective date, status and current area, and 29,186 block polygons across the three Gulf of Mexico planning areas. A shapefile is not one file but a set, and the demo reads the .shp for geometry, the .dbf for attributes and the .prj for the coordinate system as one table. Real data brings constraints a written fixture never would. DBF truncates every field name to ten characters, so LEASE_NUMBER arrives as lease_numb and CURRENT_AREA as current_ar, some ending in a bare underscore where the cut landed, and the demo asserts those names rather than pretending they are tidy. Every attribute arrives as the file's own text with the dBase type recorded in metadata rather than applied, so casting is the caller's decision and the demo casts explicitly. The coordinate system is NAD27 rather than the WGS84 most tools assume. And the register spans ninety years: 138 leases still active took effect before 1970, the oldest in February 1936, while every lease still in its primary term began in 2016 or later, two populations that do not overlap at all. ## What it creates - `{{zone_name}}.surface_land.leases` (external table) - `{{zone_name}}.surface_land.blocks` (external table) - `{{zone_name}}.surface_land.lease_register` (delta table) ## Running it The demo ships `setup.sql`, `queries.sql` and `cleanup.sql` under `demos/subsurface/shapefile-surface-footprint`. The queries carry their own `ASSERT`s, so a run that completes has verified its own numbers. Data: 10 files, 9.3 MB.