Running Spatial Quality Checks in BigQuery GIS

BigQuery makes one decision for you that changes the whole shape of a validation suite: its GEOGRAPHY type cannot represent an invalid geometry. Self-intersections, unclosed rings and inverted holes are rejected at parse time, so the classic validity rule set has nowhere to run — and the defects it would have caught arrive instead as load failures you must capture. What remains is still substantial: attribute contracts, topology between features, coverage rules, plausibility bounds and CRS discipline, all expressible as SQL over partitioned tables. This guide writes those rules the way BigQuery wants them — partition-filtered, cluster-aware and cost-instrumented — following the in-place approach of Warehouse and Lakehouse Spatial Validation.

Prerequisites

• A BigQuery dataset with a table whose geometry is stored as GEOGRAPHY, ingestion-time or column partitioned, and clustered on the geography column.
• Load-time error capture. Because invalid geometry fails at load, the loader must record rejects rather than aborting silently; without that, your worst data is invisible to the warehouse entirely.
• WGS 84 data. GEOGRAPHY is defined on the WGS 84 spheroid; projected coordinates must be transformed before loading, and the transformation belongs upstream.
• INFORMATION_SCHEMA access for the cost accounting in Step 5.
• A shared result contract so the findings table matches the one produced by other engines in the pipeline.

Step-by-Step Procedure

Step 1 — Establish what the type already rejected

-- Loader rejects are your validity findings; the table itself cannot contain them.
CREATE TABLE IF NOT EXISTS qa.load_rejects (
  load_date   DATE,
  source_uri  STRING,
  row_number  INT64,
  raw_wkt     STRING,
  error       STRING
) PARTITION BY load_date;

-- A safe parse pattern for staged text data: keep the bad rows instead of failing the load.
CREATE OR REPLACE TABLE staging.parcels_parsed AS
SELECT
  parcel_id,
  SAFE.ST_GEOGFROMTEXT(geom_wkt, make_valid => FALSE) AS geog,
  geom_wkt,
FROM staging.parcels_raw;

INSERT INTO qa.load_rejects (load_date, source_uri, row_number, raw_wkt, error)
SELECT CURRENT_DATE(), 'staging.parcels_raw', NULL, geom_wkt,
       'GEOGRAPHY parse failed — invalid or unsupported geometry'
FROM staging.parcels_parsed
WHERE geog IS NULL AND geom_wkt IS NOT NULL;

Verification: the reject count should be small and stable. A spike is the equivalent of a validity-rule spike elsewhere, and because SAFE.ST_GEOGFROMTEXT returns NULL rather than raising, it is easy to lose these rows entirely if nobody looks for them.

Note the make_valid => FALSE. Setting it to TRUE repairs on ingest, which is convenient and hides the defect — the row loads, nobody records that it was altered, and the upstream producer never learns. Repair deliberately or not at all.

Step 2 — Attribute rules first, geography untouched

-- Cheap column rules over one partition. Note: geog is never selected here.
CREATE TEMP TABLE attr_findings AS
SELECT parcel_id AS feature_id, 'ATTR_NULL_001' AS rule_id, 'blocker' AS severity,
       'parcel_id is null' AS message
FROM `proj.core.parcels`
WHERE _PARTITIONDATE = @run_date AND parcel_id IS NULL
UNION ALL
SELECT parcel_id, 'ATTR_DOMAIN_001', 'blocker',
       FORMAT('zoning_code %s not in the controlled list', zoning_code)
FROM `proj.core.parcels`
WHERE _PARTITIONDATE = @run_date
  AND zoning_code NOT IN ('R1', 'R2', 'C1', 'C2', 'M1')
UNION ALL
SELECT parcel_id, 'ATTR_RANGE_001', 'warning',
       FORMAT('assessed_value %d outside plausible range', assessed_value)
FROM `proj.core.parcels`
WHERE _PARTITIONDATE = @run_date
  AND (assessed_value < 0 OR assessed_value > 500000000);

Verification: check the bytes billed for this statement. Because the geog column is never referenced, BigQuery does not read it, and a column-rule pass over a large table should cost a small fraction of a geometry pass. That difference is the single most useful cost lever in the suite.

Step 3 — Cluster, then run the topology rules

-- One-off: clustering on the geography column enables S2-based block pruning.
CREATE OR REPLACE TABLE `proj.core.parcels`
PARTITION BY DATE(load_ts)
CLUSTER BY geog
AS SELECT * FROM `proj.core.parcels_unclustered`;

-- Overlap detection within the day's partition.
CREATE TEMP TABLE topo_findings AS
WITH day AS (
  SELECT parcel_id, geog
  FROM `proj.core.parcels`
  WHERE DATE(load_ts) = @run_date
)
SELECT
  a.parcel_id AS feature_id,
  'TOPO_OVERLAP_001' AS rule_id,
  'blocker' AS severity,
  FORMAT('overlaps %s by %.2f m2', b.parcel_id,
         ST_AREA(ST_INTERSECTION(a.geog, b.geog))) AS message
FROM day a
JOIN day b
  ON a.parcel_id < b.parcel_id
 AND ST_INTERSECTS(a.geog, b.geog)
WHERE NOT ST_TOUCHES(a.geog, b.geog)
  AND ST_AREA(ST_INTERSECTION(a.geog, b.geog)) > 0.5;

Verification: compare the query plan’s input rows for the join stage before and after clustering. On a clustered table the join reads a fraction of the blocks; on an unclustered one it reads all of them, and the cost difference on a large layer is substantial enough to notice on the invoice.

Step 4 — Coverage and containment rules

-- Every parcel must fall inside exactly one administrative district.
CREATE TEMP TABLE containment_findings AS
WITH day AS (
  SELECT parcel_id, geog FROM `proj.core.parcels`
  WHERE DATE(load_ts) = @run_date
),
hits AS (
  SELECT p.parcel_id, COUNT(d.district_id) AS district_count
  FROM day p
  LEFT JOIN `proj.ref.districts` d
    ON ST_INTERSECTS(p.geog, d.geog)
   AND ST_AREA(ST_INTERSECTION(p.geog, d.geog)) > 0.5 * ST_AREA(p.geog)
  GROUP BY p.parcel_id
)
SELECT parcel_id AS feature_id, 'TOPO_CONTAIN_001' AS rule_id, 'blocker' AS severity,
       CASE WHEN district_count = 0 THEN 'parcel is in no district'
            ELSE FORMAT('parcel is majority-inside %d districts', district_count) END AS message
FROM hits
WHERE district_count <> 1;

Verification: the majority-area test rather than a plain ST_WITHIN is deliberate. Real parcels sit slightly across district boundaries because of digitising differences, and a strict containment rule reports hundreds of false violations along every boundary. Requiring more than half the area removes those without hiding a genuinely misplaced parcel.

Step 5 — Publish findings and account for cost

-- One findings table, partitioned, using the shared contract.
CREATE TABLE IF NOT EXISTS qa.findings (
  run_date DATE, feature_id STRING, rule_id STRING, severity STRING, message STRING
) PARTITION BY run_date CLUSTER BY rule_id;

INSERT INTO qa.findings (run_date, feature_id, rule_id, severity, message)
SELECT @run_date, feature_id, rule_id, severity, message FROM attr_findings
UNION ALL SELECT @run_date, feature_id, rule_id, severity, message FROM topo_findings
UNION ALL SELECT @run_date, feature_id, rule_id, severity, message FROM containment_findings;

-- Cost per rule set, read back from the job history.
SELECT
  job_id,
  REGEXP_EXTRACT(query, r'rule_set_version=(\S+)') AS rule_set_version,
  total_bytes_billed / POW(1024, 3) AS gib_billed,
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS seconds
FROM `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND query LIKE '%qa.findings%'
ORDER BY total_bytes_billed DESC;

Verification: tag every validation query with a comment containing the rule-set version (-- rule_set_version=2026.08.1) so the cost query can attribute spend. Watching gigabytes billed per rule over time catches the rule that quietly stopped pruning long before the monthly bill does.

Interpreting Results

The area difference is worth understanding rather than eliminating. ST_AREA on a GEOGRAPHY returns square metres on the spheroid; the same parcel measured in a national projected system will differ by a small percentage depending on the projection’s distortion at that latitude. Neither is wrong. What is wrong is reporting one number this month and the other next month, so pick the authoritative computation and state it in the metadata, as described in Aligning Spatial Metadata with ISO 19157.

Gotchas & Edge Cases

Geodesic edges change topology near long boundaries. Two parcels sharing a long straight boundary in a projected system do not share a geodesic edge; the difference produces a sliver on the order of centimetres per kilometre. Tolerance-based predicates absorb it; exact-equality comparisons do not.

ST_GEOGFROMTEXT with make_valid => TRUE silently repairs. It is a reasonable ingest choice for messy third-party data, but only if the repair is recorded. Load into a staging table, compare areas before and after, and write a finding for anything that changed materially.

Clustering only helps if the query filters or joins on the clustered column. A rule that scans the whole partition and then filters in a WHERE clause on an attribute gains nothing from geography clustering — and the plan will show it.

ST_DWITHIN is usually cheaper than buffering. Writing ST_DWITHIN(a, b, 50) lets the engine use its index; ST_INTERSECTS(ST_BUFFER(a, 50), b) materialises a buffered geometry per row and cannot be pruned the same way.

Partition filters are easy to lose in a CTE. A WITH clause that omits the partition predicate scans everything even when the outer query filters — BigQuery cannot always push it down through the CTE. Put the filter in the innermost scan.

Approximate S2-based results are not returned by default, but tolerance still matters. ST_INTERSECTS on GEOGRAPHY is exact within the spheroidal model, yet coordinates loaded from a projected source carry conversion error. Keep an area or distance tolerance in every topology rule rather than testing for exact touching.

When to Escalate

• Load rejects that cannot be repaired upstream need a decision: repair on ingest with the change recorded, or refuse the delivery. That is a data-contract conversation, covered by the governance model in Defining Spatial Data Quality Policies.
• Cost growth that survives partition and clustering fixes suggests the rule set has outgrown per-run full scans. Move to incremental validation keyed on changed features rather than on load date.
• Rules needing planar semantics — precise cadastral coverage work, for instance — may simply not belong in BigQuery. Run them where the geometry is projected, and keep BigQuery for the attribute, containment and plausibility rules it does well.
• Disagreement between BigQuery and PostGIS findings is usually the geography/geometry distinction rather than a bug. Confirm which semantics each side used before treating it as an incident.

Frequently Asked Questions

Related

• Warehouse and Lakehouse Spatial Validation — the shared model these rules follow
• Validating Geometries in DuckDB Spatial — the planar, file-based counterpart
• Validating Spatial Data in Snowflake — a third engine with a different validity model

Back to Warehouse and Lakehouse Spatial Validation