Home Geolocation SQL in BigQuery: What’s the Right Syntax?

Databases

Geolocation SQL in BigQuery: What’s the Right Syntax?

Learn how to filter geolocation data with SQL in BigQuery. Fix common errors and understand ST_GEOGFROMTEXT and WHERE clause usage.

byDev Solutions

December 25, 2025

Frustrated developer fixing spatial SQL error in BigQuery using ST_GEOGFROMTEXT function

📍 BigQuery uses the GEOGRAPHY type for shapes on Earth. This makes measurements accurate across the world.
🛠️ ST_GEOGFROMTEXT is important for changing WKT into geospatial data that SQL queries can use.
🧭 WKT coordinates need to be longitude, then latitude. This helps avoid errors.
🚦 Functions like ST_CONTAINS and ST_WITHIN let you filter locations right in your WHERE clauses.
⚡ You can make things run faster by filtering early, using CTEs, and not parsing WKT over and over.

Filtering Geolocation Data in BigQuery SQL

If you work with location data—like tracking customers, fleet movement, area analysis, or geo-fencing—BigQuery SQL has a good set of functions and data types for location data. These let you work with and filter geographical data well. This guide shows you how to use these tools. It focuses on the ST_GEOGFROMTEXT function. Also, it covers common mistakes when handling location data, tips for better performance, and simple ways to query datasets that know about locations.

Geography Data Types in BigQuery

BigQuery has a special data type, GEOGRAPHY, to deal with geospatial data directly. GEOMETRY, often used in traditional Geographic Information Systems (GIS), treats the Earth as flat. But GEOGRAPHY works with data on the Earth's rounded surface. This makes locations much more accurate for different coordinates and sizes. This is very helpful when you work with data from all over the world.

Supported Geography Shapes

BigQuery works with many WKT shapes for its GEOGRAPHY type:

Point

This is one specific location, defined as:
```
ST_GEOGFROMTEXT('POINT(-122.4194 37.7749)')
```
You often use it for addresses, check-ins, or pin locations.
Polygon

This shape covers an area. It uses many connected coordinates. The shape must be closed, so the first and last coordinates need to match. It's used for things like city boundaries or delivery zones:
```
ST_GEOGFROMTEXT('POLYGON((-122.5 37.7, -122.5 37.8, -122.4 37.8, -122.4 37.7, -122.5 37.7))')
```
MultiPolygon

This is a group of polygons combined into one. It helps cover big, separate areas like an archipelago or multiple real-estate territories. You usually don't type these out yourself. People often import them from datasets.

Knowing these shape formats and when to use each is a basic part of filtering by location and querying using geographic borders.

Using ST_GEOGFROMTEXT() Properly

The main thing for spatial queries in BigQuery is changing Well-Known Text (WKT) into geometry BigQuery can use. And you do this with ST_GEOGFROMTEXT().

What Is ST_GEOGFROMTEXT?

This function takes a WKT string and changes it into a GEOGRAPHY object BigQuery can use. You need it a lot when you bring in WKT from mapping tools or location datasets.

Syntax & Format

ST_GEOGFROMTEXT('GEOMETRY_TYPE(coordinate_pairs)')

Example (San Francisco Point):

ST_GEOGFROMTEXT('POINT(-122.4194 37.7749)')

Key Formatting Rules

Coordinate order is always (longitude latitude). A common error is flipping this.
Geometry keywords like POINT, POLYGON, and MULTIPOLYGON must be in uppercase.
All coordinate pairs in polygons should form a closed loop.
Use single quotes ' ' around the entire WKT string.

If you work directly with latitude and longitude columns from your tables, think about using ST_GEOGPOINT(lon, lat) to make queries easier to write.

When to Use It

Use ST_GEOGFROMTEXT() when:

You copy or bring in WKT strings from other places.
You keep fixed boundaries as text (for example, delivery zones or political lines).
You do quick, one-time location queries for a special area.

Filtering Rows with Geographic Functions

Once you have your geography objects set up with ST_GEOGPOINT() or ST_GEOGFROMTEXT(), you can filter rows using location functions like ST_CONTAINS() and ST_WITHIN().

Using ST_CONTAINS()

This function checks if one location completely holds another.

Syntax:

ST_CONTAINS(geography_a, geography_b)

Example:

SELECT *
FROM users
WHERE ST_CONTAINS(
  ST_GEOGFROMTEXT('POLYGON(...)'),
  ST_GEOGPOINT(users.longitude, users.latitude)
)

Using ST_WITHIN()

This is the opposite of ST_CONTAINS()—it checks if a location is inside another:

WHERE ST_WITHIN(
  ST_GEOGPOINT(users.longitude, users.latitude),
  delivery_zones.geog_column
)

These functions let you use exact rules, like finding out if a place is inside a store's delivery area, or if an event happened in several target areas.

Boolean Output for WHERE Clause Integration

Both functions return true or false. So you can use them easily inside WHERE clauses.

Understanding WHERE Clauses with Spatial Logic

BigQuery handles location functions the same way it works with logical statements like =, >, IN, and so on. For example:

SELECT user_id
FROM users
WHERE ST_CONTAINS(
  ST_GEOGFROMTEXT('POLYGON((...))'),
  ST_GEOGPOINT(longitude, latitude)
)

This works the same as conditional comparisons like:

WHERE city = 'San Francisco'

But instead of matching fixed values, it checks if coordinates are included in an area.

Combining several location statements:

WHERE ST_CONTAINS(region_boundary, user_location)
  AND registration_date > '2024-01-01'

This combined rule finds users who are in an area and signed up during a certain time.

GeoJSON vs. WKT in BigQuery

BigQuery doesn’t support GeoJSON directly for its location functions. But many tools and APIs (like Leaflet.js, Mapbox) make GeoJSON data.

Converting GeoJSON to WKT

To use GeoJSON with BigQuery, change it to WKT with online tools:

Once it's changed, you can put the WKT right into ST_GEOGFROMTEXT().

Step-by-Step Example: Filtering Users in a City

Say you want to find all users inside the San Francisco boundary.

SELECT user_id
FROM users
WHERE ST_WITHIN(
  ST_GEOGPOINT(longitude, latitude),
  ST_GEOGFROMTEXT('POLYGON((...coordinates of SF...))')
)

Replace POLYGON((...)) with the actual WKT boundary of San Francisco.
Double-check that the polygon starts and ends at the same point to form a closed shape.

Debugging Common Errors

Location queries often have syntax and formatting problems, especially when you type them by hand. Look out for these things:

🚫 Coordinates swapped: Use (longitude latitude)
🚫 Polygons not closed: Put the first coordinate at the end again.
🚫 Bad characters: Extra commas or quotes often cause syntax errors.
🚫 Wrong case: Words like POINT() and POLYGON() need to be uppercase.

Example for testing WKT:

SELECT ST_GEOGFROMTEXT('POINT(-77.0365 38.8977)')

If this fails, you need to fix your WKT syntax before you use it on more data.

Performance Considerations

Location functions can be slow when you use them on a lot of data. Here's how to make them faster:

🔍 Filter early with bounding boxes or region codes when you can.
⛳ Use ST_GEOGPOINT() instead of parsing WKT strings again and again.
📁 Group or split data by area (like region, state, or zip code).
🧠 Save fixed locations using Common Table Expressions (CTEs).
⏱️ Add indexes for common searches using materialized views or join keys.

Example of using bounding pre-filter:

WHERE longitude BETWEEN -123 AND -122
  AND latitude BETWEEN 37 AND 38
  AND ST_WITHIN(...)

This greatly cuts down on location checks you don't need.

Collaboratable SQL Formatting

It's important for people to read and work together where many developers work. Here are tips for writing location SQL that works well:

✅ Use CTEs to keep parts of your logic separate.
✅ Give nicknames to complicated functions.
✅ Add comments about where your boundaries or shapes come from.

Example:

WITH sf_boundary AS (
  SELECT ST_GEOGFROMTEXT('POLYGON((...))') AS boundary
)
SELECT u.user_id
FROM users u, sf_boundary sfb
WHERE ST_WITHIN(ST_GEOGPOINT(u.longitude, u.latitude), sfb.boundary)

This makes your query easy to read, use again, and easy to test.

Handling Dynamic or User-Defined Geographies

In real apps, users often make their own service areas or draw shapes on a map. You send these changing boundaries as parameters or upload them between your app's parts.

Best ways to do it:

🧰 Check WKT format on your server before sending it to BigQuery.
🔐 Use BigQuery queries with parameters to stop injection attacks.
🧩 Save inputs you use often, especially for filters that get a lot of requests.

Example query with parameter:

SELECT user_id
FROM users
WHERE ST_WITHIN(
  ST_GEOGPOINT(longitude, latitude),
  ST_GEOGFROMTEXT(@user_polygon)
)

Alternatives: When to Use ST_GEOGPOINT()

If you work only with structured data (meaning lat/lon in columns), don't use WKT at all. This makes things faster:

WHERE ST_CONTAINS(
  zone.geog,
  ST_GEOGPOINT(users.longitude, users.latitude)
)

This stops extra work on WKT strings, makes checks faster, and cuts down on syntax errors.

Devsolus Developer FAQ

ST_WITHIN() versus ST_CONTAINS()?

ST_CONTAINS(outer, inner): Checks if one shape/query covers the other.
ST_WITHIN(inner, outer): This is the opposite of ST_CONTAINS().

They do the same thing, but one might be better depending on which shape you know first.

Can I find overlapping polygons?

Yes, you can. Use ST_INTERSECTS():

WHERE ST_INTERSECTS(zone_a.geog, zone_b.geog)

This is good for finding service zones or danger areas that overlap.

What about NULL locations?

BigQuery skips NULLs. But filtering helps keep things consistent:

WHERE geography_column IS NOT NULL
  AND ST_WITHIN(...)

Can I figure out distances?

Yes, ST_DISTANCE() finds the distance in meters between two points:

ST_DISTANCE(point1_geog, point2_geog)

Use it to set a limit for delivery distance:

WHERE ST_DISTANCE(hub_location, ST_GEOGPOINT(lon, lat)) <= 10000

(10 km radius)

Handy Tools for Geospatial SQL Work

Use these tools when getting ready or checking your data:

📏 OGC WKT Validator
✍️ Wicket GeoJSON-WKT Converter
🗺️ GeoJSON.io
📐 QGIS or a similar GIS program for complete location data setup.
🛰️ Google Maps SDK for drawing or putting shapes into your app parts.

Final Tips

To work well with location data in BigQuery SQL, you need to understand main formats like WKT, pick the right location functions like ST_CONTAINS() or ST_WITHIN(), and make queries faster with ST_GEOGPOINT() when you can. Using smart ways to boost performance, like CTEs and bounding filters—and tools to check shapes—you can confidently put out strong location-based analysis and good user experiences.

Have questions or need someone else to look at your location SQL? Join the DevSolus community. And then we can fix problems and make things faster together.