BQBrains | Geospatial Analytics in BigQuery: GEOGRAPHY, Spatial Joins, and Grid Systems That Keep the Bill Sane

Almost every warehouse has location in it somewhere — customer addresses, delivery events, store footprints, IoT pings, service territories — and almost every team exports that data to PostGIS or a notebook to do anything with it. BigQuery has had a native GEOGRAPHY type and a large ST_ function library for years, and it is fast enough that the export step is usually a mistake. This tutorial covers the parts that trip people up: what BigQuery's geometry model actually is, how to get clean geographies in, how to write spatial joins that do not turn into cross joins, and how to keep a spatial workload from quietly scanning terabytes.

What `GEOGRAPHY` is (and is not)

BigQuery's GEOGRAPHY is a spherical geometry type. Points, lines, and polygons live on the surface of the Earth (WGS84, SRID 4326), edges between vertices are geodesics — great-circle arcs — and distances come back in meters. There is no planar geometry type, no user-selectable SRID, and no ST_Transform. That has three practical consequences:

ST_DISTANCE returns meters. Never multiply by 111,111 or any other degree-fudge factor.
ST_AREA returns square meters, computed on the sphere, so it is correct for large polygons where a planar projection would distort.
Data imported from a projected coordinate system (State Plane, UTM, Web Mercator meters) must be reprojected to lon/lat before it reaches BigQuery. A GeoJSON file in EPSG:3857 will load without error and place your data somewhere in the Gulf of Guinea.

There is also ST_GEOGFROMTEXT(..., planar => TRUE) for the case where your source data was authored with planar edge semantics (most shapefiles are); it tells BigQuery to interpret straight lines between vertices as planar and convert them, which avoids subtle polygon-shape errors on large features.

Step 1: Getting geography data in

Three common paths:

From lon/lat columns. The most common case, and the cheapest.

CREATE OR REPLACE TABLE `demo.deliveries` AS
SELECT
  delivery_id,
  delivered_at,
  ST_GEOGPOINT(longitude, latitude) AS geog   -- longitude FIRST
FROM `demo.raw_deliveries`
WHERE longitude BETWEEN -180 AND 180
  AND latitude  BETWEEN -90  AND 90
  AND NOT (longitude = 0 AND latitude = 0);   -- drop null-island rows

Argument order is (longitude, latitude). Reversing it is the single most common bug in BigQuery geospatial work, and it fails silently for anywhere in the world where both values are plausible coordinates.

From WKT or GeoJSON text.

SELECT
  region_id,
  ST_GEOGFROMTEXT(wkt_geom, planar => TRUE, make_valid => TRUE) AS geog
FROM `demo.raw_regions`;

make_valid => TRUE repairs self-intersecting rings rather than failing the whole query — worth switching on for any third-party polygon file. Use ST_GEOGFROMGEOJSON(json_string, make_valid => TRUE) for GeoJSON geometry objects.

From files. Shapefiles and GeoParquet do not load natively. Convert with GDAL first (ogr2ogr -f GeoJSONSeq -t_srs EPSG:4326 out.geojsonl in.shp), load the newline-delimited GeoJSON as JSON rows, then parse the geometry with ST_GEOGFROMGEOJSON.

Validate after loading. A quick health check catches most import problems:

SELECT
  COUNTIF(geog IS NULL)                        AS null_geoms,
  COUNTIF(NOT ST_ISVALID(geog))                AS invalid_geoms,
  MIN(ST_X(ST_CENTROID(geog)))                 AS min_lon,
  MAX(ST_X(ST_CENTROID(geog)))                 AS max_lon,
  MIN(ST_Y(ST_CENTROID(geog)))                 AS min_lat,
  MAX(ST_Y(ST_CENTROID(geog)))                 AS max_lat
FROM `demo.regions`;

If the bounding box does not look like the country you expected, stop and fix the projection.

Step 2: The spatial join, done right

The workhorse query is "which polygon contains each point" or "which points are within N meters of each feature". BigQuery optimizes spatial joins when the predicate is one it recognizes, and degenerates to a cross join when it is not.

Point-in-polygon:

SELECT
  z.zcta_code,
  COUNT(*) AS deliveries
FROM `demo.deliveries` d
JOIN `bigquery-public-data.geo_us_boundaries.zip_codes` z
  ON ST_CONTAINS(z.zip_code_geom, d.geog)
GROUP BY 1;

Proximity join:

SELECT
  s.store_id,
  COUNT(DISTINCT d.delivery_id) AS deliveries_within_2km
FROM `demo.stores` s
JOIN `demo.deliveries` d
  ON ST_DWITHIN(s.geog, d.geog, 2000)   -- meters
GROUP BY 1;

Rules that decide whether the join is fast or ruinous:

Use ST_DWITHIN(a, b, meters) rather than ST_DISTANCE(a, b) < meters. The former is an optimizable predicate; the latter forces a full comparison of every pair.
Keep the spatial predicate in the ON clause as a plain conjunct. Wrapping it in CASE, COALESCE, or OR with a non-spatial condition defeats the optimizer.
ST_INTERSECTS, ST_CONTAINS, ST_WITHIN, ST_COVERS, ST_COVEREDBY, and ST_DWITHIN are the join predicates that get optimized. ST_TOUCHES, ST_CROSSES, and friends do not.
Simplify heavy polygons before joining. Census tracts and country outlines can carry tens of thousands of vertices; ST_SIMPLIFY(geog, 50) (50 m tolerance) can cut spatial-join slot time dramatically with no visible effect on a containment result.

If a join is still slow, check the stage timings: a spatial join that shows one stage with an enormous input row count is running as a cross product, and the predicate is the reason.

Step 3: Cluster on the geography column

BigQuery supports clustering directly on a GEOGRAPHY column, which sorts data by S2 cell coverage so that spatially local rows land in the same blocks. On a large point table it is the single highest-leverage change you can make.

CREATE OR REPLACE TABLE `demo.deliveries_clustered`
PARTITION BY DATE(delivered_at)
CLUSTER BY geog AS
SELECT * FROM `demo.deliveries`;

Now a bounded-area query prunes blocks instead of scanning the table:

SELECT COUNT(*)
FROM `demo.deliveries_clustered`
WHERE delivered_at >= '2026-01-01'
  AND ST_DWITHIN(geog, ST_GEOGPOINT(-73.9857, 40.7484), 1000);

Compare total_bytes_billed in INFORMATION_SCHEMA.JOBS for the clustered and unclustered versions — on a table of a few hundred million points the difference is routinely an order of magnitude.

Step 4: Grid systems — S2 and H3

When you need to aggregate rather than join — heatmaps, coverage density, privacy-preserving rollups — snap points to a grid cell ID and treat it as an ordinary integer or string key. Two options:

S2, Google's own hierarchical spherical grid, is built in:

SELECT
  S2_CELLIDFROMPOINT(geog, level => 13) AS cell,
  COUNT(*) AS n
FROM `demo.deliveries_clustered`
WHERE delivered_at >= '2026-01-01'
GROUP BY 1;

Level 13 cells are roughly a kilometre across; each level up halves the edge length. S2_CELLIDFROMPOINT plus GROUP BY is a plain hash aggregation — no spatial work at all, so it scales to billions of rows.

H3, Uber's hexagonal grid, has become the de-facto interchange grid for mobility and telco data. BigQuery has no built-in H3 functions, so you either install the community JavaScript UDFs (the carto-os public dataset publishes H3_FROMLONGLAT, H3_BOUNDARY, and friends) or compute the index upstream and store it as a column. If H3 keys appear in more than a couple of queries, store them: a JavaScript UDF over a billion rows is far more expensive than a materialized STRING column.

Grid keys also give you a cheap privacy control — aggregate to cell level and drop cells with fewer than k observations before anyone sees the result.

Step 5: Getting answers out

BigQuery Studio renders a GEOGRAPHY result column on a map directly in the query results pane — enough for validation and ad-hoc exploration.
Looker Studio consumes lon/lat or geography columns for point maps; for choropleths, join to a boundary set and output the region code.
Deck.gl / Kepler / CARTO read query results through the BigQuery API; export ST_ASGEOJSON(geog) and keep the payload small by simplifying geometry and limiting rows server-side.

The pattern that ages well: do all filtering, joining, and aggregation in BigQuery, and ship only the final few thousand rows to the renderer. Pulling raw geometry into the browser is what makes geospatial dashboards feel broken.

Cost and correctness checklist

GEOGRAPHY columns are billed at 16 bytes plus 24 bytes per vertex for on-demand scan pricing — polygon tables get expensive fast. Store a simplified geometry for joins and the full-resolution one in a separate table for rendering.
Partition on the event date, cluster on the geography, and always include both predicates.
Never call ST_GEOGPOINT inside a join predicate; materialize the geography column once.
Set maximum_bytes_billed on exploratory spatial queries — an accidental unoptimized spatial join is the classic five-figure surprise.
Re-check validity after any transformation pipeline; ST_MAKEVALID is cheaper than debugging a wrong count six weeks later.

Where this goes next

Once the geometry is clean and clustered, the interesting work starts: drive-time isochrones joined to demographics, service-territory optimization, spatial features fed into BigQuery ML models, or ST_ predicates layered into a semantic layer so analysts never have to think about S2 cells at all.

If you have location data sitting unused in BigQuery — or a spatial workload whose bill has outgrown its value — our performance optimization and data engineering teams do this work every week. Get in touch with a description of the dataset and the question you want answered.

Geospatial Analytics in BigQuery: GEOGRAPHY, Spatial Joins, and Grid Systems That Keep the Bill Sane

What GEOGRAPHY is (and is not)