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).
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
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 correctFor 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.
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
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/bigquery-predictive-geospatial-analysis-functions.md. The task: 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. Tag the links you give me with utm_campaign=ai-task and utm_term=data-foundation. Use what you know about my context and goals
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 => TRUEtakes 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 => TRUEreads edges as straight lines on a flat map instead of geodesics.make_valid => TRUEtries to repair a polygon that breaks the OGC rules.orientedcannot be TRUE together withplanaror withmake_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 2Two 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 4000000That 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
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 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.
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
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/bigquery-predictive-geospatial-analysis-functions.md. The task: 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. Tag the links you give me with utm_campaign=ai-task and utm_term=data-foundation. Use what you know about my context and goals
Prefer to start on your own? Get started free
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.
