An environmental baseline survey delivers orthophoto and elevation tiles for a licence area, and before anything is processed the GIS team has to know what arrived: how much ground it covers, at what resolution, and whether every tile is in the same coordinate system. Six tiles over two days, eighteen image directories, and not one pixel read. A TIFF is a header pointing at a chain of image file directories, and an overview pyramid is simply more directories in the same file, so each tile yields three rows: full resolution and two overviews. A reader that follows only the first directory returns six rows and looks perfectly reasonable. Both classic TIFF and BigTIFF are in the delivery and chain identically. Two faults are planted in the second delivery and the catalogue finds both: one tile was delivered in UTM zone 14N while the survey is 13N, so its coordinates are perfectly valid numbers that put it several hundred kilometres east of where it belongs, and one tile is at half the resolution of the others, which a file listing cannot see and a tag directory can. The coverage query is restricted to full resolution and the correct zone, because the overviews would count the same ground three times.
-- ============================================================================
-- Aerial Survey Raster Catalogue - Setup Script
-- ============================================================================
-- An environmental baseline survey delivers orthophoto and elevation tiles for
-- a licence area. Before anything is processed the GIS team has to know what
-- arrived: how much ground it covers, at what resolution, and whether every
-- tile is in the same coordinate system.
--
-- 11 March three orthophoto tiles
-- 12 March two more orthophotos and a BigTIFF elevation model
--
-- Answering those questions by opening the images means reading gigabytes.
-- Answering them from the tag directories means reading kilobytes, and that
-- is what this reader does: one row per image directory, describing the
-- raster rather than decoding it.
--
-- 1. raster_tiles external, DISCOVER over the landing folder
-- 2. raster_catalog DELTA, the survey catalogue
-- ============================================================================
-- ----------------------------------------------------------------------------
-- STEP 1: Zone and schema
-- ----------------------------------------------------------------------------
CREATE ZONE IF NOT EXISTS {{zone_name}} TYPE EXTERNAL
COMMENT 'External tables - demo datasets and file-backed data';
CREATE SCHEMA IF NOT EXISTS {{zone_name}}.raster_survey
COMMENT 'GeoTIFF tag directories read in place, catalogued into Delta';
-- ----------------------------------------------------------------------------
-- STEP 2: Register the landing folder with DISCOVER
-- ----------------------------------------------------------------------------
-- A TIFF is recognised from its byte-order marker and magic number, 42 for
-- classic and 43 for BigTIFF, and both are in this delivery.
--
-- One row comes out per image DIRECTORY, not per file. A TIFF is a chain of
-- directories and an overview pyramid is simply more of them in the same
-- file, so these six tiles produce eighteen rows: full resolution and two
-- overviews each. A reader that follows only the first directory sees a third
-- of what the file describes.
-- ----------------------------------------------------------------------------
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.raster_survey.raster_tiles;
DISCOVER {{zone_name}}.raster_survey.raster_tiles
PATH '{{data_subdir}}/landing'
WITH (FILE_METADATA = true);
-- ----------------------------------------------------------------------------
-- STEP 3: The curated Delta catalogue
-- ----------------------------------------------------------------------------
-- Ground extent is derived rather than stored: a raster's width in metres is
-- its pixel count times its pixel scale, and keeping that as a column is what
-- lets the catalogue answer a coverage question without arithmetic in every
-- query.
-- ----------------------------------------------------------------------------
CREATE DELTA TABLE IF NOT EXISTS {{zone_name}}.raster_survey.raster_catalog (
tile VARCHAR,
delivered_on VARCHAR,
source_file VARCHAR,
directory_index INTEGER,
is_full_resolution BOOLEAN,
width BIGINT,
height BIGINT,
bands INTEGER,
bits_per_sample INTEGER,
pixel_scale_m DOUBLE,
epsg INTEGER,
origin_x DOUBLE,
origin_y DOUBLE,
ground_width_m DOUBLE,
ground_height_m DOUBLE
) LOCATION '{{data_subdir}}/curated/raster_catalog';## What this demo does An environmental baseline survey delivers orthophoto and elevation tiles for a licence area, and before anything is processed the GIS team has to know what arrived: how much ground it covers, at what resolution, and whether every tile is in the same coordinate system. Six tiles over two days, eighteen image directories, and not one pixel read. A TIFF is a header pointing at a chain of image file directories, and an overview pyramid is simply more directories in the same file, so each tile yields three rows: full resolution and two overviews. A reader that follows only the first directory returns six rows and looks perfectly reasonable. Both classic TIFF and BigTIFF are in the delivery and chain identically. Two faults are planted in the second delivery and the catalogue finds both: one tile was delivered in UTM zone 14N while the survey is 13N, so its coordinates are perfectly valid numbers that put it several hundred kilometres east of where it belongs, and one tile is at half the resolution of the others, which a file listing cannot see and a tag directory can. The coverage query is restricted to full resolution and the correct zone, because the overviews would count the same ground three times. ## What it creates - `{{zone_name}}.raster_survey.raster_tiles` (external table) - `{{zone_name}}.raster_survey.raster_catalog` (delta table) ## Running it The demo ships `setup.sql`, `queries.sql` and `cleanup.sql` under `demos/subsurface/geotiff-raster-catalog`. The queries carry their own `ASSERT`s, so a run that completes has verified its own numbers. Data: 6 files, 0.0 MB.