# Querying GeoParquet Across Domains with Trino

When a query spans more than two domains, or the sides sit in different storage technologies, a database-to-database foreign wrapper stops being the right shape. A federation engine reading each domain’s published GeoParquet snapshot handles it — provided the snapshots are partitioned so the engine can prune, and the query is written so the engine’s predicate pushdown reaches the file statistics rather than reading every row group. This guide sets that up, proves pruning from the query plan, and shows the layout decisions that decide whether a cross-domain join scans gigabytes or terabytes. It is the engine-based path described in Federated Query and Cross-Domain Spatial Joins within Federated Ownership & Routing Architecture.

# Prerequisites

# Step-by-Step Implementation

# 1. Lay the snapshots out so pruning is possible

Partition pruning happens on directory structure; row-group skipping happens on column statistics. Both need to be designed in, and neither can be added retrospectively without rewriting.

# Published snapshot layout. Directory partitions prune whole files; the sort order
# inside each file makes the bbox column statistics tight enough to skip row groups.
s3://mesh-published/hydrology/flood_extents/
  version=v3.1.0-crs:EPSG:4326-res:10m/
    grid=33N/            # spatial partition — a UTM zone or a tile-pyramid subtree
      window=2026-08/    # temporal partition — the provider's reissue cadence
        part-0000.parquet
        part-0001.parquet

Verify the partitions are actually registered rather than merely present on disk:

trino --server "$TRINO_URL" --execute \
  "SELECT * FROM ${CATALOG}.hydrology.\"flood_extents\$partitions\" LIMIT 5"

# 2. Register the tables and confirm the geometry column is typed

A GeoParquet geometry column arrives as binary unless the connector is told what it is, and an untyped column defeats every spatial predicate.

-- register.sql — one external table per published product.
CREATE TABLE IF NOT EXISTS hive.mesh.flood_extents (
    feature_id   VARCHAR,
    severity     INTEGER,
    geom_wkb     VARBINARY,          -- GeoParquet stores WKB
    bbox_minx    DOUBLE,             -- flattened bbox columns carry the statistics
    bbox_miny    DOUBLE,
    bbox_maxx    DOUBLE,
    bbox_maxy    DOUBLE,
    version      VARCHAR,
    grid         VARCHAR,
    window       VARCHAR
)
WITH (
    external_location = 's3://mesh-published/hydrology/flood_extents/',
    format = 'PARQUET',
    partitioned_by = ARRAY['version', 'grid', 'window']
);

The flattened bbox columns are what make skipping work. Parquet keeps min/max statistics per row group for scalar columns and nothing usable for an opaque binary geometry, so a predicate expressed against bbox_minx and friends can skip row groups while one expressed only against the geometry cannot.

Verify the statistics exist and are tight:

trino --server "$TRINO_URL" --execute \
  "SHOW STATS FOR hive.mesh.flood_extents" | head -12
# The bbox columns must show distinct-value and range statistics, not nulls.

# 3. Write the join so both pruning stages engage

-- cross_domain_join.sql — partition pruning, row-group skipping, then exact predicate.
WITH bounds AS (
    SELECT ST_GeometryFromText('POLYGON((-1 50, 1 50, 1 52, -1 52, -1 50))') AS g,
           -1.0 AS minx, 50.0 AS miny, 1.0 AS maxx, 52.0 AS maxy
)
SELECT p.parcel_id, h.severity,
       ST_Area(ST_Intersection(
           ST_GeomFromBinary(p.geom_wkb), ST_GeomFromBinary(h.geom_wkb))) AS overlap
FROM hive.mesh.parcels p
JOIN hive.mesh.flood_extents h
  ON  p.bbox_minx <= h.bbox_maxx AND p.bbox_maxx >= h.bbox_minx
  AND p.bbox_miny <= h.bbox_maxy AND p.bbox_maxy >= h.bbox_miny
CROSS JOIN bounds b
-- Stage 1: partition pruning. Naming the partition columns skips whole directories.
WHERE p.version = 'v1.2.0-crs:EPSG:4326-res:10m'
  AND h.version = 'v3.1.0-crs:EPSG:4326-res:10m'
  AND p.grid = '33N' AND h.grid = '33N'
  AND h.window = '2026-08'
-- Stage 2: row-group skipping on scalar statistics.
  AND p.bbox_maxx >= b.minx AND p.bbox_minx <= b.maxx
  AND p.bbox_maxy >= b.miny AND p.bbox_miny <= b.maxy
  AND h.bbox_maxx >= b.minx AND h.bbox_minx <= b.maxx
  AND h.bbox_maxy >= b.miny AND h.bbox_miny <= b.maxy
-- Stage 3: the exact predicate, on what survived. Expensive, and now cheap to run.
  AND ST_Intersects(ST_GeomFromBinary(p.geom_wkb), b.g)
  AND ST_Intersects(ST_GeomFromBinary(h.geom_wkb), b.g)
  AND ST_Intersects(ST_GeomFromBinary(p.geom_wkb), ST_GeomFromBinary(h.geom_wkb));

Verify that both pruning stages engaged, by reading the bytes scanned rather than the wall time:

trino --server "$TRINO_URL" --execute "EXPLAIN ANALYZE $(cat cross_domain_join.sql)" \
  | grep -E 'Input:|physicalInputBytes'
# Compare against the total snapshot size. Scanning a large fraction of it means
# the partition predicates did not match the actual partition column names.

# 4. Bound the query before it runs

An engine will happily attempt a join no amount of parallelism can finish. Session limits turn that into a fast, explicit refusal.

-- Session bounds, set per consuming role rather than per query, so a consumer
-- cannot opt out of them by omitting a clause.
SET SESSION query_max_scan_physical_bytes = '256GB';
SET SESSION query_max_execution_time      = '30m';
SET SESSION query_max_memory              = '64GB';
SET SESSION join_distribution_type        = 'AUTOMATIC';
-- Broadcast the smaller side rather than repartitioning both, where it fits.
SET SESSION join_max_broadcast_table_size = '2GB';

# Configuration Reference

# Common Failure Modes & Fixes

The query scans the entire snapshot despite a bbox predicate. Root cause: the predicate is expressed only against the geometry column, which has no usable Parquet statistics. Fix: add the flattened bbox columns and predicate on them; the geometry predicate stays for exactness, not for skipping.

Partition pruning does not engage. Root cause: the predicate uses a computed value or a join-derived value for a partition column, which the engine cannot evaluate at planning time. Fix: supply partition values as literals; resolve them in the client before submitting.

Row-group skipping is ineffective even with bbox columns. Root cause: rows are unsorted within each file, so every row group’s bbox range spans the whole partition and none can be skipped. Fix: sort by a spatial key at write time; this is a writer change, not a query change.

The join succeeds and returns nothing. Root cause: the two snapshots are in different CRS. The bbox comparisons operate on numbers that do not overlap and the engine reports an empty result rather than an error. Fix: assert the CRS on both sides from the catalog before submitting.

Memory exhaustion on a join that used to work. Root cause: one side grew past the broadcast threshold, so the engine switched to repartitioning both sides. Fix: expected behaviour — either tighten the extent so the small side stays small, or accept the repartition and raise the memory bound deliberately.

# FAQ

# Related

• Federated Query and Cross-Domain Spatial Joins — the parent topic and the freshness-skew discipline
• Pushing Spatial Predicates Down with PostGIS FDW — the live, two-domain alternative
• GeoParquet vs Cloud Optimized GeoTIFF for Mesh Distribution — why the snapshot port is columnar