Migrating PostGIS SQL to BigQuery GIS functions

This page ports a PostGIS dbt project’s spatial SQL to BigQuery GIS: which calls translate one-to-one, which change units or semantics while keeping their name, and which have no equivalent and need the model restructured instead.

When to use this approach

• You are moving an existing dbt project’s execution engine to BigQuery and want a checklist rather than a discovery process.
• You maintain one project that must run on both and need to know which calls to put behind a dispatched macro — the mechanism is in cross-engine UDF portability.
• A ported model runs but returns different numbers. The unit and semantic tables below are where that difference usually lives.

Prerequisites

• A working BigQuery target — profile setup is in running dbt spatial models on BigQuery GIS.
• An inventory of the spatial functions your project actually uses, produced in step 1 rather than from memory.
• Source data already in WGS84, since BigQuery cannot reproject.
• A parity dataset — the same rows on both engines — for the comparison in step 5.

Step-by-step instructions

1. Inventory every spatial call in the project

grep -rhoiE "st_[a-z_]+" models/ macros/ | tr 'A-Z' 'a-z' | sort | uniq -c | sort -rn

     41 st_intersects
     28 st_transform
     22 st_isvalid
     19 st_dwithin
     14 st_area
     11 st_makevalid
      9 st_subdivide
      7 st_simplify
      5 st_setsrid

That list is the migration plan. Sort it by count and work top down; the long tail is usually a handful of calls in one model.

2. Translate the direct equivalents

Most of the list ports without thought. The names match, the arguments match, and the results agree.

3. Rewrite the calls that changed units

These compile, run, and return wrong numbers if ported literally. They are the reason a “successful” migration can ship a broken mart.

-- PostGIS, geometry in EPSG:4326 — tolerance and radius are DEGREES
select
    st_simplify(geom, 0.0001)                as simplified,
    st_dwithin(a.geom, b.geom, 0.0045)       as nearby,
    st_area(geom)                            as area_sq_degrees
from ...

-- BigQuery GIS — tolerance and radius are METRES, area is square metres
select
    st_simplify(geog, 10)                    as simplified,
    st_dwithin(a.geog, b.geog, 500)          as nearby,
    st_area(geog)                            as area_sqm
from ...

Verify each converted literal against a known quantity rather than a conversion factor from memory:

select st_distance(
  st_geogpoint(13.4050, 52.5200),
  st_geogpoint(13.4050, 52.5290)
) as metres;
-- Expect ≈ 1001 m — one hundredth of a degree of latitude

4. Restructure around the functions that do not exist

Four PostGIS staples have no BigQuery counterpart, and each needs a different answer.

-- ST_Transform / ST_SetSRID: no equivalent — there is no SRID.
--   → Reproject during ingestion, load WGS84, and delete the call.

-- ST_MakeValid: no equivalent — repair happens at construction.
--   → SAFE.ST_GEOGFROMTEXT(wkt, make_valid => true)

-- ST_IsValid: no equivalent — invalid geography cannot exist in a column.
--   → Test for NULL after the safe constructor instead:
select count(*) as unparsed from {{ ref('stg_zones') }} where zone_geog is null;

-- ST_Subdivide: no equivalent, and no need — there is no index to tighten.
--   → Replace with an S2 or H3 grid key join, per the grid macros topic.

The ST_Subdivide line is the one that surprises people. Subdivision exists to tighten bounding boxes for an index; BigQuery has no user-managed index, so the equivalent optimisation is a grid-key prefilter, described in discrete global grid macros.

Verify that no unported calls remain:

dbt compile --target bq_dev 2>&1 | grep -i "function not found" || echo "no missing functions"

5. Run the parity comparison before switching consumers

-- models/intermediate/int_port_parity.sql
select
    z.zone_id,
    z.area_sqm                                  as bq_area,
    p.area_sqm                                  as pg_area,
    abs(z.area_sqm - p.area_sqm) / nullif(p.area_sqm, 0) as area_diff,
    z.ping_count                                as bq_pings,
    p.ping_count                                as pg_pings,
    z.ping_count - p.ping_count                 as ping_delta
from {{ ref('mart_zone_daily_activity') }} as z
join {{ source('parity', 'zone_daily_activity_postgis') }} as p
  using (zone_id, activity_date)

models:
  - name: int_port_parity
    tests:
      - dbt_utils.expression_is_true:
          expression: "abs(ping_delta) <= 2"
      - dbt_utils.accepted_range:
          column_name: area_diff
          max_value: 0.01

A ping delta of one or two rows per zone per day is the expected consequence of geodesic edges reassigning a handful of boundary points. A delta of hundreds means a predicate changed meaning — go back to step 3.

Configuration reference

Gotchas & edge cases

• A ported ST_Simplify tolerance of 0.0001 becomes a 0.0001-metre tolerance — effectively no simplification, so payload sizes silently stay large. Check the output vertex counts, not just that the query ran.
• ST_Union is ST_UNION_AGG in aggregate position and ST_UNION for two arguments; porting the wrong one produces a type error at compile time, which is the good case.
• BigQuery has no ST_SetSRID, so the common PostGIS idiom of stamping an SRID onto SRID-0 geometry has no target. Fix the source instead.
• Antimeridian-crossing geometry behaves differently. BigQuery takes the shorter path around the globe; planar engines do not. Datasets spanning the Pacific need explicit testing.

• SAFE. prefixes only work on some functions. SAFE.ST_GEOGFROMTEXT is supported; wrapping arbitrary spatial calls in SAFE. is not a general strategy.

FAQ

Related

• Running dbt Spatial Models on BigQuery GIS — the target-side setup.
• Warehouse-Native GIS Adapters — how these engines differ as a class.
• Dispatching Spatial Macros Across Warehouses — keeping one project compiling for both.

Up: Part of Warehouse-Native GIS Adapters.