Working with BigQuery GIS Data

BigQuery GIS allows you to analyze geographic data in BigQuery. Geographic data is also known as geospatial data.

BigQuery GIS adds support for a GEOGRAPHY data type to standard SQL. The GEOGRAPHY data type represents a pointset on the Earth's surface. A pointset is a set of points, lines and polygons on the WGS84 reference spheroid, with geodesic edges.

You use the GEOGRAPHY data type by calling one of the standard SQL geography functions. The output of the geography functions is rendered as WKT (well-known text). WKT uses a longitude first, latitude second format.

Geospatial data formats

Single points on Earth can be described by just a (longitude, latitude) pair. For describing more complex geographies such as lines and polygons, BigQuery allows you to load geospatial data into a GEOGRAPHY column if the data is in one of the following supported formats:

Differences between WKT and GeoJSON

Single geometry objects versus collections

WKT is text format for describing individual geometries such as a polygon. WKT is typically used to define the values in a column in a particular file format (such as a CSV file) or in a table.

GeoJSON is more a more complex, JSON-based format for geometries. For example, a point in GeoJSON would look like the following:

{ "type": "Point", "coordinates": [-121,41] }

GeoJSON is used to describe either:

  • A single geometry object (similar to WKT)
  • A Feature object. A feature object is an object with a geometry, plus arbitrary additional named properties.
  • A FeatureCollection. Feature collections are a set of feature objects similar to a table in a database or a CSV file with many rows and columns.

BigQuery GIS supports only single geometry objects in GeoJSON.

Coordinate systems and edges

The WKT format does not provide a coordinate system so BigQuery GIS defines one. In BigQuery GIS, WKT points are positions on the surface of a WGS84 spheroid (longitude + geodetic latitude). An edge is a spherical geodesic between two endpoints. In GeoJSON, the coordinate system is explicitly WGS84 coordinates with planar edges.

To convert between these two kinds of edges, BigQuery GIS adds additional points to the line where necessary so the converted sequence of edges remains within 10 meters of the original line. This is a process known as tessellation or non-uniform densification. Currently, you cannot directly control the tessellation process.

For importing geographies with spherical edges, use WKT as in the following example:

SELECT
  *,
  ST_GeogFromText(wkt) AS g
FROM
  [TABLE]

For importing geographies with planar edges (often called geometries in other systems), use GeoJSON as in the following example:

SELECT
  *,
  ST_GeogFromGeoJSON(geocol) AS g
FROM
  [TABLE]

You may also exclude the original GeoJSON column from the results:

SELECT
  * EXCEPT(geocol),
  ST_GeogFromGeoJSON(geocol) AS geocol
FROM
  [TABLE]

Be sure to use the proper format. Most systems will either advertise their support for parsing geography (not geometry) from WKT, or they assume planar edges, in which case GeoJSON should be used as an interchange format.

Your coordinates should be longitude, latitude, and if you have any long segments or edges then you'll need to make sure they're tessellated because BigQuery GIS interprets them as spherical geodesics which may not correspond to the coordinate system where your data originated.

Loading GIS data

When you load GIS data into BigQuery, you can specify a GEOGRAPHY column in the table's schema definition. When you specify the column's data type as GEOGRAPHY, BigQuery GIS can detect whether the data is in WKT or GeoJSON format.

When you load GeoJSON geometry objects into a GEOGRAPHY column, they should be formatted as text strings not JSON objects. This is true even if the object is being loaded from a newline-delimited JSON file.

If you load data using schema auto-detect, geography values are loaded as STRINGs. Currently, schema auto-detect cannot detect geography columns.

For more information about loading data into BigQuery, see Introduction to Loading Data from Cloud Storage.

Transforming GIS data

If your table contains separate columns for longitude and latitude, you can transform the values into geographies using standard SQL geography functions such as ST_GeogPoint. For example, if you have two DOUBLE columns for longitude and latitude, you can create a geography column using the following query.

SELECT
  *,
  ST_GeogPoint(longitude, latitude) AS g
FROM
  [TABLE]

BigQuery currently supports converting WKT and GeoJSON strings to geography types. Shapefiles and many other formats should be converted using external tools.

Dealing with improperly formatted spatial data

When you load data into BigQuery, you may encounter invalid WKT or GeoJSON data from other tools that fails to be converted to a GEOGRAPHY column. For example, an error such as Edge K has duplicate vertex with edge N indicates that the polygon has duplicate vertices (besides the first and last).

To avoid formatting issues, you can use a function that generates standards-compliant output. For example, when you export data from PostGIS, you can use the ST_MakeValid function to standardize the output.

To find or to ignore the improperly formatted data, use the SAFE function prefix to output the problematic data. For example, the following query uses the SAFE prefix to retrieve improperly formatted spatial data.

SELECT
  geojson AS bad_geojson
FROM
  [TABLE]
WHERE
  geojson IS NOT NULL
  AND SAFE.ST_GeogFromGeoJson(geojson) IS NULL

Partitioning and clustering GIS data

You can partition and cluster tables that contain GEOGRAPHY columns, but a GEOGRAPHY column cannot be used as the partitioning column or as the clustering column.

When you store GEOGRAPHY data in a table that is partitioned or clustered, if your queries filter data by using a spatial predicate, ensure your geography data is spatially compact. A spatial predicate calls a boolean geography function and has a GEOGRAPHY column as one of the arguments. The following sample shows a spatial predicate that uses the ST_DWithin function:

WHERE ST_DWithin(geo, ST_GeogPoint(longitude, latitude), 100)

For example, if you have a table with columns for COUNTRY, STATE, and ZIP, add a column to the table to store a concatenated version of these columns. The following query fragment demonstrates this:

CONCAT(country, '+', IFNULL(state, ''), '+', IFNULL(zip, '')) as loc

In this example, IFNULL is used to eliminate missing values. After you create the concatenated column, you can use it to cluster the table.

Using JOINs with spatial data

Spatial JOINs are joins of two tables with a predicate geographic function in the filter (WHERE) clause. For example:

#standardSQL
-- how many stations within 1 mile range of each zip code?
SELECT
    zipcode zip,
    ST_GeogFromText(ANY_VALUE(zip_codes.zipcode_geom)) polygon,
    COUNT(*) bike_stations
FROM
    `bigquery-public-data.new_york.citibike_stations` bike_stations,
    `bigquery-public-data.utility_us.zipcode_area` zip_codes
WHERE ST_DWithin(
         ST_GeogFromText(zip_codes.zipcode_geom),
         ST_GeogPoint(bike_stations.longitude, bike_stations.latitude),
         1609.34)
GROUP BY zip
ORDER BY bike_stations desc

Spatial JOINs are implemented for the following standard SQL predicate functions:

Spatial joins perform better when your geography data is persisted. The example above creates the geography values in the query. It is more performant to store the geography values in a BigQuery table.

For example, the following query retrieves longitude, latitude pairs and converts them to geographic points. When you run this query, you specify a new destination table to store the query results.

SELECT
  *,
  ST_GeogPoint(pLongitude, pLatitude) AS p
FROM
  [TABLE]

Exporting spatial data

When you export spatial data from BigQuery, GEOGRAPHY column values are always formatted as WKT strings. To export data in GeoJSON format, use the ST_AsGeoJSON function.

If the tools you're using to analyze the exported data do not understand the GEOGRAPHY data type, you can convert the column values to strings using a geographic function such as ST_AsText or ST_AsGeoJSON. BigQuery GIS adds additional points to the line where necessary so that the converted sequence of edges remains within 10 meters of the original geodesic line.

For example, the following query uses ST_AsGeoJSON to convert GeoJSON values to strings.

SELECT
  ST_AsGeoJSON(ST_MakeLine(ST_GeogPoint(1,1), ST_GeogPoint(3,2)))

The resulting data would look like the following:

{ "type": "LineString", "coordinates": [ [1, 1], [1.99977145571783, 1.50022838764041], [2.49981908082299, 1.75018082434274], [3, 2] ] }

Notice that the GeoJSON line has two additional points. BigQuery GIS adds these points so the GeoJSON line closely follows the same path on the ground as the original line.

Working with geography in the BigQuery Client Libraries

The BigQuery Client Libraries do not currently support the GEOGRAPHY data type.

If you are using the Client Libraries with BigQuery GIS, convert GEOGRAPHY values to WKT when necessary. For example, use the ST_AsText function: ST_AsText(ANY_VALUE(zip_regions_geometry.geometry)) AS geometry.

Converting to text using ST_AsText stores only one value, and converting to WKT means the data is annotated as a STRING type instead of a GEOGRAPHY type.

What's next

Was this page helpful? Let us know how we did:

Send feedback about...

Need help? Visit our support page.