Google BigQuery

BigQuery geography functions: points, distances, polygons and BigQuery ML

BigQuery stores locations in the GEOGRAPHY type and works on them with ST_ functions. This guide runs the ones analysts use most on one small table of New York stores and customers and shows every result: building points and polygons, ST_X and ST_Y, distances in metres, ST_DWITHIN, ST_CONTAINS against ST_INTERSECTS, buffers, DBSCAN clustering, the SAFE. prefix, and which BigQuery ML models accept a GEOGRAPHY column (only K-means).

Google BigQueryIevgen Krasovytskyi14 min read

BigQuery geography functions: points, distances, polygons and BigQuery ML

BigQuery keeps locations in one data type, GEOGRAPHY: points, lines and polygons on the WGS84 spheroid, with geodesic edges. Every function that works on it starts with ST_. Distances and areas come back in metres and square metres, and every point is written longitude first.

Function Does Watch out for
ST_GEOGPOINT(lon, lat) builds a point longitude first; a NULL input gives a NULL point
ST_GEOGFROMTEXT(wkt) reads WKT, such as a polygon an invalid polygon is an error, not NULL
ST_X, ST_Y longitude, latitude of a point error on anything that is not a single point
ST_DISTANCE(a, b) shortest distance, metres sphere by default; NULL if either side is NULL
ST_DWITHIN(a, b, m) true if within m metres in a join, gets BigQuery’s optimized spatial join; ST_DISTANCE(...) <= m does not
ST_CONTAINS(a, b) b is inside a a point on the boundary is not contained
ST_INTERSECTS(a, b) a and b share any point boundary points count
ST_BUFFER(g, m) a polygon m metres around g a circle is approximated by 32 segments
ST_UNION_AGG(g) merges a column of shapes ignores NULLs
ST_CLUSTERDBSCAN(g, m, n) OVER () cluster number per row noise rows get NULL

Note: Originally published in March 2025. Fully rewritten in October 2026: every result below was produced by running the query in BigQuery on 16 October 2026.

Reading to get this done?

The table used in every example

The table used in every example

Three stores, six customers and one delivery zone in New York. Paste this before any query below and it runs as is:

WITH stores AS (
  SELECT 'Midtown' AS store, -73.9855 AS lon, 40.7580 AS lat
  UNION ALL SELECT 'Union Square', -73.9903, 40.7359
  UNION ALL SELECT 'Downtown Brooklyn', -73.9903, 40.6928
),
customers AS (
  SELECT 101 AS customer_id, -73.9772 AS lon, 40.7527 AS lat
  UNION ALL SELECT 102, -73.9969, 40.7306
  UNION ALL SELECT 103, -73.9442, 40.6782
  UNION ALL SELECT 104, -73.9496, 40.7834
  UNION ALL SELECT 105, -74.0431, 40.7178
  UNION ALL SELECT 106, -73.9850, CAST(NULL AS FLOAT64)
),
zone AS (
  SELECT ST_GEOGFROMTEXT(
    'POLYGON((-74.02 40.70, -73.97 40.705, -73.955 40.765, -73.99 40.77, -74.015 40.735, -74.02 40.70))'
  ) AS area
)

Customer 101 is near Grand Central, 102 at Washington Square, 103 in Crown Heights, 104 on the Upper East Side, 105 in Jersey City. Customer 106 has no latitude, which is what a real address table looks like. The zone is a rough polygon over lower and midtown Manhattan.

ST_GEOGPOINT, ST_X and ST_Y

ST_GEOGPOINT, ST_X and ST_Y

SELECT
  customer_id,
  ST_GEOGPOINT(lon, lat) AS point,
  ST_X(ST_GEOGPOINT(lon, lat)) AS x_lon,
  ST_Y(ST_GEOGPOINT(lon, lat)) AS y_lat
FROM customers
ORDER BY customer_id;
customer_id point x_lon y_lat
101 POINT(-73.9772 40.7527) -73.9772 40.7527
102 POINT(-73.9969 40.7306) -73.9969 40.7306
103 POINT(-73.9442 40.6782) -73.9442 40.6782
104 POINT(-73.9496 40.7834) -73.9496 40.78340000000001
105 POINT(-74.0431 40.7178) -74.0431 40.7178
106 NULL NULL NULL

ST_X returns the longitude and ST_Y the latitude, as FLOAT64, so the last digit can wobble (40.78340000000001). Customer 106 shows the NULL rule: a NULL argument makes the point NULL, with no error, and that NULL then flows through every function below.

The trap: argument order. Google’s reference says it plainly: “Some systems present latitude first; take care with argument order” (read in October 2026). BigQuery only catches the mistake when the swapped latitude is out of range. For Tokyo, ST_GEOGPOINT(35.6764, 139.6500) fails with:

ST_GeogPoint failed: Latitude must be between -90 and 90 degrees. Actual value was 139.65; check if longitude and latitude arguments order is correct

For New York it does not fail. ST_GEOGPOINT(40.7527, -73.9772) returns POINT(40.7527 -73.9772), a valid point in Antarctica, and every distance after it is wrong by thousands of kilometres. Longitudes outside [-180, 180] are wrapped, not rejected.

Hand this to your AI

Your customer and store locations sit in BigQuery, and you need distances, coverage areas and clusters without fighting ST functions.

Paste this into your AI assistant. It writes the BigQuery geography SQL against your own location tables: nearest store, customers in range, and dense areas.

The prompt

write me BigQuery SQL on my store and customer tables that finds each customer's nearest store, counts customers within a delivery radius, and clusters the dense areas

ST_GEOGFROMTEXT: polygons from WKT

ST_GEOGFROMTEXT: polygons from WKT

The zone above is built from a WKT string. The full signature (read in October 2026):

ST_GEOGFROMTEXT(wkt_string [, oriented => BOOL] [, planar => BOOL] [, make_valid => BOOL])
  • oriented => TRUE takes the interior to be on the left of the vertex order, so a polygon can be bigger than a hemisphere. Without it, BigQuery returns the smaller of the two possible polygons.
  • planar => TRUE reads edges as straight lines on a flat map instead of geodesics.
  • make_valid => TRUE tries to repair a polygon that breaks the OGC rules.
  • oriented cannot be TRUE together with planar or with make_valid.

The ring must close: the last vertex repeats the first. The zone covers 29.16 km² (ROUND(ST_AREA(area) / 1e6, 2)).

The trap: a self-crossing polygon stops the whole query. A “bow tie” ring:

SELECT ST_GEOGFROMTEXT('POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))');
ST_GeogFromText failed: Invalid polygon loop: Edge 0 crosses edge 2

Two ways out:

SELECT
  SAFE.ST_GEOGFROMTEXT('POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))') AS bowtie_safe,
  ST_ASTEXT(ST_GEOGFROMTEXT('POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))', make_valid => TRUE)) AS bowtie_repaired;
bowtie_safe bowtie_repaired
NULL MULTIPOLYGON(((0 0, 1 1.00045705372412, 0 2, 0 0)), ((1 1.00045705372412, 2 0, 2 2, 1 1.00045705372412)))

SAFE. turns the error into NULL, so you can find bad rows with WHERE wkt IS NOT NULL AND SAFE.ST_GEOGFROMTEXT(wkt) IS NULL. make_valid splits the bow tie into two triangles. They meet at latitude 1.000457, not 1, because BigQuery edges are geodesics, not straight lines on the map.

ST_DISTANCE: the nearest store

ST_DISTANCE: the nearest store

, distances AS (
  SELECT c.customer_id, s.store,
    ST_DISTANCE(ST_GEOGPOINT(c.lon, c.lat), ST_GEOGPOINT(s.lon, s.lat)) AS metres,
    ST_DISTANCE(ST_GEOGPOINT(c.lon, c.lat), ST_GEOGPOINT(s.lon, s.lat), TRUE) AS metres_wgs84
  FROM customers AS c CROSS JOIN stores AS s
)
SELECT customer_id,
  MIN_BY(store, metres) AS nearest_store,
  ROUND(MIN(metres)) AS metres,
  ROUND(MIN(metres_wgs84)) AS metres_wgs84
FROM distances
GROUP BY customer_id
ORDER BY customer_id;

The leading comma adds distances to the sample WITH.

customer_id nearest_store metres metres_wgs84
101 Midtown 914 915
102 Union Square 810 811
103 Downtown Brooklyn 4213 4221
104 Midtown 4137 4140
105 Union Square 4883 4893
106 NULL NULL NULL

ST_DISTANCE returns metres. By default it measures on a perfect sphere; the optional third argument TRUE measures on the WGS84 spheroid (read in October 2026). The gap here is 1 to 10 metres, 0.05% to 0.2%. That argument is positional: use_spheroid => TRUE fails with “Named argument use_spheroid does not exist in signature”.

It also works between a point and a polygon, which the old version of this article said it could not do: customer 103 is 3,689 metres from the zone, and customer 102, inside it, is 0.

The trap: picking the nearest row with ARRAY_AGG. The common pattern ARRAY_AGG(store ORDER BY distance LIMIT 1)[OFFSET(0)] returned “Midtown” for customer 106, whose distances are all NULL. NULLs sort first, so it picks any store. MIN_BY (see aggregate functions) skips NULL distances and returns NULL, which is the honest answer.

ST_DWITHIN and ST_CONTAINS: who is in range

ST_DWITHIN and ST_CONTAINS: who is in range

SELECT
  c.customer_id,
  ST_CONTAINS(z.area, ST_GEOGPOINT(c.lon, c.lat)) AS in_zone,
  LOGICAL_OR(ST_DWITHIN(ST_GEOGPOINT(c.lon, c.lat), ST_GEOGPOINT(s.lon, s.lat), 1000)) AS within_1km_of_store,
  COUNTIF(ST_DWITHIN(ST_GEOGPOINT(c.lon, c.lat), ST_GEOGPOINT(s.lon, s.lat), 5000)) AS stores_within_5km
FROM customers AS c
CROSS JOIN zone AS z
CROSS JOIN stores AS s
GROUP BY c.customer_id, in_zone
ORDER BY c.customer_id;
customer_id in_zone within_1km_of_store stores_within_5km
101 true true 2
102 true true 3
103 false false 1
104 false false 1
105 false false 1
106 NULL NULL 0

ST_DWITHIN(a, b, 1000) is true when the two are at most 1,000 metres apart. It says what you mean better than ST_DISTANCE(a, b) <= 1000, and in a join it matters more: Google lists ST_DWITHIN among the predicates that get an optimized spatial join, for inner and cross joins, and ST_DISTANCE is not on that list (read in October 2026). Customer 106 is NULL again, and COUNTIF counts it as 0.

The trap: points on the boundary. ST_CONTAINS needs the interiors to intersect, and Google notes that “a GEOGRAPHY does not contain its own boundary” (read in October 2026). Tested on three points:

SELECT
  label,
  ST_CONTAINS(area, p) AS contains_,
  ST_INTERSECTS(area, p) AS intersects_,
  ST_COVERS(area, p) AS covers_
FROM zone,
UNNEST([
  STRUCT('inside' AS label, ST_GEOGPOINT(-73.99, 40.74) AS p),
  STRUCT('on a corner', ST_GEOGPOINT(-74.02, 40.70)),
  STRUCT('outside', ST_GEOGPOINT(-73.95, 40.68))
]);
label contains_ intersects_ covers_
inside true true true
on a corner false true true
outside false false false

For “is this point in the area?”, ST_INTERSECTS or ST_COVERS gives the answer most people expect. ST_INTERSECTS is also the usual join condition between points and polygons.

A real boundary table: which county each customer is in

A real boundary table: which county each customer is in

The public dataset bigquery-public-data.geo_us_boundaries holds US boundaries as GEOGRAPHY:

SELECT c.customer_id, k.county_name, k.state_fips_code
FROM customers AS c
LEFT JOIN `bigquery-public-data.geo_us_boundaries.counties` AS k
  ON ST_INTERSECTS(k.county_geom, ST_GEOGPOINT(c.lon, c.lat))
ORDER BY c.customer_id;
customer_id county_name state_fips_code
101 New York 36
102 New York 36
103 Kings 36
104 New York 36
105 Hudson 34
106 NULL NULL

Manhattan is New York County, Brooklyn is Kings, and customer 105 is in Hudson County, New Jersey (FIPS 34). This scanned 191 MB, because the polygons are large. On your own tables, Google recommends clustering by the GEOGRAPHY column when queries filter with spatial predicates; a GEOGRAPHY column can be a clustering column but not a partitioning column (read in October 2026).

ST_BUFFER and ST_UNION_AGG: delivery coverage

ST_BUFFER and ST_UNION_AGG: delivery coverage

, coverage AS (
  SELECT ST_UNION_AGG(ST_BUFFER(ST_GEOGPOINT(lon, lat), 2000)) AS area
  FROM stores
)
SELECT
  ROUND(ST_AREA(area) / 1e6, 2) AS covered_km2,
  ROUND(3 * ACOS(-1) * 4, 2) AS three_circles_km2,
  ST_NUMGEOMETRIES(area) AS pieces,
  ARRAY(
    SELECT customer_id FROM customers
    WHERE NOT ST_COVERS(area, ST_GEOGPOINT(lon, lat))
    ORDER BY customer_id
  ) AS not_covered
FROM coverage;
covered_km2 three_circles_km2 pieces not_covered
34.21 37.7 2 [103, 104, 105]

ST_BUFFER draws a 2 km polygon around each store, and ST_UNION_AGG merges them. The Midtown and Union Square circles overlap, so the union has 2 pieces and covers 34.21 km², less than three separate circles (37.7 km²). Part of the gap is also the buffer itself: by default it approximates a quarter circle with 8 segments (read in October 2026), so each “circle” is a 32-sided polygon.

The trap: NULL drops out of NOT. Customer 106 is in neither list. NOT ST_COVERS(area, NULL) is NULL, not true, so a missing location is silently treated as “covered”. Count NULL locations separately before you report coverage.

ST_UNION_AGG ignores NULL shapes. The array form, ST_UNION(array), ignores NULL elements too, but the two-argument ST_UNION(a, b) returns NULL if either is NULL.

ST_UNION: Merge shapes into one

ST_UNION: Merge shapes into one

ST_UNION returns the point set union of its inputs as one GEOGRAPHY. It has two forms: two geographies, or an array of geographies. This runs both, plus the NULL rule from above:

SELECT
  ST_ASTEXT(ST_UNION(
    ST_GEOGFROMTEXT('LINESTRING(-122.12 47.67, -122.19 47.69)'),
    ST_GEOGFROMTEXT('LINESTRING(-122.12 47.67, -100.19 47.69)'))),
  -- LINESTRING(-100.19 47.69, -122.12 47.67, -122.19 47.69)
  ST_UNION(ST_GEOGPOINT(-73.99, 40.73), CAST(NULL AS GEOGRAPHY)) IS NULL,
  -- true
  ST_ASTEXT(ST_UNION([ST_GEOGPOINT(-73.99, 40.73), CAST(NULL AS GEOGRAPHY), ST_GEOGPOINT(-74.00, 40.75)]));
  -- MULTIPOINT(-73.99 40.73, -74 40.75)

The two lines share a start point, so they merge into one line. With two arguments a NULL makes the result NULL; with an array the NULL element is ignored. For a whole column, use ST_UNION_AGG, as in the coverage example above.

SRID mismatch: Why BigQuery has no SRID to mismatch

SRID mismatch: Why BigQuery has no SRID to mismatch

In some spatial databases every shape carries a coordinate system identifier, an SRID, and mixing two raises an SRID mismatch error. BigQuery’s GEOGRAPHY does not carry one. Google’s documentation describes the type as points on the WGS84 reference spheroid, and says that WKT, which has no coordinate system of its own, is read as WGS84 longitude and latitude, so you are asked to make sure the source data matches. A union or a distance between two geographies therefore never fails for a coordinate system reason. The failure you can hit instead is a source in a different system, such as projected coordinates in metres:

SELECT ST_GEOGFROMTEXT('POINT(500000 4000000)');
-- ST_GeogFromText failed: Latitude must be between -90 and 90 degrees. Actual value was 4000000

That error appears only when the numbers fall outside the valid longitude and latitude range, so check the source’s coordinate system and convert to longitude and latitude before loading.

ST_CLUSTERDBSCAN: finding dense areas

ST_CLUSTERDBSCAN: finding dense areas

SELECT
  customer_id,
  ST_CLUSTERDBSCAN(ST_GEOGPOINT(lon, lat), 3500, 2) OVER () AS cluster_3500m,
  ST_CLUSTERDBSCAN(ST_GEOGPOINT(lon, lat), 4500, 2) OVER () AS cluster_4500m
FROM customers
WHERE lat IS NOT NULL
ORDER BY customer_id;
customer_id cluster_3500m cluster_4500m
101 0 0
102 0 0
103 NULL NULL
104 NULL 0
105 NULL 0

ST_CLUSTERDBSCAN(geography, epsilon, minimum_geographies) is a window function: epsilon is a radius in metres, and a point joins a cluster when enough neighbours are within it. Points that belong nowhere get NULL, which DBSCAN calls noise. Cluster numbers start at 0.

The trap: epsilon decides the answer. At 3,500 metres only 101 and 102 form a cluster. At 4,500 metres, 104 and 105 join them. Neither is wrong; the radius is a business decision (how far does a courier go?) and belongs in the query’s description, not in someone’s memory. Use PARTITION BY in the OVER clause to cluster each city separately.

Predictive models: what BigQuery ML does with GEOGRAPHY

Predictive models: what BigQuery ML does with GEOGRAPHY

BigQuery ML trains models with CREATE MODEL and scores them with ML.PREDICT, all in SQL. Google’s table of supported input feature types (read in October 2026) lists GEOGRAPHY for one model type: K-means. Linear and logistic regression, boosted trees, deep neural networks, wide-and-deep, AutoML, PCA, autoencoders and ARIMA_PLUS_XREG do not take it.

A dry run shows it. This statement, validated by dry run (not run), passes:

CREATE OR REPLACE MODEL `your_project.your_dataset.customer_areas`
OPTIONS (model_type = 'KMEANS', num_clusters = 2) AS
-- sample WITH from the top of the article goes here
SELECT ST_GEOGPOINT(lon, lat) AS location
FROM customers
WHERE lat IS NOT NULL;

The same column in a logistic regression is rejected at dry run:

Column location in query statement is a GEOGRAPHY column, which is currently not supported for LOGISTIC_REG models.

For a churn or demand model, turn the location into numbers first: ST_X and ST_Y, the distance to the nearest store, or a flag from ST_DWITHIN. This version, also validated by dry run, is accepted:

CREATE OR REPLACE MODEL `your_project.your_dataset.churn_geo`
OPTIONS (model_type = 'LOGISTIC_REG', input_label_cols = ['churned']) AS
-- sample WITH from the top of the article goes here
SELECT
  c.lon,
  c.lat,
  MIN(ST_DISTANCE(ST_GEOGPOINT(c.lon, c.lat), ST_GEOGPOINT(s.lon, s.lat))) AS metres_to_store,
  c.customer_id IN (103, 105) AS churned  -- placeholder label
FROM customers AS c
CROSS JOIN stores AS s
WHERE c.lat IS NOT NULL
GROUP BY c.customer_id, c.lon, c.lat;

The label here is a placeholder; use your real churn flag. Five rows will not train anything useful. The point is the shape: geography becomes numeric features, and ML.PREDICT later needs the same columns computed the same way. If you only need to group nearby points, ST_CLUSTERDBSCAN above does it with no model at all.

The SAFE. prefix on geography functions

The SAFE. prefix on geography functions

SAFE. in front of a function returns NULL instead of an error. Google is precise about its limits (read in October 2026): it only catches errors caused by the function’s own input values, such as “value out of range”, not errors raised while evaluating its arguments. Operators and CAST do not take it; use SAFE_CAST for casts (see conversion functions).

SELECT
  SAFE.ST_GEOGPOINT(35.6764, 139.6500) AS swapped_point,
  SAFE.ST_GEOGFROMTEXT('POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))') AS bowtie,
  SAFE.ST_X(ST_GEOGFROMTEXT('LINESTRING(0 0, 1 1)')) AS x_of_line,
  ST_X(ST_CENTROID(ST_GEOGFROMTEXT('LINESTRING(0 0, 1 1)'))) AS x_of_centroid;
swapped_point bowtie x_of_line x_of_centroid
NULL NULL NULL 0.4999619199226218

ST_X on a line is an error; with SAFE. it is NULL. If you want a coordinate for a line or a polygon, take ST_CENTROID first. Its centroid is 0.49996, not 0.5, because the line is a geodesic.

Use SAFE. to find bad rows, then fix or exclude them on purpose. Used everywhere, it turns broken coordinates into NULLs that drop out of counts without a word.

Geography in reports

Geography in reports

Most location bugs do not error: a swapped coordinate that still lands somewhere valid, a NULL that drops out of NOT, a boundary point that ST_CONTAINS leaves out, an epsilon nobody wrote down. Each gives a believable number.

The safer setup is to decide these once. In OWOX Data Marts the query that builds points, picks the nearest store and sets the delivery radius is published as a Data Mart, with those choices written in its description. Sheets, Data Studio and AI assistants all read the same data model, so the “within 2 km” figure is the same everywhere. OWOX Data Marts runs your SQL as written, in your own BigQuery project, and does not transform the data. Every other function family is mapped in the BigQuery functions overview.

Hand this to your AI

Your customer and store locations sit in BigQuery, and you need distances, coverage areas and clusters without fighting ST functions.

Paste this into your AI assistant. It writes the BigQuery geography SQL against your own location tables: nearest store, customers in range, and dense areas.

The prompt

write me BigQuery SQL on my store and customer tables that finds each customer's nearest store, counts customers within a delivery radius, and clusters the dense areas

FAQ

Frequently Asked Questions

What order does ST_GEOGPOINT take its arguments in?

Longitude first, then latitude: ST_GEOGPOINT(lon, lat). BigQuery only raises an error when the swapped value is outside the latitude range of -90 to 90, so a swapped New York point silently lands near Antarctica.

Can I use a GEOGRAPHY column as a feature in BigQuery ML?

Only in K-means models, according to Google's table of supported input feature types. Logistic regression and the other model types reject it, so convert the location into numbers first, such as ST_X, ST_Y or the distance to the nearest store.

What units does ST_DISTANCE return in BigQuery?

Metres. By default it measures on a perfect sphere; passing TRUE as the third argument measures on the WGS84 spheroid, which in our New York test changed distances by about 0.2 percent.

What is the difference between ST_CONTAINS and ST_INTERSECTS?

ST_CONTAINS returns FALSE for a point that lies on the polygon's boundary, because a GEOGRAPHY does not contain its own boundary. ST_INTERSECTS and ST_COVERS return TRUE for that point, which is usually what you want for a point-in-area check.

How do I handle invalid polygons in ST_GEOGFROMTEXT?

Prefix the function with SAFE. to get NULL instead of an error, which lets you find the bad rows. To repair a self-crossing polygon, call ST_GEOGFROMTEXT with make_valid => TRUE.

What does the SAFE. prefix do in BigQuery?

It makes a function return NULL instead of raising an error caused by its input values, such as an out-of-range latitude. It does not catch errors from evaluating the arguments, and it does not work with operators or CAST, where SAFE_CAST is the alternative.

Who wrote this

Ievgen Krasovytskyi

Ievgen Krasovytskyi Head of Marketing

Ievgen Krasovytskyi is the Head of Marketing at OWOX, leading strategy across content, SEO, product marketing, and AI-powered automation. With deep expertise in analytics infrastructure, data warehouses, and marketing technology, he builds systems that connect marketing performance to business outcomes. Ievgen writes about SaaS growth, analytics workflows, and the future of AI in marketing operations.