Centia Snapshot Catalog

{base}/catalog.json                                   Catalog: one "child" link per dataset
{base}/schema=S/relation=R/collection.json            Collection: title, extent, CRS, one "item" link per snapshot date
{base}/schema=S/relation=R/latest.json                Pointer to the newest snapshot (newer stores only)
{base}/schema=S/relation=R/_gc2_snapshot_date=D/item.json          Item: one snapshot
{base}/schema=S/relation=R/_gc2_snapshot_date=D/data-<uuid>.parquet   the data (GeoParquet when spatial)
{base}/schema=S/relation=R/_gc2_snapshot_date=D/data-<uuid>.fgb       optional FlatGeobuf
{base}/schema=S/relation=R/_gc2_snapshot_date=D/metadata-<uuid>.json  column schema, row count, crs, bbox, formats

INSTALL httpfs; LOAD httpfs;
INSTALL spatial; LOAD spatial;          -- GEOMETRY type, ST_* functions, GeoParquet awareness
-- Public bucket, anonymous access (needed for globs; HTTP URLs cannot glob):
CREATE SECRET pub (TYPE s3, REGION 'eu-west-1', KEY_ID '', SECRET '');

-- every dataset in the store
SELECT l.title AS dataset, l.href
FROM (SELECT unnest(links) AS l FROM read_json('{base}/catalog.json'))
WHERE l.rel = 'child';

-- one dataset: extent, CRS, snapshot dates
SELECT id, title, description, keywords,
       extent.spatial.bbox[1]              AS bbox_wgs84,
       extent.temporal.interval[1]         AS first_last_snapshot,
       summaries                           -- {"gc2:schema_version": [...], "proj:code": ["EPSG:25832"]}
FROM read_json('{base}/schema=S/relation=R/collection.json');

SELECT l.href FROM (SELECT unnest(links) AS l FROM read_json('{base}/schema=S/relation=R/collection.json'))
WHERE l.rel = 'item' ORDER BY l.href DESC;          -- newest first is how they are listed

SELECT id, bbox, geometry.type AS footprint,
       properties."gc2:row_count"    AS rows,
       properties."proj:code"        AS crs,
       properties.datetime           AS snapshot_date,
       assets.data.href, assets.data.title,          -- "GeoParquet" or "Parquet"
       assets.flatgeobuf.href                        -- NULL when not produced
FROM read_json('{base}/schema=S/relation=R/_gc2_snapshot_date=D/item.json');

-- GeoParquet metadata: present only for spatial files
SELECT json_extract_string(decode(value), '$.primary_column')                       AS geom_column,
       json_extract_string(decode(value), '$.columns.the_geom.crs.id.code')          AS epsg,
       json_extract_string(decode(value), '$.columns.the_geom.geometry_types')       AS geometry_types
FROM parquet_kv_metadata('{file}.parquet') WHERE key = 'geo';      -- 0 rows => non-spatial

-- or simply look at the column type DuckDB infers (spatial extension loaded)
SELECT column_name, column_type FROM (DESCRIBE SELECT * FROM read_parquet('{file}.parquet'))
WHERE column_type LIKE 'GEOMETRY%';                                -- e.g. GEOMETRY('EPSG:25832')

-- one snapshot (HTTP is fine for single files)
SELECT count(*) FROM read_parquet('{base}/schema=S/relation=R/_gc2_snapshot_date=D/data-<uuid>.parquet');

-- the newest snapshot through the GC2 API (fixed URL, needs a Bearer token unless the layer is public)
CREATE SECRET api (TYPE http, BEARER_TOKEN '<token>');
SELECT * FROM read_parquet('https://<gc2-host>/api/v4/schemas/S/relations/R/snapshots/latest/data') LIMIT 10;

-- the newest snapshot from the store: follow latest.json (newer stores) or the first item link
SELECT item, assets.data.href FROM read_json('{base}/schema=S/relation=R/latest.json');

-- the whole history, one row per snapshot date (s3:// + secret from §2)
SELECT _gc2_snapshot_date, count(*)
FROM read_parquet('s3://gc2-parquet/centia-io/dk/schema=S/relation=R/_gc2_snapshot_date=*/data-*.parquet',
                  hive_partitioning = true)
GROUP BY 1 ORDER BY 1;